Databases & SQL quiz and interview questions
SQL is asked in nearly every backend interview and database exam. This quiz covers keys, joins, aggregates, NULL traps, normalization, indexes and transactions — including queries where you have to work out the result.
Databases & SQL: key concepts
- Keys
- A primary key uniquely identifies each row and can't be NULL. A foreign key points to a primary key in another table and keeps related data consistent.
- Joins
- INNER JOIN returns only matching rows. LEFT JOIN returns every row from the left table, with NULLs where there's no match.
- GROUP BY and HAVING
- WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregates like COUNT(*) > 5.
- NULL
- NULL means unknown. Compare with IS NULL / IS NOT NULL — = NULL is never true. COUNT(column) skips NULLs, COUNT(*) doesn't.
- Normalization
- 1NF: atomic values. 2NF: no partial dependency on part of a composite key. 3NF: no transitive dependency between non-key columns.
- Indexes and transactions
- Indexes speed up reads but slow down writes. A transaction is ACID: all-or-nothing, consistent, isolated and durable.
All 44 Databases & SQL questions with answers
Try each one first, then open the answer. Making a class test? Use the test maker.
Which is true for a primary key?
- A. It can contain NULL
- B. It must be unique and not NULL
- C. A table can have many primary keys
- D. It must be a number
Show answer
B. It must be unique and not NULL — A primary key identifies each row, so it must be unique and NOT NULL. A table has one primary key (which may span several columns).
What is a foreign key for?
- A. Speeding up queries
- B. Linking a row to a row in another table and keeping that link valid
- C. Encrypting data
- D. Sorting results
Show answer
B. Linking a row to a row in another table and keeping that link valid — Orders.CustomerId references Customers.Id, so the database rejects orders for customers that don't exist.
The Orders table has amounts 100, NULL and 50. What does this return?
SELECT COUNT(amount) FROM Orders;- A. 3
- B. 2
- C. 150
- D. NULL
Show answer
B. 2 — COUNT(column) counts only non-NULL values, so it returns 2. COUNT(*) would return 3.
What is the difference between WHERE and HAVING?
- A. None
- B. WHERE filters rows before grouping; HAVING filters groups after GROUP BY
- C. HAVING is faster
- D. WHERE works only with numbers
Show answer
B. WHERE filters rows before grouping; HAVING filters groups after GROUP BY — Use WHERE for row conditions and HAVING for conditions on aggregates, e.g. HAVING COUNT(*) > 5.
Customers: A, B, C. Orders: two for A, one for B, none for C. How many rows does this return?
SELECT c.name, o.id FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.id;- A. 3
- B. 4
- C. 2
- D. 6
Show answer
B. 4 — LEFT JOIN keeps every customer: A appears twice, B once, and C once with a NULL order — 4 rows. An INNER JOIN would return 3.
A table is in 3NF when it is in 2NF and…
- A. Has no NULLs
- B. Has no transitive dependencies (non-key columns depend only on the key)
- C. Has an index on every column
- D. Uses only integers
Show answer
B. Has no transitive dependencies (non-key columns depend only on the key) — If Employee stores DeptId and DeptName, DeptName depends on DeptId, not the employee key — move it to a Departments table.
What is the trade-off of adding an index?
- A. None
- B. Faster reads, but slower inserts/updates and more storage
- C. Slower reads and faster writes
- D. It removes duplicate rows
Show answer
B. Faster reads, but slower inserts/updates and more storage — Every insert, update and delete must also update the index. Index the columns you filter and join on, not everything.
Why does SQL Server reject this query?
SELECT name, COUNT(*) FROM Employees;- A. COUNT needs a column name
- B. name must be in a GROUP BY clause (or inside an aggregate)
- C. Missing WHERE
- D. Employees is a reserved word
Show answer
B. name must be in a GROUP BY clause (or inside an aggregate) — When you mix aggregates and plain columns, every plain column must be grouped: ... GROUP BY name.
In ACID, what does atomicity mean?
- A. Data is encrypted
- B. A transaction happens completely or not at all
- C. Queries run in parallel
- D. Each table has one key
Show answer
B. A transaction happens completely or not at all — Moving money between accounts: the debit and credit both commit or both roll back — never half.
Some users have no email, but this returns zero rows. Why?
SELECT * FROM Users WHERE email = NULL;- A. Users is empty
- B. = NULL is never true; use IS NULL
- C. email must be indexed
- D. Needs quotes around NULL
Show answer
B. = NULL is never true; use IS NULL — Comparing anything to NULL with = gives UNKNOWN, not TRUE. Write WHERE email IS NULL.
Which isolation level is the lowest that prevents dirty reads?
- A. READ UNCOMMITTED
- B. READ COMMITTED
- C. SERIALIZABLE only
- D. None of them
Show answer
B. READ COMMITTED — READ COMMITTED (SQL Server's default) only reads committed data. READ UNCOMMITTED allows dirty reads; higher levels also prevent non-repeatable and phantom reads.
How many clustered indexes can a table have?
- A. Unlimited
- B. One
- C. One per column
- D. Two
Show answer
B. One — A clustered index defines how the rows themselves are stored and ordered, so there can be only one. Non-clustered indexes are separate structures.
In SQL Server, what does this return?
SELECT 10 / 3;- A. 3.33
- B. 3
- C. 4
- D. Error
Show answer
B. 3 — Both values are integers, so SQL Server does integer division. Use 10 / 3.0 for a decimal result.
What is the correct way to prevent SQL injection?
- A. Escape quotes manually
- B. Use parameterized queries / prepared statements
- C. Hide error messages
- D. Use stored procedures with string concatenation
Show answer
B. Use parameterized queries / prepared statements — Parameters send user input as data, never as SQL code. ORMs like EF Core parameterize for you; string concatenation is the danger.
What does an RDBMS add on top of a plain DBMS?
- A. Nothing
- B. Data stored in related tables with keys and constraints that enforce the relationships
- C. Only faster storage
- D. File-based storage
Show answer
B. Data stored in related tables with keys and constraints that enforce the relationships — SQL Server, MySQL and PostgreSQL are relational: tables, primary/foreign keys and integrity rules.
What is the difference between a primary key and a unique key?
- A. None
- B. A table has one primary key (no NULLs); it can have several unique keys, and a unique key can allow NULL
- C. Unique keys can't be indexed
- D. Primary keys allow duplicates
Show answer
B. A table has one primary key (no NULLs); it can have several unique keys, and a unique key can allow NULL — In SQL Server a unique constraint allows one NULL. Both are backed by an index.
Which constraint ensures Price is always greater than zero?
- A. UNIQUE
- B. CHECK (Price > 0)
- C. DEFAULT
- D. FOREIGN KEY
Show answer
B. CHECK (Price > 0) — CHECK constraints reject rows that break a rule, no matter which app inserts them.
What is a trigger?
- A. A scheduled job
- B. Code that runs automatically when rows are inserted, updated or deleted in a table
- C. A type of index
- D. A stored view
Show answer
B. Code that runs automatically when rows are inserted, updated or deleted in a table — AFTER and INSTEAD OF triggers are used for auditing or complex rules — use sparingly, they hide logic.
What is a view?
- A. A copy of a table
- B. A saved SELECT query you can query like a table
- C. A backup
- D. An index
Show answer
B. A saved SELECT query you can query like a table — Views simplify complex joins and can expose only some columns. A normal view stores no data itself.
Employees salaries: 3000, 5000, 7000. What does this return?
SELECT COUNT(*) FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);- A. 0
- B. 1
- C. 2
- D. 3
Show answer
B. 1 — The subquery computes the average, 5000. Only 7000 is greater, so the count is 1.
What does an IDENTITY(1,1) column do?
- A. Stores the user's identity
- B. Generates numbers automatically: 1, 2, 3… for each new row
- C. Encrypts the value
- D. Makes the column unique and nullable
Show answer
B. Generates numbers automatically: 1, 2, 3… for each new row — IDENTITY(seed, increment) is SQL Server's auto-increment. Use SCOPE_IDENTITY() to get the new value.
What is a self join?
- A. A join without ON
- B. Joining a table to itself, e.g. employees to their managers in the same table
- C. A join on the primary key only
- D. A join inside a view
Show answer
B. Joining a table to itself, e.g. employees to their managers in the same table — FROM Employees e JOIN Employees m ON e.ManagerId = m.Id lists each employee with their manager.
Table A has 3 rows and table B has 4 rows. How many rows does this return?
SELECT * FROM A CROSS JOIN B;- A. 7
- B. 4
- C. 12
- D. 3
Show answer
C. 12 — A cross join pairs every row of A with every row of B: 3 × 4 = 12.
What does a FULL OUTER JOIN return?
- A. Only matching rows
- B. All rows from both tables, with NULLs where there's no match
- C. Only the left table
- D. Only unmatched rows
Show answer
B. All rows from both tables, with NULLs where there's no match — It combines LEFT and RIGHT joins — useful for reconciling two lists.
Table X has values 1, 2, 2 and table Y has 2, 3. How many rows does this return?
SELECT v FROM X UNION SELECT v FROM Y;- A. 5
- B. 3
- C. 2
- D. 4
Show answer
B. 3 — UNION removes duplicates: 1, 2, 3. UNION ALL would return all 5 rows.
Which is fastest for removing all rows but keeping the table?
- A. DELETE FROM T
- B. TRUNCATE TABLE T
- C. DROP TABLE T
- D. UPDATE T SET … = NULL
Show answer
B. TRUNCATE TABLE T — TRUNCATE deallocates pages with minimal logging and resets IDENTITY. DELETE logs each row; DROP removes the table itself.
Which is true about DELETE vs TRUNCATE?
- A. Both accept WHERE
- B. DELETE can use WHERE and fires triggers; TRUNCATE removes all rows and doesn't fire DELETE triggers
- C. TRUNCATE can't be rolled back in any transaction
- D. DELETE resets IDENTITY
Show answer
B. DELETE can use WHERE and fires triggers; TRUNCATE removes all rows and doesn't fire DELETE triggers — In SQL Server both can be rolled back inside a transaction, but only DELETE can target specific rows.
What is a stored procedure?
- A. A table
- B. Saved, named SQL code with parameters that you can execute, e.g. EXEC GetOrders @CustomerId = 5
- C. A trigger
- D. A backup plan
Show answer
B. Saved, named SQL code with parameters that you can execute, e.g. EXEC GetOrders @CustomerId = 5 — Procedures centralise logic, can be granted permissions separately and reuse cached plans.
How does a user-defined function differ from a stored procedure?
- A. No difference
- B. A function returns a value or table and can be used inside SELECT; it can't change data
- C. Functions can run INSERTs
- D. Procedures can be used in SELECT
Show answer
B. A function returns a value or table and can be used inside SELECT; it can't change data — SELECT dbo.GetTax(Price) works; you can't call a procedure inside SELECT, and functions can't modify tables.
What is a CTE (WITH clause)?
- A. A permanent table
- B. A named, temporary result set for one statement — great for readability and recursive queries
- C. An index
- D. A trigger
Show answer
B. A named, temporary result set for one statement — great for readability and recursive queries — WITH Recent AS (SELECT ...) SELECT * FROM Recent; recursive CTEs walk hierarchies like org charts.
What is a difference between a #temp table and a table variable (@t)?
- A. None
- B. #temp tables can have statistics and indexes and suit larger data; table variables suit small sets
- C. Table variables are stored on disk only
- D. #temp tables can't be joined
Show answer
B. #temp tables can have statistics and indexes and suit larger data; table variables suit small sets — Both live in tempdb. The optimizer has better estimates for #temp tables, which matters for bigger row counts.
What is a non-clustered index?
- A. The table's physical order
- B. A separate structure with key values and pointers to the rows — a table can have many
- C. A unique constraint only
- D. An index on views only
Show answer
B. A separate structure with key values and pointers to the rows — a table can have many — Like a book's index: it points to rows. A table has at most one clustered index but many non-clustered ones.
An index exists on (LastName, FirstName). Which filter can use it for a seek?
- A. WHERE FirstName = 'Ali'
- B. WHERE LastName = 'Khan'
- C. WHERE FirstName LIKE '%a'
- D. None of them
Show answer
B. WHERE LastName = 'Khan' — Composite indexes are sorted by the first column first. A filter on only the second column can't seek.
When is EXISTS often better than IN?
- A. Never
- B. With a correlated subquery over large tables — EXISTS stops at the first match
- C. For constant lists like IN (1, 2, 3)
- D. Only in MySQL
Show answer
B. With a correlated subquery over large tables — EXISTS stops at the first match — Modern optimizers often treat them similarly, but EXISTS also avoids the NOT IN + NULL trap.
Orders.CustomerId contains a NULL. Why does this return no rows?
SELECT * FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders);- A. Customers is empty
- B. NOT IN with a NULL in the list is never true — use NOT EXISTS
- C. IN needs DISTINCT
- D. Syntax error
Show answer
B. NOT IN with a NULL in the list is never true — use NOT EXISTS — x NOT IN (1, NULL) means x <> 1 AND x <> NULL, and x <> NULL is UNKNOWN. NOT EXISTS handles NULLs correctly.
Scores 90, 90, 80. What ranks do the three rows get?
SELECT Score, RANK() OVER (ORDER BY Score DESC) AS r FROM Results;- A. 1, 2, 3
- B. 1, 1, 2
- C. 1, 1, 3
- D. 1, 2, 2
Show answer
C. 1, 1, 3 — RANK gives ties the same rank and skips the next numbers: 1, 1, 3. DENSE_RANK would give 1, 1, 2.
What does ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) give you?
- A. The total row count
- B. A running number per customer, newest order = 1 — useful for "latest order per customer"
- C. Random numbers
- D. Duplicate removal
Show answer
B. A running number per customer, newest order = 1 — useful for "latest order per customer" — Filter WHERE rn = 1 in an outer query to keep one row per customer.
What does this return?
SELECT COALESCE(NULL, NULL, 'N/A', 'X');- A. NULL
- B. N/A
- C. X
- D. Error
Show answer
B. N/A — COALESCE returns the first non-NULL argument.
Which pattern finds names starting with 'Al'?
- A. LIKE '%Al'
- B. LIKE 'Al%'
- C. LIKE '_Al'
- D. = 'Al*'
Show answer
B. LIKE 'Al%' — % matches any number of characters; _ matches exactly one. 'Al%' = starts with Al.
How do you return rows 21–30 in SQL Server?
- A. TOP 30 SKIP 20
- B. ORDER BY … OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
- C. LIMIT 20, 10
- D. ROWNUM BETWEEN 21 AND 30
Show answer
B. ORDER BY … OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY — OFFSET/FETCH requires an ORDER BY. LIMIT is MySQL/PostgreSQL syntax.
Which type should store customer names in many languages (Urdu, Arabic, Chinese)?
- A. VARCHAR
- B. NVARCHAR
- C. CHAR
- D. TEXT only
Show answer
B. NVARCHAR — NVARCHAR stores Unicode. CHAR is fixed-length; VARCHAR uses a single-byte code page (unless a UTF-8 collation is set).
What is a deadlock?
- A. A slow query
- B. Two transactions each holding a lock the other needs, so SQL Server kills one
- C. A full disk
- D. A locked user account
Show answer
B. Two transactions each holding a lock the other needs, so SQL Server kills one — Access tables in the same order, keep transactions short and add the right indexes to reduce deadlocks.
What does ROLLBACK do in a transaction?
- A. Saves changes
- B. Undoes all changes made since BEGIN TRANSACTION
- C. Restarts the server
- D. Deletes the table
Show answer
B. Undoes all changes made since BEGIN TRANSACTION — BEGIN TRAN … COMMIT makes changes permanent; ROLLBACK reverts them if something fails.
What is denormalization?
- A. A bug
- B. Deliberately duplicating some data to make reads faster (e.g. storing OrderTotal)
- C. Removing indexes
- D. Converting to 3NF
Show answer
B. Deliberately duplicating some data to make reads faster (e.g. storing OrderTotal) — It trades extra storage and update work for fewer joins — common in reporting tables.
More subjects
Need software for your business instead? I'm an AI automation & full-stack developer — see what I build.