MySQL Quiz

Overview

Test your MySQL knowledge with these questions. Each section covers different aspects of MySQL from basics to advanced topics.

Quiz Structure

• Basics Quiz: Basic MySQL concepts
• Data Types Quiz: Understanding data types
• CRUD Operations Quiz: Create, Read, Update, Delete
• Joins Quiz: Table joining operations
• Aggregations Quiz: Grouping and aggregates
• Advanced Quiz: Transactions, indexes, optimization

Basics Quiz

Question 1

What is the default storage engine in MySQL 8.0?

A. MyISAM B. InnoDB C. MEMORY D. CSV

Answer: B. InnoDB

Question 2

Which command is used to select a database in MySQL?

A. USE database_name; B. SELECT database_name; C. CONNECT database_name; D. OPEN database_name;

Answer: A. USE database_name;

Question 3

What does NULL represent in MySQL?

A. Zero value B. Empty string C. Unknown or missing value D. False value

Answer: C. Unknown or missing value

Question 4

Which statement lists all databases in MySQL?

A. SHOW TABLES; B. SHOW DATABASES; C. LIST DATABASES; D. DISPLAY DATABASES;

Answer: B. SHOW DATABASES.

Question 5

What is the primary purpose of a foreign key?

A. Uniquely identify each row B. Establish relationship between tables C. Improve query performance D. Ensure data integrity across tables

Answer: D. Ensure data integrity across tables

Data Types Quiz

Question 1

Which data type is best for storing monetary values?

A. FLOAT B. DOUBLE C. DECIMAL D. INT

Answer: C. DECIMAL

Question 2

What is the maximum value of TINYINT UNSIGNED?

A. 127 B. 255 C. 32767 D. 65535

Answer: B. 255

Question 3

Which data type would you use to store a boolean value?

A. BOOLEAN B. TINYINT(1) C. INT D. VARCHAR(5)

Answer: B. TINYINT(1)

Question 4

Which character set is recommended for full Unicode support?

A. utf8 B. utf8mb4 C. latin1 D. ascii

Answer: B. utf8mb4

Question 5

What is the difference between CHAR and VARCHAR?

A. CHAR is variable length, VARCHAR is fixed B. CHAR is fixed length, VARCHAR is variable C. No difference, they are the same D. CHAR cannot be indexed

Answer: B. CHAR is fixed length, VARCHAR is variable

CRUD Operations Quiz

Question 1

Which SQL statement is used to add new records to a table?

A. ADD RECORD B. INSERT INTO C. CREATE RECORD D. APPEND RECORD

Answer: B. INSERT INTO

Question 2

How do you retrieve all columns from a table?

A. SELECT ALL FROM table_name B. SELECT * FROM table_name C. GET * FROM table_name D. RETRIEVE * FROM table_name

Answer: B. SELECT * FROM table_name

Question 3

Which clause is required in UPDATE statement to avoid updating all rows?

A. WHERE B. ORDER BY C. LIMIT D. GROUP BY

Answer: A. WHERE

Question 4

How do you delete all records from a table?

A. DELETE ALL FROM table_name B. REMOVE FROM table_name C. DELETE FROM table_name D. CLEAR FROM table_name

Answer: C. DELETE FROM table_name

Question 5

Which statement removes the table structure and all data?

A. DELETE TABLE table_name B. DROP TABLE table_name C. REMOVE TABLE table_name D. TRUNCATE TABLE table_name

Answer: B. DROP TABLE table_name

Joins Quiz

Question 1

Which JOIN returns only matching rows from both tables?

A. LEFT JOIN B. RIGHT JOIN C. INNER JOIN D. FULL OUTER JOIN

Answer: C. INNER JOIN

Question 2

Which JOIN returns all rows from the left table and matching rows from the right?

A. LEFT JOIN B. RIGHT JOIN C. INNER JOIN D. FULL OUTER JOIN

Answer: A. LEFT JOIN

Question 3

How do you join a table to itself?

A. SELF JOIN B. AUTO JOIN C. RECURSIVE JOIN D. MIRROR JOIN

Answer: A. SELF JOIN

Question 4

Which keyword is used for joining tables in SQL?

A. CONNECT B. JOIN C. LINK D. COMBINE

Answer: B. JOIN

Question 5

What is the purpose of using aliases in JOINs?

A. To create temporary tables B. To improve performance C. To avoid ambiguous column names D. To encrypt the data

Answer: C. To avoid ambiguous column names

Aggregations Quiz

Question 1

Which function counts the number of rows?

A. COUNT() B. SUM() C. AVG() D. TOTAL()

Answer: A. COUNT()

Question 2

Which clause is used to group rows in SQL?

A. ORDER BY B. GROUP BY C. WHERE D. HAVING

Answer: B. GROUP BY

Question 3

What is the difference between WHERE and HAVING?

A. WHERE filters rows before grouping, HAVING filters after B. HAVING filters rows before grouping, WHERE filters after C. They are the same D. WHERE is used with GROUP BY, HAVING is used alone

Answer: A. WHERE filters rows before grouping, HAVING filters after

Question 4

Which function calculates the average value?

A. MIN() B. MAX() C. AVG() D. MEDIAN()

Answer: C. AVG()

Question 5

What does the DISTINCT keyword do?

A. Removes duplicate rows from results B. Sorts the results C. Limits the number of rows D. Filters NULL values

Answer: A. Removes duplicate rows from results

Advanced Quiz

Question 1

What is ACID in database transactions?

A. Automatic, Complete, Integrated, Durable B. Atomic, Consistent, Isolated, Durable C. Available, Compatible, Integrated, Distributed D. Active, Current, In-memory, Distributed

Answer: B. Atomic, Consistent, Isolated, Durable

Question 2

Which statement starts a transaction?

A. START TRANSACTION B. BEGIN TRANSACTION C. Both A and B D. Neither A nor B

Answer: C. Both A and B

Question 3

What happens when you execute ROLLBACK?

A. Saves all changes B. Saves changes up to last savepoint C. Reverts all uncommitted changes D. Deletes the transaction log

Answer: C. Reverts all uncommitted changes

Question 4

Which database object improves query performance?

A. View B. Stored procedure C. Index D. Trigger

Answer: C. Index

Question 5

What does EXPLAIN do?

A. Executes the query and shows results B. Shows the query execution plan C. Optimizes the query automatically D. Creates an index for the query

Answer: B. Shows the query execution plan

Practical Quiz

Question 1

Given the following query, what is the result?

SELECT IFNULL(NULL, 'default');

A. NULL B. default C. Error D. 0

Answer: B. default

Question 2

What does this query return?

SELECT CONCAT('Hello', ' ', 'World');

A. Hello World B. Hello World C. 'Hello World' D. Error

Answer: A. Hello World

Question 3

What is the result of this expression?

SELECT MOD(17, 5);

A. 3 B. 2 C. 5 D. 0

Answer: B. 2

Question 4

Which pattern matches strings starting with 'A'?

A. LIKE '%A' B. LIKE 'A%' C. LIKE '_A' D. REGEXP '^A'

Answer: B. LIKE 'A%'

Question 5

What does LIMIT 10, 5 mean?

A. Return 10 rows starting from row 5 B. Return 5 rows starting from row 10 C. Return 10 rows, skip 5 D. Return maximum of 10 rows

Answer: B. Return 5 rows starting from row 10

Scoring

Score Calculation

Each correct answer = 1 point
Each section = 5 questions
Total possible score = 30 points

Basics: / 5
Data Types: / 5
CRUD: / 5
Joins: / 5
Aggregations: / 5
Advanced: / 5

Performance Levels

27-30 points: Expert
23-26 points: Advanced
19-22 points: Intermediate
15-18 points: Beginner
Below 15 points: Needs Review

Answers Key

Detailed Explanations

Basics:

1. InnoDB is default in MySQL 8.0, supporting transactions and foreign keys
2. USE is the command to select/switch databases
3. NULL represents unknown or missing data, not zero or empty
4. SHOW DATABASES lists all databases
5. Foreign keys establish relationships between tables, ensuring referential integrity

Data Types:

1. DECIMAL provides exact precision for monetary values
2. TINYINT UNSIGNED range is 0-255
3. BOOLEAN is stored as TINYINT(1) in MySQL
4. utf8mb4 supports full Unicode (4-byte UTF-8)
5. CHAR has fixed length, VARCHAR has variable length

CRUD Operations:

1. INSERT INTO adds new records
2. SELECT * retrieves all columns
3. WHERE clause prevents unintended updates
4. DELETE FROM table_name removes all rows
5. DROP TABLE removes structure and data completely

Joins:

1. INNER JOIN returns matching rows only
2. LEFT JOIN returns all left table rows and matching right rows
3. Self-join uses table aliases to join to itself
4. JOIN keyword combines tables
5. Aliases prevent column name conflicts in joins

Aggregations:

1. COUNT() returns the number of rows
2. GROUP BY groups rows for aggregation
3. WHERE filters before grouping, HAVING filters after
4. AVG() calculates average value
5. DISTINCT removes duplicate results

Advanced:

1. ACID ensures transaction reliability
2. Both START TRANSACTION and BEGIN work
3. ROLLBACK undoes all uncommitted changes
4. Indexes significantly improve query performance
5. EXPLAIN shows how MySQL executes queries

Study Recommendations

Based on Quiz Performance

Expert (27-30 points):
- Ready for database administration
- Can work on complex optimization
- Ready to mentor others

Advanced (23-26 points):
- Strong understanding of MySQL
- Can handle complex queries
- Need practice on optimization

Intermediate (19-22 points):
- Good working knowledge
- Can perform most operations
- Need more advanced learning

Beginner (15-18 points):
- Understand basic concepts
- Can write simple queries
- Need more practice on joins and aggregations

Needs Review (Below 15 points):
- Review basics and data types
- Practice CRUD operations
- Complete tutorial again

Next Steps

- Review chapters where you missed questions
- Practice writing queries
- Work on real projects
- Take the quiz again after review

Summary

This quiz covers:

• MySQL Basics: Commands, concepts, NULL handling
• Data Types: Appropriate type selection
• CRUD Operations: Insert, select, update, delete
• Table Joins: Inner, left, self joins
• Aggregations: Grouping, filtering, functions
• Advanced Topics: Transactions, indexes, performance

Use this quiz to assess your MySQL knowledge and identify areas for improvement.

---

Previous: Commands Reference

Next: Programming Languages