SQLite
How to Download & Install SQLite on Windows
SQLite offers a lot of different installation packages, depending on your operating systems. It...
In this article, we will see how to create tables, modify tables and dropping tables in SQLite3 with examples.
In this tutorial, you will learn-
Syntax
Below is the syntax of CREATE TABLE statement.
CREATE TABLE table_name( column1 datatype, column1 datatype );
To create a table, you should use the "CREATE TABLE" Query as follows:
CREATE TABLE guru99 ( Id Int, Name Varchar );
Within the two brackets after the table name, you define the tables' columns, each column should have the following properties:
To drop a table, use the "DROP TABLE" command followed by the table name as follows:
DROP TABLE guru99;
You can use "ALTER TABLE" command to rename a table as follows:
ALTER TABLE guru99 RENAME TO guru100;
To verify that the table's name is changed, you can use the command ".tables" to show the list of tables and the table name should be changed now as following:
As you can see the table name "guru99" is changed to "guru100" after the "alter table" command.
You can also use the "ALTER TABLE" command to add columns:
ALTER TABLE guru100 ADD COLUMN Age INT;
This will alter the table "guru100" and add a new column Age to it.
To insert values into a table, we use the "INSERT INTO" statement as follow:
INSERT INTO Tablename(colname1, colname2, ….) VALUES(valu1, value2, ….);
You can omit the columns names after the table name and write it as follows:
INSERT INTO Tablename VALUES(value1, value2, ….);
In such case, where you are omitting the columns names from the tables, the number of inserted values must be the same exact number of the table's columns. Then each value will be inserted in the correspondence column. For example, for the following insert statement:
INSERT INTO guru100 VALUES(1, 'Mike', 25);
The result of this statement will be as following:
You can populate the table with the default values for the columns at once as follows:
INSERT INTO Tablename DEFAULT VALUES;
If a column doesn't allow a null value nor a default value, you will get an error that "NOT NULL constraint failed" for that column. As following:
SQLite offers a lot of different installation packages, depending on your operating systems. It...
To write SQL queries in an SQLite database, you have to know how the SELECT, FROM, WHERE, GROUP...
SQLite databases are very lightweight. Unlike other database systems, there is no configuration,...
In this tutorial, you will learn- SQLite constraint Primary Key Not null constraint DEFAULT...
$20.20 $9.99 for today 4.6 (119 ratings) Key Highlights of SQLite PDF 159+ pages eBook Designed for...
Data types in SQLite are different compared to other database management system. In SQLite, you...