Databases Reference
In-Depth Information
The statement needed to do this is actually rather simple.
UPDATE SALESPERSON SET COMMPERCT =
(SELECT AVG(COMMPERCT) FROM SALESPERSON)
The result only tells you how many rows are updated. To see the effect of the
change, you would need to retrieve the row data from the SALESPERSON table
and be sure and include the COMMPERCT column.
SELF-CHECK
•
Describe the purpose of the TOP, DISTINCT, GROUP BY, ORDER
BY, and HAVING keywords.
•
Describe the limits on the ORDER BY clause when used with the
GROUP BY clause.
•
Compare and contrast the use of UNION, EXCEPT, and INTERSECT.
7.2 Using Joins and Subqueries
More advanced queries are possible using joins and subqueries. A
join
is a query
that combines columns from two or more sources. Effectively, you are reversing
the non-loss decomposition process used to normalize the database, pulling the
related data back together in a combined result. Technically, you can only join
two sources, such as two tables. When you need to join more sources, you do
it in twos. You join two tables, then join that result to a third table, and so on.
A
subquery
is another variation that uses multiple SELECT statements in a sin-
gle query. A subquery is a nested query, and can include several layers of nest-
ings, with the innermost query executed first.
A complete discussion of joins and subqueries is beyond the scope of this
text. Instead of trying to make you a query expert, the goal is to introduce you
to their use. This will give you the tools you need to meet the vast majority of
your data recovery needs.
7.2.1 Understanding Joins
Up to this point, all of the SELECT features that we have looked at have been
shown in the context of retrieving data from a single table. The time has come
to look at how the SELECT command combines (joins) columns from two or
more tables. To make a join work, two specifications must be made in the
SELECT statement. The tables to be joined must be listed in the FROM clause
