MySQL Beginner Quiz
30 questions on MySQL Basics.
Question 1: Which SQL statement is used to retrieve data from a database?
- A. INSERT
- B. UPDATE
- C. SELECT β (correct answer)
- D. DELETE
Explanation: The SELECT statement is used to retrieve data from a database table.
Question 2: What does the WHERE clause do in a MySQL query?
- A. Sorts the results
- B. Filters the records to extract only those that fulfill a specified condition β (correct answer)
- C. Joins two tables together
- D. Limits the number of returned rows
Explanation: The WHERE clause ensures that only data meeting specific criteria (e.g., WHERE age > 18) is returned.
Question 3: Which clause is used to sort the result-set in MySQL?
- A. SORT BY
- B. ORDER BY β (correct answer)
- C. ALIGN BY
- D. GROUP BY
Explanation: The ORDER BY clause is used to sort the result-set in ascending (ASC) or descending (DESC) order.
Question 4: Which statement is used to insert new data into a MySQL database?
- A. ADD RECORD
- B. UPDATE TABLE
- C. INSERT INTO β (correct answer)
- D. APPEND TO
Explanation: The INSERT INTO statement is the standard command for adding new rows to a table.
Question 5: Which command is used to delete records from a table?
- A. DROP
- B. REMOVE
- C. TRUNCATE
- D. DELETE β (correct answer)
Explanation: The DELETE statement removes existing records from a table based on a WHERE condition. Without WHERE, it deletes all rows.
Question 6: What is the purpose of the UPDATE statement?
- A. To upgrade MySQL versions
- B. To modify existing records in a table β (correct answer)
- C. To refresh the database connection
- D. To add a new column to a table
Explanation: The UPDATE statement changes existing data within a table. It is typically paired with a WHERE clause.
Question 7: Which SQL keyword is used to find unique, non-duplicate values?
- A. UNIQUE
- B. DISTINCT β (correct answer)
- C. DIFFER
- D. SINGLE
Explanation: Using SELECT DISTINCT column_name returns only the unique values from that column.
Question 8: What is the default sorting order of the ORDER BY clause?
- A. Descending (DESC)
- B. Ascending (ASC) β (correct answer)
- C. Random
- D. Insertion order
Explanation: If you do not specify ASC or DESC, MySQL automatically sorts in ascending order (A-Z, 0-9).
Question 9: Which aggregate function returns the total number of rows that match a specified criterion?
- A. SUM()
- B. TOTAL()
- C. COUNT() β (correct answer)
- D. MAX()
Explanation: COUNT() returns the number of rows. For example, COUNT(*) counts all rows in the result set.
Question 10: Which SQL clause limits the number of records returned?
- A. LIMIT β (correct answer)
- B. TOP
- C. MAX
- D. FETCH
Explanation: In MySQL, the LIMIT clause is used to restrict the number of rows returned (e.g., LIMIT 10).
Question 11: What is a "Primary Key"?
- A. The first column in a table
- B. A field (or combination of fields) that uniquely identifies each row in a table β (correct answer)
- C. The password used to access the database
- D. A foreign key in another table
Explanation: A primary key ensures row uniqueness and cannot contain NULL values.
Question 12: What does the AUTO_INCREMENT attribute do?
- A. Automatically increases the salary column
- B. Automatically generates a unique number for a new record inserted into a table β (correct answer)
- C. Speeds up database queries
- D. Automatically upgrades the table version
Explanation: AUTO_INCREMENT is typically used on the Primary Key column so you don't have to manually specify an ID when inserting data.
Question 13: Which operator is used to search for a specified pattern in a column?
- A. MATCH
- B. LIKE β (correct answer)
- C. EQUALS
- D. SEARCH
Explanation: The LIKE operator is used in a WHERE clause along with wildcards (% and _) to search for text patterns.
Question 14: What does the % wildcard represent in a LIKE clause?
- A. Exactly one character
- B. Zero, one, or multiple characters β (correct answer)
- C. A space character
- D. A number
Explanation: For example, LIKE 'a%' finds any string that starts with "a".
Question 15: Which keyword combines the result sets of two or more SELECT statements (removing duplicates)?
- A. JOIN
- B. COMBINE
- C. MERGE
- D. UNION β (correct answer)
Explanation: UNION stacks result sets vertically, whereas JOINs combine tables horizontally based on a related column.
Question 16: What is the difference between DELETE and TRUNCATE?
- A. DELETE drops the table structure; TRUNCATE empties the data
- B. TRUNCATE is faster and resets the AUTO_INCREMENT counter, while DELETE removes rows one by one β (correct answer)
- C. There is no difference
- D. DELETE can only be used on single columns
Explanation: TRUNCATE is a DDL operation that drops and recreates the table, making it much faster than DELETE for clearing all data.
Question 17: Which type of JOIN returns all rows from the left table, and the matched rows from the right table?
- A. INNER JOIN
- B. FULL JOIN
- C. LEFT JOIN β (correct answer)
- D. RIGHT JOIN
Explanation: A LEFT JOIN ensures every record from the left-side table is included. If there is no match on the right, the result is NULL on the right side.
Question 18: Which clause must follow GROUP BY if you want to filter the grouped results based on an aggregate function?
- A. WHERE
- B. FILTER
- C. HAVING β (correct answer)
- D. LIMIT
Explanation: The WHERE clause cannot be used with aggregate functions like COUNT() or SUM(). HAVING is designed specifically to filter grouped data.
Question 19: What is the purpose of an Index in MySQL?
- A. To enforce foreign key constraints
- B. To speed up the retrieval of rows from a table or view β (correct answer)
- C. To compress table data
- D. To automatically generate primary keys
Explanation: Indexes act like a book's table of contents, allowing the database engine to find data without scanning every row (a full table scan).
Question 20: In MySQL, what data type is best used for storing monetary values to avoid rounding errors?
- A. FLOAT
- B. DOUBLE
- C. DECIMAL β (correct answer)
- D. INT
Explanation: DECIMAL stores exact numeric values, whereas FLOAT and DOUBLE use floating-point approximations, which can cause precision issues with currency.
Question 21: What does the IN operator do?
- A. Checks if a value is inside a string
- B. Allows you to specify multiple values in a WHERE clause β (correct answer)
- C. Inserts data into a table
- D. Joins two tables
Explanation: WHERE id IN (1, 2, 3) is a shorthand for multiple OR conditions: WHERE id = 1 OR id = 2 OR id = 3.
Question 22: Which MySQL command removes a table AND all of its data completely from the database?
- A. DELETE TABLE
- B. TRUNCATE TABLE
- C. DROP TABLE β (correct answer)
- D. REMOVE TABLE
Explanation: DROP completely destroys the table structure, data, indexes, and triggers associated with it.
Question 23: How do you add a new column to an already existing table?
- A. MODIFY TABLE table_name ADD column_name
- B. ALTER TABLE table_name ADD column_name datatype β (correct answer)
- C. UPDATE TABLE table_name ADD column_name
- D. INSERT COLUMN column_name TO table_name
Explanation: The ALTER TABLE command is used to add, delete, or modify columns in an existing table.
Question 24: What is a "Foreign Key"?
- A. A key used for encrypting external databases
- B. A field that uniquely identifies a row in its own table
- C. A field in one table that refers to the Primary Key in another table β (correct answer)
- D. A secondary index
Explanation: Foreign keys establish a link between the data in two tables and enforce referential integrity.
Question 25: What happens if you run an UPDATE statement without a WHERE clause?
- A. It updates the first row only
- B. It returns an error and stops
- C. It deletes the table
- D. It updates every single row in the table β (correct answer)
Explanation: This is a common and dangerous mistake. Without a WHERE clause to restrict the scope, MySQL will apply the change to all records.
Question 26: What is a Subquery?
- A. A query nested inside another query β (correct answer)
- B. A query that only selects a subset of columns
- C. A query run on a backup database
- D. A query that takes less than 1 millisecond
Explanation: A subquery (inner query) executes first, and its result is passed to the main query (outer query).
Question 27: Which data type is most appropriate for storing a variable-length string like an email address?
- A. CHAR
- B. VARCHAR β (correct answer)
- C. TEXT
- D. BLOB
Explanation: VARCHAR stores variable-length strings efficiently. CHAR is fixed-length (padding unused space), and TEXT is for massive paragraphs.
Question 28: What does the IFNULL() function do in MySQL?
- A. Checks if the database is null
- B. Deletes null rows
- C. Returns an alternative specified value if an expression evaluates to NULL β (correct answer)
- D. Prevents null values from being inserted
Explanation: IFNULL(column, 'N/A') will display 'N/A' whenever it encounters a NULL value in the result set.
Question 29: What is the purpose of the EXPLAIN keyword?
- A. It provides documentation for SQL syntax
- B. It reveals the execution plan of a query to help optimize performance β (correct answer)
- C. It formats the output nicely
- D. It prints table definitions
Explanation: Prepending EXPLAIN to a SELECT statement shows how MySQL intends to execute it, including which indexes it will use.
Question 30: When should you use CHAR(50) instead of VARCHAR(50)?
- A. When data length varies wildly
- B. When the data is guaranteed to be exactly 50 characters long every time β (correct answer)
- C. When storing binary data
- D. Never
Explanation: CHAR allocates exactly the specified size. If data lengths are strictly uniform (like fixed hashes or country codes), CHAR performs slightly faster.