Given a table 'employees' with salary column, which query finds employees earning more than average salary?
A
SELECT * FROM employees WHERE salary > AVG(salary);
B
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
C
SELECT * FROM employees HAVING salary > AVG(salary);
D
SELECT AVG(salary) FROM employees WHERE salary > average;
Correct Answer:
B. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Explanation:
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?
A
CROSS JOIN returns Cartesian product; INNER JOIN requires join condition
B
Both are identical
C
INNER JOIN is always faster
D
CROSS JOIN filters NULL values
Correct Answer:
A. CROSS JOIN returns Cartesian product; INNER JOIN requires join condition
Explanation:
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?
A
SELECT column_name AS 'alias name' FROM table;
B
SELECT column_name alias_name FROM table;
C
SELECT column_name AS alias_name FROM table;
D
SELECT column_name FROM table AS alias;
Correct Answer:
C. SELECT column_name AS alias_name FROM table;
Explanation:
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?
A
Hash Index
B
B-Tree Index
C
Full-Text Index
D
Spatial Index
Correct Answer:
B. B-Tree Index
Explanation:
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');?
A
NULL
B
'SQL'
C
'Database'
D
Error
Explanation:
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?
A
Only insertion anomaly
B
Only deletion anomaly
C
Update, insertion, and deletion anomalies
D
Only update anomaly
Correct Answer:
C. Update, insertion, and deletion anomalies
Explanation:
3NF (Third Normal Form) eliminates update, insertion, and deletion anomalies by removing transitive dependencies.
Which of the following queries demonstrates a self-join correctly?
A
SELECT * FROM employees e1, employees e2 WHERE e1.manager_id = e2.emp_id;
B
SELECT * FROM employees JOIN employees ON emp_id = manager_id;
C
SELECT * FROM employees UNION SELECT * FROM employees;
D
SELECT * FROM employees WHERE emp_id = emp_id;
Correct Answer:
A. SELECT * FROM employees e1, employees e2 WHERE e1.manager_id = e2.emp_id;
Explanation:
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.
In a bank database with accounts and transactions tables, to find accounts with transactions greater than 100,000, which clause must follow GROUP BY?
A
WHERE
B
HAVING
C
ORDER BY
D
LIMIT
Correct Answer:
B. HAVING
Explanation:
HAVING clause filters grouped results after aggregation. WHERE filters before grouping. Use HAVING with aggregate functions.
What will be the result of: SELECT 10/3 in SQL?
Explanation:
Integer division in SQL returns integer result (3). Use CAST or decimal numbers for decimal division result.
A company table has 500K employee records. To find employees with salary > 50,000 efficiently, which strategy is optimal?
A
Create clustered index on salary column
B
Use SELECT * without WHERE clause then filter in application
C
Create non-clustered index on salary for faster filtering
D
Use UNION ALL with multiple queries
Correct Answer:
C. Create non-clustered index on salary for faster filtering
Explanation:
Non-clustered index on salary column enables fast filtering. Clustered index affects primary key ordering. Filtering in application is inefficient.
