Practical 3

1 SELECT sname,commision FROM salesman_aj;

SNAME   COMMISION
Piyush  12
Sejal   13
Miti    11
Rajesh  15
Anand   10

2 SELECT 'for'AS " ",odate AS " " ,'there is' AS " " ,COUNT(onum) AS " " ,'orders' AS " " FROM orders_aj
GROUP BY odate ORDER BY COUNT(onum) DESC;

FOR 10-MAR-97   there IS    5   orders
FOR 10-JUN-97   there IS    2   orders
FOR 10-APR-97   there IS    2   orders
FOR 10-MAY-97   there IS    1   orders

3 SELECT onum,snum,amount*(12/100) AS "amount for 12 % commission" FROM orders_aj;

ONUM    SNUM    amount FOR 12 % commission
3011    1001    1187.0256
3010    1002    157.194
3008    1001    566.76
3007    1002    9.09
3009    1003    205.5876
3006    1007    131.7792
3005    1002    619.254
3002    1004    228.012
3003    1001    92.0628
3001    1007    2.2428

4 SELECT 'for' AS " ",'the' AS " " , city AS " ",'highest rating is ' AS " "  , MAX(rating) AS " " FROM customer_aj GROUP BY city;

FOR the Rome    highest rating IS   200
FOR the Surat   highest rating IS   300
FOR the London  highest rating IS   100
FOR the Bombay  highest rating IS   300

5 SELECT * FROM customer_aj ORDER BY rating DESC;

CNUM    CNAME   CITY    RATING  SNUM
2004    Govind  Bombay  300 1002
2008    Chinmay Surat   300 1007
2003    Lalit   Surat   200 1002
2002    Gita    Rome    200 1003
2006    Chirag  London  100 1001
2001    Harsh   London  100 1001
2007    Pratik  Rome    100 1004

6 SELECT odate,SUM(amount) FROM orders_aj GROUP BY odate ORDER BY odate DESC;

ODATE   SUM(AMOUNT)
10-JUN-97   11201.83
10-MAY-97   4723
10-APR-97   1788.98
10-MAR-97   8944.59