SQLite NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Syntax

Following is the basic syntax of using NULL while creating a table.

SQLite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Here, NOT NULL signifies that the column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL which means these columns could be NULL.

A field with a NULL value is one that has been left blank during record creation.

Example

The NULL value can cause problems when selecting data, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results. Consider the following table, with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Let us use UPDATE statement to set a few nullable values as NULL as follows −

sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Now, COMPANY table will have the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22
7           James       24

Next, let us see the usage of IS NOT NULL operator to list down all the records where SALARY is not NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;

The above SQLite statement will produce the following result −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Following is the usage of IS NULL operator, which will list down all the records where SALARY is NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

The above SQLite statement will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22
7           James       24

SQLite ALTER TABLE command modifies an existing table without performing a full dump and reload of the data. You can rename a table using ALTER TABLE statement and additional columns can be added in an existing table using ALTER TABLE statement.

There is no other operation supported by ALTER TABLE command in SQLite except renaming a table and adding a column in an existing table.

Syntax

Following is the basic syntax of ALTER TABLE to RENAME an existing table.

ALTER TABLE database_name.table_name RENAME TO new_table_name;

Following is the basic syntax of ALTER TABLE to add a new column in an existing table.

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

Example

Consider the table with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Now, let”s try to rename this table using ALTER TABLE statement as follows −

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

The above SQLite statement will rename COMPANY table to OLD_COMPANY. Now, let”s try to add a new column in OLD_COMPANY table as follows −

sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

COMPANY table is now changed and following will be the output from SELECT statement.

ID          NAME        AGE         ADDRESS     SALARY      SEX
----------  ----------  ----------  ----------  ----------  ---
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

It should be noted that newly added column is filled with NULL values.

The “INDEXED BY index-name” clause specifies that the named index must be used in order to look up values on the preceding table.

If index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails.

The “NOT INDEXED” clause specifies that no index shall be used when accessing the preceding table, including implied indices created by UNIQUE and PRIMARY KEY constraints.

However, the INTEGER PRIMARY KEY can still be used to look up entries even when “NOT INDEXED” is specified.

Syntax

Following is the syntax for INDEXED BY clause and it can be used with DELETE, UPDATE or SELECT statement.

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

Example

Consider table We will create an index and use it for performing INDEXED BY operation.

sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

Now selecting the data from table COMPANY you can use INDEXED BY clause as follows −

sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Unfortunately, we do not have TRUNCATE TABLE command in SQLite but you can use SQLite DELETE command to delete complete data from an existing table, though it is recommended to use DROP TABLE command to drop the complete table and re-create it once again.

Syntax

Following is the basic syntax of DELETE command.

sqlite> DELETE FROM table_name;

Following is the basic syntax of DROP TABLE.

sqlite> DROP TABLE table_name;

If you are using DELETE TABLE command to delete all the records, it is recommended to use VACUUM command to clear unused space.

Example

Consider table with the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Following is the example to truncate the above table −

SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;

Now, COMPANY table is truncated completely and nothing will be the output from SELECT statement.

A view is nothing more than a SQLite statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQLite query.

A view can contain all rows of a table or selected rows from one or more tables. A view can be created from one or many tables which depends on the written SQLite query to create a view.

Views which are kind of virtual tables, allow the users to −

• Structure data in a way that users or classes of users find natural or intuitive.

• Restrict access to the data such that a user can only see limited data instead of a complete table.

• Summarize data from various tables, which can be used to generate reports.

SQLite views are read-only and thus you may not be able to execute a DELETE, INSERT or UPDATE statement on a view. However, you can create a trigger on a view that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.

Creating Views

SQLite views are created using the CREATE VIEW statement. SQLite views can be created from a single table, multiple tables, or another view.

Following is the basic CREATE VIEW syntax.

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query. If the optional TEMP or TEMPORARY keyword is present, the view will be created in the temp database.

Example

Consider table with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Following is an example to create a view from COMPANY table. This view will be used to have only a few columns from COMPANY table.

sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

You can now query COMPANY_VIEW in a similar way as you query an actual table. Following is an example −

sqlite> SELECT * FROM COMPANY_VIEW;

This will produce the following result.

ID          NAME        AGE
----------  ----------  ----------
1           Paul        32
2           Allen       25
3           Teddy       23
4           Mark        25
5           David       27
6           Kim         22
7           James       24

Dropping Views

To drop a view, simply use the DROP VIEW statement with the view_name. The basic DROP VIEW syntax is as follows −

sqlite> DROP VIEW view_name;

The following command will delete COMPANY_VIEW view, which we created in the last section.

sqlite> DROP VIEW COMPANY_VIEW;

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Practically, you will club many SQLite queries into a group and you will execute all of them together as part of a transaction.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID.

• Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

• Consistency − Ensures that the database properly changes states upon a successfully committed transaction.

• Isolation − Enables transactions to operate independently of and transparent to each other.

• Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

Following are the following commands used to control transactions:

• BEGIN TRANSACTION − To start a transaction.

• COMMIT − To save the changes, alternatively you can use END TRANSACTION command.

• ROLLBACK − To rollback the changes.

Transactional control commands are only used with DML commands INSERT, UPDATE, and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

BEGIN TRANSACTION Command

Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. However, a transaction will also ROLLBACK if the database is closed or if an error occurs. Following is the simple syntax to start a transaction.

BEGIN;
or
BEGIN TRANSACTION;

COMMIT Command

COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

Following is the syntax for COMMIT command.

COMMIT;
or
END TRANSACTION;

ROLLBACK Command

ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Following is the syntax for ROLLBACK command.

ROLLBACK;

Example

Consider table with the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Now, let”s start a transaction and delete records from the table having age = 25. Then, use ROLLBACK command to undo all the changes.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;

Now, if you check COMPANY table, it still has the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Let”s start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;

If you now check COMPANY table is still has the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

If you take user input through a webpage and insert it into a SQLite database there”s a chance that you have left yourself wide open for a security issue known as SQL Injection. In this chapter, you will learn how to help prevent this from happening and help you secure your scripts and SQLite statements.

Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQLite statement that you will unknowingly run on your database.

Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the following example, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars – modify these rules as needed.

if (preg_match("/^w{8,20}$/", $_GET[''username''], $matches)){
   $db = new SQLiteDatabase(''filename'');
   $result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
   echo "username not accepted";
}

To demonstrate the problem, consider this excerpt −

$name = "Qadir DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = ''{$name}''");

The function call is supposed to retrieve a record from the users table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. However in this case, by appending an entirely new query to $name, the call to the database turns into a disaster: the injected DELETE query removes all records from users.

There are databases interfaces which do not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails but SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.

Preventing SQL Injection

You can handle all escape characters smartly in scripting languages like PERL and PHP. Programming language PHP provides the function string sqlite_escape_string() to escape input characters that are special to SQLite.

if (get_magic_quotes_gpc()) {
   $name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = ''{$name}''");

Although the encoding makes it safe to insert the data, it will render simple text comparisons and LIKE clauses in your queries unusable for the columns that contain the binary data.

Note − addslashes() should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your data.

SQLite ORDER BY clause is used to sort the data in an ascending or descending order, based on one or more columns.

Syntax

Following is the basic syntax of ORDER BY clause.

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be available in the column-list.

Example

Consider COMPANY table with the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Following is an example, which will sort the result in descending order by SALARY.

sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Following is an example, which will sort the result in descending order by NAME and SALARY.

sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
5           David       27          Texas       85000.0
7           James       24          Houston     10000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0

Following is an example, which will sort the result in descending order by NAME.

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
1           Paul        32          California  20000.0
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
5           David       27          Texas       85000.0
2           Allen       25          Texas       15000.0

SQLite PRAGMA command is a special command to be used to control various environmental variables and state flags within the SQLite environment. A PRAGMA value can be read and it can also be set based on the requirements.

Syntax

To query the current PRAGMA value, just provide the name of the pragma.

PRAGMA pragma_name;

To set a new value for PRAGMA, use the following syntax.

PRAGMA pragma_name = value;

The set mode can be either the name or the integer equivalent but the returned value will always be an integer.

auto_vacuum Pragma

The auto_vacuum pragma gets or sets the auto-vacuum mode. Following is the simple syntax.

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

Where mode can be any of the following −

cache_size Pragma

The cache_size pragma can get or temporarily set the maximum size of the in-memory page cache. Following is the simple syntax.

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

The pages value represents the number of pages in the cache. The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages.

case_sensitive_like Pragma

The case_sensitive_like pragma controls the case-sensitivity of the built-in LIKE expression. By default, this pragma is false which means that the built-in LIKE operator ignores the letter case. Following is the simple syntax.

PRAGMA case_sensitive_like = [true|false];

There is no way to query for the current state of this pragma.

count_changes Pragma

count_changes pragma gets or sets the return value of data manipulation statements such as INSERT, UPDATE and DELETE. Following is the simple syntax.

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

By default, this pragma is false and these statements do not return anything. If set to true, each of the mentioned statement will return a one-column, one-row table consisting of a single integer value indicating impacted rows by the operation.

database_list Pragma

The database_list pragma will be used to list down all the databases attached. Following is the simple syntax.

PRAGMA database_list;

This pragma will return a three-column table with one row per open or attached database giving database sequence number, its name and the file associated.

encoding Pragma

The encoding pragma controls how strings are encoded and stored in a database file. Following is the simple syntax.

PRAGMA encoding;
PRAGMA encoding = format;

The format value can be one of UTF-8, UTF-16le, or UTF-16be.

freelist_count Pragma

The freelist_count pragma returns a single integer indicating how many database pages are currently marked as free and available. Following is the simple syntax.

PRAGMA [database.]freelist_count;

The format value can be one of UTF-8, UTF-16le, or UTF-16be.

index_info Pragma

The index_info pragma returns information about a database index. Following is the simple syntax.

PRAGMA [database.]index_info( index_name );

The result set will contain one row for each column contained in the index giving column sequence, column index with-in table and column name.

index_list Pragma

index_list pragma lists all of the indexes associated with a table. Following is the simple syntax.

PRAGMA [database.]index_list( table_name );

The result set will contain one row for each index giving index sequence, index name and flag indicating whether the index is unique or not.

journal_mode Pragma

The journal_mode pragma gets or sets the journal mode which controls how the journal file is stored and processed. Following is the simple syntax.

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

There are five supported journal modes as listed in the following table.

max_page_count Pragma

The max_page_count pragma gets or sets the maximum allowed page count for a database. Following is the simple syntax.

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

The default value is 1,073,741,823 which is one giga-page, which means if the default 1 KB page size, this allows databases to grow up to one terabyte.

page_count Pragma

The page_count pragma returns in the current number of pages in the database. Following is the simple syntax −

PRAGMA [database.]page_count;

The size of the database file should be page_count * page_size.

page_size Pragma

The page_size pragma gets or sets the size of the database pages. Following is the simple syntax.

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

By default, the allowed sizes are 512, 1024, 2048, 4096, 8192, 16384, and 32768 bytes. The only way to alter the page size on an existing database is to set the page size and then immediately VACUUM the database.

parser_trace Pragma

The parser_trace pragma controls printing the debugging state as it parses SQL commands. Following is the simple syntax.

PRAGMA parser_trace = [true|false];

By default, it is set to false but when enabled by setting it to true, the SQL parser will print its state as it parses SQL commands.

recursive_triggers Pragma

The recursive_triggers pragma gets or sets the recursive trigger functionality. If recursive triggers are not enabled, a trigger action will not fire another trigger. Following is the simple syntax.

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

schema_version Pragma

The schema_version pragma gets or sets the schema version value that is stored in the database header. Following is the simple syntax.

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

This is a 32-bit signed integer value that keeps track of schema changes. Whenever a schema-altering command is executed (like, CREATE… or DROP…), this value is incremented.

secure_delete Pragma

The secure_delete pragma is used to control how the content is deleted from the database. Following is the simple syntax.

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

The default value for the secure delete flag is normally off, but this can be changed with the SQLITE_SECURE_DELETE build option.

sql_trace Pragma

The sql_trace pragma is used to dump SQL trace results to the screen. Following is the simple syntax.

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

SQLite must be compiled with the SQLITE_DEBUG directive for this pragma to be included.

synchronous Pragma

The synchronous pragma gets or sets the current disk synchronization mode, which controls how aggressively SQLite will write data all the way out to physical storage. Following is the simple syntax.

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLite supports the following synchronization modes as listed in the table.

temp_store Pragma

The temp_store pragma gets or sets the storage mode used by temporary database files. Following is the simple syntax.

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite supports the following storage modes.

temp_store_directory Pragma

The temp_store_directory pragma gets or sets the location used for temporary database files. Following is the simple syntax.

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = ''directory_path

user_version Pragma

The user_version pragma gets or sets the user-defined version value that is stored in the database header. Following is the simple syntax.

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

This is a 32-bit signed integer value, which can be set by the developer for version tracking purpose.

writable_schema Pragma

The writable_schema pragma gets or sets the ability to modify system tables. Following is the simple syntax.

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

If this pragma is set, tables that start with sqlite_ can be created and modified, including the sqlite_master table. Be careful while using pragma because it can lead to complete database corruption.

SQLite DROP TABLE statement is used to remove a table definition and all associated data, indexes, triggers, constraints, and permission specifications for that table.

You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever.

Syntax

Following is the basic syntax of DROP TABLE statement. You can optionally specify the database name along with table name as follows −

DROP TABLE database_name.table_name;

Example

Let us first verify COMPANY table and then we will delete it from the database.

sqlite>.tables
COMPANY       test.COMPANY

This means COMPANY table is available in the database, so let us drop it as follows −

sqlite>DROP TABLE COMPANY;
sqlite>

Now, if you try .TABLES command, then you will not find COMPANY table anymore.

sqlite>.tables
sqlite>

It shows nothing which means the table from your database has been dropped successfully.