说明:
使用scott/tiger用户下的emp表和dept表完成下列练习,表的结构说明如下:
Alter user scott account unlock;
- emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
- dept部门表(deptno部门编号/dname部门名称/loc地点)
考试要求:
- 每个项目成员,2小时内上机操作完成;
- 每题写出标准的sql语句答案;
- 每题将语句运行后的slqplus界面文档粘贴到答卷中。
评分标准:
- 全部答案均得到正确结果的,文档格式合符标准的为正确。
- 每题分值为2,题选得分为:2分X45=90分;
- 文档提交合符文档标准的,为10分;合计得分为100分。
- 超时5分钟内扣5分,超时10分钟内扣10分,超时10分钟以上为0分
--1、选择部门30中的雇员
select *from t_dept where deptno=30;
--2、列出所有办事员(clerk)的姓名、编号和部门
select ename,empno,deptno from t_emp where job='CLERK';
--3、找出佣金(comm)高于薪金(sal)的雇员
select *from t_emp where comm > sal;
--4、找出佣金高于薪金60%的雇员
select *from t_emp where comm>sal*0.6;
--5、找出部门10中所有经理和部门20中的所有办事员的详细资料
select *from t_emp where job='MANAGE' and deptno=10
or job='CLERK' and deptno=20
--6、找出部门10中所有经理、部门20中所有办事员,既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select *from t_emp
where deptno=10 or deptno=20
and sal >= 2000;
--7、找出收取佣金的雇员的不同工作
select distinct job
from t_emp
where comm > 0;
--8、找出不收取佣金或收取的佣金低于100的雇员
select *from t_emp
where comm = 0 or comm < 100;
--9、找出各月最后一天受雇的所有雇员
select *from t_emp
where trunc(hiredate,'dd') = last_day(hiredate)
--10、找出早于25年之前受雇的雇员
select *from t_emp
where months_between(sysdate,hiredate) >25*12;
--11、显示只有首字母大写的所有雇员的姓名
select *from t_emp
where ename=nls_initcap(ename);
--12、显示正好为6个字符的雇员姓名
select ename from t_emp
where length(ename)=6;
--13、显示不带有'R'的雇员姓名
select ename from t_emp
where ename not like '%R%';
--14、显示所有雇员的姓名的前三个字符
select substr(ename,1,3)from t_emp;
--15、显示所有雇员的姓名,用a替换所有'A'
select TRANSLATE(ename,'A','a') from t_emp;
--16、显示所有雇员的姓名以及满10年服务年限的日期
select ename,hiredate
from t_emp
where to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') >=10;
--17、显示雇员的详细资料,按姓名排序
select *from t_emp
order by ename;
--18、显示雇员姓名,根据其服务年限,将最老的雇员排在最前面
select * from t_emp
order by hiredate asc;
--19、显示所有雇员的姓名、工作和薪金,按工作的降序顺序排序,而工作相同时按薪金升序
select ename,job,sal
from t_emp
order by job desc,sal asc;
--20、显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,将最早年份的项目排在最前面
select ename,to_char(hiredate,'yyyy') as year,to_char(hiredate,'MM') as month
from t_emp
order by month,year asc;
--21、显示在一个月为30天的情况下所有雇员的日薪金
select ename,hiredate,sal/30
from t_emp
where to_char(last_day(hiredate),'dd')=30;
--22、找出在(任何年份的)2月受聘的所有雇员
select *from t_emp
where to_char(hiredate,'MM')=2;
--23、对于每个雇员,显示其加入公司的天数
select ename,round(sysdate-hiredate) as day from t_emp;
--24、显示姓名字段的任何位置,包含 "A" 的所有雇员的姓名
select *from t_emp
where ename like '%A%';
--25、以年、月和日显示所有雇员的服务年限
select ename,round((sysdate-hiredate)/365) as year,
round((sysdate-hiredate)/30) as month,
round(sysdate-hiredate) as day
from t_emp;
--26、列出至少有一个雇员的所有部门
select distinct d.deptno,d.dname,d.loc
from t_dept d ,t_emp e
where d.deptno=e.deptno;
--27、列出薪金比"SMITH"多的所有雇员
select *from t_emp
where sal > (
select sal from t_emp
where ename='SMITH');
--28、列出所有雇员的姓名及其直接上级的姓名
select d.ename,b.ename as mgrname
from t_emp d left join t_emp b on d.mgr = b.empno
--29、列出入职日期早于其直接上级的所有雇员
1、查出所有雇员的入职时间以及上级入职时间
select d.ename,d.hiredate,b.ename mgrname,b.hiredate mgrdate
from t_emp d join t_emp b on d.mgr = b.empno;
2、select *from
(select d.ename,d.hiredate,b.ename mgrname,b.hiredate mgrdate
from t_emp d join t_emp b on d.mgr = b.empno) a
where a.hiredate < a.mgrdate;
--30、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select dname,ename from t_dept,t_emp
where t_dept.deptno = t_emp.deptno(+);
--31、列出所有“CLERK”(办事员)的姓名及其部门名称
select ename,dname
from t_emp,t_dept
where t_emp.job='CLERK' and t_emp.deptno = t_dept.deptno;
--32、列出各种工作类别的最低薪金,显示最低薪金大于1500的记录
select min(sal) minsal,job
from t_emp
group by job
having min(sal) > 1500;
--33、列出从事“SALES”(销售)工作的雇员的姓名,假定不知道销售部的部门编号
1、查找sales的部门编号
select deptno from t_dept
where dname='SALES';
2、select *from t_emp
where deptno = (
select deptno from t_dept
where dname='SALES');
--34、列出薪金高于公司平均水平的所有雇员
- 先查找出平均工资:select avg(sal) avgsal from t_emp;
2、 select *from t_emp
where sal>(select avg(sal) avgsal
from t_emp);
--35、列出与“SCOTT”从事相同工作的所有雇员
1、先找出SCOTT的工作
select job from t_emp
where ename='SCOTT';
2、select *from t_emp
where job=(select job from t_emp
where ename='SCOTT') and ename != 'SCOTT';
--36、列出某些雇员的姓名和薪金,条件是他们的薪金等于部门30中任何一个雇员的薪金
- 先找出30部门的薪金
select sal from t_emp
where deptno =30;
2、select *from t_emp
where sal in (select sal from t_emp
where deptno =30);
--37、列出某些雇员的姓名和薪金,条件是他们的薪金高于部门30中所有雇员的薪金
select *from t_emp
where sal > (select max(sal) from t_emp
where deptno =30);
--38、列出每个部门的信息以及该部门中雇员的数量
1、先找出所有部门所对应的员工数量
select deptno,count(ename) num
from t_emp
group by deptno;
2、select p.*,d.num
from t_dept p,
(select deptno,count(ename) num
from t_emp
group by deptno)d
where p.deptno = d.deptno (+);
--39、列出所有雇员的雇员名称、部门名称和薪金
select ename,dname,sal
from t_emp,t_dept
where t_emp.deptno = t_dept.deptno;
--40、列出从事同一种工作但属于不同部门的雇员的不同组合
- 先查出所有的
select ename,job,deptno from t_emp;
2、select distinct a.ename,a.job,a.deptno
from (select ename,job,deptno from t_emp)a,
(select ename,job,deptno from t_emp)b
where a.job = b.job and a.deptno != b.deptno;
--41、列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员
select a.*,b.num
from t_dept a,(select deptno,count(ename) num
from t_emp group by deptno)b
where a.deptno = b.deptno (+);
--42、列出各种类别工作的最低工资
select job, min(sal)
from t_emp
group by job;
--43、列出各个部门的MANAGER(经理)的最低薪金
select min(sal),deptno
from t_emp
where job='MANAGER'
group by deptno;
--44、列出按年薪排序的所有雇员的年薪
select ename,(sal+nvl(comm,0))*12 yearsal
from t_emp
order by yearsal;
--45、列出薪金水平处于第四位的雇员
select * from
(select d.*,row_number()over(order by d.sal desc) as grades from t_emp d)
where grades = 4;