Practical No. # 3
Create the Salesman_Master table described below:
| Column Name | Datatype | Size | Constraints |
| Salesman_No | Char | 6 | Primary Key |
| SalesmanName | Varchar2 | 20 | Not Null |
| Address1 | Varchar2 | 30 | Not Null |
| Address2 | Varchar2 | 30 | |
| City | Varchar2 | 20 | |
| PinCode | Number | 6 | |
| State | Varchar2 | 20 | |
| SalAmt | Number | 8,2 | Not Null |
| TgtToGet | Number | 6,2 | Not Null |
| YtdSales | Number | 6,2 | Not Null |
| Remarks | Varchar2 | 60 | |
Solution:
CREATE TABLE SALESMAN_MASTER(
SALESMAN_NO CHAR(6) PRIMARY KEY,
SALESMANNAME VARCHAR2(20) NOT NULL,
ADDRESS1 VARCHAR2(30) NOT NULL,
ADDRESS2 VARCHAR2(30),
CITY VARCHAR2(20),
PINCODE NUMBER(6),
STATE VARCHAR2(20),
SALAMT NUMBER(8,2) NOT NULL,
TGTTOGET NUMBER(6,2) NOT NULL,
YTDSALES NUMBER(6,2) NOT NULL,
REMARKS VARCHAR2(60));
Insert the following data into the table:
| SALESMAN_NO | SALESMANNAME | ADDRESS1 | ADDRESS2 | CITY | PINCODE | STATE | SALAMT | TGTTOGET | YTDSALES | REMARKS |
| S00001 | Aman | A/14 | Worli | Mumbai | 400002 | Maharashtra | 3000 | 100 | 50 | Good |
| S00002 | Omkar | 65 | Nariman | Mumbai | 400001 | Maharashtra | 3000 | 200 | 100 | Good |
| S00003 | Raj | P-7 | Bandra | Mumbai | 400032 | Maharashtra | 3000 | 200 | 100 | Good |
| S00004 | Ashish | A/5 | Juhu | Mumbai | 400044 | Maharashtra | 3500 | 200 | 150 | Good |
Create the Sales_Order table described below:
| Column Name | Datatype | Size | Default | Constraints |
| Order_No | Char | 6 | | Primary Key |
| Client_No | Char | 6 | | Foreign Key References Client_No of Client_Master Table |
| OrderDate | Date | | | Not Null |
| Salesman_No | Char | 6 | | Foreign Key References Salesman_No of Salesman_Master Table |
| DelivType | Char | 1 | F | Delivery: part(P)/full(F) |
| BillYN | Char | 1 | | |
| DelivDate | Date | | | Cannot be less than OrderDate |
| OrderStatus | Varchar2 | 10 | | Values(‘In process’, ‘Fulfilled’, ‘Backorder’, ‘Cancelled’) |
Solution:
CREATE TABLE SALES_ORDER(
ORDER_NO CHAR(6) PRIMARY KEY,
CLIENT_NO CHAR(6) REFERENCES CLIENT_MASTER,
ORDERDATE DATE,
SALESMAN_NO CHAR(6) REFERENCES SALESMAN_MASTER,
DELIVTYPE CHAR(1) DEFAULT ‘F’,
BILLYN CHAR(1),
DELIVDATE DATE,
ORDERSTATUS VARCHAR2(10),
CONSTRAINT CK_DELIVTYPE CHECK(DELIVTYPE IN(‘P’,’F’)),
CONSTRAINT CK_DELIVDATE CHECK(DELIVDATE > ORDERDATE),
CONSTRAINT CK_ORDERSTATUS CHECK(ORDERSTATUS IN(‘In Process’,‘Fulfilled’,‘Backorder’,‘Cancelled’)));
Insert the following data into the table:
| ORDER_NO | CLIENT_NO | ORDERDATE | SALESMAN_NO | DELIVTYPE | BILLYN | DELIVDATE | ORDERSTATUS |
| O19001 | C00001 | 12-JUN-02 | S00001 | F | N | 20-JUL-02 | In Process |
| O19002 | C00002 | 02-JUN-02 | S00002 | P | N | 27-JUN-02 | Cancelled |
| O19003 | C00003 | 18-FEB-02 | S00003 | F | Y | 20-FEB-02 | Fulfilled |
| O19004 | C00004 | 03-APR-02 | S00001 | F | Y | 07-APR-02 | Fulfilled |
| O19005 | C00005 | 20-MAY-02 | S00002 | P | N | 22-MAY-02 | Cancelled |
| O19006 | C00006 | 24-MAY-02 | S00004 | F | N | 26-JUL-02 | In Process |
Create the Sales_Order_Details table described below:
| Column Name | Datatype | Size | Constraints |
| Order_No | Char | 6 | Foreign Key References Order_No of Sales_Order Table |
| Product_No | Char | 6 | Foreign Key References Product_No of Product_Master Table |
| QtyOrdered | Number | 8 | |
| QtyDisp | Number | 8 | |
| ProductRate | Number | 10,2 | |
Solution:
CREATE TABLE SALES_ORDER_DETAILS(
ORDER_NO CHAR(6) REFERENCES SALES_ORDER,
PRODUCT_NO CHAR(6) REFERENCES PRODUCT_MASTER,
QTYORDERED NUMBER(8),
QTYDISP NUMBER(8),
PRODUCTRATE NUMBER(10,2));
Insert the following data into the table:
| ORDER_NO | PRODUCT_NO | QTYORDERED | QTYDISP | PRODUCTRATE |
| O19001 | P00001 | 4 | 4 | 525 |
| O19001 | P00006 | 2 | 1 | 5250 |
| O19002 | P00001 | 10 | 0 | 525 |
| O19003 | P00005 | 3 | 3 | 3150 |
| O19003 | P00006 | 3 | 1 | 5250 |
| O19003 | P00001 | 10 | 10 | 525 |
| O19003 | P00002 | 4 | 4 | 1050 |
| O19004 | P00002 | 2 | 2 | 1050 |
| O19004 | P00003 | 1 | 1 | 12000 |
| O19006 | P00001 | 10 | 5 | 525 |
Perform the following queries on these tables:
Q1: List all the information from the Sales_Order table for orders placed in the month of June.
Solution:
SELECT * FROM SALES_ORDER WHERE TO_CHAR(ORDERDATE,‘MON’) = ‘JUN’;
Output:
| ORDER_NO | CLIENT_NO | ORDERDATE | SALESMAN_NO | DELIVTYPE | BILLYN | DELIVDATE | ORDERSTATUS |
| O19001 | C00001 | 12-JUN-02 | S00001 | F | N | 20-JUL-02 | In Process |
| O19002 | C00002 | 02-JUN-02 | S00002 | P | N | 27-JUN-02 | Cancelled |
Q2: Count the total number of orders.
Solution:
SELECT COUNT(*) “TOTAL ORDERS” FROM SALES_ORDER;
Output:
| TOTAL ORDERS |
| 6 |
Q3: List the order number and day on which clients placed their order.
Solution:
SELECT ORDER_NO, TO_CHAR(ORDERDATE,‘DAY’) “ORDER DAY” FROM SALES_ORDER;
Output:
| ORDER_NO | ORDER DAY |
| O19001 | WEDNESDAY |
| O19002 | SUNDAY |
| O19003 | MONDAY |
| O19004 | WEDNESDAY |
| O19005 | MONDAY |
| O19006 | FRIDAY |
Q4: List the month (in alphabets) and date when the orders must be delivered.
Solution:
SELECT ORDER_NO, TO_CHAR(DELIVDATE,‘MONTH’) “MONTH”, DELIVDATE FROM SALES_ORDER;
Output:
| ORDER_NO | MONTH | DELIVDATE |
| O19001 | JULY | 20-JUL-02 |
| O19002 | JUNE | 27-JUN-02 |
| O19003 | FEBRUARY | 20-FEB-02 |
| O19004 | APRIL | 07-APR-02 |
| O19005 | MAY | 22-MAY-02 |
| O19006 | JULY | 26-JUL-02 |
Q5: List the order date in the format ‘DD-MONTH-YY’.
Solution:
SELECT TO_CHAR(ORDERDATE,‘DD-MONTH-YY’) “ORDERDATE” FROM SALES_ORDER;
Output:
| ORDERDATE |
| 12-JUNE -02 |
| 02-JUNE -02 |
| 18-FEBRUARY -02 |
| 03-APRIL -02 |
| 20-MAY -02 |
| 24-MAY -02 |
Q6: List the date, 15 days after today’s date.
Solution:
SELECT SYSDATE + 15 FROM DUAL;
Output:
| SYSDATE+15 |
| 07-SEP-09 |
Q7: Print the description and total qty sold for each product.
Solution:
SELECT DESCRIPTION, SUM(QTYDISP) FROM PRODUCT_MASTER PM, SALES_ORDER_DETAILS SOD WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO GROUP BY DESCRIPTION;
Output:
| DESCRIPTION | SUM(QTYDISP) |
| 1.44 Floppies | 19 |
| 540 HDD | 2 |
| Mouse | 1 |
| Keyboard | 3 |
| Monitors | 6 |
Q8: Find the value of each product sold.
Solution:
SELECT SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(SOD.QTYDISP * SOD.PRODUCTRATE) SALESPERPRODUCT FROM SALES_ORDER_DETAILS SOD, PRODUCT_MASTER PM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO GROUP BY PM.PRODUCT_NO, SOD.DESCRIPTION;
Output:
| PRODUCT_NO | DESCRIPTION | SALESPERPRODUCT |
| P00002 | Monitors | 6300 |
| P00001 | 1.44 Floppies | 9975 |
| P00003 | Mouse | 12000 |
| P00005 | Keyboard | 9450 |
| P00006 | 540 HDD | 10500 |
Q9: Calculate the average quantity sold for each client that has a maximum order value of 15000.00.
Solution:
SELECT CM.CLIENT_NO, AVG(SOD.QTYDISP) AVGSALES FROM SALES_ORDER_DETAILS SOD, CLIENT_MASTER CM WHERE CM.CLIEN_NO = SO.CLIENT_NO AND SO.ORDER_NO = SOD.ORDER_NO GROUP BY CM.CLIENT_NO HAVING MAX(SOD.QTYORDERED * SOD.PRODUCTRATE) > 15000;
Output:
| CLIENT_NO | AVGSALES |
| C00003 | 4.5 |
Q10: Find out the total of all the billed orders for the month of June.
Solution:
SELECT SO.ORDER_NO, SO.ORDERDATE, SUM(SOD.QTYORDERED * SOD.PRODUCTRATE) ORDERBILLED FROM SALES_ORDER SO, SALES_ORDER_DETAILS SOD WHERE SOD.ORDER_NO = SO.ORDER_NO AND SO.BILLYN = ‘Y’ AND TO_CHAR(ORDERDATE,‘MON’) = ‘JUN’ GROUP BY SO.ORDER_NO, SO.ORDERDATE;
Output:
no rows selected
Q11: Find out the products, which have been sold to ‘Ivan Bayross’.
Solution:
SELECT SOD.PRODUCT_NO, PM.DESCRIPTION FROM SALES_ORDER_DETAILS SOD, PRODUCT_MASTER PM, SALES_ORDER SO, CLIENT_MASTER CM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO AND SO.ORDER_NO = SOD.ORDER_NO AND CM.CLIENT_NO = SO.CLIENT_NO AND CM.NAME = ‘Ivan Bayross’;
Output:
| PRODUCT_NO | DESCRIPTION |
| P00001 | 1.44 Floppies |
| P00006 | 540 HDD |
Q12: Find out the products and their quantities that will have to be delivered in the current month.
Solution:
SELECT SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(SOD.QTYORDERED) FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO AND SO.ORDER_NO = SOD.ORDER_NO AND TO_CHAR(DELIVDATE, 'MON-YY') = TO_CHAR(SYSDATE,'MON-YY') GROUP BY SOD.PRODUCT_NO, PM.DESCRIPTION;
Output:
no rows selected
Q13: List the product number and description of constantly sold (i.e. rapidly moving) products.
Solution:
SELECT DISTINCT PRODUCT_MASTER.PRODUCT_NO, DESCRIPTION FROM SALES_ORDER_DETAILS, PRODUCT_MASTER WHERE PRODUCT_MASTER.PRODUCT_NO = SALES_ORDER_DETAILS.PRODUCT_NO;
Output:
| PRODUCT_NO | DESCRIPTION |
| P00002 | Monitors |
| P00001 | 1.44 Floppies |
| P00003 | Mouse |
| P00005 | Keyboard |
| P00006 | 540 HDD |
Q14: Find the names of clients who have purchased ‘1.22 floppies’.
Solution:
SELECT DISTINCT SO.CLIENT_NO, CM.NAME FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM, CLIENT_MASTER CM WHERE PM.PRODUCT_NO = SOD.PRODUCT_NO AND SO.ORDER_NO = SOD.ORDER_NO AND CM.CLIENT_NO = SO.CLIENT_NO AND DESCRIPTION = '1.22 Floppies';
Output:
no rows selected
Q15: List the products and orders from customers who have ordered less than 5 units of ‘keyboards’.
Solution:
SELECT SOD.PRODUCT_NO, SOD.ORDER_NO FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM WHERE SO.ORDER_NO = SOD.ORDER_NO AND PM.PRODUCT_NO = SOD.PRODUCT_NO AND SOD.QTYORDERED < description =" ‘Keyboard’;
Output:
| PRODUCT_NO | ORDER_NO |
| P00005 | O19003 |
Q16: Find the products and their quantities for the orders placed by ‘Ivan Bayross’ and ‘Mamta Muzumdar’.
Solution:
SELECT SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(QTYORDERED) UNITSORDERED FROM SALES_ORDER_DETAILS SOD, SALES_ORDER SO, PRODUCT_MASTER PM, CLIENT_MASTER CM WHERE SO.ORDER_NO = SOD.ORDER_NO AND PM.PRODUCT_NO = SOD.PRODUCT_NO AND CM.CLIENT_NO = SO.CLIENT_NO AND (CM.NAME = 'Ivan Bayross' OR CM.NAME = 'Mamta Muzumdar') GROUP BY SOD.PRODUCT_NO, PM.DESCRIPTION;
Output:
| PRODUCT_NO | DESCRIPTION | UNITSORDERED |
| P00001 | 1.44 Floppies | 4 |
| P00006 | 540 HDD | 2 |
Q17: Find the products and their quantities for the orders placed by Client_No ‘C00001’ and ‘C00002’.
Solution:
SELECT SO.CLIENT_NO, SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(QTYORDERED) UNITSORDERED FROM SALES_ORDER SO, SALES_ORDER_DETAILS SOD, PRODUCT_MASTER PM, CLIENT_MASTER CM WHERE SO.ORDER_NO = SOD.ORDER_NO AND SOD.PRODUCT_NO = PM.PRODUCT_NO AND SO.CLIENT_NO = CM.CLIENT_NO GROUP BY SO.CLIENT_NO, SOD.PRODUCT_NO, PM.DESCRIPTION HAVING SO.CLIENT_NO = 'C00001' OR SO.CLIENT_NO = 'C00002';
Output:
| CLIENT_NO | PRODUCT_NO | DESCRIPTION | UNITSORDERED |
| C00002 | P00001 | 1.44 Floppies | 10 |
| C00001 | P00006 | 540 HDD | 2 |
| C00001 | P00001 | 1.44 Floppies | 4 |
Q18: Find the Product_No and Description of non-moving products.
Solution:
SELECT PRODUCT_NO, DESCRIPTION FROM PRODUCT_MASTER WHERE PRODUCT_NO NOT IN(SELECT PRODUCT_NO FROM SALES_ORDER_DETAILS);
Output:
| PRODUCT_NO | DESCRIPTION |
| P00004 | 1.22 Floppies |
Q19: List the customer name, address, city and pincode for the client who has placed Order_No ‘O19001’.
Solution:
SELECT NAME, ADDRESS, CITY, STATE, PINCODE FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE ORDER_NO = 'O19001');
Output:
| NAME | ADDRESS | CITY | STATE | PINCODE |
| Ivan Bayross | F-111 | Bombay | Maharashtra | 400054 |
Q20: List the client names that have placed orders before the month of May’02.
Solution:
SELECT CLIENT_NO, NAME FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE TO_CHAR(ORDERDATE, 'MON,YY') < 'MAY,02');
Output:
| CLIENT_NO | NAME |
| C00001 | Ivan Bayross |
| C00002 | Vandana |
| C00003 | Praveen |
| C00004 | Basu |
Q21: List if the product ‘mouse’ has been ordered by any client and print the Client_No, name to whom it was sold.
Solution:
SELECT CLIENT_NO, NAME FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE ORDER_NO IN(SELECT ORDER_NO FROM SALES_ORDER_DETAILS WHERE PRODUCT_NO IN(SELECT PRODUCT_NO FROM PRODUCT_MASTER WHERE DESCRIPTION = 'Mouse')));
Output:
| CLIENT_NO | NAME |
| C00004 | Basu |
Q22: List the names of clients who have placed orders worth Rs. 10000 or more.
Solution:
SELECT NAME FROM CLIENT_MASTER WHERE CLIENT_NO IN(SELECT CLIENT_NO FROM SALES_ORDER WHERE ORDER_NO IN(SELECT ORDER_NO FROM SALES_ORDER_DETAILS WHERE(QTYORDERED * PRODUCTRATE) >= 10000));
Output:
| NAME |
| Ivan Bayross |
| Praveen |
| Basu |
