SQL CREATE

← Previous Next →

The CREATE command in SQL is used to define and create new database objects, such as databases, tables, views, indexes, and other structures within the database. It's an essential command for setting up the schema of a database before you start populating it with data.

Create Command Behavior:
1. Persistence: CREATE commands make permanent changes to the database schema.
2. Schema Definition: CREATE is used for defining the structure of a database (tables, views, indexes) but not for manipulating the data.
3. Data Types: When creating tables, it’s important to define appropriate data types for each column (e.g., INT, VARCHAR, DATE, DECIMAL, etc.).
4. Constraints: You can enforce data integrity with constraints like PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, etc., during the table creation process.

Example: Create Database

CREATE DATABASE CompanyDB;

Example: Create Tables

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

Example: Create Index for Faster Queries on Product Names

CREATE INDEX idx_product_name ON Products (product_name);

Example: Create a View for Orders Over $100:

CREATE VIEW ExpensiveOrders AS
SELECT o.order_id, o.order_date, p.product_name, o.quantity, (o.quantity * p.price) AS total_amount
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
WHERE (o.quantity * p.price) > 100;

Example: Create Constraint to enforce rules on the data. They can be defined when creating a table.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    customer_id INT,
    total_amount DECIMAL(10,2) CHECK (total_amount > 0)
);

This creates an Orders table with:

order_id as the primary key
order_date with a default value of the current date
total_amount with a check constraint ensuring the amount is positive.

Common Use Cases for CREATE:
1. Setting up a new database: To store and organize your data.
2. Creating tables: To define the structure of your data.
3. Creating views: To simplify complex queries or provide a layer of abstraction.
4. Creating indexes: To improve query performance for large datasets.
5. Establishing constraints: To ensure data integrity across your database.

← Previous Next →
Topics