home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Shareware Overload
/
ShartewareOverload.cdr
/
database
/
ssql1b.zip
/
APPENDIX.SHR
next >
Wrap
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