python数据库的题库_[python全栈]—数据库习题及答案

python全栈-MYSQL—数据库习题

1.列出至少有四个员工的所有部门信息

查询下每个部门的人数

select deptno,count() from emp group by deptno

至少四位

select deptno,count() from emp group by deptno having count()>4

部门信息

select d.,dd.cou

from dept d,(select deptno,count() cou from emp group by deptno having count()>4) dd

where d.deptno = dd.deptno

2.列出薪资比’smith’多的员工信息

查询Smith的薪资多少

select sal from emp where ename = ‘smith’

查询比子查询结果工资高的雇员信息

select * from emp where sal >(select sal from emp where ename = ‘smith’)

3.列出所有员工的姓名及其直接上级的姓名，没有领导的雇员也列出

select e.ename,m.ename

from emp e left join emp m

on e.mgr=m,empno

4.列出受雇日期早于其直接上级的所有员工编号/姓名/部门名称

select e.ename,e.empno

from emp e join emp m on e.mgr=m.empno

where e.hiredate部门名称。连接部门表

select e.ename,e.empno,d.dname

from emp e join emp m on e.mgr=m.empno

join dept d on e.deptno=d.deptno

where e.hiredate5.列出所有‘clerk’的姓名及部门名称、部门人数

列出所有clerk的姓名

select ename from emp where job;

部门人数

select count(),deptno from emp group by deptno

select d.dname,dd.cou,e.ename

from dept d join (select count() cou,deptno from emp group by deptno) dd on d.deptno = dd.deptno

join emp e on d.deptno = e.deptno

where job = ‘clerk’;

6.列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数

最低薪金大于1500的各种工作

select job from emp group by job having min(sal)>1500

每个工作的雇员人数

select count() from emp group by job;

select count() from emp

where job in (select job from emp group by job having min(sal)>1500)

group by job

7.列出在部门sales工作的员工的姓名，假定不知道销售部的部门编号

部门销售部的部门号

select deptno from dept where dname = ‘sales’

查询子查询结果的雇员姓名

select ename

from emp

where deptno=(select deptno from dept where dname = ‘sales’)

8.列出薪资高于公司平均薪金的所有员工，所在部门，上级领导

薪资高于公司平均薪金的所有员工

select e.*

from emp e

where sal>(select avg(sal) from emp)

连接部门表

select e.*

from emp e,join dept d on e.deptno = d.deptno

where sal>(select avg(sal) from emp)

上级领导连接emp

select e.,d.

from emp e join dept d on e.deptno = d.deptno

join emp m on e.mgr = m.empno

where e.sal>(select avg(sal) from emp);

9.列出与‘smith’从事相同工作的所有员工及部门名称

scott’从事相同工作

select job from emp where ename = ‘smith’

与‘smith’从事相同工作的所有员工

select e.*

from emp e

where job =(select job from emp where ename = ‘smith’) and e.ename != ‘smith’

连接部门表查询部门名称

select e.,d.dname

from emp e join dept d on e.deptno =d.deptno

where job =(select job from emp where ename = ‘smith’) and e.ename != ‘smith’

10.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金/部门

部门30工作的薪资

select sal from emp where deptno=30;

高于在部门30工作的所有员工的薪金

select e.ename,e.sal

from emp e

where e.sal>(select sal from emp where deptno=30)

部门

select e.ename,e.sal,d.dname

from emp e join dept d on e.deptno = d.deptno

where e.sal>(select sal from emp where deptno=30)

11.列出在每个部门工作的员工数量/部门名称平均工资和平均服务期限

服务天数 datadiff

select datediff(sysdate(),hiredate) /365 from emp

部门表和雇员表连表查询

select avg(sal),d.dname,count(),avg( datediff(sysdate(),hiredate) /365)

from dept d join emp e on d.deptno=e.deptno

group by d.dname

12.列出所有员工的年工资，按年薪从低到高排序

所有员工的年工资

select (sal+ifnull(comm,0))*12 yearsal from emp

按年薪从低到高排序

select (sal+ifnull(comm,0))*12 yearsal from emp order by yearsal

13。求出部门名称中，带‘S’字符的部门员工的工资合计工资/部门人数

部门名称中，带‘S’字符的部门号

select deptno from dept where dname like ‘%s%’

工资合计工资/部门人数

select sum(sal),count() from emp group by deptno

select sum(sal),count() from emp

where deptno in (select deptno from dept where dname like ‘%s%’)

group by deptno

select d.*,dd.cou

from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>4) dd

where d.deptno = dd.deptno;

select ename from emp where job = 'clerk';

select count(*),deptno from emp group by deptno;

select d.dname,dd.cou,e.ename

from dept d join (select count(*) cou,deptno from emp group by deptno) dd on d.deptno = dd.deptno

join emp e on d.deptno = e.deptno

where job = 'clerk';

select job from emp group by job having min(sal)>150;

select count(*) from emp group by job;

select count(*) ,job from emp

where job in (select job from emp group by job having min(sal)>150)

group by job ;

select deptno from dept where dname = 'sales';

select ename

from emp

where deptno=(select deptno from dept where dname = 'sales');

select e.*

from emp e

where sal>(select avg(sal) from emp);

select e.*,d.*

from emp e join dept d on e.deptno = d.deptno

join emp m on e.mgr = m.empno

where e.sal>(select avg(sal) from emp);

select e.*

from emp e

where job =(select job from emp where ename = 'smith') and e.ename != 'smith'

select e.*,d.dname

from emp e join dept d on e.deptno =d.deptno

where job =(select job from emp where ename = 'smith') and e.ename != 'smith'

select e.ename,e.sal,d.dname

from emp e join dept d on e.deptno = d.deptno

where e.sal>(select sal from emp where deptno=30);

select avg(sal),d.dname,count(*),avg( datediff(sysdate(),hiredate) /365)

from dept d join emp e on d.deptno=e.deptno

group by d.dname

select (sal+ifnull(comm,0))*12 yearsal from emp;

select (sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;

select sum(sal),count(*) ,deptno from emp

where deptno in (select deptno from dept where dname like '%s%')

group by deptno;