Introduction to Subqueries in SQL
Setup Instructions
- Ensure you have a SQL-compliant database (like MySQL or PostgreSQL) and necessary permissions to create and query data.
- Prepare a sample database schema with relevant tables.
Practical Implementation
Subqueries in SELECT Statement
-- Using a subquery to return the maximum salary
SELECT name, department_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
Subqueries in FROM Clause
-- Subquery returns a derived table containing the average salary per department
SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id) AS dept_avg;
Subqueries in WHERE Clause
-- Subquery to filter employees with above average salaries
SELECT name, department_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Subqueries in INSERT Statements
-- Inserting the result of a subquery into a new table
INSERT INTO high_earners (name, salary)
SELECT name, salary
FROM employees
WHERE salary > 100000;
Create Temporary Table
-- Creating a temporary table for intermediate calculations
CREATE TEMPORARY TABLE temp_avg_salary AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
Using Temporary Table
-- Querying the temporary table
SELECT e.name, e.salary, t.avg_salary
FROM employees e
JOIN temp_avg_salary t ON e.department_id = t.department_id
WHERE e.salary > t.avg_salary;
Clean Up Temporary Table
-- Dropping the temporary table
DROP TEMPORARY TABLE IF EXISTS temp_avg_salary;
Follow these examples to enhance your data analysis and manipulation using subqueries and temporary tables in SQL.
Writing Simple Subqueries: Practical Implementation
Sample Database Schema
Assume we have two tables:
employees(employee_id, name, department_id, salary)departments(department_id, department_name)
1. Using Subquery in the SELECT Clause
SELECT
name,
(SELECT department_name
FROM departments
WHERE departments.department_id = employees.department_id) AS department_name
FROM
employees;
2. Using Subquery in the FROM Clause
SELECT
department_name,
total_salary
FROM
(SELECT
department_id,
SUM(salary) AS total_salary
FROM
employees
GROUP BY
department_id) AS dept_salaries
JOIN
departments
ON
dept_salaries.department_id = departments.department_id;
3. Using Subquery in the WHERE Clause
SELECT
name,
salary
FROM
employees
WHERE
salary > (SELECT AVG(salary) FROM employees);
4. Using Subquery with IN Operator
SELECT
name
FROM
employees
WHERE
department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
5. Using Subquery with EXISTS Operator
SELECT
name,
department_id
FROM
employees e
WHERE
EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Engineering');
6. Using Multiple Subqueries
SELECT
e1.name AS employee_name,
d1.department_name AS department_name,
(SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id) AS avg_department_salary
FROM
employees e1
JOIN
departments d1
ON
e1.department_id = d1.department_id;
These examples illustrate practical use cases for subqueries in SQL, which can be directly applied in various data analysis and manipulation scenarios.
Advanced Subqueries and Correlation in SQL
Correlated Subquery Example
SELECT employee_id, salary
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
);
Using Temporary Tables
-- Creating a temporary table
CREATE TEMPORARY TABLE high_salaries AS
SELECT employee_id, salary
FROM employees
WHERE salary > 100000;
-- Querying the temporary table
SELECT * FROM high_salaries;
Subqueries in SELECT Clause
SELECT
employee_id,
salary,
(SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id) AS average_department_salary
FROM employees e1;
Subqueries in FROM Clause
SELECT department_id, AVG(salary) AS avg_salary
FROM (
SELECT department_id, salary
FROM employees
) AS emp
GROUP BY department_id;
Subqueries with EXISTS
SELECT employee_id, first_name, last_name
FROM employees e1
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.manager_id = e1.employee_id
);
Updates using Subqueries
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id IN (
SELECT employee_id
FROM high_salaries
);
Deleting using Subqueries
DELETE FROM employees
WHERE department_id NOT IN (
SELECT department_id
FROM departments
);
Cleanup Temporary Table
DROP TEMPORARY TABLE IF EXISTS high_salaries;
Conclusion
These examples can be used practically to implement advanced subqueries and correlation in SQL.
Using Temporary Tables in SQL
Creating a Temporary Table
CREATE TEMPORARY TABLE temp_sales (
sale_id INT,
product_id INT,
quantity INT,
sale_date DATE
);
Inserting Data into a Temporary Table
INSERT INTO temp_sales (sale_id, product_id, quantity, sale_date)
VALUES
(1, 101, 2, '2023-01-10'),
(2, 102, 1, '2023-01-11'),
(3, 101, 5, '2023-01-12');
Selecting Data from a Temporary Table
SELECT * FROM temp_sales;
Using a Temporary Table in a JOIN
SELECT
p.product_name,
t.quantity,
t.sale_date
FROM
products p
JOIN
temp_sales t ON p.product_id = t.product_id;
Updating Records in a Temporary Table
UPDATE temp_sales
SET quantity = 3
WHERE sale_id = 2;
Deleting Records from a Temporary Table
DELETE FROM temp_sales
WHERE sale_date < '2023-01-11';
Dropping a Temporary Table
DROP TEMPORARY TABLE IF EXISTS temp_sales;
Ensure to replace the table and column names with those found in your actual database schema. This implementation is directly applicable in a real-world SQL environment.
Creating and Using Temporary Tables in SQL
Creating Temporary Tables
-- Create a temporary table to store intermediate results
CREATE TEMPORARY TABLE temp_sales_summary (
category_id INT,
total_sales DECIMAL(10, 2),
total_orders INT
);
Insert Data into Temporary Tables
-- Populate the temporary table with summarized sales data
INSERT INTO temp_sales_summary (category_id, total_sales, total_orders)
SELECT
category_id,
SUM(sales_amount) AS total_sales,
COUNT(order_id) AS total_orders
FROM
sales
GROUP BY
category_id;
Using Temporary Tables
-- Query the temporary table for further analysis
SELECT
tss.category_id,
tss.total_sales,
tss.total_orders,
c.category_name
FROM
temp_sales_summary tss
JOIN
categories c ON tss.category_id = c.category_id;
Dropping Temporary Tables
-- Drop the temporary table once it is no longer needed
DROP TEMPORARY TABLE IF EXISTS temp_sales_summary;
This implementation showcases how to create, use, and drop temporary tables to facilitate data analysis and manipulation in SQL.
Combining Subqueries and Temporary Tables for Complex Queries
-- 1. Create a temporary table to store intermediate results
CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;
-- 2. Use a subquery to calculate average quantity sold for products in 'Electronics' category
SELECT product_id, total_quantity
FROM temp_sales
WHERE product_id IN (
SELECT id
FROM products
WHERE category = 'Electronics'
) AND total_quantity > (
SELECT AVG(total_quantity)
FROM temp_sales
);
-- 3. Clean up: Drop the temporary table
DROP TEMPORARY TABLE temp_sales;
This code demonstrates:
- Creating a temporary table (
temp_sales) for storing the sum of quantities sold per product. - Using a subquery to filter products that belong to the ‘Electronics’ category and have a total quantity sold greater than the average total quantity sold for all products.
- Dropping the temporary table after its use to clean up.
