**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Subqueries and Advanced Data Retrieval **Topic:** Write queries with subqueries for advanced data retrieval.(Lab topic) **Objective:** In this lab, you will learn how to write advanced queries using subqueries in SQLite to retrieve and manipulate data from multiple tables. By the end of this topic, you will be able to write subqueries to perform various tasks such as filtering data, selecting data from multiple tables, and retrieving aggregated data. **Writing Queries with Subqueries** A subquery is a query nested inside another query. The inner query is used to retrieve data that can be used by the outer query. Subqueries are useful when you want to perform complex operations that cannot be achieved with a single query. **Types of Subqueries** There are two main types of subqueries: 1. **Scalar subqueries**: Return a single value. 2. **Table subqueries**: Return a set of rows. **Using Subqueries in SELECT Statements** You can use subqueries in SELECT statements to retrieve data from multiple tables. For example, suppose you have two tables: `orders` and `customers`. You want to retrieve the order details for customers who are from a specific city. ```sql SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city = 'New York' ); ``` In this example, the subquery `SELECT id FROM customers WHERE city = 'New York'` returns a list of customer IDs. The outer query then uses this list to retrieve the order details from the `orders` table. **Using Subqueries in FROM Clause** You can use subqueries in the FROM clause to create a derived table. For example, suppose you have a table `sales` with columns `product_id`, `quantity`, and `price`. You want to calculate the total revenue for each product. ```sql SELECT product_id, SUM(quantity * price) AS total_revenue FROM ( SELECT product_id, quantity, price FROM sales WHERE quantity > 0 ) AS derived_table GROUP BY product_id; ``` In this example, the subquery `SELECT product_id, quantity, price FROM sales WHERE quantity > 0` returns a derived table with the product ID, quantity, and price for each sale with a quantity greater than zero. The outer query then groups the results by product ID and calculates the total revenue. **Using Subqueries in WHERE Clause** You can use subqueries in the WHERE clause to filter data. For example, suppose you have a table `employees` with columns `id`, `name`, and `salary`. You want to retrieve the employees who have a salary greater than the average salary. ```sql SELECT * FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); ``` In this example, the subquery `SELECT AVG(salary) FROM employees` returns the average salary. The outer query then uses this value to filter the employees who have a salary greater than the average salary. **Lab Exercise** Use the SQLite database provided to practice writing queries with subqueries. The database contains tables `orders`, `customers`, `employees`, and `sales`. Write the following queries: 1. Retrieve the order details for customers who are from a specific city. 2. Calculate the total revenue for each product. 3. Retrieve the employees who have a salary greater than the average salary. **Example Database Schema** ```sql CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER, product_id INTEGER, quantity INTEGER, price REAL ); CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT, city TEXT ); CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT, salary REAL ); CREATE TABLE sales ( id INTEGER PRIMARY KEY, product_id INTEGER, quantity INTEGER, price REAL ); ``` **Additional Resources** * For more information on subqueries, visit [https://www.sqlite.org/lang_expr.html#subqueries](https://www.sqlite.org/lang_expr.html#subqueries) * For more examples on subqueries, visit [https://www.w3schools.com/sql/sql_subquery.asp](https://www.w3schools.com/sql/sql_subquery.asp) **Takeaways** * Subqueries can be used in SELECT statements to retrieve data from multiple tables. * Subqueries can be used in the FROM clause to create derived tables. * Subqueries can be used in the WHERE clause to filter data. * Subqueries are useful when you want to perform complex operations that cannot be achieved with a single query. **Do you have any questions or need help with this lab? Leave a comment below.** **Next Topic:** Introduction to good database design principles. From: SQLite Database Design and Normalization. Please proceed to the next topic, **Introduction to good database design principles** after completing this lab.