Temporary Tables in MySql

Create a Temporary Table: Use the CREATE TEMPORARY TABLE statement to create a temporary table. Temporary tables are session-specific and are automatically dropped when the session ends or when they are explicitly dropped.


Drop the Temporary Table (Optional): Temporary tables are automatically dropped when the session ends. However, if you want to explicitly drop the temporary table within the session, use the DROP TEMPORARY TABLE statement.

Example:
CREATE TEMPORARY TABLE temp_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO temp_table (name) VALUES ('John'), ('Alice'), ('Bob');
SELECT * FROM temp_table WHERE name = 'Alice';
DROP TEMPORARY TABLE temp_table;

Remember, temporary tables are useful for storing intermediate results within a session and are automatically cleaned up when the session ends, making them suitable for temporary data storage needs.

Using intermediate tables in SQL queries is a common practice for breaking down complex operations into smaller, manageable steps. Here are a few examples of how you can use intermediate tables to answer queries:

Example 1: Using a Common Table Expression (CTE)
-- Create an intermediate table (CTE) to calculate average salary by department
WITH AvgSalaryByDept AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)

-- Query to find departments where average salary is above a certain threshold
SELECT department_id
FROM AvgSalaryByDept
WHERE avg_salary > 50000;

In this example:

  • We first calculate the average salary by department using a Common Table Expression (WITH clause).
  • Then, we query the CTE to find departments where the average salary is above $50,000.
Example 2: Using Temporary Tables
-- Create a temporary table to store employees hired in the last year
CREATE TEMPORARY TABLE recent_hires AS (
    SELECT employee_id, hire_date
    FROM employees
    WHERE hire_date >= CURDATE() - INTERVAL 1 YEAR
);

-- Query to find the average tenure of recent hires
SELECT AVG(DATEDIFF(CURDATE(), hire_date)) AS avg_tenure_in_days
FROM recent_hires;

-- Drop the temporary table (optional)
DROP TEMPORARY TABLE recent_hires;

In this example:

  • We create a temporary table recent_hires to store employees hired in the last year.
  • We then query this temporary table to find the average tenure of these recent hires.
  • Finally, we drop the temporary table once we are done with it.
Example 3: Using Subqueries
-- Query to find employees whose salary is above the department average
SELECT employee_id, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

In this example:

  • We use a subquery to calculate the average salary for each department.
  • The main query then selects employees whose salary is higher than their department's average salary.
Benefits of Using Intermediate Tables:
  • Modularity: Breaking down complex queries into smaller, more understandable steps.
  • Performance: In some cases, using intermediate results can optimize query execution by reducing the complexity of each individual step.
  • Reusability: Intermediate tables or results can be reused across multiple queries within the same session.

These examples demonstrate how intermediate tables (CTEs, temporary tables, or subqueries) can be used effectively to answer specific queries or perform complex data manipulations in SQL. Each approach has its strengths depending on the specific requirements and constraints of your database and application.

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more