**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Subqueries and Advanced Data Retrieval **Topic:** Writing scalar and table subqueries In this topic, you will learn about scalar and table subqueries, their syntax, and use cases. By the end of this lesson, you will be able to write efficient subqueries to retrieve and manipulate data in your SQLite databases. **What is a Subquery?** A subquery, also known as a nested query or inner query, is a query nested inside another query, called the outer query. Subqueries can be used in various scenarios, including filtering data, aggregating data, and retrieving specific data. **Scalar Subqueries** A scalar subquery returns a single value, which can be used in the outer query. Scalar subqueries are typically used in the SELECT, WHERE, or HAVING clause. **Syntax:** ```sql SELECT column1, column2, (scalar_subquery) AS new_column FROM table_name; ``` **Example:** Suppose we have a table called "employees" with columns "name," "department," and "salary." We want to retrieve the names of employees who have a salary greater than the average salary of their department. ```sql SELECT name, department, salary, (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS avg_dept_salary FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department); ``` In this example, the subquery returns the average salary for each department, which is used to filter the employees who have a salary greater than the average. **Table Subqueries** A table subquery returns a set of rows, which can be used in the outer query. Table subqueries are typically used in the FROM, WHERE, or HAVING clause. **Syntax:** ```sql SELECT column1, column2 FROM table_name WHERE column_name IN (table_subquery); ``` **Example:** Suppose we have two tables, "orders" and "customers," and we want to retrieve the names of customers who have placed orders. ```sql SELECT name FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders ); ``` In this example, the subquery returns a set of customer IDs that have placed orders, which is used to filter the customers. **Key Concepts and Best Practices:** * Use scalar subqueries to retrieve a single value, and table subqueries to retrieve a set of rows. * Avoid using correlated subqueries, which can impact performance. * Use table subqueries in the FROM clause to improve performance. * Optimize your subqueries to reduce the number of rows being processed. **Practical Takeaways:** * Scalar subqueries can be used to retrieve a single value, such as an average or maximum value. * Table subqueries can be used to retrieve a set of rows, such as a list of customer IDs. * Correlated subqueries should be avoided in favor of joins or other query optimization techniques. **Additional Resources:** * SQLite documentation on subqueries: https://sqlite.org/lang_expr.html * SQL Fiddle for practicing subqueries: http://sqlfiddle.com/ **Exercise:** Try writing a scalar subquery to retrieve the maximum salary of employees in each department. Then, try writing a table subquery to retrieve a list of customer IDs who have placed orders. **Leave a comment below if you have any questions or need help with the exercise.** In the next topic, we will cover correlated subqueries and performance considerations.