How to Create a Table in SQL

SQL stands for Structured Query Language. 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 lets you access and manipulates databases. RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL and all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

SQL Create Table

To create a table in SQL, use the CREATE Statement. A CREATE TABLE statement is used to create a new table in the Database.

Working with SQL for data analysis and manipulation sometimes requires creating 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 those scenarios, you must first 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.

Syntax

See the following syntax for 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. Therefore, 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 a 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 data type it is.

Example

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, the Apps table will be created in the Database.

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

How to create a table using another table in SQL

To create a table using another table in SQL, use the CREATE TABLE. It will be a copy of an existing 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, a new table will be filled with all the existing values from the old one.

The syntax for creating a table using another table is the 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 the DummyApp table from the Apps table.

Create a table with extra parameters in SQL

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

1. NOT NULL: If you add this to your column, you can’t add NULL values to the given column.
2. UNIQUE: If you add this to your column, that means you can’t add the same value to a column twice. It is essential when you store unique user IDs. In these cases, duplicate values are not allowed.
3. PRIMARY KEY: This combination of NOT NULL and UNIQUE has some technical advantages. A primary key is a field in a table that 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 primary keys, they are called composite keys.

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 columns have NOT NULL attributes. It means that those columns do not take NULL values while inserting the values inside the table. If NULL is found, then it will throw an error.

The output is the following.

That’s it for this tutorial.