**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Modifying Data: INSERT, UPDATE, DELETE **Topic:** Perform data manipulation tasks using INSERT, UPDATE, and DELETE commands. (Lab topic) **Objective:** By the end of this lab topic, you will be able to perform data manipulation tasks using INSERT, UPDATE, and DELETE commands in a database. **Materials needed:** * A database management system (DBMS) such as MySQL or PostgreSQL * A database with existing tables * A SQL client or IDE (Integrated Development Environment) ### Introduction In this lab topic, we will practice performing data manipulation tasks using the INSERT, UPDATE, and DELETE commands. These commands are essential for modifying data in a database. We will work with existing tables and practice inserting new records, updating existing records, and deleting records. ### Exercise 1: Inserting new records (INSERT INTO) For this exercise, we will create a new table called 'employees' and insert new records into it. **Step 1:** Create the 'employees' table: ```sql CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(255), department VARCHAR(255), salary DECIMAL(10, 2) ); ``` **Step 2:** Insert new records into the 'employees' table: ```sql INSERT INTO employees (employee_id, name, department, salary) VALUES (1, 'John Smith', 'Sales', 50000.00), (2, 'Jane Doe', 'Marketing', 60000.00), (3, 'Bob Johnson', 'IT', 70000.00); ``` **Step 3:** Verify the records have been inserted: ```sql SELECT * FROM employees; ``` ### Exercise 2: Updating existing records (UPDATE) For this exercise, we will update the 'salary' of an existing record in the 'employees' table. **Step 1:** Update the 'salary' of employee with 'employee_id' 2: ```sql UPDATE employees SET salary = 65000.00 WHERE employee_id = 2; ``` **Step 2:** Verify the record has been updated: ```sql SELECT * FROM employees WHERE employee_id = 2; ``` ### Exercise 3: Deleting records (DELETE) For this exercise, we will delete an existing record from the 'employees' table. **Step 1:** Delete the record with 'employee_id' 3: ```sql DELETE FROM employees WHERE employee_id = 3; ``` **Step 2:** Verify the record has been deleted: ```sql SELECT * FROM employees; ``` ### Practical Takeaways * The INSERT INTO command is used to add new records to a database table. * The UPDATE command is used to modify existing records in a database table. * The DELETE command is used to delete existing records from a database table. * Always use the WHERE clause with UPDATE and DELETE commands to avoid modifying or deleting unintended records. ### Additional Resources * For more information on SQL syntax and commands, visit the official MySQL documentation: [https://dev.mysql.com/doc/](https://dev.mysql.com/doc/) * For more information on PostgreSQL syntax and commands, visit the official PostgreSQL documentation: [https://www.postgresql.org/docs/](https://www.postgresql.org/docs/) **Do you have any questions or need help with this lab topic? Leave a comment below and we will be happy to assist you.** **What's Next?** In the next topic, we will cover 'Introduction to subqueries and their use cases' in Subqueries and Nested Queries. Subqueries are used to nest one query inside another and can be very powerful in retrieving data from a database.