**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Inserting, Updating, and Deleting Data **Topic:** Perform data manipulation tasks using INSERT, UPDATE, and DELETE. (Lab topic) ## Introduction In the previous topics, we covered the basics of SQLite and explored how to create, manage, and query databases. Now, it's time to dive deeper into data manipulation tasks using INSERT, UPDATE, and DELETE. This lab topic is designed to provide hands-on experience with these essential SQL commands. ## Objective By the end of this lab, you will be able to: * Insert new data into existing tables * Update existing records in tables * Delete records from tables * Understand how to handle conflicts and use the REPLACE command ## Prerequisites Before starting this lab, make sure you have: * A SQLite database management system installed on your computer (e.g., DB Browser for SQLite, SQLite Studio, or the SQLite command-line tool) * A basic understanding of SQLite and SQL syntax (covered in previous topics) ## Lab Exercise 1: Inserting New Data In this exercise, you will create a new table and insert data into it using the INSERT INTO command. ### Step 1: Create a new table Open your SQLite database management system and create a new table called "employees" with the following columns: * employee_id ( INTEGER PRIMARY KEY ) * name ( TEXT NOT NULL ) * department ( TEXT NOT NULL ) * salary ( REAL NOT NULL ) Use the following SQL command to create the table: ```sql CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY, name TEXT NOT NULL, department TEXT NOT NULL, salary REAL NOT NULL ); ``` ### Step 2: Insert data into the table Insert the following data into the "employees" table: | employee_id | name | department | salary | |------------|-------------|-------------|---------| | 1 | John Smith | Sales | 50000.0 | | 2 | Jane Doe | Marketing | 60000.0 | | 3 | Bob Brown | IT | 70000.0 | Use the following SQL command to insert the data: ```sql INSERT INTO employees (employee_id, name, department, salary) VALUES (1, 'John Smith', 'Sales', 50000.0), (2, 'Jane Doe', 'Marketing', 60000.0), (3, 'Bob Brown', 'IT', 70000.0); ``` ## Lab Exercise 2: Updating Existing Records In this exercise, you will update existing records in the "employees" table using the UPDATE command. ### Step 1: Update a single record Update the department of John Smith to "HR". Use the following SQL command to update the record: ```sql UPDATE employees SET department = 'HR' WHERE employee_id = 1; ``` ### Step 2: Update multiple records Update the salaries of all employees in the "IT" department by 10%. Use the following SQL command to update the records: ```sql UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT'; ``` ## Lab Exercise 3: Deleting Records In this exercise, you will delete records from the "employees" table using the DELETE command. ### Step 1: Delete a single record Delete the record of John Smith. Use the following SQL command to delete the record: ```sql DELETE FROM employees WHERE employee_id = 1; ``` ### Step 2: Delete multiple records Delete all records of employees in the "Marketing" department. Use the following SQL command to delete the records: ```sql DELETE FROM employees WHERE department = 'Marketing'; ``` ## Handling Conflicts and Using the REPLACE Command In SQLite, you can use the REPLACE command to handle conflicts when inserting data into a table with a PRIMARY KEY or UNIQUE constraint. ### Step 1: Create a table with a PRIMARY KEY constraint Create a new table called "products" with a PRIMARY KEY constraint on the "product_id" column: ```sql CREATE TABLE products ( product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL ); ``` ### Step 2: Insert data into the table Insert data into the "products" table using the INSERT INTO command: ```sql INSERT INTO products (product_id, name, price) VALUES (1, 'Product A', 10.99), (2, 'Product B', 9.99), (3, 'Product C', 12.99); ``` ### Step 3: Replace a record using the REPLACE command Replace the record of Product A with a new price: ```sql REPLACE INTO products (product_id, name, price) VALUES (1, 'Product A', 11.99); ``` ## Conclusion In this lab, you performed data manipulation tasks using INSERT, UPDATE, and DELETE. You also learned how to handle conflicts and use the REPLACE command. Remember to always use the correct syntax and consider the constraints on your tables when performing data manipulation tasks. **What's Next?** In the next topic, we will explore subqueries and their use cases. A subquery is a query nested inside another query. Subqueries can be used to return data that depends on the result of another query. **External Resources:** * SQLite Documentation: [INSERT INTO](https://www.sqlite.org/lang_insert.html) * SQLite Documentation: [UPDATE](https://www.sqlite.org/lang_update.html) * SQLite Documentation: [DELETE](https://www.sqlite.org/lang_delete.html) **Leave a Comment or Ask for Help:** If you have any questions or need help with this lab, please leave a comment below. We are here to help you master SQLite!