SQL Create Table Statement Example | Create Table in SQL Tutorial

By Krunal Last updated Apr 2, 2019

SQL Create Table Statement Example | Create Table in SQL Tutorial is today’s topic. A CREATE TABLE statement is used to create a new table in the database. SQL is the Standard Query Language for manipulating, storing and retrieving data in databases. SQL is used in MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems. SQL stands for Structured Query Language. SQL lets you access and manipulates databases. RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

SQL Create Table Statement Example

Working with SQL for data analysis and manipulation sometimes requires creating the new tables. Requirements like, Do you want to store the output of your SQL queries? Do you need to pull the new data sources (for example, csv files) into your data analysis? Do you want to store your transformed data without deleting your original data sets? In all of those scenarios, First, you have to know how to create tables in SQL.

Creating a primary table involves naming the table and defining its columns and each column’s data type.

See the following syntax of creating a table in SQL is following.

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

The column parameters indicate the names of the columns of a table.

The data type parameter specifies the type of data the column can hold (e.g., varchar, integer, date, boolean, etc.).

CREATE TABLE is a SQL keyword. You should always have it at the beginning of your SQL statement.

CREATE TABLE is the keyword telling the DBMS what you want to do. In this case, you want to create the new table. The unique name or identifier for the table follows the CREATE TABLE statement.

Then in brackets comes the list defining each column in the table and what sort of data type it is.

Let’s take the example of creating a table.

CREATE TABLE Apps (
    AppID int,
    AppName varchar(255),
    CreatorName varchar(255),
    AppCategory varchar(255),
    AppPrice int 
);

Now, run the query. I am using Sequel Pro for running the SQL queries. I have already connected the client to the Database. After running the query, Apps table will be created in the database.

It has created AppID, AppName, CreatorName, AppCategory, and AppPrice columns.

Create Table Using Another Table

The copy of an existing table can also be created using the CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected based on your requirement.

If you create a new table using the existing one, then a new table will be filled with all the existing values from the old table.

The syntax for creating a table using another table is following.

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

Let’s see the following example.

CREATE TABLE DummyApp AS
SELECT AppID, AppName
FROM Apps

So, we have created DummyApp table from Apps table.

Create Table With Extra parameters

After defining a data type of the column, you can add some extra parameters too. These are optional arguments and mostly technical things, but still, I will highlight the three most important parameters:

NOT NULL: If you add this to your column that means you can’t add NULL values to the given column.
UNIQUE: If you add this to your column that means you can’t add the same value to a column twice. It is especially important when you store unique user IDs. In these cases, duplicate values are not allowed.
PRIMARY KEY: Practically speaking, this is the combination of NOT NULL and UNIQUE, but it has some technical advantages as well. A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

Let’s see the following example by creating a new table with extra parameters.

CREATE TABLE test_results
(
  name         TEXT,
  student_id   INTEGER   PRIMARY KEY,
  birth_date   DATE,
  test_result  DECIMAL   NOT NULL,
  grade        TEXT      NOT NULL,
  passed       BOOLEAN   NOT NULL
);

So, in the above code, we have defined the student_id as a PRIMARY KEY and test_result, grade, and passed column has NOT NULL attribute. It means that those columns do not take NULL values while inserting the values inside the table. If NULL found, then it will throw an error.

The output is following.

Finally, SQL Create Table Statement Example | Create Table in SQL Tutorial is over.

MySQL SQL