AbairTechSolutionDIGITAL SOLUTIONS FOR A SMARTER TOMORROW

Menu
HomeLive DemosMobile AppsBusiness SoftwareServicesKnowledge CenterInterview PrepContact

Home › Programming MCQs › SQL

SQL · 40 MCQs with explanations

SQL MCQ Questions and Answers: 40 Practice Questions with Explanations

By AbairTech Solution · Written by Oracle and database developers

Published by AbairTech Solution · Reviewed November 2026 · Editorial policy
How to use this page. Read the short study notes, then try each question before opening Show answer. Every answer has a one-line explanation, so you learn why the other options are wrong. The questions follow standard SQL; where Oracle behaves differently, the question says so.

SQL study notes before you start

SQL is the language every relational database speaks: Oracle, MySQL, PostgreSQL and SQL Server all use it with small differences. Most interview and exam questions test a handful of ideas, and if you understand these, the questions become much easier.

Function What it returns for ties (scores 90, 90, 80)
ROW_NUMBER() 1, 2, 3 – always unique
RANK() 1, 1, 3 – gap after the tie
DENSE_RANK() 1, 1, 2 – no gap
1–10 Basics & NULLs11–20 Joins & subqueries21–30 Transactions & analytics31–40 Design & Oracle specifics

Questions 1–10: SELECT basics, filtering and NULLs

1. Which clause filters groups after GROUP BY has been applied?

  1. WHERE
  2. HAVING
  3. ORDER BY
  4. DISTINCT
Show answer

Answer: B – HAVING. WHERE filters individual rows before grouping; HAVING filters the groups afterwards, so it can use aggregates such as HAVING COUNT(*) > 5.

2. What does WHERE commission = NULL return?

  1. All rows where commission is empty
  2. All rows
  3. No rows, because the comparison evaluates to UNKNOWN
  4. A syntax error
Show answer

Answer: C. Comparing anything with NULL gives UNKNOWN, and WHERE only keeps TRUE rows. Write WHERE commission IS NULL instead.

3. A column has 10 rows, 3 of which are NULL. What does COUNT(column) return?

  1. 7
  2. 10
  3. 3
  4. NULL
Show answer

Answer: A – 7. COUNT(column) ignores NULL values. COUNT(*) counts rows and would return 10.

4. Which LIKE wildcard matches exactly one character?

  1. %
  2. *
  3. ?
  4. _ (underscore)
Show answer

Answer: D – underscore. _ matches one character and % matches zero or more. * and ? are not SQL LIKE wildcards.

5. WHERE salary BETWEEN 1000 AND 2000 returns salaries that are:

  1. Greater than 1000 and less than 2000
  2. From 1000 to 2000, including both values
  3. From 1000 up to but not including 2000
  4. Outside the range 1000–2000
Show answer

Answer: B. BETWEEN is inclusive at both ends; it is shorthand for salary >= 1000 AND salary <= 2000.

6. Which keyword removes duplicate rows from a query result?

  1. DISTINCT
  2. SINGLE
  3. ONLY
  4. GROUP
Show answer

Answer: A – DISTINCT. SELECT DISTINCT city FROM customers returns each city once.

7. In logical processing order, which clause is evaluated first?

  1. SELECT
  2. WHERE
  3. FROM
  4. ORDER BY
Show answer

Answer: C – FROM. The database first identifies the source tables and joins, then applies WHERE, GROUP BY, HAVING, SELECT and finally ORDER BY.

8. Why does SELECT salary*12 AS annual FROM emp WHERE annual > 50000 fail?

  1. Aliases cannot contain lowercase letters
  2. Arithmetic is not allowed in SELECT
  3. AS is not valid syntax
  4. WHERE is processed before SELECT, so the alias does not exist yet
Show answer

Answer: D. Repeat the expression in WHERE (salary*12 > 50000) or use a subquery or CTE. Aliases can be used in ORDER BY, which runs after SELECT.

9. What is the default sort order of ORDER BY?

  1. Descending
  2. Ascending
  3. Random
  4. Insertion order
Show answer

Answer: B – ascending. Add DESC for descending. Without ORDER BY, the database guarantees no order at all.

10. What does COALESCE(NULL, NULL, 'Lahore', 'Karachi') return?

  1. NULL
  2. ‘Karachi’
  3. ‘Lahore’
  4. An error, because it has more than two arguments
Show answer

Answer: C – ‘Lahore’. COALESCE returns the first non-NULL argument and accepts any number of arguments. Oracle’s NVL accepts only two.

Questions 11–20: joins, subqueries and set operators

11. What does a LEFT JOIN return?

  1. Only rows that match in both tables
  2. All rows from the right table
  3. Every combination of rows
  4. All rows from the left table, with NULLs where no right-table row matches
Show answer

Answer: D. LEFT (OUTER) JOIN keeps every left-table row. It is the usual way to find “customers with no orders”: LEFT JOIN orders, then WHERE o.order_id IS NULL.

12. Table A has 5 rows and table B has 4 rows. How many rows does A CROSS JOIN B return?

  1. 9
  2. 20
  3. 5
  4. 4
Show answer

Answer: B – 20. A cross join returns the Cartesian product: every row of A paired with every row of B (5 × 4).

13. Which join returns all rows from both tables, matching where possible?

  1. FULL OUTER JOIN
  2. INNER JOIN
  3. LEFT JOIN
  4. NATURAL JOIN
Show answer

Answer: A. FULL OUTER JOIN combines the results of a left and a right join, filling NULLs on whichever side has no match.

14. A self join is:

  1. A join without a condition
  2. A join between a table and a view
  3. A table joined to itself, using two aliases
  4. A join that removes duplicates
Show answer

Answer: C. The classic example lists each employee with their manager: FROM emp e JOIN emp m ON e.manager_id = m.emp_id.

15. What is the difference between UNION and UNION ALL?

  1. UNION ALL removes duplicates
  2. UNION works only on numeric columns
  3. There is no difference
  4. UNION removes duplicates; UNION ALL keeps them and is usually faster
Show answer

Answer: D. UNION must sort or hash the result to remove duplicates. Use UNION ALL when duplicates are impossible or acceptable.

16. WHERE dept_id = (SELECT dept_id FROM depts WHERE city = 'Lahore') fails with “single-row subquery returns more than one row”. What is the fix?

  1. Use IN instead of =
  2. Add DISTINCT to the outer query
  3. Use HAVING instead of WHERE
  4. Add ORDER BY to the subquery
Show answer

Answer: A – use IN. = expects a single value. IN (or = ANY) accepts a list of values returned by the subquery.

17. What makes a subquery “correlated”?

  1. It is placed in the FROM clause
  2. It refers to a column from the outer query and is evaluated for each outer row
  3. It returns exactly one row
  4. It uses a GROUP BY
Show answer

Answer: B. Example: employees earning more than their department average – the subquery uses the outer row’s dept_id each time.

18. WHERE EXISTS (subquery) is TRUE when:

  1. The subquery returns no rows
  2. The subquery returns exactly one row
  3. The subquery returns at least one row
  4. The subquery returns no NULL values
Show answer

Answer: C. EXISTS only checks whether any row comes back; the selected columns do not matter, which is why SELECT 1 is common inside it.

19. Why can WHERE id NOT IN (SELECT manager_id FROM emp) return no rows at all?

  1. NOT IN is not supported in subqueries
  2. The subquery must use DISTINCT
  3. NOT IN only works with numbers
  4. If the subquery returns any NULL, every NOT IN comparison becomes UNKNOWN
Show answer

Answer: D. This is a common trap. Use NOT EXISTS, or add WHERE manager_id IS NOT NULL to the subquery.

20. Which keyword starts a Common Table Expression (CTE)?

  1. WITH
  2. DEFINE
  3. TEMP
  4. USING
Show answer

Answer: A – WITH. WITH dept_totals AS (SELECT …) SELECT … FROM dept_totals makes long queries easier to read, and recursive CTEs can walk hierarchies.

Questions 21–30: transactions, aggregates and window functions

21. Which command undoes changes that have not yet been committed?

  1. REVOKE
  2. ROLLBACK
  3. UNDO
  4. TRUNCATE
Show answer

Answer: B – ROLLBACK. ROLLBACK discards uncommitted DML. ROLLBACK TO savepoint_name undoes only the work after that savepoint.

22. Which statement removes all rows, keeps the table structure and, in Oracle, cannot be rolled back?

  1. DELETE
  2. DROP
  3. TRUNCATE
  4. MERGE
Show answer

Answer: C – TRUNCATE. TRUNCATE is DDL and commits implicitly. DELETE is DML (can be rolled back and can use WHERE); DROP removes the table itself.

23. In ACID, what does the “I” stand for?

  1. Integrity
  2. Indexing
  3. Isolation
  4. Insertion
Show answer

Answer: C – Isolation. Concurrent transactions should not see each other’s uncommitted work. The others are Atomicity, Consistency and Durability.

24. Which of these is NOT an aggregate function?

  1. SUM
  2. AVG
  3. MAX
  4. UPPER
Show answer

Answer: D – UPPER. UPPER is a single-row function that works on each value. Aggregates return one value for a group of rows.

25. SELECT dept_id, job, COUNT(*) FROM emp GROUP BY dept_id fails. Why?

  1. job is not aggregated and not in GROUP BY
  2. COUNT(*) cannot be used with GROUP BY
  3. GROUP BY must come before FROM
  4. dept_id must be aggregated
Show answer

Answer: A. Every non-aggregated column in SELECT must appear in GROUP BY. Add job to GROUP BY or wrap it in an aggregate.

26. What does ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) give?

  1. The total salary of each department
  2. A unique sequence number within each department, highest salary first
  3. The same number for equal salaries
  4. One row per department
Show answer

Answer: B. Filtering where this number = 1 is a common way to get the top earner per department without losing the other columns.

27. Scores are 95, 95 and 80. What does RANK() give the 80?

  1. 1
  2. 2
  3. 3
  4. NULL
Show answer

Answer: C – 3. RANK leaves a gap after a tie (1, 1, 3). DENSE_RANK would give 2.

28. What does a window function do that GROUP BY does not?

  1. It only works on numbers
  2. It removes duplicate rows
  3. It must be used in WHERE
  4. It calculates across related rows while keeping every row in the result
Show answer

Answer: D. For example, SUM(amount) OVER (ORDER BY sale_date) gives a running total beside each sale row.

29. Which statement inserts new rows and updates existing ones in a single command?

  1. UPSERT ALL
  2. REPLACE
  3. MERGE
  4. ALTER
Show answer

Answer: C – MERGE. MERGE INTO target USING source ON (…) WHEN MATCHED THEN UPDATE … WHEN NOT MATCHED THEN INSERT … is standard SQL and widely used in Oracle data loads.

30. What is a deadlock?

  1. A query that runs out of memory
  2. Two sessions each waiting for a lock held by the other
  3. A table with no primary key
  4. A committed transaction that cannot be undone
Show answer

Answer: B. The database detects the cycle and rolls back one statement (in Oracle, ORA-00060). Locking rows in a consistent order helps prevent it.

Questions 31–40: keys, constraints, design and Oracle specifics

31. Which statement about a primary key is correct?

  1. It must be unique and cannot be NULL
  2. It allows one NULL value
  3. A table can have several primary keys
  4. It must be numeric
Show answer

Answer: A. A table has at most one primary key, which can span several columns (a composite key). Its values must be unique and not NULL.

32. What is the main purpose of a foreign key?

  1. To speed up every query
  2. To encrypt a column
  3. To keep referential integrity between child and parent tables
  4. To allow duplicate primary keys
Show answer

Answer: C. A foreign key stops you inserting an order for a customer that does not exist, or deleting a customer that still has orders (unless ON DELETE rules say otherwise).

33. Which constraint ensures that salary is always greater than zero?

  1. UNIQUE
  2. DEFAULT
  3. NOT NULL
  4. CHECK
Show answer

Answer: D – CHECK. CONSTRAINT chk_sal CHECK (salary > 0). Note that a CHECK condition that evaluates to UNKNOWN (a NULL salary) is allowed, so combine it with NOT NULL if needed.

34. What is a view?

  1. A copy of a table’s data
  2. A stored query that behaves like a virtual table
  3. An index on several columns
  4. A backup of the database
Show answer

Answer: B. A normal view stores no data; it runs its query when used. A materialized view stores the result and is refreshed.

35. What is the trade-off of adding an index?

  1. Faster lookups, but slightly slower INSERT, UPDATE and DELETE and extra storage
  2. Faster DML, but slower queries
  3. Indexes remove the need for primary keys
  4. There is no trade-off
Show answer

Answer: A. Every index must be maintained when data changes, so index the columns you actually filter and join on.

36. First Normal Form (1NF) requires that:

  1. Every table has a foreign key
  2. No column depends on another non-key column
  3. Every column holds a single, atomic value, with no repeating groups
  4. All columns are indexed
Show answer

Answer: C. Storing “Math, Physics” in one column breaks 1NF. Move multi-valued data into a separate child table.

37. Third Normal Form (3NF) removes:

  1. Primary keys
  2. All NULL values
  3. Indexes
  4. Transitive dependencies (non-key columns depending on other non-key columns)
Show answer

Answer: D. If an orders table stores customer_id and customer_city, the city depends on the customer, not the order, so it belongs in the customers table.

38. Which of these is a DCL (Data Control Language) command?

  1. GRANT
  2. COMMIT
  3. CREATE
  4. UPDATE
Show answer

Answer: A – GRANT. GRANT and REVOKE manage privileges. COMMIT is TCL, CREATE is DDL and UPDATE is DML.

39. In Oracle, what is the result of 'abc' || NULL?

  1. NULL
  2. ‘abc’
  3. An error
  4. ‘abcNULL’
Show answer

Answer: B – ‘abc’. Oracle treats NULL as an empty string in concatenation. Standard SQL (and PostgreSQL) would return NULL – a frequent difference in interviews.

40. Which clause returns only the first 10 rows in standard SQL (supported by Oracle 12c and later)?

  1. LIMIT 10 ONLY
  2. TOP 10
  3. FETCH FIRST 10 ROWS ONLY
  4. WHERE ROWS <= 10
Show answer

Answer: C. ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY. MySQL and PostgreSQL also support LIMIT, and SQL Server uses TOP.

What to practise next

If you scored above 32 out of 40, you are ready for most SQL interview rounds. Below that, revisit NULL handling, joins and GROUP BY: they account for most wrong answers. Then move on to the PL/SQL MCQs to practise procedural code, or see all topics on the Programming MCQs hub. Oracle developers can continue with the 100 Oracle APEX MCQs.

Need an Oracle or SQL expert?

AbairTech builds and tunes Oracle databases, PL/SQL back ends and Oracle APEX applications for businesses.

Oracle ServicesMore MCQs