Sql Practice Questions

Both queries use an implicit inner join FROM account A, account B. For the customer(s) with the maximum balance, the condition A.balance < B.balance (and A.balance <= B.balance for Query 1) is never true for any row in $B$. Consequently, the inner join excludes these top-ranked customers entirely from the result set, making neither query a correct implementation of the specification (Statement 4 is correct).

Query 1 and Query 2 produce the same row set (the set of non-maximum balance customers) only when the counts calculated happen to align based on the database data distribution (e.g., if there are no ties, though they effectively exclude the maximums). Thus, Statement 1 is correct as they produce the same output for some, but not all, databases.

To optimally execute the query, we perform the following steps:

1. Filter Cars relation:
   • Identify 'red' cars (cid 102, 104) by scanning all 4 tuples in Cars: $4$ comparisons.
   • Identify 'green' cars (cid 103) by scanning all 4 tuples in Cars: $4$ comparisons.
2. Join Reserves with filtered Cars:
   • For 'red' cars: Join Reserves (10 tuples) with the 2 identified 'red' cars using a nested loop: $10 \times 2 = 20$ comparisons. Result set 1: $\{22, 31, 64\}$.
   • For 'green' cars: Join Reserves (10 tuples) with the 1 identified 'green' car using a nested loop: $10 \times 1 = 10$ comparisons. Result set 2: $\{22, 31, 74\}$.
3. Perform Intersection:
   • Intersect the resulting did sets $\{22, 31, 64\}$ and $\{22, 31, 74\}$ using a nested loop: $3 \times 3 = 9$ comparisons.
4. Total Comparisons ($n$):
   • $n = 4 + 4 + 20 + 10 + 9 = 47$.
   • Since $47$ lies in the range $44-48$, option B is correct.

The provided SQL query uses the not unique condition to filter suppliers. The subquery effectively retrieves the supplierid for every item supplied by a specific supplier (STMP.supplierid) that is present in the 'Nagpur' warehouse. If a supplier provides $N$ distinct items to the 'Nagpur' warehouse, the subquery generates a list of $N$ rows, each containing that supplierid. The not unique clause evaluates to true if the number of rows returned is greater than 1, meaning the subquery result contains duplicates or multiple entries. Consequently, the query selects only those suppliers who supply $N > 1$ items to the 'Nagpur' warehouse.

The SQL query select hostel from student natural join hobby where marks >= 75 and roll between 2000 and 3000 performs a natural join on tables student and hobby, filtering for marks >= 75 and 2000 <= roll <= 3000. The qualifying students are those with roll values 2369, 2581, 2643, 2872, 2926, and 2959. Joining these with the hobby table results in:

• Roll 2369: 1 hobby (Hostel 7)
• Roll 2581: 1 hobby (Hostel 6)
• Roll 2643: 2 hobbies (Hostel 5, 5)
• Roll 2872: 1 hobby (Hostel 5)
• Roll 2926: 1 hobby (Hostel 5)
• Roll 2959: 1 hobby (Hostel 7)
  The SQL query outputs all these rows, totaling 7.

The relational algebra expression $S' = \Pi_{\text{hostel}} (\dots)$ performs a natural join under the same filtering conditions, resulting in the same join set: $\{7, 6, 5, 5, 5, 5, 7\}$. However, the relational algebra projection operator $\Pi$ is defined by set semantics and removes duplicate tuples. Thus, the resulting set is $\{5, 6, 7\}$, which contains 3 unique tuples. The difference between the number of rows in the SQL output (7) and the number of tuples in $S'$ (3) is $7 - 3 = 4$.

The subquery count(*) from book as T where T.price > B.price calculates the number of books ($N$) that are strictly more expensive than the current book $B$. The condition $N < 5$ selects books where the number of more expensive books is $0, 1, 2, 3,$ or $4$.

• If $N=0$, the book is the 1st most expensive.
• If $N=1$, the book is the 2nd most expensive, and so on, up to $N=4$, which is the 5th most expensive book.
  Thus, the query effectively returns the titles of the five most expensive books in the relation.

The initial values in the $Marks$ column are $\{10, 20, 30, NULL\}$.
After executing Update T1 set marks = marks + 5, the values become $\{15, 25, 35, NULL\}$, since any arithmetic operation involving $NULL$ in SQL results in $NULL$.
The avg() aggregate function in SQL automatically ignores $NULL$ values when calculating the mean.
Therefore, the function computes the average of the three non-$NULL$ values: $\{15, 25, 35\}$.
The average is calculated as $\frac{15 + 25 + 35}{3} = \frac{75}{3} = 25$.

The query Select * from student, department performs a Cartesian product because no join condition is specified in a WHERE clause.
The number of rows in the resulting table is the product of the number of rows in each table: $3 \times 2 = 6$ rows.
The number of columns in the resulting table is the sum of the columns in each table: $3 \text{ (from student)} + 2 \text{ (from department)} = 5$ columns.
Thus, the result contains 6 rows and 5 columns.

The where sex = 'M' clause filters the Employee relation to include only male employees. The group by deptName then organizes these male employees into departments, and the having avg(salary) computes the average salary specifically for the male employees within each department. The subquery (select avg (salary) from Employee) calculates the average salary of all employees across the entire company. Thus, the query returns departments where the average salary of male employees is greater than the company-wide average salary.

Statement i: update Student set Dept_id = Null where Roll_no = 1 succeeds because foreign keys, unless explicitly constrained as NOT NULL, allow NULL values to represent the absence of a relationship between the Student and Department tables.

Statement ii: update Department set Dept_id = Null where Dept_id = 1 fails because Dept_id is the primary key of the Department table, and primary keys must inherently be unique and non-null. Attempting to set a primary key to NULL violates standard relational database integrity constraints.

Thus, statement i succeeds while statement ii fails.

The natural join is performed on the common attribute $rollno$, which is the primary key of the $Students$ table. Each of the 8 tuples in the $Enroll$ table contains a single $rollno$ value. Since $rollno$ is the primary key in $Students$, each $rollno$ in the $Enroll$ table matches exactly one tuple in the $Students$ table. Consequently, the natural join $(Students * Enroll)$ will consistently result in 8 tuples, as each tuple in $Enroll$ maps to a unique student record. Thus, the maximum and minimum number of tuples are both 8.

The SQL query SELECT DISTINCT a1, ..., an FROM r1, ..., rm WHERE P translates to relational algebra operations as follows:

1. The FROM clause creates the Cartesian product of all relations, denoted as $r_1 \times r_2 \times \dots \times r_m$.
2. The WHERE clause applies the selection predicate $P$ (represented as $\rho$ in the options) to this Cartesian product, written as $\sigma_{\rho}(r_1 \times r_2 \times \dots \times r_m)$.
3. The SELECT DISTINCT clause performs a projection of the attributes $a_1, a_2, \dots, a_n$, which is $\Pi_{a_1, a_2, \dots, a_n}$.
4. Combining these gives the expression $\Pi_{a_1, a_2, \dots, a_n}(\sigma_{\rho}(r_1 \times r_2 \times \dots \times r_m))$.

This sequence of operations corresponds directly to option A.

The query performs an inner join between the $Students$, $Courses$, and $Grades$ tables based on their common attributes ($Roll\_number$ and $Course\_number$). The condition $Courses.Instructor = 'Korth'$ limits the result set to courses taught by Korth, and $Grades.Grade = 'A'$ filters these results to only include instances where a student received an 'A'. Because the query uses a standard join rather than universal quantification or aggregation (like GROUP BY with HAVING count(*)), it returns a row for every individual occurrence of a student receiving an 'A' in a course taught by Korth. The $DISTINCT$ keyword ensures each student's name appears only once. Consequently, the query identifies students who have earned an 'A' in at least one course taught by Korth.

The table geq contains tuples $(ib, ub)$ such that $ib \geq ub$. The foreign key definition foreign key (ub) references geq (referencing the primary key $ib$) implies that for any tuple $(y, w)$ in the table, $y$ must also exist as an $ib$ in some tuple. When a tuple $(x, y)$ is deleted, the on delete cascade constraint mandates that any tuple $(y, w)$ referencing $(x, y)$ (where $y$ matches the $ib$ of the referenced tuple) must also be deleted. Because $(x, y) \in geq$, we have $x \geq y$, and because $(y, w) \in geq$, we have $y \geq w$. By transitivity, $x \geq y \geq w$, which implies $w \leq x$. Consequently, it is possible that $w < x$.

Under the provided rule, any comparison involving $NULL$ evaluates to $False$. For option C:
If $x$ is $NULL$, the expression $x \neq 5$ evaluates to $False$ because it is a comparison with $NULL$.
However, the expression $\text{not}(x=5)$ evaluates as: $x=5$ is $False$ (since it is a comparison with $NULL$), and $\text{not}(False)$ evaluates to $True$.
Since the two expressions yield different results ($False \neq True$) for $NULL$ inputs, they are not equivalent.
In contrast, pairs A and B both evaluate to $False$ when $x$ is $NULL$, maintaining equivalence.

The query SELECT DISTINCT w, x FROM r, s calculates the projection $\pi_{w, x}(r \times s)$ and removes duplicate tuples.
The Cartesian product $r \times s$ produces $|r| \times |s|$ tuples, essentially duplicating each tuple in $r$ exactly $|s|$ times.
If $s$ is empty, the result is an empty set $\emptyset$, which is not $r$, so $s$ must be non-empty to ensure the projection contains all tuples from $r$.
With $s$ non-empty, the DISTINCT operator collapses the $|s|$ copies of each tuple in $r$ into a single instance.
For the resulting set of unique tuples to be identical to $r$, the original relation $r$ must not contain any duplicates.
Thus, the condition is that $r$ has no duplicates and $s$ is non-empty.

Option A is false because standard SELECT statements return all rows; one must explicitly use SELECT DISTINCT to remove duplicates.
Option B is false because indexes are merely optional structures for performance optimization, not functional requirements for query execution.
Option C is false because relational database standards prohibit identical attribute names within a single relation.
Since none of the statements A, B, or C are correct, the only valid choice is D.

Category $X$: non-NULL prices $= \{100\}$. $\texttt{AVG} = 100/1 = 100$. Category $Y$: non-NULL prices $= \{200, 300\}$. $\texttt{AVG} = 500/2 = 250$. Category $Z$: all prices are NULL. $\texttt{AVG}$ of all NULLs $= \text{NULL}$ (no non-NULL values). Note that $\texttt{AVG}$ for $X$ is 100, not 50 - the NULL price for PID 2 is excluded from both sum and count.

The $\texttt{WHERE}$ clause $B > 2$ AND $B < 4$ selects rows where $B = 3$. All 5 tuples satisfy this since all have $B = 3$. $\texttt{SUM(A)} = 1 + 2 + 3 + 4 + 5 = 15$.

$\texttt{SUM(B)}$ returns NULL when all values of $B$ in the group are NULL (since there are no non-NULL values to sum). A group appears in the result of $\texttt{GROUP BY}$ only if it has at least one row, so option B is incorrect. Option A is wrong - $\texttt{SUM}$ of all-zeros returns 0, not NULL. Hence the $\texttt{HAVING SUM(B) IS NULL}$ condition selects groups where every $B$ value is NULL.