Category: sqlite

SQLite – UPDATE Query ”; Previous Next SQLite UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows, otherwise all the rows would be updated. Syntax Following is the basic syntax of UPDATE query with WHERE clause. UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; You can combine N number of conditions using AND or OR operators. 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 update ADDRESS for a customer whose ID is 6. sqlite> UPDATE COMPANY SET ADDRESS = ”Texas” WHERE ID = 6; 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 Texas 45000.0 7 James 24 Houston 10000.0 If you want to modify all ADDRESS and SALARY column values in COMPANY table, you do not need to use WHERE clause and UPDATE query will be as follows − sqlite> UPDATE COMPANY SET ADDRESS = ”Texas”, SALARY = 20000.00; Now, COMPANY table will have the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 Texas 20000.0 2 Allen 25 Texas 20000.0 3 Teddy 23 Texas 20000.0 4 Mark 25 Texas 20000.0 5 David 27 Texas 20000.0 6 Kim 22 Texas 20000.0 7 James 24 Texas 20000.0 Print Page Previous Next Advertisements ”;

SQLite – INSERT Query ”; Previous Next SQLite INSERT INTO Statement is used to add new rows of data into a table in the database. Syntax Following are the two basic syntaxes of INSERT INTO statement. INSERT INTO TABLE_NAME [(column1, column2, column3,…columnN)] VALUES (value1, value2, value3,…valueN); Here, column1, column2,…columnN are the names of the columns in the table into which you want to insert data. You may not need to specify the column(s) name in the SQLite query if you are adding values for all the columns of the table. However, make sure the order of the values is in the same order as the columns in the table. The SQLite INSERT INTO syntax would be as follows − INSERT INTO TABLE_NAME VALUES (value1,value2,value3,…valueN); Example Consider you already have created COMPANY table in your testDB.db as follows − sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Now, the following statements would create six records in COMPANY table. INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Allen”, 25, ”Texas”, 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”Teddy”, 23, ”Norway”, 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Mark”, 25, ”Rich-Mond ”, 65000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, ”David”, 27, ”Texas”, 85000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, ”Kim”, 22, ”South-Hall”, 45000.00 ); You can create a record in COMPANY table using the second syntax as follows − INSERT INTO COMPANY VALUES (7, ”James”, 24, ”Houston”, 10000.00 ); All the above statements would create the following records in COMPANY table. In the next chapter, you will learn how to display all these records from a table. 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 Populate One Table Using Another Table You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate the first table. Here is the syntax − INSERT INTO first_table_name [(column1, column2, … columnN)] SELECT column1, column2, …columnN FROM second_table_name [WHERE condition]; For now, you can skip the above statement. First, let”s learn SELECT and WHERE clauses which will be covered in subsequent chapters. Print Page Previous Next Advertisements ”;

SQLite – DELETE Query ”; Previous Next SQLite DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the selected rows, otherwise all the records would be deleted. Syntax Following is the basic syntax of DELETE query with WHERE clause. DELETE FROM table_name WHERE [condition]; You can combine N number of conditions using AND or OR operators. 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 DELETE a customer whose ID is 7. sqlite> DELETE FROM COMPANY WHERE ID = 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 South-Hall 45000.0 If you want to DELETE all the records from COMPANY table, you do not need to use WHERE clause with DELETE query, which will be as follows − sqlite> DELETE FROM COMPANY; Now, COMPANY table does not have any record as all the records have been deleted by DELETE statement. Print Page Previous Next Advertisements ”;

SQLite – DISTINCT Keyword ”; Previous Next SQLite DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only the unique records. There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. Syntax Following is the basic syntax of DISTINCT keyword to eliminate duplicate records. SELECT DISTINCT column1, column2,…..columnN FROM table_name WHERE [condition] 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 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0 First, let us see how the following SELECT query returns duplicate salary records. sqlite> SELECT name FROM COMPANY; This will produce the following result. NAME ———- Paul Allen Teddy Mark David Kim James Paul James James Now, let us use DISTINCT keyword with the above SELECT query and see the result. sqlite> SELECT DISTINCT name FROM COMPANY; This will produce the following result, where there is no duplicate entry. NAME ———- Paul Allen Teddy Mark David Kim James Print Page Previous Next Advertisements ”;

SQLite – LIKE Clause ”; Previous Next SQLite LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. There are two wildcards used in conjunction with the LIKE operator − The percent sign (%) The underscore (_) The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations. Syntax Following is the basic syntax of % and _. SELECT FROM table_name WHERE column LIKE ”XXXX%” or SELECT FROM table_name WHERE column LIKE ”%XXXX%” or SELECT FROM table_name WHERE column LIKE ”XXXX_” or SELECT FROM table_name WHERE column LIKE ”_XXXX” or SELECT FROM table_name WHERE column LIKE ”_XXXX_” You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value. Example Following table lists a number of examples showing WHERE part having different LIKE clause with ”%” and ”_” operators. Sr.No. Statement & Description 1 WHERE SALARY LIKE ”200%” Finds any values that start with 200 2 WHERE SALARY LIKE ”%200%” Finds any values that have 200 in any position 3 WHERE SALARY LIKE ”_00%” Finds any values that have 00 in the second and third positions 4 WHERE SALARY LIKE ”2_%_%” Finds any values that start with 2 and are at least 3 characters in length 5 WHERE SALARY LIKE ”%2” Finds any values that end with 2 6 WHERE SALARY LIKE ”_2%3” Finds any values that has a 2 in the second position and ends with a 3 7 WHERE SALARY LIKE ”2___3” Finds any values in a five-digit number that starts with 2 and ends with 3 Let us take a real 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 display all the records from COMPANY table where AGE starts with 2. sqlite> SELECT * FROM COMPANY WHERE AGE LIKE ”2%”; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 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 display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text. sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE ”%-%”; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0 Print Page Previous Next Advertisements ”;

SQLite – WHERE Clause ”; Previous Next SQLite WHERE clause is used to specify a condition while fetching the data from one table or multiple tables. If the given condition is satisfied, means true, then it returns the specific value from the table. You will have to use WHERE clause to filter the records and fetching only necessary records. The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which will be covered in subsequent chapters. Syntax Following is the basic syntax of SQLite SELECT statement with WHERE clause. SELECT column1, column2, columnN FROM table_name WHERE [condition] Example You can specify a condition using Comparision or Logical Operators such as >, <, =, LIKE, NOT, etc. 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 a simple examples showing the usage of SQLite Logical Operators. Following SELECT statement lists down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00. sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00. sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE is not NULL, which means all the records because none of the record has AGE equal to NULL. sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL; 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 SELECT statement lists down all the records where NAME starts with ”Ki”, does not matter what comes after ”Ki”. sqlite> SELECT * FROM COMPANY WHERE NAME LIKE ”Ki%”; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 6 Kim 22 South-Hall 45000.0 Following SELECT statement lists down all the records where NAME starts with ”Ki”, does not matter what comes after ”Ki”. sqlite> SELECT * FROM COMPANY WHERE NAME GLOB ”Ki*”; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 6 Kim 22 South-Hall 45000.0 Following SELECT statement lists down all the records where AGE value is either 25 or 27. sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE value is neither 25 nor 27. sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27. sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement makes use of SQL sub-query, where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by the sub-query − sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000); AGE ———- 32 25 23 25 27 22 24 Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from the outside query is greater than the age in the result returned by the sub-query. sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 Print Page Previous Next Advertisements ”;

SQLite – Indexes ”; Previous Next Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers. An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data. Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order. Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there”s an index. The CREATE INDEX Command Following is the basic syntax of CREATE INDEX. CREATE INDEX index_name ON table_name; Single-Column Indexes A single-column index is one that is created based on only one table column. The basic syntax is as follows − CREATE INDEX index_name ON table_name (column_name); Unique Indexes Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows − CREATE UNIQUE INDEX index_name on table_name (column_name); Composite Indexes A composite index is an index on two or more columns of a table. The basic syntax is as follows − CREATE INDEX index_name on table_name (column1, column2); Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query”s WHERE clause as filter conditions. Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice. Implicit Indexes Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Example Following is an example where we will create an index in COMPANY table for salary column − sqlite> CREATE INDEX salary_index ON COMPANY (salary); Now, let”s list down all the indices available in COMPANY table using .indices command as follows − sqlite> .indices COMPANY This will produce the following result, where sqlite_autoindex_COMPANY_1 is an implicit index which got created when the table itself was created. salary_index sqlite_autoindex_COMPANY_1 You can list down all the indexes database wide as follows − sqlite> SELECT * FROM sqlite_master WHERE type = ”index”; The DROP INDEX Command An index can be dropped using SQLite DROP command. Care should be taken when dropping an index because performance may be slowed or improved. Following is the basic syntax is as follows − DROP INDEX index_name; You can use the following statement to delete previously created index. sqlite> DROP INDEX salary_index; When Should Indexes Be Avoided? Although indexes are intended to enhance the performance of a database, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered. Indexes should not be used in − Small tables. Tables that have frequent, large batch update or insert operations. Columns that contain a high number of NULL values. Columns that are frequently manipulated. Print Page Previous Next Advertisements ”;

SQLite – Commands ”; Previous Next This chapter will take you through simple and useful commands used by SQLite programmers. These commands are called SQLite dot commands and exception with these commands is that they should not be terminated by a semi-colon (;). Let”s start with typing a simple sqlite3 command at command prompt which will provide you with SQLite command prompt where you will issue various SQLite commands. $sqlite3 SQLite version 3.3.6 Enter “.help” for instructions sqlite> For a listing of the available dot commands, you can enter “.help” any time. For example − sqlite>.help The above command will display a list of various important SQLite dot commands, which are listed in the following table. Sr.No. Command & Description 1 .backup ?DB? FILE Backup DB (default “main”) to FILE 2 .bail ON|OFF Stop after hitting an error. Default OFF 3 .databases List names and files of attached databases 4 .dump ?TABLE? Dump the database in an SQL text format. If TABLE specified, only dump tables matching LIKE pattern TABLE 5 .echo ON|OFF Turn command echo on or off 6 .exit Exit SQLite prompt 7 .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on 8 .header(s) ON|OFF Turn display of headers on or off 9 .help Show this message 10 .import FILE TABLE Import data from FILE into TABLE 11 .indices ?TABLE? Show names of all indices. If TABLE specified, only show indices for tables matching LIKE pattern TABLE 12 .load FILE ?ENTRY? Load an extension library 13 .log FILE|off Turn logging on or off. FILE can be stderr/stdout 14 .mode MODE Set output mode where MODE is one of − csv − Comma-separated values column − Left-aligned columns. html − HTML <table> code insert − SQL insert statements for TABLE line − One value per line list − Values delimited by .separator string tabs − Tab-separated values tcl − TCL list elements 15 .nullvalue STRING Print STRING in place of NULL values 16 .output FILENAME Send output to FILENAME 17 .output stdout Send output to the screen 18 .print STRING… Print literal STRING 19 .prompt MAIN CONTINUE Replace the standard prompts 20 .quit Exit SQLite prompt 21 .read FILENAME Execute SQL in FILENAME 22 .schema ?TABLE? Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE 23 .separator STRING Change separator used by output mode and .import 24 .show Show the current values for various settings 25 .stats ON|OFF Turn stats on or off 26 .tables ?PATTERN? List names of tables matching a LIKE pattern 27 .timeout MS Try opening locked tables for MS milliseconds 28 .width NUM NUM Set column widths for “column” mode 29 .timer ON|OFF Turn the CPU timer measurement on or off Let”s try .show command to see default setting for your SQLite command prompt. sqlite>.show echo: off explain: off headers: off mode: column nullvalue: “” output: stdout separator: “|” width: sqlite> Make sure there is no space in between sqlite> prompt and dot command, otherwise it will not work. Formatting Output You can use the following sequence of dot commands to format your output. sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite> The above setting will produce the output in the following format. 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 CPU Time: user 0.000000 sys 0.000000 The sqlite_master Table The master table holds the key information about your database tables and it is called sqlite_master. You can see its schema as follows − sqlite>.schema sqlite_master This will produce the following result. CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text ); Print Page Previous Next Advertisements ”;

SQLite – DROP Table ”; Previous Next 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. Print Page Previous Next Advertisements ”;

SQLite – Overview ”; Previous Next This chapter helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that”s growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain. What is SQLite? SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system. SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly. Why SQLite? SQLite does not require a separate server process or system to operate (serverless). SQLite comes with zero-configuration, which means no setup or administration needed. A complete SQLite database is stored in a single cross-platform disk file. SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted. SQLite is self-contained, which means no external dependencies. SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads. SQLite supports most of the query language features found in SQL92 (SQL2) standard. SQLite is written in ANSI-C and provides simple and easy-to-use API. SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). SQLite A Brief History 2000 – D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program. 2000 – In August, SQLite 1.0 released with GNU Database Manager. 2011 – Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database). SQLite Limitations There are few unsupported features of SQL92 in SQLite which are listed in the following table. Sr.No. Feature & Description 1 RIGHT OUTER JOIN Only LEFT OUTER JOIN is implemented. 2 FULL OUTER JOIN Only LEFT OUTER JOIN is implemented. 3 ALTER TABLE The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported. 4 Trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers. 5 VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. 6 GRANT and REVOKE The only access permissions that can be applied are the normal file access permissions of the underlying operating system. SQLite Commands The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature − DDL – Data Definition Language Sr.No. Command & Description 1 CREATE Creates a new table, a view of a table, or other object in database. 2 ALTER Modifies an existing database object, such as a table. 3 DROP Deletes an entire table, a view of a table or other object in the database. DML – Data Manipulation Language Sr.No. Command & Description 1 INSERT Creates a record 2 UPDATE Modifies records 3 DELETE Deletes records DQL – Data Query Language Sr.No. Command & Description 1 SELECT Retrieves certain records from one or more tables Print Page Previous Next Advertisements ”;