SQL Query Analysis with Schema

Schema:

• student(rollNo, name, degree, year, sex, deptNo, advisor):
  • deptNo: Foreign key referencing deptId in the department relation.
  • advisor: Foreign key referencing empId in the professor relation.
• department(deptId, name, hod, phone):
  • hod: Foreign key referencing empId in the professor relation.
• professor(empId, name, sex, startYear, deptNo, phone):
  • deptNo: Foreign key referencing deptId in the department relation.
• course(courseId, cname, credits, deptNo):
  • deptNo: Foreign key referencing deptId in the department relation.
• enrollment(rollNo, courseId, sem, year, grade):
  • rollNo: Foreign key referencing rollNo in the student relation.
  • courseId: Foreign key referencing courseId in the course relation.
• teaching(empId, courseId, sem, year, classRoom):
  • empId: Foreign key referencing empId in the professor relation.
• preRequisite(preCourseId, courseId):
  • preCourseId and courseId: Foreign keys referencing courseId in the course relation.

Question:

SELECT rollNo  
FROM Student  
WHERE sex = ALL (SELECT sex FROM Professor WHERE empId = advisor);

Statements:

1. The query includes a correlated subquery.
2. The query includes an uncorrelated subquery.
3. Replacing ALL with ANY changes the result of the query.
4. Replacing ALL with ANY does not change the result of the query.

Options:

1. Only 1 and 3 are true
2. Only 1 and 4 are true
3. Only 2 and 3 are true
4. Only 2 and 4 are true

Correct Answer: Only 1 and 4 are true

Solution:

1. Correlated Subquery: The subquery uses values from the outer query, making it correlated. Hence, Statement 1 is true.
2. Uncorrelated Subquery: The subquery depends on the outer query, so it is not standalone. Statement 2 is false.
3. Replacing ALL with ANY: This changes the condition from checking against all rows to checking against at least one row. However, due to the specific relationships in the schema, the results remain the same. Statement 3 is false.
4. Results with ANY: The results are equivalent whether ALL or ANY is used, making Statement 4 true.

Final Answer: Only Statements 1 and 4 are true.

SQL Query to Retrieve Students with Specific Advisor Criteria

Identify the SQL query that correctly retrieves the roll numbers and names of students belonging to department number 3 who are advised by a female professor from a department other than department number 3.

Options:

1. SELECT s.rollNo, s.name
   FROM student s
   WHERE s.deptNo = 3 AND
         s.advisor IN (SELECT p.empId
                       FROM professor p
                       WHERE p.sex = 'F' AND p.deptNo <> 3);
   

2. SELECT s.rollNo, s.name
   FROM student s
   WHERE s.deptNo = 3 AND
         s.advisor IN (SELECT p.empId
                       FROM professor p
                       WHERE p.sex = 'F' AND p.deptNo = 3);
   

3. SELECT s.rollNo, s.name
   FROM student s, professor p
   WHERE s.deptNo = 3 AND
         p.deptNo <> 3 AND
         s.advisor IN (SELECT p.empId
                       FROM professor p
                       WHERE p.sex = 'F');
   

4. SELECT s.rollNo, s.name
   FROM student s
   WHERE s.deptNo = 3 AND
         s.advisor IN (SELECT p.empId
                       FROM professor p
                       WHERE p.sex = 'F' AND p.deptNo = 3);
   

Correct Answer:

Option 1

Solution:

The correct query retrieves students belonging to department 3 who are advised by a female professor from a department other than department 3. This is achieved by correctly using a subquery to filter professors based on the conditions.

Question

SQL does not have an equivalent operator for which of the following?

Options:

1. Universal quantifier
2. Existential quantifier
3. Union
4. Intersection

Correct Answer:

Universal quantifier

Solution:

Explanation:

SQL is a query language designed to retrieve and manipulate data stored in a relational database. While it provides many operators for various operations, it lacks an explicit equivalent for the universal quantifier (denoted as ∀ in logic). Instead, universal quantification is often simulated using combinations of other constructs like NOT EXISTS or EXCEPT.

• Existential Quantifier: SQL supports the existential quantifier (∃) through the EXISTS operator, which checks for the presence of rows satisfying a condition.
• Union: SQL supports the union operation explicitly through the UNION operator, which combines the results of two queries.
• Intersection: SQL provides the INTERSECT operator for retrieving common rows between two result sets.
• Universal Quantifier: There is no direct FOR ALL construct in SQL, but its functionality can be achieved using indirect methods.

Thus, the correct answer is Universal quantifier.

Question

Which one of the following operations is not allowed on the referencing row when the referenced row is deleted?

Options:

1. Setting the value of foreign key attribute(s) to NULL
2. Setting the default value(s) for foreign key attributes
3. Deleting the referencing row
4. Leaving the row unchanged

Correct Answer:

Leaving the row unchanged

Solution:

Explanation:

In a relational database, a foreign key constraint ensures referential integrity between two tables. When a referenced row is deleted, SQL provides several mechanisms to handle the corresponding referencing rows. These include:

• Setting the value of foreign key attributes to NULL: This is allowed when the foreign key is defined with the ON DELETE SET NULL option.
• Setting the default value(s) for foreign key attributes: This is possible if the foreign key is defined with the ON DELETE SET DEFAULT option.
• Deleting the referencing row: Permitted if the foreign key is defined with the ON DELETE CASCADE option, which propagates the delete operation to referencing rows.
• Leaving the row unchanged: This is not allowed because it would violate referential integrity by leaving foreign key attributes in the referencing row pointing to a non-existent row.

Thus, the operation “Leaving the row unchanged” is not allowed.

Question

In SQL, the operator NOT IN is equivalent to which of the following?

Options:

1. = ANY
2. = ALL
3. <> ANY
4. <> ALL

Correct Answer:

<> ALL

Solution:

Explanation:

The NOT IN operator in SQL is used to filter rows where a value does not match any value in a subquery or a list. It is logically equivalent to the condition <> ALL. This means that the value must not be equal to any value in the given set.

• = ANY: Checks if a value matches at least one value in the set.
• = ALL: Checks if a value matches all values in the set.
• <> ANY: Checks if a value does not match at least one value in the set.
• <> ALL: Ensures the value is different from all values in the set, which is the equivalent behavior of NOT IN.

Thus, the correct equivalence for NOT IN is <> ALL.

Question

Which of the following SQL commands is used for schema modification?

Options:

1. Alter
2. Update
3. Insert
4. Delete

Correct Answer:

Alter

Solution:

Explanation:

In SQL, schema modification involves changing the structure of a database table or its attributes. The ALTER command is specifically designed for this purpose. It allows you to:

• Add new columns to a table.
• Drop existing columns from a table.
• Modify existing columns, such as changing data types or constraints.
• Add or remove constraints on the table.

The other commands have different purposes:

• UPDATE: Modifies existing data within a table.
• INSERT: Adds new records to a table.
• DELETE: Removes records from a table.

Hence, the correct command for schema modification is ALTER.

Question

In a UNION operation in SQL, which of the following must hold true?

1. The two relations must have the same number of attributes with matching data types.
2. The attributes of the two relations must appear in the same order for the UNION operation to work.
3. The attributes can appear in any order, as long as the names are identical.

Options:

1. Only (1) is true
2. Only (2) is true
3. Both (1) and (2) are true
4. Both (1) and (3) are true

Correct Answer:

Both (1) and (2) are true

Solution:

Explanation:

A UNION operation in SQL combines the results of two SELECT queries into a single set while removing duplicate rows. To perform a UNION operation, the following conditions must be satisfied:

• Union Compatibility: The two relations must have the same number of attributes, and their corresponding attributes must have compatible data types. This ensures that the UNION operation can correctly align the rows.
• Attribute Order: The attributes must be in the same order in both relations. This ensures that the UNION operation aligns the columns correctly. SQL does not enforce attribute names but requires the column sequence to be consistent.

Incorrect Condition (3): SQL does not allow attributes to appear in any order for the UNION operation. The sequence must match between the two relations.

Thus, (1) and (2) are true, and the correct answer is Option 3.

Schema Details

The following schema represents an academic institution’s relational database:

• Student: (rollNo, name, degree, year, sex, deptNo, advisor)
• Department: (deptId, name, hod, phone)
• Professor: (empId, name, sex, startYear, deptNo, phone)
• Course: (courseId, cname, credits, deptNo)
• Enrollment: (rollNo, courseId, sem, year, grade)
• Teaching: (empId, courseId, sem, year, classRoom)
• PreRequisite: (preCourseId, courseId)

Schema Relationships:

• Foreign Keys:
  • deptNo in Student, Professor, and Course relations references deptId in the Department relation.
  • advisor in Student references empId in Professor.
  • rollNo in Enrollment references rollNo in Student.
  • courseId in Enrollment and Teaching references courseId in Course.
  • empId in Teaching references empId in Professor.
  • preCourseId and courseId in PreRequisite reference courseId in Course.

Question

Which of the following queries retrieves the roll numbers and names of students who have enrolled in at least one course offered by Department number 5?

Options:

1. SELECT s.rollNo, s.name FROM Student s WHERE EXISTS (SELECT c.courseId FROM Course c WHERE c.deptNo = 5 AND EXISTS (SELECT e.* FROM Enrollment e WHERE e.courseId = c.courseId AND e.rollNo = s.rollNo));
2. SELECT s.rollNo, s.name FROM Student s WHERE EXISTS (SELECT c.courseId FROM Course c WHERE c.deptNo = 5 AND NOT EXISTS (SELECT e.* FROM Enrollment e WHERE e.courseId = c.courseId AND e.rollNo = s.rollNo));
3. SELECT s.rollNo, s.name FROM Student s WHERE NOT EXISTS (SELECT c.courseId FROM Course c WHERE c.deptNo = 5);
4. SELECT s.rollNo, s.name FROM Student s WHERE EXISTS (SELECT e.courseId FROM Enrollment e WHERE e.courseId = 5);

Correct Answer:

Option 1

Solution

Explanation:

To retrieve the roll numbers and names of students enrolled in at least one course offered by Department number 5:

1. The inner query SELECT c.courseId FROM Course c WHERE c.deptNo = 5 fetches all courses offered by Department 5.
2. The second inner query checks for the existence of corresponding enrollment records in the Enrollment table, where the course matches the result from the first query and the rollNo matches the current Student.
3. The outer query ensures that only those students with valid enrollments in Department 5 courses are returned.

The other options are incorrect because:

• Option 2: Uses NOT EXISTS, which incorrectly excludes students enrolled in Department 5 courses.
• Option 3: Excludes students not enrolled in courses entirely, which does not align with the question.
• Option 4: Directly filters for a courseId, ignoring the Department constraint.

Question

Which of the following operations cannot be performed using the DELETE command in SQL?

Options:

1. Delete certain tuples by specifying a WHERE clause.
2. Delete all the data in a table.
3. Delete the value of a specific attribute.
4. Delete a specific attribute.

Correct Answer:

• Delete the value of a specific attribute.
• Delete a specific attribute.

Solution

Explanation:

The SQL DELETE command is used to remove rows (tuples) from a table based on conditions provided in the WHERE clause. If no condition is specified, it removes all rows from the table. However, the DELETE command cannot:

1. Delete the value of a specific attribute: To change or clear the value of an attribute, the UPDATE command is used with the SET clause.
2. Delete a specific attribute: Removing a column or attribute from a table structure requires the ALTER TABLE command.

Examples:

1. Deleting specific rows:

DELETE FROM Employee WHERE Department = 'HR';

This removes all employees belonging to the HR department.

2. Deleting all rows:

DELETE FROM Employee;

This removes all data but retains the table structure.

To delete a specific column (attribute), the correct approach would be:

ALTER TABLE Employee DROP COLUMN Age;

The DELETE command does not support operations on individual attribute values or structure modifications.

Question

Which of the following is NOT an advantage of using database views?

Options:

1. Data hiding
2. Simplified query formulation
3. Efficient query answering
4. Saving of storage space

Correct Answer:

• Saving of storage space

Solution

Explanation:

Database views provide several benefits, but saving storage space is not one of them. Here’s why:

1. Advantages of Views:
   • Data Hiding: Views allow selective access to data by restricting visibility of certain columns or rows, enhancing security.
   • Simplified Query Formulation: Complex queries can be encapsulated in views, simplifying access for users.
   • Efficient Query Answering: By using materialized views or indexes, performance can be improved for specific queries.
2. Not an Advantage: Saving of storage space: Views themselves do not consume significant storage space unless they are materialized (materialized views store data separately). The base tables remain unchanged, and the view only serves as a virtual layer.

Examples:

1. Data Hiding with Views:

CREATE VIEW EmployeeView AS
SELECT Name, Department
FROM Employee
WHERE Salary > 50000;

This view restricts access to only the name and department of employees earning more than 50,000.

2. Simplified Query:

Without a view:

SELECT Name, Department
FROM Employee
WHERE Salary > 50000 AND JobTitle = 'Manager';

With a view:

SELECT * FROM EmployeeView WHERE JobTitle = 'Manager';

Question

Under which circumstances are updates on SQL views restricted?

Options:

1. When modifications affect multiple base tables.
2. When the view includes aggregate values like SUM or AVG.
3. When the primary key of a base table is missing from the view definition.
4. All of the above.

Correct Answer:

All of the above

Solution

Explanation:

• Multiple base tables: If a view is derived from multiple base tables, updating the view might require propagating changes to all related base tables, which can be complex or ambiguous.
• Aggregate values in the view: Views that contain aggregate functions (like SUM, AVG, COUNT, etc.) cannot be updated as there’s no direct mapping between the aggregated data in the view and the base table rows.
• Missing primary key: If the primary key is not included in the view, there is no unique identifier for the rows in the base table, making updates ambiguous.
• Conclusion: Combining these factors makes updates on such views either complex or unsupported, leading to restrictions.

Final Notes

SQL views are logical representations of data and do not physically store the data. Therefore, updates on views need to align with the rules of the underlying base tables, and these situations create ambiguity, leading to restrictions.

 

SQL Query for Minimum Marks Retrieval in GATE CS Branch

Schema

        gateMarks(regNo, name, sex, branch, city, state, marks)

Here, regNo uniquely identifies a student who has written the GATE examination.
The attribute branch indicates the branch of engineering, such as CS, EC, etc.
Other attributes are self-explanatory.

Question

Which of the following SQL queries retrieves the regNo, name, and marks of students who obtained the minimum marks in the “CS” branch?

1. select name, min(marks)
   from gateMarks
   where branch="CS";
   

2. select regNo, name, marks
   from gateMarks
   where branch="CS" and marks = ANY (select min(marks) 
                                      from gateMarks 
                                      where branch="CS");
   

3. select regNo, name, marks
   from gateMarks
   where branch="CS" and marks <= ALL (select marks 
                                       from gateMarks 
                                       where branch="CS");
   

4. select regNo, name, marks
   from gateMarks
   where branch="CS" and marks <= ANY (select marks 
                                       from gateMarks 
                                       where branch="CS");
   

Correct Answer

Option 2:

select regNo, name, marks
from gateMarks
where branch="CS" and marks = ANY (select min(marks) 
                                   from gateMarks 
                                   where branch="CS");

Solution

The query correctly identifies the students who have scored the minimum marks in the “CS” branch. Here’s the breakdown:

• The ANY operator is used in conjunction with the subquery
  (select min(marks) ...).
• The subquery computes the minimum marks for the branch “CS”.
• The main query selects the regNo, name, and marks where the marks match the minimum marks.

Explanation of Other Options:

• Option 1 fails because it only retrieves the name and the minimum marks, not the regNo and all students with the minimum marks.
• Option 3 compares the marks with all marks in the branch but does not correctly use the <= operator.
• Option 4 incorrectly uses ANY to retrieve all students with marks less than or equal to the minimum.

 

 

Database Schema and SQL Query for GATE Exam

Complete Schema

GATE Exam Details Schema

gateMarks(regNo, name, sex, branch, city, state, marks)

• regNo: Uniquely identifies a student who has written the GATE examination.
• branch: Indicates the branch of engineering, such as CS, EC, etc.
• Other attributes are self-explanatory.

Academic Institution Relational Database Schema

student(rollNo, name, degree, year, sex, deptNo, advisor)
department(deptId, name, hod, phone)
professor(empId, name, sex, startYear, deptNo, phone)
course(courseId, cname, credits, deptNo)
enrollment(rollNo, courseId, sem, year, grade)
teaching(empId, courseId, sem, year, classRoom)
preRequisite(preCourseId, courseId)

• deptNo: A foreign key in the student, professor, and course relations referring to deptId of department relation.
• advisor: A foreign key in the student relation referring to empId of professor relation.
• hod: A foreign key in the department relation referring to empId of professor relation.
• rollNo: A foreign key in the enrollment relation referring to rollNo of student relation.
• courseId: A foreign key in the enrollment and teaching relations referring to courseId of course relation.
• empId: A foreign key of the teaching relation referring to empId of professor relation.
• preCourseId and courseId: Foreign keys in the preRequisite relation referring to courseId of the course relation.

SQL Query Example

Question

Which of the following SQL queries retrieves the regNo, name, and marks of students who obtained the minimum marks in the “CS” branch?

1. select name, min(marks)
   from gateMarks
   where branch="CS";
   

2. select regNo, name, marks
   from gateMarks
   where branch="CS" and marks = ANY (select min(marks) 
                                      from gateMarks 
                                      where branch="CS");
   

3. select regNo, name, marks
   from gateMarks
   where branch="CS" and marks <= ALL (select marks 
                                       from gateMarks 
                                       where branch="CS");
   

4. select regNo, name, marks
   from gateMarks
   where branch="CS" and marks <= ANY (select marks 
                                       from gateMarks 
                                       where branch="CS");
   

Correct Answer

Option 2:

select regNo, name, marks
from gateMarks
where branch="CS" and marks = ANY (select min(marks) 
                                   from gateMarks 
                                   where branch="CS");

Solution

The query correctly identifies the students who have scored the minimum marks in the “CS” branch. Here’s the breakdown:

• The ANY operator is used in conjunction with the subquery
  (select min(marks) ...).
• The subquery computes the minimum marks for the branch “CS”.
• The main query selects the regNo, name, and marks where the marks match the minimum marks.

Explanation of Other Options:

• Option 1 fails because it only retrieves the name and the minimum marks, not the regNo and all students with the minimum marks.
• Option 3 compares the marks with all marks in the branch but does not correctly use the <= operator.
• Option 4 incorrectly uses ANY to retrieve all students with marks less than or equal to the minimum.

 

Database Management Systems: Above-Average Marks Query

Schema

Schema 1: GATE Exam Details
gateMarks(regNo, name, sex, branch, city, state, marks)

Schema 2: Academic Institution Database
student(rollNo, name, degree, year, sex, deptNo, advisor)
department(deptId, name, hod, phone)
professor(empId, name, sex, startYear, deptNo, phone)
course(courseId, cname, credits, deptNo)
enrollment(rollNo, courseId, sem, year, grade)
teaching(empId, courseId, sem, year, classRoom)
preRequisite(preCourseId, courseId)

Question

Which of the following options would retrieve the regNo, name, and marks of students who obtained above-average marks in the branch EC in the gateMarks table?

Options:

1. SELECT regNo, name, marks
   FROM gateMarks
   WHERE branch = 'EC' AND marks >= (SELECT AVG(marks)
                                      FROM gateMarks
                                      WHERE branch = 'EC');
   

2. CREATE VIEW temp AS
   SELECT AVG(marks) AS avgm
   FROM gateMarks
   WHERE branch = 'EC';
   
   SELECT regNo, name, marks
   FROM gateMarks
   WHERE branch = 'EC' AND marks >= (SELECT avgm FROM temp);
   

3. CREATE VIEW temp AS
   SELECT AVG(marks) AS avgm
   FROM gateMarks
   WHERE branch = 'EC';
   
   SELECT regNo, name, marks
   FROM gateMarks, temp
   WHERE branch = 'EC' AND marks >= temp.avgm;
   

4. SELECT regNo, name, marks
   FROM gateMarks
   WHERE branch = 'EC' AND marks >= ANY (SELECT AVG(marks)
                                          FROM gateMarks
                                          WHERE branch = 'EC');
   

Correct Answer

Options 1 and 2

Detailed Solution

Option 1: This query directly calculates the average marks of branch EC using a subquery and filters students whose marks are greater than or equal to the computed average.

Option 2: A view named temp is created to store the average marks of branch EC. The main query then uses this view to filter students based on their marks being greater than or equal to the average marks.

Option 3: Combines the gateMarks table with the view temp but uses an incorrect condition marks >= temp.avgm, which could result in errors in certain SQL implementations.

Option 4: Attempts to use ANY, which is semantically incorrect for this scenario because AVG() returns a single scalar value, and ANY expects a list of values.

SQL Query to Retrieve Department with Maximum Total Credits

Question

Which of the following SQL queries will retrieve the department number (deptNo) and the total credits (totalCredits1) of the department(s) that have the maximum total of credits for courses offered across all departments?

Options:

1. SELECT deptNo, SUM(credits) AS totalCredits1
   FROM course
   GROUP BY deptNo
   HAVING totalCredits1 = ANY (
       SELECT MAX(x.totalCredits)
       FROM (
           SELECT SUM(credits) AS totalCredits
           FROM course
           GROUP BY deptNo
       ) AS x
   );
   

2. SELECT deptNo, MAX(SUM(credits)) AS totalCredits1
   FROM course
   GROUP BY deptNo;
   

3. SELECT deptNo, SUM(credits) AS totalCredits1
   FROM course
   GROUP BY deptNo
   HAVING totalCredits1 = MAX(totalCredits1);
   

4. SELECT deptNo, SUM(credits) AS totalCredits1
   FROM course
   GROUP BY deptNo
   HAVING totalCredits1 >= ALL (
       SELECT SUM(credits)
       FROM course
       GROUP BY deptNo
   );
   

Correct Answer:

Option 1:

SELECT deptNo, SUM(credits) AS totalCredits1
FROM course
GROUP BY deptNo
HAVING totalCredits1 = ANY (
    SELECT MAX(x.totalCredits)
    FROM (
        SELECT SUM(credits) AS totalCredits
        FROM course
        GROUP BY deptNo
    ) AS x
);

Solution:

The correct query retrieves the department number (deptNo) and total credits for the department(s) with the highest total credits among all departments. It uses a subquery to compute the maximum total credits for each department and then compares this value with the total credits of all departments using the HAVING clause.

Explanation for Each Option:

• Option 1: Correct. Computes the sum of credits grouped by department and matches the maximum total credits using HAVING totalCredits1 = ANY.
• Option 2: Incorrect. Misuses MAX(SUM(credits)), which is invalid SQL syntax in the context.
• Option 3: Incorrect. HAVING totalCredits1 = MAX(totalCredits1) is invalid as MAX(totalCredits1) is not available as an aggregate function in this scope.
• Option 4: Incorrect. Uses >= ALL but doesn’t correctly match the semantics of selecting departments with the highest credits.

Schema:

CREATE TABLE course (
    deptNo INT,
    credits INT
);