当前位置：首页 -> AI编程基础 -> 数据库编程

SQL经典18题(不知道经不经典)(一)

2014-11-24 13:29:11 · 作者: · 浏览: 1
标签: SQL 经典 不知 道经

表结构

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

说明：

工资 ＝ 薪金 ＋ 佣金没有说明，sql语句在 Oracle、MySql下通用。如果使用Oracle练习，请用Scott账户登录，该账户下有这两张表

题目

1．列出至少有一个员工的所有部门

select deptno,dname from dept where deptno in (select deptno from emp group by deptno having count(*)>0);

2．列出薪金比“SMITH”多的所有员工。 
select * from emp where sal>(select sal from emp where ename='SMITH');
3．列出所有员工的姓名及其直接上级的姓名。 
select e1.ename '员工', e2.ename '上级' from emp e1 left join emp e2 on e1.mgr=e2.empno;

select ename,(select ename from emp where empno=a.mgr) from emp a;

4．列出受雇日期晚于其直接上级的所有员工。 
连接查询：
select e1.ename,e1.hiredate,e2.hiredate 
from emp e1 left join emp e2 on e1.mgr=e2.empno
where e1.hiredate>e2.hiredate;
子查询：
select ename from emp a
where hiredate>(select hiredate from emp where empno=a.mgr);

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 
select d1.dname,e1.ename from emp e1 right join dept d1 on e1.deptno=d1.deptno order by d1.dname;

6．列出所有“CLERK”（办事员）的姓名及其部门名称。 
select ename,(select dname from dept where deptno=e1.deptno) deptname from emp e1 where job='CLERK';

select dname,ename from dept a,emp b where a.deptno=b.deptno and job='CLERK';

7．列出最低薪金大于1500的各种工作。 
select job,min(sal) minSal from emp group by job having min(sal)>1500;

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 
select ename from emp where deptno =(select deptno from dept where dname='SALES');

9．列出薪金高于公司平均薪金的所有员工。 
select ename from emp where sal>(select avg(sal) from emp);

10．列出与“SCOTT”从事相同工作的所有员工。 
select ename from emp where job=(select job from emp where ename='SCOTT');

11．列出薪金等于部门3中员工的薪金的所有员工的姓名和薪金。 
select ename,sal from emp where sal in(select sal from emp where deptno=3);

select ename,sal from emp where sal = any(select sal from emp where deptno=3);

12．列出薪金高于在部门3工作的所有员工的薪金的员工姓名和薪金。 
select ename,sal from emp where sal>all(select sal from emp where deptno=3);
select ename,sal from emp where sal>(select max(sal) from emp where deptno=3);

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 
--下面是MySql下sql语句
--DATEDIFF()函数用来计算天数、TIMESTAMPDIFF()函数最后会介绍
select deptno,count(empno),avg(sal) avgSal,avg(DATEDIFF(now(),hiredate)) avgday from emp group by deptno;
select deptno,count(empno),avg(sal) avgSal,avg(TIMESTAMPDIFF(DAY,hiredate,now())) avgday from emp group by deptno;
--下面是Oracle下sql语句
select deptno,count(*),trunc(avg(sal)) avgsal,round(avg(MONTHS_BETWEEN(sysdate,hiredate)),2) avgday from emp group by deptno;
14．列出所有员工的姓名、部门名称和工资。 
--下面是MySql下sql语句
select e.ename,d.dname,(e.sal+IFNULL(e.comm,0)) sal from emp e join dept d on e.deptno=d.deptno;
--下面是Oracle下sql语句<
select ename,dname,sal+nvl(comm,0) from emp,dept where emp.deptno=dept.deptno;

15．列出所有部门的详细信息和部门人数。 
select d.*, count(empno) from emp e right join dept d on e.deptno=d.deptno group by deptno;
select a.*,(select count(*) from emp where deptno=a.deptno) tot from dept a ;

16．列出各种工作的最低工资。 
--下面是MySql下sql语句
select job,min(sal+ifnull(comm,0)) from emp group by job;
--下面是Oracle下sql语句
select job,min(sal+nvl(comm,0)) from emp group by job;

这样写有问题

select job,min(nvl(sal+comm,0)) from emp group by job;

17．列出MANAGER（经理）的最低薪金。 
select min(sal) from emp where job='MANAGER';

18．列出所有员工的年工资,按年薪从低到高排序。 
select ename,12*(sal+ifnull(comm,0)) ansal from emp order by ansal;

附录

1.TIMESTAMPDIFF函数用法

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
说明:
返回日期或日期时间表达式datetime_expr1 和d