Total Pageviews

January 13, 2015

1/13/2015 09:04:00 PM

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


1.     Which day had the hightest total amount ordered.
Select odate, amt, snum, cnum
from orders
where amt = (select max(amt)
from orders)
2.     Count all orders for Oct 3rd.
Select count(*)
from orders
where odate = ‘03-OCT-94’;
3.     Count the number of different non NULL city values in customers table.
Select count(distinct city)
from cust;
  

4.     Select each customer’s smallest order.
Select cnum, min(amt)
from orders
group by cnum;
5.     First customer in alphabetical order whose name begins with G.
Select min(cname)
from cust
where cname like ‘G%’;
6.     Get the output like “ For dd/mm/yy there are ___ orders.
Select 'For ' || to_char(odate,'dd/mm/yy') || ' there are '||
count(*) || ' Orders'
from orders
group by odate;
7.     Assume that each salesperson has a 12% commission. Produce order no., salesperson no., and amount of salesperson’s commission for that order.
Select onum, snum, amt, amt * 0.12
from orders
order by snum;
8.     Find highest rating in each 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 : (' ||
max(rating) || ')'
from cust
group by city;
9.     Display the totals of orders for each day and place the results in descending order.
Select odate, count(onum)
from orders
group by odate
order by count(onum);
10. All combinations of salespeople and customers who shared a city. (ie same city).
Select sname, cname
from salespeople, cust
where salespeople.city = cust.city;
11. Name of all customers matched with the salespeople serving them.
Select cname, sname
from cust, salespeople
where cust.snum = salespeople.snum;
12. List each order number followed by the name of the customer who made the order.
Select onum, cname
from orders, cust
where orders.cnum = cust.cnum;




13. Names of salesperson and customer for each order after the order number.
Select onum, sname, cname
from orders, cust, salespeople
where orders.cnum = cust.cnum and
           orders.snum = salespeople.snum;
14. Produce all customer serviced by salespeople with a commission above 12%.
Select cname, sname, comm
from cust, salespeople
where comm > 0.12 and
           cust.snum = salespeople.snum;
15. Calculate the amount of the salesperson’s commission on each order with a rating above 100.
Select sname, amt * comm
from orders, cust, salespeople
where rating > 100 and
          salespeople.snum = cust.snum and
          salespeople.snum = orders.snum and
          cust.cnum = orders.cnum
16. Find all pairs of customers having the same rating.
Select a.cname, b.cname,a.rating
from cust a, cust b
where a.rating = b.rating and
          a.cnum != b.cnum
17. Find all pairs of customers having the same rating, each pair coming once only.
Select a.cname, b.cname,a.rating
from cust a, cust b
where a.rating = b.rating and
          a.cnum != b.cnum and
                      a.cnum < b.cnum;
18. Policy is to assign three salesperson to each customers. Display all such combinations.
Select cname, sname
from salespeople, cust
where sname in  ( select sname
     from salespeople
                                         where rownum <= 3)
order by cname;
19. Display all customers located in cities where salesman serres has customer.
Select cname
from cust
where city = ( select city
                                   from cust, salespeople
           where cust.snum = salespeople.snum and                  sname = 'Serres');



Select cname
from cust
where city in ( select city
                                    from cust, orders
                                    where cust.cnum = orders.cnum and
                                    orders.snum in ( select snum
   from salespeople
                                                              where sname = 'Serres'));
20. Find all pairs of customers served by single salesperson.
Select cname from cust
 where snum in (select snum from cust
                group by snum
                having count(snum) > 1);

Select distinct a.cname
from cust a ,cust b
where a.snum = b.snum and a.rowid != b.rowid;
21. Produce all pairs of salespeople which are living in the same city. Exclude combinations of salespeople with themselves as well as duplicates with the order reversed.
Select a.sname, b.sname
from salespeople a, salespeople b
where a.snum > b.snum and
      a.city = b.city;
22. Produce all pairs of orders by given customer, names that customers and eliminates duplicates.
Select c.cname, a.onum, b.onum
from orders a, orders b, cust c
where a.cnum = b.cnum and
          a.onum > b.onum and
                      c.cnum = a.cnum;
23. Produce names and cities of all customers with the same rating as Hoffman.
Select cname, city
from cust
where rating = (select rating
                                      from cust
              where cname = 'Hoffman')
and cname != 'Hoffman';
24. Extract all the orders of Motika.
Select Onum
from orders
where snum = ( select snum
   from salespeople
   where sname = ‘Motika’);


25. All orders credited to the same salesperson who services Hoffman.
Select onum, sname, cname, amt
from orders a, salespeople b, cust c
where a.snum = b.snum and
          a.cnum = c.cnum and
          a.snum = ( select snum
                            from orders
                                        where cnum = ( select cnum
                                                                  from cust
                                                                  where cname = 'Hoffman'));
26. All orders that are greater than the average for Oct 4.
Select *
from orders
where amt > ( select avg(amt)
                        from orders
                                    where odate = '03-OCT-94');
27. Find average commission of salespeople in london.
Select avg(comm)
from salespeople
where city = ‘London’;
28. Find all orders attributed to salespeople servicing customers in london.
Select snum, cnum
from orders
where cnum in (select cnum
  from cust
                          where city = 'London');
29. Extract commissions of all salespeople servicing customers in London.
Select comm
from salespeople
where snum in (select snum
                          from cust
                          where city = ‘London’);
30. Find all customers whose cnum is 1000 above the snum of serres.
Select cnum, cname from cust
where cnum > ( select snum+1000
                          from salespeople

                          where sname = 'Serres');

0 comments:

Post a Comment