CHum 240

SQL SELECT Statement


SQLite

Get/Install SQLite from
sqlite.org/download.html


Practice Database

Download 
chum240-1-db.zip

Unzip the file.

Open a terminal or command-line window, and go to the
directory where the files were unzipped.

When unzipped, you should have three file:
* chum240-1.db -- the first sample database
* sqlite.bat -- a shell script to start sqlite3 with settings
* .sqliterc -- a flat-file database with settings for SQLite3


Windows Users

Download the file by chosing the 'Save' button. This should
make a zipped folder appear on your desktop, if it doesn't,
then you know what you are doing.

Right-click on the file and choose 'Extract all'.

Keep clicking on the 'Next' buttons until a 'Finish' button
shows up, then click on it.

An new Explorer window should have opened after you clicked
the 'Finish' button. In it is an item 'sqlite.bat'. Double
click on it.

You are now ready to begin.
Once SQLite is running, you need to know how to get out of it. Simply type '.q' and press enter. If it doesn't immediately quit, type ';' and press enter (ignoring any messages), and then type '.q' and press enter. Typing .help will help you remember. You can also abbreviate anything that starts with a dot--as long as you give enough to be unique. These "dot commands" are specific to SQLite. Other systems may have other ways of communicating with the DBMS. There are two entities we may communicate with, the manager of databases, and the databases themsselves. SELECT statements The simplest select statement just gives us what we want. select 5; Simplest useful select statement gets all rows and all columns from the a table. select * from t; Select statements always start with the word SELECT. The asterisk is a shortcut that stands for all columns in the table. The word FROM precedes the name of the table from which the rows are taken. Finally, the statement must be terminated by a semicolon. We talked about separators and delimiters, now we know about a terminator--it marks the end. select * from t ; If you misspell or leave out anything (except the semi-colon) you will get a simple error message that is not at all helpful. select from t; ERROR! SQL uses a free-form syntax, which means that spaces and line ends serve as separators between words. select * from t; However, spaces cannot appear in words. selec t * from t; ERROR Our practice table T has two columns, named A and B. The relational concept of projection allows us to choose which columns we want. We do this by listing their names instead of using the asterisk. A comma separates items in the list, and spaces and line breaks are no substitute for the comma. select a, b from t; select a,b from t; The columns we list need not appear in order, or even only once. select b, a from t; select b, b from t; Because the comma is a separator appearing between the names of columns, do not terminate the list of columns with a comma. (A surprisingly frequent error, in my experience!) select a, from t; ERROR! SQL is very consistent: anywhere a list is allowed, commas will be used to separate elements of that list. In fact, that is one way the program reading SQL statements determines that it is scanning a list. When it sees a comma, it goes into list reading mode, reading in a list element and checking if it is followed by a comma. (aside on computer languages?) Our list may also contain exactly one column, in which case no commas are required. select a from t; select b from t; The list of rows we see after a successfully executed select statement are called the result set. Remember, RDBMS is the child of logic and set theory. We've seen a result set, now for some logic. WHERE clause If we are interested in just part of the table, we add a clause to the SELECT statement with a logical predicate giving the condition that rows must satisfy to be included in the result. We do this by appending the word WHERE after the name of the table, and then including a logical expression that qualifies rows for inclusion in the result set. select * from t where b = 5; The logical expression "b = 5" is explained as "the value in column b equals five." This is a "binary" expression which compares two values, in this case the value of column b of each row against the constant, literal value five. Logical expressions equate to either true or false, which, in SQL, have the values one (1) or zero (0) respectively. select 5 = 5; select 5 = 2; select * from t where 1; select * from t where 0; So, adding what we know about choosing columns (projection) to choosing rows (selection), we can do the following. select a from t where b = 5; select b, a from t where b = 10; Try these other operators on column b: > < >= <= <> Logical expression can be combined with the operators AND and OR, and grouped with parenthesis--especially when combining AND and OR operators in the same expression. select * from t where b > 5 and b < 10; select * from t where b > 15 and (b = 3 or b = 5); Computation can also be done. select 2 + 2; select * from t where b * 2 > 10; - - - - Copyright 2007 Brigham Young University