Here we will learn how to use sqlite create table statement to create table with example and create table from select in sqlite with example
Generally in SQLite "CREATE TABLE" command is used to create a new table in database.
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.
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,
In above example of sqlite create table, we have created table named emp_master with 4 columns.
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',
In this example, we have created table named contacts having 3 columns.
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).
Following is the syntax of creating table from select statement.
CREATE TABLE new_table AS
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,
Now let look at the example of CREATE TABLE AS statement in SQLite.
CREATE TABLE active_contacts AS
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.