SQL – Create Database and Table

Before any data can be stored, two things must exist — a database to hold everything, and a table inside that database to organize the data. SQL provides the CREATE command to build both.

Think of a database as a physical office building, and tables as the different departments inside it — HR, Finance, Operations. The CREATE statement is what constructs both the building and its rooms.

CREATE DATABASE

The CREATE DATABASE statement creates a new, empty database where tables can be added later.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE SchoolDB;

This creates a new database called SchoolDB. The database is empty at this point — no tables exist inside it yet.

Selecting a Database to Use

After creating a database, it must be selected before any tables can be created or any queries can be run inside it.

USE SchoolDB;

The USE command tells the database system: "From this point on, all operations should happen inside SchoolDB."

Checking Existing Databases

SHOW DATABASES;

This lists all databases available on the server. It helps verify whether a database was created successfully.

CREATE TABLE

The CREATE TABLE statement creates a new table inside the currently selected database. When creating a table, the column names and their data types must be defined.

Syntax

CREATE TABLE table_name (
    column1_name  datatype,
    column2_name  datatype,
    column3_name  datatype,
    ...
);

Example: Creating a Students Table

CREATE TABLE Students (
    StudentID   INT,
    StudentName VARCHAR(50),
    Age         INT,
    City        VARCHAR(30),
    JoinDate    DATE
);

This creates a table called Students with five columns. At this point, the table exists but contains no rows — it is like an empty spreadsheet with only the column headers defined.

Visual Structure of the Created Table

Adding Constraints During Table Creation

Constraints are rules applied to columns to control what data is allowed. They help maintain the quality and consistency of data.

Common Constraints

Example: Table With Constraints

CREATE TABLE Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Age         INT,
    City        VARCHAR(30) DEFAULT 'Unknown',
    JoinDate    DATE
);

Explanation of each constraint used:

• StudentID — Automatically gets a unique number (1, 2, 3...) and is the primary key, so no two students will have the same ID.
• StudentName — Cannot be empty. Every student must have a name.
• City — If no city is provided, it defaults to 'Unknown'.
• JoinDate — No constraint applied; it can be left empty.

Viewing Existing Tables

SHOW TABLES;

This shows all the tables present inside the currently selected database. It is useful for confirming that a table was created successfully.

Viewing the Structure of a Table

DESCRIBE Students;

The DESCRIBE command (also written as DESC) shows the column names, data types, and constraints of a table. The output looks like this:

Creating a Table Only If It Does Not Already Exist

Running CREATE TABLE on a table that already exists will cause an error. To avoid this, the IF NOT EXISTS clause can be used.

CREATE TABLE IF NOT EXISTS Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Age         INT,
    City        VARCHAR(30) DEFAULT 'Unknown',
    JoinDate    DATE
);

If the Students table already exists, this command will simply do nothing instead of throwing an error.

Dropping (Deleting) a Table or Database

To completely remove a table and all its data:

DROP TABLE Students;

To completely remove a database and everything inside it:

DROP DATABASE SchoolDB;

Warning: The DROP command permanently deletes the table or database and all data within it. This action cannot be undone, so it must be used with caution.

Full Working Example

-- Step 1: Create the database
CREATE DATABASE SchoolDB;

-- Step 2: Select the database to use
USE SchoolDB;

-- Step 3: Create the Students table
CREATE TABLE Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Age         INT,
    City        VARCHAR(30) DEFAULT 'Unknown',
    JoinDate    DATE
);

-- Step 4: Verify the table was created
DESCRIBE Students;

Key Points to Remember

• Always run USE database_name; after creating a database to switch into it.
• Table and column names should be meaningful and should not contain spaces.
• Every table should have a PRIMARY KEY column to uniquely identify each row.
• Use AUTO_INCREMENT with the primary key so the database assigns ID numbers automatically.
• Use IF NOT EXISTS to avoid errors when creating tables in scripts that may run more than once.

Summary

CREATE DATABASE sets up the container where all tables will be stored. CREATE TABLE defines the structure of a table — the column names, data types, and any constraints. Together, these two statements form the foundation of any SQL database project. Once the database and tables are created, data can be inserted and managed.