JOBSEEKER?

MySQL Interview Questions

Practice for MySQL interviews by solving TestDome questions. Our interview questions are used by more than 7,000 companies and 450,000 individual test takers.

Jobseekers: Certify Your Knowledge

Take a Certification Test

Companies: Use Our Tests for Screening

Buy a Pack Of Candidates

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

Pagination
   
Easy  

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.)

The query will return rows 5-15.
For the results of this query to be consistent, it must have an ORDER BY clause.
The OFFSET clause will have a negative performance impact when the offset value is large.
The LIMIT clause has a negative performance impact.
   


2. Item Archive

Triggers
   
Easy  

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

Date functions
   
Easy  

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.

   


4. Insert User

Insert Stored Procedure
   
Easy 

A company needs a stored procedure that will insert a new user with an appropriate type.

Consider the following tables:

TABLE userTypes
  id INTEGER NOT NULL PRIMARY KEY
  type VARCHAR(50) NOT NULL

TABLE users
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(50) NOT NULL,
  userTypeId INTEGER NOT NULL REFERENCES userTypes(id)

Finish the insertUser procedure so that it inserts a userwith these requirements:

  • id is auto incremented.
  • email is equal to the email parameter.
  • userTypeId is the id of the userTypes row whose type attribute is equal to the type parameter.

See the example case for more details.

MySQL 8.0  
 


  •   Example case: Wrong answer
  •   There is only one row in the userTypes table: Wrong answer
  •   There are several rows in the userTypes table: Wrong answer


5. Question Versions

CTE Recursion Select
   
Hard 

An application for testing programmers contains an initial question version and newer question versions, represented by the following schema:

TABLE questions
  id INTEGER NOT NULL PRIMARY KEY
  name VARCHAR(50) NOT NULL
  parentId INTEGER REFERENCES question(id)

The initial question version has parentId set to NULL, other question versions will have parentId set to the previous version. Each question version can be a parent to only one other question version.

Finish the findAllVersions stored procedure so that it returns, in any order, question version ids that are ancestors or descendants of the given questionId, including the questionId parameter.

See the example case for more details.

MySQL 8.0  
 


  •   Example case: Wrong answer
  •   Calling the procedure with the first question in the chain: Wrong answer
  •   Calling the procedure with the last question in the chain: Wrong answer
  •   Calling the procedure with the question in the middle of the chain: Wrong answer


If you feel ready, take one of our timed public MySQL Interview Questions tests:
MySQL

MySQL and SQL Online Test (Easy / Hard)

PHP

PHP, MySQL, and SQL Online Test (Easy / Hard)

Not exactly what you are looking for? Go to our For Jobseekers section.
Dashboard Start Trial Sign In Home Tour Tests Questions Pricing For Jobseekers