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 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.
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.
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.
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
CREATE item_delete ON item
FOR EACH ROW
INSERT INTO item_archive(name) VALUES ();