Copy and paste the PL/SQL code on the space provided after each questions. Table Name: PARTS CREATE
Question:
Copy and paste the PL/SQL code on the space provided after each questions.
Table Name: PARTS
CREATE TABLE PARTS(
PARTNUM CHAR(4) PRIMARY KEY,
DESCRIPTION VARCHAR(20),
ONHAND NUMBER(6),
CLASS CHAR(5),
WAREHOUSE NUMBER(6),
PRICE NUMBER(6));
INSERT INTO PARTS VALUES('AT94', 'IRON',50,'HW',3,2495);
INSERT INTO PARTS VALUES('BVO6','HOME GYM' ,45,'SG',2,79495);
INSERT INTO PARTS VALUES('CD52','MICROWAVE OVEN',32,'AP',1,165);
INSERT INTO PARTS VALUES('DL71','CORDLESS DRILL',21,'HW',3,12995);
INSERT INTO PARTS VALUES('DR93','GAS RANGE',21,'AP',2,495);
INSERT INTO PARTS VALUES('DW11','WASHER',12,'AP',3,399);
INSERT INTO PARTS VALUES('FD21','STAND MIXER',22,'HW',3,159);
INSERT INTO PARTS VALUES('KL62','DRYER',12,'AP',1,349);
INSERT INTO PARTS VALUES('KT03','DISHWASHER',8,'AP',3,595);
INSERT INTO PARTS VALUES('KV29','TREADMILL',9,'SG',2,1390);
PARTNUM | DESCRIPTION | ONHAND | CLASS | WAREHOUSE | PRICE |
AT94 | IRON | 50 | HW | 3 | 2495 |
BVO6 | HOME GYM | 45 | SG | 2 | 79495 |
CD52 | MICROWAVE OVEN | 32 | AP | 1 | 165 |
DL71 | CORDLESS DRILL | 21 | HW | 3 | 12995 |
DR93 | GAS RANGE | 21 | AP | 2 | 495 |
DW11 | WASHER | 12 | AP | 3 | 399 |
FD21 | STAND MIXER | 22 | HW | 3 | 159 |
KL62 | DRYER | 12 | AP | 1 | 349 |
KT03 | DISHWASHER | 8 | AP | 3 | 595 |
KV29 | TREADMILL | 9 | SG | 2 | 1390 |
PARTS structure
COLUMN NAME | DATA TYPE/SIZE | KEY | NULL |
PARTNUM | CHAR – 4 | PRIMARY | NOT NULL |
DESCRIPTION | VARCHAR – 20 | | NOT NULL |
ONHAND | NUMBER – 6 | | |
CLASS | CHAR – 5 | | |
WAREHOUSE | NUMBER – 6 | | |
PRICE | NUMBER – 6 | | |
1. Create a report listing only the column DESCRIPTION, PARTNUM, CLASS, and PRICE of all PART whose CLASS is equal to HW.
2. Create a report listing only the column PARTNUM, DESCRIPTION and PRICE of all PARTS where price is less than 500. Sort the PRICE in ascending order.
3. Create a report listing only the column DESCRIPTION, ONHAND and WAREHOUSE of all PARTS where ONHAND is greater than or equal to 21.
4. Create a report listing only the column DESCRIPTION, CLASS and PRICE of all PARTS where class is not equal to AP.
5. Create a report listing only the column CLASS, DESCRITPION and PRICE of all PARTS where price range is between 200 to 500. Sort the Price in descending order.
Auditing and Assurance services an integrated approach
ISBN: 978-0133125689
15th edition
Authors: Alvin a. arens, Randal j. elder, Mark s. Beasley