Final Quiz

A 30-question final assessment across the whole course.

Concept

A 30-question final assessment across the whole course. Target: 24 or more correct.

Why It Matters

Mixed questions reveal gaps that module-level practice may hide.

Syntax

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 is MySQL's default storage engine? A) MyISAM B) InnoDB C) MEMORY D) NDB Answer: B.

A2. Default TCP port? A) 1433 B) 5432 C) 3306 D) 1521 Answer: C.

A3. Default transaction isolation in InnoDB? A) READ UNCOMMITTED B) READ COMMITTED C) REPEATABLE READ D) SERIALIZABLE

A4. Which character set stores emoji? A) utf8mb3 B) latin1 C) utf8mb4 D) ascii

A5. Which statement deletes and re-inserts a row on key conflict? A) INSERT IGNORE B) REPLACE C) ON DUPLICATE KEY UPDATE D) MERGE

A6. Which option makes mysqldump take a consistent InnoDB snapshot without locking? A) --lock-all-tables B) --single-transaction C) --quick D) --no-data

A7. Which log is used for replication and point-in-time recovery? A) Redo log B) Undo log C) Binary log D) Slow log

A8. Which EXPLAIN type is a full table scan? A) ref B) range C) eq_ref D) ALL Answer: D.

A9. Which authentication plugin is the default in MySQL 8? A) mysql_native_password B) caching_sha2_password C) auth_socket D) sha256_password

A10. Which feature did MySQL 8.0 remove? A) JSON B) Query cache C) Triggers D) Views Section B — True/False

B1. In MySQL, || concatenates strings by default. — False (logical OR unless PIPES_AS_CONCAT).

B2. CHECK constraints are enforced since MySQL 8.0.16. — True.

B3. A failed statement inside a MySQL transaction automatically rolls back the whole transaction. — False (only the statement, except deadlocks).

B4. InnoDB secondary indexes store the primary key value. — True.

B5. MySQL supports FULL OUTER JOIN. — False. B6. TRUNCATE TABLE can be rolled back in MySQL. — False (implicit commit).

B7. A UNIQUE index in MySQL allows multiple NULL values. — True.

B8. Partitioned InnoDB tables can have foreign keys. — False.

Section C — Predict the output (sample database) C1. SELECT 7 / 2, 7 DIV 2, 7 % 2; Answer: 3.5000, 3, 1.

C2. SELECT COUNT(*), COUNT(dept_id), COUNT(DISTINCT dept_id) FROM employees; Answer: 8, 7, 3.

C3. SELECT CONCAT('ECU', NULL), CONCAT_WS('-', 'ECU', NULL, '7'); Answer: NULL, ECU-7.

C4. SELECT 'ravi' = 'RAVI'; with the default collation.

Answer: 1 (case-insensitive _ci collation).

C5. SELECT first_name FROM employees WHERE dept_id NOT IN (2, 3, NULL); Answer: Empty set.

C6. SELECT DATEDIFF('2026-03-10', '2026-01-05'), TIMESTAMPDIFF(MONTH, '2026-01-05', '2026-03-10'); Answer: 64, 2.

Section D — Find the error Answer: ERROR 1055 under ONLY_FULL_GROUP_BY: first_name is neither grouped nor aggregated. Use ANY_VALUE, an aggregate, or group by it.

Answer: ERROR 1093 (target table in subquery). Wrap the subquery in a derived table, or simply DELETE FROM customers WHERE email IS NULL (subject to FKs).

Answer: OFFSET and SIGNAL are reserved words → syntax error. Rename (signal_offset, signal_name) or backtick them.

Section E — Write the query E1. Top 2 products by revenue from order_items (all orders).

E2. Upsert a department (5, 'Quality', 'Chennai'), updating the location if the ID exists.

E3. Create a read-only role for eh_company and grant it to a new user.

SELECT dept_id, first_name, COUNT(*) FROM employees GROUP BY dept_id;

DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM customers WHERE email IS NULL);

CREATE TABLE signals (id INT PRIMARY KEY, offset DOUBLE, signal VARCHAR(20));

SELECT p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id ORDER BY revenue DESC LIMIT 2;
-- Laptop 150000.00, Monitor 36000.00

INSERT INTO departments (dept_id, dept_name, location) VALUES (5, 'Quality', 'Chennai') AS new
ON DUPLICATE KEY UPDATE location = new.location;

CREATE ROLE 'r_eh_ro';
GRANT SELECT ON eh_company.* TO 'r_eh_ro';
CREATE USER 'viewer'@'%' IDENTIFIED BY 'use-a-strong-secret';
GRANT 'r_eh_ro' TO 'viewer'@'%';
SET DEFAULT ROLE ALL TO 'viewer'@'%';

Expected Result

Score 1 point per question (30 total). 24+: ready for advanced work; 18–23: review Modules 9–17; below 18: repeat Stage 1 and 2 exercises.

Common Mistakes

Errors usually cluster around NULL logic, ONLY_FULL_GROUP_BY, transactions, and MySQL-specific syntax differences.

Best Practices

Retake the quiz after a week without reading the answers first.

Interview Relevance

Sections C and D mirror technical screening questions.

Practice Questions

Write five new quiz questions about your weakest topics and answer them.