SQL: Practice Exercises for DELETE Statement
If you want to test your skills using the SQL DELETE statement, try some of our practice exercises.
These practice exercises allow you to test your skills with the DELETE statement. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer.
Get started!
Practice Exercise #1:
Based on the employees table, delete all employee records whose salary is greater than $60,000:
CREATE TABLE employees ( employee_number int NOT NULL, last_name char(50) NOT NULL, first_name char(50) NOT NULL, salary int, dept_id int, CONSTRAINT employees_pk PRIMARY KEY (employee_number) ); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1001, 'Smith', 'John', 62000, 500); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1002, 'Anderson', 'Jane', 57500, 500); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1003, 'Everest', 'Brad', 71000, 501); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1004, 'Horvath', 'Jack', 42000, 501);
Solution for Practice Exercise #1:
The following DELETE statement would delete these records from the employees table:
Try ItDELETE FROM employees WHERE salary > 60000;
There would be 2 records deleted and the employees table would now look like this:
employee_number | last_name | first_name | salary | dept_id |
---|---|---|---|---|
1002 | Anderson | Jane | 57500 | 500 |
1004 | Horvath | Jack | 42000 | 501 |
Practice Exercise #2:
Based on the suppliers table, delete the supplier record whose state is 'California' and supplier_name is not Google:
CREATE TABLE suppliers ( supplier_id int NOT NULL, supplier_name char(50) NOT NULL, city char(50), state char(50), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) ); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (100, 'Microsoft', 'Redmond', 'Washington'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (200, 'Google', 'Mountain View', 'California'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (300, 'Oracle', 'Redwood City', 'California'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (400, 'Kimberly-Clark', 'Irving', 'Texas'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (500, 'Tyson Foods', 'Springdale', 'Arkansas'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (600, 'SC Johnson', 'Racine', 'Wisconsin'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (700, 'Dole Food Company', 'Westlake Village', 'California'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (800, 'Flowers Foods', 'Thomasville', 'Georgia'); INSERT INTO suppliers (supplier_id, supplier_name, city, state) VALUES (900, 'Electronic Arts', 'Redwood City', 'California');
Solution for Practice Exercise #2:
The following SQL DELETE statement would delete these records from the suppliers table:
Try ItDELETE FROM suppliers WHERE state = 'California' AND supplier_name <> 'Google';
There would be 3 records deleted and the suppliers table would now look like this:
supplier_id | supplier_name | city | state |
---|---|---|---|
100 | Microsoft | Redmond | Washington |
200 | Mountain View | California | |
400 | Kimberly-Clark | Irving | Texas |
500 | Tyson Foods | Springdale | Arkansas |
600 | SC Johnson | Racine | Wisconsin |
800 | Flowers Foods | Thomasville | Georgia |
Advertisements