1 echo create table emp
(id int
,name char
(20),job char
(10),salary double
,doj
date,deptno int
,mgrno int
);
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
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 ---------------------------------------------------------
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 ---------------------------------------------------------
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
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
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