In SQLite the CREATE TABLE Statement can be used to create table in a database.
A database in SQLite can contain one or more tables. The table conatins one or more rows as well as one or more columns.
Each table in SQLite must have a name.
The syntex for creating a new table in a database using SQLite is show below:
CREATE TABLE table_name ( column_name1 data_type PRIMARY KEY(can have one or more columns), column_name2 data_type, column_name3 data_type, .... column_nameN data_type, );
So let's consider we have created SCHOOL database. Now we want to create a STUDENTS table in this database.
sqlite> CREATE TABLE STUDENTS( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, SURNAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50) );
In the Example above we created a STUDENTS table with five columns (ID, NAME, SURNAME, AGE, ADDRESS). The table STUDENTS have ID column as primary key and NOT NULL. These are the constrains to the field ID so that field ID can not be NULL while creating records.
Now Lets say we want to create one more table in SCHOOL database. This time we will create a table called TEACHERS.
sqlite> CREATE TABLE TEACHERS( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, SURNAME TEXT NOT NULL, AGE INT NOT NULL, SUBJECT CHAR(50), SALARY REAL );
Above we have created two tables in the database. .tables command can be used to list down all the tables in the database.
sqlite>.tables STUDENTS TEACHERS
.schema command can be used to view the complete information or the table.
sqlite> .schema STUDENTS CREATE TABLE STUDENTS( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, SURNAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50) ); sqlite>