General Note: Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database

In this tutorial, we are going to see how to create table in SQLite database.

CREATE TABLE keyword is used to create table in SQLite.

Syntax:


CREATE TABLE table_name (column_name datatype DEFAULT value, )

Here table_name and column_name are user defined names. So if you are going to give your own naming to table or column, you must follow some rules.

They are Column Naming rules:

  • Do not use any reserved keyword
  • Must begin with letter
  • Minimum length is 1 and do not know max length. We have used 50 length variable (It supported)
  • Must contain only A_Z, a-z, _, $
  • Must not duplicate the name

The above rules are applicable to name both the tables and columns also.

If you want to use a reserved keyword as a name like SELECT or FROM, etc., you need to quote it. There are four ways of quoting keywords in SQLite:

'keyword'   A keyword in single quotes is a string literal. Ex: 'SELECT'
"keyword"   A keyword in double-quotes is an identifier. Ex: "SELECT"
[keyword]   A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility. Ex: [SELECT]
`keyword`   A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility. Ex: `SELECT`

So you must follow those above rules to name tables and columns.

Every column hold one datatype. SQLite supports wide range of datatype. If you want to know all data type available in SQLite click here. We always look for data type whenever we are going to learn programming language or database. Because data types are essential part of database tables to hold data, we can not store irrelevant data on same column. However, while working with SQLite, we came to know that, data types are not much important here, because in SQLite a number column can support text also. Furthermore, even if you give some invalid data type (like id act) it will support while creating table. It will not throw any error if you give anything as datatype.

We will explain this when we get deeper into this topic later in the section.

Here we are going to create employee table, which hold employees id, employee name, age and mobileno


Create table employee (id int, name text, age int, mobileno int)

In the above query we did not assign any default values to column. So the default value for all the above column is null.

Here we are going to create another employee table (employee1 because I already created employee table), but the salary column has default value. If you skip the salary column while inserting data to employee1 table, it will get 2000 as it value.


Create table employee1 (id int, name text, salary int default 2000)

The below employee table holds date and time as their default value using current_timestamp


Create table employee2 (name text,joined_date timestamp default current_timestamp)

Alternative to the above query is datetime


CREATETABLE whatever(    ....     timestamp DATEDEFAULT(datetime('now','localtime')),    ...);

Create table using the select Query

You can create table using SELECT query. Whatever the query is returning is created as table. By using this we can take a copy of table or partially can copy table data as per our requirement.

Syntax:


Create table table_name as select *|Column_list from table_name1 
[where] [group by] [having] [order by]

If you want take copy of one table you can write like


Create table empdata as select * from employee;

So this query will take a copy of employee table’s data.

If you want to partially copy data


Create table empdata1 as select name from employee;

In SQLite column data type is not followed strictly. Just see the below query


Create table funny(id f);

Table funny is created without any error. The column id’s data type is f, but we do not have any data type like f. If you try to insert values in the funny table, it will store the value as usual.


Select * from sqlite_master;

Master table for storing all the Meta data related to objects which are stored in SQLite database (like data dictionary in oracle)