MySQL Quiz ( Intermediate ) - All Questions

This intermediate-level MySQL quiz is designed for learners who understand SQL basics and want to move toward real-world database usage. It focuses on joins, subqueries, indexes, normalization, transactions, constraints, and common interview-level MySQL scenarios.

Question 1: What is the main purpose of normalization in MySQL?

Increase redundancy
Improve query speed
Reduce data duplication
Simplify joins

Explanation: Normalization reduces data redundancy and improves integrity.

Question 2: Which normal form removes partial dependency?

1NF
2NF
3NF
BCNF

Explanation: Second Normal Form removes partial dependency.

Question 3: Which normal form removes transitive dependency?

1NF
2NF
3NF
BCNF

Explanation: Third Normal Form removes transitive dependency.

Question 4: What is a foreign key?

A unique identifier
A primary key
A reference to another table's primary key
An index

Explanation: Foreign keys reference primary keys of other tables.

Question 5: Which action maintains referential integrity?

Indexes
Constraints
Views
Triggers

Explanation: Constraints maintain data relationships.

Question 6: What does ON DELETE CASCADE do?

Prevents deletion
Deletes child rows automatically
Deletes parent only
Throws error

Explanation: It deletes related child records.

Question 7: Which JOIN returns all records from both tables?

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

Explanation: FULL OUTER JOIN returns all rows from both tables.

Question 8: Which JOIN returns unmatched rows as NULL?

INNER JOIN
LEFT JOIN
CROSS JOIN
SELF JOIN

Explanation: LEFT JOIN returns NULL for unmatched right rows.

Question 9: What is a SELF JOIN?

Join with no condition
Join table with itself
Join two databases
Join using subquery

Explanation: Self join joins a table to itself.

Question 10: Which clause is executed after GROUP BY?

WHERE
ORDER BY
HAVING
LIMIT

Explanation: HAVING filters grouped results.

Question 11: What is the purpose of HAVING clause?

Filter rows
Filter columns
Filter aggregated data
Sort data

Explanation: HAVING filters aggregated results.

Question 12: Which aggregate function calculates average?

SUM()
AVG()
MEAN()
COUNT()

Explanation: AVG() returns average value.

Question 13: What is a subquery?

A temporary table
A query inside another query
A stored procedure
A join condition

Explanation: Subqueries are nested queries.

Question 14: Which subquery returns a single value?

Correlated
Scalar
Derived
Nested

Explanation: Scalar subqueries return one value.

Question 15: Which keyword checks existence of rows?

IN
ANY
EXISTS
ALL

Explanation: EXISTS checks row existence.

Question 16: What is an index?

A data table
A sorting mechanism
A performance optimization structure
A constraint

Explanation: Indexes speed up data retrieval.

Question 17: Which index allows duplicate values?

PRIMARY
UNIQUE
NON-UNIQUE
FULLTEXT

Explanation: Non-unique indexes allow duplicates.

Question 18: Which index is best for text searching?

BTREE
HASH
FULLTEXT
UNIQUE

Explanation: FULLTEXT supports text search.

Question 19: What is the main drawback of indexes?

Slow reads
Extra storage and slower writes
Data loss
Query failure

Explanation: Indexes slow down write operations.

Question 20: Which MySQL feature ensures ACID properties?

MyISAM
InnoDB
Memory
CSV

Explanation: InnoDB supports ACID transactions.

Question 21: What does COMMIT do?

Undo changes
Save changes permanently
Lock table
End connection

Explanation: COMMIT saves transaction changes.

Question 22: What does ROLLBACK do?

Save changes
Undo uncommitted changes
Delete database
Reset server

Explanation: ROLLBACK undoes uncommitted changes.

Question 23: What is a transaction?

Single query
Group of SQL statements executed as one unit
Backup process
Index operation

Explanation: Transactions ensure atomic operations.

Question 24: Which isolation level allows dirty reads?

READ COMMITTED
REPEATABLE READ
READ UNCOMMITTED
SERIALIZABLE

Explanation: READ UNCOMMITTED allows dirty reads.

Question 25: Which isolation level prevents phantom reads?

READ COMMITTED
REPEATABLE READ
SERIALIZABLE
READ UNCOMMITTED

Explanation: SERIALIZABLE prevents phantom reads.

Question 26: What is a deadlock?

System crash
Two transactions waiting for each other
Index failure
Table corruption

Explanation: Deadlock occurs when transactions block each other.

Question 27: Which command creates a view?

MAKE VIEW
ADD VIEW
CREATE VIEW
NEW VIEW

Explanation: CREATE VIEW defines a view.

Question 28: What is the benefit of using views?

Faster writes
Security and query simplification
Extra storage
Index replacement

Explanation: Views simplify queries and improve security.

Question 29: What is a stored procedure?

Saved SQL logic on server
Temporary query
Index type
Trigger

Explanation: Stored procedures store SQL logic.

Question 30: Which statement creates a stored procedure?

CREATE FUNCTION
CREATE PROCEDURE
MAKE PROCEDURE
ADD PROCEDURE

Explanation: CREATE PROCEDURE defines procedures.

Question 31: What is a trigger?

Manual script
Auto-executed SQL on events
Index optimizer
Backup job

Explanation: Triggers run automatically on events.

Question 32: Which event activates a trigger?

SELECT
INSERT, UPDATE, DELETE
JOIN
GROUP BY

Explanation: Triggers respond to DML events.

Question 33: What is the purpose of CHECK constraint?

Ensure uniqueness
Validate column values
Create index
Set default

Explanation: CHECK enforces value conditions.

Question 34: Which function handles NULL values?

NVL()
IFNULL()
COALESCE()
Both B and C

Explanation: IFNULL and COALESCE handle NULLs.

Question 35: What does DISTINCT do?

Sorts data
Removes duplicate rows
Limits rows
Groups data

Explanation: DISTINCT removes duplicates.

Question 36: Which clause limits grouped results?

WHERE
GROUP BY
HAVING
ORDER BY

Explanation: HAVING limits grouped data.

Question 37: What does EXPLAIN do?

Runs query
Shows query execution plan
Optimizes index
Debugs SQL syntax

Explanation: EXPLAIN shows query execution plan.

Question 38: Which factor affects query performance most?

Column order
Indexes
Table name
Alias usage

Explanation: Indexes significantly affect performance.

Question 39: What is denormalization?

Removing indexes
Adding redundancy intentionally
Splitting tables
Removing constraints

Explanation: Denormalization improves read performance.

Question 40: Which MySQL object improves reusable logic?

Index
View
Stored procedure
Constraint

Explanation: Stored procedures reuse logic.

Question 41: Which keyword enforces uniqueness at table level?

PRIMARY
UNIQUE
CHECK
INDEX

Explanation: UNIQUE enforces uniqueness.

Question 42: Which SQL clause is applied last?

WHERE
GROUP BY
HAVING
ORDER BY

Explanation: ORDER BY is applied last.

Question 43: What is the default isolation level in MySQL?

READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE

Explanation: REPEATABLE READ is the default.

Question 44: Which tool is commonly used to manage MySQL visually?

Mongo Compass
pgAdmin
MySQL Workbench
DBeaver

Explanation: MySQL Workbench is widely used.

Question 45: Which command backs up a MySQL database?

mysqlbackup
mysqldump
dbdump
sqlbackup

Explanation: mysqldump is used for backups.

Question 46: What is the main goal of indexing strategy?

Minimize storage
Balance read and write performance
Avoid joins
Reduce schema size

Explanation: Indexes trade write cost for read speed.

Question 47: What is the primary purpose of MySQL in applications?

Frontend rendering
Persistent relational data storage
Caching only
Message queue

Explanation: MySQL stores relational application data.

Back to programming Quizzes

MySQL Quiz ( Intermediate )

Questions

~10

Minutes

Min Limit

Free ATS Friendly Resume Builder Online

MySQL Quiz ( Intermediate )