Practical 2

--Practical 2
1 SELECT snum,MAX(amount) FROM orders_aj GROUP BY snum HAVING snum IN (1002,1007) ORDER BY snum;

SNUM    MAX(AMOUNT)
1002    5160.45
1007    1098.16

2 SELECT COUNT (*) FROM orders_aj WHERE odate='3-oct-97';

COUNT(*)
0

3 SELECT SUM (amount) FROM orders_aj;

SUM(AMOUNT)
26658.4

4 SELECT avg (amount) FROM orders_aj;

AVG(AMOUNT)
2665.84

5 SELECT  COUNT (DISTINCT snum) FROM orders_aj ;

COUNT(DISTINCTSNUM)
5

6 SELECT snum,odate,MAX(amount) FROM orders_aj GROUP BY snum,odate;

SNUM    ODATE      MAX(AMOUNT)
1007    10-MAR-97   1098.16
1002    10-MAR-97   5160.45
1001    10-JUN-97   9891.88
1002    10-APR-97   75.75
1001    10-MAR-97   767.19
1001    10-MAY-97   4723
1003    10-APR-97   1713.23
1004    10-MAR-97   1900.1
1002    10-JUN-97   1309.95

7 SELECT snum,MAX(amount) FROM orders_aj HAVING odate = '10-mar-97' GROUP BY snum,odate;

SNUM    MAX(AMOUNT)
1007    1098.16
1002    5160.45
1001    767.19
1004    1900.1

8 SELECT COUNT(*) FROM customer_aj WHERE city IS NOT NULL;

COUNT(*)
7

9 SELECT cnum,MIN(amount) FROM orders_aj GROUP BY cnum;

CNUM    MIN(AMOUNT)
2002    1713.23
2007    1900.1
2006    4723
2004    75.75
2008    18.69
2003    5160.45
2001    767.19

10 SELECT cname FROM customer_aj HAVING cname LIKE 'G%' GROUP BY cname ORDER BY cname; -- ek hi naam chahiye madam se puch na

CNAME
Gita
Govind

11 SELECT odate,COUNT(DISTINCT snum) FROM orders_aj GROUP BY odate ORDER BY odate;

ODATE   COUNT(DISTINCTSNUM)
10-MAR-97   4
10-APR-97   2
10-MAY-97   1
10-JUN-97   2