lesson 64 Database engineering · advanced

CTEs, CTAS & temporary tables

Choose between named queries, created tables and temporary results.

the idea

A CTE names an expression for one statement. A view persists a definition. CREATE TEMP TABLE ... AS SELECT materializes an intermediate result for the connection. SQL Server uses SELECT ... INTO #temp; that syntax is not SQLite. CTAS does not carry over source constraints.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

CREATE TEMP TABLE customer_units AS
SELECT customer_id,SUM(quantity) AS units
FROM orders
GROUP BY customer_id;
SELECT customer_id,units
FROM customer_units
WHERE units>3;

Watch for: SQL Server CTEs do not materialize by default. SQLite can choose different CTE strategies; neither promises a universal speed-up.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Compute units you will materialize. CTAS and TEMP tables store a query result. Build the customer_units shape in your head before writing CREATE TEMP TABLE.
step-1.sql
sql›
```
SELECT customer_id, SUM(quantity) AS units
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
```

Materialize with CTAS. CREATE TEMP TABLE … AS SELECT stores the result for later queries in the same session.

step-2.sql

CREATE TEMP TABLE customer_units AS
SELECT customer_id,SUM(quantity) AS units
FROM orders
GROUP BY customer_id;
SELECT customer_id,units
FROM customer_units
WHERE units>3;