DBMS & SQL · Full Mock 2 – Mock Test

Question 1 NULL Beginner Practice Query executed

How many products are "not out of stock" by this query?

products

id	name	category	price	stock	launched
1	Pen	Stationery	20.00	500	2024-01-10
2	Notebook	Stationery	60.00	120	2024-03-05
3	Backpack	Bags	899.00	40	2025-06-01
4	Laptop Sleeve	Bags	650.00	0	2025-08-15
5	Mouse	Electronics	499.00	75	2025-02-20
6	Keyboard	Electronics	1299.00	NULL	2026-01-12
7	Marker	Stationery	35.00	300	NULL

SELECT COUNT(*) AS n FROM products WHERE stock <> 0;

n
4

n
6

n
7

n
5

Question 2 CASE Intermediate Practice Query executed

What does this CASE expression return?

products

id	name	category	price	stock	launched
1	Pen	Stationery	20.00	500	2024-01-10
2	Notebook	Stationery	60.00	120	2024-03-05
3	Backpack	Bags	899.00	40	2025-06-01
4	Laptop Sleeve	Bags	650.00	0	2025-08-15
5	Mouse	Electronics	499.00	75	2025-02-20
6	Keyboard	Electronics	1299.00	NULL	2026-01-12
7	Marker	Stationery	35.00	300	NULL

SELECT name,
  CASE
    WHEN stock IS NULL THEN 'unknown'
    WHEN stock = 0 THEN 'out'
    WHEN stock < 100 THEN 'low'
    ELSE 'ok'
  END AS status
FROM products
WHERE category = 'Bags' OR id = 6
ORDER BY id;

name	status
Backpack	low
Laptop Sleeve	low
Keyboard	ok

name	status
Backpack	low
Laptop Sleeve	out
Keyboard	unknown

name	status
Backpack	low
Laptop Sleeve	out
Keyboard	NULL

name	status
Backpack	ok
Laptop Sleeve	out
Keyboard	unknown

Question 3 LEFT JOIN Beginner Practice Query executed

How many rows does this query return?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT COUNT(*) AS total
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

total
3

total
4

total
6

total
5

Question 4 Self join Intermediate Practice Query executed

manager_id refers to another employee. What does this self join return?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

employee	manager
Ravi	Asha
Meena	Asha
John	Asha
Zoya	John

employee	manager
Asha	NULL
Ravi	Asha
Meena	Asha
John	Asha
Zoya	John

employee	manager
Asha	Ravi
Asha	Meena
Asha	John
John	Zoya

employee	manager
Ravi	Asha
Meena	Asha
John	Asha

Question 5 COUNT Beginner Practice Query executed

What does this query return?

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meena	Pune
4	John	Mumbai
5	Zoya	NULL

orders

id	customer_id	amount	status	order_date
101	1	500	paid	2026-01-05
102	1	1500	paid	2026-01-20
103	2	800	cancelled	2026-02-02
104	3	NULL	pending	2026-02-10
105	3	1200	paid	2026-02-15
106	4	700	paid	2026-03-01
107	1	300	refunded	2026-03-03
108	NULL	400	paid	2026-03-09

SELECT COUNT(*), COUNT(amount), COUNT(customer_id)
FROM orders;

COUNT(*)	COUNT(amount)	COUNT(customer_id)
8	7	7

COUNT(*)	COUNT(amount)	COUNT(customer_id)
7	7	7

COUNT(*)	COUNT(amount)	COUNT(customer_id)
8	8	8

COUNT(*)	COUNT(amount)	COUNT(customer_id)
8	7	6

Question 6 GROUP BY with NULL Beginner Practice Query executed

What are the paid totals per customer?

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meena	Pune
4	John	Mumbai
5	Zoya	NULL

orders

id	customer_id	amount	status	order_date
101	1	500	paid	2026-01-05
102	1	1500	paid	2026-01-20
103	2	800	cancelled	2026-02-02
104	3	NULL	pending	2026-02-10
105	3	1200	paid	2026-02-15
106	4	700	paid	2026-03-01
107	1	300	refunded	2026-03-03
108	NULL	400	paid	2026-03-09

SELECT customer_id, SUM(amount) AS paid
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY customer_id;

customer_id	paid
1	2000
3	1200
4	700

customer_id	paid
1	2000
3	1200
4	700
NULL	400

customer_id	paid
1	2300
2	800
3	1200
4	700
NULL	400

customer_id	paid
NULL	400
1	2000
3	1200
4	700

Question 7 HAVING Intermediate Practice Query executed

Which query lists customers whose paid orders total more than 700, by name?

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meena	Pune
4	John	Mumbai
5	Zoya	NULL

orders

id	customer_id	amount	status	order_date
101	1	500	paid	2026-01-05
102	1	1500	paid	2026-01-20
103	2	800	cancelled	2026-02-02
104	3	NULL	pending	2026-02-10
105	3	1200	paid	2026-02-15
106	4	700	paid	2026-03-01
107	1	300	refunded	2026-03-03
108	NULL	400	paid	2026-03-09

SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(o.amount) > 700
ORDER BY c.name;

SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
HAVING COUNT(*) > 1
ORDER BY c.name;

SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid' AND SUM(o.amount) > 700
GROUP BY c.id, c.name;

SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
HAVING SUM(o.amount) > 700
ORDER BY c.name;

Question 8 DENSE_RANK Beginner Practice Query executed

What does DENSE_RANK give for Maths?

scores

id	student	subject	marks
1	Asha	Maths	92
2	Ravi	Maths	85
3	Meena	Maths	92
4	John	Maths	78
5	Asha	Physics	88
6	Ravi	Physics	90
7	Meena	Physics	75
8	John	Physics	90

SELECT student, DENSE_RANK() OVER (ORDER BY marks DESC) AS r
FROM scores
WHERE subject = 'Maths'
ORDER BY r, student;

student	r
Asha	1
Meena	1
Ravi	2
John	3

student	r
Asha	1
Meena	1
Ravi	1
John	2

student	r
Asha	1
Meena	2
Ravi	3
John	4

student	r
Asha	1
Meena	1
Ravi	3
John	4

Question 9 UNIQUE and NULL Beginner Practice Query executed

email is UNIQUE and Ravi's email is already NULL. What happens?

departments

id	name
1	Engineering
2	Sales

employees

id	email	name	dept_id	salary
1	asha@x.com	Asha	1	90000
2	NULL	Ravi	2	40000
3	meena@x.com	Meena	1	75000

INSERT INTO employees (id, email, name, dept_id, salary) VALUES (4, NULL, 'John', 2, 50000);
SELECT COUNT(*) AS null_emails FROM employees WHERE email IS NULL;

null_emails
0

null_emails
2

null_emails
1

null_emails
4

Question 10 Autocommit Beginner Practice Query executed

No transaction was started. What is Ravi's balance after the ROLLBACK?

accounts

id	holder	balance
1	Asha	1000
2	Ravi	500

UPDATE accounts SET balance = 0 WHERE id = 2;
ROLLBACK;
SELECT balance FROM accounts WHERE id = 2;

balance
0

balance
NULL

balance
(no rows)

balance
500

Question 11 Nth highest Intermediate Practice Query executed

A candidate answers "third-highest salary" with this query. What does it return?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

SELECT salary FROM staff
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

salary
70000

salary
65000

salary
62000

salary
90000

Question 12 Pivot Intermediate Practice Query executed

This turns quarters into columns. What does it return?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

SELECT staff_id,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2
FROM sales
GROUP BY staff_id
ORDER BY staff_id;

staff_id	q1	q2
4	100	150
5	120	60
6	0	90

staff_id	q1	q2
4	100	150
5	120	60
6	NULL	90

staff_id	q1	q2
4	100	150
5	80	60
6	0	90

staff_id	q1	q2
4	250	250
5	180	180
6	90	90

Question 13 NOT IN with NULL Intermediate Practice Query executed

How many departments does this query count?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT COUNT(*) AS total FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);

total
3

total
1

total
2

total
0

Question 14 Frames and ties Intermediate Practice Query executed

The window orders by marks, which has a tie. What is the running sum?

scores

id	student	subject	marks
1	Asha	Maths	92
2	Ravi	Maths	85
3	Meena	Maths	92
4	John	Maths	78
5	Asha	Physics	88
6	Ravi	Physics	90
7	Meena	Physics	75
8	John	Physics	90

SELECT student, marks, SUM(marks) OVER (ORDER BY marks DESC) AS running
FROM scores
WHERE subject = 'Maths'
ORDER BY marks DESC, id;

student	marks	running
Asha	92	184
Meena	92	184
Ravi	85	269
John	78	347

student	marks	running
Asha	92	347
Meena	92	347
Ravi	85	347
John	78	347

student	marks	running
Asha	92	92
Meena	92	92
Ravi	85	177
John	78	255

student	marks	running
Asha	92	92
Meena	92	184
Ravi	85	269
John	78	347

Question 15 Top-N per group Intermediate Practice Query executed

Which query returns the top scorer(s) of each subject including ties, by subject and name?

scores

id	student	subject	marks
1	Asha	Maths	92
2	Ravi	Maths	85
3	Meena	Maths	92
4	John	Maths	78
5	Asha	Physics	88
6	Ravi	Physics	90
7	Meena	Physics	75
8	John	Physics	90

SELECT subject, student FROM scores
WHERE marks = (SELECT MAX(marks) FROM scores)
ORDER BY subject, student;

SELECT subject, student FROM (
  SELECT subject, student, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY marks DESC, id) AS r FROM scores
) t WHERE r = 1 ORDER BY subject, student;

SELECT subject, student FROM (
  SELECT subject, student, RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS r FROM scores
) t WHERE r = 1 ORDER BY subject, student;

SELECT subject, student FROM (
  SELECT subject, student, DENSE_RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS r FROM scores
) t WHERE r <= 2 ORDER BY subject, student;

Question 16 String functions Advanced Practice Query executed

Keyboard's stock is NULL. What does this return?

products

id	name	category	price	stock	launched
1	Pen	Stationery	20.00	500	2024-01-10
2	Notebook	Stationery	60.00	120	2024-03-05
3	Backpack	Bags	899.00	40	2025-06-01
4	Laptop Sleeve	Bags	650.00	0	2025-08-15
5	Mouse	Electronics	499.00	75	2025-02-20
6	Keyboard	Electronics	1299.00	NULL	2026-01-12
7	Marker	Stationery	35.00	300	NULL

SELECT CONCAT(name, '-', stock) AS c1, CONCAT_WS('-', name, stock) AS c2
FROM products WHERE id = 6;

c1	c2
Keyboard-NULL	Keyboard

c1	c2
NULL	Keyboard

c1	c2
Keyboard-	Keyboard-

c1	c2
NULL	NULL

Question 17 Duplicates Advanced Practice Query executed

This removes duplicate emails, keeping the lowest id. Which rows remain with the duplicated address?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

DELETE s1 FROM staff s1
JOIN staff s2 ON s1.email = s2.email AND s1.id > s2.id;
SELECT id, name FROM staff WHERE email = 'ravi@example.com';

id	name
2	Ravi

id	name
2	Ravi
8	Arjun

id	name
(no rows)

id	name
8	Arjun

Question 18 Top-N per group Advanced Practice Query executed

This "top 2 earners per department" query uses no window functions. What does it return?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

SELECT dept, name FROM staff s
WHERE (SELECT COUNT(*) FROM staff t
       WHERE t.dept = s.dept AND t.salary > s.salary) < 2
ORDER BY dept, salary DESC, name;

dept	name
Eng	Asha
Eng	Meera
HR	Neha
Sales	Karan
Sales	John

dept	name
Eng	Asha
Eng	Meera
Eng	Ravi
Eng	Arjun
HR	Neha
Sales	Karan
Sales	John

dept	name
Eng	Asha
Eng	Meera
Eng	Ravi
HR	Neha
Sales	Karan
Sales	John

dept	name
Eng	Asha
Eng	Ravi
Sales	Karan
Sales	John

Question 19 Architecture Beginner Practice

Adding an index or moving tables to faster disks without changing any application is an example of:

Data redundancy Referential integrity Physical data independence Logical data independence

Question 20 Workloads Advanced Practice

What distinguishes OLTP from OLAP?

OLTP is only for reporting OLTP handles many short read/write transactions; OLAP runs fewer, heavy analytical queries over large data OLAP needs no SQL They are the same thing

Question 21 Prime attributes Advanced Practice

R(A, B, C, D) has candidate keys AB and BC. Which attributes are prime?

A, B, C and D A, B and C Only B A and B

Question 22 Super key Intermediate Practice

R(A, B, C, D) has exactly one candidate key, A. How many super keys does R have?

Your answer

Question 23 Participation Beginner Practice

"Every loan must belong to a customer." How is the participation of Loan in the Borrows relationship described?

Total participation (double line) Optional participation Recursive participation Partial participation (single line)

Question 24 ER to tables Intermediate Practice

Department and Employee are linked by a 1:N relationship WorksIn (one department, many employees). What is the minimum number of tables needed?

Your answer

Question 25 ER to tables Advanced Practice

An ER diagram has strong entities A, B and C. A–B is M:N, B–C is 1:N, and A has one multivalued attribute. What is the minimum number of tables?

Your answer

Question 26 3NF Beginner Practice

In employees(emp_id, dept_id, dept_name), emp_id → dept_id and dept_id → dept_name. Which normal form is violated?

1NF 3NF – dept_name depends transitively on the key through dept_id None 2NF

Question 27 Finding the normal form Intermediate Practice

R(A, B, C) has the FDs AB → C and C → B. What is the highest normal form of R?

1NF BCNF 3NF 2NF

Question 28 Dependency preservation Intermediate Practice

What does it mean for a decomposition to be dependency-preserving?

Every attribute appears in every table Every table keeps the same primary key Every original FD can be checked within the individual tables, without joining them No rows are lost when joining

Question 29 Finding candidate keys Advanced Practice

R(A, B, C, D, E) has the FDs A → B, BC → E and ED → A. How many candidate keys does R have?

Your answer

Question 30 ACID Beginner Practice

Which ACID property ensures concurrent transactions do not see each other's intermediate, uncommitted work?

Atomicity Durability Consistency Isolation

Question 31 Read anomalies Intermediate Practice

T1 runs SELECT COUNT(*) FROM accounts WHERE balance > 400 twice. Between the two, T2 inserts a new qualifying account and commits. T1 gets a higher count. What is this?

A dirty read A non-repeatable read A phantom read A deadlock

Question 32 Deadlock prevention Advanced Practice

Under the wait-die scheme, an older transaction requests a lock held by a younger one. What happens?

The older transaction is rolled back The younger transaction is rolled back Both are rolled back The older transaction waits

Question 33 When indexes are ignored Beginner Practice

orders has an index on order_date. Why can WHERE YEAR(order_date) = 2026 not use it efficiently, and what is the fix?

Dates cannot be indexed YEAR() is not allowed in WHERE The function hides the column from the index; write WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' Add DISTINCT

Question 34 Composite indexes Intermediate Practice

An index exists on (city, age). Which query can use both columns of it?

WHERE city = 'Pune' AND age > 30 WHERE age = 30 OR city = 'Pune' WHERE age > 30 WHERE city > 'P' AND age = 30

Question 35 Index types Advanced Practice

What is the difference between a dense and a sparse index?

A dense index is clustered; a sparse one is a hash index A sparse index has an entry for every record They differ only in storage engine A dense index has an entry for every record; a sparse index has one entry per data block and needs the file sorted on the key

DBMS & SQL · Full Mock 2

Sections

Before you start