Question
REQUIRED: PLEASE ANSWER QUESTION NO. 7-10 Copy and paste the PL/SQL code on the space provided after each questions. Copy and paste the PARTS table
REQUIRED:
PLEASE ANSWER QUESTION NO. 7-10 Copy and paste the PL/SQL code on the space provided after each questions.
Copy and paste the PARTS table
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);
Table Name: PARTS
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 |
|
|
Create a report displaying all rows and columns.
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 |
1. Create a report by eliminating the duplicate rows for column class and warehouse.
select distinct * from parts group by class, warehouse;
PARTNUM | DESCRIPTION | ONHAND | CLASS | WAREHOUSE | PRICE |
KL62 | DRYER | 12 | AP | 1 | 349 |
DR93 | GAS RANGE | 21 | AP | 2 | 495 |
KT03 | DISHWASHER | 8 | AP | 3 | 595 |
FD21 | STAND MIXER | 22 | HW | 3 | 159 |
KV29 | TREADMILL | 9 | SG | 2 | 1390 |
2. Create a report specifying only the column PRICE, ONHAND and DESCRIPTION.
select PRICE, ONHAND, DESCRIPTION from parts;
PRICE | ONHAND | DESCRIPTION |
2495 | 50 | IRON |
79495 | 45 | HOME GYM |
165 | 32 | MICROWAVE OVEN |
12995 | 21 | CORDLESS DRILL |
495 | 21 | GAS RANGE |
399 | 12 | WASHER |
159 | 22 | STAND MIXER |
349 | 12 | DRYER |
595 | 8 | DISHWASHER |
1390 | 9 | TREADMILL |
3. Create a report that will add 10% increase in PRICE. List only the column DESCRIPTION, CLASS and PRICE.
select DESCRIPTION, CLASS, PRICE*1.10 from parts;
DESCRIPTION | CLASS | PRICE |
IRON | HW | 2744.5 |
HOME GYM | SG | 87444.5 |
MICROWAVE OVEN | AP | 181.5 |
CORDLESS DRILL | HW | 14294.5 |
GAS RANGE | AP | 544.5 |
WASHER | AP | 438.9 |
STAND MIXER | HW | 174.9 |
DRYER | AP | 383.9 |
DISHWASHER | AP | 654.5 |
TREADMILL | SG | 1529.0 |
4. Create a report that will deduct 5 from ONHAND, multiply 5 in WAREHOUSE, after getting the value on both ONHAND and WAREHOUSE add their data: as shown below:
ONHAND - 5 + 5 * WAREHOUSE
Note that you have to force the Oracle to prioritize first the Subtraction over Multiplication. List only the column DESCRIPTION, ONHAND and WAREHOUSE.
select DESCRIPTION, ONHAND-5 AS NEW_ONHAND, WAREHOUSE*5 AS NEW_WAREHOUSE, (ONHAND-5) + (WAREHOUSE*5) as ONHAND_WAREHOUSE from parts;
DESCRIPTION | NEW_ONHAND | NEW_WAREHOUSE | ONHAND_WAREHOUSE |
IRON | 45 | 15 | 60 |
HOME GYM | 40 | 10 | 50 |
MICROWAVE OVEN | 27 | 5 | 32 |
CORDLESS DRILL | 16 | 15 | 31 |
GAS RANGE | 16 | 10 | 26 |
WASHER | 7 | 15 | 22 |
STAND MIXER | 17 | 15 | 32 |
DRYER | 7 | 5 | 12 |
DISHWASHER | 3 | 15 | 18 |
TREADMILL | 4 | 10 | 14 |
5. Create a report that will rename the column DESCRIPTION to TITLE, PARTNUM to ID and ONHAND to STOCK.
ALTER TABLE parts RENAME COLUMN DESCRIPTION to TITLE;
ALTER TABLE parts RENAME COLUMN PARTNUM to ID;
ALTER TABLE parts RENAME COLUMN ONHAND to STOCK;
select * from parts;
ID | TITLE | STOCK | 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 |
7.. Create a report the will merge the column CLASS and PRICE rename the COLUMN as CLASS PRICE.
8. Create a report that will combine the column PARTNUM and DESCRIPTION put a literal character string belongs to in between the two columns then rename the column as NUMBER TITLE. Note put space before and after the character literal string to avoid no spaces in the report.
9. Create a report that will display the unique value for WAREHOUSE rename the column as No. of Available Warehouse.
10. What is the help of SELECT statement? Is there any permanent made in the database once the user uses different SELECT.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started