Om Pawaskar

S22
Roll no: 95

Implementation of basic SQL queries

1. Find names of all clients.

mysql> select client_name from client_master;

+------------------+
| client_name |
+------------------+
| Ivan Bayross |
| Vandana Saitwal |
| Pramada Jaguste |
| Basu Navindgi |
| Ravi Shreedharan |
| Rukmini |
+------------------+
6 rows in set (0.00 sec)

2. print entire Client master table.

mysql> select * from client_master;

+-----------+------------------+----------+----------+--------+-------------+---------+-------------+
| client_no | client_name | address1 | address2 | city | state | pincode | balance_due |
+-----------+------------------+----------+----------+--------+-------------+---------+-------------+
| C00001 | Ivan Bayross | NULL | NULL | Bombay | Maharashtra | 400054 | 15000.00 |
| C00002 | Vandana Saitwal | NULL | NULL | Madras | Tamil Nadu | 780001 | 0.00 |
| C00003 | Pramada Jaguste | NULL | NULL | Bombay | Maharashtra | 400057 | 5000.00 |
| C00004 | Basu Navindgi | NULL | NULL | Bombay | Maharashtra | 400056 | 0.00 |
| C00005 | Ravi Shreedharan | NULL | NULL | Delhi | | 100001 | 2000.00 |
| C00006 | Rukmini | NULL | NULL | Bombay | Maharashtra | 400050 | 15000.00 |
+-----------+------------------+----------+----------+--------+-------------+---------+-------------+
6 rows in set (0.01 sec)

3.List name and city of clients.

mysql> select client_name , city from client_master;

+------------------+--------+
| client_name | city |
+------------------+--------+
| Ivan Bayross | Bombay |
| Vandana Saitwal | Madras |
| Pramada Jaguste | Bombay |
| Basu Navindgi | Bombay |
| Ravi Shreedharan | Delhi |
| Rukmini | Bombay |
+------------------+--------+
6 rows in set (0.00 sec)

4. List various products available from product master.

mysql> select description from product_master;

+---------------+
| description |
+---------------+
| 1.44 Floppies |
| Monitors |
| Mouse |
| HDD |
| 1.22 Floppies |
| Ketboards |
| CD Drive |
| 1.44 Drive |
| 1.22 Drive |
+---------------+
9 rows in set (0.00 sec)

5. Find names of all client having a as second letter.

mysql> select client_name from client_master where client_name like '_a%';

+------------------+
| client_name |
+------------------+
| Vandana Saitwal |
| Basu Navindgi |
| Ravi Shreedharan |
+------------------+
3 rows in set (0.01 sec)

6. Find names of all client having a as second letter in city.

mysql> select client_name,city from client_master where city like '_a%';

+-----------------+--------+
| client_name | city |
+-----------------+--------+
| Vandana Saitwal | Madras |
+-----------------+--------+
1 row in set (0.00 sec)
7. list all the clients who stay in bombay ,delhi or madras.

mysql> select client_name,city from client_master where city='Bombay'or city='delhi'or

city='madras';
+------------------+--------+
| client_name | city |
+------------------+--------+
| Ivan Bayross | Bombay |
| Vandana Saitwal | Madras |
| Pramada Jaguste | Bombay |
| Basu Navindgi | Bombay |
| Ravi Shreedharan | Delhi |
| Rukmini | Bombay |
+------------------+--------+
6 rows in set (0.00 sec)

8.list all the clients who stay in bombay ,delhi or madras using IN operator .

mysql> select client_name,city from client_master where city in ('Bombay','Delhi','Madras');

+------------------+--------+
| client_name | city |
+------------------+--------+
| Ivan Bayross | Bombay |
| Vandana Saitwal | Madras |
| Pramada Jaguste | Bombay |
| Basu Navindgi | Bombay |
| Ravi Shreedharan | Delhi |
| Rukmini | Bombay |
+------------------+--------+
6 rows in set (0.00 sec)

9. list the clients who stay in bombay.

mysql> select client_name,city from client_master where city in ('Bombay');

+-----------------+--------+
| client_name | city |
+-----------------+--------+
| Ivan Bayross | Bombay |
| Pramada Jaguste | Bombay |
| Basu Navindgi | Bombay |
| Rukmini | Bombay |
+-----------------+--------+
4 rows in set (0.00 sec)

10. list the names of client having balance due is greater than 10000.

mysql> select client_name from client_master where balance_due>10000;

+--------------+
| client_name |
+--------------+
| Ivan Bayross |
| Rukmini |
+--------------+
2 rows in set (0.05 sec)

11. list the data of clients where client no is 'C00001'or 'C00002'.

mysql> select * from client_master where client_no='C00001' or client_no='C00002';

+-----------+-----------------+----------+----------+--------+-------------+---------+-------------+
| client_no | client_name | address1 | address2 | city | state | pincode | balance_due |
+-----------+-----------------+----------+----------+--------+-------------+---------+-------------+
| C00001 | Ivan Bayross | NULL | NULL | Bombay | Maharashtra | 400054 | 15000.00 |
| C00002 | Vandana Saitwal | NULL | NULL | Madras | Tamil Nadu | 780001 | 0.00 |
+-----------+-----------------+----------+----------+--------+-------------+---------+-------------+
2 rows in set (0.02 sec)

12. show the product having description '1.44 Drive' or '1.22 Drive'

mysql> select product_no,description from product_master where description='1.44 Drive' or

description='1.22 Drive';
+------------+-------------+
| product_no | description |
+------------+-------------+
| P07975 | 1.44 Drive |
| P08865 | 1.22 Drive |
+------------+-------------+
2 rows in set (0.00 sec)

13. list the data where order is placed in the month of january.

mysql> select * from sales_order where s_order_date like '%-01-%';

+------------+--------------+-----------+-----------+-------------+-----------+-----------+------------+--------------+
| s_order_no | s_order_date | client_no | dely_addr | salesman_no | dely_type | billed_yn |
dely_date | order_status |
+------------+--------------+-----------+-----------+-------------+-----------+-----------+------------+--------------+
| O19001 | 1996-01-12 | C00001 | NULL | S00001 | F | N | 1996-01-20 | IP |
| O19002 | 1996-01-25 | C00002 | NULL | S00002 | P | N | 1996-01-27 | C |
+------------+--------------+-----------+-----------+-------------+-----------+-----------+------------+--------------+
2 rows in set (0.00 sec)

14. show the product having selling price between 2000 , 5000.

mysql> select product_no,description,sell_price from product_master where sell_price

between 2000 and 5000;
+------------+-------------+------------+
| product_no | description | sell_price |
+------------+-------------+------------+
| P07885 | CD Drive | 3150.00 |
+------------+-------------+------------+
1 row in set (0.00 sec)

15. list the products having sell price greater than 1500.

mysql> select product_no,description,sell_price from product_master where sell_price>1500;

+------------+-------------+------------+
| product_no | description | sell_price |
+------------+-------------+------------+
| P03453 | Monitors | 12000.00 |
| P07868 | Keyboards | 3150.00 |
| P07885 | CD Drive | 5250.00 |
| P07965 | HDD | 8400.00 |
+------------+-------------+------------+
4 rows in set (0.00 sec)
16. list the products having sell price greater than 1500 and also find new selling price as
original price multiplied by 15.

mysql> select product_no,description,sell_price,sell_price*15 as original_price from

product_master where sell_price>1500;
+------------+-------------+------------+----------------+
| product_no | description | sell_price | new_sale_price |
+------------+-------------+------------+----------------+
| P03453 | Monitors | 12000.00 | 169200.00 |
| P07868 | Keyboards | 3150.00 | 45750.00 |
| P07885 | CD Drive | 5250.00 | 76500.00 |
| P07965 | HDD | 8400.00 | 120000.00 |
+------------+-------------+------------+----------------+
4 rows in set (0.00 sec)

17. list the products having cost price less than 1500.

mysql> select product_no,description,cost_price from product_master where

cost_price<1500;
+------------+---------------+------------+
| product_no | description | cost_price |
+------------+---------------+------------+
| P00001 | 1.44 Floppies | 500.00 |
| P06734 | Mouse | 1000.00 |
| P07865 | 1.22 Floppies | 500.00 |
| P07975 | 1.44 Drive | 1000.00 |
| P08865 | 1.22 Drive | 1000.00 |
+------------+---------------+------------+
5 rows in set (0.00 sec)

18. sort the products by description in ascending order.

mysql> select description from product_master order by description;

+---------------+
| description |
+---------------+
| 1.22 Drive |
| 1.22 Floppies |
| 1.44 Drive |
| 1.44 Floppies |
| CD Drive |
| HDD |
| Ketboards |
| Monitors |
| Mouse |
+---------------+
9 rows in set (0.00 sec)

19. sort the products by description in descinding order.

mysql> select description from product_master order by description desc;

+---------------+
| description |
+---------------+
| Mouse |
| Monitors |
| Ketboards |
| HDD |
| CD Drive |
| 1.44 Floppies |
| 1.44 Drive |
| 1.22 Floppies |
| 1.22 Drive |
+---------------+
9 rows in set (0.00 sec)

20.display the the square root of price from product master.

mysql> select product_no,description,sqrt(cost_price) as sq_cost_price from product_master;

+------------+---------------+--------------------+
| product_no | description | sq_cost_price |
+------------+---------------+--------------------+
| P00001 | 1.44 Floppies | 22.360679774997898 |
| P03453 | Monitors | 106.20734437881403 |
| P06734 | Mouse | 31.622776601683793 |
| P07065 | HDD | 89.44271909999159 |
| P07865 | 1.22 Floppies | 22.360679774997898 |
| P07868 | Ketboards | 71.4142842854285 |
| P07885 | CD Drive | 55.226805085936306 |
| P07975 | 1.44 Drive | 31.622776601683793 |
| P08865 | 1.22 Drive | 31.622776601683793 |
+------------+---------------+--------------------+
9 rows in set (0.01 sec)
21. divide the cost of a product hdd by the difference between its price and 100.

mysql> select cost_price/(cost_price-100) as difference from product_master where

description='HDD';
+------------+
| difference |
+------------+
| 1.012658 |
+------------+
1 row in set (0.00 sec)

22.list the data of client where state is not maharashtra.

mysql> select client_name,city,state from client_master where not(state='Maharashtra');

+------------------+--------+------------+
| client_name | city | state |
+------------------+--------+------------+
| Vandana Saitwal | Madras | Tamil Nadu |
| Ravi Shreedharan | Delhi | |
+------------------+--------+------------+
2 rows in set (0.00 sec)

23.show all the products having m as their 1st letter.

mysql> select description,sell_price,product_no from product_master where description like

'm%';
+-------------+------------+------------+
| description | sell_price | product_no |
+-------------+------------+------------+
| Monitors | 12000.00 | P03453 |
| Mouse | 1050.00 | P06734 |
+-------------+------------+------------+
2 rows in set (0.00 sec)
NESTED QUERIES

24. Find the product_no and description of non-moving products (eg. products not being sold).

select product_no,description
-> from product_master
-> where product_no not in(
-> select product_no from sales_order_details);

+------------+---------------+
| product_no | description |
+------------+---------------+
| P07865 | 1.22 Floppies |
| P08865 | 1.22 Drive |
+------------+---------------+

25. Find the customers name ,city and pincode for the client who has placed order no
'O19001'.

select client_name, city,address1,address2,pincode

-> from Client_master
-> where
-> client_no in
-> ( select client_no from sales_order
-> where s_order_no ='O19001');

+-------------+--------+---------+
| name | city | pincode |
+-------------+--------+---------+
| IvanBayross | Bombay | 400054 |
+-------------+--------+---------+
1 row in set (0.00 sec)