SQL Quizzes (Final Assessment)

A comprehensive final assessment covering the entire course.

Concept

A comprehensive final assessment covering the entire course. Quiz checkpoints 1–7 appeared after the major sections; this final quiz mixes all topics. Target: at least 80% correct. All questions use the course sample database unless stated otherwise.

Why It Matters

Mixed-topic testing reveals gaps that section quizzes hide, and it mirrors real interviews.

Syntax

Question types: Multiple choice (MC), True/False (TF), Predict the output (PO), Find the error (FE), Write the query (WQ).

Example

Section A — Multiple Choice A1. Which clause is evaluated first logically? A) SELECT B) WHERE C) FROM D) ORDER BY

Answer: C. FROM (with joins) builds the source rows first.

A2. Which statement returns the number of employees with a department? A) COUNT(*) B) COUNT(dept_id) C) COUNT(DISTINCT dept_id) D) SUM(dept_id)

Answer: B (7). COUNT(column) ignores NULLs; C returns distinct departments (3).

A3. Which normal form removes transitive dependencies? A) 1NF B) 2NF C) 3NF D) 4NF Answer: C.

A4. Which isolation level allows dirty reads? A) READ UNCOMMITTED B) READ COMMITTED C) REPEATABLE READ D) SERIALIZABLE Answer: A.

A5. The best protection against SQL injection is: A) Escaping quotes manually B) Client-side validation C) Parameterized queries D) Hiding error messages

A6. Which index type supports range queries such as BETWEEN? A) Hash B) B-tree C) Bitmap only D) None Answer: B.

A7. Which window function leaves gaps after ties? A) ROW_NUMBER B) DENSE_RANK C) RANK D) NTILE

A8. Which backup type contains changes since the last full backup? A) Incremental B) Differential C) Log D) Logical Section B — True/False

B1. NULL = NULL evaluates to TRUE. — False (UNKNOWN).

B2. A LEFT JOIN can never return fewer rows than the left table has. — True (it can return more, due to multiple matches, but never fewer).

B3. A table can have only one UNIQUE constraint. — False.

B4. A CTE can be referenced by a later, separate SQL statement. — False (scope is one statement).

B5. UNION ALL removes duplicates. — False. B6. A recursive CTE requires a termination condition to avoid infinite recursion. — True.

B7. Views always improve query performance. — False (normal views store no data).

B8. In MySQL, CREATE TABLE inside a transaction causes an implicit commit. — True. Section C — Predict the Output

Answer: 0. The subquery returns a NULL (Kiran), so NOT IN is never TRUE.

Answer: Finance, 1. COUNT(*) counts the NULL-padded row; COUNT(e.emp_id) would return 0.

Answer: Neha 1, Meera 1, Arjun 2 (the ranking is computed only over the filtered rows).

Answer: DELIVERED, 180800.00 — only DELIVERED has more than one order.

Answer: 25, then −1 (Standing Desk stock 0 → NULLIF → NULL → COALESCE → −1).

Answer: 5 rows of 'x'. SELECT NULL returns one row, so EXISTS is TRUE for every customer.

Section D — Find the Error Answer: Aliases and aggregates cannot be used in WHERE; use HAVING COUNT(*) > 1.

Answer: Not a syntax error, but check intent: it returns Engineering employees and employees without a department. If the intent was "all employees, showing the department name only when it is in Bangalore", the location filter belongs in the ON clause.

Answer: The second UPDATE affects 0 rows without an error, so 500 disappears. Check affected row counts and ROLLBACK if either is not 1.

Answer: The cast makes the predicate non-sargable, so the index cannot be used for seeking. Use order_date >= DATE '2026-03-01' AND order_date < DATE '2026-04-01'.

Answer: No termination condition → infinite recursion (until a limit error). Add WHERE n < 10.

Section E — Write the Query E1. List department names with their highest salary, including departments without employees (show NULL).

Result: Engineering 250000, Sales 120000, HR 90000, Finance NULL.

E2. Show each order with its rank by amount within its month (highest = 1).

Result: 1001→1, 1002→2, 1003→1, 1004→2, 1005→2, 1006→1.

E3. Find customers whose first order was cancelled.

Result: Chen Wei.

E4. Create a table ecu_flash_log that stores ECU flashing events: unique ID, ECU reference, software version reference, start and end timestamps (end after start), and a result limited to SUCCESS/FAILED.

E5. Write a safe transaction that deletes customer 5 only if they have no orders, reporting the number of deleted rows.

SELECT COUNT(*) FROM employees WHERE dept_id NOT IN (SELECT dept_id FROM employees);

SELECT d.dept_name, COUNT(*) FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE d.dept_id = 4 GROUP BY d.dept_name;

SELECT first_name, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees WHERE first_name IN ('Neha', 'Meera', 'Arjun');

SELECT status, SUM(total_amount) FROM orders GROUP BY status HAVING COUNT(*) > 1;

SELECT COALESCE(NULLIF(stock, 0), -1) AS s FROM products WHERE product_id IN (105, 106) ORDER BY product_id;

SELECT 'x' FROM customers c WHERE EXISTS (SELECT NULL);

SELECT customer_id, COUNT(*) AS n
FROM orders
WHERE n > 1
GROUP BY customer_id;

SELECT e.first_name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = 'Bangalore' OR e.dept_id IS NULL;

BEGIN;
UPDATE bank_accounts SET balance = balance - 500 WHERE account_no = 'ACC-100';
UPDATE bank_accounts SET balance = balance + 500 WHERE account_no = 'ACC-999';   -- account does not exist
COMMIT;

CREATE INDEX ix_orders_date ON orders (order_date);
SELECT * FROM orders WHERE CAST(order_date AS VARCHAR(10)) LIKE '2026-03%';

Expected Result

Scoring: 1 point per question (32 questions). 26+ = ready for advanced/senior practice; 20–25 = review the modules related to missed questions; below 20 = repeat Stage 1–2 modules and the quiz checkpoints.

Common Mistakes

Missed questions usually cluster around NULL behavior, outer-join filters, and window-function scope — revisit Modules 8, 13, 17, and 40.

Best Practices

Retake the quiz after a week without looking at the answers.

Interview Relevance

Sections C and D mirror the "what does this return / what is wrong" style common in technical screens.

Practice Questions

Write five new quiz questions of your own (one per type) about topics you found hardest, and answer them.