In SQL, which clause is used to filter groups based on aggregate function results?
HAVING clause filters groups created by GROUP BY, while WHERE filters individual rows before grouping.
Which data type in SQL is used to store large text documents or binary data?
BLOB (Binary Large Object) stores large binary data. CLOB stores large text. TEXT is limited compared to BLOB/CLOB.
What is the purpose of the DISTINCT keyword in SQL?
DISTINCT eliminates duplicate rows from query results, ensuring each unique combination appears only once.
Which SQL JOIN returns only matching rows from both tables?
INNER JOIN returns only rows where join condition is satisfied in both tables. Other JOINs include non-matching rows.
What will be the result of: SELECT 10 % 3; in SQL?
The modulus operator (%) returns remainder after division. 10 divided by 3 gives remainder 1.
Advertisement
Which aggregate function ignores NULL values by default?
SUM() and AVG() ignore NULL values. COUNT(*) counts all rows including those with NULLs, while COUNT(column) ignores NULLs.
Given a table 'employees' with salary column, which query finds employees earning more than average salary?
Aggregate functions cannot be used directly in WHERE clause. Must use subquery in parentheses to calculate average first.
What is the difference between CROSS JOIN and INNER JOIN?
CROSS JOIN produces all combinations of rows (m×n rows if m and n are table sizes). INNER JOIN filters based on condition.
Which statement correctly uses aliases in SQL?
Correct syntax uses AS keyword with alias without quotes (unless alias has spaces). Option A uses unnecessary quotes, B omits AS.
For a query with transactions table, which index type is most efficient for WHERE clauses checking transaction_date range?
B-Tree indexes are optimal for range queries (BETWEEN, <, >). Hash indexes are for equality, Full-Text for text search, Spatial for geographic data.
What is the output of: SELECT COALESCE(NULL, NULL, 'SQL', 'Database');?
COALESCE returns first non-NULL value. It skips two NULLs and returns 'SQL' as first non-NULL argument.
In a normalized database following 3NF, which anomaly is prevented?
3NF (Third Normal Form) eliminates update, insertion, and deletion anomalies by removing transitive dependencies.
Which of the following queries demonstrates a self-join correctly?
Self-join requires table aliasing (e1, e2) to reference same table twice with join condition. Option B lacks aliases, C uses UNION, D is illogical.
What will be the result of: SELECT COUNT(DISTINCT department) FROM employees WHERE salary > 50000;?
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?
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?
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?
Indexes enable efficient data lookup. Without indexing, DB performs full table scan (O(n)). With B-Tree index, complexity reduces to O(log n).
What is the result of: SELECT CASE WHEN 1=1 THEN 'A' WHEN 2=2 THEN 'B' ELSE 'C' END;?
CASE statement evaluates conditions sequentially and returns value for first TRUE condition. 1=1 is TRUE, so 'A' is returned.
In concurrent transaction scenarios, which isolation level allows dirty reads?
READ UNCOMMITTED is lowest isolation level allowing dirty reads (reading uncommitted data). SERIALIZABLE prevents all anomalies but reduces concurrency.
Which SQL command is used to remove all records from a table without removing the table structure?
TRUNCATE removes all rows but keeps the table structure. DELETE removes rows one by one and can be rolled back. DROP removes the entire table.
