Blog

SQL CREATE Keyword Tutorial

Introduction

SQL (Structured Query Language) is used to manage and manipulate relational databases. One of the primary keywords used in SQL is CREATE, which is used to create various database objects such as databases, tables, indexes, views, and more. This tutorial will focus on the CREATE keyword, particularly on creating databases and tables.

1. Creating a Database

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE SchoolDB;

Explanation

• CREATE DATABASE: The command to create a new database.
• SchoolDB: The name of the database being created.

When this command is executed, a new database named SchoolDB is created in the DBMS. You can verify the creation by listing all databases, usually with a command like SHOW DATABASES; in MySQL.

2. Creating a Table

Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

Example

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    EnrollmentDate DATE
);

Explanation

• CREATE TABLE: The command to create a new table.
• Students: The name of the table being created.
• Columns:
• StudentID: An integer column, serves as the primary key.
• FirstName and LastName: Variable character columns with a maximum length of 50 characters.
• BirthDate and EnrollmentDate: Date columns for storing date values.

This command creates a Students table with the specified columns and constraints.

3. Creating a Table with Constraints

Example with Constraints

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT CHECK (Credits > 0),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Explanation

• CourseID: Primary key.
• CourseName: Must not be null (NOT NULL constraint).
• Credits: Must be a positive integer (CHECK constraint).
• DepartmentID: Foreign key referencing the Departments table.

4. Creating a Table with Auto-Increment Column

Example

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Explanation

• AUTO_INCREMENT: Automatically increments the TeacherID for each new record inserted.

5. Creating a Table with Default Values

Example

CREATE TABLE Enrollment (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE DEFAULT CURRENT_DATE
);

Explanation

• EnrollmentDate: Defaults to the current date if no value is provided during insertion.

6. Creating a Temporary Table

Syntax

CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Example

CREATE TEMPORARY TABLE TempStudents (
    StudentID INT,
    Name VARCHAR(50)
);

Explanation

• TEMPORARY: Indicates that the table is temporary and will be deleted when the session ends.

7. Creating an Index

Syntax

CREATE INDEX index_name ON table_name (column_name);

Example

CREATE INDEX idx_student_name ON Students (LastName);

Explanation

• CREATE INDEX: The command to create an index.
• idx_student_name: The name of the index.
• Students: The table on which the index is created.
• LastName: The column being indexed.

Indexes improve the speed of data retrieval operations on a table at the cost of additional storage space and potential impact on write performance.

8. Creating a View

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

CREATE VIEW StudentNames AS
SELECT FirstName, LastName
FROM Students;

Explanation

• CREATE VIEW: The command to create a view.
• StudentNames: The name of the view.
• SELECT statement: The query defining the view.

Views are virtual tables that provide a way to present data in a specific format or subset without modifying the actual tables.

Summary

The CREATE keyword in SQL is fundamental for defining the structure of databases and their objects. In this tutorial, we’ve covered how to create databases, tables with various constraints, auto-increment columns, default values, temporary tables, indexes, and views. Each example included the syntax, a sample code snippet, and a detailed explanation to help you understand the use and function of the CREATE keyword in SQL.

Feel free to experiment with these commands in your SQL environment to reinforce your learning and understand the behavior of each scenario.