Sign In
Get Started

Mastering SQL: Utilizing Subqueries and Temporary Tables

by | SQL

Introduction to Subqueries in SQL

Setup Instructions

  1. Ensure you have a SQL-compliant database (like MySQL or PostgreSQL) and necessary permissions to create and query data.
  2. 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:

Sales Now On Advertisement
  • 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:

  1. Creating a temporary table (temp_sales) for storing the sum of quantities sold per product.
  2. 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.
  3. Dropping the temporary table after its use to clean up.

Related Posts