Practical : 5

Table : SALESMAN

create table salesman(snum varchar2(6) primary key,sname

varchar2(20) NOT NULL,city varchar2(15),comm
number(5,2),check(snum like 'S%'));

insert into salesman values('S1001','Piyush','London','0.12');

insert into salesman values('S1002','Niraj','San jose','0.13');
insert into salesman values('S1003','Miti','London','0.11');
insert into salesman values('S1004','Rajesh','Barcelona','0.15');
insert into salesman values('S1005','Haresh','New york','0.10');
insert into salesman values('S1006','Ram','Bombay','0.10');
insert into salesman values('S1007','Nehal','Delhi','0.09');

TABLE : CUSTOMER

create table customer(CNUM varchar2(6) primary key,CNAME

varchar2(20)
NOT NULL,CITY varchar2(15),RATING number(5),SNUM
varchar2(6),
check(cnum like 'C%'));

insert into customer

values('C2001','Hardik','London','100','S1001');
insert into customer values('C2002','Geeta','Rome','200','S1003');
insert into customer values('C2003','Kavish','San
jose','200','S1002');
insert into customer values('C2004','Dhruv','Berlin','300','S1002');
insert into customer
values('C2005','Pratham','London','100','S1001');
insert into customer values('C2006','Vyomesh','San
jose','300','S1007');
insert into customer values('C2007','Kirit','Rome','100','S1004');

TABLE : ORDER

create table orders(ONUM varchar2(6) primary key,AMT

number(10,2) NOT NULL,ODATE date,CNUM varchar2(6),SNUM
varchar2(6),check(onum like 'O%'));

insert into orders values('O3001','18.69','Mar-10-

90','C2008','S1007');
insert into orders values('O3003','767.19','Mar-10-
90','C2001','S1001');
insert into orders values('O3002','1900.10','Oct-03-
90','C2007','S1004');
insert into orders values('O3005','5160.45','Oct-04-
90','C2003','S1002');
insert into orders values('O3006','1098.16','Mar-10-
90','C2008','S1007');
insert into orders values('O3009','1713.23','April-10-
90','C2002','S1003');
insert into orders values('O3007','75.75','April-10-
90','C2004','S1002');
insert into orders values('O3008','4723.00','May-10-
90','C2006','S1001');
insert into orders values('O3010','1309.95','May-10-
90','C2004','S1002');
insert into orders values('O3011','9891.88','June-10-
90','C2006','S1001');
 Queries :

1. Display all the information of salesmen.

select * from salesman;

2. Display snum,sname,city from salesmen

table.
select snum,sname,city from salesman;

3. Display odate,snum,onum and amt from

orders.
select odate,snum,onum,amt from orders;

4. Display the information of orders without

duplication.
select distinct * from orders;

5. List of sname, city from salesmen where

city is 'LONDON'.
select sname,city from salesman where city= 'London';

6. List all records of customers where rating

is equal to 100.
select * from customer where rating=100;
7. Write a select command that produces the
order number,amount and date for all rows
in the order table.
select onum,amt,odate from orders;

8. Produces all rows from the customer

table for which the salesperson's number is
S1001.
select * from customer where snum = 'S1001';

9. Display the salesperson table with the

column in the following order:
city,sname,snum,comm.
select city,sname,snum,comm from salesman;

10. Write a select command that produces the

rating followed by the name of each
customer in SAN JOSE.
select rating,cname from customer where city = 'San jose';

11. Display SNUM values of all salesmen

without any repeat.
select distinct(snum) from salesman
 Operators :

12. List all customers with a rating above 200.

select * from customer where rating>200;

13. List all customers in SAN JOSE who have

a rating above 200.
select * from customer where city = 'San jose' and rating>200;

14. List all customers who were either located

in SAN JOSE or had a rating above 200.
select * from customer where city= 'San jose' or rating>200;

15. List of all customers who were either

located in SAN JOSE or not rating above
200.
select * from customer where city= 'San jose' or rating<=200;

16. List of all customers who were not located

in SAN JOSE or rating is not above 200.
select * from customer where city!= 'San jose' or rating<=200;

17. Write a query that will give you all orders

for more than $1000.
select * from orders where amt>1000;
18. Write a query that will give you the names
and cities of all salesmen in LONDON with a
commission
above 0.10.
select sname,city from salesman where city= 'London' and
comm>0.10;

19. Write a query on the customers table

whose output will exclude all customers
with a rating <= 100 and
they are located in ROME.
select * from customer where rating>100 and city!= 'Rome' ;
 SPECIAL OPERATORS :

20. Display all salesmen that were located in

either BARCELONA or LONDON(use IN
keyword).
select * from salesman where city in('London' , 'Barcelona');

21. Find all customers matched with

salesmen S1001,S1007 and S1004.
select * from salesman where snum in( 'S1001' , 'S1007' ,
'S1004' );

22. Display all salesmen with commission

between 0.10 and 0.12.
select * from salesman where comm between 0.10 and 0.12;

23. Select all customers whose names fall in

a 'A' and '`G' alphabetical.
select * from customer where cname like '%a%' and cname like
'%G%' ;
 LIKE OPERATORS :

24. List all the customers whose names begin

with 'G'.
select * from customer where cname like 'G%';

25. List all salesmen whose sname start with letter 'P' and end letter is 'H'.
select * from salesman where sname like 'P%' and sname like '%h';

NULL OPERATORS :

26. Find all records in customer table with

NULL values in the city column.
select * from customer where city= 'NULL' ;

27. Write a two queries that will produce all

orders taken on October 3rd or 4th ,1990
( use IN operator and
Use BETWEEN operator )
select * from orders where odate between 'Oct-03-90' and
'Oct-04-90';

28. Write a query that selects all of the

customers matched with S1001 and S1002 .
 select * from customer where snum in( 'S1001' , 'S1002' );

29. Write a query that will produce all of the

customers whose names begin with aletter
from A to H.
select * from customer WHERE cname LIKE 'A%' or cname
LIKE 'B%' or cname LIKE 'C%'or cname
LIKE 'D%' or cname LIKE 'E%' or cname LIKE 'F%' or
cname LIKE 'G%' or cname LIKE 'H%' ;

30. Write a query that selects all customers

whose names begin with 'C'.
select * from customer where cname like 'C%' ;

31. Write a query that selects all orders

without ZEROS or NULLS in amt field.
select * from orders where amt!= 'NULL' or amt!=0;

FUNCTIONS :

32. Display sum of amt,average of orders.

select sum(amt),avg(amt) from orders;

33. To count the numbers of salesmen

without duplication in the orders tables.
 select count( distinct(snum)) from orders;

34. Count the rating of customers (with NULL

and without NULL).
select rating from customer;

35. Find the largest order taken by each

salesperson.(hint: use group by)
select max(amt) from orders group by snum;

36. Find the largest order taken by each

salesperson on each date.
select max(amt) from orders group by snum,odate;

37. Find out which day had the higher total

amount ordered.
select odate from orders where amt in(select max(amt) from
orders where odate in(select odate
from orders where amt in(select sum(amt) from orders
group by odate)));

38. Write a query that counts all orders for

October 3rd.
select count(*) from orders where odate like '%03%' and odate
like '%OCT%' ;
39. Write a query that counts the number of
different non-NULL city in the customer
table.
select count(*) from customer where city!= 'NULL' ;

40. Write a query that selects the first

customer in alphabetical order whose na
mebegin with `G'.
select * from customer where cname like 'G%' order by
cname;

41. write a query that selects each customers

smallest order.
select * from orders a where amt in(select min(amt) from
orders group by cnum);

42. Write a query that selects the highest

rating in each city.
select max(rating),city from customer group by city;

43. Write a query that counts the number of

salesmen registering orders for each day(if
a salesperson has more than one order on a
given day , he or she should be counted
only once)
select count( distinct snum) from orders group by odate;
44. Display all the information in descending
orders(use column CNUM).
select * from orders order by cnum DESC;

45. Display all the information in descending

orders(use column CNUM,AMT).
select * from orders order by amt DESC,cnum DESC;

46. Display sname and comm. From salesmen

in descending order(in place of column
name use column number).
select sname,comm from salesman order by snum DESC;

47. Assume each salesperson has a 0.12

commission. Write a query on the orders
table that will produce the order number,the
salesperson number and the amount of the
salesperson’s commission for that order
select [Link],[Link],[Link] from orders o,salesman s
where [Link]=[Link];

48. Write a query on the customers table that

will find the highest rating in each city. Put
 the output in this form. For the city (city) ,
the highest rating is: (rating).
select city,max(rating) from customer group by city;

49. Write a query that lists customers in

descending order of rating. Output the
rating field first, followed by the customer’s
name and number.
select rating,cname,cnum from customer order by rating desc;

50. Write a query that totals the orders for

each day and places the results in
descending order.
select sum(amt) as amt,odate from orders group by odate
order by amt desc;

51. Show the names of all customers

matched with the salesmen serving them.
select cname,sname,cnum from salesman,customer where
[Link]=[Link];

52. Write a query that lists each order number

followed by the name of the customer
who made the order.
select [Link],[Link] from customer,orders
where [Link]=[Link];
53. Write a query that gives the names of both
the salesperson and the customer for each
order after the
order number.
select cname,sname,onum from customer a,salesman b,orders
c where [Link] = [Link] and
[Link]=[Link];

54. Write a query that produces all customers

serviced by salesmen with a commission
above 0.12. Output
the customer’s name, the salesperson’s
name and the salesperson’s rate of
commission.
select [Link],[Link],[Link] from customer a,salesman c
where [Link]=[Link] and
[Link]>'0.12';

55. Write a query that calculates the amount

of the salesperson’s commission on
each order by a customer with a rating
above 100.
select
[Link],[Link],[Link],[Link],[Link],[Link],[Link]*[Link]
from orders a,customer b,salesman c where [Link]=[Link]
and [Link]=[Link] and [Link]>100;
 OTHERS :

56. List all customer located in cities where

salesperson 'PIYUSH' has customer.
select * from customer where snum = 'S1001';

57. List all salesmen who are living in same city

without duplicate rows.
select [Link] salesman1, [Link] salesman2 , [Link] city
from salesman s1,salesman s2 where [Link] > [Link] and
[Link] = [Link];

58. Extract all orders of 'PIYUSH'.

select distinct [Link], [Link], [Link], [Link], [Link] from
orders o, salesman where [Link] = (select snum from
salesman where sname = 'Piyush');

59. Extract all orders of LONDON’S salesmen.

select onum from orders where snum = ( select snum from
salesman where city ='LONDON');

60. Find all customers whose cnum is 1000

above than the snum of ‘PIYUSH’.
select cnum, cname from customer where cnum>(select snum
from salesman where
sname='PIYUSH');
61. Count the no. of customers with the rating
above than average Rating of ‘LONDON’.
select count(cnum) from customer where rating > (select
avg(rating) from customer where
city='london');

62. Produce the name and rating of all

customers who have above average Rating.
select max([Link]), max([Link]), [Link] from orders a,
customer b where [Link] = [Link] group by [Link] having
count([Link]) > ( select avg(count(cnum)) from orders group
by cnum);

63. List all salesmen with customers located

in their cities.
select [Link],[Link],[Link],[Link],[Link],[Link] from
salesman s, customer c where [Link] = [Link] and
[Link]=[Link];

64. Select all customers whose rating doesn’t

match with any rating customer of ‘SAN
JOSE’.
select * from customer where rating not in ( select rating from
customer where upper(city)='san
jose');
65. Create a union of two queries that shows
the names cities and ratings of all
[Link] with
 rating of >=200 should display ‘HIGH
RATING’ and those with <200 should
display ‘LOW RATING’.
Select cname, city, rating, 'Higher Rating' from customer where
rating >= 200 UNION Select cname, city, rating, 'Lower Rating'
from customer where rating < 200;
66. Find all customers with orders on 3rd
october 1990 using correlate sub query.
select [Link],[Link],[Link],[Link],[Link],[Link] from
customer c, orders o where odate in (select odate from orders
where odate='oct-03-1990');

67. Find all customers having rating greater

than any customer in ‘ROME’.
select * from customer where rating>(select max(rating) from
customer where city='Rome');

68. Insert a row into salesmen table with the

values snum is s1008,salesmen name is
‘RAKESH’, city is
unknown and commission is 14%.
insert into salesman values('S1008','RAKESH',NULL,'0.14');

69. Create another table London_staff having

same structure as salesmen table
create table london_staff as select * from salesman ;

Delete all orders from customer ‘PIYUSH’

from the order table.
 delete from orders where cnum in ( select cnum from customer
where upper(cname) = 'piyush' );

70. Set the ratings of all the customers of

PIYUSH to 400.
update customer set rating = 400 where
[Link] = (select [Link]
from salesman where
upper(sname)='piyush');
71. Increase the rating of all the customers in
ROME by 100.
update customer set rating = rating + 100 where upper(city) =
'Rome';

72. Double the commission of all salesmen

of LONDON.
select 2*comm from salesman where city = 'LONDON';

73. Set ratings for all customers in LONDON

to NULL.
update customer set rating = null where upper(city) = 'London';
74. Delete all salesmen who have at least one
customer with a rating of 100 from
salesmen table.
delete from salesman where snum in ( select snum from
customer where rating = 100 );