Here we will learn how to use SQLite create table statement to create a table with example and create a table from select in SQLite with example
Generally in SQLite "CREATE TABLE" command is used to create a new table in the database.
The most basic syntax for CREATE TABLE in SQLite looks something like shown below.
CREATE [TEMP | TEMPORARY] TABLE table_name
(
Column1 column_type [constraint],
Column2 column_type [constraint],
[...]
);
If you observe the above syntax we mentioned different properties to create a table in SQLite those are
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 the example of creating a table in the SQLite database.
CREATE TABLE emp_master
( emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name VARCHAR NOT NULL,
last_name VARCHAR,
salary NUMERIC
);
In the above example of SQLite create a table, we have created a table named emp_master with 4 columns.
Now, we look at the example of creating a 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 a table named contacts having 3 columns.
We can also create a 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 a table from the select statement.
CREATE TABLE new_table AS
SELECT expressions
FROM existing_tables
[WHERE conditions];
In the above syntax, we have different properties to create a 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 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
SELECT *
FROM contacts
WHERE Phone<>'UNKNOWN';
This example would create a new table called active_contacts that included all the columns from the contacts table.
If there were records in the 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 an example that shows how to create a table by copying selected columns from multiple tables.
Following is the example of creating a 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 a 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.