Temp Tables QNA

Q1: Top 5 real-time questions along with their SQL queries for Temp Tables in SQL.

Ans:

1. Employee Information in a Temp Table:

• Question: Create a temporary table to store information about employees with a specific job title and retrieve the details.

• Query:

  CREATE TEMPORARY TABLE temp_employee_info AS
  SELECT employee_id, employee_name, job_title
  FROM employees
  WHERE job_title = 'Software Engineer';
  
  SELECT * FROM temp_employee_info;

2. Temporary Table for Aggregated Data:

• Question: Create a temporary table to store the total sales for each product category and retrieve the details.
• Query:

  CREATE TEMPORARY TABLE temp_category_sales AS
  SELECT category, SUM(sales) AS total_sales
  FROM sales
  GROUP BY category;
  
  SELECT * FROM temp_category_sales;

3. Filtering Data with a Temp Table:

• Question: Create a temporary table to store employees with a salary above a certain threshold and retrieve the details.
• Query:

  CREATE TEMPORARY TABLE temp_high_salary_employees AS
  SELECT employee_id, employee_name, salary
  FROM employees
  WHERE salary > 80000;
  
  SELECT * FROM temp_high_salary_employees;

4. Joining Data using Temp Tables:

• Question: Create temporary tables to store information about employees and their respective departments, then retrieve the details through a join.
• Query:

  CREATE TEMPORARY TABLE temp_employee AS
  SELECT employee_id, employee_name, department_id
  FROM employees;
  
  CREATE TEMPORARY TABLE temp_department AS
  SELECT department_id, department_name
  FROM departments;
  
  SELECT e.employee_id, e.employee_name, d.department_name
  FROM temp_employee e
  JOIN temp_department d ON e.department_id = d.department_id;

5. Using Temp Table for Data Transformation:

• Question: Create a temporary table to store transformed data (e.g., uppercasing names) and retrieve the modified details.
• Query:

  CREATE TEMPORARY TABLE temp_transformed_data AS
  SELECT employee_id, UPPER(employee_name) AS uppercase_name
  FROM employees;
  
  SELECT * FROM temp_transformed_data;

These questions and queries showcase scenarios where temporary tables can be employed to store and manipulate data temporarily, allowing for more complex and multi-step processing. Adjust the queries based on your specific database schema and requirements.