Sql Practice Questions

When all values are NULL: $\texttt{SUM(X)}$ ignores NULLs - with no non-NULL values, the result is NULL (not 0). $\texttt{COUNT(X)}$ counts non-NULL values of $X$ - result is 0. $\texttt{COUNT(*)}$ counts all rows regardless of NULLs - result is 1 (the table has one row). This is a classic GATE question testing the distinction between these three aggregate behaviours.

COUNT(column) ignores NULL values, COUNT(*) counts all rows.

In SQL execution order: $\texttt{WHERE}$ filters individual rows before grouping occurs, while $\texttt{HAVING}$ filters groups (aggregated results) after $\texttt{GROUP BY}$. Here, $\texttt{HAVING SUM(AMOUNT) > 1000}$ keeps only those regions whose total sales exceed 1000. You cannot use aggregate functions like $\texttt{SUM}$ in a $\texttt{WHERE}$ clause - that is the key distinction.

In SQL, NULL is treated as a valid group key in $\texttt{GROUP BY}$. All rows with $A = \text{NULL}$ are placed in one group (NULLs are considered equal for grouping purposes, even though $\text{NULL} \neq \text{NULL}$ in comparisons). $\texttt{COUNT(*)}$ counts all rows in that NULL group. So the output includes a row with $A = \text{NULL}$ and the corresponding count.

$\texttt{COUNT(*)}$ counts all rows in the group, including those with NULL salary. $\texttt{SUM(SALARY)}$ ignores NULLs - only non-NULL salaries are summed. So an employee with NULL salary contributes 1 to the group's $\texttt{COUNT(*)}$ but contributes nothing to $\texttt{SUM(SALARY)}$. This asymmetry is crucial when interpreting results involving NULLs.

$\texttt{SUM(Y)} = 10 + 20 + 30 = 60$ (NULLs ignored). $\texttt{COUNT(*)} = 4$ (all rows including NULL row). So $\texttt{AVG1} = 60 / 4 = 15$. $\texttt{AVG(Y)}$ ignores NULLs in both numerator and denominator: $60 / 3 = 20$. So $\texttt{AVG1} \neq \texttt{AVG2}$. This is a classic pitfall - manually computing average using $\texttt{SUM/COUNT(*)}$ gives a different result from $\texttt{AVG}$ when NULLs are present.

$\texttt{COUNT(C)}$ in the $\texttt{HAVING}$ clause counts non-NULL values of $C$ within each group. Only groups where at least 2 rows have a non-NULL value of $C$ are included. This is distinct from $\texttt{COUNT(*) > 1}$, which would count all rows regardless of NULLs. Options C and D say the same thing - C is the more precise SQL-standard formulation.

$\texttt{COUNT(*)} = 10$: counts all rows regardless of NULLs. $\texttt{COUNT(B)} = 7$: counts non-NULL values of $B$ (3 NULLs are excluded). $\texttt{COUNT(DISTINCT B)} = 7$: counts distinct non-NULL values of $B$ - since all 7 non-NULL values are distinct, this equals 7. If there were duplicate non-NULL values, $\texttt{COUNT(DISTINCT B)}$ would be less than $\texttt{COUNT(B)}$.

Option B is the correct syntax: $\texttt{GROUP BY CID}$ groups orders per customer, and $\texttt{HAVING AVG(AMOUNT) > 500}$ filters groups where the average (computed over non-NULL amounts) exceeds 500. Option A is invalid - aggregate functions cannot be used in $\texttt{WHERE}$. Option C is a syntax error - $\texttt{WHERE}$ cannot follow $\texttt{GROUP BY}$. Option D is invalid without $\texttt{GROUP BY}$.

$Q_1$: $\texttt{COUNT(*)}$ counts all rows; $\texttt{COUNT(B)}$ counts non-NULL $B$ values. $\texttt{COUNT(*) > COUNT(B)}$ is true iff at least one $B$ is NULL in the group. $Q_2$: $\texttt{MIN(B)}$ ignores NULLs. $\texttt{MIN(B) IS NULL}$ is true iff all $B$ values are NULL (no non-NULL value to compute MIN over). These two queries are NOT equivalent - they detect different conditions. Option A is therefore incorrect. The correct description is option B.

For group $A=2$: rows are $(2, \text{NULL}, 3)$ and $(2, 6, \text{NULL})$. $\texttt{COUNT(B)}$: non-NULL $B$ values are $\{6\}$ → count = 1. $\texttt{COUNT(C)}$: non-NULL $C$ values are $\{3\}$ → count = 1. $\texttt{COUNT(*)}$: all rows in the group = 2. Each $\texttt{COUNT(col)}$ independently ignores NULLs for that specific column.

$\texttt{HAVING COUNT(*) <> COUNT(AMOUNT)}$: $\texttt{COUNT(*)}$ counts all rows; $\texttt{COUNT(AMOUNT)}$ counts non-NULL amounts. They differ iff at least one AMOUNT is NULL in the region - so option B is TRUE. $\texttt{SUM(AMOUNT)}$ ignores NULLs; if all amounts in the region are NULL, $\texttt{SUM}$ returns NULL - so option C is TRUE. A region that passes the $\texttt{HAVING}$ clause may have some non-NULL amounts too, so it need not be the case that ALL are NULL - option A is FALSE. Hence D is correct.

$\texttt{COUNT(DISTINCT GRADE)}$ counts the number of distinct non-NULL values of $\texttt{GRADE}$. The distinct non-NULL grades are $\{A, B\}$ - count is 2. NULL values are ignored by $\texttt{COUNT}$, and $\texttt{DISTINCT}$ does not count NULL as a distinct value in this context.

Option B correctly uses $\texttt{COUNT(DISTINCT DEPT)}$ to count the number of unique departments that satisfy the condition. Option A counts rows (not departments) and may double-count departments. Option C counts rows where salary > 50000. Option D is syntactically invalid - $\texttt{SALARY}$ cannot be used directly in $\texttt{HAVING}$ without an aggregate function.

(P) TRUE: $\texttt{SUM}$ of all NULLs returns NULL (no non-NULL values to sum). (Q) TRUE: $\texttt{COUNT(*)}$ counts rows, never NULL - it returns 0 for an empty table, never NULL. (R) TRUE: $\texttt{AVG}$ computes $\texttt{SUM(col) / COUNT(col)}$ internally, both ignoring NULLs. (S) TRUE: $\texttt{MIN}$ and $\texttt{MAX}$ skip NULLs; if all values are NULL, they return NULL. All four statements are correct.

EXISTS checks if subquery returns any rows.

$\texttt{COUNT(B)}$ counts non-NULL values of $B$ in each group. For $A=2$: tuples are $(2, \text{NULL})$ and $(2, 30)$ - only one non-NULL $B$ value. $\texttt{COUNT(B)} = 1$. For $A=3$: tuples are $(3, \text{NULL})$ and $(3, \text{NULL})$ - no non-NULL $B$ values. $\texttt{COUNT(B)} = 0$.

If a department has exactly one employee with a non-NULL salary, say $s$, then $\texttt{MAX(SALARY)} = s$ and $\texttt{MIN(SALARY)} = s$. The result is $s - s = 0$. The query does not produce NULL or an error - it simply returns 0, indicating no spread in salary within that department.