TABLE SALESPEOPLE
SNUM SNAME CITY COMM
1001 Peel London .12
1002 Serres San
Jose .13
1004
Motika London .11
1007
Rafkin Barcelona .15
1003 Axelrod New
york .1
TABLE CUST
CNUM
CNAME CITY RATING SNUM
2001 Hoffman London 100 1001
2002 Giovanne Rome 200 1003
2003 Liu San
Jose 300 1002
2004 Grass Brelin 100 1002
2006
Clemens London 300 1007
2007 Pereira Rome 100 1004
ORDERS
ONUM AMT ODATE CNUM SNUM
3001 18.69 03-OCT-94 2008 1007
3003 767.19 03-OCT-94 2001 1001
3002 1900.10
03-OCT-94 2007 1004
3005 5160.45
03-OCT-94 2003 1002
3006 1098.16
04-OCT-94 2008 1007
3009 1713.23
04-OCT-94 2002 1003
3007 75.75 05-OCT-94 2004 1002
3008 4723.00 05-OCT-94 2006 1001
3010 1309.95
06-OCT-94 2004 1002
3011 9891.88 06-OCT-94 2006 1001
Problems :
1.
Display snum,sname,city and comm of all
salespeople.
Select snum, sname, city,
comm
from salespeople;
2.
Display all snum without duplicates from all
orders.
Select distinct snum
from orders;
3.
Display names and commissions of all
salespeople in london.
Select sname,comm
from salespeople
where city = ‘London’;
4.
All customers with rating of 100.
Select cname
from cust
where rating = 100;
5.
Produce orderno, amount and date form all
rows in the order table.
Select ordno, amt, odate
from orders;
6.
All customers in San Jose, who have rating
more than 200.
Select cname
from cust
where rating > 200;
7.
All customers who were either located in San
Jose or had a rating above 200.
Select cname
from cust
where city = ‘San Jose’ or
rating > 200;
8.
All orders for more than $1000.
Select *
from orders
where amt > 1000;
9.
Names and citires of all salespeople in
london with commission above 0.10.
Select sname, city
from salepeople
where comm > 0.10 and
city = ‘London’;
10.
All customers
excluding those with rating <= 100 unless they are located in Rome.
Select cname
from cust
where rating <= 100 or
city = ‘Rome’;
11.
All
salespeople either in Barcelona or in london.
Select sname, city
from salespeople
where city in
(‘Barcelona’,’London’);
12.
All
salespeople with commission between 0.10 and 0.12. (Boundary values should be
excluded)
Select sname, comm
from salespeople
where comm > 0.10 and
comm < 0.12;
13.
All customers
with NULL values in city column.
Select cname
from cust
where city is null;
14.
All orders
taken on Oct 3Rd and Oct 4th 1994.
Select *
from orders
where odate in
(‘03-OCT-94’,’04-OCT-94’);
15.
All customers
serviced by peel or Motika.
Select cname
from cust, orders
where orders.cnum =
cust.cnum and
orders.snum in ( select snum
from salespeople
where sname in 'Peel','Motika'));
16.
All customers
whose names begin with a letter from A to B.
Select cname
from cust
where cname like ‘A%’ or
cname like ‘B%’;
17.
All orders
except those with 0 or NULL value in amt field.
Select onum
from orders
where amt != 0 or
amt is not null;
18.
Count the
number of salespeople currently listing orders in the order table.
Select count(distinct snum)
from orders;
19.
Largest order
taken by each salesperson, datewise.
Select odate, snum, max(amt)
from orders
group by odate, snum
order by odate,snum;
20.
Largest order
taken by each salesperson with order value more than $3000.
Select odate, snum, max(amt)
from orders
where amt > 3000
group by odate, snum
order by odate,snum;
