home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Power-Programmierung
/
CD1.mdf
/
sql
/
select.shr
< prev
next >
Wrap
Text File
|
1988-02-29
|
38KB
|
1,087 lines
SELECT (Single Table)
SYNTAX:
SELECT [DISTINCT] column_name [,column_name ...]
FROM table_name [,table_name ...]
[WHERE search_expression]
[INTO file_name] <-- Non-Standard
[GROUP BY column_name [,column_name ...] [HAVING criteria]
[ORDER BY column_name [,column_name ...]
If you want all the columns in a table you can replace the list
of column names with an asterisk (*).
The select command produces a report in the form of a table.
Although the basic use of the select takes a short time to learn,
the more advanced uses take a long time to master.
The pm table is included so you can try entering the commands
yourself. For those of you who know about data normalization,
the pm table is not even in the second normal form. I did this
so I could more easily explain the use of all the commands using
a single table.
The pm (product manufacture) table consists of:
code - 2-character product code
desc - 15-character product description
mst - state in which the product was manufactured
defects - % defects of the last batch of products manufactured
Since the product can be manufactured in more that one state,
the combination of code and mst is the primary key.
All the examples have each clause starting on a new line for
purposes of clarity. It is not necessary to have them on
separate lines. It is sometimes advantageous to keep the query
on one line since if you need to make a correction, you cannot
backup a line.
THE BASICS OF SELECT
To find all the data in a table:
select *
from pm;
code desc mst defects
---- --------------- --- -------
AB Megawamp WA 3
AC Gigasnarf AZ 0
DD Decawidgit WA 3
DA Technowidgit OR 10
DZ Electrowidgit AZ
EA nanomouse AZ 12
EC RGBMouse ID 2
FA Dynamic Disk NV 3
AB Megawamp ID 5
9 rows selected
SELECT-1
Instead of typing all the column names, you can use the * to
denote all column names. On the next line, the from pm tells us
that we want the data to come from the table named "p" (for
product). All select commands must end with a semi-colon (;).
If we just wanted the product code and the state that it was
manufactured in you would type:
select code, mst
from pm;
code mst
---- ---
AB WA
AC AZ
DD WA
DA OR
DZ AZ
EA AZ
EC ID
FA NV
AB ID
9 rows selected
DISTINCT
The optional statement "distinct" will eliminate all rows with
duplicate columns that you have specified. If you wanted to find
out all the different states where products are manufactured, you
would type:
select distinct mst
from pm;
mst
---
WA
AZ
OR
ID
NV
5 rows selected
As you can see only five rows were found. This is because three
products are manufactured in Arizona and two products are
SELECT-2
manufactured in Washington. Only the first occurance of each
data element is retained.
The "distinct" modifier can be used with one or more column names
in a select statement. You will see useful examples of this when
we discuss more complex searches.
SELECT-3
WHERE
The where clause of the select command is the most powerful and
complex. Our discussion will be broken down into various phases.
The first phase is using the where in conjunction with a single
table name. Later you will see it used with multiple tables and
you will find that you can even put another select command within
the where clause.
search_expression
=, <>, !=, >, <, >=, <=, IS NULL, IS NOT NULL, LIKE
The simplest search expression relates a column name to a
constant. A constant is an exact value that you enter. All
column names are tested against that value. If the column name
that you are testing is defined as a char, the constant must be
enclosed in single quotes or double quotes. The constant you are
searching for may not contain a quote. If the column name you
are testing for is defined as a num, the constant is a number
without any quotes.
You can relate the column name to the constant in the following
ways:
= equal
<> not equal
!= not equal
> greater than
< less than
>= greater than or equal
<= less than or equal
If you wanted to find out which products are manufactured in
Arizona:
select *
from pm
where mst = 'AZ';
code desc mst defects
---- --------------- --- -------
AC Gigasnarf AZ 0
DZ Electrowidgit AZ
EA nanomouse AZ 12
3 rows selected
SELECT-4
Spaces are optional on either side of the "=". You could have
typed:
where mst='AZ';
Instead of single quotes you could have used double quotes:
where mst="AZ";
The column name "defects" is the percent of defects when the
product was manufactured. If you wanted a list of all the
product descriptions and their corresponding defects for those
products with defects over 10%:
select desc, defects
from pm
where defects > 10;
desc defects
--------------- -------
nanomouse 12
1 row selected
We find that only the nanomouse has more than 10% defects.
SPECIAL SEARCH EXPRESSION - IS NULL, IS NOT NULL
Nulls are an important concept in relational databases. For
example, let's look at the percentage of defects in Arizona:
select *
from pm
where mst = 'AZ';
code desc mst defects
---- --------------- --- -------
AC Gigasnarf AZ 0
DZ Electrowidgit AZ
EA nanomouse AZ 12
3 rows selected
In Arizona, the Gigasnarf has zero defects, the nanomouse has 12%
defects but there is nothing listed for the Electrowidgit. This
"nothing" is called a null. It does not mean zero or blank. A
null means that the data element is not relevant in the row. In
our case, Electrowidgits are not tracked for defects like other
parts. This isn't to say that there are zero defects. It just
means we have no entry.
SELECT-5
If we wanted to look at all products for which we do not track
defects:
select *
from pm
where defects is null;
code desc mst defects
---- --------------- --- -------
DZ Electrowidgit AZ
1 row found
We could find all the products for which we track defects by
using is not null:
select *
from pm
where defects is not null;
code desc mst defects
---- --------------- --- -------
AB Megawamp WA 3
AC Gigasnarf AZ 0
DD Decawidgit WA 3
DA Technowidgit OR 10
EA nanomouse AZ 12
EC RGBMouse ID 2
FA Dynamic Disk NV 3
AB Megawamp ID 5
8 rows selected
SELECT-6
SPECIAL SEARCH EXPRESSION - LIKE, NOT LIKE
The expression like is used when we want similar characters. Get
all product descriptions that begin with the letter 'D':
select *
from pm
where desc like 'D%';
code desc mst defects
---- --------------- --- -------
DD Decawidgit WA 3
FA Dynamic Disk NV 3
2 rows selected
The special character '%' tells the expression to accept any
characters at that position and any subsequent position. The
underline is another special character. It tells the expression
to accept any character at that position only. Get all product
codes that end in 'A':
select *
from pm
where code like '_A';
code desc mst defects
---- --------------- --- -------
DA Technowidgit OR 10
EA nanomouse AZ 12
FA Dynamic Disk NV 3
3 rows selected
The two special characters '%' and '_' can also be used together
in a search expression. You can produce the opposite result by
using the not modifier. Get all product codes which do not end
in 'A':
select *
from pm
where code not like '_A';
code desc mst defects
---- --------------- --- -------
AB Megawamp WA 3
AC Gigasnarf AZ 0
DD Decawidgit WA 3
DZ Electrowidgit AZ
EC RGBMouse ID 2
AB Megawamp ID 5
6 rows selected
SELECT-7
AND, OR, NOT
We can link expressions through the use of ands and ors. If you
use an and, both expressions have to be true in order to add the
row to the table we are producing. If you use an or, only one of
the expressions have to be true. You can use as many ands and
ors as you want in a query. The expressions are executed from
left to right. All the ands are done first, then execution
starts again from the left and the ors are processed.
If you want to reference different column names in a search, the
logic as stated in English is typically the same as it is in SQL.
Usually there is no confusion between the and and the or as is
the case when we are dealing with the same column name.
If you want to find all products in Washington with defects over
2%:
select *
from pm
where mst = 'WA' and defects > 2;
code desc mst defects
---- --------------- --- -------
AB Megawamp WA 3
DD Decawidgit WA 3
2 rows selected
Generally, the use of a single and or an or in a where clause is
more or less common sense unless you are refering to the same
column name. What if you wanted to find all the products
manufactured in Washington and Idaho. This has two meanings in
everyday English:
1. Out of all the products we manufacture, I want a list of the
ones we manufacture in Washington and a list of the ones we
manufacture in Idaho.
2. Out of all the products we manufacture, I want a list of each
one we manufacture in BOTH Washington and Idaho.
We will accept the first meaning in this example. It is
interesting to note that we could have also said, "List all the
products manufactured in Washington or Idaho." It seems odd that
in English "or" can have the same meaning as "and". Even if you
don't agree with my interpretation, beware that others do and
that it may cause communication problems.
In the English version you use the "and" so there is a tendency
to always use the "and" in the SQL version too. This would be
wrong. The English perspective and the SQL perspective are
different. The English perspective often looks at the whole
group of items. The SQL perspective looks at one item at a time.
SELECT-8
To translate Washington and Idaho into SQL we look at it from its
perspective. We will look at each item. If that item is
manufactured in Washington or Idaho, we will add it to our table.
Notice that when we look at it from the SQL perspective, we use
the "or" instead of the "and". This not just a quirk of SQL
logic, it is common to virtually all data bases. To find out all
the products manufactured in Washington and Idaho:
select desc
from pm
where mst='WA' or mst='ID';
desc
-----------------
Megawamp
Decawidgit
RGBMouse
Megawamp
4 rows selected
Megawamp is repeated above because it is manufactured in both
Washington and Idaho. To get rid of the duplicate you would
replace the first line with: select distinct desc
It is also interesting to note what the meaning would be if we
used an and instead of an or. Would it tell us every product
manufactured in BOTH Washington and Idaho? No, it wouldn't!
Actually the following search makes no sense. How could a state
for an individual item be both WA and ID?????
select desc
from pm
where mst='WA' and mst='ID';
[ none found ]
But how WOULD you find out every product manufactured in both
Washington and Idaho? The search strategy is rather complex and
the topic is covered under joining and virtual tables.
In order to express the opposite logic, you use the not. If you
wanted to find the products NOT made in Washington and/or Idaho:
select *
from pm
where not (mst='WA' or mst='ID');
code desc mst defects
---- --------------- --- -------
AC Gigasnarf AZ 0
DA Technowidgit OR 10
DZ Electrowidgit AZ
EA nanomouse AZ 12
FA Dynamic Disk NV 3
5 rows selected
SELECT-9
Normally, if a condition is true, we add that row to the table.
By using the not, if the condition is false, we add the row to
the table. On the first row above, the state is AZ. It was
selected because it is not equal to WA or ID. Just as we can
express the same thought in English more than one way, we can do
the same with SQL. We could have written:
select *
from pm
where mst <> 'WA' and mst <> 'ID';
or
select *
from pm
where not (mst = 'WA') and not (mst = 'ID');
What if we would have written "or" instead of "and" in the above
two examples? Every row would have been selected. No matter
what the state is, it would either be not equal to WA or not
equal to ID!
Remember that whenever you use the not, you must enclose the
expression in parentheses.
You can have as many ands and ors in a query as you need. What
if you wanted all products in Washington and Idaho with defects
over 3%:
select *
from pm
where (mst='WA' or mst='ID') and defects > 3;
code desc mst defects
---- --------------- --- -------
AB Megawamp ID 5
1 row selected
We find that only one product is selected. Notice that
parentheses enclose the or expression. This is because without
the parentheses the and expression would have been executed
first. Remember that without parentheses the ands are processed
first and the ors are processed next. Not inserting the
parentheses would have changed the meaning considerably:
SELECT-10
select *
from pm
where mst='WA' or mst='ID' and defects > 3;
code desc mst defects
---- --------------- --- -------
AB Megawamp WA 3
DD Decawidgit WA 3
AB Megawamp ID 5
3 rows selected
The above query is the equivalent of saying, "List all products
in Washington and list all products in Idaho with defects over
3%." Another way of stating it is "In looking through all the
products, if a product is manufactured in Washington or an Idaho
product has over 3% defects, add it to the list." The important
concept is that the 3% defects relates to the products
manufactured in Idaho, not all products.
SELECT-11
SETS (used with constants)
Instead of relating items one at a time, we can relate groups of
items at once. These groups are called sets. They are enclosed
by parentheses and the constants are separated by commas.
ANY
You saw above how you can use =, >, <, <>, >=, <=. We can expand
that concept to include "any" and "all". As you will see, the
"any" corresponds to an "or" and the "all" corresponds to an
"and".
All possible combinations:
=any means equal to any
>any means greater than any
<any means less than any
<>any means not equal to any
>=any means greater than or equal to any
<=any means less than or equal to any
Let's rewrite the query concerning all products manufactured in
Washington or Idaho:
select desc
from pm
where mst =any ('WA','ID');
desc
-----------------
Megawamp
Decawidgit
RGBMouse
Megawamp
3 rows selected
Translated into English it would be: Select the description from
the p(roduct) table where the state of manufacture is equal to
any of the following - WA or ID.
We can use numbers too. What if you wanted to know the names of
products that have a percentage of defects of 2% or 3%:
select desc
from pm
where defects =any (2,3);
desc
-----------------
Megawamp
Decawidgit
RGBMouse
Dynamic Disk
4 rows selected
SELECT-12
IN
An equivalent to =any is in. We will see that sets are much more
useful when we discuss using nested selects and multiple tables.
However, you could translate the above query to:
select desc
from pm
where defects in (2,3);
ALL
The "all" is similar to the "any" except that instead of the
items being connected by "or's", they are connected by "and's".
It is useful in determining a group of items outside a certain
set. If you wanted to find all products manufactured outside
of WA and ID:
select *
from pm
where mst !=all ('WA','ID');
BETWEEN constant AND constant
An easy way to select a range of values is use the between/and
component of the where clause. If you wanted to find the product
descriptions with defects between 3% and 10%:
select desc, defects
from pm
where defects between 3 and 10;
desc defects
--------------- -------
Megawamp 3
Decawidgit 3
Technowidgit 10
Dynamic Disk 3
Megawamp 5
5 rows selected
Notice that when we use the between, we include the 3 and the 10.
Unfortunately, the word "between" can have more than one meaning in
standard English. If you say "The treasure is located between San
Francisco and Walnut Creek", you would assume that the treasure
is not IN San Francisco or Walnut Creek. That is, you would not
include the end points. If you say "This insurance plan is
available for those people between the ages of 18 and 65", you
would assume that if you are 18 or 65, you are eligible. That
is, you would include the end points. Just remember that in SQL,
between includes the end points.
SELECT-13
MATHEMATICAL FUNCTIONS - AVG, MIN, MAX, SUM, COUNT
All of the mathematical functions operate on numeric columns
except count which will operate on alphanumeric columns.
SYNTAX:
AVG([DISTINCT] column_name)
MIN(column_name)
MAX(column_name)
SUM([DISTINCT]column_name)
COUNT([DISTINCT]column_name)
SUMMARY
AVG - without DISTINCT: The average of all values in a column
with DISTINCT : The average of non-duplicate values
MIN - The minimum value in the column
MAX - The maximum value in the column
SUM - without DISTINCT: The sum of all values in the column
with DISTINCT : The sum of non-duplicate values
COUNT - without DISTINCT: the number of column entries
with DISTINCT : the number of non-duplicate column entries
Notice that some of the functions have the distinct option. This
will delete all rows in the table which have duplicate column
names. The following examples show the difference:
select count(mst)
from pm;
CNT(mst)
--------
9
9 rows selected
The above counts all the entries in the mst column. There are a
total of nine.
select count(distinct mst)
from pm;
CNT(mst)
--------
5
9 rows selected
The above is typically more useful. It answers the question:
"How many different states are used for manufacturing products?"
There is one difference between the syntax for standard SQL and
this program, SSQL. Standard SQL allows count(*). In SSQL the
"*" is not allowed in the count function. You must have a valid
column name.
SELECT-14
Before we discuss the other functions, let's look at the
percentage of defects in Arizona:
select *
from pm
where mst = 'AZ';
code desc mst defects
---- --------------- --- -------
AC Gigasnarf AZ 0
DZ Electrowidgit AZ
EA nanomouse AZ 12
3 rows selected
In Arizona, the Gigasnarf has zero defects, the nanomouse has 12%
defects but there is nothing listed for the Electrowidgit. This
"nothing" is called a null. Nulls are important in relational
data bases. It does not mean zero or blank. A null means that
the data element is not relevant in the row. In our case,
Electrowidgits are not tracked for defects like other parts.
This isn't to say that there are zero defects. It just means we
have no entry. Because there are only two valid entries for
defects in Arizona, the functions will not take into account the
null data element. The average percentage of defects will be
12/2 = 6, not 12/3 = 4.
select avg(defects)
from pm
where mst='AZ';
AVG(defects)
------------
6
3 rows selected
select count(defects)
from pm
where mst = 'AZ';
CNT(defects)
------------
2
3 rows selected
SELECT-15
GROUP BY, HAVING
The group by clause is normally used in conjunction with the
mathematical functions. It does two operations:
1. It sorts by the column name.
2. The mathematical functions only operate based on the rows
which have the same column name. The functions in essence create
sub-totals based on the column name.
What if we wanted to know the average percentage of defects in
each state?
select mst, avg(defects)
from pm
group by mst;
mst AVG(defects)
-----------------
AZ 6
ID 3
NV 3
OR 10
WA 3
5 rows selected
In SSQL you must select the column name that you use in the group
by clause.
You can restrict the group by clause through the use of the
"having" component. In SSQL you are allowed one simple selection
which includes the =, >, <, >=, <=, and <>. As with the group
by, the column name in the having component must exist in the
select clause.
If you only wanted a list of states with the average percentage
of defects over 3%, you would type the following:
select mst, avg(defects)
from pm
group by mst having avg(defects) > 3;
mst AVG(defects)
--- ------------
AZ 6
OR 10
2 rows selected
The group by clause can have multiple fields. This is when you
want a sort within a sort. However with such a small table we
cannot create anything useful.
SELECT-16
ORDER BY
The order by clause sorts the table based on the column name(s)
listed. As with the group by clause, the order by clause can be
used with the where clause. For example, the following will
produce a sorted list of descriptions for those items
manufactured in Arizona:
select desc
from pm
where mst = 'AZ'
order by desc;
desc
-----------------
Electrowidgit
Gigasnarf
nanomouse
3 rows selected
You can also produce a sort within a sort. The following will
produce an ordered list of states and within each state the
product descriptions will be sorted:
select mst, desc
from pm
order by mst, desc;
mst desc
--- -----------------
AZ Electrowidgit
AZ Gigasnarf
AZ nanomouse
ID Megawamp
ID RGBMouse
NV Dynamic Disk
OR Technowidgit
WA Decawidgit
WA Megawamp
9 rows selected
The order by clause assumes ascending order. You can reverse the
order by adding the descending modifier to the sort. It alway is
put after the the column name it is modifying.
select *
from pm
where defects > 3
order by defects descending;
code desc mst defects
---- --------------- --- -------
EA nanomouse AZ 12
DA Technowidgit OR 10
AB Megawamp ID 5
3 rows selected
SELECT-17
INTO file_name
Normally the report that is produced by the select command is
displayed on the monitor. However, through the use of the into
clause, the report can be redirected to a text file or to the
printer. The text file is in standard ASCII text. That is,
there are no special characters in it. The file name you give it
has the extension ".txt" added. Your file name must be one to
eight characters and you cannot have a period "." in the file
name.
If you wanted all the Arizona products stored in a file called
ARIZ and you wanted the report sorted by product description:
select *
from pm
where mst='AZ'
into ariz
order by desc;
[ 3 found ]
The only response to your query would be the number of rows
selected. Now it would be stored on your disk under the name
"ARIZ.TXT". To display the file on the monitor, you would exit
SSQL and type the following AT THE DOS PROMPT. The report would
be displayed:
A>type ariz.txt
code desc mst defects
---- --------------- --- -------
DZ Electrowidgit AZ
AC Gigasnarf AZ 0
EA nanomouse AZ 12
You can also have the report printed on the printer. Replace the
file name with prn or lpt1. Make sure the printer is on when you
use this option. For example, the following would print the
report instead of display it on the monitor.
select *
from pm
where mst='AZ'
into prn
order by desc;
[ 3 found ]
SELECT-18
