Need to practice your MySQL skills for an upcoming job interview? Try solving these MySQL interview questions that test your knowledge of relational database concepts and MySQL features such as triggers, pagination, date functions, and other skills. We’ll provide feedback on your answers, and you can use a hint if you get stuck.
These MySQL interview questions are examples of real tasks used by employers to screen job candidates such as database administrators, back-end developers, data analysts, and others that require knowledge of MySQL in order to interface with and access a MySQL database efficiently.
1. Paginate Users
Consider the following table:
CREATE TABLE users (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
The following query is used to paginate the table:
SELECT * FROM users ORDER BY username LIMIT 5 OFFSET 15;
Select all the true statements about this method of pagination.
(Select all acceptable answers.)
2. Item Archive
Fill in the blanks so that the trigger item_delete inserts name from item table to the item_archive table, after a row from the table item is deleted.
CREATE TABLE item (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
quantity INT NOT NULL
);
CREATE TABLE item_archive (
archive_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
DELIMITER $$
CREATE item_delete ON item
FOR EACH ROW
BEGIN
INSERT INTO item_archive(name) VALUES ();
END;
$$
DELIMITER ;
See the example case for more details.
3. Registrations
Consider the following DDL statement:
CREATE TABLE registrations (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
create_date DATE NOT NULL,
last_activity DATE NOT NULL
);
Fill in the blanks so that the following select returns all registrations that were last active at least 30 days after being created.
SELECT name, create_date, last_activity FROM registrations
WHERE >= (, INTERVAL 30 DAY);
See the example case for more details.
