Ch 7 · SQL INSERT, UPDATE, DELETE · SQL
Topic 7 of 8 in SQL — Query a Real Database — 4 lessons.
Data Modification
Learn to modify data with INSERT, UPDATE, and DELETE statements. INSERT adds new rows, UPDATE changes values in rows that already exist and DELETE removes rows. The golden rule is to always add a WHERE clause to UPDATE and DELETE, because without one the change hits every row in the table. Almost every app that stores data relies on these three statements.
Data Modification in Code
The examples show each statement in turn: adding one employee, adding two at once, giving everyone in Engineering a 10% rise with salary * 1.10, and deleting a single employee by ID. ROUND keeps the new salaries as whole numbers. Notice that each INSERT lists its columns before the values, in the same order. The final SELECT shows the table after all the changes, and the commented-out line shows the danger of leaving out WHERE: every row would be deleted. Each run starts from a fresh copy of the sample data, so experiment freely.
Practice: Data Modification
Practise changing data safely with three tasks: add a new employee, give everyone in Sales (dept_id 2) a 5% pay rise and remove the employee with ID 99. A correct answer lists the columns in its INSERT, uses a WHERE clause so only the right rows change, and calculates the new salary from the old one. Test each WHERE with a SELECT first.
Solution & Challenge: Data Modification
Compare your three statements with the worked solution, especially the WHERE clauses. There is no employee with ID 99, so the DELETE changes nothing, and the final SELECT lets you check the rest. The challenge then asks you to move an employee to a new department using a transaction, a group of changes that either all succeed or all fail together. A correct answer updates the employee, records the move in a transfer log and finishes with COMMIT.