Consider a table with columns: id, name, salary. If a row has NULL salary, what will SELECT * FROM table WHERE salary = NULL return?
A
The row with NULL salary
B
No rows (NULL cannot be compared with =)
C
All rows
D
An error message
Correct Answer:
B. No rows (NULL cannot be compared with =)
Explanation:
NULL values cannot be compared using = operator. To check for NULL, use IS NULL or IS NOT NULL.
What will be the output of: SELECT MAX(salary) FROM employees GROUP BY department;
A
The maximum salary across all departments
B
The maximum salary for each department
C
The minimum salary in each department
D
An error because MAX and GROUP BY cannot be used together
Correct Answer:
B. The maximum salary for each department
Explanation:
MAX() with GROUP BY returns the maximum salary value for each distinct department.
What is the difference between UNION and UNION ALL in SQL?
A
UNION includes duplicates; UNION ALL removes duplicates
B
UNION removes duplicates; UNION ALL includes duplicates
C
They are identical
D
UNION ALL works only with numbers; UNION works with all data types
Correct Answer:
B. UNION removes duplicates; UNION ALL includes duplicates
Explanation:
UNION removes duplicate rows from the result, while UNION ALL includes all rows with duplicates.
Which of the following is the correct order of SQL clause execution?
A
SELECT → WHERE → GROUP BY → HAVING → ORDER BY
B
WHERE → SELECT → GROUP BY → HAVING → ORDER BY
C
GROUP BY → WHERE → SELECT → HAVING → ORDER BY
D
SELECT → GROUP BY → WHERE → HAVING → ORDER BY
Correct Answer:
A. SELECT → WHERE → GROUP BY → HAVING → ORDER BY
Explanation:
SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE is applied before grouping, HAVING after.
What will be the result of: SELECT COUNT(DISTINCT department) FROM employees WHERE salary > 50000;?
A
Number of employees with salary > 50000
B
Number of unique departments with at least one employee earning > 50000
C
Total salary of employees in each department
D
Number of rows in employees table
Correct Answer:
B. Number of unique departments with at least one employee earning > 50000
Explanation:
COUNT(DISTINCT column) counts unique values. Query returns count of distinct departments where salary exceeds 50000.
For optimizing a query with multiple JOINs and subqueries, which approach is most effective for 2024 databases?
A
Always use nested subqueries
B
Replace subqueries with JOINs and use EXPLAIN plan analysis
C
Use subqueries in SELECT clause exclusively
D
Avoid using indexes
Correct Answer:
B. Replace subqueries with JOINs and use EXPLAIN plan analysis
Explanation:
Modern SQL optimizers handle JOINs more efficiently than nested subqueries. EXPLAIN analysis identifies bottlenecks and index opportunities.
Which scenario requires using PARTITION BY in window functions instead of GROUP BY?
A
When you need aggregate results only
B
When you need aggregates with original row details preserved
C
When tables have NULL values
D
When sorting alphabetically
Correct Answer:
B. When you need aggregates with original row details preserved
Explanation:
PARTITION BY (window function) retains all rows with aggregate values added, while GROUP BY collapses rows showing only aggregates.
A table has 1M rows. Query A uses WHERE on non-indexed column, Query B uses indexed column in WHERE. Expected performance difference?
A
No significant difference
B
Query B is 10-100x faster due to index usage
C
Query A is always faster
D
Depends only on data types
Correct Answer:
B. Query B is 10-100x faster due to index usage
Explanation:
Indexes enable efficient data lookup. Without indexing, DB performs full table scan (O(n)). With B-Tree index, complexity reduces to O(log n).
In concurrent transaction scenarios, which isolation level allows dirty reads?
A
READ COMMITTED
B
REPEATABLE READ
C
READ UNCOMMITTED
D
SERIALIZABLE
Correct Answer:
C. READ UNCOMMITTED
Explanation:
READ UNCOMMITTED is lowest isolation level allowing dirty reads (reading uncommitted data). SERIALIZABLE prevents all anomalies but reduces concurrency.
For a student result database, to calculate cumulative marks from beginning of year, which window function is appropriate?
A
ROW_NUMBER()
B
SUM() OVER (ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
C
RANK() OVER (PARTITION BY student_id)
D
LAG() function
Correct Answer:
B. SUM() OVER (ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Explanation:
SUM with ROWS BETWEEN clause calculates running/cumulative sum. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows up to current row.
