Required: Copy and paste the PL/SQL code on the space provided after each questi
ID: 3851047 • Letter: R
Question
Required: 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 that will merge the column DESCRIPTION and PRICE put a literal character string of = “ with a price of ” in between the two columns. Limit the rows returned by getting only the partnum that starts with letter ‘K’.
2. Create a report that will display the distinct value for CLASS and WAREHOUSE limit the rows by getting only the parts under WAREHOUSE 3.
3. Create a report by listing the column DESCRIPTION, WAREHOUSE and ONHAND. Get only the warehouse value equal to 3 and the onhand value is equal to 21.
4. Create a report by listing the column PARTNO, DESCRIPTION and PRICE. Get only those Partnum that either starts with letter ‘K’ or price that is less than 500. Sort your report by price in ascending order.
5. Create a report by listing the column PARTNO, DESCRIPTION and WAREHOUSE. Get only that description that does not ends with ‘ER’. Note that you have to merge the said three columns, rename the merge column as “Parts Record”. Below is the sample output for column.
Parts Record
is the part number of IRON which belong to warehouse 3
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
Explanation / Answer
Hi,
Please find below the solutions based on Oracle PL/SQL syntax-
Ans 1- select DESCRIPTION||'with a price of '||PRICE FROM PARTS WHERE SUBSTRING(PARTNUM,1,1)='K';
Ans 2 - select DISTINCT CLASS, WAREHOUSE FROM PARTS WHERE WAREHOUSE=3;
Ans 3- SELECT DESCRIPTION, WAREHOUSE , ONHAND FROM PART WHERE AND WAREHOUSE=3;
Ans4- SELECT PARTNO, DESCRIPTION, PRICE FROM PARTS WHERE SUBSTRING(PARTNUM,1,1)='K' OR PRICE<500 ORDER BY PRICE;
Ans 5- select PARTNO||'is a part number of '||DESCRIPTION||'which belongs to warehouse '||WAREHOUSE AS 'Parts Record' from PARTS WHERE SUBSTRING(DESCRIPTION,LENGTH(DESCRIPTON)-2,2)<>'ER';
Regards,
Vinay singh
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.