Assignment 4

Assignment 4
1 Display the salesman details whose name starts with A.

2 Display max, min and average salary in salesman tables.
3 Display distinct salary from salesman table
4 Display the OID for maximum qamount.
5 Find out the name of salesman having alphabet N as the second last letter in their name.
6 List all the info of the customers whose city contains null values.
7 Display the name of the city of customers whose name ends with alphabet N.
8 Display the salesman name starting with A and end with V.
9 Display customer and salesman name in upper and lower case both.
10 Display cid and sid b/w two dates.
11 Display city which name starts with A in customer table.
12 Find the number of customers, salesman and orders from respective tables.
13 Find the second highest salary from salesman table.
mysql> use pchard;

Database changed
mysql> select * from salesman where sname like 'A%';
+-----+----------+-----------+--------+------------+----------+---------+
| sid | sname | city | salary | product | tgttoget | country |
+-----+----------+-----------+--------+------------+----------+---------+
| 1 | Anubhav | Delhi | 20000 | GPU | 5000 | India |
| 2 | Aayush | Goa | 15000 | Monitor | 20000 | India |
| 3 | Akash | Delhi | 15000 | CPU | 20000 | India |
| 4 | Abhijeet | Ghaziabad | 10000 | Hard Drive | 25000 | India |
| 5 | Anand | Kolkata | 18000 | RAM | 50000 | India |
+-----+----------+-----------+--------+------------+----------+---------+
5 rows in set (0.30 sec)
mysql> select max(salary), min(salary), avg(salary) from salesman;

+-------------+-------------+-------------+
| max(salary) | min(salary) | avg(salary) |
+-------------+-------------+-------------+
| 20000 | 10000 | 15600.0000 |
+-------------+-------------+-------------+
1 row in set (0.01 sec)
mysql> select distinct(salary) from salesman;

+--------+
| salary |
+--------+
| 20000 |
| 15000 |
| 10000 |
| 18000 |
+--------+
mysql> select * from orders;

+-----+------+------+------------+------+------------+-------+
| oid | cid | sid | product | qty | odate | qamnt |
+-----+------+------+------------+------+------------+-------+
| 1| 1| 1 | GPU | 500 | 2016-08-25 | 51000 |
| 2| 5| 2 | Monitor | 5 | 2016-08-25 | 48000 |
| 3| 4| 4 | Hard Drive | 10 | 2016-08-25 | 30000 |
| 4| 2| 3 | CPU | 5 | 2016-08-25 | 57000 |
| 5| 3| 5 | RAM | 50 | 2016-08-25 | 20000 |
+-----+------+------+------------+------+------------+-------+
mysql> select oid from orders where qamnt=(select max(qamnt) from orders);
+-----+
| oid |
+-----+
| 4|
+-----+
mysql> select sname from salesman where sname like '%n_';

+-------+
| sname |
+-------+
| Anand |
+-------+
mysql> select sname from salesman where sname like '%N_';

+-------+
| sname |
+-------+
| Anand |
+-------+
mysql> select * from customers where city is null;

Empty set (0.00 sec)
mysql> select city from customers where cname like '%n';

+--------+
| city |
+--------+
| Gnoida |
+--------+
mysql> select sname from salesman where sname like 'a%v';

+---------+
| sname |
+---------+
| Anubhav |
+---------+
mysql> select lcase(sname), ucase(sname) from salesman;'

+--------------+--------------+
| lcase(sname) | ucase(sname) |
+--------------+--------------+
| anubhav | ANUBHAV |
| aayush | AAYUSH |
| akash | AKASH |
| abhijeet | ABHIJEET |
| anand | ANAND |
+--------------+--------------+
mysql> select lcase(cname), ucase(cname) from customers;

+--------------+--------------+
| lcase(cname) | ucase(cname) |
+--------------+--------------+
| naman | NAMAN |
| apoorva | APOORVA |
| abhishek | ABHISHEK |
| vedant | VEDANT |
| dewanshi | DEWANSHI |
+--------------+--------------+

+-----+------+------+------------+------+------------+-------+
+-----+------+------+------------+------+------------+-------+
| 1| 1| 1 | GPU | 500 | 2016-08-25 | 51000 |
| 2| 5| 2 | Monitor | 5 | 2016-08-25 | 48000 |
| 3| 4| 4 | Hard Drive | 10 | 2016-08-25 | 30000 |
| 4| 2| 3 | CPU | 5 | 2016-08-25 | 57000 |
| 5| 3| 5 | RAM | 50 | 2016-08-25 | 20000 |
+-----+------+------+------------+------+------------+-------+
-> set odate = '2016-11-12' where oid=4;

Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update orders



+-----+------+------+------------+------+------------+-------+
+-----+------+------+------------+------+------------+-------+
| 1| 1| 1 | GPU | 500 | 2016-08-25 | 51000 |
| 2| 5| 2 | Monitor | 5 | 2016-09-12 | 48000 |
| 3| 4| 4 | Hard Drive | 10 | 2016-10-23 | 30000 |
| 4| 2| 3 | CPU | 5 | 2016-11-12 | 57000 |
| 5| 3| 5 | RAM | 50 | 2017-01-01 | 20000 |
+-----+------+------+------------+------+------------+-------+
mysql> select cid,sid from orders where odate between '2016-08-25' and '2016-10-23';
+------+------+
| cid | sid |
+------+------+
| 1| 1|
| 5| 2|
| 4| 4|
+------+------+
mysql> select city from customers where city like 'k%';

+---------+
| city |
+---------+
| Kolkata |
+---------+
mysql> select count(cid) from customers;

+------------+
| count(cid) |
+------------+
| 5|
+------------+
mysql> select count(sid) from salesman;

+------------+
| count(sid) |
+------------+
| 5|
+------------+
mysql> select count(oid) from orders;
+------------+
| count(oid) |
+------------+
| 5|
+------------+
mysql> select * from salesman;

+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
mysql> select salary from salesman

-> order by salary desc limit 1 offset 1;
+--------+
| salary |
+--------+
| 18000 |
+--------+
-> order by salary desc limit 2;
+--------+
| salary |
+--------+
| 20000 |
| 18000 |
+--------+

+--------+
| salary |
+--------+
| 18000 |
| 15000 |
+--------+

+--------+
| salary |
+--------+
| 15000 |
+--------+
Assignment 5
1. Find the total qty of each product

2. Find the sum of all pin codes for each class
3. Find the total salary of salesman which belong to same city
4. Display avg salary for each city salesman
5. Display target to get for each product
6. Display salesman info in desc order
7. Display total qty for each date
8. Display info in asc and desc dates of order placed
9. Display total qty for each date where qty is greater than 5 and display it desc order of qty
10. Find the salary of salesman whose customer lives in Gnoida city
11. Display the sid, cid for salesman and customer who live in same city
12. Display class of a customer with its associate salesman sid
13. Find the name of the customer whose salesman has highest salary
14. Display customer info of salesman 1 and 2
mysql> use pchard;

Database changed
+-----+------+------+------------+------+------------+-------+
+-----+------+------+------------+------+------------+-------+
| 1 | 1 | 1 | GPU | 500 | 2016-08-25 | 51000 |
| 2 | 5 | 2 | Monitor | 5 | 2016-09-12 | 48000 |
| 3 | 4 | 4 | Hard Drive | 10 | 2016-10-23 | 30000 |
| 4 | 2 | 3 | CPU | 5 | 2016-11-12 | 57000 |
| 5 | 3 | 5 | RAM | 50 | 2017-01-01 | 20000 |
+-----+------+------+------------+------+------------+-------+
mysql> select product, qty from orders group by product;

+------------+------+
| product | qty |
+------------+------+
| CPU | 5|
| GPU | 500 |
| Hard Drive | 10 |
| Monitor | 5 |
| RAM | 50 |
+------------+------+
mysql> select * from customers;
+-----+----------+----------+-------------+---------+------------+-------+---------+
| cid | cname | city | state | pincode | product | class | country |
+-----+----------+----------+-------------+---------+------------+-------+---------+
| 1 | Naman | Gnoida | UP | 201012 | GPU |B | India |
| 2 | Apoorva | GNoida | UP | 201308 | CPU |A | India |
| 3 | Abhishek | Kolkata | West Bengal | 730000 | RAM |A | India |
| 4 | Vedant | Patna | Bihar | 800001 | Hard Drive | C | India |
| 5 | Dewanshi | Agartala | Tripura | 102398 | Monitor | A | India |
+-----+----------+----------+-------------+---------+------------+-------+---------+
mysql> select class, sum(pincode) from customers group by class;

+-------+--------------+
| class | sum(pincode) |
+-------+--------------+
|A | 1033706 |
|B | 201012 |
|C | 800001 |
+-------+--------------+
mysql> select city, sum(salary) from salesman group by city;

+-----------+-------------+
| city | sum(salary) |
+-----------+-------------+
| Delhi | 35000 |
| Ghaziabad | 10000 |
| Goa | 15000 |
| Kolkata | 18000 |
+-----------+-------------+
mysql> select city, avg(salary) from salesman group by city;

+-----------+-------------+
| city | avg(salary) |
+-----------+-------------+
| Delhi | 17500.0000 |
| Ghaziabad | 10000.0000 |
| Goa | 15000.0000 |
| Kolkata | 18000.0000 |
+-----------+-------------+
mysql> select product, tgttoget from salesman group by product;

+------------+----------+
| product | tgttoget |
+------------+----------+
| CPU | 20000 |
| GPU | 5000 |
| Hard Drive | 25000 |
| Monitor | 20000 |
| RAM | 50000 |
+------------+----------+
mysql> select * from salesman order by sname,sid desc;

+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
mysql> select * from salesman order by sname,sid ;

+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
mysql> select * from salesman order by sname desc;

+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
mysql> select odate, sum(qty) from orders group by odate;

+------------+----------+
| odate | sum(qty) |
+------------+----------+
| 2016-08-25 | 500 |
| 2016-09-12 | 5|
| 2016-10-23 | 10 |
| 2016-11-12 | 5|
| 2017-01-01 | 50 |
+------------+----------+
mysql> select * from orders order by odate desc;

+-----+------+------+------------+------+------------+-------+
+-----+------+------+------------+------+------------+-------+
| 5 | 3 | 5 | RAM | 50 | 2017-01-01 | 20000 |
| 4 | 2 | 3 | CPU | 5 | 2016-11-12 | 57000 |
| 3 | 4 | 4 | Hard Drive | 10 | 2016-10-23 | 30000 |
| 2 | 5 | 2 | Monitor | 5 | 2016-09-12 | 48000 |
| 1 | 1 | 1 | GPU | 500 | 2016-08-25 | 51000 |
+-----+------+------+------------+------+------------+-------+
s
mysql> select odate, sum(qty) from orders group by odate having max(qty)>5 order by qty desc;
+------------+----------+
| odate | sum(qty) |
+------------+----------+
| 2016-08-25 | 500 |
| 2017-01-01 | 50 |
| 2016-10-23 | 10 |
+------------+----------+
mysql> select sname,salary from salesman,customers,orders where customers.cid=orders.cid and

salesman.sid=orders.sid and customers.city="Gnoida";
+---------+--------+
| sname | salary |
+---------+--------+
| Anubhav | 20000 |
| Akash | 15000 |
+---------+--------+
mysql> select salesman.sid,customers.cid from salesman,customers,orders where

customers.cid=orders.cid and salesman.sid=orders.sid and customers.city=salesman.city;
+-----+-----+
| sid | cid |
+-----+-----+
| 5| 3|
+-----+-----+
mysql> select salesman.sid,class from salesman,customers,orders where customers.cid=orders.cid and

salesman.sid=orders.sid;
+-----+-------+
| sid | class |
+-----+-------+
| 1|B |
| 2|A |
| 4|C |
| 3|A |
| 5|A |
+-----+-------+
mysql> select cname from salesman,customers,orders where customers.cid=orders.cid and

salesman.sid=orders.sid and salary=(select max(salary) from salesman);
+-------+
| cname |
+-------+
| Naman |
+-------+
mysql> select customers.* from salesman,customers,orders where customers.cid=orders.cid and

salesman.sid=orders.sid and salesman.sid in (1,2);
+-----+----------+----------+---------+---------+---------+-------+---------+
+-----+----------+----------+---------+---------+---------+-------+---------+
+-----+----------+----------+---------+---------+---------+-------+---------+
Assignment 6
1. Display the customer name and salesman name having highest orders amount in orders
table.
mysql> select cname, sname from customers, salesman, orders where customers.cid = orders.cid
and salesman.sid = orders.sid and qamnt=(select max(qamnt) from orders);
+---------+-------+
| cname | sname |
+---------+-------+
| Apoorva | Akash |
+---------+-------+
2. Display the salesman city who observed class A.
mysql> select salesman.city from salesman, customers, orders where salesman.sid=orders.sid

and customers.cid = orders.cid and customers.class='A'
-> ;
+---------+
| city |
+---------+
| Goa |
| Delhi |
| Kolkata |
+---------+
3. Display the odate salesman and customer city and salesman id in descending order
mysql> select odate, salesman.city, customers.city, salesman.sid from salesman, customers,

orders where salesman.sid=orders.sid and customers.cid=orders.cid order by salesman.sid desc;
+------------+-----------+----------+-----+
| odate | city | city | sid |
+------------+-----------+----------+-----+
| 2017-01-01 | Kolkata | Kolkata | 5 |
| 2016-10-23 | Ghaziabad | Patna | 4|
| 2016-11-12 | Delhi | GNoida | 3 |
| 2016-09-12 | Goa | Agartala | 2 |
| 2016-08-25 | Delhi | Gnoida | 1 |
+------------+-----------+----------+-----+
4. Display customer name whose salesman lives in Delhi.
mysql> select cname from salesman, customers, orders where salesman.sid=orders.sid and
customers.cid=orders.cid and salesman.city= 'Delhi';
+---------+
| cname |
+---------+
| Naman |
| Apoorva |
+---------+
5. Display salesman names whose customer doesnt live in GNoida.
mysql> select sname from salesman, customers, orders where salesman.sid=orders.sid and
customers.cid=orders.cid and customers.city!= 'GNoida';
+----------+
| sname |
+----------+
| Aayush |
| Abhijeet |
| Anand |
+----------+
6. Show the salaries of all employees with 10% increment with present salary
mysql> select sname, salary*1.1 as NewSalary from salesman;

+----------+-----------+
| sname | NewSalary |
+----------+-----------+
| Anubhav | 22000.0 |
| Aayush | 16500.0 |
| Akash | 16500.0 |
| Abhijeet | 11000.0 |
| Anand | 19800.0 |
+----------+-----------+
7. Retrieve the no. of customers whose salesman having salary between 9000 to 16000
mysql> select count(customers.cid) from customers, salesman, orders where salesman.sid =

orders.sid and customers.cid=orders.cid and salesman.salary between 9000 and 16000;
+----------------------+
| count(customers.cid) |
+----------------------+
| 3|
+----------------------+
8. Retrieve the distinct no. Of city of customer whose salesman having salary between
20000 to 30000
mysql> select count(distinct(customers.city)) as DistinctCity from customers, salesman, orders

where salesman.sid = orders.sid and customers.cid=orders.cid and salesman.salary between 9000
and 16000;
+--------------+
| DistinctCity |
+--------------+
| 3|
+--------------+
9. Retrieve the no. of salesman having more than one customers

+-----+----------+----------+-------------+---------+------------+-------+---------+
+-----+----------+----------+-------------+---------+------------+-------+---------+
| 5 | Dewanshi | Agartala | Tripura | 102398 | Monitor |A | India |
+-----+----------+----------+-------------+---------+------------+-------+---------+

+-----+----------+----------+-------------+---------+------------+-------+---------+
+-----+----------+----------+-------------+---------+------------+-------+---------+
| 5 | Dewanshi | Agartala | Tripura | 102398 | Monitor |A | India |
| 6 | Ankit | Mumbai | Maharashtra | 998845 | Mouse |B | India |
| 7 | Shubham | Panjim | Goa | 997845 | Mouse |B | India |
| 8 | Shivam | Patna | Bihar | 997845 | Keyboard | C | India |
+-----+----------+----------+-------------+---------+------------+-------+---------+

+-----+------+------+------------+------+------------+-------+
+-----+------+------+------------+------+------------+-------+
| 1| 1| 1 | GPU | 500 | 2016-08-25 | 51000 |
| 2| 5| 2 | Monitor | 5 | 2016-09-12 | 48000 |
| 3| 4| 4 | Hard Drive | 10 | 2016-10-23 | 30000 |
| 4| 2| 3 | CPU | 5 | 2016-11-12 | 57000 |
| 5| 3| 5 | RAM | 50 | 2017-01-01 | 20000 |
| 6| 6| 6 | Mouse | 50 | 2017-02-01 | 25000 |
| 7| 7| 6 | Mouse | 10 | 2017-02-04 | 5000 |
+-----+------+------+------------+------+------------+-------+
mysql> select count(distinct salesman.sid) from salesman, customers, orders where

customers.cid=orders.cid and salesman.sid=orders.sid group by orders.sid having
count(orders.sid)>1;
+------------------------------+
| count(distinct salesman.sid) |
+------------------------------+
| 1|
+------------------------------+
10. Retrieve the name of customers who doesnt have any salesman
mysql> select customers.cname from salesman, customers, orders where

customers.cid=orders.cid and salesman.sid=orders.sid group by orders.sid having
count(orders.sid)=0;
Assignment 7
1. Retrieve the names of salesmen who dont have any customer.
mysql> select sname from salesman where salesman.sid not in ( select salesman.sid from
salesman, customers, orders where salesman.sid=orders.sid and customers.cid= orders.cid);
2. Retrieve the name of each salesmen who has a customer with the same name and both
live in same city
mysql> select sname from salesman, customers, orders where salesman.sid=orders.sid and
customers.cid=orders.cid and salesman.sname=customers.cname and salesman.city=
customers.city;
3. List the no of salesmen having at least one customers
mysql> select count(distinct salesman.SID) from salesman, customers, order1 where

salesman.SID=order1.Sid and customers.Cid=order1.Cid and salesman.SID in(select Sid from
order1 where order1.Sid>=1);
+------------------------------+
| count(distinct salesman.SID) |
+------------------------------+
| 4|
+------------------------------+
4. For each product retrieve the product name, the no of salesmen who sold that product and
their avg salary
mysql> select order1.Product, count(distinct order1.Sid), avg(salesman.Salary) f

rom salesman, customers, order1 where customers.Cid=order1.Cid and salesman.SID=
order1.Sid group by order1.Product;
+-------------+----------------------------+----------------------+
| Product | count(distinct order1.Sid) | avg(salesman.Salary) |
+-------------+----------------------------+----------------------+
| RO Purifier | 4| 16400.0000 |
+-------------+----------------------------+----------------------+
5. For each product retrieve the product name and the no of salesmen from Delhi city who
sold that product
mysql> select order1.Product, count(distinct order1.Sid),salesman.City from cust

omers, order1, salesman where customers.Cid=order1.Cid and salesman.SID=order1.S
id group by order1.product having salesman.City='Noida';
+-------------+----------------------------+-------+
| Product | count(distinct order1.Sid) | City |
+-------------+----------------------------+-------+
| RO Purifier | 4 | Noida |
+-------------+----------------------------+-------+
Assignment 8
1. Create a view of salesman having salary more than 15000.
mysql> create view saleview as select * from salesman where salary>15000;

Query OK, 0 rows affected (0.00 sec)
mysql> select * from saleview;

+-----+---------+---------+--------+----------+----------+---------+
+-----+---------+---------+--------+----------+----------+---------+
| 6 | Ankit | Mumbai | 18000 | Mouse | 50000 | India |
| 7 | Anchal | Goa | 18000 | Keyboard | 50000 | India |
+-----+---------+---------+--------+----------+----------+---------+
2. Create a view of salesman who sold monitor to customers
mysql> create view saleview1 as select * from salesman where product='Monitor';

mysql> select * from saleview1;

+-----+--------+------+--------+---------+----------+---------+
+-----+--------+------+--------+---------+----------+---------+
+-----+--------+------+--------+---------+----------+---------+
3. Create a view of orders having same qamnt.
mysql> create view ordview as select * from orders where qamnt in(select qamnt from orders
group by qamnt having count(*)>1);
mysql> select * from ordview;

+-----+------+------+---------+------+------------+-------+
+-----+------+------+---------+------+------------+-------+
| 1| 1| 1 | GPU | 500 | 2016-08-25 | 48000 |
| 2| 5| 2 | Monitor | 5 | 2016-09-12 | 48000 |
| 5| 3| 5 | RAM | 50 | 2017-01-01 | 25000 |
| 6| 6| 6 | Mouse | 50 | 2017-02-01 | 25000 |
+-----+------+------+---------+------+------------+-------+
Assignment 9
1. Create a view of salesman having salary more than 15000.
mysql> create view saleview as select * from salesman where salary>15000;

mysql> select * from saleview;

+-----+---------+---------+--------+----------+----------+---------+
+-----+---------+---------+--------+----------+----------+---------+
+-----+---------+---------+--------+----------+----------+---------+
2. Create a view of salesman who sold monitor to customers
mysql> create view saleview1 as select * from salesman where product='Monitor';

mysql> select * from saleview1;

+-----+--------+------+--------+---------+----------+---------+
+-----+--------+------+--------+---------+----------+---------+
+-----+--------+------+--------+---------+----------+---------+
3. Create a view of orders having same qamnt.
mysql> create view ordview as select * from orders where qamnt in(select qamnt from orders
group by qamnt having count(*)>1);
mysql> select * from ordview;

+-----+------+------+---------+------+------------+-------+
+-----+------+------+---------+------+------------+-------+
| 1| 1| 1 | GPU | 500 | 2016-08-25 | 48000 |
| 2| 5| 2 | Monitor | 5 | 2016-09-12 | 48000 |
| 5| 3| 5 | RAM | 50 | 2017-01-01 | 25000 |
| 6| 6| 6 | Mouse | 50 | 2017-02-01 | 25000 |
+-----+------+------+---------+------+------------+-------+
4. Create a view of cities having same no of salesman and customers.
mysql> create view cityview as select city, count(city) from customers group by city having
count(city) in (select count(city) from salesman group by city) and city in (select city from
salesman group by city);
mysql> select * from cityview;

+---------+-------------+
| city | count(city) |
+---------+-------------+
| Kolkata | 1|
| Mumbai | 1|
+---------+-------------+
5. Create a view of customers having more than 2 customers in a class.
mysql> create view view1 as select * from customers where class in (select class from customers
group by class having count(class)>2);
mysql> select * from view1;
+-----+----------+----------+-------------+---------+---------+-------+---------+
+-----+----------+----------+-------------+---------+---------+-------+---------+
| 6 | Ankit | Mumbai | Maharashtra | 998845 | Mouse | B | India |
| 7 | Shubham | Panjim | Goa | 997845 | Mouse | B | India |
+-----+----------+----------+-------------+---------+---------+-------+---------+
6. Create a view to find the name of customers whose salesman lived in same city.
mysql> create view view2 as select customers.* from customers,salesman,orders where

customers.cid=orders.cid and salesman.sid=orders.sid and salesman.city=customers.city;
mysql> select * from view2;

+-----+----------+---------+-------------+---------+---------+-------+---------+
+-----+----------+---------+-------------+---------+---------+-------+---------+
| 6 | Ankit | Mumbai | Maharashtra | 998845 | Mouse | B | India |
+-----+----------+---------+-------------+---------+---------+-------+---------+
Assignment 10
1. Create procedure to increase 10% salary of salesman who are having more than one
customers.
mysql> create procedure p2() select sname, 1.1*salary as new_salary from salesman, customers,
orders where salesman.sid=orders.sid and customers.cid=orders.cid group by orders.sid having
count(orders.sid)>1;
mysql> call p2();

+-------+------------+
| sname | new_salary |
+-------+------------+
| Ankit | 19800.0 |
+-------+------------+
2. Create procedures to find the name of salesman who has ordered maximum order
q_amnt.
mysql> create procedure p3() select sname from salesman,customers,orders where

salesman.sid=orders.sid and customers.cid=orders.cid and qamnt=(select max(qamnt) from
orders);
mysql> call p3();

+-------+
| sname |
+-------+
| Akash |
+-------+
3. Create procedure to find CID of customers of class B having more than one salesmen.
mysql> create procedure p7() select customers.cid from salesman, customers, orders where
salesman.sid=orders.sid and customers.cid=orders.cid and customers.class='B' group by
orders.cid having count(orders.cid)>1;
mysql> call p7();

+-----+
| cid |
+-----+
| 6|
+-----+
4. Create procedure to find the number of customers who ordered same number of orders in
order table.
5. Create a procedure to find the number of orders in a given range of date.
mysql> create procedure p5() select count(orders.oid) from orders, customers, salesman where
orders.cid=customers.cid and orders.sid=salesman.sid and orders.odate between "2016-09-12"
and "2017-02-01";
mysql> call p5();

+-------------------+
| count(orders.oid) |
+-------------------+
| 5|
+-------------------+

Assignment 11
Insert Triggers example
mysql> create table table1( A_N int(10), Amount int(10));

mysql> create trigger t1 before insert on table1 for each row set @Amount = @Amount +
New.Amount;
mysql> set @Amount=5;

mysql> insert into table1 values (10,200), (10,150), (20,200);

Records: 3 Duplicates: 0 Warnings: 0
mysql> select @Amount as total;

+-------+
| total |
+-------+
| 555 |
+-------+
mysql>
mysql> drop trigger t1;

mysql> create trigger t1 before insert on table1 for each row set New.Amount =
10*New.Amount;
mysql> insert into table1 values (10,200), (10,150), (20,200);

mysql> select * from table1;

+------+--------+
| A_N | Amount |
+------+--------+
| 10 | 200 |
| 10 | 150 |
| 20 | 200 |
| 10 | 200 |
| 10 | 150 |
| 20 | 200 |
| 10 | 2000 |
| 10 | 1500 |
| 20 | 2000 |
+------+--------+
mysql> create table table2( name char(20), salary int(5));

mysql> create trigger t1 before insert on table1 for each row set New.name = reverse(new.name);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the
same action time and event for one table'
mysql> create trigger t1 before insert on table1 for each row set New.name = reverse(new.name);
ERROR 1054 (42S22): Unknown column 'name' in 'NEW'
mysql> create trigger t1 before insert on table2 for each row set new.name = reverse(new.name);
mysql> insert into table2 values('Raman' , 200),('Sakshi',5000);

mysql> select * from table2;

+--------+--------+
| name | salary |
+--------+--------+
| namaR | 200 |
| ihskaS | 5000 |
+--------+--------+
Update trigger Examples
mysql> create table t3 (id int(10), tax varchar(10), salary varchar(10));


mysql> create trigger t1 before update on t3 for each row set new.salary = new.salary*10;
mysql> insert into t3 values(2,'20','100'),(1,'15','300'),(3,'10','250');

mysql> update t3 set tax=30 where id=1;

mysql> select * from t3;

+------+------+--------+
| id | tax | salary |
+------+------+--------+
| 2 | 20 | 100 |
| 1 | 30 | 3000 |
| 3 | 10 | 250 |
+------+------+--------+
Triggers on Salesman
mysql> create trigger t1 before insert on salesman for each row set
@tgttoget=@tgttoget+new.tgttoget;
mysql> set @tgttoget=5;

mysql> insert into salesman values (8,'Dom','Delhi',15000,'GPU',20000,'India'),
(9,'Brian','NY',40000,'laptop',40000,'USA');
mysql> select @tgttoget as total;

+-------+
| total |
+-------+
| 60005 |
+-------+

mysql> create trigger t1 before update on salesman for each row set new.salary=new.salary*10;

+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
| 8 | Dom | Delhi | 15000 | GPU | 20000 | India |
| 9 | Brian | NY | 40000 | laptop | 40000 | USA |
+-----+----------+-----------+--------+------------+----------+---------+
mysql> update salesman set product='Monitor' where sid=8;


+-----+----------+-----------+--------+------------+----------+---------+
+-----+----------+-----------+--------+------------+----------+---------+
| 8 | Dom | Delhi | 150000 | Monitor | 20000 | India |
| 9 | Brian | NY | 40000 | laptop | 40000 | USA |
+-----+----------+-----------+--------+------------+----------+---------+

Assignment 4

Uploaded by

Copyright:

Available Formats

Assignment 4

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assignment 4

Uploaded by

Copyright:

Available Formats

Assignment 4

1 Display the salesman details whose name starts with A.

mysql> use pchard;

mysql> select max(salary), min(salary), avg(salary) from salesman;

mysql> select distinct(salary) from salesman;

mysql> select * from orders;

mysql> select sname from salesman where sname like '%n_';

mysql> select sname from salesman where sname like '%N_';

mysql> select * from customers where city is null;

mysql> select city from customers where cname like '%n';

mysql> select sname from salesman where sname like 'a%v';

mysql> select lcase(sname), ucase(sname) from salesman;'

mysql> select lcase(cname), ucase(cname) from customers;

mysql> select * from orders;

-> set odate = '2016-11-12' where oid=4;

mysql> update orders

mysql> update orders

mysql> select * from orders;

mysql> select city from customers where city like 'k%';

mysql> select count(cid) from customers;

mysql> select count(sid) from salesman;

mysql> select * from salesman;

mysql> select salary from salesman

mysql> select salary from salesman

mysql> select salary from salesman

1. Find the total qty of each product

mysql> use pchard;

mysql> select product, qty from orders group by product;

mysql> select class, sum(pincode) from customers group by class;

mysql> select city, sum(salary) from salesman group by city;

mysql> select city, avg(salary) from salesman group by city;

mysql> select product, tgttoget from salesman group by product;

mysql> select * from salesman order by sname,sid desc;

mysql> select * from salesman order by sname,sid ;

mysql> select * from salesman order by sname desc;

mysql> select odate, sum(qty) from orders group by odate;

mysql> select * from orders order by odate desc;

mysql> select sname,salary from salesman,customers,orders where customers.cid=orders.cid and

mysql> select salesman.sid,customers.cid from salesman,customers,orders where

mysql> select salesman.sid,class from salesman,customers,orders where customers.cid=orders.cid and

mysql> select cname from salesman,customers,orders where customers.cid=orders.cid and

mysql> select customers.* from salesman,customers,orders where customers.cid=orders.cid and

2. Display the salesman city who observed class A.

mysql> select salesman.city from salesman, customers, orders where salesman.sid=orders.sid

mysql> select odate, salesman.city, customers.city, salesman.sid from salesman, customers,

4. Display customer name whose salesman lives in Delhi.

5. Display salesman names whose customer doesnt live in GNoida.

mysql> select sname, salary*1.1 as NewSalary from salesman;

mysql> select count(customers.cid) from customers, salesman, orders where salesman.sid =

mysql> select count(distinct(customers.city)) as DistinctCity from customers, salesman, orders

9. Retrieve the no. of salesman having more than one customers

mysql> select * from customers;

mysql> select * from customers;

mysql> select * from orders;

mysql> select count(distinct salesman.sid) from salesman, customers, orders where

mysql> select customers.cname from salesman, customers, orders where

1. Retrieve the names of salesmen who dont have any customer.

3. List the no of salesmen having at least one customers