Power-Programmierung/CD1.mdf/sql/appendix.shr

Text File | 1988-02-03 | 11KB | 307 lines

         APPENDIX A

         The following queries will give you an idea of the power of SQL.
         Registered users not only receive the answers, they also receive
         detailed explanations of how to arrive at the answers.  In some
         cases there is more than one solution.

         Most of the answers require knowledge of joining tables and
         subqueries.

         The information is divided into six tables.  For those of you
         not familiar with data normalization, it may seem like it would
         be better to combine the tables.  That would produce more
         problems than it would solve.

         SALES TABLE
         date = Date of sale - YYMMDD format
         bc   = Branch Code        - corresponds to code in the Branch table
         cc   = Customer Code      - corresponds to code in the Cust table
         sn   = Salesperson number - corresponds to enum in the Emp table
         pc   = Product Code       - corresponds to code in the Prod table
         qty  = Quantity purchased
         Primary key is date,bc,cc,sn,pc
         We are assuming that a customer will not purchase the same
         product from the same branch from the same salesperson on the
         same day!

         BRANCH TABLE
         code   = Branch code
         st     = Branch state
         MgrNum = Employee number of manager - corresponds to enum in the
                  Emp table
         Primary key is code

         EMP TABLE
         enum = Employee number
         name = Employee name
         mgrn = Employee number of employee's manager - corresponds to
                enum
         Primary key is enum

         PROD TABLE
         code = Product code
         desc = Product description
         Primary key is code

         MANU TABLE (Manufacturing information)
         code    = Product code - corresponds to code in the Prod table
         mst     = State where the product is manufactured
         defects = percent of defects last time the product was
                   manufactured
         Primary key is code

         CUSTOMER TABLE
         code   = Customer code
         name   = Customer name
         st     = Customer state
         rating = Customer rating
         Primary key is code

                                       APP-1



         The tables should already be on your disk with the following
         data:

         SALES                        BRANCH
         date   bc cc sn pc qty       code st MgrNum
         ------ -- -- -- -- ---       ---- -- ------
         870401 b1 c3 11 AB  23       b1   AZ     10
         870401 b1 c3 11 DD  34       b2   CA     20
         870408 b1 c4 12 AB   2       b3   OR     30
         870420 b1 c5 13 AC 450       b4   WA     40
         870421 b1 c6 11 FA  87       b5   NV     50
         870422 b1 c6 12 EC  12       b6   AZ     60
         870402 b2 c5 24 EC  55
         870408 b2 c5 24 DZ  81       EMP
         870410 b2 c2 27 DA   3       enum name             mgrn
         870412 b2 c2 27 AB  41       ---- ---------------- ----
         870415 b3 c2 33 AC  33          1 Harvey Bigcheese
         870418 b3 c1 35 DZ 125         10 Mary Hizzle         1
         870420 b3 c1 35 DA 875         11 Marty Nogglater    12
         870425 b3 c5 32 AB 674         12 Gary Hoppertalk    10
         870430 b3 c5 32 AC 675         13 Kelly Unlucky      12
         870401 b4 c1 41 EA 947         14 Bobo Butters       12
         870408 b4 c1 41 EC 452         20 Nancy Umlat         1
         870415 b4 c1 41 FA  32         22 Larry Moe          20
         870402 b5 c6 52 AB 342         24 Curley Moe         22
         870408 b5 c4 56 AC 845         27 Quarkly Poslak     20
         870420 b5 c4 56 DD 654         30 Vernal Equinox      1
         870401 b6 c3 63 DA  45         32 Otto Otter         35
         870408 b6 c3 63 DZ  73         33 Yarquark Moon      35
                                        35 Joe Snow           30
                                        37 Iflab Wasmal       35
         PROD                           40 Telan Rowaq         1
         code desc                      41 Robby Marslat      43
         ---- --------------            43 Mongus Wombat      40
         AB   Megawamp                  50 Kanga Rat           1
         AC   Gigasnarf                 52 Elark Kaboom       50
         DD   Decawidgit                54 Agarth Boavlam     50
         DA   Technowidgit              56 Finster Funankle   52
         DZ   Electrowidgit             60 Xero Xanadu         1
         EA   Nanomouse                 62 Duncan Donut       60
         EC   RGBMouse                  63 Duncan Bagel       60
         FA   Dynamic Disk              64 Ticia Splatt       60


         MANU                        CUSTOMER
         code mst defects            code name            st rating
         ---- --- -------            ---- --------------- -- ------
         AB   WA        3            c1   Compugorp       WA     20
         AC   AZ        0            c2   Techoharps      OR     15
         DD   WA        3            c3   Organomice      AZ     34
         DA   OR       10            c4   QuarkCo         AZ     10
         DZ   AZ                     c5   Marswarp        CA     30
         EA   AZ       12            c6   Multicrud       NV      2
         EC   ID        2
         FA   NV        3
         AB   ID        5

                                       APP-2




         1.  What is the total number of different products sold to
         customer c1?

         2.  Get product numbers for all products supplied to all
         customers in California.

         3.  Get customer numbers for customers who have purchased from
         any branch which sells products manufactured in Arizona.

         4.  Get the employee name and corresponding manager name for
         every employee who has a manager.

         5.  Get the employee name, corresponding manager name, and the
         manager's manager name for all instances where the three exist.
         For example, the following would be a row from the result:
         Duncan Donut  Xero Xanadu  Harvey Bigcheese
         This is because Duncan works for Xero and Xero works for
         Harvey.

         6.  For sales from 870410 to 870420, print the date, customer
         name and salesperson name, product name and qty for each sale.

         7. Get customer names and product names for customers who have
         purchased items not manufactured in their own state. Sort it by
         product description within customer name.

         8. Get all columns from the sales table for the customer with
         the highest rating.

         9. Get branch code, customer names and product names for who
         purchase products manufactured in their own state from a branch
         in their own state.

         10. Get branch numbers for branches who sell to both c1 and c2.

         11. Get product codes for products sold to any customer in
         California.

         13.  Get product numbers and corresponding customer names for
         products manufactured in the same state as a customer.

         14.  Get customer numbers for customers sold at least one
         product from a branch not in the same state.

         15.  Get branch codes for branches which sell at least one
         product sold by at least one branch which sells product DD.

         16.  Get all pairs of state values such that the branch in the
         first state sells to a customer in a second state.

         17.  Get all [State, product code, State] where the branch in
         the first state sells the product to the customer in the second
         state.  Exclude rows where the states are the same.

                                       APP-3




         18.  What is the average quantity for a purchase of a product that
         ends in 'A'?

         19.  Did Yarquark Moon's manager sell anything to customer c1?

         20.  What are the names of managers who actually sold something?

         21.  In order to determine the significance of the customer
         rating, what is the average quantity for each rating, sorted by
         rating.  Just list the rating and the average quantity.

         22.  How many purchases of Megawamps were sold by a branch in AZ
         and what was the average quantity sold?

         23.  What are the names of managers whose salespeople have sold
         products to Organomice?

         24.  Get the manager name and the total quantity sold by that
         manager's immediate subordinates.

         25.  Get the defects and the average quantity sold for each level
         of defects.  Only include products for which we track defects.

         26.  Who of Xero Xanadu's subordinates sold Technowidgits and what
         were the quantities sold?





























                                     APP-4



         APPENDIX B

         Answers to the questions in the tutorial:

         1. List all the data in the table.
         select *
         from cust;

         code name            st rating
         ---- --------------- -- ------
         c1   Compugorp       WA     20
         c2   Techoharps      OR
         c3   Organomice      AZ     36

         3 rows selected

         2. List the customer name and rating for all customers in AZ.
         select name, rating
         from cust
         where st = 'AZ';

         name            rating
         --------------- ------
         Organomice          36

         1 row selected

         3. List all the data for ratings from 10 to 35.
         select *
         from cust
         where rating between 10 and 35;

         code name            st rating
         ---- --------------- -- ------
         c1   Compugorp       WA     20

         1 row selected

         4. List the average rating.
         select avg(rating)
         from cust;

         AVG(rating)
         -----------
                  28

         3 rows selected

         5. How many customers have a rating of less than 25?
         select count(code)
         from cust
         where rating < 25;

         CNT(code)
         ---------
               1

         1 row selected

                                     APP-5