How to Create Table in SQLite? Explain with example.

SQLite Create Table

Generally in SQLite "CREATE TABLE" command is used to create a new table in database.

SQLite Create Table Syntax

The most basic syntax for CREATE TABLE in SQLite looks something like show below.

CREATE [TEMP | TEMPORARY] TABLE table_name

(

Column1 column_type [constraint],

Column2 column_type [constraint],

[...]

);

 If you observe above syntax we mentioned different properties to create table in SQLite those are

table_name: The name of the table that you wish to create but it must valid identifier.

Column1, Column2: The columns that you wish to create in the table. It also must be valid identifier.

Column-type: The data type for the column like INTEGER, TEXT, etc.

Constraints: Constraints for the specific column like AUTOINCREMENT, PRIMARY KEY, NOT NULL, etc.

Here note that there can only be one column in a table that is set as AUTOINCREMENT with a data type of INTEGER. This column must be the primary key.

SQLite Create Table Example

Let’s look at example of creating table in SQLite database.

CREATE TABLE emp_master

( emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name VARCHAR NOT NULL,

last_name VARCHAR,

salary NUMERIC

);

In above example of sqlite create table, we have created table named emp_master with 4 columns.

• The first column is called emp_id which is created as an INTEGER data type. It has been defined as the primary key and is set as an AUTOINCREMENT field which means that it is an auto number field (starting at 1, and incrementing by 1, unless otherwise specified.)
• The second column named first_name which having VARCHAR data type and cannot contain null value.
• The third column named last_name which also having VARCHAR data type and it may contain NULL value.
• The forth column named salary which is having NUMERIC data type and it also may contain NULL value.

Now, we look at the example of create table with default value constraint.

CREATE TABLE contacts

(

Id INTEGER PRIMARY KEY,

Name TEXT NOT NULL,

Phone TEXT DEFAULT 'UNKNOWN',

UNIQUE(Name, Phone)

);

 In this example, we have created table named contacts having 3 columns.

• The first column named Id having data type INTEGER and having row level constraint PRIMARY KEY.
• The second column named Name which is having data type TEXT and also can not contain NULL value which is again row level constraint.
• The third column named Phone which is having data type TEXT and cannot contain NULL values. If no value is provided for this column, the DEFAULT VALUE will be UNKNOWN.
• After that, there is a table-level constraint of UNIQUE, which is defined for columns name and phone together.

SQLite Create table from SELECT

We can also create table in SQLite with SELECT statement by using CREATE TABLE AS command.

The SQLite CREATE TABLE AS statement is used to create a table from an existing table by copying the existing table's columns. 

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

SQL Create Table from Select Syntax

Following is the syntax of creating table from select statement.

CREATE TABLE new_table AS

SELECT expressions

FROM existing_tables

[WHERE conditions];

 In above syntax we have different properties to create table in SQLite using SELECT statement those are

table_name: The name of the table that you wish to create.

Expressions: The columns from the existing_tables that you would like created in the new_table. The column definitions from those columns listed will be transferred to the new_table that you create.

existing_tables: The existing tables from which to copy the column definitions and the associated records (as per the WHERE clause).

WHERE conditions: It is an optional. The conditions that must be met for the records to be copied to the new_table.

Here note that,

• The column definitions from the existing_tables will be copied to the new_table.
• The new_table will be populated with records based on the conditions in WHERE clause.

SQLite Create Table from Select Example

Now let look at the example of CREATE TABLE AS statement in SQLite.

CREATE TABLE active_contacts AS

SELECT *

FROM contacts

WHERE Phone<>'UNKNOWN';

This example would create a new table called active_contacts that included all the columns from contacts table.

If there were records in contacts table, then the new active_contacts table would be populated with the records returned by the SELECT statement.

Now, let's look at a CREATE TABLE AS example that shows how to create a table by copying selected columns from multiple tables.

Following is the example of creating table by copying selected columns from multiple tables.

CREATE TABLE active_emps AS

SELECT emp_master.employee_id AS "active_employee_id",

emp_master.last_name, emp_master.first_name, dept_master.department_name

FROM emp_master, dept_master

WHERE emp_master.department_id = dept_master.department_id

AND emp_master.hire_date IS NOT NULL;

This example would create table called active_emps based on column definitions from both the emp_master and dept_master tables. Here it contains records which hire_date is not equal to Null and only employees records where department_id match in both the tables.