create table salesman(snum varchar2(6) primary key,sname varchar2(20) NOT NULL,c
ity 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');
create table customer(cnum varchar2(6) primary key,cname varchar2(20) NOT NULL,c
ity 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');
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','10-Mar-90','C2008','S1007');
insert into orders values('O3003','767.19','10-Mar-90','C2001','S1001');
insert into orders values('O3002','1900.10','03-Oct-90','C2007','S1004');
insert into orders values('O3005','5160.45','04-Oct-90','C2003','S1002');
insert into orders values('O3006','1098.16','10-Mar-90','C2008','S1007');
insert into orders values('O3009','1713.23','10-April-90','C2002','S1003');
insert into orders values('O3007','75.75','10-April-90','C2004','S1002');
insert into orders values('O3008','4723.00','10-May-90','C2006','S1001');
insert into orders values('O3010','1309.95','10-May-90','C2004','S1002');
insert into orders values('O3011','9891.88','10-June-90','C2006','S1001');
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 salespersons 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 abov
e
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 abov
e
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 in( 04-Oct-90 , 03-Oct-90 );
==> select * from orders where odate between 03-Oct-90 and 04-Oct-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
a
letter from A to H.
==> select * from customer WHERE cname LIKE A% or cname LIKE B% or cname LIK
E 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 odat
e 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
me
begin 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 cnu
m);
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;
48. Write a query on the customers table that will find the highest rating in ea
ch
city. Put the output in this form.
For the city (city) , the highest rating is: (rating).
==> Select For the city ( || city || ), the highest rating is : ( ||rating||
) from customer where city,rating in (select city,max(rating) from customer gr
oup by city );
==> select count(distinct snum) from orders s where s.odate=odate;
