SQL CREATE DATABASE and CREATE TABLE

CREATE command in SQL is used to define new databases and tables. A database acts as a container that holds related objects such as tables, views and procedures, while a table stores data in rows and columns. We will learn how to create databases and tables in SQL with the help of examples.

Creating Database

To create a new database in SQL, we use CREATE DATABASE command followed by the database name. Database names cannot contain spaces; if needed, an underscore (_) can be used instead.

Syntax:

CREATE DATABASE database_name;

Let’s create a simple database called GeeksForGeeks

Query:

CREATE DATABASE GeeksForGeeks;

Output

Explanation: This query creates an empty database named GeeksForGeeks. A success message will confirm that the database was created.

Note: If you try to create a database with a name that already exists, you’ll see an error. To avoid this, either choose a new name or use IF NOT EXISTS clause to only create database if it doesn't already exist.

Query:

CREATE DATABASE IF NOT EXISTS GeeksForGeeks;

1. Verifying Database Creation

To confirm that a new database has been created, use:

Query:

SHOW DATABASES;

Output

Explanation: This command lists all databases in the system. From this list, you can check if GeeksForGeeks has been added.

2. Switching to a Database (USE Command)

Once your database is created, we can switch to that database to begin adding tables, inserting data and performing queries. To switch to your new database, use the USE command.

Syntax:

USE database_name

Query:

USE GeeksForGeeks;

Explanation: After running this, any SQL operations like creating tables or inserting data will be done within GeeksForGeeks database. This allows you to work within the scope of the newly created database.

3. Deleting a Database

If you ever want to delete a database, use:

Query:

DROP DATABASE GeeksForGeeks;

This permanently deletes the database.

Note: Once dropped, all data inside the database will be lost permanently.

Creating Tables

A table is the core structure of a database where data is stored in rows and columns. While creating a table, you must define:

• Column names
• Data types (e.g., INT, VARCHAR, DATE)
• Constraints (e.g., PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, FOREIGN KEY)

Syntax:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Example: Creating a Student Table

Here, we are creating a table named Student to store student details like ID, name, subject, year and marks.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Subject VARCHAR(50),
    Year INT,
    Marks INT
);

Explanation: In this table, StudentID is the unique identifier (Primary Key), Name must not be empty, Subject stores the subject name, Year represents the study year and Marks stores the marks scored by the student.

1. Verifying Table Creation

After running the CREATE TABLE query, the system shows a success message like Table created successfully. To confirm that the table exists, we can list all tables in the current database using:

SHOW TABLES;

This will display all the tables created inside the database and from here you can confirm that Student table has been successfully created.

2. Deleting a Table

If you no longer need a table, use:

DROP TABLE Student;

Explanation: This removes the Student table permanently from the database.