54 PEDALS 54...

Odnośniki

Smutek to uczucie, jak gdyby się tonęło, jak gdyby grzebano cię w ziemi.

25
42 SEATS 24.50
46 TIRES 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00
76 CLIPPLESS SHOE 65.00 <-NOTE SAME #
You saved your company from this bad situation by checking PART before anyone used it:
INPUT/OUTPUT:
SELECT F.PARTNUM, F.DESCRIPTION,
S.PARTNUM,S.DESCRIPTION
FROM PART F, PART S
WHERE F.PARTNUM = S.PARTNUM
AND F.DESCRIPTION <> S.DESCRIPTION
PARTNUM DESCRIPTION PARTNUM DESCRIPTION

========== ======================== ======= ============
76 ROAD BIKE 76 CLIPPLESS SHOE
76 CLIPPLESS SHOE 76 ROAD BIKE
ANALYSIS:
Now you are a hero until someone asks why the table has only two entries. You,
remembering what you have learned about JOINs, retain your hero status by explaining how the join produced two rows that satisfied the condition WHERE F.PARTNUM =
S.PARTNUM AND F.DESCRIPTION <> S.DESCRIPTION. Of course, at some point, the row of data containing the duplicate PARTNUM would have to be corrected.
Summary
Today you learned that a join combines all possible combinations of rows present in the
selected tables. These new rows are then available for selection based on the
information that you want.
Congratulations--you have learned almost everything there is to know about the
SELECT clause. The one remaining item, subqueries, is covered tomorrow (Day 7,
"Subqueries: The Embedded SELECT Statement").
Q&A
Q Why cover outer, inner, left, and right joins when I probably won't ever
use them?
A A little knowledge is a dangerous thing, and no knowledge can be expensive.
You now know enough to understand the basics of what your SQL engine might
try while optimizing you queries.
Q How many tables can you join on?
A That depends on the implementation. Some implementations have a 25-table
limit, whereas others have no limit. Just remember, the more tables you join on,
the slower the response time will be. To be safe, check your implementation to find out the maximum number of tables allowed in a query.
Q Would it be fair to say that when tables are joined, they actually become
one table?
A Very simply put, that is just about what happens. When you join the tables, you can select from any of the columns in either table.
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."
Quiz
1. How many rows would a two-table join produce if one table had 50,000 rows and the other had 100,000?
2. What type of join appears in the following SELECT statement?
select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where e.employee_id = ep.employee_id;
3. Will the following SELECT statements work?
a. select name, employee_id, salary
from employee_tbl e,
employee_pay_tbl ep
where employee_id = employee_id
and name like '%MITH';
b. select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where name like '%MITH';
c. select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where e.employee_id = ep.employee_id
and e.name like '%MITH';
4. In the WHERE clause, when joining the tables, should you do the join first or the conditions?
5. In joining tables are you limited to one-column joins, or can you join on more than one column?
Exercises
1. In the section on joining tables to themselves, the last example returned two
combinations. Rewrite the query so only one entry comes up for each redundant
part number.
2. Rewrite the following query to make it more readable and shorter.
INPUT:
select orders.orderedon, orders.name, part.partnum,
part.price, part.description from orders, part
where orders.partnum = part.partnum and
orders.orderedon between '1-SEP-96' and '30-SEP-96'
order by part.partnum;