Test Your MySQL UPSERT Knowledge: SQL Quiz

Progress

True / False

In MySQL, the term UPSERT refers to an operation that inserts a new row or updates an existing row if a duplicate key is found.

True False

UPSERT is a combination of "UPDATE" and "INSERT". In MySQL, it allows for inserting a new row or updating an existing row if a conflict occurs due to a duplicate key.

True / False

The INSERT IGNORE statement in MySQL can be used to perform an UPSERT operation.

True False

INSERT IGNORE ignores duplicate key errors but does not update existing rows. It only inserts new rows without causing an error if a duplicate key is found.

True / False

In MySQL, the REPLACE INTO statement can be used to achieve similar functionality as UPSERT by deleting the existing row with the same primary key and inserting a new one.

True False

The REPLACE INTO statement deletes the existing row with the same primary key and inserts a new row, achieving a similar result to an UPSERT operation.

True / False

The INSERT ... ON DUPLICATE KEY UPDATE statement in MySQL is a common way to perform an UPSERT operation.

True False

The INSERT ... ON DUPLICATE KEY UPDATE statement allows for inserting a row or updating it if a duplicate key is found, making it a typical way to perform an UPSERT in MySQL.

True / False

When using INSERT ... ON DUPLICATE KEY UPDATE in MySQL, if no duplicate key is found, only an update operation is performed.

True False

If no duplicate key is found, an insert operation is performed. The update operation only occurs if a duplicate key is found.

True / False

The INSERT ... ON DUPLICATE KEY UPDATE statement in MySQL can only be used with tables that have primary keys.

The INSERT ... ON DUPLICATE KEY UPDATE statement in MySQL can only be used with tables that have primary keys. The INSERT ... ON DUPLICATE KEY UPDATE statement in MySQL can only be used with tables that have primary keys.

The INSERT ... ON DUPLICATE KEY UPDATE statement can be used with tables that have unique keys as well as primary keys.

True / False

In MySQL, the ON DUPLICATE KEY UPDATE clause can be used to conditionally update certain columns based on the values of other columns.

True False

The ON DUPLICATE KEY UPDATE clause allows for complex expressions and conditions to update certain columns based on the values of other columns.

True / False

Using INSERT ... ON DUPLICATE KEY UPDATE in MySQL can result in both insert and update operations being logged in the binary log if binary logging is enabled.

True False

Both insert and update operations are logged in the binary log if binary logging is enabled, making it possible to replicate the exact sequence of events.

True / False

MySQL's INSERT ... ON DUPLICATE KEY UPDATE statement can be used with partitioned tables.

True False

The INSERT ... ON DUPLICATE KEY UPDATE statement is fully compatible with partitioned tables in MySQL.

Q10

True / False

In MySQL, the ON DUPLICATE KEY UPDATE clause can include multiple column updates separated by commas.

True False

The ON DUPLICATE KEY UPDATE clause allows multiple column updates, and each column update is separated by a comma.

Q11

Single Choice

What does the UPSERT operation in MySQL do?

Deletes a row if it exist Inserts a row if it does not exist, otherwise updates it Updates a row if it exists, otherwise inserts itDeletes a row if it does not exist

An UPSERT operation in MySQL allows a row to be inserted if it does not exist, or updated if it already exists.

Q12

Single Choice

Which MySQL statement is used to perform an UPSERT operation

INSERT UPDATE DELETEINSERT ... ON DUPLICATE KEY UPDATE

The INSERT ... ON DUPLICATE KEY UPDATE statement is used in MySQL to perform an UPSERT operation.

Q13

Single Choice

MySQL, which clause can you use to handle duplicates during an insert operation?

ON DUPLICATE ON CONFLICT ON DUPLICATE KEY UPDATEON EXISTING KEY

The ON DUPLICATE KEY UPDATE clause is used to specify what should happen if a duplicate key is found during an insert operation.

Q14

Single Choice

Which of the following MySQL features allows you to perform an UPSERT operation when a unique key conflict occurs?

MERGE INSERT IGNORE INSERT ... ON DUPLICATE KEY UPDATEREPLACE INTO

The INSERT ... ON DUPLICATE KEY UPDATE feature in MySQL allows you to perform an UPSERT operation when a unique key conflict occurs.

Q15

Single Choice

What does the following MySQL statement do?

SQL Code

INSERT INTO users (id, name) VALUES (1, 'John') ON DUPLICATE KEY UPDATE name='John';

Only inserts a new user if the id does not exist Inserts a new user and updates the name if the id exists Deletes the user with id 1 and inserts a new userInserts a new user or does nothing if the id exists

This statement inserts a new user if the id does not exist or updates the name to 'John' if the id already exists.

Q16

Single Choice

Which of the following MySQL statements correctly performs an UPSERT operation?

UPDATE users SET name='Alice' WHERE id=1 INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name='Alice' REPLACE INTO users (id, name) VALUES (1, 'Alice')INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name='Alice'

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement is used to perform an UPSERT operation in MySQL.

Q17

Single Choice

How does MySQL handle an UPSERT operation when using the ON DUPLICATE KEY UPDATE clause and there are multiple unique constraints?

updates only the first unique constraint encountered raises an error if there are multiple unique constraints updates all unique constraints simultaneouslyupdates the row based on the primary key only

MySQL handles multiple unique constraints by updating all unique constraints simultaneously when using the ON DUPLICATE KEY UPDATE clause.

Q18

Single Choice

Consider the following MySQL table structure:

SQL Code

CREATE TABLE products (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(100),
 price DECIMAL(10,2),
 UNIQUE (product_name)
);
What will happen if you run the following UPSERT statement?
INSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 999.99)
ON DUPLICATE KEY UPDATE price=999.99;

The statement will fail due to unique constraint violation The statement will insert a new row The statement will update the price of the existing rowThe statement will insert a new row and update the price

The statement will update the price of the existing row if the product_id or product_name already exists.

Q19

Single Choice

Which MySQL statement is equivalent to performing an UPSERT operation using INSERT ... ON DUPLICATE KEY UPDATE but deletes the existing row before insertion?

MERGE INTO INSERT IGNORE REPLACE INTOUPDATE ... INSERT

The REPLACE INTO statement deletes the existing row and inserts a new row, making it equivalent to an UPSERT operation but with deletion.

Q20

Single Choice

MySQL, what will be the result of the following UPSERT operation when the table employees has a unique constraint on the email column?

SQL Code

INSERT INTO employees (id, name, email) VALUES (2, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE name = 'Alice';

new row will be inserted regardless of the unique constraint row with the duplicate email will be updated with the new name statement will fail due to the unique constraint violation on the emailnew row will be inserted and the existing row will be deleted

The statement will update the row with the duplicate email with the new name, as specified in the ON DUPLICATE KEY UPDATE clause.

Q21

Multiple Choice

Identify the correct SQL statement to perform an UPSERT operation using INSERT...ON DUPLICATE KEY UPDATE.

SQL Code

INSERT INTO users (user_id, username, email)
VALUES (1, 'jdoe', 'jdoe@example.com')
ON DUPLICATE KEY UPDATE email = 'jdoe@example.com';

INSERT INTO users (user_id, username, email) VALUES (1, 'jdoe', 'jdoe@example.com') ON DUPLICATE KEY UPDATE email = 'jdoe@example.com'; INSERT INTO users (user_id, username, email) VALUES (1, 'jdoe', 'jdoe@example.com') ON DUPLICATE UPDATE email = 'jdoe@example.com'; UPSERT INTO users (user_id, username, email) VALUES (1, 'jdoe', 'jdoe@example.com') ON DUPLICATE KEY UPDATE email = 'jdoe@example.com';UPDATE users SET email = 'jdoe@example.com' WHERE user_id = 1;

This UPSERT operation inserts a new row or updates the existing row if a duplicate key (user_id) is found.

Q22

Multiple Choice

Choose the correct SQL statement to perform an UPSERT operation using REPLACE INTO.

SQL Code

REPLACE INTO products (product_id, product_name, price)
VALUES (1, 'Laptop', 1500);

REPLACE INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 1500); INSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 1500) ON DUPLICATE KEY REPLACE price = 1500; UPSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 1500);REPLACE products SET product_name = 'Laptop', price = 1500 WHERE product_id = 1;

REPLACE INTO first deletes the existing row with a matching primary key (if any) and then inserts the new row.

Q23

Multiple Choice

Determine the correct SQL statement to update existing data or insert a new row if no matching data is found.

SQL Code

INSERT INTO inventory (item_id, stock)
VALUES (1001, 50)
ON DUPLICATE KEY UPDATE stock = stock + 50;

INSERT INTO inventory (item_id, stock) VALUES (1001, 50) ON DUPLICATE KEY UPDATE stock = stock + 50; REPLACE INTO inventory (item_id, stock) VALUES (1001, stock + 50); UPSERT INTO inventory (item_id, stock) VALUES (1001, 50) ON DUPLICATE KEY UPDATE stock = stock + 50;UPDATE inventory SET stock = stock + 50 WHERE item_id = 1001;

This SQL statement adds new stock if the item_id already exists; otherwise, it inserts a new row.

Q24

Multiple Choice

Which SQL statement correctly performs an UPSERT operation when dealing with multiple rows of data?

SQL Code

INSERT INTO orders (order_id, order_date, total)
VALUES (1, '2024-01-01', 500),
(2, '2024-01-02', 750)
ON DUPLICATE KEY UPDATE total = VALUES(total);

INSERT INTO orders (order_id, order_date, total) VALUES (1, '2024-01-01', 500), (2, '2024-01-02', 750) ON DUPLICATE KEY UPDATE total = VALUES(total); REPLACE INTO orders (order_id, order_date, total) VALUES (1, '2024-01-01', 500), (2, '2024-01-02', 750); INSERT INTO orders (order_id, order_date, total) VALUES (1, '2024-01-01', 500), (2, '2024-01-02', 750) ON DUPLICATE UPDATE total = VALUES(total);UPSERT INTO orders (order_id, order_date, total) VALUES (1, '2024-01-01', 500), (2, '2024-01-02', 750) ON DUPLICATE KEY UPDATE total = VALUES(total);

This SQL statement attempts to insert multiple rows and updates the total if the order_id already exists.

Q25

Multiple Choice

Select the correct SQL statement to insert or update a row while setting a default value if the row does not exist.

SQL Code

INSERT INTO settings (setting_name, setting_value)
VALUES ('theme', 'dark')
ON DUPLICATE KEY UPDATE setting_value = 'dark';

INSERT INTO settings (setting_name, setting_value) VALUES ('theme', 'dark') ON DUPLICATE KEY UPDATE setting_value = 'dark'; REPLACE INTO settings (setting_name, setting_value) VALUES ('theme', 'dark'); UPSERT INTO settings (setting_name, setting_value) VALUES ('theme', 'dark');UPDATE settings SET setting_value = 'dark' WHERE setting_name = 'theme';

This SQL statement ensures that the 'theme' setting is set to 'dark' whether it is inserted or updated.

Q26

Multiple Choice

Determine the correct SQL statement to perform an UPSERT operation with a calculated value in the UPDATE clause.

SQL Code

INSERT INTO accounts (account_id, balance)
VALUES (101, 500)
ON DUPLICATE KEY UPDATE balance = balance + 500;

INSERT INTO accounts (account_id, balance) VALUES (101, 500) ON DUPLICATE KEY UPDATE balance = balance + 500; REPLACE INTO accounts (account_id, balance) VALUES (101, balance + 500); UPSERT INTO accounts (account_id, balance) VALUES (101, 500) ON DUPLICATE KEY UPDATE balance = balance + 500;UPDATE accounts SET balance = balance + 500 WHERE account_id = 101;

The UPDATE clause in this UPSERT operation calculates the new balance by adding 500 to the existing balance if the account_id already exists.

Q27

Multiple Choice

Which SQL statement correctly uses the INSERT...ON DUPLICATE KEY UPDATE syntax to handle a unique constraint violation?

SQL Code

INSERT INTO email_subscribers (email, subscription_date)
VALUES ('user@example.com', '2024-01-01')
ON DUPLICATE KEY UPDATE subscription_date = '2024-01-01';

INSERT INTO email_subscribers (email, subscription_date) VALUES ('user@example.com', '2024-01-01') ON DUPLICATE KEY UPDATE subscription_date = '2024-01-01'; REPLACE INTO email_subscribers (email, subscription_date) VALUES ('user@example.com', '2024-01-01'); UPSERT INTO email_subscribers (email, subscription_date) VALUES ('user@example.com', '2024-01-01');UPDATE email_subscribers SET subscription_date = '2024-01-01' WHERE email = 'user@example.com';

This SQL statement attempts to insert a new subscriber but updates the subscription_date if the email already exists in the table.

Q28

Multiple Choice

Identify the correct SQL statement to perform an UPSERT operation that increments a counter in the UPDATE clause.

SQL Code

INSERT INTO page_views (page_id, view_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

INSERT INTO page_views (page_id, view_count) VALUES (1, 1) ON DUPLICATE KEY UPDATE view_count = view_count + 1; REPLACE INTO page_views (page_id, view_count) VALUES (1, view_count + 1); UPSERT INTO page_views (page_id, view_count) VALUES (1, 1) ON DUPLICATE KEY UPDATE view_count = view_count + 1;UPDATE page_views SET view_count = view_count + 1 WHERE page_id = 1;

This UPSERT operation inserts a new row with view_count set to 1 or increments the view_count by 1 if the page_id already exists.

Q29

Multiple Choice

Select the correct SQL statement to perform an UPSERT operation using INSERT IGNORE.

SQL Code

INSERT IGNORE INTO orders (order_id, customer_id, order_date)
VALUES (1, 101, '2024-01-01');

INSERT IGNORE INTO orders (order_id, customer_id, order_date) VALUES (1, 101, '2024-01-01'); INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 101, '2024-01-01') ON DUPLICATE KEY IGNORE; UPSERT IGNORE INTO orders (order_id, customer_id, order_date) VALUES (1, 101, '2024-01-01');REPLACE INTO orders (order_id, customer_id, order_date) VALUES (1, 101, '2024-01-01');

The INSERT IGNORE statement ignores any errors caused by duplicate keys, effectively performing an UPSERT without updating existing rows.

Q30

Multiple Choice

Determine the correct SQL statement to perform an UPSERT operation that conditionally updates only if a column has a specific value.

SQL Code

INSERT INTO products (product_id, stock)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE stock = CASE
WHEN stock < 50 THEN stock + 50
ELSE stock END;

INSERT INTO products (product_id, stock) VALUES (101, 50) ON DUPLICATE KEY UPDATE stock = CASE WHEN stock < 50 THEN stock + 50 ELSE stock END; REPLACE INTO products (product_id, stock) VALUES (101, 50) WHERE stock < 50; UPSERT INTO products (product_id, stock) VALUES (101, 50) ON DUPLICATE KEY UPDATE stock = CASE WHEN stock < 50 THEN stock + 50 ELSE stock END;UPDATE products SET stock = CASE WHEN stock < 50 THEN stock + 50 ELSE stock END WHERE product_id = 101;

This UPSERT operation conditionally updates the stock only if the existing stock is less than 50, otherwise it leaves the stock unchanged.

MySQL Database Quiz Questions