MySQL + SQL · Lesson 130

MySQL MCQ Quiz (150 Questions)

In this lesson: 150 MySQL & DBMS MCQs with instant answers and explanations — online quiz for exams and interviews. Test yourself now.

Complete MySQL MCQ Quiz – 150 Questions

This is an interactive MySQL/SQL test. It shows one question at a time with timer, next/previous buttons, question palette, result and answer review.

Case study tables (for questions marked [Case study])

customers (id INT PRIMARY KEY, name, city)

id	name	city
1	Asha	Delhi
2	Ravi	Pune
3	Meena	Delhi
4	John	Jaipur

orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id), amount INT, order_date DATE)

id	customer_id	amount	order_date
101	1	500	2026-01-05
102	1	1200	2026-01-20
103	2	300	2026-02-02
104	3	1500	2026-02-10
105	3	700	2026-03-01

Every case-study answer was checked by running the query on a real MySQL-compatible server.

Question 1/15090:00

Question Palette

Green = answered, Purple = review, White = not answered.

Answer key with explanations

Attempt the test and submit it first, then use this key to revise. Each answer has a short explanation so you understand why it is correct.

Show all 150 answers

What is a DBMS?
Answer: B. Software to create, store, manage and query databases – A Database Management System (MySQL, Oracle, PostgreSQL) lets users define, store, retrieve and protect data.
Which is an advantage of a DBMS over a file system?
Answer: C. Reduced data redundancy and better consistency – A DBMS stores shared data once and enforces constraints, so copies do not go out of sync.
In RDBMS, data is stored in the form of:
Answer: A. Tables (relations) – A relational database organises data into tables made of rows and columns.
A row in a table is also called a:
Answer: C. Tuple – Row = tuple = record. Column = attribute = field.
A column in a table is also called an:
Answer: D. Attribute – Each column describes one property (attribute) of the entity.
The number of rows in a relation is called its:
Answer: D. Cardinality – Cardinality counts rows (tuples); degree counts columns (attributes).
The number of columns in a relation is called its:
Answer: B. Degree – A table with columns RollNo, Name, Marks has degree 3.
The set of allowed values for an attribute is called its:
Answer: D. Domain – Example: the domain of Gender could be {'M','F','O'}; the domain of Marks could be 0–100.
Which key uniquely identifies each row and cannot be NULL?
Answer: D. Primary key – A primary key must be unique and NOT NULL; a table has only one primary key.
All attributes or attribute sets that can uniquely identify a row are:
Answer: B. Candidate keys – From the candidate keys, one is chosen as primary key; the rest become alternate keys.
A candidate key that is NOT chosen as primary key is called:
Answer: B. Alternate key – If RollNo is the primary key, AadhaarNo (also unique) is an alternate key.
A column in one table that refers to the primary key of another table is a:
Answer: A. Foreign key – Foreign keys link tables, e.g. Marks.RollNo references Students.RollNo.
A primary key made of two or more columns is called a:
Answer: B. Composite key – Example: (RollNo, Subject) together identify a row in a Marks table.
A super key is:
Answer: A. Any set of attributes that uniquely identifies a row – Every candidate key is a minimal super key; adding extra columns to a key still gives a super key.
Referential integrity ensures that:
Answer: D. A foreign key value matches an existing primary key value or is NULL – You cannot insert marks for a student RollNo that does not exist in Students.
Which of these is NOT a DBMS model?
Answer: A. Spreadsheet model – Classic data models: hierarchical, network, relational, and object-oriented.
The overall logical design of a database is called the:
Answer: D. Schema – Schema is the structure; instance is the data stored at a particular moment.
The data stored in a database at a particular moment is called:
Answer: B. Instance – The instance changes with every insert/update/delete; the schema changes rarely.
Data about data (table names, column types) is called:
Answer: A. Metadata – The data dictionary/information_schema stores metadata.
Which level of data abstraction describes how data is physically stored?
Answer: B. Physical (internal) level – Three levels: physical, logical/conceptual, and view/external.
Physical data independence means:
Answer: C. Changing storage structure without changing the logical schema – E.g. adding an index or moving files does not require changing application queries.
In an ER diagram, an entity is shown by a:
Answer: B. Rectangle – Rectangle = entity, ellipse = attribute, diamond = relationship.
In an ER diagram, a relationship is shown by a:
Answer: A. Diamond – Example: Student —<Enrolls>— Course.
A Student can enrol in many Courses and a Course has many Students. The relationship is:
Answer: D. Many-to-many – M:N relationships are implemented with a separate linking table such as Enrollment(RollNo, CourseID).
An attribute that can be calculated from other attributes (e.g. Age from DOB) is:
Answer: A. Derived attribute – Derived attributes are usually not stored; they are computed when needed.
An attribute that can have several values for one entity (e.g. phone numbers) is:
Answer: B. Multivalued attribute – In a relational design, multivalued attributes are moved to a separate table.
First Normal Form (1NF) requires that:
Answer: C. Every column holds atomic (single) values – A column like Subjects = 'Maths, Science' violates 1NF.
A table is in 2NF if it is in 1NF and:
Answer: B. No non-key attribute depends on only part of a composite key – 2NF removes partial dependencies, which only arise with composite keys.
3NF removes which type of dependency?
Answer: B. Transitive dependency – If RollNo → PinCode and PinCode → City, then City depends transitively on RollNo; move it to a PinCode table.
BCNF is a stricter form of:
Answer: D. 3NF – In BCNF, every determinant must be a candidate key.
The main purpose of normalisation is to:
Answer: A. Reduce redundancy and update anomalies – Normalised tables avoid insert, update and delete anomalies.
Deleting the last student of a course also deletes the course details. This is a:
Answer: A. Delete anomaly – It happens when unrelated facts are stored in the same table.
Which property of a transaction means 'all or nothing'?
Answer: D. Atomicity – ACID: Atomicity, Consistency, Isolation, Durability.
Which ACID property ensures committed data survives a power failure?
Answer: B. Durability – InnoDB writes a redo log so committed transactions are not lost.
Which ACID property keeps concurrent transactions from seeing each other's uncommitted changes?
Answer: B. Isolation – Isolation levels (READ COMMITTED, REPEATABLE READ…) control this.
Which of the following is an open-source RDBMS?
Answer: A. MySQL – MySQL (and MariaDB, PostgreSQL) are open-source relational database systems.
The default storage engine in MySQL 8 is:
Answer: B. InnoDB – InnoDB supports transactions, row-level locking and foreign keys.
Which storage engine does NOT support transactions?
Answer: B. MyISAM – MyISAM uses table-level locking and has no COMMIT/ROLLBACK.
SQL stands for:
Answer: B. Structured Query Language – SQL is the standard language for relational databases.
Which category of SQL commands defines structure (CREATE, ALTER, DROP)?
Answer: C. DDL – DDL = Data Definition Language.
Which category includes INSERT, UPDATE and DELETE?
Answer: D. DML – DML = Data Manipulation Language.
GRANT and REVOKE belong to:
Answer: D. DCL – DCL = Data Control Language, used for permissions.
COMMIT, ROLLBACK and SAVEPOINT belong to:
Answer: D. TCL – TCL = Transaction Control Language.
Which constraint prevents NULL values in a column?
Answer: C. NOT NULL – NOT NULL forces a value to be supplied for that column.
Which constraint allows only distinct values but (in MySQL) permits multiple NULLs?
Answer: B. UNIQUE – Unlike PRIMARY KEY, a UNIQUE column can contain NULLs.
A CHECK constraint is used to:
Answer: C. Allow only values that satisfy a condition – Example: CHECK (Marks BETWEEN 0 AND 100). MySQL enforces CHECK from version 8.0.16.
A view in SQL is:
Answer: A. A virtual table based on a SELECT query – Views store the query, not the data; they simplify complex queries and hide columns.
An index is mainly used to:
Answer: A. Speed up searching and sorting – Indexes make SELECT faster but add a small cost to INSERT/UPDATE.
Which person is responsible for managing users, backups and performance of a database?
Answer: B. Database Administrator (DBA) – The DBA controls access, backups, tuning and recovery.
Two tables are joined using a common column. In a good design this column is usually:
Answer: D. A primary key in one table and a foreign key in the other – PK–FK pairs define the relationship and keep data consistent.
Which command creates a new database named school?
Answer: D. CREATE DATABASE school; – CREATE DATABASE creates it; USE school; selects it.
Which command selects a database for use?
Answer: C. USE school; – After USE, all table names refer to that database.
Which command shows all tables of the current database?
Answer: B. SHOW TABLES; – SHOW DATABASES lists databases; SHOW TABLES lists tables.
Which command shows the structure (columns and types) of a table?
Answer: D. DESCRIBE students; – DESC students; is the short form.
Which data type is best for a name of up to 50 characters?
Answer: A. VARCHAR(50) – VARCHAR stores variable-length text up to the given length.
Which data type stores exact money values like 1250.75?
Answer: A. DECIMAL(10,2) – DECIMAL avoids floating-point rounding errors in money calculations.
The default date format in MySQL is:
Answer: C. YYYY-MM-DD – Example: '2026-03-15'.
Which statement adds a new row to a table?
Answer: A. INSERT INTO students VALUES (1, 'Asha', 88); – INSERT INTO table [(columns)] VALUES (...).
Which statement changes Asha's marks to 92?
Answer: B. UPDATE students SET marks = 92 WHERE name = 'Asha'; – Always use WHERE with UPDATE, otherwise every row changes.
What happens if you run UPDATE students SET marks = 0; without WHERE?
Answer: C. Marks of every student become 0 – UPDATE without WHERE affects all rows – a common costly mistake. MySQL Workbench's safe-update mode blocks it.
Which statement deletes all students of class 9 but keeps the table?
Answer: D. DELETE FROM students WHERE class = 9; – DELETE removes rows; DROP removes the whole table; TRUNCATE has no WHERE.
Difference between DELETE (without WHERE) and TRUNCATE:
Answer: C. TRUNCATE is DDL, faster and resets AUTO_INCREMENT – Both empty the table, but TRUNCATE recreates it and cannot be filtered.
Which command removes a table and its structure permanently?
Answer: A. DROP TABLE students; – After DROP, the table no longer exists.
Which command adds a column email to students?
Answer: A. ALTER TABLE students ADD email VARCHAR(80); – ALTER TABLE … ADD / MODIFY / DROP / RENAME COLUMN changes structure.
Which command changes the data type of column marks to DECIMAL(5,2)?
Answer: B. ALTER TABLE students MODIFY marks DECIMAL(5,2); – MODIFY changes the definition; CHANGE can also rename the column.
Which query shows only the name and marks columns?
Answer: A. SELECT name, marks FROM students; – List the columns after SELECT, separated by commas.
Which keyword removes duplicate values from the result?
Answer: A. DISTINCT – SELECT DISTINCT city FROM students; lists each city once.
Which clause sorts the result by marks, highest first?
Answer: A. ORDER BY marks DESC – ASC is the default; DESC gives descending order.
Which condition finds names starting with 'A'?
Answer: A. name LIKE 'A%' – % matches any number of characters; _ matches exactly one.
Which condition finds names whose second letter is 'a'?
Answer: B. name LIKE '_a%' – The _ stands for exactly one character before 'a'.
Which query finds students with marks from 60 to 80 inclusive?
Answer: B. WHERE marks BETWEEN 60 AND 80 – BETWEEN includes both end values.
Which condition selects students from Delhi, Pune or Jaipur?
Answer: A. city IN ('Delhi', 'Pune', 'Jaipur') – IN is a short form of several OR conditions.
How do you find rows where phone is missing?
Answer: A. WHERE phone IS NULL – NULL is not equal to anything, even NULL; always use IS NULL / IS NOT NULL.
What does SELECT COUNT(*) FROM students; return?
Answer: B. The number of rows in students – COUNT(*) counts rows including those with NULLs.
COUNT(phone) counts:
Answer: A. Rows where phone is not NULL – Aggregate functions on a column ignore NULLs.
Which function returns the average marks?
Answer: D. AVG(marks) – Other aggregates: SUM, MIN, MAX, COUNT.
Which clause groups rows to show average marks per class?
Answer: B. GROUP BY class – SELECT class, AVG(marks) FROM students GROUP BY class;
To show only classes whose average marks are above 75, use:
Answer: D. HAVING AVG(marks) > 75 – WHERE filters rows before grouping; HAVING filters groups after aggregation.
Correct order of clauses in a SELECT statement:
Answer: B. SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT – Writing them in this order avoids syntax errors.
Which query returns the top 3 scorers?
Answer: D. SELECT * FROM students ORDER BY marks DESC LIMIT 3; – MySQL uses LIMIT; SQL Server uses TOP.
What does SELECT ROUND(78.456, 1); return?
Answer: C. 78.5 – ROUND(x, d) rounds to d decimal places.
What does SELECT UPPER('sql'); return?
Answer: D. SQL – UPPER/UCASE converts to capitals; LOWER/LCASE to small letters.
What does SELECT LENGTH('India'); return?
Answer: D. 5 – LENGTH returns bytes; for plain English letters that equals the number of characters. Use CHAR_LENGTH for Hindi text.
What does SELECT SUBSTR('Database', 1, 4); return?
Answer: C. Data – SUBSTR(string, start, length); positions start at 1.
What does SELECT CONCAT('Coding', 'Easily'); return?
Answer: D. CodingEasily – CONCAT joins strings without adding spaces.
What does SELECT MOD(17, 5); return?
Answer: D. 2 – MOD returns the remainder: 17 = 5×3 + 2.
Which function returns today's date?
Answer: B. CURDATE() – NOW() returns date and time; CURDATE() returns only the date.
What does SELECT YEAR('2026-08-15'); return?
Answer: B. 2026 – MONTH() and DAY() extract the other parts.
What does SELECT POWER(2, 5); return?
Answer: D. 32 – POWER(x, y) = x raised to y.
Which gives an alias to a column in the result?
Answer: C. SELECT marks AS score FROM students; – AS renames a column or table only in that query's output.
Which command gives user 'ravi' permission to read the school database?
Answer: D. GRANT SELECT ON school.* TO 'ravi'@'localhost'; – REVOKE … FROM removes the permission.
After START TRANSACTION, which command undoes uncommitted changes?
Answer: A. ROLLBACK; – ROLLBACK cancels all changes since the transaction started (or since a SAVEPOINT).
Which statement inserts several rows at once?
Answer: D. INSERT INTO t (a,b) VALUES (1,2), (3,4), (5,6); – Separate each row's values with commas – faster than many single inserts.
Which constraint automatically gives the next number to a new row's id?
Answer: D. AUTO_INCREMENT – Usually: id INT AUTO_INCREMENT PRIMARY KEY.
Which clause in CREATE TABLE sets a value when none is given?
Answer: B. DEFAULT – Example: city VARCHAR(30) DEFAULT 'Delhi'.
Which command renames table students to learners?
Answer: D. RENAME TABLE students TO learners; – ALTER TABLE students RENAME TO learners; also works.
Which query counts students in each city?
Answer: D. SELECT city, COUNT(*) FROM students GROUP BY city; – GROUP BY makes one row per city; COUNT(*) counts rows in each group.
What is the result of NULL + 10 in SQL?
Answer: A. NULL – Any arithmetic with NULL gives NULL; use IFNULL(col, 0) or COALESCE to replace it.
Which function replaces NULL marks with 0 in the output?
Answer: A. IFNULL(marks, 0) – COALESCE(marks, 0) is the standard SQL equivalent.
Which statement saves all changes of the current transaction permanently?
Answer: A. COMMIT; – With autocommit ON, each statement is committed automatically.
Which join returns only rows that have matching values in both tables?
Answer: B. INNER JOIN – Rows without a match in either table are dropped.
Which join returns all rows of the left table and matching rows of the right table?
Answer: A. LEFT JOIN – Unmatched right-side columns appear as NULL.
A CROSS JOIN of a 4-row table with a 5-row table returns:
Answer: D. 20 rows – A cross join is the Cartesian product: 4 × 5.
Joining a table with itself (e.g. employee and manager in one table) is called:
Answer: B. Self join – Use aliases: FROM emp e JOIN emp m ON e.manager_id = m.id.
MySQL does not support FULL OUTER JOIN directly. It can be produced with:
Answer: A. LEFT JOIN UNION RIGHT JOIN – UNION removes duplicates of the matched rows.
UNION and UNION ALL differ because:
Answer: B. UNION removes duplicate rows, UNION ALL keeps them – UNION ALL is faster when duplicates are acceptable.
A subquery is:
Answer: B. A query written inside another query – Example: SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);
A correlated subquery:
Answer: B. Refers to a column of the outer query and runs once per outer row – Example: find students scoring above their own class average.
Which operator checks whether a subquery returns any row?
Answer: D. EXISTS – WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id).
marks > ALL (SELECT marks FROM students WHERE class = 9) means:
Answer: C. Greater than the highest class-9 mark – > ANY means greater than at least one value (i.e. the minimum).
Which window function gives 1, 2, 2, 4 for marks 95, 90, 90, 85?
Answer: A. RANK() – RANK leaves gaps after ties; DENSE_RANK would give 1, 2, 2, 3.
Which window function gives 1, 2, 2, 3 for marks 95, 90, 90, 85?
Answer: A. DENSE_RANK() – DENSE_RANK does not skip numbers after ties (MySQL 8+).
SUM(amount) OVER (ORDER BY order_date) calculates:
Answer: B. A running total – Window functions keep every row while adding an aggregate.
A CTE (Common Table Expression) starts with the keyword:
Answer: D. WITH – WITH toppers AS (SELECT …) SELECT * FROM toppers; (MySQL 8+).
Which statement shows how MySQL will execute a query (index usage etc.)?
Answer: A. EXPLAIN SELECT … – EXPLAIN shows the access type, keys used and rows estimated.
An index on (last_name, first_name) helps most for:
Answer: A. WHERE last_name = 'Sharma' AND first_name = 'Ravi' – Composite indexes are used from the leftmost column (leftmost-prefix rule).
A stored procedure is called using:
Answer: A. CALL proc_name(); – Procedures can take IN, OUT and INOUT parameters.
Why is DELIMITER $$ used before CREATE PROCEDURE in the MySQL client?
Answer: B. So the semicolons inside the body do not end the statement early – Reset with DELIMITER ; afterwards.
A trigger runs:
Answer: B. Automatically before or after INSERT, UPDATE or DELETE on a table – Example: a BEFORE INSERT trigger that converts names to upper case.
Inside an UPDATE trigger, the new value of a column is accessed as:
Answer: B. NEW.column – OLD.column gives the value before the update.
A SAVEPOINT allows you to:
Answer: A. Roll back part of a transaction – ROLLBACK TO sp1; undoes changes made after the savepoint.
Which isolation level is the InnoDB default?
Answer: C. REPEATABLE READ – It prevents dirty and non-repeatable reads.
Reading another transaction's uncommitted change is called a:
Answer: A. Dirty read – Allowed only at READ UNCOMMITTED.
Two transactions each waiting for a lock held by the other is a:
Answer: D. Deadlock – InnoDB detects deadlocks and rolls back one transaction.
Which tool creates a logical backup (SQL file) of a MySQL database?
Answer: C. mysqldump – mysqldump -u root -p school > school.sql; restore with mysql school < school.sql.
Which clause prevents SQL injection in application code?
Answer: A. Prepared statements with parameters – Never build SQL by joining user input into the query string.
CASE in SQL is used to:
Answer: D. Return values based on conditions inside a query – CASE WHEN marks >= 90 THEN 'A1' WHEN marks >= 80 THEN 'A2' ELSE 'B' END.
ON DELETE CASCADE on a foreign key means:
Answer: A. Deleting a parent row automatically deletes its child rows – Use with care – one DELETE can remove many related rows.
Which data type stores JSON documents with validation in MySQL 8?
Answer: B. JSON – Use ->> or JSON_EXTRACT to read values.
GROUP_CONCAT(name) in a GROUP BY query returns:
Answer: D. All names of the group joined in one string – Default separator is a comma; SEPARATOR ' | ' changes it.
[Case study] SELECT COUNT(*) FROM orders; returns:
Answer: D. 5 – The orders table has 5 rows (ids 101–105).
[Case study] SELECT SUM(amount) FROM orders WHERE customer_id = 1; returns:
Answer: A. 1700 – Asha's orders: 500 + 1200 = 1700.
[Case study] Which customer has no orders?
Answer: C. John – Customer 4 (John) has no row in orders; find him with LEFT JOIN … WHERE o.id IS NULL.
[Case study] SELECT COUNT(*) FROM customers c JOIN orders o ON c.id = o.customer_id; returns:
Answer: A. 5 – Every order matches exactly one customer, so the inner join has 5 rows.
[Case study] SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id; returns:
Answer: A. 6 – 5 matched rows + 1 row for John with NULL order columns.
[Case study] SELECT city, SUM(amount) … GROUP BY city — the total for Delhi is:
Answer: A. 3900 – Delhi customers Asha (1700) and Meena (2200): 1700 + 2200 = 3900.
[Case study] Which customer has the highest total order amount?
Answer: D. Meena – Totals: Asha 1700, Ravi 300, Meena 2200.
[Case study] SELECT MAX(amount) FROM orders WHERE order_date < '2026-02-01'; returns:
Answer: B. 1200 – January orders are 500 and 1200.
[Case study] SELECT AVG(amount) FROM orders; returns:
Answer: A. 840.0000 – Total 4200 ÷ 5 orders = 840 (MySQL shows 840.0000).
[Case study] How many customers have total orders above 1000 (… GROUP BY customer_id HAVING SUM(amount) > 1000)?
Answer: A. 2 – Asha (1700) and Meena (2200).
[Case study] SELECT COUNT(DISTINCT customer_id) FROM orders; returns:
Answer: D. 3 – Customers 1, 2 and 3 placed orders.
[Case study] SELECT MONTH(order_date) m, COUNT(*) FROM orders GROUP BY m — the count for month 2 is:
Answer: D. 2 – Orders 103 and 104 are in February.
[Case study] Which query lists customers who placed at least one order?
Answer: D. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders); – The subquery returns 1, 2, 3, 3, 1 → Asha, Ravi, Meena.
[Case study] SELECT name FROM customers WHERE city = 'Delhi' ORDER BY name DESC LIMIT 1; returns:
Answer: A. Meena – Delhi names sorted Z–A: Meena, Asha.
[Case study] For Asha, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) gives order 102 the number:
Answer: A. 2 – Asha's orders by date: 101 (1st), 102 (2nd).
[Case study] To stop deleting a customer who still has orders, the foreign key should use:
Answer: D. ON DELETE RESTRICT (the default) – RESTRICT/NO ACTION blocks the delete with error 1451.
[Case study] Which index would most speed up WHERE customer_id = ? on orders?
Answer: D. An index on orders(customer_id) – InnoDB automatically creates this index when you define the foreign key.
[Case study] SELECT c.name, o.amount FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.amount > (SELECT AVG(amount) FROM orders); returns how many rows?
Answer: A. 2 – Average is 840; orders above it: 1200 (Asha) and 1500 (Meena).
[Case study] SELECT COALESCE(SUM(o.amount), 0) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE c.name = 'John'; returns:
Answer: B. 0 – John has no orders, so SUM is NULL and COALESCE turns it into 0.
[Case study] Which statement adds a 10% discount to orders above 1000?
Answer: A. UPDATE orders SET amount = amount * 0.9 WHERE amount > 1000; – UPDATE with WHERE changes only orders 102 and 104.