**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Subqueries and Advanced Data Retrieval **Topic:** Using subqueries with SELECT, INSERT, UPDATE, and DELETE. ### Introduction In the previous topic, we discussed the basics of subqueries and how to write scalar and table subqueries. In this topic, we will explore how to use subqueries with SELECT, INSERT, UPDATE, and DELETE statements. Subqueries are powerful tools that allow you to perform complex data retrieval and manipulation operations. ### Using Subqueries with SELECT You can use subqueries in the WHERE clause or in the FROM clause with the SELECT statement. When using subqueries in the WHERE clause, you can use comparison operators such as =, <, >, etc. ```sql -- Example of using subquery in the WHERE clause SELECT * FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders); ``` In this example, the subquery calculates the average total amount of all orders, and the main query returns all orders with a total amount greater than the average. When using subqueries in the FROM clause, you can treat the result of the subquery as a temporary table and use it to join with other tables. ```sql -- Example of using subquery in the FROM clause SELECT * FROM ( SELECT order_id, customer_id, total_amount FROM orders WHERE total_amount > 100 ) AS subquery JOIN customers ON subquery.customer_id = customers.customer_id; ``` In this example, the subquery selects orders with a total amount greater than 100 and joins the result with the customers table based on the customer_id. ### Using Subqueries with INSERT You can use subqueries with INSERT INTO statement to insert data into a table. ```sql -- Example of using subquery with INSERT INTO INSERT INTO orders (customer_id, order_date, total_amount) SELECT customer_id, '2022-01-01', 1000 FROM customers WHERE country = 'USA'; ``` In this example, the subquery selects customers from the USA and inserts new orders with a fixed order date and total amount. ### Using Subqueries with UPDATE You can use subqueries with UPDATE statement to update data in a table. ```sql -- Example of using subquery with UPDATE UPDATE orders SET total_amount = total_amount * 1.1 WHERE order_id IN ( SELECT order_id FROM orders WHERE total_amount > 1000 ); ``` In this example, the subquery selects orders with a total amount greater than 1000 and updates the total amount of these orders by multiplying it by 1.1. ### Using Subqueries with DELETE You can use subqueries with DELETE statement to delete data from a table. ```sql -- Example of using subquery with DELETE DELETE FROM orders WHERE order_id IN ( SELECT order_id FROM orders WHERE total_amount < 100 ); ``` In this example, the subquery selects orders with a total amount less than 100 and deletes these orders. ### Best Practices and Performance Considerations * Use subqueries instead of joins when you need to perform complex data retrieval operations. * Use subqueries instead of aggregate functions when you need to perform calculations on multiple columns. * Avoid using subqueries in the FROM clause if possible, as it can lead to performance issues. * Use EXISTS or IN instead of COUNT(*) or SUM() when checking for existence of data. For more information on subqueries and performance considerations, you can refer to the official SQLite documentation: [https://www.sqlite.org/lang_select.html](https://www.sqlite.org/lang_select.html) ### Conclusion In this topic, we have covered how to use subqueries with SELECT, INSERT, UPDATE, and DELETE statements. Subqueries are powerful tools that allow you to perform complex data retrieval and manipulation operations. Remember to use best practices and performance considerations when using subqueries. **What's Next?** In the next topic, we will explore good database design principles. [Introduction to good database design principles.](#) ( SQLite Database Design and Normalization) **Do you have any questions about this topic?** Leave a comment below or ask for help.