Practical No. # 2
Create the Product_Master table described below:
| Column Name | Datatype | Size | Constraints |
| Product_No | Char | 6 | Primary Key |
| Description | Varchar2 | 15 | Not Null |
| ProfitPercent | Number | 4,2 | Not Null |
| UnitMeasure | Varchar2 | 10 | Not Null |
| QtyOnHand | Number | 8 | Not Null |
| ReOrderLvl | Number | 8 | Not Null |
| SellPrice | Number | 8,2 | Not Null |
| CostPrice | Number | 8,2 | Not Null |
Solution:
CREATE TABLE PRODUCT_MASTER(
PRODUCT_NO CHAR(6) PRIMARY KEY,
DESCRIPTION VARCHAR2(15) NOT NULL,
PROFITPERCENT NUMBER (4,2) NOT NULL,
UNITMEASURE VARCHAR2(10) NOT NULL,
QTYONHAND NUMBER(8) NOT NULL,
REORDERLVL NUMBER(8) NOT NULL,
SELLPRICE NUMBER(8,2) NOT NULL,
COSTPRICE NUMBER(8,2) NOT NULL);
Insert the following data into the table:
| PRODUCT_NO | DESCRIPTION | PROFITPERCENT | UNITMEASURE | QTYONHAND | REORDERLVL | SELLPRICE | COSTPRICE |
| P00001 | 1.44 Floppies | 5 | Price | 100 | 20 | 525 | 500 |
| P00002 | Monitors | 6 | Price | 10 | 3 | 12000 | 11280 |
| P00003 | Mouse | 5 | Price | 20 | 5 | 1050 | 1000 |
| P00004 | 1.22 Floppies | 5 | Price | 100 | 20 | 525 | 500 |
| P00005 | Keyboard | 2 | Price | 10 | 3 | 3150 | 3050 |
| P00006 | 540 HDD | 2.5 | Price | 10 | 3 | 5250 | 5100 |
Perform the following queries on the table:
Q1: List the various products available from the Product_Master table.
Solution:
SELECT DESCRIPTION FROM PRODUCT_MASTER;
Output:
| DESCRIPTION |
| 1.44 Floppies |
| Monitors |
| Mouse |
| 1.22 Floppies |
| Keyboard |
| 540 HDD |
Q2: Find the products with description as 1.44 and 1.22 floppies
Solution:
SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE DESCRIPTION = ‘1.44 Floppies’ OR DESCRIPTION = ‘1.22 Floppies’;
Output:
| DESCRIPTION |
| 1.44 Floppies |
| 1.22 Floppies |
Q3: List the products whose selling price is greater than 2000 and less than or equal to 5000.
Solution:
SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE SELLPRICE > 2000 AND SELLPRICE <= 5000;
Output:
| DESCRIPTION |
| Keyboard |
Q4: List products whose selling price is more than 5000. Calculate a new selling price as, original selling price * 0.15. Rename the new column in the output of the above query as New_Price.
Solution:
SELECT DESCRIPTION, SELLPRICE*0.15 “NEW_PRICE” FROM PRODUCT_MASTER WHERE SELLPRICE > 5000;
Output:
| DESCRIPTION | NEW_PRICE |
| Monitors | 1800 |
| 540 HDD | 787.5 |
Q5: List all the products whose cost price is less than 15000.
Solution:
SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE COSTPRICE <>
Output:
| DESCRIPTION |
| 1.44 Floppies |
| Monitors |
| Mouse |
| 1.22 Floppies |
| Keyboard |
| 540 HDD |
Q6: Calculate square root of price of each product.
Solution:
SELECT SQRT(SELLPRICE), SQRT(COSTPRICE) FROM PRODUCT_MASTER;
Output:
| SQRT(SELLPRICE) | SQRT(COSTPRICE) |
| 22.91287847477920003294023596864004244492 | 22.3606797749978969640917366873127623544 |
| 109.544511501033222691393956560160426791 | 106.207344378814029359871437704620785888 |
| 32.40370349203930115482983718043998328852 | 31.62277660168379331998893544432718533719 |
| 22.91287847477920003294023596864004244492 | 22.3606797749978969640917366873127623544 |
| 56.12486080160912078375623098474823952634 | 55.22680508593630387105456921672158530426 |
| 72.4568837309471928685933207858488586157 | 71.41428428542849997999399811367265278766 |
Q7: List the product in sorted order of their description.
Solution:
SELECT DESCRIPTION FROM PRODUCT_MASTER ORDER BY DESCRIPTION;
Output:
| DESCRIPTION |
| 1.22 Floppies |
| 1.44 Floppies |
| 540 HDD |
| Keyboard |
| Monitors |
| Mouse |
Q8: Calculate the average price of all the products.
Solution:
SELECT AVG(SELLPRICE), AVG(COSTPRICE) FROM PRODUCT_MASTER;
Output:
| AVG(SELLPRICE) | AVG(COSTPRICE) |
| 3750 | 3571.666666666666666666666666666666666667 |
Q9: Determine the minimum and maximum product prices. Rename the output as Min_Price and Max_Price respectively.
Solution:
SELECT MIN(SELLPRICE) “MIN_PRICE”, MAX(SELLPRICE) “MAX_PRICE” FROM PRODUCT_MASTER;
Output:
| MIN_PRICE | MAX_PRICE |
| 525 | 12000 |
Q10: Count the number of products having price less than 15000.
Solution:
SELECT COUNT(*) FROM PRODUCT_MASTER WHERE SELLPRICE <>
Output:
| COUNT(*) |
| 6 |
Q11: List all the products whose QtyOnHand is less than ReOrderLvl.
Solution:
SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE QTYONHAND <>
Output:
no rows selected
