Consider a table with columns: id, name, salary. If a row has NULL salary, what will SELECT * FROM table WHERE salary = NULL return?
Answer: B
NULL values cannot be compared using = operator. To check for NULL, use IS NULL or IS NOT NULL.
Q.2Hard
What will be the output of: SELECT MAX(salary) FROM employees GROUP BY department;
Answer: B
MAX() with GROUP BY returns the maximum salary value for each distinct department.
Q.3Hard
What is the difference between UNION and UNION ALL in SQL?
Answer: B
UNION removes duplicate rows from the result, while UNION ALL includes all rows with duplicates.
Q.4Hard
Which of the following is the correct order of SQL clause execution?
Answer: A
SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE is applied before grouping, HAVING after.
Q.5Hard
What will be the result of: SELECT COUNT(DISTINCT department) FROM employees WHERE salary > 50000;?
Answer: B
COUNT(DISTINCT column) counts unique values. Query returns count of distinct departments where salary exceeds 50000.
Advertisement
Q.6Hard
For optimizing a query with multiple JOINs and subqueries, which approach is most effective for 2024 databases?
Answer: B
Modern SQL optimizers handle JOINs more efficiently than nested subqueries. EXPLAIN analysis identifies bottlenecks and index opportunities.
Q.7Hard
Which scenario requires using PARTITION BY in window functions instead of GROUP BY?
Answer: B
PARTITION BY (window function) retains all rows with aggregate values added, while GROUP BY collapses rows showing only aggregates.
Q.8Hard
A table has 1M rows. Query A uses WHERE on non-indexed column, Query B uses indexed column in WHERE. Expected performance difference?
Answer: B
Indexes enable efficient data lookup. Without indexing, DB performs full table scan (O(n)). With B-Tree index, complexity reduces to O(log n).
Q.9Hard
In concurrent transaction scenarios, which isolation level allows dirty reads?
Answer: C
READ UNCOMMITTED is lowest isolation level allowing dirty reads (reading uncommitted data). SERIALIZABLE prevents all anomalies but reduces concurrency.
Q.10Hard
For a student result database, to calculate cumulative marks from beginning of year, which window function is appropriate?
Answer: B
SUM with ROWS BETWEEN clause calculates running/cumulative sum. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows up to current row.
Q.11Hard
For optimizing a complex query joining 5 tables with WHERE conditions, what is the recommended approach?
Answer: B
Proper indexing on join and filter columns is crucial. Analyzing execution plan identifies bottlenecks. Application-level combining is inefficient. VIEWs don't inherently optimize.
Q.12Hard
In a manufacturing database, to find products with sales in ALL regions, which approach is correct?
Answer: A
Counts distinct regions per product and compares to total regions. Option B doesn't ensure ALL regions. Option C only checks one region. Option D is incomplete.
