Category: sqlite

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

SQLite – Python ”; Previous Next In this chapter, you will learn how to use SQLite in Python programs. Installation SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards. To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements. Python sqlite3 module APIs Following are important sqlite3 module routines, which can suffice your requirement to work with SQLite database from your Python program. If you are looking for a more sophisticated application, then you can look into Python sqlite3 module”s official documentation. Sr.No. API & Description 1 sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file. You can use “:memory:” to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object. When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds). If the given database name does not exist then this call will create the database. You can specify filename with the required path as well if you want to create a database anywhere else except in the current directory. 2 connection.cursor([cursorClass]) This routine creates a cursor which will be used throughout of your database programming with Python. This method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor. 3 cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style). For example − cursor.execute(“insert into people values (?, ?)”, (who, age)) 4 connection.execute(sql [, optional parameters]) This routine is a shortcut of the above execute method provided by the cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor”s execute method with the parameters given. 5 cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. 6 connection.executemany(sql[, parameters]) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor.s executemany method with the parameters given. 7 cursor.executescript(sql_script) This routine executes multiple SQL statements at once provided in the form of script. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. All the SQL statements should be separated by a semi colon (;). 8 connection.executescript(sql_script) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor”s executescript method with the parameters given. 9 connection.total_changes() This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. 10 connection.commit() This method commits the current transaction. If you don”t call this method, anything you did since the last call to commit() is not visible from other database connections. 11 connection.rollback() This method rolls back any changes to the database since the last call to commit(). 12 connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost! 13 cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. 14 cursor.fetchmany([size = cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter. 15 cursor.fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available. Connect To Database Following Python code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. #!/usr/bin/python import sqlite3 conn = sqlite3.connect(”test.db”) print “Opened database successfully”; Here, you can also supply database name as the special name :memory: to create a database in RAM. Now, let”s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. Keep the above code in sqlite.py file and execute it as shown below. If the database is successfully created, then it will display the following message. $chmod +x sqlite.py $./sqlite.py Open database successfully Create a Table Following Python program will be used to create a table in the previously created database. #!/usr/bin/python import sqlite3 conn = sqlite3.connect(”test.db”) print “Opened database successfully”; conn.execute(”””CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);”””) print “Table created successfully”; conn.close() When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages − Opened database successfully Table created successfully INSERT Operation Following Python program shows how to create records in the COMPANY table created in the above example. #!/usr/bin/python import sqlite3 conn = sqlite3.connect(”test.db”) print “Opened database successfully”; conn.execute(“INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 )”); conn.execute(“INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Allen”, 25, ”Texas”, 15000.00 )”); conn.execute(“INSERT INTO COMPANY

Discuss SQLite ”; Previous Next SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. This tutorial will give you a quick start with SQLite and make you comfortable with SQLite programming. Print Page Previous Next Advertisements ”;

SQLite – C/C++ ”; Previous Next In this chapter, you will learn how to use SQLite in C/C++ programs. Installation Before you start using SQLite in our C/C++ programs, you need to make sure that you have SQLite library set up on the machine. You can check SQLite Installation chapter to understand the installation process. C/C++ Interface APIs Following are important C/C++ SQLite interface routines, which can suffice your requirement to work with SQLite database from your C/C++ program. If you are looking for a more sophisticated application, then you can look into SQLite official documentation. Sr.No. API & Description 1 sqlite3_open(const char *filename, sqlite3 **ppDb) This routine opens a connection to an SQLite database file and returns a database connection object to be used by other SQLite routines. If the filename argument is NULL or ”:memory:”, sqlite3_open() will create an in-memory database in RAM that lasts only for the duration of the session. If the filename is not NULL, sqlite3_open() attempts to open the database file by using its value. If no file by that name exists, sqlite3_open() will open a new database file by that name. 2 sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg) This routine provides a quick, easy way to execute SQL commands provided by sql argument which can consist of more than one SQL command. Here, the first argument sqlite3 is an open database object, sqlite_callback is a call back for which data is the 1st argument and errmsg will be returned to capture any error raised by the routine. SQLite3_exec() routine parses and executes every command given in the sql argument until it reaches the end of the string or encounters an error. 3 sqlite3_close(sqlite3*) This routine closes a database connection previously opened by a call to sqlite3_open(). All prepared statements associated with the connection should be finalized prior to closing the connection. If any queries remain that have not been finalized, sqlite3_close() will return SQLITE_BUSY with the error message Unable to close due to unfinalized statements. Connect To Database Following C code segment shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. #include <stdio.h> #include <sqlite3.h> int main(int argc, char* argv[]) { sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open(“test.db”, &db); if( rc ) { fprintf(stderr, “Can”t open database: %sn”, sqlite3_errmsg(db)); return(0); } else { fprintf(stderr, “Opened database successfullyn”); } sqlite3_close(db); } Now, let”s compile and run the above program to create our database test.db in the current directory. You can change your path as per your requirement. $gcc test.c -l sqlite3 $./a.out Opened database successfully If you are going to use C++ source code, then you can compile your code as follows − $g++ test.c -l sqlite3 Here, we are linking our program with sqlite3 library to provide required functions to C program. This will create a database file test.db in your directory and you will have the following result. -rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out -rw-r–r–. 1 root root 323 May 8 02:05 test.c -rw-r–r–. 1 root root 0 May 8 02:06 test.db Create a Table Following C code segment will be used to create a table in the previously created database − #include <stdio.h> #include <stdlib.h> #include <sqlite3.h> static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i = 0; i<argc; i++) { printf(“%s = %sn”, azColName[i], argv[i] ? argv[i] : “NULL”); } printf(“n”); return 0; } int main(int argc, char* argv[]) { sqlite3 *db; char *zErrMsg = 0; int rc; char *sql; /* Open database */ rc = sqlite3_open(“test.db”, &db); if( rc ) { fprintf(stderr, “Can”t open database: %sn”, sqlite3_errmsg(db)); return(0); } else { fprintf(stdout, “Opened database successfullyn”); } /* Create SQL statement */ sql = “CREATE TABLE COMPANY(” “ID INT PRIMARY KEY NOT NULL,” “NAME TEXT NOT NULL,” “AGE INT NOT NULL,” “ADDRESS CHAR(50),” “SALARY REAL );”; /* Execute SQL statement */ rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg); if( rc != SQLITE_OK ){ fprintf(stderr, “SQL error: %sn”, zErrMsg); sqlite3_free(zErrMsg); } else { fprintf(stdout, “Table created successfullyn”); } sqlite3_close(db); return 0; } When the above program is compiled and executed, it will create COMPANY table in your test.db and the final listing of the file will be as follows − -rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out -rw-r–r–. 1 root root 1207 May 8 02:31 test.c -rw-r–r–. 1 root root 3072 May 8 02:31 test.db INSERT Operation Following C code segment shows how you can create records in COMPANY table created in the above example − #include <stdio.h> #include <stdlib.h> #include <sqlite3.h> static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i = 0; i<argc; i++) { printf(“%s = %sn”, azColName[i], argv[i] ? argv[i] : “NULL”); } printf(“n”); return 0; } int main(int argc, char* argv[]) { sqlite3 *db; char *zErrMsg = 0; int rc; char *sql; /* Open database */ rc = sqlite3_open(“test.db”, &db); if( rc ) { fprintf(stderr, “Can”t open database: %sn”, sqlite3_errmsg(db)); return(0); } else { fprintf(stderr, “Opened database successfullyn”); } /* Create SQL statement */ sql = “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 );”; /* Execute SQL statement */ rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg); if( rc != SQLITE_OK ){ fprintf(stderr, “SQL error: %sn”, zErrMsg); sqlite3_free(zErrMsg); } else { fprintf(stdout, “Records created successfullyn”); } sqlite3_close(db); return 0; } When the above program is compiled and executed, it will create the given records in COMPANY table and will display the following two lines − Opened database successfully Records created successfully SELECT Operation Before proceeding with actual example to fetch records, let us look at some detail about the callback function, which we

SQLite – TRUNCATE TABLE Command ”; Previous Next 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 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 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. Print Page Previous Next Advertisements ”;

SQLite – GLOB Clause ”; Previous Next SQLite GLOB operator is used to match only text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the GLOB operator will return true, which is 1. Unlike LIKE operator, GLOB is case sensitive and it follows syntax of UNIX for specifying THE following wildcards. The asterisk sign (*) The question mark (?) The asterisk sign (*) represents zero or multiple numbers or characters. The question mark (?) represents a single number or character. Syntax Following is the basic syntax of * and ?. SELECT FROM table_name WHERE column GLOB ”XXXX*” or SELECT FROM table_name WHERE column GLOB ”*XXXX*” or SELECT FROM table_name WHERE column GLOB ”XXXX?” or SELECT FROM table_name WHERE column GLOB ”?XXXX” or SELECT FROM table_name WHERE column GLOB ”?XXXX?” or SELECT FROM table_name WHERE column GLOB ”????” 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 GLOB clause with ”*” and ”?” operators. Sr.No. Statement & Description 1 WHERE SALARY GLOB ”200*” Finds any values that start with 200 2 WHERE SALARY GLOB ”*200*” Finds any values that have 200 in any position 3 WHERE SALARY GLOB ”?00*” Finds any values that have 00 in the second and third positions 4 WHERE SALARY GLOB ”2??” Finds any values that start with 2 and are at least 3 characters in length 5 WHERE SALARY GLOB ”*2” Finds any values that end with 2 6 WHERE SALARY GLOB ”?2*3” Finds any values that have a 2 in the second position and end with a 3 7 WHERE SALARY GLOB ”2???3” Finds any values in a five-digit number that start with 2 and end 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 GLOB ”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 GLOB ”*-*”; 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 – NULL Values ”; Previous Next 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, COMPANY 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 Print Page Previous Next Advertisements ”;

SQLite – Subqueries ”; Previous Next A Subquery or Inner query or Nested query is a query within another SQLite query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators such as =, <, >, >=, <=, IN, BETWEEN, etc. There are a few rules that subqueries must follow − Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery. Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator. BETWEEN operator cannot be used with a subquery; however, BETWEEN can be used within the subquery. Subqueries with SELECT Statement Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows − SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE]) 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 Now, let us check the following sub-query with SELECT statement. sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Subqueries with INSERT Statement Subqueries can also be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions. Following is the basic syntax is as follows − INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ] Example Consider a table COMPANY_BKP with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. To copy the complete COMPANY table into COMPANY_BKP, following is the syntax − sqlite> INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ; Subqueries with UPDATE Statement The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement. Following is the basic syntax is as follows − UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] Example Assuming, we have COMPANY_BKP table available which is a backup of COMPANY table. Following example updates SALARY by 0.50 times in COMPANY table for all the customers, whose AGE is greater than or equal to 27. sqlite> UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 ); This would impact two rows and finally COMPANY table would have the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 10000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Subqueries with DELETE Statement Subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above. Following is the basic syntax is as follows − DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] Example Assuming, we have COMPANY_BKP table available which is a backup of COMPANY table. Following example deletes records from COMPANY table for all the customers whose AGE is greater than or equal to 27. sqlite> DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 ); This will impact two rows and finally COMPANY table will have the following records − 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 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Print Page Previous Next Advertisements ”;

SQLite – Date & Time ”; Previous Next SQLite supports five date and time functions as follows − Sr.No. Function Example 1 date(timestring, modifiers…) This returns the date in this format: YYYY-MM-DD 2 time(timestring, modifiers…) This returns the time as HH:MM:SS 3 datetime(timestring, modifiers…) This returns YYYY-MM-DD HH:MM:SS 4 julianday(timestring, modifiers…) This returns the number of days since noon in Greenwich on November 24, 4714 B.C. 5 strftime(timestring, modifiers…) This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below. All the above five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. Following section will give you detail on different types of time strings and modifiers. Time Strings A time string can be in any of the following formats − Sr.No. Time String Example 1 YYYY-MM-DD 2010-12-30 2 YYYY-MM-DD HH:MM 2010-12-30 12:10 3 YYYY-MM-DD HH:MM:SS.SSS 2010-12-30 12:10:04.100 4 MM-DD-YYYY HH:MM 30-12-2010 12:10 5 HH:MM 12:10 6 YYYY-MM-DDTHH:MM 2010-12-30 12:10 7 HH:MM:SS 12:10:01 8 YYYYMMDD HHMMSS 20101230 121001 9 now 2013-05-07 You can use the “T” as a literal character separating the date and the time. Modifiers The time string can be followed by zero or more modifiers that will alter date and/or time returned by any of the above five functions. Modifiers are applied from the left to right. Following modifers are available in SQLite − NNN days NNN hours NNN minutes NNN.NNNN seconds NNN months NNN years start of month start of year start of day weekday N unixepoch localtime utc Formatters SQLite provides a very handy function strftime() to format any date and time. You can use the following substitutions to format your date and time. Substitution Description %d Day of month, 01-31 %f Fractional seconds, SS.SSS %H Hour, 00-23 %j Day of year, 001-366 %J Julian day number, DDDD.DDDD %m Month, 00-12 %M Minute, 00-59 %s Seconds since 1970-01-01 %S Seconds, 00-59 %w Day of week, 0-6 (0 is Sunday) %W Week of year, 01-53 %Y Year, YYYY %% % symbol Examples Let”s try various examples now using SQLite prompt. Following command computes the current date. sqlite> SELECT date(”now”); 2013-05-07 Following command computes the last day of the current month. sqlite> SELECT date(”now”,”start of month”,”+1 month”,”-1 day”); 2013-05-31 Following command computes the date and time for a given UNIX timestamp 1092941466. sqlite> SELECT datetime(1092941466, ”unixepoch”); 2004-08-19 18:51:06 Following command computes the date and time for a given UNIX timestamp 1092941466 and compensate for your local timezone. sqlite> SELECT datetime(1092941466, ”unixepoch”, ”localtime”); 2004-08-19 13:51:06 Following command computes the current UNIX timestamp. sqlite> SELECT strftime(”%s”,”now”); 1393348134 Following command computes the number of days since the signing of the US Declaration of Independence. sqlite> SELECT julianday(”now”) – julianday(”1776-07-04”); 86798.7094695023 Following command computes the number of seconds since a particular moment in 2004. sqlite> SELECT strftime(”%s”,”now”) – strftime(”%s”,”2004-01-01 02:34:56”); 295001572 Following command computes the date of the first Tuesday in October for the current year. sqlite> SELECT date(”now”,”start of year”,”+9 months”,”weekday 2”); 2013-10-01 Following command computes the time since the UNIX epoch in seconds (like strftime(”%s”,”now”) except includes fractional part). sqlite> SELECT (julianday(”now”) – 2440587.5)*86400.0; 1367926077.12598 To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers as follows − sqlite> SELECT time(”12:00”, ”localtime”); 05:00:00 sqlite> SELECT time(”12:00”, ”utc”); 19:00:00 Print Page Previous Next Advertisements ”;

SQLite – JOINS ”; Previous Next SQLite Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. SQL defines three major types of joins − The CROSS JOIN The INNER JOIN The OUTER JOIN Before we proceed, let”s consider two tables COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let”s assume the list of records available in COMPANY 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 Another table is DEPARTMENT with the following definition − CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); Here is the list of INSERT statements to populate DEPARTMENT table − INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, ”IT Billing”, 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, ”Engineering”, 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, ”Finance”, 7 ); Finally, we have the following list of records available in DEPARTMENT table − ID DEPT EMP_ID ———- ———- ———- 1 IT Billing 1 2 Engineering 2 3 Finance 7 The CROSS JOIN CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y row, respectively, the resulting table will have x*y row. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to only use them when appropriate. Following is the syntax of CROSS JOIN − SELECT … FROM table1 CROSS JOIN table2 … Based on the above tables, you can write a CROSS JOIN as follows − sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT; The above query will produce the following result − EMP_ID NAME DEPT ———- ———- ———- 1 Paul IT Billing 2 Paul Engineering 7 Paul Finance 1 Allen IT Billing 2 Allen Engineering 7 Allen Finance 1 Teddy IT Billing 2 Teddy Engineering 7 Teddy Finance 1 Mark IT Billing 2 Mark Engineering 7 Mark Finance 1 David IT Billing 2 David Engineering 7 David Finance 1 Kim IT Billing 2 Kim Engineering 7 Kim Finance 1 James IT Billing 2 James Engineering 7 James Finance The INNER JOIN INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, the column values for each matched pair of rows of A and B are combined into a result row. An INNER JOIN is the most common and default type of join. You can use INNER keyword optionally. Following is the syntax of INNER JOIN − SELECT … FROM table1 [INNER] JOIN table2 ON conditional_expression … To avoid redundancy and keep the phrasing shorter, INNER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns. SELECT … FROM table1 JOIN table2 USING ( column1 ,… ) … A NATURAL JOIN is similar to a JOIN…USING, only it automatically tests for equality between the values of every column that exists in both tables − SELECT … FROM table1 NATURAL JOIN table2… Based on the above tables, you can write an INNER JOIN as follows − sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; The above query will produce the following result − EMP_ID NAME DEPT ———- ———- ———- 1 Paul IT Billing 2 Allen Engineering 7 James Finance The OUTER JOIN OUTER JOIN is an extension of INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL, SQLite only supports the LEFT OUTER JOIN. OUTER JOINs have a condition that is identical to INNER JOINs, expressed using an ON, USING, or NATURAL keyword. The initial results table is calculated the same way. Once the primary JOIN is calculated, an OUTER JOIN will take any unjoined rows from one or both tables, pad them out with NULLs, and append them to the resulting table. Following is the syntax of LEFT OUTER JOIN − SELECT … FROM table1 LEFT OUTER JOIN table2 ON conditional_expression … To avoid redundancy and keep the phrasing shorter, OUTER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns. SELECT … FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,… ) … Based on the above tables, you can write an outer join as follows − sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; The above query will produce the following result − EMP_ID NAME DEPT ———- ———- ———- 1 Paul IT Billing 2 Allen Engineering Teddy Mark David Kim 7 James Finance Print Page Previous Next Advertisements ”;