Sql Practice Questions

1. Inner Query Execution:

   • The condition C.cname = 'Mark' identifies cid = 102 from the coach table.
   • Joining with team on T.cid = C.cid means we look for teams coached by cid = 102. From the team table, tid = 10 (MI) is coached by cid = 102.
   • Joining with members on M.tid = T.tid means we find players in tid = 10. From the members table, pid = 1 and pid = 3 are members of tid = 10.
   • The inner query SELECT M.pid returns the set of player IDs: $\{1, 3\}$.

2. Outer Query Execution:

   • The outer query SELECT MIN(P.age) FROM player P WHERE P.pid IN (1, 3) selects players whose pid is either 1 or 3.
   • For pid = 1, age = 31.
   • For pid = 3, age = 26.
   • The MIN(P.age) function then finds the minimum age among these players, which is $\min(31, 26) = 26$.

The value returned by the SQL query is 26.

The final answer is $\boxed{26}$

The SQL query SELECT * FROM Student WHERE gender = 'F' AND marks > 65; filters the Student table.
It selects rows where the gender is 'F' (Female) AND marks are greater than 65.
From the table, the female students are Aliya (rollNum 2, marks 70), Aliya (rollNum 3, marks 80), and Swati (rollNum 5, marks 65).
Applying the marks > 65 condition:

• Aliya (rollNum 2) has 70 marks, which is greater than 65.
• Aliya (rollNum 3) has 80 marks, which is greater than 65.
• Swati (rollNum 5) has 65 marks, which is not greater than 65.
  Thus, two rows satisfy both conditions.

The SQL query is:
SELECT * FROM Student AS S WHERE NOT EXIST (SELECT CNO FROM Course WHERE dNo = "D01" EXCEPT SELECT CNO FROM Register WHERE sNo = S.sNo)

Let's break down the NOT EXIST subquery:
INNER_SUBQUERY = (SELECT CNO FROM Course WHERE dNo = "D01" EXCEPT SELECT CNO FROM Register WHERE sNo = S.sNo)

1. SELECT CNO FROM Course WHERE dNo = "D01": This gets all CNOs (Course Numbers) for courses offered by department D01.
   From Course table: D01 courses are C11 (DS) and C12 (OS).
   Result set 1: {C11, C12}.

2. SELECT CNO FROM Register WHERE sNo = S.sNo: This gets all CNOs for courses registered by the current student S.sNo. This is correlated with the outer query's S.sNo.

3. INNER_SUBQUERY = (Result set 1 EXCEPT Result set 2): This means, for a given student S.sNo, find the courses from D01 that the student has NOT registered for.
   If INNER_SUBQUERY is empty, it means the student S.sNo has registered for ALL courses offered by department D01.

The WHERE NOT EXIST (...) clause means the outer query selects students S for whom the INNER_SUBQUERY is empty.
So, the query selects students who have registered for all courses offered by department D01.

Let's examine students and their registered courses for D01 courses ({C11, C12}):

• S01 (James, D01):
  Registered courses for S01: {C11, C12}.
  SELECT CNO FROM Course WHERE dNo = "D01" = {C11, C12}.
  SELECT CNO FROM Register WHERE sNo = S01 = {C11, C12}.
  {C11, C12} EXCEPT {C11, C12} = $\emptyset$.
  NOT EXIST ($\emptyset$) is TRUE. So, S01 is selected.

• S02 (Rocky, D02): (Student is from D02, but query condition only depends on D01 courses.)
  Registered courses for S02: {C11}.
  SELECT CNO FROM Course WHERE dNo = "D01" = {C11, C12}.
  SELECT CNO FROM Register WHERE sNo = S02 = {C11}.
  {C11, C12} EXCEPT {C11} = {C12}.
  NOT EXIST ({C12}) is FALSE (since C12 exists in the result). So, S02 is NOT selected.

• S03 (Jackson, D02):
  Registered courses for S03: {C21, C22, C23}. (These are not D01 courses).
  SELECT CNO FROM Course WHERE dNo = "D01" = {C11, C12}.
  SELECT CNO FROM Register WHERE sNo = S03 = {C21, C22, C23}.
  {C11, C12} EXCEPT {C21, C22, C23} = {C11, C12}.
  NOT EXIST ({C11, C12}) is FALSE. So, S03 is NOT selected.

• S04 (Jane, D01):
  Registered courses for S04: {C11, C12}.
  SELECT CNO FROM Course WHERE dNo = "D01" = {C11, C12}.
  SELECT CNO FROM Register WHERE sNo = S04 = {C11, C12}.
  {C11, C12} EXCEPT {C11, C12} = $\emptyset$.
  NOT EXIST ($\emptyset$) is TRUE. So, S04 is selected.

• S05 (Milli, D02):
  Registered courses for S05: {C11, C21}.
  SELECT CNO FROM Course WHERE dNo = "D01" = {C11, C12}.
  SELECT CNO FROM Register WHERE sNo = S05 = {C11, C21}.
  {C11, C12} EXCEPT {C11, C21} = {C12}.
  NOT EXIST ({C12}) is FALSE. So, S05 is NOT selected.

The students selected are S01 and S04.
The query SELECT * FROM Student will return all attributes for these selected students.
So, the number of rows returned is 2.

The query calculates deptId and count(*) from the emp table.
The where clause filters employees by gender = "female" and salary > (select avg(salary) from emp).
The subquery (select avg(salary) from emp) calculates the average salary of ALL employees in the company.
So, the where clause selects female employees whose salary is greater than the average salary of ALL employees in the company.
The group by deptId clause then groups these filtered female employees by their department ID and counts them.
Therefore, the query gives, for each department, the number of female employees whose salary is greater than the average salary of employees in the company.

The RIGHT OUTER JOIN retrieves all rows from $TableB$ and joins them with $TableA$ where $A.columnName = B.columnName$, resulting in $NULL$ values for columns from $TableA$ where no match is found. The clause WHERE A.columnName IS NULL effectively filters this result set to include only the rows from $TableB$ that do not have a corresponding record in $TableA$. This logic isolates the records in $TableB$ that exist outside the set intersection with $TableA$. Therefore, it returns the rows from $TableB$ exclusive of any matching equality condition with $TableA$.

The query involves a subquery to first calculate the average cost for parts with pno = 'P4'.

1. The inner query (SELECT AVG(cost) FROM Catalogue WHERE pno = 'P4' GROUP BY pno) identifies all entries in the Catalogue table where pno is 'P4' and computes their average cost. The costs for 'P4' are 200 and 250, so the average is $(200 + 250) / 2 = 225$.
2. The outer query then selects s.sno, s.sname from a join of Suppliers (aliased as s) and Catalogue (aliased as c) where s.sno = c.sno and c.cost > 225.
3. Join Suppliers and Catalogue on sno. Then filter for cost > 225.
   • S1 (cost 150) is not > 225.
   • S1 (cost 50) is not > 225.
   • S1 (cost 100) is not > 225.
   • S2 (cost 200) is not > 225.
   • S2 (cost 250) is > 225. (This yields S2, M/s Balaji furniture)
   • S3 (cost 250) is > 225. (This yields S3, M/s Premium furniture)
   • S3 (cost 150) is not > 225.
   • S3 (cost 300) is > 225. (This yields S3, M/s Premium furniture)
   • S3 (cost 250) is > 225. (This yields S3, M/s Premium furniture)
4. The matching rows from the Suppliers table are:
   • (S2, M/s Balaji furniture) because c.sno=S2 has cost=250.
   • (S3, M/s Premium furniture) because c.sno=S3 has cost=250.
   • (S3, M/s Premium furniture) because c.sno=S3 has cost=300.
   • (S3, M/s Premium furniture) because c.sno=S3 has cost=250.
5. Therefore, a total of 4 rows are returned.

The DELETE command is a DML (Data Manipulation Language) operation that logs row-by-row deletions, making it part of a transaction; thus, changes can be reverted using ROLLBACK before a COMMIT is executed. In contrast, TRUNCATE is a DDL (Data Definition Language) operation that deallocates storage pages directly, which typically triggers an implicit COMMIT and prevents the operation from being rolled back. Because DELETE allows for transactional recovery while TRUNCATE does not, both statement A and statement C correctly describe the fundamental difference in their behaviors.

[CORRECT_OPTION: A, C]

The SQL query lacks a join condition in the WHERE clause (e.g., S.Roll_no = P.Roll_no), resulting in a Cartesian product (cross join) between the Student and Performance tables. The query then groups the result by S.Student_name. Since there are 5 distinct student names in the Student table, the GROUP BY clause will create 5 groups. The WHERE P.Marks > 84 clause is applied, but since it's a cross product, each of the 5 students will be associated with all performance records meeting this criterion. As long as at least one performance record has marks > 84, every student group will be non-empty. Thus, the query will return one row for each of the 5 students.

The relational algebra expression for SELECT DISTINCT w,x FROM R,S is $\pi_{w,x}(R \times S)$.
The Cartesian product $R \times S$ produces tuples $(r, s)$ for all $r \in R$ and $s \in S$.
If $S = \emptyset$, then $R \times S = \emptyset$, so the projection is empty, meaning $R$ cannot be returned. Thus, $S$ must be non-empty.
If $S \neq \emptyset$, then $\pi_{w,x}(R \times S) = \{ (r.w, r.x) \mid r \in R \}$.
Because the operation includes DISTINCT, all duplicate tuples in $R$ are removed in the output.
For the result to equal $R$, $R$ must not contain any duplicates initially.
Thus, the condition is that $R$ has no duplicates and $S$ is non-empty.

The query performs an inner join across the $Students$, $Courses$, and $Grades$ tables using the conditions defined in the $WHERE$ clause.

1. $Students.Roll\_number = Grades.Roll\_number$ joins student records with their grades.
2. $Courses.Instructor = 'Sriram'$ filters for courses taught specifically by Sriram.
3. $Courses.Course\_number = Grades.Course\_number$ links these filtered courses to the grades.
4. $Grades.Grade = 'A'$ restricts the result set to only those records where the student earned an 'A'.
   Because the query selects the $Name$ of any student satisfying these conditions for at least one record without using aggregation functions like $COUNT$ or grouping, it identifies students who received an 'A' in at least one course taught by Sriram.

A FULL OUTER JOIN returns all rows from both tables, including matched rows and unmatched rows from each table. An INNER JOIN returns only matched rows. A LEFT OUTER JOIN returns all rows from the left table and matched rows from the right. A RIGHT OUTER JOIN does the opposite. By definition, the result of a FULL OUTER JOIN contains all rows from the INNER, LEFT, and RIGHT joins. Therefore, its output is always a superset of the other three.

The query performs a natural join between two projections of the original table: T_1 = \pi_{\text{Employee, Department}}(\text{Overtime_allowance}) and T_2 = \pi_{\text{Department, OT_allowance}}(\text{Overtime_allowance}).
The join condition is on the common attribute 'Department'.
Since all values in the 'Department' column (Mechanical, Electrical, Computer, Civil) are distinct, each tuple in $T_1$ joins with exactly one unique tuple in $T_2$ that shares the same 'Department' value.
This process reconstructs the original 4 rows of the table.
Therefore, $\text{count}(*)$ yields the total number of matched rows, which is 4.

The SQL query consists of an inner query and an outer query.

First, the inner query (SELECT DeptName, COUNT(EmpId) AS Num FROM EMP GROUP BY DeptName) calculates the number of employees in each department. Based on the provided table, the result is:

• AA: 4
• AB: 3
• AC: 3
• AD: 2
• AE: 1

The outer query SELECT AVG(EC.Num) FROM EC ... then calculates the average of the Num column from the result of this inner query (aliased as EC).

The average is calculated as the sum of the counts divided by the number of departments:
$\text{Average} = \frac{4 + 3 + 3 + 2 + 1}{5} = \frac{13}{5} = 2.6$

The SQL query has two conditions joined by AND.

The first condition is ta.goals > ALL (SELECT ... WHERE tb.country = 'Spain'). Since there are no players from 'Spain' in the table, the subquery returns an empty set. The condition > ALL (empty set) always evaluates to TRUE.

The second condition is ta.goals > ANY (SELECT ... WHERE tc.country = 'Germany'). The goals for Germany are {16, 14, 11, 10}. The > ANY condition means ta.goals must be greater than the minimum value in this set, so ta.goals > 10.

The query effectively selects players where goals > 10. Counting from the table, these players are Klose (16), Ronaldo (15), G Muller (14), Fontaine (13), Pele (12), Klinsmann (11), and Kocsis (11), which totals 7 tuples.

The query first calculates the total capacity for each district using a common table expression (CTE) named total.

• Ajmeer: 20
• Bikaner: 10 + 10 + 20 = 40
• Churu: 10 + 20 = 30
• Dungargarh: 10
  Next, a second CTE total_avg computes the average of these summed capacities: $(20 + 40 + 30 + 10) / 4 = 25$.
  The final SELECT statement returns the names of districts where the total capacity is greater than or equal to the average capacity (25). The districts meeting this condition are Bikaner (40) and Churu (30). Thus, the query returns 2 tuples.

The query iterates through each book $B$ and calculates the number of books $T$ with a strictly higher price: $C_B = \text{count}(\{T \mid T.\text{price} > B.\text{price}\})$.
The condition $C_B < 5$ limits the result to books where there are fewer than 5 books more expensive than $B$, meaning $C_B \in \{0, 1, 2, 3, 4\}$.
A value of $C_B = 0$ corresponds to the most expensive book, $C_B = 1$ to the second most expensive, and so on, up to $C_B = 4$ for the fifth most expensive book.
Since the query retrieves all such books, it lists the titles of the five most expensive books.

The inner subquery SELECT P.pid FROM Parts P WHERE P.color <> 'blue' retrieves all part IDs ($pid$) that are not blue.
The middle subquery SELECT C.sid FROM Catalog C WHERE C.pid NOT IN ... then identifies suppliers who have supplied at least one blue part, as these are the catalog entries where the $pid$ is not in the set of non-blue parts.
The outer query SELECT S.name FROM Suppliers S WHERE S.sid NOT IN ... excludes these suppliers, leaving only those who have never supplied a blue part.
Thus, the query effectively identifies suppliers who have supplied only non-blue parts.
This logic aligns with the descriptions in both options provided.

[CORRECT_OPTION: A, C]

The SELECT operation in SQL corresponds to the projection operation ($\pi$) in relational algebra, as both are used to select specific columns from a table. However, there is a key difference. The standard projection operation in relational algebra implicitly removes duplicate tuples from the result. In contrast, the SQL SELECT statement, by default, retains all duplicate rows. To achieve the duplicate-eliminating behavior of relational algebra's projection, one must use SELECT DISTINCT in SQL.

The goal is to select addresses of theaters whose capacity is the maximum of all capacities. The subquery (select P2.capacity from Cinema P2) returns a list of all capacity values from the Cinema table. The ALL keyword is used to compare a value to every value in a list. The condition P1.capacity >= ALL (...) will only be true if P1.capacity is greater than or equal to every single capacity value returned by the subquery. This is logically equivalent to finding the rows where the capacity is the maximum.