-
1. Which clause filters groups after aggregation, for example keeping only departments with more than 5 employees?
- A. WHERE
- B. ORDER BY
- C. HAVING
- D. DISTINCT
Answer: C. HAVING
HAVING is applied after GROUP BY, so it can use aggregates such as COUNT(*) > 5. WHERE filters individual rows before grouping and cannot use aggregate functions.
-
2. In what logical order does a database process the clauses of a SELECT query?
- A. SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
- B. FROM → GROUP BY → WHERE → HAVING → SELECT → ORDER BY
- C. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
- D. SELECT → WHERE → FROM → ORDER BY → GROUP BY → HAVING
Answer: C. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
Rows are first read (FROM), filtered (WHERE), grouped (GROUP BY), groups filtered (HAVING), columns computed (SELECT) and finally sorted (ORDER BY). This is why a column alias from SELECT cannot be used in WHERE.
-
3. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
How many rows does this query return?
SELECT * FROM employees WHERE salary > 45000;
- A. 4
- B. 2
- C. 3
- D. 5
Answer: C. 3
Salaries above 45000 are Asha (50000), Chen (70000) and Divya (60000). Farah (45000) is not greater than 45000, and Eli’s NULL salary makes the condition unknown, so his row is not returned.
-
4. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
What does this query return?
SELECT COUNT(*), COUNT(salary) FROM employees;
- A. 6 and 6
- B. 6 and 5
- C. 5 and 5
- D. 5 and 6
Answer: B. 6 and 5
COUNT(*) counts every row, so it returns 6. COUNT(salary) counts only rows where salary is not NULL, and Eli’s salary is NULL, so it returns 5.
-
5. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
What does this query return?
SELECT AVG(salary) FROM employees WHERE dept = 'IT';
- A. 43333.33
- B. 65000
- C. 60000
- D. NULL
Answer: B. 65000
AVG ignores NULL values. The IT rows have salaries 70000, 60000 and NULL, so the average is (70000 + 60000) ÷ 2 = 65000, not a division by 3.
-
6. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
How many rows does this query return?
SELECT dept, COUNT(*)
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 2;
- A. 3
- B. 1
- C. 2
- D. 6
Answer: C. 2
Grouping gives three departments: Sales (2 rows), IT (3 rows) and HR (1 row). HAVING keeps groups with at least 2 rows, so Sales and IT are returned: 2 rows.
-
7. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
How many rows does this query return?
SELECT * FROM employees WHERE salary = NULL;
- A. 1
- B. 0
- C. 5
- D. An error in every database
Answer: B. 0
Any comparison with NULL using = gives UNKNOWN, never TRUE, so no row matches. The query runs but returns nothing. To find Eli’s row you must write WHERE salary IS NULL.
-
8. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
How many rows does this query return?
SELECT * FROM employees
WHERE salary IS NULL OR salary < 50000;
- A. 2
- B. 3
- C. 4
- D. 1
Answer: B. 3
Eli matches salary IS NULL. Ben (40000) and Farah (45000) match salary < 50000. Asha has exactly 50000, which is not less than 50000. Total: 3 rows.
-
9. Table customers:
id | name | city
1 | Ravi | Pune
2 | Meera | Delhi
3 | Sam | Pune
4 | Tara | Mumbai
Table orders:
id | customer_id | amount
101 | 1 | 500
102 | 1 | 300
103 | 2 | 700
104 | 5 | 200
How many rows does this query return?
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
- A. 4
- B. 5
- C. 2
- D. 3
Answer: D. 3
An inner join keeps only matching pairs. Ravi matches orders 101 and 102, Meera matches 103. Order 104 belongs to customer 5, who does not exist, so it is dropped. Result: 3 rows.
-
10. Table customers:
id | name | city
1 | Ravi | Pune
2 | Meera | Delhi
3 | Sam | Pune
4 | Tara | Mumbai
Table orders:
id | customer_id | amount
101 | 1 | 500
102 | 1 | 300
103 | 2 | 700
104 | 5 | 200
How many rows does this query return?
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
- A. 4
- B. 3
- C. 6
- D. 5
Answer: D. 5
A left join keeps every customer. Ravi appears twice (two orders), Meera once, and Sam and Tara once each with NULL amount because they have no orders: 2 + 1 + 1 + 1 = 5 rows.
-
11. Table customers:
id | name | city
1 | Ravi | Pune
2 | Meera | Delhi
3 | Sam | Pune
4 | Tara | Mumbai
Table orders:
id | customer_id | amount
101 | 1 | 500
102 | 1 | 300
103 | 2 | 700
104 | 5 | 200
How many rows does this query return?
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
- A. 1
- B. 2
- C. 0
- D. 3
Answer: B. 2
This is the standard pattern for "customers with no orders". The left join gives NULL order columns for Sam and Tara, and the WHERE clause keeps only those rows: 2 rows.
-
12. Table customers:
id | name | city
1 | Ravi | Pune
2 | Meera | Delhi
3 | Sam | Pune
4 | Tara | Mumbai
Table orders:
id | customer_id | amount
101 | 1 | 500
102 | 1 | 300
103 | 2 | 700
104 | 5 | 200
How many rows does this query return?
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
- A. 5
- B. 4
- C. 7
- D. 6
Answer: D. 6
A full outer join keeps matched rows plus unmatched rows from both sides. Matches: 3 rows (101, 102, 103). Unmatched customers: Sam and Tara (2 rows). Unmatched order: 104 (1 row). Total 6.
-
13. Table customers:
id | name | city
1 | Ravi | Pune
2 | Meera | Delhi
3 | Sam | Pune
4 | Tara | Mumbai
Table orders:
id | customer_id | amount
101 | 1 | 500
102 | 1 | 300
103 | 2 | 700
104 | 5 | 200
How many rows does this query return?
SELECT * FROM customers CROSS JOIN orders;
- A. 8
- B. 4
- C. 16
- D. 0
Answer: C. 16
A cross join pairs every row of the first table with every row of the second, with no condition. 4 customers × 4 orders = 16 rows.
-
14. Table customers:
id | name | city
1 | Ravi | Pune
2 | Meera | Delhi
3 | Sam | Pune
4 | Tara | Mumbai
Table orders:
id | customer_id | amount
101 | 1 | 500
102 | 1 | 300
103 | 2 | 700
104 | 5 | 200
What total does this query show for Pune?
SELECT c.city, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.city;
- A. 1500
- B. 500
- C. 800
- D. 1000
Answer: C. 800
The inner join keeps only customers with orders. Pune has Ravi (500 + 300) and Sam (no orders, so no rows). Pune total = 800. Delhi shows 700, and order 104 has no customer so it is excluded.
-
15. Table customers:
id | name | city
1 | Ravi | Pune
2 | Meera | Delhi
3 | Sam | Pune
4 | Tara | Mumbai
Table orders:
id | customer_id | amount
101 | 1 | 500
102 | 1 | 300
103 | 2 | 700
104 | 5 | 200
What does this query return?
SELECT COUNT(DISTINCT city) FROM customers;
- A. 4
- B. 2
- C. 1
- D. 3
Answer: D. 3
The cities are Pune, Delhi, Pune and Mumbai. DISTINCT removes the repeated Pune, leaving Pune, Delhi and Mumbai, so the count is 3.
-
16. Which statement about a PRIMARY KEY is correct?
- A. Its values must be unique and cannot be NULL
- B. It can hold duplicate values but not NULL
- C. A table can have several primary keys
- D. It must always be a single integer column
Answer: A. Its values must be unique and cannot be NULL
A primary key uniquely identifies each row, so its values must be unique and not NULL. A table has only one primary key, although it can be made of more than one column (a composite key) and need not be an integer.
-
17. What is the main trade-off of adding an index to a column?
- A. Faster searches on that column, but slower inserts and updates and extra storage
- B. Faster inserts, but slower searches
- C. It removes duplicate rows automatically
- D. No trade-off; indexes only make every operation faster
Answer: A. Faster searches on that column, but slower inserts and updates and extra storage
An index is a separate sorted structure that lets the database find rows without scanning the whole table. It must be updated on every insert, update and delete, and it takes disk space.
-
18. A users table has an ordinary B-tree index on the email column. Which WHERE condition can NOT use this index to seek directly to matching rows?
- A. WHERE email LIKE '%@example.com'
- B. WHERE email = 'ravi@example.com'
- C. WHERE email IN ('a@example.com', 'b@example.com')
- D. WHERE email > 'm'
Answer: A. WHERE email LIKE '%@example.com'
A B-tree index is sorted from the start of the value. A pattern that begins with a wildcard gives no starting point, so the database must scan. Equality, IN lists and ranges can all seek through the sorted index.
-
19. Table scores:
student | score
Anu | 90
Bala | 85
Chitra | 85
Dev | 80
What value does Dev get from this query?
SELECT student, RANK() OVER (ORDER BY score DESC) AS rnk
FROM scores;
- A. 4
- B. 3
- C. 2
- D. 5
Answer: A. 4
RANK gives tied rows the same rank and then skips numbers. Anu is 1, Bala and Chitra tie at 2, so rank 3 is skipped and Dev gets 4. DENSE_RANK would give Dev 3.
-
20. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
How many rows does this query return?
SELECT name, dept,
SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;
- A. 6
- B. 3
- C. 5
- D. 1
Answer: A. 6
A window function does not collapse rows the way GROUP BY does. Every one of the 6 employees is returned, each with the salary total of their department alongside.
-
21. Table sales:
day | amount
1 | 100
2 | 50
3 | 70
What is running_total on day 3?
SELECT day,
SUM(amount) OVER (ORDER BY day) AS running_total
FROM sales;
- A. 70
- B. 120
- C. 150
- D. 220
Answer: D. 220
With ORDER BY inside OVER, the sum covers all rows from the first day up to the current one. Day 1: 100. Day 2: 150. Day 3: 100 + 50 + 70 = 220.
-
22. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
What does this query return?
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
- A. 60000
- B. 70000
- C. 50000
- D. NULL
Answer: A. 60000
The subquery finds the highest salary, 70000. The outer query takes the maximum of salaries below 70000, which is Divya’s 60000. This is a common way to find the second-highest salary.
-
23. Which command removes a table completely, including its structure, so it can no longer be queried?
- A. DELETE FROM
- B. DROP TABLE
- C. TRUNCATE TABLE
- D. ALTER TABLE
Answer: B. DROP TABLE
DROP TABLE removes the table definition and all its data. DELETE removes rows (optionally with a WHERE clause) and TRUNCATE removes all rows, but both leave the empty table in place.
-
24. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
The salary column is DECIMAL. What does this query return (rounded to 2 decimals)?
SELECT AVG(COALESCE(salary, 0))
FROM employees WHERE dept = 'IT';
- A. 43333.33
- B. 65000
- C. 60000
- D. NULL
Answer: A. 43333.33
COALESCE replaces Eli’s NULL with 0 before averaging, so all three IT rows count: (70000 + 60000 + 0) ÷ 3 = 43333.33. Without COALESCE the NULL would be skipped and the answer would be 65000.
-
25. Table employees:
id | name | dept | salary | manager_id
1 | Asha | Sales | 50000 | NULL
2 | Ben | Sales | 40000 | 1
3 | Chen | IT | 70000 | NULL
4 | Divya | IT | 60000 | 3
5 | Eli | IT | NULL | 3
6 | Farah | HR | 45000 | NULL
How many rows does this self join return?
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;
- A. 6
- B. 4
- C. 2
- D. 3
Answer: D. 3
The table is joined to itself. Only employees with a manager_id that matches an id appear: Ben (manager Asha), Divya (manager Chen) and Eli (manager Chen). Asha, Chen and Farah have NULL manager_id, so they drop out.