**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Subqueries and Nested Queries **Topic:** Using subqueries with SELECT, INSERT, UPDATE, and DELETE In this topic, we'll explore how to use subqueries with various SQL statements, including `SELECT`, `INSERT`, `UPDATE`, and `DELETE`. This will help you to enhance your skills in creating complex queries and tackling real-world problems. **Using Subqueries with SELECT Statements** A subquery can be used in the `WHERE` clause of a `SELECT` statement to filter data based on the results of the subquery. Here's an example: ```sql SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); ``` In this example, the subquery `SELECT AVG(salary) FROM employees` calculates the average salary of all employees. The outer query then selects all employees whose salary is greater than the average salary. **Using Subqueries with INSERT Statements** A subquery can be used in the `INSERT` statement to insert data from one or more tables. Here's an example: ```sql INSERT INTO employees_backup (employee_id, name, salary) SELECT employee_id, name, salary FROM employees WHERE department = 'Sales'; ``` In this example, the subquery `SELECT employee_id, name, salary FROM employees WHERE department = 'Sales'` selects the required data from the `employees` table. The outer query then inserts this data into the `employees_backup` table. **Using Subqueries with UPDATE Statements** A subquery can be used in the `UPDATE` statement to update data based on the results of the subquery. Here's an example: ```sql UPDATE orders SET total_amount = total_amount * (SELECT discount_rate FROM discounts WHERE product_id = orders.product_id); ``` In this example, the subquery `SELECT discount_rate FROM discounts WHERE product_id = orders.product_id` calculates the discount rate for each order. The outer query then updates the `total_amount` column of the `orders` table based on the calculated discount rate. **Using Subqueries with DELETE Statements** A subquery can be used in the `DELETE` statement to delete data based on the results of the subquery. Here's an example: ```sql DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM employees_backup); ``` In this example, the subquery `SELECT employee_id FROM employees_backup` selects the `employee_id` values from the `employees_backup` table. The outer query then deletes the corresponding rows from the `employees` table. **Key Concepts and Takeaways** * Subqueries can be used with various SQL statements, including `SELECT`, `INSERT`, `UPDATE`, and `DELETE`. * Subqueries can be used in the `WHERE` clause of a `SELECT` statement to filter data based on the results of the subquery. * Subqueries can be used in the `INSERT` statement to insert data from one or more tables. * Subqueries can be used in the `UPDATE` statement to update data based on the results of the subquery. * Subqueries can be used in the `DELETE` statement to delete data based on the results of the subquery. **Additional Resources** For more information on subqueries, you can refer to the following resources: * [SQL Tutorial by W3Schools](https://www.w3schools.com/sql/) * [Subqueries by MySQL](https://dev.mysql.com/doc/refman/8.0/en/subqueries.html) * [Subqueries by PostgreSQL](https://www.postgresql.org/docs/current/functions-subquery.html) **Exercise** Try to use subqueries with different SQL statements, such as `SELECT`, `INSERT`, `UPDATE`, and `DELETE`. Use the sample databases provided in the course materials to practice. **Leave a Comment or Ask for Help** If you have any questions or need further clarification on any of the concepts discussed in this topic, please leave a comment below. We'll be happy to help. In the next topic, we'll discuss the principles of good database design. This is an essential topic that will help you design and develop efficient and scalable databases.