CREATE TABLE AS SELECT

CREATE TABLE AS SELECT builds a brand-new, permanent table by running a query and saving whatever it returns - filtered, joined, or reshaped however you need. Like photocopying a document before scribbling notes on it, the original stays untouched while you work freely on the copy.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Creating a Safe Working Copy with CREATE TABLE AS SELECT

CREATE TABLE orders_clean AS
SELECT * FROM orders

Result:

Step by step

CREATE TABLE defines a new table along with its columns.
```
CREATE TABLE orders_clean AS
```
The asterisk symbol means all columns.
```
CREATE TABLE orders_clean AS
SELECT *
```

FROM tells SQL which table to pull the data from.

CREATE TABLE orders_clean AS
SELECT *
FROM orders;

Practice

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

1. Write a query that creates a new table called products_clean by copying all data from the products table.

CREATE TABLE products_clean AS SELECT * FROM products

2. Write a query that creates a new table called customers_clean by copying all data from the customers table.

CREATE TABLE customers_clean AS SELECT * FROM customers

3. Write a query that creates a new table called orders_clean by copying all data from the orders table.

CREATE TABLE orders_clean AS SELECT * FROM orders

4. Write a query that creates a new table called inventory_clean by copying all data from the inventory table.

CREATE TABLE inventory_clean AS SELECT * FROM inventory

5. Write a query that creates a new table called suppliers_clean by copying all data from the suppliers table.

CREATE TABLE suppliers_clean AS SELECT * FROM suppliers

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