Creating Tables in SQL using CREATE command

SQL

CREATE TABLE statement is used to create a new table in a database. The basic syntax looks like this:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);
  • table_name: This is the name of the table you want to create.
  • column1, column2, etc.: These are the names of the columns in the table.
  • datatype: Specifies the type of data that the column can hold (e.g., INT, VARCHAR, DATE).

Example: Creating a Simple Table

Let’s consider a practical example where we want to create a table to store information about books:

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(50),
    PublicationYear INT,
    Price DECIMAL(10, 2)
);

In this example:

  • BookID is an integer and serves as the primary key for uniquely identifying each book.
  • Title and Author are strings for storing the book title and author’s name.
  • PublicationYear is an integer representing the year the book was published.
  • Price is a decimal number with two decimal places, indicating the book’s price.

Primary Key

The PRIMARY KEY constraint is used to uniquely identify each record in a table. It must contain unique values, and no two rows can have the same primary key value.

In our example, BookID is designated as the primary key.

Adding Constraints and More

Beyond specifying data types, you can add various constraints to ensure data integrity and enforce rules. Common constraints include NOT NULL (to enforce non-null values) and UNIQUE (to ensure unique values).

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18),
    Email VARCHAR(100) UNIQUE
);

In this example:

  • NOT NULL ensures that the first and last names must have values.
  • CHECK (Age >= 18) ensures that the age is 18 or older.
  • UNIQUE ensures that each email address is unique.

Creating a Table using another table

Creating a table using another table is often done through the use of the CREATE TABLE...AS statement.

Here’s an example:

Let’s assume we have an existing table named OldTable with the following structure and data:

CREATE TABLE OldTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

INSERT INTO OldTable VALUES
    (1, 'John', 25),
    (2, 'Jane', 30),
    (3, 'Bob', 22);

Now, let’s create a new table named NewTable based on the structure and data of OldTable:

CREATE TABLE NewTable AS
SELECT *
FROM OldTable;

In this example, NewTable is created with the same structure as OldTable, and it contains the same data. This is often useful when you want to duplicate a table for various reasons, such as archiving data or performing analysis without affecting the original table.