MySQL(06)——SQL練習テーマ(部門従業員表)
8784 ワード
データ:従業員テーブルを自己関連テーブルとします.
練習テーマ:
sql文:
--
create table dept(
deptno int primary key auto_increment, --
dname varchar(14) , --
loc varchar(13) --
) ;
--
create table emp(
empno int primary key auto_increment,--
ename varchar(10), -- -
job varchar(9), --
mgr int, --
hiredate date, -- ，
sal int, --
comm int, --
deptno int not null, --
foreign key (deptno) references dept(deptno)
);
insert into dept values(10,' ',' ');
insert into dept values(20,' ',' ');
insert into dept values(30,' ',' ');
insert into dept values(40,' ',' ');
insert into emp values(7369,' ',' ',7902,'1980-12-17',800,null,20);
insert into emp values(7499,' ',' ',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,' ',' ',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,' ',' ',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,' ',' ',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,' ',' ',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,' ',' ',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,' ',' ',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,' ',' ',null,'1981-11-17',5000,null,10);
insert into emp values(7844,' ',' ',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,' ',' ',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,' ',' ',7698,'1981-12-03',950,null,30);
insert into emp values(7902,' ',' ',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,' ',' ',7782,'1983-01-23',1300,null,10);
練習テーマ:
-- 1． 。
-- 2． " " 。
-- 3．***** 。
-- 4． 。
-- 5． ， 。
-- 6． job “ ” 。
-- 7． 1500 。
-- 8． " " ， 。
-- 9． 。
-- 10． " " 。
-- 11． 30 。
-- 12． 30 。
-- 13． 、 。
-- 14． 、 。
-- 15． 。
-- 16． 。
-- 17． 。
-- 18． , 。
-- 19. emp 3000 （ 3000） 、 、 。
-- 20. ' ' 。
-- 21. emp 20， 2000 （ 2000） ， ， ， ：
-- 22. emp （ ）
-- 23. （comm） 。
-- 24. 800 2500 （ ） 。（ ： and between and）
-- 25. 7521，7900，7782 。（ ： ，or in）
-- 26. “ ” ， 1000 （ 1000） 。
-- 27. “ ” 。
-- 28. ， 。
-- 29. ， 。 order by convert(name using gbk) asc;
-- 30. 、 。
-- 31. 、 、 ， ， 100.
-- 32. 。
-- 33. emp ， 10 。
-- 34. 10 。
-- 35. 30%。
-- 36. ， ( >=3000 3 ， >2000 2 ， <=2000 1 )
-- ：case when ... then ... when ... then ... else ... end
sql文:
-- 1． 。
select dept.deptno,count(empno) from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno
having count(empno)>=1
;
-- 2． " " 。
-- 2.1
select sal from emp where ename=' ';
-- 2.2
select * from emp where sal>(select sal from emp where ename=' ');
-- 3．***** 。
select e1.ename,e2.ename from emp as e1,emp as e2
where e1.mgr=e2.empno;
-- 4． 。
select e1.ename,e2.ename from emp as e1,emp as e2
where e1.mgr=e2.empno
and e1.hiredate1500;
-- 8． " " ， 。
select emp.ename
from emp,dept
where emp.deptno=dept.deptno
and dept.dname=' ';
-- 9． 。
select * from emp
where sal>(select avg(sal) from emp);
-- 10． " " 。
--
select job from emp where ename=' ';
select * from emp
where job = (select job from emp where ename=' ');
-- exists
select * from emp e1
where exists (select * from emp e2 where e2.job=e1.job and e2.ename=' ' );
-- 11． 30 。
select * from emp
where sal in (select sal from emp where deptno=30);
-- 12． 30 。
select * from emp
where sal > all(select sal from emp where deptno=30);
-- 13． 、 。
select dept.deptno,count(emp.empno),avg(sal)
from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno;
-- 14． 、 。
select emp.ename, dept.dname,emp.sal
from emp, dept
where emp.deptno=dept.deptno;
-- 15． 。
select deptno,count(empno) from emp
group by deptno;
select dept.*, ifnull(empCount,0) as from dept
left outer join (select deptno,count(empno) as empCount from emp
group by deptno) as temp
on dept.deptno=temp.deptno;
-- 16． 。
select job,min(sal)
from emp
group by job;
-- 17． 。
select deptno, min(sal)
from emp
where job=' '
group by deptno;
-- 18． , 。
select emp.*,(sal+ifnull(comm,0))*12 nianxin from emp
order by nianxin asc
;
-- 19. emp 3000 （ 3000） 、 、 。
select empno,ename,sal from emp
where sal>=3000;
-- 20. ' ' 。
select * from emp
where sal>(select sal from emp where ename=' ');
-- 21. emp 20， 2000 （ 2000） ，
-- ， ， ：
select empno as , ename as , sal as
from emp
where deptno=20
and sal>2000;
-- 22. emp （ ）
select distinct job from emp;
-- 23. （comm） 。
select * from emp
where comm is not null;
-- 24. 800 2500 （ ） 。（ ： and between and）
select * from emp
where sal >=800 and sal<=2500;
select * from emp
where sal between 800 and 2500;
-- 25. 7521，7900，7782 。（ ： ，or in）
select * from emp
where empno=7521 or empno=7900 or empno=7782;
select * from emp
where empno in (7521, 7900, 7782);
-- 26. “ ” ， 1000 （ 1000） 。
select * from emp
where ename like '% %'
and sal>1000
;
-- 27. “ ” 。
select * from emp
where ename like '__ ';
-- 28. ， 。
select * from emp
order by sal asc, hiredate desc;
-- 29. ， 。
-- order by convert(name using gbk) asc;
select * from emp
order by convert(substring(ename,1,1) using gbk) asc, sal desc;
-- 30. 、 。
select min(hiredate) from emp;
select * from emp
where hiredate = (select min(hiredate) from emp);
-- 31. 、 、 ， ， 100.
select ename,sal,ifnull(comm,100)
from emp;
-- 32. 。
select max(sal) from emp;
-- ：
select job from emp
where sal = (select max(sal) from emp);
-- ：
select job from emp
where sal >= all(select sal from emp);
-- 33. emp ， 10 。
select deptno,max(sal) as maxSal,min(sal) as minSal from emp
group by deptno;
select dept.dname as ,maxSal ,minSal from dept
left outer join (select deptno,max(sal) as maxSal,min(sal) as minSal from emp
group by deptno) temp
on dept.deptno=temp.deptno
where dept.deptno<>10;
-- 34. 10 。
select empno from emp
where deptno=10 and sal>=all(select sal from emp where deptno=10);
delete from emp
where empno = (select empno from (select empno from emp
where deptno=10
and sal>=all(select sal from emp where deptno=10)) as temp);
-- 35. 30%。
select empno from emp
where sal >= all(select sal from emp);
update emp
set sal=sal*(1-0.3)
where empno in (select temp.empno from (select empno from emp
where sal >= all(select sal from emp)) temp);
select * from emp;
-- 36. ， ( >=3000 3 ， >2000 2 ， <=2000 1 )
-- ：case when ... then ... when ... then ... else ... end
select ename,
case when sal>3000 then '3 '
when sal>2000 then '2 '
else '1 '
end
from emp;