Learn Sql

Creating, dropping, and altering tables in SQLite
In this part of the SQLite tutorial, we will cover the data definition language (DDL) of the SQLite database. The DDL consists of SQL statements that define the database schema. The schema is the database structure described in a formal language. In relational databases, the schema defines the tables, views, indexes, relationships, or triggers.
The SQLite supports the following three DDL statements:
CREATE
ALTER TABLE
DROP
In SQLite, the CREATE statement is used to create tables, indexes, views, and triggers. The ALTER TABLE statement changes the structure of a table. The DROP statement removes tables, indexes, views, or triggers.
Creating tables
The CREATE statement is used to create tables. It is also used to create indexes, views, and triggers.
To create a table, we give a name to a table and to its columns. Each column can have one of these data types:
NULL — The value is a NULL value
INTEGER — a signed integer
REAL — a floating point value
TEXT — a text string
BLOB — a blob of data sqlite> CREATE TABLE Testing(Id INTEGER); sqlite> .schema Testing
CREATE TABLE Testing(Id INTEGER);
We create a simple Testing table with the CREATE TABLE statement. The .schema command shows the formal definition of the table. sqlite> CREATE TABLE Testing(Id INTEGER);
Error: table Testing already exists
If we try to create a table that already exists, we get an error. Therefore the CREATE TABLE statement has an optional IF NOT EXISTS clause. With this clause nothing is done and we receive no error. sqlite> CREATE TABLE IF NOT EXISTS Testing(Id INTEGER);
We get no error message for trying to create a table that already exists.
The CREATE TABLE ... AS statement enables to create a new table based on a SELECT statement. sqlite> CREATE TABLE Cars2 AS SELECT * FROM Cars;
The above statement creates an identical table to the Cars table using a specific SELECT statement. sqlite> ATTACH