test/sql/Aggregate/exp.test037.ksh

   1 echo create table emp(id int,name char(20),job char(10),salary double,doj date,deptno int,mgrno int);
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 Statement Executed: Rows Affected = 1
   9 Statement Executed: Rows Affected = 1
  10 Statement Executed: Rows Affected = 1
  11 Statement Executed: Rows Affected = 1
  12 echo select * from emp;
  13 ---------------------------------------------------------
  14         emp.id  emp.name        emp.job emp.salary      emp.doj emp.deptno      emp.mgrno
  15 ---------------------------------------------------------
  16         1       Allen   Sales   10000.000000    2006/3/3        10      4
  17         2       Piter   IT      10000.000000    2005/3/3        20      3
  18         3       Jack    Mkting  9000.000000     2006/3/3        30      6
  19         4       Tom     Clerk   7000.000000     2004/3/3        10      1
  20         5       Harry   IT      25000.000000    2005/3/3        20      5
  21         6       Dick    Sales   15000.000000    2005/3/3        30      7
  22         7       Boyee   Mentain 20000.000000    2005/3/3        20      2
  23         3       Jack    IT      9000.000000     2006/3/3        30      6
  24         2       Piter   Mkting  10000.000000    2005/3/3        20      3
  25
  26 echo select a.deptno,a.name,b.name from emp as a,emp as b where a.id=b.mgrno;
  27 ---------------------------------------------------------
  28         a.deptno        a.name  b.name
  29 ---------------------------------------------------------
  30         10      Allen   Tom
  31         20      Piter   Boyee
  32         30      Jack    Piter
  33         30      Jack    Piter
  34         10      Tom     Allen
  35         20      Harry   Harry
  36         30      Dick    Jack
  37         30      Dick    Jack
  38         20      Boyee   Dick
  39         30      Jack    Piter
  40         30      Jack    Piter
  41         20      Piter   Boyee
  42
  43 echo select a.deptno,a.name,b.name from emp as a,emp as b where a.id=b.mgrno group by a.deptno,a.name,b.name;
  44 ---------------------------------------------------------
  45         a.deptno        a.name  b.name
  46 ---------------------------------------------------------
  47         10      Allen   Tom
  48         20      Piter   Boyee
  49         30      Jack    Piter
  50         10      Tom     Allen
  51         20      Harry   Harry
  52         30      Dick    Jack
  53         20      Boyee   Dick
  54
  55 echo select a.deptno,a.name,b.name,count(a.salary),sum(b.salary) from emp as a,emp as b where a.id=b.mgrno group by a.deptno,a.name,b.name;
  56 ---------------------------------------------------------
  57         a.deptno        a.name  b.name  COUNT(a.salary) SUM(b.salary)
  58 ---------------------------------------------------------
  59         10      Allen   Tom     1       7000.000000
  60         20      Piter   Boyee   2       40000.000000
  61         30      Jack    Piter   4       40000.000000
  62         10      Tom     Allen   1       10000.000000
  63         20      Harry   Harry   1       25000.000000
  64         30      Dick    Jack    2       18000.000000
  65         20      Boyee   Dick    1       15000.000000
  66
  67 echo select a.deptno,a.name,b.name,count(a.salary),sum(b.salary) from emp as a,emp as b where a.id=b.mgrno group by a.deptno,a.name,b.name having count(a.salary)<2 or count(a.salary)>2;
  68 ---------------------------------------------------------
  69         a.deptno        a.name  b.name  COUNT(a.salary) SUM(b.salary)
  70 ---------------------------------------------------------
  71         10      Allen   Tom     1       7000.000000
  72         30      Jack    Piter   4       40000.000000
  73         10      Tom     Allen   1       10000.000000
  74         20      Harry   Harry   1       25000.000000
  75         20      Boyee   Dick    1       15000.000000
  76
  77 echo select a.deptno,a.name,b.name,count(a.salary),sum(b.salary) from emp as a,emp as b where a.id=b.mgrno group by a.deptno,a.name,b.name having (count(a.salary)<2 or count(a.salary)>2) and sum(b.salary)>15000;
  78 ---------------------------------------------------------
  79         a.deptno        a.name  b.name  COUNT(a.salary) SUM(b.salary)
  80 ---------------------------------------------------------
  81         30      Jack    Piter   4       40000.000000
  82         20      Harry   Harry   1       25000.000000
  83
  84 Statement Executed