2018-12-24 MySQL练习

2018-12-24 MySQL练习

select ename,hiredate
from emp
where hiredate = (
select min(hiredate)
from emp)

select ename,sal,dname,loc
from emp e join dept d
on e.deptno = d.deptno
where loc = 'chicago'
      and sal >(select sal
								from emp
								where ename = 'smith')


select ename,hiredate
from emp 
where hiredate < (select min(hiredate)
									from emp
									where deptno = 20)


select e.deptno,dname,count(*)
from emp e join dept d
on e.deptno = d.deptno
group by e.deptno 
having count(*) > (SELECT avg(num)
										from (
												SELECT count(*) num
												from emp
												group by deptno
												) a)


select EMPNO,ename,sal
from emp
where sal >(select avg(sal)
						from emp)

select EMPNO,ename,sal
from emp e join (select job,avg(sal) s
									from emp
									group by job) a
on e.job = a.job
where e.sal > a.s


SELECT empno,ename,job,sal
from emp
where deptno <> 10 and sal > any (select sal from emp where deptno=10)


select ename,hiredate
from emp
where deptno <>10
  and hiredate > any(select hiredate from emp
                        where deptno = 10)


select ename,hiredate
from emp
where deptno <> 10
  and hiredate > all(select hiredate from emp where deptno = 10)

select distinct job
from emp
where deptno <> 10
  and job in(select job from emp where deptno =10)



select empno,ename,sal
from emp
where (deptno,sal) in (select deptno,min(sal)
                       from emp 
                       group by deptno)
   and deptno is not null


select ename,sal,e.deptno,s
from emp e join
							(select deptno,avg(sal) s
								from emp
								where deptno is not null
								group by deptno) a
on e.deptno = a.deptno
where sal > s

#1.查询部门平均工资在2500元以上的部门名称及平均工资。
select dname,s
from dept d join (select deptno,avg(sal) s
									from emp
									group by deptno ) a
on d.deptno = a.deptno
where s > 2500

select dname,avg(sal) s
from emp e join dept d
on e.deptno = d.deptno
group by dname
having s >2500



#2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select job,avg(sal) s
from emp
group by job
having s >2500 and job not like 'sa'
order by s desc

#3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

#4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。 

select job,sum(sal)
from emp
group by job
having sum(sal)>=2500
#5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，
#  不包括最低工资小于3000的，按最低工资由高到低排序。

select m.empno,m.ename,min(e.sal) m
from emp e left join emp m
on e.mgr = m .empno
group by m.empno
having m >= 3000

#6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

SELECT *
from emp
where sal>(select sal
						from emp 
						where empno = 7782)
  and job in(select job
						from emp
						where empno = 7369)


#7.查询工资最高的员工姓名和工资。 
select ename,sal
from emp
where sal = (select max(sal)
							from emp )


#8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select min(sal),dname,e.deptno
from emp e join dept d
on e.deptno = d.deptno
group by e.deptno
having min(sal) >(SELECT min(sal)
									from emp 
									group by deptno
									having deptno = 10)

#9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
select empno,ename,sal
from emp e join (select deptno,min(sal) m
									from emp
									group by deptno) a
on e.deptno = a.deptno
where sal = m

#10.显示经理是KING的员工姓名，工资。
select e.ename,e.sal
from emp e join emp m
on e.mgr = m.empno
where m.ename = 'KING'

#11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

select ename,sal,hiredate
from emp
where hiredate <(select hiredate
									from emp
									where ename = 'SMITH')

#12.使用子查询的方式查询哪些职员在NEW YORK工作。
select *
from emp e join (select deptno
								from dept 
								where loc = 'NEW YORK') a
on e.deptno = a.deptno

#13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
select ename,hiredate
from emp
where deptno =(select deptno
							 from emp
							where ename = 'SMITH')
 and ename <> 'SMITH'

#14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
select empno,ename
from emp
where sal >(select avg(sal)
						from emp)

#15. 显示部门名称和人数
select dname,count(*)
from emp e right join dept d
on e.deptno = d.deptno
group by e.deptno


#16. 显示每个部门的最高工资的员工
select *
from emp e join (select deptno,max(sal) m
									from emp 
									group by deptno ) a
on e.deptno = a.deptno
where sal = m

#17. 显示出和员工号7369部门相同的员工姓名，工资
SELECT ename,sal
from emp
where deptno = (select deptno
								from emp 
								where empno = '7369')

#18. 显示出和姓名中包含“W”的员工相同部门的员工姓名
select ename
from emp 
where deptno =(select deptno
								from emp
								where ename like '%W%')