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