Lessons › SQL › Deep Dive: Database Admin & ETL

SQL lesson

CREATE TABLE

CREATE TABLE builds an empty, structured container in a database - you define what columns it has and what kind of data each column holds before a single row of data goes in.

Example

Creating a Retail Product Table with CREATE TABLE

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  price NUMERIC,
  in_stock BOOLEAN,
  added_date DATE
)

Result:

Step by step

1. Every table starts with the keywords CREATE TABLE. By themselves they don't do anything - you still owe SQL a name and a column list.

   CREATE TABLE

2. Add the table name (`products`) and an open parenthesis. Everything between the parens will be the column definitions. This is still incomplete - SQL needs at least one column before you close the paren.

   CREATE TABLE products (

3. Each column is a NAME followed by a TYPE. `product_id INTEGER` is the first. INTEGER stores whole numbers (no decimals).

   CREATE TABLE products (
     product_id INTEGER
   );

Practice

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

CREATE TABLE products (product_id SERIAL, brand TEXT, category TEXT, price NUMERIC)

CREATE TABLE customers (customer_id SERIAL, full_name TEXT, email TEXT, phone TEXT, signup_date DATE)

CREATE TABLE orders (order_id SERIAL, customer_id INTEGER, category TEXT, revenue NUMERIC, order_date DATE)

CREATE TABLE inventory (product_id SERIAL, warehouse TEXT, stock_count INTEGER, last_updated DATE)

CREATE TABLE suppliers (supplier_id SERIAL, supplier_name TEXT, country TEXT, contract_start DATE)