Reference no: EM13932922

Premiere Products Database (figure 1.2)

Table: REP

REP_NUM	LAST_NAME	FIRST_NAME	STREET	CITY	STATE	ZIP	COMMISSION	RATE
20	Kaiser	Valerie	624 Randall	Grove	FL	33321	$20,542.50	0.05
35	Hull	Richard	532 Jackson	Sheldon	FL	33553	$39,216.00	0.07
65	Perez	Juan	1626 Taylor	Fillmore	FL	33336	$23,487.00	0.05

 

 

 

 

 

Table:  CUSTOMER

CUSTOMER_NUM	CUSTOMER_NAME	STREET	CITY	STATE	ZIP	BALANCE	CREDIT_LIMIT	REP_NUM
148	Al's Appliance and Sport	2837 Greenway	Fillmore	FL	33336	$6,550.00	$7,500.00	20
282	Brookings Direct	3827 Devon	Grove	FL	33321	$431.50	$10,000.00	35
356	Ferguson's	382 Wildwood	Northfield	FL	33146	$5,785.00	$7,500.00	65
408	The Everything Shop	1828 Raven	Crystal	FL	33503	$5,285.25	$5,000,00	35
462	Bargains Galore	3829 Central	Grove	FL	33321	$3,412.00	$10,000.00	65
524	Kline's	838 Ridgeland	Fillmore	FL	33336	$12,762.00	$15,000.00	20
608	Johnson's Department Store	372 Oxford	Sheldon	FL	33553	$2,106.00	$10,000,00	65
687	Lee's Sport and Appliance	282 Evergreen	Altonville	FL	32543	$2,851.00	$5,000.00	35
725	Deerfield's Four Seasons	282 Columbia	Sheldon	FL	33553	$248.00	$7,500.00	35
842	All Season	28 Lakeview	Grove	FL	33321	$8,221.00	$7,500.00	20

Table: ORDERS

ORDER_NUM	ORDER_DATE	CUSTOMER_NUM
21608	10/20/2003	148
21610	10/20/2003	356
21613	10/21/2003	408
21614	10/21/2003	282
21617	10/23/2003	608
21619	10/23/2003	148
21623	10/23/2003	608

Table: ORDER_LINE

ORDER_NUM	PART_NUM	NUM_ORDERED	QUOTED_PRICE
21608	AT94	11	$21.95
21610	DR93	1	$495.00
21610	DW11	1	$399.99
21613	KL62	4	$329.95
21614	KT03	2	$595.00
21617	BV06	2	$794.95
21617	CD52	4	$150.00
21619	DR93	1	$495.00
21623	KV29	2	$1,290.00

Table: PART

PART_NUM	DESCRIPTION	ON_HAND	CLASS	WAREHOUSE	PRICE
AT94	Iron	50	HW	3	$24.95
BV06	Home Gym	45	SG	2	$794.95
CD52	Microwave Oven	32	AP	1	$165.00
DL71	Cordless Drill	21	HW	3	$129.95
DR93	Gas Range	8	AP	2	$495.00
DW11	Washer	12	AP	3	$399.99
FD21	Stand Mixer	22	HW	3	$159.95
KL62	Dryer	12	AP	1	$349.95
KT03	Dishwasher	8	AP	3	$595.00
KV29	Treadmill	9	SG	2	$1,390.00

Use SQL, and the Premiere Products database (see Figure 1.2) to complete the following exercises.

1.  For each order placed on October 20, 2003, list the order number along with the number and name of the customer that

placed the order.

2.  Use the IN operator to find the number and name of each customer that placed an order on October 20, 2003.

3.  Find the number and name of each customer that did not place an order on October 20, 2003. 

4.  For each order, list the order number, order date, part number, part description, and item class for each part that makes up

the order but order the rows by item class and then by order number.

5.  Find the rep number, last name, and first name of each sales rep who represents at least one customer with a credit limit of

$10,000.  List each sales rep only once in the results.  Do not use a subquery.

6.  List the part number, part description, and item class for each pair of parts that are in the same item class. 

7.  List the order number and order date for each order that contains an order line for a Gas Range.

8.  List the order number and order date for each order that was placed by Ferguline for a Gas Range. 

9.  List the part number, part description, unit price, and item class for each part that has a unit price greater that the unit price

of every part in item class AP.  Use either the ALL or ANY operator in your query. (Hint: Make sure you select the correct

operator.)

10.  For each part, list the part number, description, units on hand, order number, and number of units ordered.  All parts

should be included in the results.  For those parts that are currently not on order, the order number and number of units

ordered should be left blank.  Order the results by part number.