SQL CREATE Statement

Purpose of CREATE

The CREATE statement is used to define and build new database objects. This includes databases, tables, indexes, views, and more. Strong understanding of CREATE is essential for structuring your data and optimizing performance.

Creating a New Database

To start organizing data, you first CREATE DATABASE. This allocates storage and metadata for objects you will add.

-- Create a database named shop
CREATE DATABASE shop;

-- Switch to the new database (syntax varies):
USE shop;            -- MySQL, SQL Server
\c shop             -- PostgreSQL

After creation, you switch context so subsequent CREATE statements apply inside shop.

Creating Tables

Next, define tables with CREATE TABLE. You specify column names, data types, and optional constraints to enforce rules.

-- Simple products table
CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  price        DECIMAL(10,2) NOT NULL,
  in_stock     INT DEFAULT 0
);

This example defines product_id as the unique identifier, name and price as required, and in_stock defaulting to zero.

Adding Constraints

Constraints enforce data integrity. Common ones are NOT NULL, UNIQUE, CHECK, and FOREIGN KEY.

CREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  order_date   DATE NOT NULL,
  customer_id  INT NOT NULL,
  amount       DECIMAL(10,2) CHECK (amount > 0),
  CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

Here, CHECK ensures amount is positive, and the FOREIGN KEY links orders to the customers table.

Creating Indexes

Indexes speed up queries on large tables. You CREATE INDEX on columns used in WHERE or JOIN conditions.

-- Index on products.name for fast lookups
CREATE INDEX idx_products_name
ON products(name);

-- Composite index example
CREATE INDEX idx_orders_date_amount
ON orders(order_date, amount);

A composite index on order_date and amount helps queries filtering by both columns.

Creating Views

Views present a virtual table defined by a SELECT. Use CREATE VIEW to simplify complex queries.

CREATE VIEW v_order_summary AS
SELECT c.name AS customer, COUNT(o.order_id) AS total_orders, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

Now, SELECT * FROM v_order_summary shows aggregated order data per customer.

Creating Schemas

Schemas group related tables. You CREATE SCHEMA to organize objects and manage permissions.

-- Create schema sales
CREATE SCHEMA sales;

-- Create table inside schema
CREATE TABLE sales.quarterly (
  id     INT PRIMARY KEY,
  qtr    VARCHAR(10),
  revenue DECIMAL(12,2)
);

Using sales.quarterly differentiates it from other schemas.

Dropping Objects

Use DROP to remove objects when no longer needed.

DROP TABLE IF EXISTS products;
DROP INDEX IF EXISTS idx_products_name;
DROP VIEW IF EXISTS v_order_summary;
DROP DATABASE IF EXISTS old_shop;

IF EXISTS prevents errors if the object is missing.

Best Practices

Define clear, meaningful names for databases, tables, and indexes.
Always specify appropriate data types and constraints to enforce integrity.
Create indexes thoughtfully; too many can slow writes.
Use schemas to organize and secure objects.

Summary

The CREATE family of commands is the foundation of database structure. Mastering CREATE DATABASE, CREATE TABLE, CREATE INDEX, CREATE VIEW, and CREATE SCHEMA empowers you to build well-organized, performant, and secure databases.