SQL CREATE TABLE – Tutorial with Examples

SQL CREATE TABLE is a statement used in SQL to create a new table. A table is a database object that consists of columns and rows, where data is stored. The CREATE TABLE statement is used to define the columns and their data types, as well as any constraints that should be applied to the data in the table.

Creating a Table

To create a table, you must specify the name of the table and the columns, along with their data types. The general syntax for creating a table is as follows:

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

For example, the following statement creates a table named “customers” with three columns: “id”, “name”, and “email”:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

Constraints in SQL CREATE TABLE

Constraints are used to define certain rules that data must follow within a table. Constraints can be applied to individual columns or the entire table. The most commonly used constraints in SQL are:

• PRIMARY KEY: Ensures that the column contains unique values and cannot be null.
• UNIQUE: Ensures that the column contains unique values, but can contain null values.
• NOT NULL: Ensures that the column cannot contain null values.
• FOREIGN KEY: Ensures that the column contains values that are present in a column in another table (i.e., creates a relationship between the two tables).

For example, the following statement creates a table named “orders” with three columns: “order_id”, “customer_id”, and “order_date”. The “order_id” column is the primary key and the “customer_id” column is a foreign key that references the “id” column in the “customers” table:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Creating a Table from Another Table

In SQL, it is possible to create a new table based on the data of another table. This is known as creating a table from another table, or creating a derived table. To do this, you can use a SELECT statement within the CREATE TABLE statement:

CREATE TABLE new_table_name AS
SELECT column1, column2, ..., columnN
FROM original_table_name;

For example, the following statement creates a table named “top_customers” based on data from the “customers” table:

CREATE TABLE top_customers AS
SELECT name, email
FROM customers;

This will create a new table called “top_customers” with only the “name” and “email” columns from the “customers” table. The data in the “top_customers” table will be the same as the data in the “customers” table at the time the statement is executed. If the data in the “customers” table changes, the data in the “top_customers” table will not be updated unless the CREATE TABLE AS statement is executed again.

It’s important to note that creating a table from another table does not create a relationship between the two tables. The new table is simply a copy of the data in the original table at the time the statement was executed.

Conclusion

SQL CREATE TABLE is an essential statement for creating tables in a database. It allows you to define the columns and data types of a table, as well as apply constraints to the data. Additionally, you can create a new table from the data of another table using a SELECT statement within the CREATE TABLE statement. By understanding the basic syntax and features of the SQL CREATE TABLE statement, you can effectively create and manage tables in a database.