|
  
6.
Selecting Data
 6.1
Result Sets
6.2
Selecting an Entire Table
6.3
Selecting Specific Columns
6.4
Selecting Specific Rows
6.
Selecting Data
One of
the most important capabilities for a database, other than storing information,
is retrieving that information. This is done with the SELECT statement.
The SELECT
statement can range from very simple to extremely complicated, and has
a number of optional clauses that are used for various purposes.
6.1
Result Sets
The output
of the SELECT statement is called a result set, which contains all the
data that meets the conditions specified in the SELECT command.
6.2
Selecting an Entire Table
The simplest
select statement you can create is to SELECT all information in a table.
To do this, you can use a command like the following:
select * from table_name¡F
This command
selects data from all columns in the specified table. In the select
statement, the asterisk (`*') is used to represent all columns in a
table. If you use this command on the Employees table, it will
return all information on all employees in the table.
Use the
command shown below to select all data from the Employees table.
The data that is returned by the database should look like that shown
below.
dmSQL> select * from Employees;
Number FirstName LastName Manager Phone BirthDa* HireDate
======== =========== =============== ======== =========== ======== ========
10000 Gabrial Davis 10000 228-6932 1995-04* 1957-01*
10001 Greg Carter 10002 271-9240 1992-11* 1971-02*
10002 Dean Cogar 10000 958-9036 1990-02* 1962-09*
10003 Benson Armstrong 10002 918-3517 1993-03* 1970-12*
10004 Lyn Belger 10000 363-4511 1991-12* 1959-05*
...
...
...
10045 Todd Culp 10004 576-6605 1991-12* 1969-07*
10046 Matthew Griffin 10006 230-4636 1990-08* 1960-09*
10047 Lou Diehl 10006 262-3303 1992-07* 1969-04*
10048 Gordon Barksdale 10006 478-7576 1990-06* 1975-11*
10049 Duncan Hewitt 10002 266-1234 1993-05* 1962-11*
50 rows selected
The select
statement tells dmSQL that it wants to see all columns in a table (represented
by an `*'). Then it tells it which table it wants data from. In this
case, it is the Employees table. Then dmSQL displays the name
of the columns and the data in each of those columns. Finally, it displays
how many rows were actually displayed.
This provides
a convenient method to find the column names for a table if you do not
remember them, instead of having to deal with the system tables. It
also provides a faster way to view data if you do want to see all of
the columns.
You can
see from the sample output that not all of the columns are fully displayed.
This is because the width of the dmSQL display line is set to 80 by
default. This will be used throughout the tutorial for formatting reasons.
If you want to see all of the data in the columns, you can use the following
command to turn off the linewidth feature:
dmSQL> set linewidth off;
You can
also get all of the data in all of the columns by using all of the column
names in the select statement. In the Employees table, the column
names are: Number, FirstName, LastName, Manager,
Phone, BirthDate, and HireDate. To enter a command
equivalent to the one above, you can enter:
dmSQL> select Number, FirstName, LastName, Manager, Phone, BirthDate, HireDate from Employees;
Number FirstName LastName Manager Phone BirthDa* HireDate
======== =========== =============== ======== =========== ======== ========
10000 Gabrial Davis 10000 228-6932 1995-04* 1957-01*
10001 Greg Carter 10002 271-9240 1992-11* 1971-02*
10002 Dean Cogar 10000 958-9036 1990-02* 1962-09*
10003 Benson Armstrong 10002 918-3517 1993-03* 1970-12*
10004 Lyn Belger 10000 363-4511 1991-12* 1959-05*
...
...
...
10045 Todd Culp 10004 576-6605 1991-12* 1969-07*
10046 Matthew Griffin 10006 230-4636 1990-08* 1960-09*
10047 Lou Diehl 10006 262-3303 1992-07* 1969-04*
10048 Gordon Barksdale 10006 478-7576 1990-06* 1975-11*
10049 Duncan Hewitt 10002 266-1234 1993-05* 1962-11*
50 rows selected
In this
case, you explicitly specify which columns you want by listing them.
You list the columns Number, FirstName, LastName,
Manager, Phone, BirthDate, and HireDate
as the columns you want to retrieve, specify the Employees table
as the table that you want to get the data from. Notice that the output
from this example is the same as from the last example.
The names
in this database are case sensitive. If you make a spelling error, or
you use the wrong case when entering the name of a column, you may get
an error like the following:
dmSQL> select number, FirstName, LastName, Manager, Phone, BirthDate, HireDate from Employees;
ERROR (6523): invalid column name : number
If you
spelled multiple column names incorrectly, or made errors in case on
more than one column name, only the first error encountered will be
returned. After you correct the first error, if you don't also find
and correct the second error, another error will be returned. Or, if
you enter the table name wrong, you may get an error like the one shown
in the following example:
dmSQL> select Number, FirstName, LastName, Manager, Phone, BirthDate, HireDate from Employes;
ERROR (6521): table or view does not exist : SYSADM.Employes
6.3
Selecting Specific Columns
You can
also select specific columns from a table by specifying only the column
names that you want to see. To do this, you can use a command like the
following:
select column_name, column_name, ... from table_name¡F
This command
selects only the data in the columns specified in the column-name list.
For example,
if you were going to make a telephone list of all employees that worked
at the company in the tutorial database, you could select the first
name, last name, and telephone number columns to view only this data.
dmSQL> select FirstName, LastName, Phone from Employees;
FirstName LastName Phone
=============== ==================== ===============
Gabrial Davis 228-6932
Greg Carter 271-9240
Dean Cogar 958-9036
Benson Armstrong 918-3517
Lyn Belger 363-4511
...
...
...
Todd Culp 576-6605
Matthew Griffin 230-4636
Lou Diehl 262-3303
Gordon Barksdale 478-7576
Duncan Hewitt 266-1234
50 rows selected
6.4
Selecting Specific Rows
If we had
to select all records in a table every time we performed a query, then
a database would not be very useful. SQL allows us to select specific
records in a database as well as selecting specific columns. This is
done with the where clause.
The WHERE
clause allows us to define a conditional expression to select the data
we want to see. If the data meets the condition in the WHERE clause,
it will be included in the result set. If it doesn't meet the conditions,
it will be excluded. A simple WHERE clause might appear like the example
below:
dmSQL> select * from Employees where expression;
Operator
Types
There
are three types of operators you can use in the expression of a WHERE
clause: arithmetic operators, comparison operators, and logical operators.
Each of these operators is used for a different purpose, and the comparison
operators is the most frequently used type of operator.
Comparison
Operators
Comparison
operators are used to compare the values of two operators, and are generally
used to determine whether a row should be included in a result set.
The comparison operators are:
|
=
|
Equal
to
|
|
>
|
Greater
than
|
|
<
|
Less
than
|
|
>=
|
Greater
than or equal to
|
|
<=
|
Less
than or equal to
|
|
<>
|
Not
equal to
|
|
BETWEEN
|
Used
to specify a range of values
|
|
LIKE
|
Used
in pattern matching
|
|
IN
|
Used
to specify record in a database
|
|
IS
|
Used
in testing for special values
|
First we
will try a very simple conditional clause using a comparison operator
to demonstrate how this works. Enter the following command at the dmSQL
command prompt:
dmSQL> select * from Employees where Number = 10041;
Number FirstName LastName Manager Phone BirthDa* HireDate
======== =========== =============== ======== =========== ======== ========
10041 Cedric Asbury 10004 223-1530 1961-01* 1991-11*
1 rows selected
This query
will select all columns from the Employees table because the
asterisk (`*') was used in the column list. The WHERE clause specifies
that only those records where the employee number = 10041 will be returned
in the result set. In this case, employee number 10041 is Cedric Asbury.
This type of query is useful when you know a single piece of data from
a record and want to display the remaining data.
In this
example, we will get the names of all employees whose names begin with
the letters G, H, and I. Enter the following command at the dmSQL command
prompt:
dmSQL> select * from Employees where LastName between `Ga' and `Iz';
Number FirstName LastName Manager Phone BirthDa* HireDate
======== =========== =============== ======== =========== ======== ========
10007 Taylor Galbreath 10002 648-6633 1975-02* 1994-10*
10013 Abner Hewitt 10004 1064-9761 1978-11* 1994-09*
10014 Vivian Gannon 10004 458-0677 1962-08* 1991-06*
10035 Christine Hudson 10006 729-8474 1968-11* 1994-08*
10036 Adam Hinman 10004 715-9283 1960-05* 1993-07*
10046 Matthew Griffin 10006 230-4636 1960-09* 1990-08*
10049 Duncan Hewitt 10002 266-1234 1962-11* 1993-05*
7 rows selected
This query
will select all columns from the Employees table because the
asterisk (`*') was used in the column list. The BETWEEN keyword used
in the WHERE clause specifies that only those records where an employee
has a name that starts with the letters G, H, or I are returned in the
result set. It does this by using the BETWEEN comparison operator, which
takes two arguments separated by AND. Note that although this AND is
used in the same way as the logical operator AND shown below, it is
not a logical operator but part of the BETWEEN keyword. In order to
get all names that are start with g, h, or I, the query uses the values
`Ga' and `Iz'. If you only use the values `G' and `I', you will only
get those names that begin with G and H, and will get no names that
begin with the letter I. This is because any names that have letters
after the I come after the sort order for the single letter I. By using
`Iz' you get all the names between Ga and Iz. Although if someone has
the names G or Izzy, they won't be included because they are outside
the range.
In this
example, we will get the names of all employees whose last name begins
with the letters `Gri'. Enter the following command at the dmSQL command
prompt:
dmSQL> select * from Customers where LastName like `Gri%';
Number FirstName LastName Phone IDType IDRegi* IDNumber Cre*
======= ========= ============ ========= ======= ======= ============ ====
10034 Jacob Griffith 241-3280 103 2101 8583 520796* 641
10096 Tony Griffith 765-0455 103 1102 3283 875271* 37
10100 Clyde Griffith 606-6916 103 1301 5595 846117* 686
10163 Linda Griffith 924-8988 101 1410 6919 6782 4* 97
10294 Derrick Griffis 391-4186 301 1502 SS075441 564
10395 Tracy Griffis 423-4597 101 1403 1632 0234 4* 61
10616 May Griffis 397-2142 201 1403 36074646 364
10742 Albert Griffis 269-6186 103 1402 2356 209343* 739
10753 Hillary Griffith 292-5639 103 1202 3325 131210* 403
11005 Tyrone Griffith 664-7043 601 1601 395274158 480
11093 Valeria Griffis 696-8698 102 1206 5134 601 52* 735
11115 Guy Griffis 924-0143 103 1203 6383 044551* 172
12 rows selected
This query
will select all columns from the Employees table because the
asterisk (`*') was used in the column list. The BETWEEN keyword used
in the WHERE clause specifies that only those records where an employee
has a name that starts with the letters G, H, or I are returned in the
result set. It does this by using the BETWEEN comparison operator, which
takes two arguments separated by AND. Note that although this AND is
used in the same way as the logical operator AND shown below, it is
not a logical operator but part of the BETWEEN keyword. In order to
get all names that are start with g, h, or I, the query uses the values
`Ga' and `Iz'. If you only use the values `G' and `I', you will only
get those names that begin with G and H, and will get no names that
begin with the letter I. This is because any names that have letters
after the I come after the sort order for the single letter I. By using
`Iz' you get all the names between Ga and Iz. Although if someone has
the names G or Izzy, they won't be included because they are outside
the range.
Logical
Operators
Logical
operators are used to connect two expressions in a WHERE clause to show
that there is some relationship between them. The logical operators
are:
|
AND
|
both
expressions are true
|
|
OR
|
either
expression is true
|
|
NOT
|
an
expression is not true
|
In this
example, we will get the names of all movies that were made in Canada
in 1995.
dmSQL> select Name from Movies where Year = `1995' and Country = `Canada';
Name
===========================================================================
Johnny Mnemonic
Magic in the Water
When Night is Falling
3 rows selected
This query
retrieves the names of all movies where the country of origin is Canada
AND the year of production is 1995.
Arithmetic
Operators
Arithmetic
operators are used to perform mathematical calculations. These are usually
part of a comparison operation where the result is not known until after
some calculation is performed.
|
+
|
Addition
|
|
-
|
Subtraction/Unary
negation
|
|
*
|
Multiplication
|
|
/
|
Division
|
|
