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 organise 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 constructs both the building and its rooms.

Create a Database

The CREATE DATABASE statement creates a new, empty database.

MySQL Query

CREATE DATABASE SchoolDB;

MS SQL Server Query

CREATE DATABASE SchoolDB;
GO

Note: The GO keyword in MS SQL Server marks the end of a batch of statements. It is not required in every case but is commonly used after CREATE DATABASE to ensure the command completes before the next statement runs.

Switch to a Database

After creating a database, you must select it before creating tables or running any queries inside it.

MySQL Query

USE SchoolDB;

MS SQL Server Query

USE SchoolDB;
GO

List All Databases

MySQL Query

SHOW DATABASES;

MS SQL Server Query

-- SHOW DATABASES does not exist in MS SQL Server.
-- Use this query instead to list all databases.
SELECT name FROM sys.databases;

Create a Table

The CREATE TABLE statement creates a new table inside the currently selected database. You must define column names and their data types.

MySQL Query

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

MS SQL Server Query

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

What Each Part Means

• AUTO_INCREMENT / IDENTITY(1,1) — The database automatically assigns the ID number for each new row.
• PRIMARY KEY — Uniquely identifies each row. No two rows can have the same value.
• NOT NULL — This column cannot be left empty.
• DEFAULT 'Unknown' — If no city is provided, it automatically stores 'Unknown'.

List All Tables

MySQL Query

SHOW TABLES;

MS SQL Server Query

-- SHOW TABLES does not exist in MS SQL Server.
-- Use this query instead.
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

View Column Structure of a Table

MySQL Query

DESCRIBE Students;

MS SQL Server Query

-- DESCRIBE does not exist in MS SQL Server.
-- Use INFORMATION_SCHEMA.COLUMNS to see column details.
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Students';

Create Table Only If It Does Not Already Exist

Running CREATE TABLE on a table that already exists causes an error. Use the following approach to avoid that.

MySQL Query

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
);

MS SQL Server Query

-- MS SQL Server uses IF NOT EXISTS with a BEGIN...END block.
IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'Students'
)
BEGIN
    CREATE TABLE Students (
        StudentID   INT          IDENTITY(1,1) PRIMARY KEY,
        StudentName NVARCHAR(50) NOT NULL,
        Age         INT,
        City        NVARCHAR(30) DEFAULT 'Unknown',
        JoinDate    DATE
    );
END

Drop (Delete) a Table or Database

MySQL Query

-- Remove a table permanently
DROP TABLE Students;

-- Remove a database permanently
DROP DATABASE SchoolDB;

MS SQL Server Query

-- Remove a table permanently
DROP TABLE Students;

-- Remove a database permanently
DROP DATABASE SchoolDB;

Warning: The DROP command permanently deletes the table or database and all data within it. This action cannot be undone.

Full Working Example

MySQL Query

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

-- Step 2: Switch to the database
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 structure
DESCRIBE Students;

MS SQL Server Query

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

-- Step 2: Switch to the database
USE SchoolDB;
GO

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

-- Step 4: Verify the table structure
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Students';

Key Points

• Always run USE database_name; after creating a database to switch into it.
• Every table should have a PRIMARY KEY column to uniquely identify each row.
• MySQL uses AUTO_INCREMENT; MS SQL Server uses IDENTITY(1,1).
• MySQL uses SHOW DATABASES, SHOW TABLES, and DESCRIBE. MS SQL Server uses sys.databases, INFORMATION_SCHEMA.TABLES, and INFORMATION_SCHEMA.COLUMNS.
• MySQL uses CREATE TABLE IF NOT EXISTS; MS SQL Server uses an IF NOT EXISTS (...) BEGIN ... END block.
• The DROP command is permanent and cannot be undone — always double-check before using it.