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
Pagination
Pagination is the process of dividing data into discrete pages. It can be used to reduce the overhead of fetching large quantities of data, and as such, it is important when developing systems that fetch remote data.
Easy
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
Triggers
A database trigger is a procedural code that is automatically executed in response to certain events on a particular table or view in a database. It's often used to enforce data integrity and, as such, is an important topic for all developers.
Easy
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
Date functions
Date functions are used to manipulate dates. As this is a common and often complicated task, it's important that every developer knows how to reuse a language or libraries provided for date functions.
Easy
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:
Not exactly what you are looking for? Go to our For Jobseekers section.
