YouTip

# SQLite SELECT Statement The SQLite **SELECT** statement is used to fetch data from an SQLite database table and return the data in the form of a result table. These result tables are also called result-sets. ## Syntax The basic syntax of the SELECT statement is as follows: SELECT column1, column2, columnN FROM table_name; Here, column1, column2... are the fields of a table whose values you want to fetch. If you want to fetch all available fields of a table, you can use the following syntax: SELECT * FROM table_name; ## Example Consider the COMPANY table with the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 Following is an example to fetch and display all these records. Here, the first two commands are used to set up properly formatted output. sqlite>.header on sqlite>.mode column sqlite> SELECT * FROM COMPANY; This will produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 If you want to fetch only selected fields from the COMPANY table, then use the following query: sqlite> SELECT ID, NAME, SALARY FROM COMPANY; This will produce the following result: ID NAME SALARY ---------- ---------- ----------1 Paul 20000.02 Allen 15000.03 Teddy 20000.04 Mark 65000.05 David 85000.06 Kim 45000.07 James 10000.0 ## Setting Output Column Width Sometimes, due to the default width of the columns to be displayed, the output of **.mode column** is truncated. In such cases, you can use the **.width num, num....** command to set the width of the display columns, as shown below: sqlite>.width 10, 20, 10 sqlite>SELECT * FROM COMPANY; The above **.width** command sets the width of the first column to 10, the second column to 20, and the third column to 10. Therefore, the above SELECT statement will produce the following result: ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 ## Schema Information Since all **dot commands** are available only in the SQLite prompt, when you are doing programming with SQLite, you will use the following SELECT statement with the **sqlite_master** table to list all the tables created in your database: sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table'; Assuming there is only one table COMPANY in testDB.db, this will produce the following result: tbl_name ---------- COMPANY You can list down the complete information about the COMPANY table as follows: sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY'; Assuming there is only one table COMPANY in testDB.db, this will produce the following result: CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL )