SQL CREATE TABLE

SQL CREATE TABLE statement is used to create a table in a database.
In RDBMS, database tables store data in the form of structures (fields and records). In this context, a field defines the type of data to be stored in a table, and a record contains the actual data within a row.

If you want to create a table, you should name the table and define its column and each column’s data type.

The simple syntax to create the table

Here’s a simplified version of the SQL CREATE TABLE statement without specifying column constraints:

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

In this syntax

• table_name is the name of the table you want to create.
• column1, column2, column3, etc., are the names of the columns in the table.
• datatype specifies the data type of each column.

Example

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE
);

This statement creates a table named students with four columns: student_id, first_name, last_name, and date_of_birth, each with its respective data type.

SQL CREATE TABLE Example in MySQL

here’s an example of creating a table named employees in MySQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE
);

In this example:

• employees is the name of the table being created.
• employee_id, first_name, last_name, email, and hire_date are the column names.
• INT, VARCHAR(50), VARCHAR(100), and DATE are the data types for each column, respectively.
• PRIMARY KEY specifies that the employee_id column is the primary key for the table, ensuring uniqueness for each row.

Creating a Table using another table

To create a table using another table as a reference in SQL, you can use the CREATE TABLE ... AS SELECT statement. This statement allows you to create a new table based on the result set of a SELECT query. Here’s an example:

Let’s say you have an existing table named employees with columns employee_id, first_name, last_name, and salary. You want to create a new table named employees_copy with the same structure and data as the employees table

CREATE TABLE employees_copy AS
SELECT * FROM employees;

In this example:

• employees_copy is the name of the new table being created.
• employees is the existing table from which you want to copy the structure and data.
• SELECT * FROM employees selects all columns and rows from the employees table.
• The CREATE TABLE ... AS SELECT statement creates a new table (employees_copy) with the same structure and data as the result set of the SELECT query (SELECT * FROM employees).

Conclusion

In conclusion, the SQL CREATE TABLE statement is fundamental for database schema design, allowing users to define the structure of tables within a relational database. Key elements of this statement include specifying the table name, column names, and their corresponding data types. Additional constraints such as primary keys, foreign keys, and default values can be applied to ensure data integrity and enforce business rules. Whether creating a new table from scratch or based on existing data, the CREATE TABLE statement provides the foundation for organizing and storing data efficiently within a database system.

Frequently Asked Questions