Total Pageviews

May 17, 2016

5/17/2016 08:45:00 AM

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;