SQL数据库常见基础问题集及备注

目录

对象

问题集：

--01 列出至少有3个员工的所有部门名字

--02列出薪资比‘SMITH’多的所有员工，这里用到了子查询（即子查询SMITH的工资）

--03列出所有员工的姓名及其直接上级的姓名

--04列出受雇日期早于其直接上级的员工

--05列出部门名称和这些部门的员工信息，包括那些没有员工的部门

--06列出所有job为clerk的员工姓名及其部门名称

--07列出最低薪资大于1500的各种工作

--08列出在SALES部门工作的员工姓名，假定不知道销售部的部门编号

--09列出薪资高于公司平均薪资的所有员工

--10列出与员工'SCOTT'从事相同工作的人

--11列出薪资等于30部门员工薪资的所有员工的姓名和薪资,

--12列出薪资比30部门所有员工的薪资都高的员工及其薪资

--13列出在每个部门工作的员工数量、平均工资和平均服务期限

--14列出所有员工的姓名、部门名称和工资

--15列出从事同一种工作但属于不同部门的员工的一种组合,

--16列出所有部门的详细信息

--17列出各种工作的最低工资

--18列出各部门的经理的最低薪资

--19列出所有员工的年薪(含津贴)，按年薪从低到高排列,

--20列出职位为clerk的员工平均工资

--21列出职位为clerk的平均工资,按部门进行分组

--22列出职位为clerk的平均工资,按部门进行分组 并且部门编号in(20,30) 且部门员工人数>=2 按照平均工资进行降序排列,

对象

scott.sql文件-其它文档类资源-CSDN文库

本问题集基于表

dept表：

DNAME	LOC	DEPTNO
ACCOUNTING	NEW YORK	10
RESEARCH	DALLAS	20
SALES	CHICAGO	30
OPERATIONS	BOSTON	40

emp表：

salgrade表：

 

 

问题集：

--01 列出至少有3个员工的所有部门名字

--分析：emp表中有部门的号码以及详细数据、dept表中有部门的号码以及名字，因此需要通过部门号码先将他们连接起来；然后需要将他们分好组；最后用count统计分好组的数据并与3比较。（注意：分组后用having进行组级过滤）

select d.dname from emp e join dept d on(e.deptno = d.deptno) group by d.dname having count(d.dname) >= 3;

--02列出薪资比‘SMITH’多的所有员工，这里用到了子查询（即子查询SMITH的工资）

select * from emp e where sal > (select sal from emp where ename = 'SMITH');

--03列出所有员工的姓名及其直接上级的姓名

select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;

--04列出受雇日期早于其直接上级的员工

select e.ename 员工,m.ename 上级,e.hiredate 员工入职日期,m.hiredate 其上级入职日期 from emp e,emp m where e.mgr = m.empno and m.hiredate > e.hiredate;

--05列出部门名称和这些部门的员工信息，包括那些没有员工的部门

select d.dname,e.ename from emp e right join dept d using(deptno);

--06列出所有job为clerk的员工姓名及其部门名称

--using(deptno)和where(e.deptno=d.deptno)同义

select d.dname,e.ename from emp e join dept d using(deptno) where e.job = 'CLERK';

select d.dname,e.ename from emp e join dept d where(e.deptno=d.deptno) and e.job like 'CLERK';

--07列出最低薪资大于1500的各种工作

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

--08列出在SALES部门工作的员工姓名，假定不知道销售部的部门编号

select e.ename from emp e join dept d using(deptno) where d.dname = 'sales';

--09列出薪资高于公司平均薪资的所有员工

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

--10列出与员工'SCOTT'从事相同工作的人

select * from emp e where job = (select job from emp where ename = 'scott') and ename<>'scott';

--11列出薪资等于30部门员工薪资的所有员工的姓名和薪资,

当有多个目标时不可以只用“=”，可用“= any”后者“in”

select * from emp where sal = any(select sal from emp where deptno = 30) and deptno <> 30;

select * from emp where sal in (select sal from emp where deptno = 30) and deptno <> 30;

--12列出薪资比30部门所有员工的薪资都高的员工及其薪资

select * from emp where sal > all(select sal from emp where deptno = 30) and deptno <> 30;

--13列出在每个部门工作的员工数量、平均工资和平均服务期限

select deptno,count(ename),avg(sal),avg(TO_DAYS(now()-hiredate)) from emp group by deptno;

--这里其实缺少没有员工的40部门，为统计到40部门，故连接dept表

select deptno,count(ename),avg(sal),avg(TO_DAYS(now()-hiredate)) from emp right join dept d using(deptno) group by deptno;

--14列出所有员工的姓名、部门名称和工资

select ename 姓名,deptno 部门名称,sal 工资 from emp right join dept using(deptno);

--15列出从事同一种工作但属于不同部门的员工的一种组合,

select e.empno,e.ename,m.empno,m.ename from emp e ,emp m where e.job = m.job and e.deptno<>m.deptno;

这种情况下其实还有重复计算的，比如miller&smith以及smith&miller两种形式都被统计到了，避免这种情况只需要将e.deptno<>m.deptno改为e.deptno > m.deptno或e.deptno < m.deptno 就好了，这样就避免了重复统计的问题

select e.empno,e.ename,m.empno,m.ename from emp e ,emp m where e.job = m.job and e.deptno<m.deptno;

--16列出所有部门的详细信息

select * from emp e join dept d using(deptno) group by d.dname;

--17列出各种工作的最低工资

select job,min(sal) 最低工资 from emp group by deptno;

--18列出各部门的经理的最低薪资

select min(sal) from emp where job = 'manager' group by deptno;

--19列出所有员工的年薪(含津贴)，按年薪从低到高排列,

排序（order by sal/order by sal desc），

为避免comm为空的情况，可加一个ifnull(comm,0)，

ifnull(a,b)用法：不为空返回a，为空返回b

这里2代指(sal+ifnull(comm,0))*12；若1就指ename

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

--20列出职位为clerk的员工平均工资

select avg(sal) from emp e where  e.job = 'clerk';

--21列出职位为clerk的平均工资,按部门进行分组

select deptno,avg(sal) from emp e where e.job = 'clerk' group by deptno;

--22列出职位为clerk的平均工资,按部门进行分组 并且部门编号in(20,30) 且部门员工人数>=2 按照平均工资进行降序排列,

in(10,30)指得是只有10、30

select deptno,avg(sal) from emp e where e.job = 'clerk' and deptno in (20,30) group by deptno having count(empno) >=2 order by avg(sal) desc;