Create a Table

How to Create a Table in SQL

A table is a collection of data organized into rows and columns, and to create a table, you need to name it and define columns data types.

Here’s the basic syntax:

CREATE TABLE table_name( 
column1 datatype1, 
column2 datatype2, 
column3 datatype3,  
...  
);

You can replace “table_name” with the name of the table you want to create. Then, add column definitions inside the parentheses to specify columns’ names and data types. After running this statement, you’ll have an empty table to INSERT DATA.

See what it looks like in practice.

CREATE TABLE table_name ( 
id INT, 
name VARCHAR(50), 
email VARCHAR(100) 
);

This statement creates a table with three columns:

• “id” of integer type
• “name” of variable character type with a maximum length of 50 characters
• and “email” of variable character type with a maximum length of 100 characters.

You can also use the “AS SELECT * FROM VALUES” clause to create a table and insert the data. This statement uses the values from a set of rows enclosed in parentheses. The VALUES clause specifies the rows of data to be inserted into the table.

CREATE OR REPLACE TABLE table (PRODUCT TEXT, QUANTITY INT, PRICE INT) 
AS SELECT * FROM VALUES 
   ('Dress', 0,12), 
   ('Tshirt', NULL ,8);

In this case, the table will have three columns, as follows:

In this example, we used “CREATE OR REPLACE TABLE” to first check if the “table” exists, and if it does, replace it with the new table. This can be helpful if you want to modify an existing table’s structure or replace the entire table with a new one.