**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Subqueries and Nested Queries **Topic:** Write queries with subqueries for more advanced data retrieval and manipulation **Overview** In this lab topic, we will explore how to write queries with subqueries for advanced data retrieval and manipulation. We will cover various techniques and provide practical examples to help you grasp and apply the concepts effectively. **Querying with Subqueries** Subqueries can be used in various parts of a query, including the `SELECT`, `FROM`, `WHERE`, and `HAVING` clauses. We will examine each of these cases in detail, with examples to illustrate the concepts. **Using Subqueries in the `SELECT` Clause** Subqueries can be used in the `SELECT` clause to calculate additional columns or to perform calculations that involve multiple rows. Example: ```sql SELECT name, country, (SELECT COUNT(*) FROM cities WHERE country = countries.country) AS num_cities FROM countries; ``` This query uses a subquery to count the number of cities for each country and includes the result as an additional column in the output. **Using Subqueries in the `FROM` Clause** Subqueries can be used in the `FROM` clause to define a derived table, which can then be joined with other tables. Example: ```sql SELECT orders.order_id, customers.name FROM (SELECT order_id, customer_id FROM order_items WHERE quantity > 10) AS large_orders JOIN orders ON large_orders.order_id = orders.order_id JOIN customers ON large_orders.customer_id = customers.customer_id; ``` This query uses a subquery to define a derived table that contains order IDs with quantities greater than 10. The derived table is then joined with the `orders` and `customers` tables to retrieve additional information. **Using Subqueries in the `WHERE` Clause** Subqueries can be used in the `WHERE` clause to filter data based on a comparison with the result of a subquery. Example: ```sql SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = 'Sales'); ``` This query uses a subquery to calculate the average salary for employees in the Sales department and then filters the results to include only employees with a salary greater than the average. **Using Subqueries in the `HAVING` Clause** Subqueries can be used in the `HAVING` clause to filter grouped data based on a comparison with the result of a subquery. Example: ```sql SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING avg_salary > (SELECT AVG(salary) FROM employees WHERE department = 'Sales'); ``` This query uses a subquery to calculate the average salary for employees in the Sales department and then filters the grouped results to include only departments with an average salary greater than the average for the Sales department. **Best Practices** When using subqueries, keep the following best practices in mind: * Use derived tables instead of correlated subqueries whenever possible. * Avoid using subqueries in the `SELECT` clause when a join or grouping can be used instead. * Use indexing to optimize subquery performance. **Lab Exercises** 1. Write a query that uses a subquery in the `SELECT` clause to calculate the average order value for each customer. 2. Write a query that uses a subquery in the `FROM` clause to retrieve the top 10 orders by total value. 3. Write a query that uses a subquery in the `WHERE` clause to retrieve all employees who earn more than the average salary for their department. **Additional Resources** * For more information on subqueries, see the MySQL documentation: <https://dev.mysql.com/doc/refman/8.0/en/subqueries.html> * For more information on derived tables, see the PostgreSQL documentation: <https://www.postgresql.org/docs/current/queries-table-expressions.html> **Comments and Feedback** We encourage you to leave comments or ask for help if you have any questions or need further clarification on any of the topics covered in this lab topic. Please post your comments below. **What's Next** In the next topic, we will cover the principles of good database design, including normalization and denormalization. This will be covered in the section **Database Design and Normalization** under the topic **Principles of good database design**.