Teach Yourself SQL in 21 Days, Second Edition
  
- Day 2 -
Introduction to the Query: The SELECT Statement
Objectives
Welcome to Day 2! By the end of the day you will be able to do the following:
- Write an SQL query
- Select and list all rows and columns from a table
- Select and list selected columns from a table
- Select and list columns from multiple tables
Background
To fully use the power of a relational database as described briefly on Day 1,
"Introduction to SQL," you need to communicate with it. The ultimate communication
would be to turn to your computer and say, in a clear, distinct voice, "Show
me all the left-handed, brown-eyed bean counters who have worked for this company
for at least 10 years." A few of you may already be doing so (talking to your
computer, not listing bean counters). Everyone else needs a more conventional way
of retrieving information from the database. You can make this vital link through
SQL's middle name, "Query."
As mentioned on Day 1, the name Query is really a misnomer in this context. An
SQL query is not necessarily a question to the database. It can be a command to do
one of the following:
- Build or delete a table
- Insert, modify, or delete rows or fields
- Search several tables for specific information and return the results in a specific
order
- Modify security information
A query can also be a simple question to the database. To use this powerful tool,
you need to learn how to write an SQL query.
General Rules of Syntax
As you will find, syntax in SQL is quite flexible, although there are rules to
follow as in any programming language. A simple query illustrates the basic syntax
of an SQL select statement. Pay close attention to the case, spacing, and logical
separation of the components of each query by SQL keywords.
SELECT NAME, STARTTERM, ENDTERM
FROM PRESIDENTS
WHERE NAME = 'LINCOLN';
In this example everything is capitalized, but it doesn't have to be. The preceding
query would work just as well if it were written like this:
select name, startterm, endterm
from presidents
where name = 'LINCOLN';
Notice that LINCOLN appears in capital letters in both examples. Although
actual SQL statements are not case sensitive, references to data in a database are.
For instance, many companies store their data in uppercase. In the preceding example,
assume that the column name stores its contents in uppercase. Therefore,
a query searching for 'Lincoln' in the name column would not find any data
to return. Check your implementation and/or company policies for any case requirements.
NOTE: Commands in SQL are not case sensitive.
Take another look at the sample query. Is there something magical in the spacing?
Again the answer is no. The following code would work as well:
select name, startterm, endterm from presidents where name = 'LINCOLN';
However, some regard for spacing and capitalization makes your statements much
easier to read. It also makes your statements much easier to maintain when they become
a part of your project.
Another important feature of ; (semicolon)semicolon (;)the sample query is the
semicolon at the end of the expression. This punctuation mark tells the command-line
SQL program that your query is complete.
If the magic isn't in the capitalization or the format, then just which elements
are important? The answer is keywords, or the words in SQL that are reserved as a
part of syntax. (Depending on the SQL statement, a keyword can be either a mandatory
element of the statement or optional.) The keywords in the current example are
Check the table of contents to see some of the SQL keywords you will learn and
on what days.
The Building Blocks of Data Retrieval: SELECT and FROM
As your experience with SQL grows, you will notice that you are typing the words
SELECT and FROM more than any other words in the SQL vocabulary.
They aren't as glamorous as CREATE or as ruthless as DROP, but
they are indispensable to any conversation you hope to have with the computer concerning
data retrieval. And isn't data retrieval the reason that you entered mountains of
information into your very expensive database in the first place?
This discussion starts with SELECT because most of your statements will
also start with SELECT:
SYNTAX:
SELECT <COLUMN NAMES>
The commands, see also statementsbasic SELECT statement couldn't be simpler.
However, SELECT does not work alone. If you typed just SELECT into
your system, you might get the following response:
INPUT:
SQL> SELECT;
OUTPUT:
SELECT
*
ERROR at line 1:
ORA-00936: missing expression
The asterisk under the offending line indicates where Oracle7 thinks the offense
occurred. The error message tells you that something is missing. That something is
the FROM clause:
SYNTAX:
FROM <TABLE>
Together, the statements SELECT and FROM begin to unlock the
power behind your database.
NOTE: keywordsclausesAt this point you
may be wondering what the difference is between a keyword, a statement, and a clause.
SQL keywords refer to individual SQL elements, such as SELECT and FROM.
A clause is a part of an SQL statement; for example, SELECT column1, column2,
... is a clause. SQL clauses combine to form a complete SQL statement. For example,
you can combine a SELECT clause and a FROM clause to write an SQL
statement.
NOTE: Each implementation of SQL has a
unique way of indicating errors. Microsoft Query, for example, says it can't show
the query, leaving you to find the problem. Borland's Interbase pops up a dialog
box with the error. Personal Oracle7, the engine used in the preceding example, gives
you an error number (so you can look up the detailed explanation in your manuals)
and a short explanation of the problem.
Examples
Before going any further, look at the sample database that is the basis for the
following examples. This database illustrates the basic functions of SELECT
and FROM. In the real world you would use the techniques described on Day
8, "Manipulating Data," to build this database, but for the purpose of
describing how to use SELECT and FROM, assume it already exists.
This example uses the CHECKS table to retrieve information about checks
that an individual has written.
The CHECKS table:
CHECK# PAYEE AMOUNT REMARKS
--------- -------------------- ------ ---------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
6 Cash 25 Wild Night Out
7 Joans Gas 25.1 Gas
Your First Query
INPUT:
SQL> select * from checks;
OUTPUT:
queriesCHECK# PAYEE AMOUNT REMARKS
------ -------------------- ------- ---------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
6 Cash 25 Wild Night Out
7 Joans Gas 25.1 Gas
7 rows selected.
ANALYSIS:
This output looks just like the code in the example. Notice that columns 1 and
3 in the output statement are right-justified and that columns 2 and 4 are left-justified.
This format follows the alignment convention in which numeric data types are right-justified
and character data types are left-justified. Data types are discussed on Day 9, "Creating
and Maintaining Tables."
The asterisk (*) in select * tells the database to return all
the columns associated with the given table described in the FROM clause.
The database determines the order in which to return the columns.
Terminating an SQL Statement
In some implementations of SQL, the semicolon at the end of the statement tells
the interpreter that you are finished writing the query. For example, Oracle's SQL*PLUS
won't execute the query until it finds a semicolon (or a slash). On the other hand,
some implementations of SQL do not use the semicolon as a terminator. For example,
Microsoft Query and Borland's ISQL don't require a terminator, because your query
is typed in an edit box and executed when you push a button.
Changing the Order of the Columns
The preceding example of an SQL statement used the * to select all columns
from a table, the order of their appearance in the output being determined by the
database. To specify the order of the columns, you could type something like:
INPUT:
SQL> SELECT payee, remarks, amount, check# from checks;
Notice that each column name is listed in the SELECT clause. The order
in which the columns are listed is the order in which they will appear in the output.
Notice both the commas that separate the column names and the space between the final
column name and the subsequent clause (in this case FROM). The output would
look like this:
OUTPUT:
PAYEE REMARKS AMOUNT CHECK#
-------------------- ------------------ --------- ---------
Ma Bell Have sons next time 150 1
Reading R.R. Train to Chicago 245.34 2
Ma Bell Cellular Phone 200.32 3
Local Utilities Gas 98 4
Joes Stale $ Dent Groceries 150 5
Cash Wild Night Out 25 6
Joans Gas Gas 25.1 7
7 rows selected.
Another way to write the same statement follows.
INPUT:
SELECT payee, remarks, amount, check#
FROM checks;
Notice that the FROM clause has been carried over to the second line.
This convention is a matter of personal taste when writing SQL code. The output would
look like this:
OUTPUT:
PAYEE REMARKS AMOUNT CHECK#
-------------------- -------------------- --------- --------
Ma Bell Have sons next time 150 1
Reading R.R. Train to Chicago 245.34 2
Ma Bell Cellular Phone 200.32 3
Local Utilities Gas 98 4
Joes Stale $ Dent Groceries 150 5
Cash Wild Night Out 25 6
Joans Gas Gas 25.1 7
7 rows selected.
ANALYSIS:
The output is identical because only the format of the statement changed. Now
that you have established control over the order of the columns, you will be able
to specify which columns you want to see.
Selecting Individual Columns
Suppose you do not want to see every column in the database. You used SELECT
* to find out what information was available, and now you want to concentrate
on the check number and the amount. You type
INPUT:
SQL> SELECT CHECK#, amount from checks;
which returns
OUTPUT:
CHECK# AMOUNT
--------- ---------
1 150
2 245.34
3 200.32
4 98
5 150
6 25
7 25.1
7 rows selected.
ANALYSIS:
Now you have the columns you want to see. Notice the use of upper- and lowercase
in the query. It did not affect the result.
What if you need information from a different table?
Selecting Different Tables
Suppose you had a table called DEPOSITS with this structure:
DEPOSIT# WHOPAID AMOUNT REMARKS
-------- ---------------------- ------ -------------------
1 Rich Uncle 200 Take off Xmas list
2 Employer 1000 15 June Payday
3 Credit Union 500 Loan
You would simply change the FROM clause to the desired table and type
the following statement:
INPUT:
SQL> select * from deposits
The result is
OUTPUT:
DEPOSIT# WHOPAID AMOUNT REMARKS
-------- ---------------------- ------ -------------------
1 Rich Uncle 200 Take off Xmas list
2 Employer 1000 15 June Payday
3 Credit Union 500 Loan
ANALYSIS:
With a single change you have a new data source.
Queries with Distinction
If you look at the original table, CHECKS, you see that some of the data
repeats. For example, if you looked at the AMOUNT column using
INPUT:
SQL> select amount from checks;
you would see
OUTPUT:
AMOUNT
---------
150
245.34
200.32
98
150
25
25.1
Notice that the amount 150 is repeated. What if you wanted to see how
may different amounts were in this column? Try this:
INPUT:
SQL> select DISTINCT amount from checks;
The result would be
OUTPUT:
AMOUNT
---------
25
25.1
98
150
200.32
245.34
6 rows selected.
ANALYSIS:
Notice that only six rows are selected. Because you specified DISTINCT,
only one instance of the duplicated data is shown, which means that one less row
is returned. ALL is a keyword that is implied in the basic SELECT
statement. You almost never see ALL because SELECT <Table>
and SELECT ALL <Table> have the same result.
Try this example--for the first (and only!) time in your SQL career:
INPUT:
SQL> SELECT ALL AMOUNT
2 FROM CHECKS;
OUTPUT:
AMOUNT
---------
150
245.34
200.32
98
150
25
25.1
7 rows selected.
It is the same as a SELECT <Column>. Who needs the extra keystrokes?
Summary
The keywords SELECT and FROM enable the query to retrieve data.
You can make a broad statement and include all tables with a SELECT * statement,
or you can rearrange or retrieve specific tables. The keyword DISTINCT limits
the output so that you do not see duplicate values in a column. Tomorrow you learn
how to make your queries even more selective.
Q&A
- Q Where did this data come from and how do I connect to it?
A The data was created using the methods described on Day 8. The database
connection depends on how you are using SQL. The method shown is the traditional
command-line method used on commercial-quality databases. These databases have traditionally
been the domain of the mainframe or the workstation, but recently they have migrated
to the PC.
Q OK, but if I don't use one of these databases, how will I use SQL?
A You can also use SQL from within a programming language. Embedded SQLEmbedded
SQL is normally a language extension, most commonly seen in COBOL, in which SQL is
written inside of and compiled with the program. Microsoft has created an entire
Application Programming Interface (API) that enables programmers to use SQL from
inside Visual Basic, C, or C++. Libraries available from Sybase and Oracle also enable
you to put SQL in your programs. Borland has encapsulated SQL into database objects
in Delphi. The concepts in this book apply in all these languages.
Workshop
The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises," and make sure
you understand the answers before starting tomorrow's work.
Quiz
- 1. Do the following statements return the same or different output:
SELECT * FROM CHECKS;
select * from checks;?
- 2. The following queries do not work. Why not?
a. Select *
b. Select * from checks
c. Select amount name payee FROM checks;
3. Which of the following SQL statements will work?
a. select *
from checks;
b. select * from checks;
c. select * from checks
/
Exercises
- 1. Using the CHECKS table from earlier today, write a query to
return just the check numbers and the remarks.
2. Rewrite the query from exercise 1 so that the remarks will appear as
the first column in your query results.
3. Using the CHECKS table, write a query to return all the unique
remarks.
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
