Select Queries

SELECT * FROM table_name;

SQL queries can be used for a myriad of tasks such as record searching, statistical analysis and making other complex calculations. On the previous page we used a a simple select query (shown above) to verify that the records were entered properly into the inventory table. What this query does is select all columns from the inventory table and lists every record in that table.

In SQLite- To specify that the column names are to be listed above the query results use the ".headers ON " on the command line, this only needs to be done once during the session unless the ".headers OFF " command was entered

sqlite> .headers ON
sqlite> SELECT * FROM inventory;
StockNumber|Descrip|OnHandQuan|PackQty|PackCost
23155|Shovel Pointed Long Handle|15|1|9.82
43111|White Gas Gallon Can|14|4|14.75
43512|10W-30 Motor Oil, Quart|36|12|18.2
43522|5W-30 Motor Oil, Quart|17|12|18.2
51001|AAA Dry Cells 4 Pack |92|12|9.0
51002|AA Dry Cells 4 Pack |173|12|9.0
51004|AA Dry Cells 8 Pack|5|12|16.8
51013|D Dry Cells 8 Pack|19|12|90.2
75149|Ball Point Pens Blue Fine tip, 12pack|92|20|15.37
sqlite>

First of all, we want to limit the columns in the query result to only those that we are interested in. We do this by replacing the asterisk in the previous select query with a list of fieldnames separated by commas.

sqlite> SELECT StockNumber,OnHandQuan,Descrip FROM inventory;
StockNumber|OnHandQuan|Descrip
23155|15|Shovel Pointed Long Handle
43111|14|White Gas Gallon Can
43512|36|10W-30 Motor Oil, Quart
43522|17|5W-30 Motor Oil, Quart
51001|92|AAA Dry Cells 4 Pack
51002|173|AA Dry Cells 4 Pack
51004|5|AA Dry Cells 8 Pack
51013|19|D Dry Cells 8 Pack
75149|92|Ball Point Pens Blue Fine tip, 12pack
sqlite>

Let us say that someone asks what Stock Number 75149 is. Of course in an actual business application, the number of records in a table could number in the thousands or even the millions making it time consuming and difficult to find the records that you want by visually searching the whole table. A better solution is to attach a WHERE clause to the query specifying that only records with the stock number equal to 75149 should be returned.

sqlite> SELECT StockNumber,OnHandQuan,Descrip FROM inventory WHERE StockNumber = 75149;
StockNumber|OnHandQuan|Descrip
75149|92|Ball Point Pens Blue Fine tip, 12pack

Using a LIKE Clause with Wildcards to Find Records

LIKE, NOT LIKE Conditions and Wildcards

The LIKE operator is used with one or more wildcard characters to select records based on a text string where it is not possible or it would too inconvenient to make an exact match. The wildcard characters used in SQLite are the percent sign (%), which matches zero or more characters and spaces and the underscore (_) which matches a single character or space. The asterisk used in the select query at the top of the page is also a wild card which selects all columns in a table.

In this next example we wish to find what types of motor oil that we stock but we don't know the stock numbers but we do know that the phrase "motor oil" will be in the description. Using the LIKE clause and the words "motor oil" sandwiched between percent signs and single quotes we get the following results.

sqlite> SELECT StockNumber,OnHandQuan,Descrip FROM inventory WHERE Descrip LIKE '%motor oil%';
StockNumber|OnHandQuan|Descrip
43512|36|10W-30 Motor Oil, Quart
43522|17|5W-30 Motor Oil, Quart
sqlite>

sqlite> SELECT brand,descrip,onhand_quan,on_order FROM product;

brand descrip onhand_quan on_order
----------   -------------------------   ------------   ----------
Shady Oak Milk 12 0
Cloverleaf 2 % Milk - Quart 31 12
Cloverleaf 1 % Milk - Quart 13 12
Cloverleaf Skim Milk - Quart 42 0
Shady Oak 2 % Milk - Gallon 6 0
Acme Soy Milk - Quart 0 48
Shady Oak Whole Milk - Vitamin D 2 20
sqlite>

In this example we wish to know how many quart containers of milk that we have on hand.

sqlite>SELECT brand,descrip,onhand_quan,on_order FROM product WHERE descrip LIKE '%Milk - Quart';

brand descrip onhand_quan on_order
------------ -------------------- ------------ ------------
Cloverleaf 2 % Milk - Quart 31 12
Cloverleaf 1 % Milk - Quart 13 12
Cloverleaf Skim Milk - Quart 42 0
Acme Soy Milk - Quart 0 48
sqlite>

If we wish to exclude Soy milk from the results then we can use an "AND" clause and a "NOT LIKE" condition.

sqlite> SELECT brand,descrip,onhand_quan,on_order FROM product WHERE descrip LIKE '%Milk - Quart' AND descrip NOT LIKE 'Soy%';

brand descrip onhand_quan on_order
------------ -------------------- ------------ ------------
Cloverleaf 2 % Milk - Quart 31 12
Cloverleaf 1 % Milk - Quart 13 12
Cloverleaf Skim Milk - Quart 42 0
sqlite>

ESCAPE characters

Here we wish to find out how many containers of 2% milk we have on hand and on order. To prevent SQLite from mistaking the percent sign in the description for a wildcard, we proceed it with an escape character which we declare at the end of the statement with the following syntax.

ESCAPE '\ '

The escape character does not necessarily have to be a backslash, it can be any single character you choose.

sqlite> SELECT brand,descrip,onhand_quan,on_order FROM product WHERE descrip LIKE '2 \% milk%' ESCAPE '\';

brand descrip onhand_quan on_order
----------   -------------------------   ------------   ----------
Cloverleaf 2 % Milk - Quart 31 12
Shady Oak 2 % Milk - Gallon 6 0

Here we have a table listing book titles and their authors.

sqlite> .width 20 40
sqlite> SELECT * FROM booklist;

author title
-------------------- ----------------------------------------
John Doe Metalurgy and Casting
John C. Doe Probability and Error Estimation
John Charles Doe Statistics - Tools for Decision Making
Joanna Carla Doe Business Statistics and Applications
Mike J. Doe Methods of Error Estimation
John Albert Doe Methods of Analytic Chemistry

We want to select a list of books written by John Charles Doe. Since the Author may be listed with a full middle name or only a middle initial we use the following syntax.

LIKE 'john_c%doe'

Notice the underscore character between john and c which is another wildcard representing a single character or number.

sqlite> SELECT author, title FROM booklist WHERE author LIKE 'john_c%doe';

author title
-------------------- ----------------------------------------
John C. Doe Probability and Error Estimation
John Charles Doe Statistics - Tools for Decision Making
sqlite>

USING "IN" to SELECT Records Based on A List of Values

Another way to use the WHERE clause is to use it with the word IN and a comma separated list of values as in the example below.

sqlite> SELECT InvoiceNo,ItemNo,Quan FROM catalogsales WHERE ItemNo IN(4501,4502) ORDER BY InvoiceNo,ItemNo DESC;

InvoiceNo ItemNo Quan
--------------- --------------- ---------------
21001 4502 10
21001 4501 10
21015 4502 10
21015 4501 3
21023 4502 27
21023 4501 10
21027 4501 10
sqlite>

Notice also the ORDER BY clause and the list of column names that follows the clause. This will define the sort order of the results.

The ORDER BY Clause

ORDER BY column name DESC - sort descending, highest to lowest
ORDER BY column name ASC - sort ascending,lowest to highest. The addition of "ASC" after the column name to sort by is generally unnecessary since the ascending sort order is the default.
Rows will be sorted in the order in which they are listed after the "ORDER BY " clause in the example above the records are first sorted by invoice number and then item number because we wish to keep the rows for each invoice together.


Colin Riley -- Updated May 2011 --