Database (DBMS) — MCQ Practice Questions & Answers

Question 1

Consider a table with columns: id, name, salary. If a row has NULL salary, what will SELECT * FROM table WHERE salary = NULL return?

Accepted Answer

Correct answer: B. No rows (NULL cannot be compared with =) — NULL values cannot be compared using = operator. To check for NULL, use IS NULL or IS NOT NULL.

Answer

A. The row with NULL salary

Answer

C. All rows

Answer

D. An error message

Question 2

What will be the output of: SELECT MAX(salary) FROM employees GROUP BY department;

Accepted Answer

Correct answer: B. The maximum salary for each department — MAX() with GROUP BY returns the maximum salary value for each distinct department.

Answer

A. The maximum salary across all departments

Answer

C. The minimum salary in each department

Answer

D. An error because MAX and GROUP BY cannot be used together

Question 3

What is the difference between UNION and UNION ALL in SQL?

Accepted Answer

Correct answer: B. UNION removes duplicates; UNION ALL includes duplicates — UNION removes duplicate rows from the result, while UNION ALL includes all rows with duplicates.

Answer

A. UNION includes duplicates; UNION ALL removes duplicates

Answer

C. They are identical

Answer

D. UNION ALL works only with numbers; UNION works with all data types

Question 4

Which of the following is the correct order of SQL clause execution?

Accepted Answer

Correct answer: A. SELECT → WHERE → GROUP BY → HAVING → ORDER BY — SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE is applied before grouping, HAVING after.

Answer

B. WHERE → SELECT → GROUP BY → HAVING → ORDER BY

Answer

C. GROUP BY → WHERE → SELECT → HAVING → ORDER BY

Answer

D. SELECT → GROUP BY → WHERE → HAVING → ORDER BY

Question 5

What will be the result of: SELECT COUNT(DISTINCT department) FROM employees WHERE salary > 50000;?

Accepted Answer

Correct answer: B. Number of unique departments with at least one employee earning > 50000 — COUNT(DISTINCT column) counts unique values. Query returns count of distinct departments where salary exceeds 50000.

Answer

A. Number of employees with salary > 50000

Answer

C. Total salary of employees in each department

Answer

D. Number of rows in employees table

Question 6

For optimizing a query with multiple JOINs and subqueries, which approach is most effective for 2024 databases?

Accepted Answer

Correct answer: B. Replace subqueries with JOINs and use EXPLAIN plan analysis — Modern SQL optimizers handle JOINs more efficiently than nested subqueries. EXPLAIN analysis identifies bottlenecks and index opportunities.

Answer

A. Always use nested subqueries

Answer

C. Use subqueries in SELECT clause exclusively

Answer

D. Avoid using indexes

Question 7

Which scenario requires using PARTITION BY in window functions instead of GROUP BY?

Accepted Answer

Correct answer: B. When you need aggregates with original row details preserved — PARTITION BY (window function) retains all rows with aggregate values added, while GROUP BY collapses rows showing only aggregates.

Answer

A. When you need aggregate results only

Answer

C. When tables have NULL values

Answer

D. When sorting alphabetically

Question 8

A table has 1M rows. Query A uses WHERE on non-indexed column, Query B uses indexed column in WHERE. Expected performance difference?

Accepted Answer

Correct answer: B. Query B is 10-100x faster due to index usage — Indexes enable efficient data lookup. Without indexing, DB performs full table scan (O(n)). With B-Tree index, complexity reduces to O(log n).

Answer

A. No significant difference

Answer

C. Query A is always faster

Answer

D. Depends only on data types

Database (DBMS) - MCQ Practice Questions