MySQL & SQL

Guided Learning Questions

A comprehensive question bank covering DDL · DML · DCL · TCL

DDL DML DCL TCL

Over 50 detailed, instructional questions · No answers provided · Think before you query
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

Table of Contents

1 Introduction to Databases & MySQL Basics, environment, SQL sublanguages

2 DDL — Data Definition Language CREATE, ALTER, DROP, TRUNCATE, RENAME

3 DML — Data Manipulation Language INSERT, SELECT, UPDATE, DELETE

4 DCL — Data Control Language GRANT, REVOKE, user management

5 TCL — Transaction Control Language Transactions, COMMIT, ROLLBACK, isolation

6 Advanced SQL Techniques Subqueries, CTEs, views, procedures, optimization

How to use this guide: Work through each question independently. Write your SQL in a MySQL client and test against
a practice database. Pay close attention to the hint (■) for guided thinking before consulting documentation.

Page 2
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

■■ SECTION 1 — Introduction to Databases & MySQL

1.1 What Is a Database?

Q1.
Imagine you are tasked with storing and managing the student records of a university. What are the key
limitations of using spreadsheets or plain text files for this task? How does a Relational Database
Management System (RDBMS) like MySQL address these limitations?
■ Think about data integrity, concurrent access, querying flexibility, and scalability.

Q2.
Define the following core database concepts in your own words and provide a concrete real-world example
for each: (a) Database, (b) Table, (c) Row (Record), (d) Column (Attribute), (e) Primary Key, (f) Foreign
Key.
■ Use a consistent domain — e.g., an online bookstore — for all six examples.

Q3.
What is the difference between a Database Management System (DBMS) and a Relational Database
Management System (RDBMS)? Name three popular RDBMS products and explain what makes MySQL a
widely adopted choice for web applications.
■ Consider factors like licensing, performance, community support, and ease of use.

1.2 MySQL Environment & Basics

Q4.
Describe the purpose of the MySQL command-line client. Write the command you would use to (a) connect
to a local MySQL server as root, (b) list all existing databases, (c) select a specific database named
'company_db', and (d) display all tables within that database.
■ Remember that MySQL statements end with a semicolon.

Q5.
Explain the difference between MySQL's storage engines InnoDB and MyISAM. Why is InnoDB
recommended as the default for most production applications?
■ Focus on transactions, foreign key support, crash recovery, and locking mechanisms.

Q6.
What are SQL sublanguages? Briefly describe DDL, DML, DCL, and TCL, and give one example statement
for each. Why is understanding these categories important when designing database workflows?
■ Think of each sublanguage as a tool with a specific job in the database lifecycle.

■■ SECTION 2 — DDL: Data Definition Language

Page 3
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

2.1 CREATE — Designing Database Objects

Q7.
Write a CREATE DATABASE statement to create a new database called 'ecommerce_db'. Then write a
USE statement to set it as the active database. Why is it important to specify a character set and collation
when creating a database, and which values would you recommend for a multilingual application?
■ Look into CHARACTER SET utf8mb4 and COLLATE utf8mb4_unicode_ci.

Q8.
Design a 'customers' table for an e-commerce system with the following business requirements: each
customer must have a unique identifier auto-generated by the system, a required full name (up to 100
characters), a unique email address, an optional phone number, a required registration date that defaults to
the current timestamp, and a boolean 'is_active' flag defaulting to TRUE. Write the full CREATE TABLE
statement.
■ Use appropriate data types: INT, VARCHAR, DATE/TIMESTAMP, BOOLEAN. Don't forget AUTO_INCREMENT
and NOT NULL constraints.

Q9.
You are building a 'products' table. Write a CREATE TABLE statement that includes: a primary key, a
product name (required, indexed for fast search), a price using a data type appropriate for monetary values,
a stock quantity that cannot be negative, a category_id that references a 'categories' table, and a
created_at timestamp. Explain each constraint you apply and why it was chosen.
■ Consider DECIMAL for price, CHECK constraint for stock, and FOREIGN KEY for category_id.

Q10.
What is the difference between CHAR and VARCHAR data types in MySQL? Under what circumstances
would you choose CHAR(10) over VARCHAR(10), and vice versa? Provide two real-world column
examples for each.
■ Think about fixed-length codes (country codes, status flags) versus variable-length names.

Q11.
Explain the purpose of database indexes. Write a CREATE TABLE statement for an 'orders' table and then
add a composite index on (customer_id, order_date) using a separate CREATE INDEX statement. When
would a composite index be more efficient than two separate single-column indexes?
■ Consider query patterns: if you always filter by both columns together, a composite index is ideal.

Q12.
Write a CREATE TABLE statement for an 'employees' table that demonstrates the use of: (a) a PRIMARY
KEY on employee_id, (b) a UNIQUE constraint on email, (c) a NOT NULL constraint on last_name, (d) a
DEFAULT value for department, (e) a CHECK constraint ensuring salary > 0, and (f) a FOREIGN KEY
referencing a 'departments' table. Label and explain each constraint inline as a comment.
■ Use the CONSTRAINT keyword to name your constraints — named constraints are easier to manage later.

2.2 ALTER — Modifying Table Structure

Q13.

Page 4
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

Your 'customers' table is already in production. A new business requirement means you must add a
'date_of_birth' column (DATE, nullable) and a 'loyalty_points' column (INT, default 0, not null). Write the
ALTER TABLE statements for both changes. Why is modifying production tables risky, and what
precautions should you take?
■ Think about locking, downtime, and always testing on a staging environment first.

Q14.
Write ALTER TABLE statements to perform the following operations on an 'orders' table: (a) rename the
column 'order_dt' to 'order_date', (b) change the data type of 'total_amount' from FLOAT to DECIMAL(10,2),
(c) add a NOT NULL constraint to an existing nullable 'status' column (after ensuring no NULL values exist),
and (d) drop the column 'internal_notes'.
■ Use RENAME COLUMN, MODIFY COLUMN, and DROP COLUMN clauses. The order of operations matters.

Q15.
You need to add a FOREIGN KEY constraint to an existing 'order_items' table so that 'product_id'
references the 'products' table. Write the ALTER TABLE statement. What will happen if there are existing
rows in 'order_items' where the product_id value does not exist in the 'products' table?
■ MySQL will reject the ALTER if referential integrity is violated — you must clean the data first.

Q16.
Explain the difference between ALTER TABLE ... MODIFY COLUMN and ALTER TABLE ... CHANGE
COLUMN in MySQL. Write an example of each that changes the 'phone' column in a 'contacts' table.
■ CHANGE allows renaming; MODIFY does not.

2.3 DROP, TRUNCATE & RENAME

Q17.
Compare DROP TABLE, TRUNCATE TABLE, and DELETE FROM table (with no WHERE clause). How do
they differ in terms of: (a) what they remove, (b) whether the action can be rolled back in a transaction, (c)
speed, and (d) effect on AUTO_INCREMENT counters?
■ This is a classic interview question. Be precise about rollback behaviour under InnoDB.

Q18.
Write the SQL to safely drop a database called 'test_db' only if it exists. Then write the statement to drop a
'temp_logs' table only if it exists. Why is the IF EXISTS clause a best practice in scripts?
■ Omitting IF EXISTS causes an error if the object doesn't exist, breaking automated scripts.

Q19.
You have a 'reports_2023' table that you want to rename to 'archive_reports_2023'. Write the RENAME
TABLE statement. Could you use ALTER TABLE for this same task? Show both approaches.
■ Both RENAME TABLE and ALTER TABLE ... RENAME TO achieve the same result.

Q20.
You need to reset all data in a 'session_logs' table at the start of each day as part of a scheduled job. Write
the TRUNCATE statement. Explain why you would choose TRUNCATE over DELETE here, and describe
one scenario where DELETE would be the safer option.
■ DELETE is preferable when you need to preserve some rows, log deletions via triggers, or need rollback ability.

Page 5
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

✏■ SECTION 3 — DML: Data Manipulation Language

3.1 INSERT — Adding Data

Q21.
Write an INSERT statement to add a single new customer to the 'customers' table (id, full_name, email,
phone, registration_date, is_active). Then write a second INSERT that adds three customers in a single
statement. Why is multi-row INSERT more efficient than multiple single-row INSERT statements?
■ Multi-row INSERT reduces round trips to the server and transaction overhead.

Q22.
You are importing a batch of products from a CSV. Write an INSERT INTO ... SELECT statement that
copies all products from a staging table called 'products_import' into the live 'products' table, but only where
the price is greater than zero and the category_id exists in the 'categories' table.
■ Use a subquery or JOIN in the SELECT to filter valid category IDs.

Q23.
Explain the behaviour of INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE. Write an
example of each for inserting into a 'user_preferences' table that has a unique key on (user_id,
preference_key). When would you choose one over the other?
■ INSERT IGNORE silently skips duplicates; ON DUPLICATE KEY UPDATE lets you modify the existing row.

Q24.
Write an INSERT statement that inserts a new order into an 'orders' table, where the customer_id is
retrieved via a subquery that looks up the customer by email address rather than by a known numeric ID.
■ Embed a SELECT subquery inside the VALUES list or use INSERT ... SELECT.

3.2 SELECT — Querying Data

Q25.
Write SELECT queries against a 'products' table (columns: id, name, price, stock_qty, category_id,
created_at) to: (a) retrieve all columns, (b) retrieve only name and price, (c) retrieve distinct category IDs,
(d) retrieve products where stock_qty is 0, (e) retrieve products where price is between 10.00 and 50.00,
and (f) retrieve the 5 most expensive products.
■ Use DISTINCT, WHERE, BETWEEN, ORDER BY, and LIMIT.

Q26.
You need to find all customers whose email address ends in '@[Link]' and whose full_name contains
the word 'Ahmed'. Write the SELECT query using the LIKE operator. What are the performance implications
of leading wildcards (e.g., '%Ahmed') versus trailing wildcards ('Ahmed%')?
■ Leading wildcards prevent index usage; trailing wildcards can use an index if one exists on the column.

Page 6
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

Q27.
Write a SELECT query that calculates the total revenue per category from an 'order_items' table (columns:
order_id, product_id, quantity, unit_price) joined to a 'products' table. Show the category name, total
quantity sold, total revenue, and average unit price. Filter to show only categories with total revenue above
10,000, and sort by revenue descending.
■ You will need JOIN, GROUP BY, aggregate functions (SUM, AVG, COUNT), HAVING, and ORDER BY.

Q28.
Explain the difference between WHERE and HAVING in SQL. Write a query using both in the same
statement against an 'orders' table: use WHERE to exclude cancelled orders, and HAVING to show only
customers who have placed more than 3 orders.
■ WHERE filters rows before grouping; HAVING filters groups after aggregation.

Q29.
Write a SELECT query that uses a LEFT JOIN to list all customers from the 'customers' table along with the
count of their orders from the 'orders' table — including customers who have placed zero orders. What type
of result would you get with an INNER JOIN instead, and why?
■ LEFT JOIN preserves all rows from the left table; INNER JOIN only returns matching rows.

Q30.
Write a query using a subquery in the WHERE clause to find all products whose price is above the average
price of all products. Then rewrite the same query using a JOIN with a derived table (inline view). Which
approach is more readable? Which might perform better on a large dataset?
■ Both approaches are valid; the derived table approach can be more performant if the subquery is correlated.

Q31.
You need to generate a sales summary report. Write a SELECT query that: (a) joins 'orders', 'order_items',
'products', and 'customers' tables, (b) groups results by month and year, (c) calculates total orders, total
items sold, and total revenue per month, and (d) uses ORDER BY to sort chronologically.
■ Use DATE_FORMAT() or YEAR()/MONTH() functions for date grouping.

3.3 UPDATE — Modifying Data

Q32.
Write an UPDATE statement to increase the price of all products in category_id 5 by 10%. Then write a
second UPDATE that sets is_active = FALSE for all customers who have not placed any orders in the last
12 months. Use a subquery for the second statement.
■ Use price = price * 1.10 and a subquery with NOT IN or NOT EXISTS for the second query.

Q33.
You accidentally need to correct a customer's email address. Write a safe UPDATE statement that changes
the email only for the specific customer with id = 42. What is the danger of writing an UPDATE statement
without a WHERE clause, and how can MySQL's 'safe update mode' help prevent accidents?
■ Safe update mode (SQL_SAFE_UPDATES) prevents UPDATE/DELETE without a WHERE clause using a key
column.

Q34.

Page 7
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

Write an UPDATE statement that uses a JOIN to update the 'stock_qty' in the 'products' table by subtracting
the quantities found in a 'pending_shipments' table, but only for products where the current stock is
sufficient (stock_qty >= quantity to subtract).
■ MySQL supports UPDATE with JOIN: UPDATE products JOIN pending_shipments ...

Q35.
A business rule change requires that all orders with status 'processing' that were created more than 7 days
ago should be automatically updated to status 'delayed'. Write the UPDATE statement, and explain what
index on the 'orders' table would make this query most efficient.
■ An index on (status, order_date) would allow MySQL to quickly locate the matching rows.

3.4 DELETE — Removing Data

Q36.
Write a DELETE statement to remove all products from the 'products' table where stock_qty = 0 AND the
product has never appeared in any 'order_items' record. Use a subquery with NOT EXISTS. Why is it
important to verify with a SELECT before executing the DELETE?
■ Always run SELECT with the same WHERE clause first to preview what will be deleted.

Q37.
Explain the concept of cascading deletes. If the 'orders' table has a foreign key to 'customers' with ON
DELETE CASCADE, what happens when you delete a customer record? Write the CREATE TABLE
statement that would establish this cascading relationship.
■ CASCADE means dependent rows in child tables are automatically deleted when the parent is deleted.

Q38.
Write a DELETE statement that removes duplicate email entries from a 'subscribers' table, keeping only the
row with the lowest id for each email address. This is a classic problem — explain your approach step by
step before writing the query.
■ You cannot directly delete from a table you are selecting from in the same query; use a subquery or a JOIN-based
DELETE.

Q39.
Compare the performance implications of deleting 1 million rows using a single DELETE statement versus
using a loop that deletes 10,000 rows at a time (batched deletion). Why might batched deletion be preferred
in a production system?
■ Large single deletes cause long-running transactions, lock contention, and large undo log growth in InnoDB.

■ SECTION 4 — DCL: Data Control Language

4.1 GRANT — Assigning Permissions

Q40.

Page 8
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

Explain the principle of least privilege as it applies to MySQL user management. A junior developer on your
team needs read-only access to the 'reports' database. Write the CREATE USER and GRANT statements
to create this user and assign only SELECT privileges on all tables in the 'reports' database.
■ Use GRANT SELECT ON reports.* TO 'username'@'host'. Always specify the host.

Q41.
Your application connects to MySQL using a dedicated service account. Write the GRANT statement to
give this account INSERT, UPDATE, SELECT, and DELETE privileges on a specific table 'transactions' in
the 'banking_db' database. Why should application accounts never be granted full administrative (SUPER
or ALL) privileges?
■ Least privilege limits damage if credentials are compromised.

Q42.
What is the difference between GRANT ... ON *.* and GRANT ... ON database_name.*? Write one example
of each, and describe a real-world scenario where each level of privilege grant would be appropriate.
■ Global grants (*.* ) apply server-wide; database-level grants apply only to one database.

4.2 REVOKE & Managing Users

Q43.
A team member has left the company. Write the MySQL statements to: (a) revoke all privileges from their
account on the 'hr_db' database, (b) drop their user account, and (c) flush privileges to ensure changes take
effect immediately.
■ Use REVOKE ALL PRIVILEGES, DROP USER, and FLUSH PRIVILEGES.

Q44.
Write the query to view all current privilege grants for a specific user in MySQL. What system table or
command would you use to inspect which users have which permissions across all databases?
■ Use SHOW GRANTS FOR 'username'@'host'; and query the information_schema or [Link] tables.

■ SECTION 5 — TCL: Transaction Control Language

5.1 Transactions & COMMIT / ROLLBACK

Q45.
Explain what a database transaction is and why transactions are essential in a banking application. Define
the four ACID properties (Atomicity, Consistency, Isolation, Durability) and give a concrete example of how
each property protects data in a funds-transfer scenario.
■ Use the classic example: debit one account, credit another — both must succeed or both must fail.

Q46.

Page 9
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

Write a MySQL transaction that transfers 500 units of currency from account A (id=1) to account B (id=2) in
a 'bank_accounts' table. The transaction should: (a) begin explicitly, (b) debit account A, (c) credit account
B, (d) check that account A's balance did not go below zero, and (e) COMMIT on success or ROLLBACK
on failure.
■ Use START TRANSACTION, UPDATE statements, a conditional check, COMMIT, and ROLLBACK.

Q47.
What is the difference between an explicit transaction (using START TRANSACTION / COMMIT) and
MySQL's default autocommit behaviour? Write an example demonstrating how to disable autocommit,
perform multiple DML operations, and then commit them as a unit.
■ SET autocommit = 0 disables autocommit. Without explicit COMMIT, changes are not persisted.

Q48.
Explain what a SAVEPOINT is in MySQL and when you would use one. Write a transaction that inserts
three rows, sets a savepoint after the second insert, then rolls back only to the savepoint (undoing the third
insert) before committing.
■ SAVEPOINT name; ... ROLLBACK TO SAVEPOINT name; — useful for partial rollbacks in complex workflows.

5.2 Isolation Levels

Q49.
MySQL supports four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED,
REPEATABLE READ, and SERIALIZABLE. Briefly describe each level and the type of concurrency
anomaly (dirty read, non-repeatable read, phantom read) it prevents or allows. Which level is MySQL
InnoDB's default, and why is it a good balance for most applications?
■ The default is REPEATABLE READ. Understand that higher isolation levels reduce concurrency.

Q50.
What is a 'dirty read'? Write a scenario (not actual code, but a step-by-step description of two concurrent
sessions) that illustrates a dirty read, and explain which isolation level would prevent it.
■ A dirty read occurs when Session A reads data modified but not yet committed by Session B.

■ SECTION 6 — Advanced SQL Techniques

6.1 Subqueries & CTEs

Q51.
What is a correlated subquery? Write a correlated subquery that retrieves each employee along with the
average salary of their department, selecting only those employees who earn above their department's
average.
■ A correlated subquery references the outer query — it is re-evaluated for each row of the outer query.

Q52.

Page 10
MySQL & SQL — Guided Learning Questions DDL · DML · DCL · TCL

Rewrite the correlated subquery from the previous question using a Common Table Expression (CTE) with
the WITH clause. Compare the readability and potential performance differences between the two
approaches.
■ CTEs can improve readability significantly for complex queries and may be materialized by the optimizer.

6.2 Views, Stored Procedures & Functions

Q53.
Create a VIEW named 'active_customer_summary' that shows each active customer's id, full name, email,
and total number of orders. Explain the difference between a simple view and a complex view, and describe
when views are useful from a security and abstraction perspective.
■ A simple view (single table, no aggregates) is updatable; a complex view (JOINs, GROUP BY) is not.

Q54.
Write a stored procedure named 'update_product_price' that accepts a product_id and a
percentage_change parameter, and updates the product's price accordingly. Include error handling using
DECLARE ... HANDLER for the case where the product does not exist.
■ Use DELIMITER, BEGIN...END, and DECLARE CONTINUE HANDLER FOR NOT FOUND.

6.3 Performance & Optimization

Q55.
Explain how to use the EXPLAIN statement in MySQL. Run EXPLAIN on a SELECT query that joins three
tables and describe how to interpret the key columns: id, select_type, type, possible_keys, key, rows, and
Extra. What values in the 'type' column indicate poor performance?
■ The 'type' values from best to worst: system > const > eq_ref > ref > range > index > ALL. 'ALL' means full table
scan.

Q56.
You have a slow query that searches the 'orders' table by customer_id and order_date. Describe the
process of identifying why the query is slow using SHOW STATUS and EXPLAIN. Then write the CREATE
INDEX statement that would most likely improve performance, and explain your reasoning.
■ Consider which columns appear in WHERE, ORDER BY, and JOIN conditions when designing indexes.

Page 11