Mastering in SQLite

Data Definition

To visit the introduction page to see all the available topics click here

A number of superheroes are available in our DB, click here to download it.

SQLite Datatype

SQLite uses a dynamic type system. In other words, a value stored in a column determines its data type, not the column’s data type.

. NULL

NULL is used to store any NULL values.

. INTEGER

INTEGER keyword is used to store any numeric value as a signed integer value. (both positive and negative values).The INTEGER values in SQLite are stored in either 1,2,3,4,6 or 8 bytes of storage depending on the value of the number.

. REAL

REAL keyword is used to store the floating-point values, and they are stored in 8 bytes of storage.

. TEXT

TEXT stores text string. It also supports different encoding like UTF-8, UTF-16 BE, or UTF- 26LE.

. BLOB

BLOB is used to store large files, like images or text files. The value is stored as a byte array the same as the input value.

SQLite Create

In SQLite, the CREATE TABLE statement is used to create a new table. While creating the table, we name that table and define its column and data types of each column.

Syntax

CREATE TABLE tableName(
     column1 datatype PRIMARY KEY,
     column2 datatype NOT NULL,
     ...
     columnN datatype
 )

specify the column list of the table. Each column has a name, data type, and the column constraint.SQLite supports PRIMARY KEY, NOT NULL, CHECK, UNIQUE constraints.

Example

CREATE TABLE newTable(
     id INTEGER PRIMARY KEY,
     name TEXT NOT NULL,
     superPower TEXT,
     salary INTEGER,
     realName TEXT
 )

Output

Alter Table

In SQLite ALTER TABLE statement is used to change the structure of an existing table.
ALTER TABLE statement can perform two actions.
1) Rename a table name
2)Add a new column to a table

1)Alter table- Rename a table name

To rename a table, ALTER TABLE RENAME TO statement is used.

Syntax

ALTER TABLE existingTableName
RENAME TO newTableName

Example

ALTER TABLE
     newtable RENAME TO charactersDetail

Output

2) Alter table- add a new column

ALTER TABLE ADD COLUMN statement is used to add a new column to an existing table.SQLite appends the new column at the end of the existing columns list.

Syntax

ALTER TABLE tableName
ADD COLUMN columnDefinition

There are some restrictions on the new column
* The new column cannot have a UNIQUE or PRIMARY KEY constraint.
* If the new column has a NOT NULL constraint, you must specify a default value for the column other than a NULL value.

Example

ALTER TABLE
     charactersDetail
 ADD
     COLUMN nameInMovies TEXT

Output

Rename a column name

ALTER TABLE RENAME COLUMN statement is used to modify a column name in an existing table.

Syntax

ALTER TABLE
     TableName RENAME COLUMN oldName TO newName

Example

ALTER TABLE
     charactersDetail RENAME COLUMN nameInMovies TO characterName

Output

Drop-Table

DROP TABLE statement is used to remove the table in a database.

Syntax

DROP TABLE tablename

In this syntax, you specify the name of the table which you want to remove after the DROP TABLE keywords.SQLite allows you to drop only one table at a time.

Example

DROP TABLE charactersDetail

Output

table has been deleted

To reach out the next topic- Constraints

Kishorepremkumar