Create SQL Tables

Creating a SQL Table

To create an SQL table, you use SQL's CREATE TABLE statement. Here's a simplified syntax:

CREATE TABLE "tableName" (
    "Column1" "DataType",
    "Column2" "DataType",
    ...
    "ColumnN" "DataType"
);

This syntax might look a bit complex, but let's break it down step by step.

• CREATE TABLE: This is the command that tells the database you want to create a new table.
• "tablename": Replace this with the name you want to give your table. Be sure to choose a descriptive name that reflects the data it will hold.
• "Column1", "Column2", …, "ColumnN": These are the names of the columns within your table. Each column should have a unique name.
• "data type": For each column, specify the type of data it will hold, such as INT for integers or VARCHAR for text.

Let's Create Your First SQL Table

Let's walk through an example of creating a simple table for storing student information. We'll create a table named "STUDENTS" with columns for ID, Name, Age, and Address. The ID column will also serve as the primary key, ensuring that each student has a unique identifier.

CREATE TABLE STUDENTS
(
    ID INT NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(25),
    PRIMARY KEY (ID)
);

In this example:

• ID, NAME, AGE, and ADDRESS are the column names.
• INT, VARCHAR(20), INT, and CHAR(25) specify the data types for each column.
• NOT NULL ensures that these fields cannot be left empty when adding records.
• PRIMARY KEY (ID) designates the ID column as the primary key, ensuring its uniqueness.

Verifying Your Table

After running the SQL command, the database should confirm that you've created the table successfully. You can also use the DESC command to check the table's structure:

DESC STUDENTS;

This command provides a detailed overview of the table's columns, data types, and constraints.

Copying Tables

Sometimes, you may need to create a new table that mirrors the structure of an existing one. This can be achieved using the CREATE TABLE statement with a SELECT clause:

CREATE TABLE STUDENTSCopy AS
SELECT StudentID, Name, Age
FROM Employee;

This command creates a new table, EmployeeCopy, with columns matching those selected from the Employee table. It's a handy way to duplicate tables when needed.

Adding Primary Keys

Primary keys are crucial for maintaining data integrity and ensuring each record's uniqueness. Here's how you can define primary keys in your SQL table:

CREATE TABLE employee (
    employeeid NUMBER NOT NULL,
    firstname  VARCHAR(255) NOT NULL,
    lastname   VARCHAR(255),
    city       VARCHAR(255),
    PRIMARY KEY ( employeeid )
);

You can also create primary keys that span multiple columns for added data integrity:

CREATE TABLE employee (
    employeeid NUMBER NOT NULL,
    firstname  VARCHAR(255) NOT NULL,
    lastname   VARCHAR(255),
    city       VARCHAR(255),
    CONSTRAINT pk_employee PRIMARY KEY ( employeeid,
                                         firstname )
);