SQL经典18题(不知道经不经典)
来源:互联网 发布:华丽上班族电影知乎 编辑:程序博客网 时间:2024/06/05 02:03
表结构
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.empnowhere e1.hiredate>e2.hiredate;子查询:select ename from emp awhere 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 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同。
SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');
interval可是:
秒 SECONDS
分钟 MINUTES
小时 HOURS
天 DAYS
月 MONTHS
年 YEARS
2.MySql下的建表语句和初始化数据
CREATE TABLE EMP (EMPNO int auto_increment, ENAME VARCHAR(10), JOB VARCHAR(9), MGR int, HIREDATE DATETIME, SAL int, COMM int, DEPTNO int, CONSTRAINT PK_EMP PRIMARY KEY (EMPNO) ); CREATE TABLE DEPT (DEPTNO int auto_increment, DNAME VARCHAR(14), LOC VARCHAR(13), CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) );alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT(DEPTNO);Insert into DEPT (DEPTNO,DNAME,LOC) values (1,'ACCOUNTING','NEW YORK');Insert into DEPT (DEPTNO,DNAME,LOC) values (2,'RESEARCH','DALLAS');Insert into DEPT (DEPTNO,DNAME,LOC) values (3,'SALES','CHICAGO');Insert into DEPT (DEPTNO,DNAME,LOC) values (4,'OPERATIONS','BOSTON');Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,2);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,3);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,3);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,2);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,3);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,null,3);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,null,1);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'1981-4-19',3000,null,2);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,1);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,3);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'1987-5-23',1100,null,2);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'1981-12-3',950,null,3);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'1981-12-3',3000,null,2);Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'1982-1-23',1300,null,1);
1 0
- SQL经典18题(不知道经不经典)
- 经典SQL题6
- 经典SQL题4
- 经典SQL题3
- 经典SQL题2
- 经典SQL题1
- 经典SQL题5
- sql经典题
- sql 经典题
- 经典sql
- sql 经典
- sql 经典
- sql 经典
- sql 经典
- 经典sql
- sql 经典
- 经典sql
- 经典sql
- Yii Framework 开发教程(13) UI 组件 ContentDecorator示例
- IOS 发送邮件
- Yii Framework 开发教程(14) UI 组件 MaskedTextField示例
- IOS学习笔记——UITableView(一)
- Yii Framework 开发教程(15) UI 组件 MultiFileUpload示例
- SQL经典18题(不知道经不经典)
- i++ 和 ++i 效率的分析以及自定义类型的自增/自减运算符重载实例
- Yii Framework 开发教程(16) UI 组件 StarRating示例
- svn: Server sent unexpected return value (403 Forbidden) in response to CHECKOUT request for-解决方法
- 怎样提高WebService性能(大数据量网络传输处理)
- 小波变换 完美通俗解读【转载】
- 网络视频监控P2P解决方案
- Vector作为返回值时出现的问题
- 利用格点证明恒等式