List the part number part description and item class for


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.

Solution Preview :

Prepared by a verified Expert
PL-SQL Programming: List the part number part description and item class for
Reference No:- TGS01242251

Now Priced at $20 (50% Discount)

Recommended (99%)

Rated (4.3/5)