PROBLEM STATEMENT: DESIGN AT LEAST 10 SQL QUERIES FOR
SUTABLE DATABASE APPLICATION USING SQL DML STATEMENTS:ALL
TYPES OF JOIN,SUB-QUERY AND VIEW.
A) Exercise on Joins and Correlations :
********************************************************************************
1) Find out the products. Which have been sold to 'Ivan Bayross'.
mysql> SELECT
NAME,
PRODUCT_MASTER.PRODUCT_NO,
DESCRIPTION,
SALES_ORDER.ORDER_NO
FROM
CLIENT_MASTER,
PRODUCT_MASTER,
SALES_ORDER,
SALES_ORDER_DETAILS
WHERE
CLIENT_MASTER.NAME ='Ivan Bayross';
AND
CLIENT_MASTER.CLIENT_NO = SALES_ORDER.CLIENT_NO
AND
SALES_ORDER.ORDER_NO = SALES_ORDER_DETAILS.ORDER_NO
AND
SALES_ORDER_DETAILS.PRODUCT_NO = PRODUCT_MASTER.PRODUCT_NO;
+--------------+------------+--------------+----------+
| NAME | PRODUCT_NO | DESCRIPTION | ORDER_NO |
+--------------+------------+--------------+----------+
| IVAN BAYROSS | P0001 | T-SHIRTS | O19001 |
| IVAN BAYROSS | P07965 | DEIM SHIRTS | O19001 |
| IVAN BAYROSS | P07885 | PULL OVERS | O19001 |
| IVAN BAYROSS | P0345 | SHIRTS | O19003 |
| IVAN BAYROSS | P06734 | COTTON JEANS | O19003 |
+--------------+------------+--------------+----------+
5 rows in set (0.00 sec)
________________________________________________________________________________
2) Find out the products and their quantities that will have to be delivered in the
current
month. (ANS NOT SURE)
mysql> SELECT DELY_DATE,
SALES_ORDER_DETAILS.PRODUCT_NO,QTY_ORDERED,DESCRIPTION FROM
SALES_ORDER,SALES_ORDER_DETAILS,PRODUCT_MASTER WHERE SALES_ORDER.ORDER_NO
= SALES_ORDER_DETAILS.ORDER_NO AND SALES_ORDER_DETAILS.PRODUCT_NO =
PRODUCT_MASTER.PRODUCT_NO AND DATE_FORMAT(SALES_ORDER.DELY_DATE,'MM') =
DATE_FORMAT(SYSDATE(),'MM');
+------------+------------+-------------+--------------+
| DELY_DATE | PRODUCT_NO | QTY_ORDERED | DESCRIPTION |
+------------+------------+-------------+--------------+
| 2002-07-20 | P0001 | 4 | T-SHIRTS |
| 2002-07-20 | P07965 | 2 | DEIM SHIRTS |
| 2002-07-20 | P07885 | 2 | PULL OVERS |
| 2002-06-27 | P0001 | 10 | T-SHIRTS |
| 2002-04-07 | P0345 | 2 | SHIRTS |
| 2002-04-07 | P06734 | 1 | COTTON JEANS |
| 2002-07-26 | P0001 | 10 | T-SHIRTS |
| 2002-07-26 | P07975 | 5 | LYCRA TOPS |
| 2002-02-20 | P07868 | 3 | TROUSERS |
| 2002-02-20 | P07885 | 3 | PULL OVERS |
| 2002-02-20 | P0001 | 10 | T-SHIRTS |
| 2002-02-20 | P0345 | 4 | SHIRTS |
| 2002-05-22 | P07965 | 1 | DEIM SHIRTS |
| 2002-05-22 | P07975 | 1 | LYCRA TOPS |
+------------+------------+-------------+--------------+
14 rows in set (0.00 sec)
________________________________________________________________________________
3) List the ProductNo and description of constantly sold (i.e. rapidly moving)
products.
mysql> SELECT PRODUCT_NO FROM SALES_ORDER_DETAILS WHERE PRODUCT_RATE = (SELECT
MAX(PRODUCT_RATE) FROM SALES_ORDER_DETAILS );
+------------+
| PRODUCT_NO |
+------------+
| P06734 |
+------------+
1 row in set (0.00 sec)
___________________________________________________________________________________
________
4) Find the names of the clients who have purchased 'Trousers'.
mysql> 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 ='TROUSERS';
+-----------+---------------+
| CLIENT_NO | NAME |
+-----------+---------------+
| C00003 | CHHAYA BANKER |
+-----------+---------------+
1 row in set (0.00 sec)
___________________________________________________________________________________
___
__________
5) List the products and orders from customers who have orderd less than 5 units of
'Pull
Overs'
mysql> select PRODUCT_MASTER.PRODUCT_NO,ORDER_NO from
PRODUCT_MASTER,SALES_ORDER_DETAILS where DESCRIPTION = 'PULL OVERS' and
PRODUCT_MASTER.PRODUCT_NO = SALES_ORDER_DETAILS.PRODUCT_NO and QTY_ORDERED <
5;
+------------+----------+
| PRODUCT_NO | ORDER_NO |
+------------+----------+
| P07885 | O19001 |
| P07885 | O46865 |
+------------+----------+
2 rows in set (0.00 sec)
___________________________________________________________________________________
___
__________
6) Find the products and their quantities for the orders placed by �Ivan Bayross�
and
�Mamta Muzumdar�.
mysql> SELECT SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(QTY_ORDERED) UNITS_ORDERED
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;
+------------+--------------+---------------+
| PRODUCT_NO | DESCRIPTION | UNITS_ORDERED |
+------------+--------------+---------------+
| P0001 | T-SHIRTS | 14 |
| P0345 | SHIRTS | 2 |
| P06734 | COTTON JEANS | 1 |
| P07885 | PULL OVERS | 2 |
| P07965 | DEIM SHIRTS | 2 |
+------------+--------------+---------------+
5 rows in set (0.00 sec)
________________________________________________________________________
_______
7) Find the products and their quantities for the orders placed by Client_No
�C00001�
and �C00002�.
mysql> SELECT SO.CLIENT_NO, SOD.PRODUCT_NO, PM.DESCRIPTION, SUM(QTY_ORDERED)
UNITS_ORDERED 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 =
