Try to solve 3 MySQL interview questions below. Hints can help you find answers to questions you are having trouble with.

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.

   


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

MySQL and SQL Online Test (Easy)

PHP

PHP, MySQL, and SQL Online Test (Easy)

Not exactly what you are looking for? Go to our For Jobseekers section.