Quiz

MySQL Final Quiz

Test your foundational MySQL knowledge with 10 untimed multiple-choice questions covering databases, queries, constraints, aggregation, joins, and safe data changes.

Quiz overview

This final assessment contains 10 multiple-choice questions covering the main foundational MySQL outcomes: database structure, table definitions, data types, constraints, data retrieval, data modification, conditions, aggregation, grouping, and joins.

The quiz is untimed. Each question has one correct answer. Select the best option for every question, then use the platform's submit action when you are finished. After submission, review your score, the correct answers, and the explanations. If the learning platform supports saved progress, you may leave and return before completing the quiz.

1. Start the quiz with the platform's start button.
2. Read each question and select one answer.
3. Use question navigation to check or change responses before submitting.
4. Submit the completed quiz.
5. Review the score and topic-based guidance, then revisit any weak areas.

MySQL knowledge check

Question 1: Database terminology

Which description is correct?

1. A database is one row, and a table is one column.
2. A database or schema contains tables; a table contains rows and columns; a row is a record, and a column represents a field or attribute.
3. A column contains several unrelated databases.
4. A DBMS is a single value stored in a field.

Correct answer: 2

Explanation: MySQL is a relational database management system, or DBMS. It manages databases, also called schemas in many MySQL contexts. A database contains tables, tables contain rows, and columns describe the values stored in each row. A record is another name for a row, while a field commonly refers to a value or attribute associated with a column. Option 1 reverses the hierarchy, option 3 misrepresents a column, and option 4 confuses software with stored data.

Question 2: Creating a table with types and constraints

Which statement creates an automatically numbered identifier and requires every customer name?

1. CREATE TABLE customers (customer_id VARCHAR(20), name TEXT);
2. CREATE TABLE customers (customer_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);
3. CREATE TABLE customers (customer_id DATE DEFAULT NULL, name INT UNIQUE);
4. CREATE TABLE customers (customer_id BOOLEAN, name DECIMAL);

Correct answer: 2

Explanation: INT AUTO_INCREMENT PRIMARY KEY provides an integer identifier generated by MySQL and uniquely identifies each row. VARCHAR(100) NOT NULL stores variable-length text and rejects missing names. Numeric values commonly use INT, while precise monetary values commonly use DECIMAL. Date-only values use DATE; date and time use DATETIME. MySQL has boolean-like behavior through BOOLEAN, commonly represented as 0 or 1. Option 1 does not generate identifiers or require names, and options 3 and 4 use unsuitable types for this design.

Question 3: Selecting, filtering, sorting, and limiting

Which query returns at most 10 customer names and email addresses for names beginning with A, sorted alphabetically?

1. SELECT * FROM customers;
2. SELECT name, email FROM customers WHERE name LIKE 'A%' ORDER BY name ASC LIMIT 10;
3. SELECT name, email FROM customers HAVING name = 'A%' LIMIT name;
4. SELECT customers FROM name WHERE email ORDER BY 10;

Correct answer: 2

Explanation: SELECT name, email chooses specific columns, FROM customers identifies the table, WHERE filters rows, LIKE 'A%' matches names beginning with A, ORDER BY name ASC sorts ascending, and LIMIT 10 caps the result. An alias can make an expression easier to read, for example COUNT(*) AS order_count. Option 1 selects every column and applies no filter; options 3 and 4 use clauses and syntax incorrectly.

Question 4: Safe data modification

Which statement safely changes the email address for only the customer whose identifier is 1?

1. UPDATE customers SET email = 'ava.lee@example.com';
2. UPDATE customers SET email = 'ava.lee@example.com' WHERE customer_id = 1;
3. DELETE FROM customers WHERE email = 'ava.lee@example.com';
4. INSERT INTO customers SET email = 'ava.lee@example.com';

Correct answer: 2

Explanation: UPDATE changes existing rows, SET supplies the new value, and the primary-key condition in WHERE targets one row. An UPDATE without WHERE can change every row. The same safety rule applies to DELETE: DELETE FROM customers; removes every row, while DELETE FROM customers WHERE customer_id = 1; targets one identified row. Option 3 deletes rather than updates, and option 4 is not the intended update operation.

Question 5: Conditions and pattern matching

Which condition finds products priced from 10 through 50 inclusive, whose category is either books or games, and whose name is not missing?

1. price BETWEEN 10 AND 50 AND category IN ('books', 'games') AND name IS NOT NULL
2. price > 10 OR price < 50 OR category = ('books', 'games')
3. price LIKE 10-50 AND category BETWEEN books AND games AND name = NULL
4. NOT price BETWEEN 10 AND 50 AND category NOT IN ('books', 'games')

Correct answer: 1

Explanation: BETWEEN includes both endpoints, IN checks membership in a set, and IS NOT NULL checks that a value exists. Comparison operators include =, <>, >, <, >=, and <=. Logical operators combine conditions: AND requires all conditions, OR accepts alternatives, and NOT reverses a condition. NULL must be tested with IS NULL or IS NOT NULL, not = NULL. The LIKE operator is for patterns, with % matching any sequence and _ matching one character.

Question 6: Aggregation and grouped filtering

Which query returns each customer with more than one order?

1. SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;
2. SELECT customer_id, COUNT(*) FROM orders WHERE COUNT(*) > 1;
3. SELECT customer_id FROM orders ORDER BY COUNT(*) > 1;
4. SELECT COUNT(customer_id), SUM(customer_id) FROM orders;

Correct answer: 1

Explanation: COUNT counts rows, SUM totals numeric values, AVG calculates an average, and MIN and MAX find the smallest and largest values. GROUP BY customer_id produces one group per customer. HAVING filters groups after aggregation, so it is appropriate for COUNT(*) > 1. WHERE filters individual rows before grouping and cannot normally contain an aggregate condition. Option 3 does not group rows correctly, and option 4 does not produce one result per customer.

Question 7: Primary keys, foreign keys, and joins

Which query connects orders to customers through the customer's primary key and the order's foreign key?

1. SELECT o.order_id, c.name FROM orders AS o INNER JOIN customers AS c ON o.customer_id = c.customer_id;
2. SELECT o.order_id, c.name FROM orders AS o INNER JOIN customers AS c ON o.order_id = c.name;
3. SELECT o.order_id, c.name FROM orders AS o WHERE o.customer_id;
4. SELECT * FROM orders LEFT JOIN customers;

Correct answer: 1

Explanation: The ON clause connects the foreign key orders.customer_id to the primary key customers.customer_id. An INNER JOIN returns only rows with matching records in both tables. A LEFT JOIN returns every row from the left table and matching rows from the right table, including left-side rows without a match. Joining normalized tables avoids repeating customer details in every order. Option 2 joins unrelated columns, option 3 is not a join, and option 4 lacks a valid join condition.

Question 8: Database and table definition commands

Which sequence creates a database, selects it, and adds a column to an existing table?

1. CREATE DATABASE shop_db; USE shop_db; ALTER TABLE customers ADD COLUMN signup_date DATE;
2. CREATE TABLE shop_db; SELECT shop_db; CHANGE DATABASE customers ADD signup_date;
3. DROP DATABASE shop_db; USE customers; SELECT TABLE customers;
4. INSERT DATABASE shop_db; ALTER DATABASE customers WHERE signup_date;

Correct answer: 1

Explanation: CREATE DATABASE creates a database, USE selects the schema for subsequent statements, and ALTER TABLE changes an existing table structure. A concrete example is ALTER TABLE customers ADD COLUMN signup_date DATE;. To remove a database, use DROP DATABASE shop_db;, but this permanently removes the database and its tables, so it requires special care. Options 2 and 4 use nonexistent or incorrect command forms, while option 3 selects and drops the wrong objects.

Question 9: Predicting grouped output

Suppose orders contains customer IDs 1, 1, 2, 3, 3, 3. What does this query return?

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

1. One row with a total count of 6.
2. Three rows: customer 1 has 2, customer 2 has 1, and customer 3 has 3.
3. Six rows, each with a count of 1.
4. No rows because COUNT requires SUM.

Correct answer: 2

Explanation: GROUP BY customer_id creates one group for each distinct customer ID. COUNT(*) counts rows in each group, so the result is three grouped rows. An alias, order_count, labels the calculated column. Option 1 would be closer to a query without GROUP BY, option 3 ignores grouping, and option 4 is false because COUNT works independently.

Question 10: Constraints and data integrity

Which constraint combination best prevents duplicate customer emails while allowing MySQL to generate a unique numeric identifier?

1. customer_id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE
2. customer_id VARCHAR(255), email TEXT DEFAULT NULL
3. customer_id DATE, email INT NOT NULL
4. customer_id INT, email VARCHAR(255) AUTO_INCREMENT

Correct answer: 1

Explanation: PRIMARY KEY identifies each row and is unique; AUTO_INCREMENT generates successive numeric identifiers. UNIQUE prevents duplicate email values. NOT NULL requires a value, and DEFAULT supplies a value when one is omitted. A FOREIGN KEY would enforce a relationship to a key in another table. These constraints protect data integrity by rejecting invalid, missing, or conflicting values. The other options use unsuitable types or place AUTO_INCREMENT on a text column.

Score summary and review guidance

Count one point for each correct answer. Your maximum score is 10.

• 9–10: Strong foundation. Practise writing complete queries and handling edge cases.
• 7–8: Good progress. Review the explanations for missed syntax and safety decisions.
• 5–6: Revisit filtering, constraints, aggregation, and joins before advancing.
• 0–4: Repeat the introductory lessons and practise database, table, and CRUD commands with small datasets.

For topic-based review, revisit database hierarchy for question 1; data types and constraints for questions 2 and 10; SELECT, filtering, sorting, aliases, and limits for questions 3 and 5; safe INSERT, UPDATE, and DELETE work for question 4; aggregation and grouping for questions 6 and 9; joins for question 7; and database or table definition commands for question 8.

MySQL command and clause reference

Constraint reference

Common troubleshooting reminders

• If an UPDATE or DELETE affects every row, inspect the statement for a missing or overly broad WHERE condition. First run a matching SELECT to verify the target rows.
• If a missing-value query returns no expected rows, replace column = NULL with column IS NULL.
• If a grouped query fails, include selected non-aggregate columns in GROUP BY and move aggregate filters to HAVING.
• If a join creates unexpected duplicates, verify the ON condition and join primary-key and foreign-key columns rather than unrelated or non-unique fields.
• If an insert violates a key, check PRIMARY KEY and UNIQUE values, or allow an AUTO_INCREMENT identifier to be generated.
• If LIKE finds too few values, check the wildcard: % matches any sequence of characters and _ matches one character.

Continue practising with the MySQL Final Quiz when you are ready to retake the assessment.