1 echo create table emp(id int,name char(20),job char(10),salary double,doj date,deptno int,mgrno int);
2 create table emp(id int,name char(20),job char(10),salary double,doj date,deptno int,mgrno int);
3 insert into emp values(1,'Allen','Sales' ,10000,'2006-03-03',10,4);
4 insert into emp values(2,'Piter', 'IT' ,10000,'2005-03-03',20,3);
5 insert into emp values(3,'Jack' ,'Mkting' ,9000 ,'2006-03-03',30,6);
6 insert into emp values(4,'Tom', 'Clerk' ,7000 ,'2004-03-03',10,1);
7 insert into emp values(5,'Harry ','IT',25000,'2005-03-03',20,5);
8 insert into emp values(6,'Dick' ,'Sales' ,15000,'2005-03-03',30,7);
9 insert into emp values(7,'Boyee' ,'Mentain',20000,'2005-03-03',20,2);
10 insert into emp values(3,'Jack' , 'IT' ,9000 ,'2006-03-03',30,6);
11 insert into emp values(2,'Piter', 'Mkting',10000,'2005-03-03',20,3);
12 echo select * from emp;
14 echo select a.deptno,a.name,b.name from emp as a,emp as b where a.id=b.mgrno;
15 select a.deptno,a.name,b.name from emp as a,emp as b where a.id=b.mgrno;
16 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;
17 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;
18 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;
19 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;
20 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;
21 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;
22 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;
23 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;