Docstoc

2

Document Sample
2 Powered By Docstoc
					2.4 Using Outer Joins in Queries
In Section 2.3 we created queries that required the use of information from more than one
database object (tables, queries). We know that when using relational databases, we can relate
the information from one object to another. We have also briefly discussed the mechanism that
is used to actually create an inner join.. In this section, we will introduce the concept of an outer
join and explore how this can be used to obtain our desired record sets.

Joining two tables –Outer Joins
Let us consider a database consisting of three tables as seen in Figure 1. This database will keep
track of shipments and receipts of various fruits for our little fruit store. The fruit names are
listed on table Fruit by Fruitid. Shipments are listed on a separate table, as are receipts. The
Ship and Receipts tables are each related to the Fruit table by Fruitid. Is referential data integrity
enforced for either of these relationships? No, both the Ship table and Receipts table contain
records with Fruitid numbers not found on the Fruit table.

                                                                               qty
                          qty                                        fruitid shipped
             fruitid   received                                            1       2
                   1          3
                                  ∞   1     fruits fruitid
                                                             1 ∞           1       3
                                          apples         1
                   1          4           pears          2                 3       4   Figure 1
                   3          6           bananas        3                 4       3
                   5          5           grapes         4                 4       2
                                                                           7       1

What happens when we do an inner join between the Ship
                                                                           Fruit.   ship.          qty
table and Fruit table? If you recall, when the computer           fruits   fruitid fruitid       shipped
joins these two tables it combines every Ship record with      apples            1           1         2
every Fruit record creating a master list of all possible      apples            1           1         3
records. Selecting on those records with matching join-        bananas           3           3         4
on fields a dynaset of 5 records is formed as seen in          grapes            4           4         3
Figure 2.                                                      grapes            4           4         2
                                                                              Figure 2
Notice that there will be no records for pears since there were no receipts of pears. There will
also be no records for Fruitid 7, even though there were receipts, since there is no matching
record on the Fruits table. Any subsequent queries based on this join, will not include either of
these two items.

But what if you want to list all the records from the Fruit table including pears? We’ve already
noticed that our combined record set only contains records if there is a match on both tables. If
you want all the records on the Fruit table, and related records on the Ship table, you need to use
an Outer Join. The outer join relative to the Fruit table will take all records that have a
matching value in the foreign key field plus any unmatched records on the Fruit table as seen
in Figure 3. Notice that now there are pears listed, even though the matching records on the
received table entries are blank (null values).



Using Outer Joins in a Query 2.4                                                       Page 221
Alternately, you can tell Access to include all the records from the Ship table, and only the
related records from the Fruit table. This would require an Outer Join relative to the ship table
and result in the dynaset seen in Figure 4.

                 Fruit.         ship.        qty                               Fruit.           ship.           qty
      fruits     fruitid       fruitid     shipped            fruits           fruitid         fruitid        shipped
   apples                  1             1       2         apples                        1               1               2
   apples                  1             1       3         apples                        1               1               3
   pears                   2                               bananas                       3               3               4
   bananas                 3             3          4      grapes                        4               4               3
   grapes                  4             4          3      grapes                        4               4               2
   grapes                  4             4          2                                                    7               1


                      Figure 3                                                                Figure 4

The inner join is the default join type in Access. To specify an outer join type in a query, you
can use the QBE tool. First show all the tables required for your query and setup the appropriate
relationships, if they have not already been specified. Double click on the line joining the two
objects. A dialog box will appear that allows you to select inner or either type of outer join.
Notice that there is no option to list all records from both tables regardless of whether or not they
match.
What dynaset would result if we were to join the                       Field                 Fruidid         Fruits        Qty
Fruit table to the Receipt’s table in order to list                                                                      Received
fruitid, name and quanity received? Depending on                       Table                  Fruit          Fruit       Receipts
which join type you would get one of the following                     Total
as seen in Figure 5:                                                   Sort
                                                                       Show                    x               x              x
                                                                       Criteria
                                                                       OR
                                                                       OR


                                                                   qty
                                              fruitid fruits    received                                                qty
                         qty                        1 apples            3                 fruitid fruits             received
    fruitid fruits    received                      1 apples            4                       1 apples                     3
          1 apples            3                     2 pears                                     1 apples                     4
          1 apples            4                     3 bananas              6                    3 bananas                    6
          3 bananas           6                     4 grapes                                                                 5

           Inner Join                        Outer Join – Relative Fruit                 Outer Join – Relative Received
                                                                Figure 5

Notice that the inner join contains only those records in common, no pears or grapes from the
Fruit table and no fruitid 5 from the Received table. The outer join relative to the Fruit table
does contain a record for pears with a null value for quantity received, while the outer join
relative to the Received table contains a record for fruitid 5 with a null value for fruitid and
fruits. Why doesn’t fruitid for fruitid 5 appear in the resulting dynaset? The fruitid was taken
from the Fruits table where no such value exists. When using outer joins, be careful when
showing the join-on field. Use the table that contains the “full” set of values..


Using Outer Joins in a Query 2.4                                                                                   Page 222
      Multiple Joins with a “Many to One to Many” relationship
      What happens if we wanted to use outer joins to relate all three of the Fruit database tables in
      order to summarize, by Fruit, the number of items shipped, and the number of items received?
      We already know that we cannot directly relate the Ship table to the Receipts table since the key
      that relates them is not primary in either table. But the Ship table is related to the Fruits table
      which is in turn related to the Receipts table, won’t this work with outer joins? If you recall with
      inner joins, we needed to understand the types of relationships before we could determine if
      multiple joins gave us a realistic record set.
      Notice that the “common” table is the Fruit table. The relationship between the Fruit table and
      the Ship table is a one-to-many. For each Fruit there may be many shipments. Similarly there is
      a one-to-many relationship between the Fruit table and the Receipt table. Thus the relationship
      between the three tables in a many-to-one-to-many relationship. As with the inner join, we
      cannot relate a specific record on the Fruits table to both one specific record on the Ship table
      and one specific record on the Receipts table. For example, which apple shipments go with
      which apple receipts? When the “common” table is the “one” and the related tables are the
      “many” it will not be possible to relate all three in one step even using outer joins. Let us look at
      an example to better illustrate this case.
      When joining more than two tables, MS Access will successively create the record set from each
      join and then combine it with the next table. First recall the record set that is generated by the
      outer join of the Fruits table and the shipments table, relative to the Fruits table, as seen in Figure
      6. Here we have all combinations of records with matching fruitids from both tables plus any
      additional records which appear on the Fruits table.

                                                                        qty                            Fruit.          ship.        qty
                                                        fruitid       shipped            fruits        fruitid        fruitid     shipped
              fruits fruitid                                      1         2         apples                     1              1       2
            apples         1                                      1         3         apples                     1              1       3
            pears          2                                      3         4         pears                      2
            bananas        3                                      4         3         bananas                    3              3          4
            grapes         4                                      4         2         grapes                     4              4          3
                                                                  7         1         grapes                     4              4          2
                Figure 6

      Next let’s take this resulting dataset and join it to the Received table as seen in Figure 7. Here
      we’ve taken all the records from record set of the outer join of Fruits and Ship tables and used an
      Outer Join, relative to the fruits table, to join it with the Receipts table. This should ensure that
      the resulting dynaset contains all fruits.
              Fruit.         ship.        qty                                            Fruit.         ship.          qty   receipts.    qty
   fruits     fruitid       fruitid     shipped                              fruits      fruitid       fruitid       shipped fruitid   received
apples                  1             1       2              qty
apples                  1             1       3
                                                                          apples                   1             1         2         1         3
                                                  fruitid received
pears                   2                               1        3        apples                   1             1         2         1         4
bananas                 3             3      4          1        4        apples                   1             1         3         1         3
grapes                  4             4      3          3        6
grapes                  4             4      2          5        5
                                                                          apples                   1             1         3         1         4
                                                                          pears                    2
                                                                          bananas                  3             3              4          3   6
                                Figure 7                                  grapes                   4             4              3
                                                                          grapes                   4             4              2



      Using Outer Joins in a Query 2.4                                                                                          Page 223
Notice the final data set shows 4 different records for apples resulting from 2 shipments and 2
receipts. As with the inner join case of a many-to-one-to-many relationship, this algorithm of
using outer joins also results in multiple record sets being created for each unique item (in this
case fruitid). This number can be calculated as the product of the number of records of that item
from each of the related tables. In this case there are 2 ship records for fruitid 1 multiplied by 2
receipt records for fruitid 1 resulting in 4 records.
To summarize this multiple join, we can write a query that takes this record set and groups it by
Fruitid. The resulting dynaset can be seen in Figure 8. Here the four records created for apples
are combined into a single record showing the receipt of 14 apples and the shipment of 10
apples. We know from merely looking at the original tables that only 7 apples have been
received and 5 apples shipped. As with an inner join, using an outer join to combine records for
a many-one-many set of related data, leads us to the wrong results.

                                                             Field      Fruidid   Fruits     Qty      Shipped
                                                                                           Received
fruits      fruitid      qty. received qty shipped           Table       Fruit    Fruit    Receipts      Ship
apples                 1             14          10          Total
pears                  2                                     Sort
bananas                3              6           4          Show         x         x         x           x
grapes                 4                          2          Criteria
                      Figure 8                               OR
                                                             OR


So, is it possible to create a table listing a summary of receipts and shipments by Fruitid? Yes,
but it will involve more than one query step. Before we detail how this can be done, let us first
look at a case where we can join multiple tables in a single query using outer joins.


Multiple Joins with a “One to Many to One” Relationship
Now consider a slightly different database as seen in Figure 9. Here we have three different
tables. This time, the Fruit table is related to the Receipts table by Fruitid, and the Receipts table
is then related to the Vendor table by Vendor. As we’ve previously learned, we cannot directly
relate Vendors to specific Fruits since there is no foreign key

                                              qty
      fruits fruitid               fruitid received vendor              vendor company name
    apples         1                     1        3      2                   1 Washington Co.
    pears          2                     1        4      3                   2 ABC Importers
    bananas        3                     3        6      1                   3 Florida Grows             Figure 9
    grapes         4                     5        5      0                   4 California Inc

However both the Vendor and Fruits tables relate to the Receipts table. This “common” table is
the “many” in the one-to-many-to-one relationship. Does the outer join of these three tables
give us meaningful results? They should, since one can follow the relationships logically to see
that for each record on the Receipts table there is one unique Fruitid and one unique Vendor.
There are several different combinations of inner and/or outer joins that can be used to join these
three tables. In several cases the joins are ambiguous and Access cannot execute them. In the
Using Outer Joins in a Query 2.4                                                              Page 224
above example we are using an outer join from the Fruits table to the Received table (relative to
Fruits) and then another outer join from the Receipts table to the Vendors table (relative to the
Receipts table). The resulting dataset is seen in Figure 10.


                         Fruit. receipts.     qty    receipts. vendors.
                  fruits fruitid fruitid   received vendor vendor company name
                apples         1         1         3         2        2 ABC Importers     Figure 10
                apples         1         1         4         3        3 Florida Grows
                pears          2
                bananas        3         3         6         1        1 Washington Co.
                grapes         4




As we can see, whenever we are joining tables in a database, we need to carefully understand the
relationships of our database tables before attempting to extract information. This is especially
true in cases where multiple joins are required. We also need to understand what information is
to be included in our resulting dataset. Do we want to include only those records which are
common to all tables (inner join) or all records from one specific table and only those records
which are in common from the other (outer join)?



Solving the original problem
Now let us go back to solving the original problem of creating a list of each fruit summarizing
the total amounts shipped and received. Having already concluded that it is not possible to
simply combine the three tables together in one step. How can we go about creating a solution?

                                                                                          qty
                            qty                                                 fruitid shipped
               fruitid   received                                                     1       2
                     1          3
                                    ∞    1       fruits fruitid
                                                                    1 ∞               1       3
                                               apples         1
                     1          4              pears          2                       3       4
                     3          6              bananas        3                       4       3
                     5          5              grapes         4                       4       2
                                                                                      7       1
What if we were to first summarize receipts and shipments separately before combining the
results?
       We could use a Query to create a list of all fruits and the amounts received. This would
        require an Outer Join of the Fruits table and the Receipts table. We could use the
        Group by feature to summarize receipts by Fruitid and sum the quantity received field.
       Similarly, we could use another query to create a list of all fruits and the amounts
        shipped. This would require an Outer Join of the Fruits table to the Ship table. Again
        we would use the Group by feature to summarize by Fruitid and sum the quantity
        shipped.




Using Outer Joins in a Query 2.4                                                                  Page 225
Look at the dynasets that result from these two queries as seen in Figure 12 and Figure 12.

Field      Fruidid       Fruits          Qty
                                       Received                                      SumOfqty
Table       Fruit        Fruit         Receipts               fruitid    fruits       received
Total      Groupby      Groupby         Sum                          1 apples                    7
Sort                                                                 2 pears
Show              x         x             x                          3 bananas                   6
Criteria
                                                                     4 grapes
OR
OR                                                            Figure 11              ReceiptSummary



Field      Fruidid       Fruits          Qty
                                       Shipped                                          SumOfqty
Table       Fruit        Fruit          Ships                    fruitid    fruits       shipped
Total      Groupby      Groupby         Sum                             1 apples                     5
Sort                                                                    2 pears
Show              x         x             x                             3 bananas                    4
Criteria
                                                                        4 grapes                     5
OR
                                                                                          ShipSummary
OR                                                                  Figure 12

Notice we now have a one-to-one relationship between these two resulting query tables
(ShipSummary and ReceiptSummary) as well as to the original fruit table. Each of the fruits
exists on each list exactly once. We know that we can easily join tables that contain one-to-one
relationships between the records. Thus, the final step is to write a query using inner joins to
relate the ShipSummary query to the ReceiptSummary query and then to the original Fruit table.
The resulting dynaset is shown in Figure 13.

Field      Fruits     Fruidid      Sumof qty      Sumof qty
                                    received       shipped                        SumOfqty               SumOfqty
                                                                   fruits fruitid received               shipped
Table       Fruit      Fruit        Receipt         Ships        apples         1                    7              5
                                   Summary        Summary        bananas        3                    6              4
Total                                                            grapes         4                                   5
Sort                                                             pears          2
Show          x         x             x              x
                                                                                      Figure 13
Criteria


Notice that receipts of Fruitid 5 and shipments of Fruitid 7 are still not part of this query. The
outer join of the Fruit table to the Receipts table included all Fruits, but not items on Receipts
which do not appear on the Fruits table. Similarly, the outer join of the Fruit table to the Ship
table included all Fruits but not items on the Ship table which do not appear on the Fruits table.




Using Outer Joins in a Query 2.4                                                                     Page 226
In general when faced with this type of problem where you wish to summarize information that
cannot be directly related follow these steps:
       Summarize each set of information using an outer join to the table that contains the entire
        listing. Remember, if Referential Data Integrity is not enforced, there still may be items
        on the related tables that are “lost”.
       Using an inner join, combine each of the summarizations and the original table (if
        needed) to combine your results.




Using Outer Joins in a Query 2.4                                                   Page 227
Exercise 2.4-1 Child database
Again let us use the child database from Exercise 2.4-1 to create the following queries.



                                       Table:      Children
                                       Childid#    Age          Name
                                       1           13           Mike
                                       2           9            Rachel
                                       3           10           Alex
                                       4           9            Naomi
                                       5           8            Ann Marie
                                       6           11           Robert
                                       7           15           Miranda
                                       8           5            Stephanie
                                       9           3            Thomas

Table:      Swim                                                Table:       Ice Skate
swim#       childid      minswam       date                     skate#       childid       min skated   date
1           3            20             7/ 1/98                 1            1             23            7/ 3/98
2           4            10             7/ 3/98                 2            3             13            7/ 3/98
3           1            15             7/ 3/98                 3            4             17            7/ 8/98
4           3            5              7/ 5/98                 4            3             5             7/ 9/98
5           3            15             7/ 9/98                 5            3             15            7/10/98
6           9            12             7/ 9/98                 6            8             8             7/10/98
7           7            22             7/10/98                 7            7             5             7/10/98
8           7            15             7/11/98                 8            1             12            7/12/98
                                                                9            7             5             7/13/98


    1. How could you modify the query in Exercise 2.3-1 Query #5 so that your list would include all
       children’s names regardless of whether or not they swam?




    2. If the childid# on the children table is setup as a number field and the childid# is setup as a text
       field on the swim table – how would that effect the database?




Using Outer Joins in a Query 2.4                                                                   Page 228
3. Using the design view below, write a query that will create a list of all children (name, id) and the
average minutes they swam.
Tables __________________________________ Join on ___________________________________
Join Type ________________________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for calculated expression if needed:




4. Using the design view below, write a query that will list all children over the age of 9 and the total
minutes they swam.
Tables __________________________________ Join on ___________________________________
Join Type ________________________________
Field

Table

Total

Sort

Show

Criteria

OR

OR




Using Outer Joins in a Query 2.4                                                        Page 229
5. You are trying to develop a ratio of minutes swam to age – to see if you can predict a child’s behavior.
Create a query that will list each child’s id, name, age and the value of this ratio. The ratio should be
calculated as follows:
     Average swam divided by their age
     Use the results of Query 5 to obtain values for average minutes swam.
     If a child did not swim the ratio returned should equal zero.
Tables __________________________________ Join on ___________________________________
Join Type ________________________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for calculated expression if needed:




Using Outer Joins in a Query 2.4                                                          Page 230
            Name            Age        SumOfmin skated     SumOfminswam          total
            Mike            13         35                  15                    50
            Rachel          9                                                    0
            Alex            10         33                  40                    73
            Naomi           9          17                  10                    27
            Ann Marie       8                                                    0

            Robert          11                                                   0
            Miranda         15         10                  37                    47
            Stephanie       5          8                                         8

            Thomas          3                              12                    12


6. Using the design view(s) below and on the following pages, write a query(s) that will result in the
table you see above.
New Query Name: __________________________
Tables __________________________________ Join on ___________________________________
Join Type ________________________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for calculated expression if needed:




Using Outer Joins in a Query 2.4                                                         Page 231
#6 continued
New Query Name: ___________________________
Tables __________________________________ Join on ___________________________________
Join Type ________________________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for calculated expression if needed:




New Query Name: ___________________________
Tables __________________________________ Join on ___________________________________
Join Type ________________________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for calculated expression if needed:




Using Outer Joins in a Query 2.4                                          Page 232
  Chapter 2.4-2 Conference Registration Database

                                    Attendees
Id# FName     LName                 Address           City     State Zip
 1 Nancy     Davolio    507 - 20th Ave. E. Apt. 2A Seattle     WA    98122                              Registration
 2 Tim       Smith      908 W. Capital Way         Tacoma      WA    98401                    registration# id# Service Code
 3 Brandon Coake        722 Moss Bay Blvd.         Kirkland    WA    98033                               1   4D
 4 Margaret Peacock 4110 Old Redmond Rd.           Redmond WA        98052                               2   7A
 5 Matthew Dunn         14 Garrett Hill            Seattle     WA    98105                               3   7B
 6 Helvetius Nagy       722 DaVinci Blvd.          Kirkland    WA    98034                               4   2B
 7 Deborah Peterson 305 - 14th Ave. S. Suite 3B Seattle        WA    98128                               5   1A
10 Smith     John       123 Main Street            Columbus OH       43081                               6   5D
11 Johnson Mark         420 Broad Street           Columbus OH       42001                               7   6A
12 Griffin   Jen        500 High Street            Columbus OH       43081                               8   6C
13 King      Jennifer 400 Walnut Street            Cincinnati OH     41098                               9   9D
14 Day       Karen      250 Fifth Street           Cincinnati OH     41077                              10 10 E
15 Bair      Jeff       125 Plum Street            Cincinnati OH     41077                              11 14 D
16 Beard     Nancy      10 South Wacker            Chicago     IL    60639                              12 17 A
17 Wallace Brad         120 South LaSalle          Chicago     IL    60639                              13 17 B
18 Davis     Joe        15 West Washington         Chicago     IL    60221                              14 12 B
                                                                                                        15 16 A
                                                                payments                                16   5D
                  Rates                              id# payment$   date          method                17 13 A
    Service Code Description              Fee          1      $400.00 5/16/2002 credit card             18 13 C
    A               Class Tuition       $800.00        4      $250.00 6/16/2002 check                   19 11 D
    B               Exam - I            $200.00        4      $250.00 5/22/2002 credit card
    C               Room & Board        $400.00        6 $1,200.00 6/21/2002 PO
    D               All Inclusive     $1,250.00        7      $500.00 5/28/2002 credit card
    E               Exam - II           $100.00       11      $200.00 6/3/2002 check
                                                      11 $1,050.00 6/8/2002 PO
                                                      12      $100.00 6/8/2002 credit card
                                                      15 $1,250.00 6/12/2002 credit card
                                                      16      $800.00 6/16/2002 credit card
                                                      17      $100.00 6/2/2002 check


        The Conference-Registration database above consists of the following tables:
            Attendees -list of all attendees of this annual conference. A unique id# is associated with
              each attendee. Note that some attendees have not yet registered for specific services but
              have been included since they have indicated their desire to attend.
            Rates – A list of the different conference services and rates, by unique service code.
              Attendees may sign up for one or more service code items or the all inclusive fee which
              covers all services including exams.
            Registration – is a list of all this year’s conference registrations by attendee by service
              (ie: attendee #7 is signed up for both A and B – class tuition and exam).
            Payments – is a list of this year’s payments including the amount, date and payment
              method.




        Using Outer Joins in a Query 2.4                                                                 Page 233
Draw the relationship diagram and indicate the primary and foreign keys.
    Table:                                                                     Table:
    Payments                                                                   Rates
    Primary Key:                                                               Primary Key:

                            Table:                Table:
                            Attendees             Registration
                            Primary Key:          Primary Key:




Using the QBE grids provided, write a query or set of queries to answer the following questions.
Name the query by the question number. If more than one query is required for a specific
question, name each query by the question number plus A, B, etc. Assume that the data provided
is only a part of the data base and the Referential Data Integrity is enforced for all relationships.

    1. List the full name of all attendees planning on taking any of the exams during the
       conference. Sort the list by last name then first name. All exams are listed as such by
       their description. Make sure your query will work even if another exam is later added to
       the table.
    2. Summarize the payments made for all Ohio Attendees. Include the total amount of their
       payments, the attendee’s id number and full name.
    3. Summarize the payments made by payment method. Include the total value of the
       payments, the average payment value and the minimum payment value.
    4. Create a Registration Fee list which includes each registration record and the associated
       fee for services. List the registration#, registrant’s id# and the associated fee.
    5. Create a list by attendee (id#, last name) that details the number of payments made and
       the total amount still owed by the attendee based on their registration charges. Please list
       all attendees.
    6. A 5% late fee is being assessed on the outstanding balances (amount owed calculated in
       query 5). Please list, by attendee (id#, last name), the original amount owed, the late fee
       and the new balance.
    7. Summarize the registration fees for the current registration list by the attendee’s state of
       residence.




Using Outer Joins in a Query 2.4                                                      Page 234
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Using Outer Joins in a Query 2.4                                     Page 235
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Using Outer Joins in a Query 2.4                                     Page 236
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Using Outer Joins in a Query 2.4                                     Page 237
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Using Outer Joins in a Query 2.4                                     Page 238
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field
Table
Total
Sort
Show
Criteria
OR
OR
Additional Room (if needed)




Using Outer Joins in a Query 2.4                                     Page 239
 Chapter 2.4-3 - WebServices Database

                                      Client
Client#            Client                Contact          Phone          Industry
     1 Elegance Hair              Helen Phars         1-223-444-5555              1                          Projects
     2 Overholts                  Bill Overholts      206-888-9999                4              Project#   Description      Client#
     3 Larsens School of Design Larry Larsen          1-233-445-5665              5                    1 grading system             4
     4 PS202                      Jeanne Moser        383-0333                    5                    2 on-line shopping           8
     5 Krackle Snacks             Jeanne-Pierre Marot 1-800-111-2222              4                    3 inventory                  8
     6 Le Chocolatier             Eliott Marti        1-555-111-1111              3                    4 repair status             10
     7 Neptune Swim               Fred Chen           445-1212                    3                    5 home page                 14
     8 The Candy Tree             Susan O'Brien       1-319-222-7777              4                    6 order entry                5
     9 Nate Olongo                Nate Olongo         1-455-333-1616              8                    7 info/home page             3
    10 HS heating                 Samantha Rider      888-3434                    1
    11 Zipsi plumbing             Order desk          1-909-333-9876              1
    12 Carter & Carter            James Carter        303-4132                    6
    13 Lofton Medical             Sue Waller          (333)123-4567               2
    14 Universal Beverages        Rene Lau            222-1333                    1
                                                                                                                         industry
                                                                              payments
                                                                                                                icode       industry
                     joblist                                      client# payment         date
                                                                                                                       1 services
service# fee# hrs project# provider         date                          3    $425.00 7/11/2002
                                                                                                                       2 medical
          1   5   31         tim lange     7/6/2002                       4    $300.00 7/5/2002
                                                                                                                       3 retail
          2   3   31         edna james 7/6/2002                          5 $1,000.00 7/15/2002
                                                                                                                       4 manufacturing
          3   4   82         kim sutton    7/6/2002                       5 $1,000.00 7/20/2002
                                                                                                                       5 education
          4   2   44         john krome    7/7/2002                       8    $500.00 7/2/2008
                                                                                                                       6 legal
          5   5   65         tim lange     7/7/2002                      10    $100.00 7/10/2002
                                                                                                                       7 government
          6   1   57         joan kline    7/7/2002
                                                                                                                       8 other
          7   3   83         edna james     7/1/702
                                                                               skills
          8   5   46         tom freed     7/6/2002               fee#        skill      fee
          9   4   66         john krome    7/8/2002                   1 animator        $85.00
      10      3   86         edna james 7/8/2002                      2 editor          $27.00
      11      4   86         john krome    7/9/2002                   3 artitst         $38.00
      12      4   86         john krome 7/12/2002                     4 programmer $75.00
                                                                      5 project mgr $57.00

Webservices is a small web design firm that services clients by providing a full range of web
design and execution services, from project management & design through animation and
programming. You have created the above database to help Webservices keep track of their
clients, projects and billing management. You have included the following tables:
     Client – contains a list of all past/present clients of Webservices including their industry
         classification.
     Industry – contains a list of industry classification descriptions.
     Skills – contains a list of skills available for your projects and hourly fee for each skill
         being charged to clients.
     Projects – contains a list of currently active projects including description and client
         (clients may have multiple projects).
     JobList – contains a list of hours spent by project and skill. Also included are the service
         date and provider name.
     Payments – contains a list of payments made to Webservices from clients.




Using Outer Joins in a Query 2.4                                                                              Page 240
Draw the relationship diagram and indicate the primary and foreign keys. Indicate 1 vs. many for
each relationship.

     Table:                           Table:                          Table:
     Skills                           Joblist                         Payments
     Primary Key:                     Primary Key:                    Primary Key:




     Table:                           Table:                          Table:
     Projects                         Clients                         Industry
     Primary Key:                     Primary Key:                    Primary Key:




Using the Relationship diagram above - what multiple joins are not valid (many – one-many).




Using the QBE grids provided, write a query or set of queries to answer the following questions.
Name the query by the question number. If more than one query is required for a specific
question, name each query by the question number plus A, B, etc. Assume that the data provided
is only a part of the data base and the Referential Data Integrity is enforced for all relationships.

    1. List the client name, contact and industry description for all clients with active projects.
       Sort the list by industry then client name.
    2. Summarize fees by project. List all current projects including the project number, client
       number, fees accrued and total manhours.
    3. Summarize the current balance ($accrued- $paid) by client. List all clients, including
       their client#, name, and industry description.
    4. Often animators need to contact the clients for whose projects they are working on. For
       each animator job (fee#1), list the service#, client name, contact name and phone number.
    5. Summarize manhours accrued by skill. List the fee#, skill description, total hours
       accrued for that skill, the minimum hours charged in one job for that skill and the average
       transaction hours charged for that skill. Include all skills.
    6. Service industry clients (icode #1) are notorious for not paying their bills. You are going
       to offer a one time deal – that if they pay off their balances in full they will receive a 10%
       rebate on the balance amount (ie: if they owe $100, they will get a $10 rebate). Write a
       query to list the value of this rebate for each of Service industry clients. List the client#,
       name and rebate value.




Using Outer Joins in a Query 2.4                                                     Page 241
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Using Outer Joins in a Query 2.4                                             Page 242
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Using Outer Joins in a Query 2.4                                             Page 243
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Using Outer Joins in a Query 2.4                                             Page 244
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Using Outer Joins in a Query 2.4                                             Page 245
Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Query Name _________________________ Tables Req’d ________________________
Join on _____________________________ Join Types _______________________
Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room (if needed)




Using Outer Joins in a Query 2.4                                             Page 246
Exercise 2.4-4 Chapter Review – Antique Car Club
Access Problem:
As the president of your antique car club, you have set up a database to keep track of your
members’ information for your annual club picnic. Portions of each data table are listed below.

Members
MemberID FName           LName           Street              City       State    Zip        Phone
       1 Bob            Marshall   644 Cloverview Ave.   Columbus       OH      43229   (614) 456-7890
       2 Stephanie      Turner     3844 Ligorio Dr.      Reynoldsburg   OH      43283   (614) 438-2023
       3 Max            Power      78 Elm St.            Columbus       OH      43083   (614) 293-1923
       4 David          Green      452 Washington St.    Grove City     OH      43529   (614) 435-6323
       5 Melanie        Hartwell   14525 St. Rt. 185     Piqua          OH      45385   (937) 283-8562
       6 George         Costanza   880 Cherry Lane       Columbus       OH      43293   (614) 298-2832
       7 Lillian        Hayworth   128 Sesame St.        Columbus       OH      43928   (614) 928-2844



                                                           Cars
Spouses                                                    MemberID   Make                  Model     Year
MemberID SpouseName                                               1 Chevrolet       Delux             1952
       1 Janet                                                    1 Ford            Fairlane          1957
       2 Ted                                                      2 Buick           Riviera           1970
       4 Matilda                                                  3 Buick           Riviera           1986
       7 Jake                                                     3 Pontiac         Firebird          1968
                                                                  3 Chevrolet       Coupe             1929
                                                                  5 Chevrolet       Bel Air           1956
                                                                  7 Ford            Mustang           1965
Children                                                          7 Pontiac         Firebird          1962
MemberID ChildName Age Sex                                        7 Oldsmobile      Convertible Coupe 1938
       1 Tara        15 F                                         7 Oldsmobile      Phaeton           1941
       1 Chad        10 M
       3 Ashley       4F                                   ClubPayments
       4 Kristen     13 F                                  MemberID AmtPaid DatePaid
       4 Christopher 10 M                                         5 $20.00 3/12/02
       4 Katie        6F                                          1 $15.00 3/15/02
       4 Kimberly     3F                                          3 $10.00 3/17/02
       4 Carl         2M                                          7 $10.00     4/1/02
       7 Randy       16 M                                         7 $10.00     4/1/02
       7 Joel        14 M                                         7 $10.00     4/1/02
       7 Amanda      10 F                                         2 $50.00 4/12/02
                                                                  1 $10.00 4/12/02
                                                                  5 $17.00 4/17/02




Using Outer Joins in a Query 2.4                                                              Page 247
                                   Using Databases to Solve Problems


    The Members table is a list of the names, addresses, and phone numbers of your club
     members. (Each member is identified by his or her member ID.)
    The Spouses table keeps track of the names of the spouses of your club members. You can
     assume that each member will only have one spouse at any given time. Unmarried members
     will not have a row on this table.
    The Children table lists the names, ages, and genders of the members’ children.
    The Cars table lists information about the members’ cars. Some members may have multiple
     antique cars that they register with the club, while other members may have none.
    The ClubPayments table keeps track of all the payments that members make to the club. The
     memberID of the member making the payment is listed with the date of the payment and the
     amount that was paid.


1. (1 pt.) Write a query named AutoList in the design view below to summarize car info by
   member ID. For each member that has at least one car registered with the club, list the
   member ID, the member’s full name, the number of antique cars they have, and the model
   year of the oldest car they own.

     Query Name: AutoList     Tables Required: _____________________________________
     Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:




Using Outer Joins in a Query 2.4                                               Page 248
                                   Using Databases to Solve Problems



2. (1 pt.) Write a query named MemberList in the design view below to summarize car info by
   member ID. For all members, list the member ID, the member’s full name, the number of
   antique cars he or she has, and the model year of the oldest car they own.

     Query Name: MemberList      Tables Required: __________________________________
     Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:




3. (1 pt.) What different records will be generated by each of the two previous queries, AutoList
   and MemberList? Explain the values you expect to find in the total number of cars and
   oldest car model year fields for these different records.




Using Outer Joins in a Query 2.4                                                  Page 249
                                   Using Databases to Solve Problems



     4. (2 pts.) Write a query named ColumbusCars to calculate the number of antique cars that
        are registered to Columbus residents. (Hint: This query will return only one value, not a
        list of data.)
     Query Name: ColumbusCars          Tables Required: ________________________________
     Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:



5. (2 pts.) Your auto club would like to arrange a singles night for its unmarried members.
   Write a query named Unmarried to list the full names and phone numbers of all members
   who do not have a spouse.
   Query Name: Unmarried         Tables Required: ________________________________
   Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:




Using Outer Joins in a Query 2.4                                                  Page 250
                                   Using Databases to Solve Problems


6. (3 pts.) At the annual club picnic, special activities are provided for the members’ children.
   Because of this, each member is charged $10 for each child attending the picnic. (You can
   assume that all the members’ children will be attending.) Write a query or queries to list for
   all members the amount that each member owes for his or her children, the total amount of
   payments that he or she has already made, and the amount that he or she still owes to the
   club. You may use any of the previous queries from this homework. You may not need to
   use all of the design views provided below.

     Query Name: _____________     Tables Required: ________________________________
     Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:




     Query Name: _____________     Tables Required: ________________________________
     Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:


Using Outer Joins in a Query 2.4                                                  Page 251
                                   Using Databases to Solve Problems


     Query Name: _____________     Tables Required: ________________________________
     Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:




     Query Name: _____________     Tables Required: ________________________________
     Foreign Keys: ________________________________ Join Type: ____________________

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional Room for Expressions if needed:




Using Outer Joins in a Query 2.4                                        Page 252

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:8/8/2011
language:English
pages:32