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