Dbms
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Dbms as PDF for free.
More details
- Words: 1,758
- Pages: 10
SQL> create table salesmen(snum number(4) primary key,sname varchar2(20),city varchar2(20), commission number(6,2)) ; Table created. SQL> create table customers(cnum number(4) primary key,cname varchar2(20),city varchar2(20), rating number(5),snum references salesmen(snum)); Table created. SQL> create table orders(onum number(4) primary key,amount number(7,2),odate date, cnum references customers(cnum),snum references salesmen(snum)); Table created. SQL> insert into salesmen values(&snum,’&sname’,’&city’,&commission); SNUM SNAME CITY COMMISSION ------------------------------------------------------1001 Piyush London 11 % 1002 Sejal Surat 10 % 1004 Mit London 21 % 1007 Rajesh Baroda 14 % 1003 Anand New Delhi 15 %
SQL> insert into customers values(&cnum,'&cname','&city',&rating,&snum); CNUM CNAME CITY RATING SNUM ------------------------------------------------------2001 Harsh London 100 1001 2002 Gita Rome 200 1003 2003 Lalit Surat 200 1002 2004 Govind Bombay 300 1002 2006 Chirag London 100 1001 2008 Chinmay Surat 300 1007 2007 Pratik Rome 100 1004
SQL> insert into orders values(&onum,&amount,’&odate’,&cnum,&snum); ONUM AMOUNT ODATE CNUM SNUM ------------------------------------------------------3001 18.69 10/03/06 2008 1007 3003 767.19 10/03/06 2001 1001 3002 1900.10 10/03/06 2007 1004 3005 5160.45 10/03/06 2003 1002 3006 1098.16 10/03/06 2008 1007 3009 1713.23 10/04/06 2002 1003 3007 75.75 10/04/06 2004 1002 3008 4723.00 10/05/06 2006 1001
3010 3011
1309.95 9891.88
10/06/06 10/06/06
2004 2006
1002 1001
1. Produce the order no, amount and date of all orders. SQL> select onum,amount,odate from orders; ONUM AMOUNT ODATE --------- --------- --------3001 18.69 10-MAR-06 3003 767.19 10-MAR-06 3002 1900.1 10-MAR-06 3005 5160.45 10-MAR-06 3006 1098.06 10-MAR-06 3009 1713.23 10-APR-06 3007 75.75 10-APR-06 3008 4723 10-MAY-06 3010 1309.95 10-JUN-06 3011 9891.88 10-JUN-06 10 rows selected. 2. Give all the information about all the customer with salesman number 1001. SQL> select * from customers where snum = 1001; CNUM --------2001 2006
CNAME -------------------Harsh Chirag
CITY RATING SNUM -------------------- --------- --------London 100 1001 London 100 1001
3. Display the following information in the order of city, sname, snum and commission. SQL> select city,sname,snum,commission from salesmen order by city; CITY -------------------Baroda London London New Delhi Surat
SNAME SNUM COMMISSION -------------------- --------- ---------Rajesh 1007 14 Piyush 1001 11 Mit 1004 21 Anand 1003 15 Sejal 1002 10
4. List of rating followed by the name of each customer in Surat. SQL> select rating,cname from customers where city = 'Surat';
RATING --------200 300
CNAME -------------------Lalit Chinmay
5. List snum of all salesmen with order in order table without any duplicates. SQL> select distinct snum from orders order by snum; SNUM --------1001 1002 1003 1004 1007 6. List of all order for more than Rs 1000. SQL> select * from orders where amount > 1000; ONUM AMOUNT ODATE CNUM SNUM --------- --------- --------- --------- --------3002 1900.1 10-MAR-06 2007 1004 3005 5160.45 10-MAR-06 2003 1002 3006 1098.06 10-MAR-06 2008 1007 3009 1713.23 10-APR-06 2002 1003 3008 4723 10-MAY-06 2006 1001 3010 1309.95 10-JUN-06 2004 1002 3011 9891.88 10-JUN-06 2006 1001 7 rows selected. 7. List of name & cities of all salesmen in London with commission above 10%. SQL> select sname,city from salesmen where city = 'London' and commission > 10; SNAME -------------------Piyush Mit
CITY -------------------London London
8. List all customers excluding those with rating < 10 unless they are located in Rome. SQL> select * from customers where rating >= 10 and city != 'Rome'; CNUM --------2001 2003 2004
CNAME -------------------Harsh Lalit Govind
CITY RATING SNUM -------------------- --------- --------London 100 1001 Surat 200 1002 Bombay 300 1002
2006 Chirag 2008 Chinmay
London Surat
100 300
1001 1007
9. List all orders for more than Rs.100 except the order of snum<100 of 10/03/97. SQL> select * from orders where amount > 100 and snum >=100 and odate ! = '10-mar-97'; ONUM AMOUNT ODATE CNUM SNUM --------- --------- --------- --------- --------3003 767.19 10-MAR-06 2001 1001 3002 1900.1 10-MAR-06 2007 1004 3005 5160.45 10-MAR-06 2003 1002 3006 1098.06 10-MAR-06 2008 1007 3009 1713.23 10-APR-06 2002 1003 3008 4723 10-MAY-06 2006 1001 3010 1309.95 10-JUN-06 2004 1002 3011 9891.88 10-JUN-06 2006 1001 8 rows selected. 10. List all orders taken on October 3rd or 4th or 6th 2006. SQL> select * from orders where odate in( 2 '3-oct-06','4-oct-06','6-oct-06'); no rows selected 11. List all customers whose name begins with
letter 'C'.
SQL> select * from customers where cname like 'C%'; CNUM --------2006 2008
CNAME -------------------Chirag Chinmay
CITY RATING SNUM -------------------- --------- --------London 100 1001 Surat 300 1007
12. List all customers whose name begin with letter ‘A’ to 'G'. SQL> select * from customers where cname between 'A%' and 'G%'; CNUM --------2006 2008
CNAME -------------------Chirag Chinmay
CITY RATING SNUM -------------------- --------- --------London 100 1001 Surat 300 1007
13. List all orders with zero or NULL amount.
SQL> select * from orders where amount = 0 or amount is NULL; no rows selected
14. Find out the largest order of salesman 1002 and 1007. SQL> select max(amount),snum from orders where snum = 1002 or snum = 1007 group by snum; MAX(AMOUNT) SNUM ----------- --------5160.45 1002 1098.06 1007 15. Count all order of October 3 2006. SQL> select count(onum) from orders where odate = '3-oct-2006'; COUNT(ONUM) ----------0 16. Calculate the total amount ordered. SQL> select sum(amount) from orders; SUM(AMOUNT) ----------26658.3 17. Calculate the average amount ordered. SQL> select avg(amount) from orders; AVG(AMOUNT) ----------2665.83 18. Count the no of salesmen currently having orders. SQL> select * from orders where to_char(odate,'dd-mon-yy') = (select to_char(sysdate,'dd-mon-yy') 2 from dual) 3 ; no rows selected 19. Find the largest orders take by each salesman of each date. SQL> select snum,max(amount) from orders group by snum; SNUM MAX(AMOUNT) --------- ----------1001 9891.88 1002 5160.45 1003 1713.23 1004 1900.1 1007 1098.06
20. Find the largest order take by each salesman of 10/03/2006. SQL> select snum,max(amount) from orders where odate = '10-mar-06' group by snum; SNUM MAX(AMOUNT) --------- ----------1001 767.19 1002 5160.45 1004 1900.1 1007 1098.06 21. Count the no of different not NUL cities in the Customer table. SQL> select count(city) from customers where city is not null; COUNT(CITY) ----------7 22. Find out each customer's smallest order. SQL> select min(amount),cnum from orders group by cnum; MIN(AMOUNT) CNUM ----------- --------767.19 2001 1713.23 2002 5160.45 2003 75.75 2004 4723 2006 1900.1 2007 18.69 2008 7 rows selected. 23. Find out first customer in alphabetical order whose name begins with 'G'.
24. Count the no of salesmen registering order for each day. SQL> select count(snum),odate from orders group by odate; COUNT(SNUM) ----------5 2 1 2
ODATE --------10-MAR-06 10-APR-06 10-MAY-06 10-JUN-06
25. List all salesmen with their % of commission.
SQL> select snum,sname,commission from salesmen; SNUM --------1001 1002 1004 1007 1003
SNAME COMMISSION -------------------- ---------Piyush 11 Sejal 10 Mit 21 Rajesh 14 Anand 15
26. Display the no of orders for each day in the descending order of the no of orders in the following format. FOR dd-mon-yy, there are _ Orders. SQL> select odate,count(onum) from orders group by odate order by count(onum) desc; ODATE COUNT(ONUM) --------- ----------10-MAR-06 5 10-APR-06 2 10-JUN-06 2 10-MAY-06 1 27. Assume each salesperson has 12 % commission. Write a query on the order table that will produce the order number, salesman no and the amount of commission for that order.
28. Find the highest rating in each city in the for : For the city (city), the highest rating is : (rating) SQL> select city,max(rating) from customers group by city; CITY MAX(RATING) -------------------- ----------Bombay 300 London 100 Rome 200 Surat 300 29. List all customers in descending order of rating. SQL> select * from customers order by rating desc; CNUM --------2004 2008 2002 2003 2001 2006
CNAME -------------------Govind Chinmay Gita Lalit Harsh Chirag
CITY RATING SNUM -------------------- --------- --------Bombay 300 1002 Surat 300 1007 Rome 200 1003 Surat 200 1002 London 100 1001 London 100 1001
2007 Pratik
Rome
100
1004
7 rows selected. 30. Calculate the total of order for each day an place the result in descending order. SQL> select sum(amount),odate from orders group by odate order by sum(amount) desc; SUM(AMOUNT) ODATE ----------- --------11201.83 10-JUN-06 8944.49 10-MAR-06 4723 10-MAY-06 1788.98 10-APR-06 31. Show the name of all customer with their salesman's name. SQL> select cname,sname from customers,salesmen where customers.snum = salesmen.snum; CNAME -------------------Harsh Gita Lalit Govind Chirag Chinmay Pratik
SNAME -------------------Piyush Anand Sejal Sejal Piyush Rajesh Mit
7 rows selected. 32. List all customer and salesmen who share same city. SQL> select customers.city,cname,sname from customers,salesmen where customers.city = salesmen.city; CITY -------------------London London London London Surat Surat
CNAME -------------------Harsh Chirag Harsh Chirag Lalit Chinmay
SNAME -------------------Piyush Piyush Mit Mit Sejal Sejal
6 rows selected. 33. List all order with the name of their customer and salesman. SQL> select onum,amount,odate,cname,sname from orders,customers, Salesmen where orders.cnum = customers.cnum and orders.snum = salesmen.snum;
ONUM AMOUNT ODATE CNAME --------- --------- --------- -------------------3001 18.69 10-MAR-06 Chinmay 3003 767.19 10-MAR-06 Harsh 3002 1900.1 10-MAR-06 Pratik 3005 5160.45 10-MAR-06 Lalit 3006 1098.06 10-MAR-06 Chinmay 3009 1713.23 10-APR-06 Gita 3007 75.75 10-APR-06 Govind 3008 4723 10-MAY-06 Chirag 3010 1309.95 10-JUN-06 Govind 3011 9891.88 10-JUN-06 Chirag
SNAME -----------------Rajesh Piyush Mit Sejal Rajesh Anand Sejal Piyush Sejal Piyush
10 rows selected. 34. List all order by the customer no locate in the same city as their salesman. SQL> select onum,amount,odate,orders.cnum,orders.snum from orders, customers,salesmen where customers.city = salesmen.city and orders.cnum = customers.cnum and orders.snum = salesmen.snum ; ONUM AMOUNT ODATE CNUM SNUM --------- --------- --------- --------- --------3003 767.19 10-MAR-06 2001 1001 3005 5160.45 10-MAR-06 2003 1002 3008 4723 10-MAY-06 2006 1001 3011 9891.88 10-JUN-06 2006 1001 35. List all customers serviced by salespeople with commission above 12%. SQL> select customers.* from customers,salesmen where customers.snum = salesmen.snum and salesmen.commission>12 ; CNUM --------2002 2008 2007
CNAME -------------------Gita Chinmay Pratik
CITY RATING SNUM -------------------- --------- --------Rome 200 1003 Surat 300 1007 Rome 100 1004
36. Calculate the amount of the salesman commission of each order by customer with rating above 100.
37. Find all pair of customer having the same rating without duplication.
38. Company policy is to assign each salesperson three customers, one at each of the three ratings. Display all possible combination of such three customers.
39. List all customer locate in cities where salesmen Sejal has customers. SQL> select customers.*,salesmen.sname from customers,salesmen where salesmen.sname = 'Sejal' and salesmen.snum = customers.snum; CNUM CNAME CITY RATING SNUM SNAME 2003 Lalit Surat 200 1002 Sejal 2004 Govind Bombay 300 1002 Sejal 40. Find all pair of customer served by a single salesmen with the salesman's name and no. 41. List all salesmen who are living in the same city with out duplicate rows. 42. List all pairs of order by a given customer with customer name. 43. Produce the name and city of all the customer with the same rating as Harsh.
SQL> select cname,city from customers where rating = (select rating from customers where cname = 'Harsh') and cname!= 'Harsh' ; CNAME CITY -------------------- -------------------Chirag London Pratik Rome
SQL> insert into customers values(&cnum,'&cname','&city',&rating,&snum); CNUM CNAME CITY RATING SNUM ------------------------------------------------------2001 Harsh London 100 1001 2002 Gita Rome 200 1003 2003 Lalit Surat 200 1002 2004 Govind Bombay 300 1002 2006 Chirag London 100 1001 2008 Chinmay Surat 300 1007 2007 Pratik Rome 100 1004
SQL> insert into orders values(&onum,&amount,’&odate’,&cnum,&snum); ONUM AMOUNT ODATE CNUM SNUM ------------------------------------------------------3001 18.69 10/03/06 2008 1007 3003 767.19 10/03/06 2001 1001 3002 1900.10 10/03/06 2007 1004 3005 5160.45 10/03/06 2003 1002 3006 1098.16 10/03/06 2008 1007 3009 1713.23 10/04/06 2002 1003 3007 75.75 10/04/06 2004 1002 3008 4723.00 10/05/06 2006 1001
3010 3011
1309.95 9891.88
10/06/06 10/06/06
2004 2006
1002 1001
1. Produce the order no, amount and date of all orders. SQL> select onum,amount,odate from orders; ONUM AMOUNT ODATE --------- --------- --------3001 18.69 10-MAR-06 3003 767.19 10-MAR-06 3002 1900.1 10-MAR-06 3005 5160.45 10-MAR-06 3006 1098.06 10-MAR-06 3009 1713.23 10-APR-06 3007 75.75 10-APR-06 3008 4723 10-MAY-06 3010 1309.95 10-JUN-06 3011 9891.88 10-JUN-06 10 rows selected. 2. Give all the information about all the customer with salesman number 1001. SQL> select * from customers where snum = 1001; CNUM --------2001 2006
CNAME -------------------Harsh Chirag
CITY RATING SNUM -------------------- --------- --------London 100 1001 London 100 1001
3. Display the following information in the order of city, sname, snum and commission. SQL> select city,sname,snum,commission from salesmen order by city; CITY -------------------Baroda London London New Delhi Surat
SNAME SNUM COMMISSION -------------------- --------- ---------Rajesh 1007 14 Piyush 1001 11 Mit 1004 21 Anand 1003 15 Sejal 1002 10
4. List of rating followed by the name of each customer in Surat. SQL> select rating,cname from customers where city = 'Surat';
RATING --------200 300
CNAME -------------------Lalit Chinmay
5. List snum of all salesmen with order in order table without any duplicates. SQL> select distinct snum from orders order by snum; SNUM --------1001 1002 1003 1004 1007 6. List of all order for more than Rs 1000. SQL> select * from orders where amount > 1000; ONUM AMOUNT ODATE CNUM SNUM --------- --------- --------- --------- --------3002 1900.1 10-MAR-06 2007 1004 3005 5160.45 10-MAR-06 2003 1002 3006 1098.06 10-MAR-06 2008 1007 3009 1713.23 10-APR-06 2002 1003 3008 4723 10-MAY-06 2006 1001 3010 1309.95 10-JUN-06 2004 1002 3011 9891.88 10-JUN-06 2006 1001 7 rows selected. 7. List of name & cities of all salesmen in London with commission above 10%. SQL> select sname,city from salesmen where city = 'London' and commission > 10; SNAME -------------------Piyush Mit
CITY -------------------London London
8. List all customers excluding those with rating < 10 unless they are located in Rome. SQL> select * from customers where rating >= 10 and city != 'Rome'; CNUM --------2001 2003 2004
CNAME -------------------Harsh Lalit Govind
CITY RATING SNUM -------------------- --------- --------London 100 1001 Surat 200 1002 Bombay 300 1002
2006 Chirag 2008 Chinmay
London Surat
100 300
1001 1007
9. List all orders for more than Rs.100 except the order of snum<100 of 10/03/97. SQL> select * from orders where amount > 100 and snum >=100 and odate ! = '10-mar-97'; ONUM AMOUNT ODATE CNUM SNUM --------- --------- --------- --------- --------3003 767.19 10-MAR-06 2001 1001 3002 1900.1 10-MAR-06 2007 1004 3005 5160.45 10-MAR-06 2003 1002 3006 1098.06 10-MAR-06 2008 1007 3009 1713.23 10-APR-06 2002 1003 3008 4723 10-MAY-06 2006 1001 3010 1309.95 10-JUN-06 2004 1002 3011 9891.88 10-JUN-06 2006 1001 8 rows selected. 10. List all orders taken on October 3rd or 4th or 6th 2006. SQL> select * from orders where odate in( 2 '3-oct-06','4-oct-06','6-oct-06'); no rows selected 11. List all customers whose name begins with
letter 'C'.
SQL> select * from customers where cname like 'C%'; CNUM --------2006 2008
CNAME -------------------Chirag Chinmay
CITY RATING SNUM -------------------- --------- --------London 100 1001 Surat 300 1007
12. List all customers whose name begin with letter ‘A’ to 'G'. SQL> select * from customers where cname between 'A%' and 'G%'; CNUM --------2006 2008
CNAME -------------------Chirag Chinmay
CITY RATING SNUM -------------------- --------- --------London 100 1001 Surat 300 1007
13. List all orders with zero or NULL amount.
SQL> select * from orders where amount = 0 or amount is NULL; no rows selected
14. Find out the largest order of salesman 1002 and 1007. SQL> select max(amount),snum from orders where snum = 1002 or snum = 1007 group by snum; MAX(AMOUNT) SNUM ----------- --------5160.45 1002 1098.06 1007 15. Count all order of October 3 2006. SQL> select count(onum) from orders where odate = '3-oct-2006'; COUNT(ONUM) ----------0 16. Calculate the total amount ordered. SQL> select sum(amount) from orders; SUM(AMOUNT) ----------26658.3 17. Calculate the average amount ordered. SQL> select avg(amount) from orders; AVG(AMOUNT) ----------2665.83 18. Count the no of salesmen currently having orders. SQL> select * from orders where to_char(odate,'dd-mon-yy') = (select to_char(sysdate,'dd-mon-yy') 2 from dual) 3 ; no rows selected 19. Find the largest orders take by each salesman of each date. SQL> select snum,max(amount) from orders group by snum; SNUM MAX(AMOUNT) --------- ----------1001 9891.88 1002 5160.45 1003 1713.23 1004 1900.1 1007 1098.06
20. Find the largest order take by each salesman of 10/03/2006. SQL> select snum,max(amount) from orders where odate = '10-mar-06' group by snum; SNUM MAX(AMOUNT) --------- ----------1001 767.19 1002 5160.45 1004 1900.1 1007 1098.06 21. Count the no of different not NUL cities in the Customer table. SQL> select count(city) from customers where city is not null; COUNT(CITY) ----------7 22. Find out each customer's smallest order. SQL> select min(amount),cnum from orders group by cnum; MIN(AMOUNT) CNUM ----------- --------767.19 2001 1713.23 2002 5160.45 2003 75.75 2004 4723 2006 1900.1 2007 18.69 2008 7 rows selected. 23. Find out first customer in alphabetical order whose name begins with 'G'.
24. Count the no of salesmen registering order for each day. SQL> select count(snum),odate from orders group by odate; COUNT(SNUM) ----------5 2 1 2
ODATE --------10-MAR-06 10-APR-06 10-MAY-06 10-JUN-06
25. List all salesmen with their % of commission.
SQL> select snum,sname,commission from salesmen; SNUM --------1001 1002 1004 1007 1003
SNAME COMMISSION -------------------- ---------Piyush 11 Sejal 10 Mit 21 Rajesh 14 Anand 15
26. Display the no of orders for each day in the descending order of the no of orders in the following format. FOR dd-mon-yy, there are _ Orders. SQL> select odate,count(onum) from orders group by odate order by count(onum) desc; ODATE COUNT(ONUM) --------- ----------10-MAR-06 5 10-APR-06 2 10-JUN-06 2 10-MAY-06 1 27. Assume each salesperson has 12 % commission. Write a query on the order table that will produce the order number, salesman no and the amount of commission for that order.
28. Find the highest rating in each city in the for : For the city (city), the highest rating is : (rating) SQL> select city,max(rating) from customers group by city; CITY MAX(RATING) -------------------- ----------Bombay 300 London 100 Rome 200 Surat 300 29. List all customers in descending order of rating. SQL> select * from customers order by rating desc; CNUM --------2004 2008 2002 2003 2001 2006
CNAME -------------------Govind Chinmay Gita Lalit Harsh Chirag
CITY RATING SNUM -------------------- --------- --------Bombay 300 1002 Surat 300 1007 Rome 200 1003 Surat 200 1002 London 100 1001 London 100 1001
2007 Pratik
Rome
100
1004
7 rows selected. 30. Calculate the total of order for each day an place the result in descending order. SQL> select sum(amount),odate from orders group by odate order by sum(amount) desc; SUM(AMOUNT) ODATE ----------- --------11201.83 10-JUN-06 8944.49 10-MAR-06 4723 10-MAY-06 1788.98 10-APR-06 31. Show the name of all customer with their salesman's name. SQL> select cname,sname from customers,salesmen where customers.snum = salesmen.snum; CNAME -------------------Harsh Gita Lalit Govind Chirag Chinmay Pratik
SNAME -------------------Piyush Anand Sejal Sejal Piyush Rajesh Mit
7 rows selected. 32. List all customer and salesmen who share same city. SQL> select customers.city,cname,sname from customers,salesmen where customers.city = salesmen.city; CITY -------------------London London London London Surat Surat
CNAME -------------------Harsh Chirag Harsh Chirag Lalit Chinmay
SNAME -------------------Piyush Piyush Mit Mit Sejal Sejal
6 rows selected. 33. List all order with the name of their customer and salesman. SQL> select onum,amount,odate,cname,sname from orders,customers, Salesmen where orders.cnum = customers.cnum and orders.snum = salesmen.snum;
ONUM AMOUNT ODATE CNAME --------- --------- --------- -------------------3001 18.69 10-MAR-06 Chinmay 3003 767.19 10-MAR-06 Harsh 3002 1900.1 10-MAR-06 Pratik 3005 5160.45 10-MAR-06 Lalit 3006 1098.06 10-MAR-06 Chinmay 3009 1713.23 10-APR-06 Gita 3007 75.75 10-APR-06 Govind 3008 4723 10-MAY-06 Chirag 3010 1309.95 10-JUN-06 Govind 3011 9891.88 10-JUN-06 Chirag
SNAME -----------------Rajesh Piyush Mit Sejal Rajesh Anand Sejal Piyush Sejal Piyush
10 rows selected. 34. List all order by the customer no locate in the same city as their salesman. SQL> select onum,amount,odate,orders.cnum,orders.snum from orders, customers,salesmen where customers.city = salesmen.city and orders.cnum = customers.cnum and orders.snum = salesmen.snum ; ONUM AMOUNT ODATE CNUM SNUM --------- --------- --------- --------- --------3003 767.19 10-MAR-06 2001 1001 3005 5160.45 10-MAR-06 2003 1002 3008 4723 10-MAY-06 2006 1001 3011 9891.88 10-JUN-06 2006 1001 35. List all customers serviced by salespeople with commission above 12%. SQL> select customers.* from customers,salesmen where customers.snum = salesmen.snum and salesmen.commission>12 ; CNUM --------2002 2008 2007
CNAME -------------------Gita Chinmay Pratik
CITY RATING SNUM -------------------- --------- --------Rome 200 1003 Surat 300 1007 Rome 100 1004
36. Calculate the amount of the salesman commission of each order by customer with rating above 100.
37. Find all pair of customer having the same rating without duplication.
38. Company policy is to assign each salesperson three customers, one at each of the three ratings. Display all possible combination of such three customers.
39. List all customer locate in cities where salesmen Sejal has customers. SQL> select customers.*,salesmen.sname from customers,salesmen where salesmen.sname = 'Sejal' and salesmen.snum = customers.snum; CNUM CNAME CITY RATING SNUM SNAME 2003 Lalit Surat 200 1002 Sejal 2004 Govind Bombay 300 1002 Sejal 40. Find all pair of customer served by a single salesmen with the salesman's name and no. 41. List all salesmen who are living in the same city with out duplicate rows. 42. List all pairs of order by a given customer with customer name. 43. Produce the name and city of all the customer with the same rating as Harsh.
SQL> select cname,city from customers where rating = (select rating from customers where cname = 'Harsh') and cname!= 'Harsh' ; CNAME CITY -------------------- -------------------Chirag London Pratik Rome
