Temporary tables

A temporary table holds intermediate results for the duration of your working session, then disappears automatically when you disconnect.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Breaking Retail Analysis into Steps with Temporary Tables

CREATE TEMP TABLE high_value_orders AS
SELECT order_id, customer_id, category, revenue, order_date
FROM orders
WHERE revenue > 100;

SELECT customer_id, SUM(revenue) AS total_revenue
FROM high_value_orders
GROUP BY customer_id

Result:

customer_id	total_revenue
C101	488.99

Step by step

CREATE TEMP TABLE makes a table that only exists for your session.
```
CREATE TEMP TABLE high_value_orders AS
```

After SELECT, list the column names you want, separated by commas.

CREATE TEMP TABLE high_value_orders AS
SELECT order_id, customer_id, category, revenue, order_date

FROM tells SQL which table to pull the data from.

CREATE TEMP TABLE high_value_orders AS
SELECT order_id, customer_id, category, revenue, order_date
FROM orders

Practice

Try each one in your head first, then open the answer.

1. Write a statement that creates a temporary table called tmp_orders as a copy of orders.

CREATE TEMP TABLE tmp_orders AS SELECT * FROM orders

2. Write a statement that creates a temporary table called tmp_store_sales as a copy of store_sales.

CREATE TEMP TABLE tmp_store_sales AS SELECT * FROM store_sales

3. Write a statement that creates a temporary table called tmp_products as a copy of products.

CREATE TEMP TABLE tmp_products AS SELECT * FROM products

4. Write a statement that creates a temporary table called tmp_customers as a copy of customers.

CREATE TEMP TABLE tmp_customers AS SELECT * FROM customers

5. Write a statement that creates a temporary table called tmp_returns as a copy of returns.

CREATE TEMP TABLE tmp_returns AS SELECT * FROM returns

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed