Database Essentials - Writing Advanced SQL Queries - CTAS - Create Table As Select

CTAS is primarily used to create tables based on query results. It is commonly used for tasks such as taking backups, reorganizing tables for performance tuning, and loading query results into tables for analysis and data quality checks. When using CTAS, column names and data types are derived from the SELECT clause, so it’s essential to provide meaningful aliases and type cast derived values for clarity and accuracy.

put a place holder for the video here with text so that I can replace as part of the automation

Key Concepts Explanation

Taking Backups using CTAS

To create a backup table using CTAS, simply specify the table name and use SELECT * FROM original_table.

CREATE TABLE customers_backup
SELECT * FROM customers;

Reorganizing Tables with Derived Values

You can create a new table with derived values by selecting specific columns and applying functions or transformations.

CREATE TABLE orders_backup
SELECT order_id,
    to_char(order_date, 'yyyy')::int AS order_year,
    to_char(order_date, 'MM')::int AS order_month,
FROM orders;

Hands-On Tasks

  1. Create a backup table for customers named customers_backup.
  2. Generate a new table orders_backup with columns order_id, order_year, order_month, order_customer_id, and order_status derived from the original orders table.


CTAS, or Create Table As Select, is a powerful SQL feature that allows users to create tables based on query results. By following best practices such as providing meaningful aliases and type casting, users can efficiently create derived tables for various use cases. Practice using CTAS to reinforce your understanding and join the community for further learning opportunities.

Watch the video tutorial here