hql和sql练习题

SQL与HQL练习题

要求，每一道题要写出SQL语句与HQL语句。

表结构定义：

员工表(emp){

   员工编号  empno

员工姓名  ename

职位  job

主管  mgr

参加工作时间  hiredate

薪水  sal

佣金  comm

所在部门编号  deptno

}

部门表(dept){

部门编号  deptno

部门名称  dname

备注  loc

}

第一部分

1. 选出部门30中的所有员工。

select * from emp where deptno = 30

from  Emp  e  where e.deptno = 30

2. 列出所有办事员(CLERK)的姓名、编号和部门编号。

select t.ename,t.empno,t.deptno from emp t where t.job = ‘CLERK’

select t.ename,t.empno,t.deptno from Emp t where t.job = ‘CLERK’

3. 找出佣金高于薪金的员工。

Select * from emp where comm>sal

From Emp where comm > sal

4. 找出佣金高于薪金的60%的员工。

Select * from emp where comm > (sal*0.6)

From Emp where comm > (sal*0.6)

5. 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料。

select * from emp where (job = 'MANAGER' and deptno  = 10) or (job = 'CLERK' and deptno  = 20);

From Emp where (job = 'MANAGER' and deptno  = 10) or (job = 'CLERK' and deptno  = 20);

6. 找出部门10中所有经理(MANAGER)，部门20中所有办事员(CLERK)，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。

select * from emp  where (job = 'MANAGER' and deptno  = 10) or (job = 'CLERK' and deptno  = 20)or(job <> 'CLERK' and job <> 'MANAGER' and sal >= 2000);

select * from Emp where (job = 'MANAGER' and deptno  = 10) or (job = 'CLERK' and deptno  = 20)or(job <> 'CLERK' and job <> 'MANAGER' and sal >= 2000);

7. 找出收取佣金的员工的不同工作。

select distinct t.job from emp t where t.comm > 0

select distinct t.job from Emp t where t.comm > 0

8. 找出不收取佣金或收取的佣金低于100的员工。

select * from emp t where t.comm <100 or t.comm is null 

select * from Emp t where t.comm <100 or t.comm is null 

9. 查询所有部门名称和员工姓名，包括没有员工的部门名称也显示。

select d.dname,e.ename from dept d  left join emp e on e.deptno = d.deptno;

select d.dname,e.ename from Dept d  left join Emp e with e.deptno = d.deptno;

10. 查询工资高于公司平均工资的所有员工信息。

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

from Emp t where t.sal in(select avg(e.sal) from Emp e)

11. 查询工资高于部门平均工资的所有员工。

select * from emp t where t.sal >(select avg(e.sal) from emp e where t.deptno = e.deptno)

from Emp t where t.sal >(select avg(e.sal) from Emp e where t.deptno = e.deptno)

12. 查询emp表的第1~3行。

select * from emp t where rownum <= 3 

session.createQuery(from Emp).setFirstResult(0).setMaxResults(3).list()

13. 把员工姓名和工作类型连接在一起，中间用“-”分割显示(concat函数)。

select CONCAT(ENAME||'-',JOB) from emp

select ename||’-’||job from Emp

14. 截取员工姓名的前3个字符和第4个字符以后的内容显示。

select substr(ENAME,0,3)||'-'||substr(ENAME,4) from emp

select substring(ename,0,3)||'-'||substring(ename,4) from Emp

15. 查询员工编号，姓名和所在部门的名称。

select EMPNO,ENAME,DNAME from emp t,dept d where t.deptno = d.deptno

select empno||’’||ename||’’||dname from Emp t,Dept d where t.deptno = d.deptno

16. 查询部门的名称以及该部门的人数，要求没有员工的部门也要显示。

select DNAME,count(e.EMPNO) from dept t left join emp e on t.deptno = e.deptno group by DNAME

select dname,count(e.empno) from Dept t left join Emp e on t.deptno = e.deptno group by dname

Sting = “i”

17. 查询员工姓名及其直接上级的姓名。

select e.ENAME,t.ENAME from emp e,emp t where e.mgr = t.empno

select e.ename||’的上级’||t.ename from Emp e,Emp t where e.mgr = t.empno

18. 查询工资高于平均工资的员工姓名。

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

select e.ename from Emp e where e.sal >(select avg(t.sal) from Emp t)

19. 查询工资高于本部门平均工资的员工。

select e.ENAME from emp e where e.SAL > (select avg(t.SAL) from emp t where e.DEPTNO = t.DEPTNO);

select e.ename from Emp e where e.sal> (select avg(t.sal) from Emp t where e.deptno = t.deptno)

20. 查询每个部门中拥有最高工资的员工的信息。

select e.ENAME from emp e where e.SAL = (select max(t.SAL) from emp t)

select e.ename from Emp e where e.sal = (select max(t.sal) from Emp t)

21. 统计“JONES”所带领的团队的工资总额。

select sum(e.SAL) from emp e where e.MGR = (select t.MGR from emp t where t.ENAME = 'JONES' )

select sum(e.sal) from Emp e where e.mgr= (select t.mgrfrom Emp t where t.ename = ‘JONES’ )

22. 列出10号部门中有而20号部门中没有的工作。

select e.job from emp e where e.deptno = 10 and e.job not in(select t.job from emp t where t.deptno = 20) 

select e.job from Emp e where e.deptno = 10 and e.job not in(select t.job from Emp t where t.deptno = 20) 

23. 记录并集(union)

select * from emp where EMPNO = 7369 union select * from emp where EMPNO = 7499

select * from Emp where EMPNO = 7369 union select * from Emp where EMPNO = 7499

第二部分

1. 查询津贴为空的所有员工。

select * from emp where comm is null

from Emp where comm is null

2. 查询姓名以字母“S”开头的员工。

select * from emp where ename like 'S%'

from Emp where ename like ‘S%’

3. 查询所有的经理(MANAGER)和办事员(CLERK)的员工信息。

select * from emp where job = 'CLERK' or job = 'MANAGER'

from Emp where job =’CLERK’ or job = ‘MANAGRE’

4. 按参加工作时间从早到晚显示员工信息。

select * from emp order by hiredate asc

from Emp order by hiredate asc

5. 增加一个部门。

insert into dept values(50,'PRODUCT','HK')

Dept dept = new Dept();

dept.setDeptno(50);

dept.setDname(“PRODUCT”);

dept.setLoc(“HK”);

....

session.save(dept);

....

6. 给员工涨工资5%。

update emp set sal = sal*(1+0.05)

update Emp set sal = sal*(1+0.05)

7. 删除新增的部门“培训部”。

delete from emp where dname = ‘培训部’

delete from Emp where dname = ‘培训部’

8. 查询系统时间。

select systimestamp  from dual 

取new Date()就可以，不需要做hql查询

9. 查询在20年前参加工作的员工。

select * from emp t where t.hiredate <(select  sysdate - interval '20' year   from dual)

10. 查询在当月倒数第三天参加工作的员工。

select * from emp where last_day(hiredate)-2 = hiredate

11. 查询每个员工的工作天数。

 select sysdate-hiredate as days from emp

12. 显示系统时间是xxxx年xx月xx日，是一年中的第几天，是星期几。

select  to_char(sysdate,'day')  from  dual;   

13. 按每月30天计算员工的日薪金，要求计算结果四舍五入到小数点后2位。

select  trunc(t.SAL/30,2)  from emp t

select  trunc(t.sal/30,2)  from Emp t

14. 计算每个员工已经工作了多少个月，要求忽略小数部分。

select trunc((sysdate-hiredate)/30) from emp 

select trunc((sysdate-hiredate)/30) from Emp 

15. 按年和月的格式显示员工参加工作的时间。

select to_char(hiredate,'yyyy/mm') from emp

select date_format(hiredate,’%Y/%m’) from Emp

16. 查询在1987年2月到1987年5月之间(包括2月和5月)参加工作的员工。

select * from emp t where t.hiredate between to_date('1987/2','yyyy/mm') and to_date('1987/5','yyyy/mm')

from Emp t where t.hiredate between 

17. 统计员工的人数，津贴不为空的人数。

select * from emp t where t.comm is not null

from Emp t  where t.comm is not null

18. 统计部门最低工资大于900的部门和最低工资。

select a.deptno, (select min(sal) from emp where deptno=a.deptno) minSal 

from emp a group by a.deptno

select a.deptno, (select min(sal) from Emp where deptno=a.deptno) minSal 

from Emp a group by a.deptno

19. 统计每个部门工资在1400元以上的所有员工的工资总额。

select e.deptno,(select sum(sal) from emp where deptno = e.deptno) as sumsal from emp e  where e.sal > 1400 group by deptno

select e.deptno,(select sum(sal) from Emp where deptno = e.deptno)  from Emp e  where e.sal > 1400 group by deptno

20. 统计不同工作的个数。

select count(distinct job) from emp 

select count(distinct job) from Emp 

21. 找出各月倒数第3天受雇的所有员工。

select * from emp  where hiredate = last_day(hiredate)-2 

22. 找出早于12（30）年前受雇的员工。

select * from emp where hiredate < (select  sysdate - interval '30' year from dual)

23. 以首字母大写的方式显示所有员工的姓名。

select INITCAP(ename) from emp 

select upper(substring(ename,0,1))||substring(ename,1) from Emp 

24. 显示正好为5个字符的员工的姓名。

select ename from emp where LENGTH(ename)= 5

select ename from Emp where length(ename)= 5

25. 显示不带有“R”的员工的姓名。

select ename  from emp where ename not like '%R%'

select ename  from Emp where ename not like '%R%'

26. 显示所有员工姓名的前三个字符。

select substr(ename,0,3) from emp 

select substring(ename,0,3) from Emp

27. 显示所有员工的姓名，用“a”替换所有“A”。

select replace(ename,'A','a') from emp 

28. 显示满10(30)年服务年限的员工的姓名和受雇日期。

select hiredate from emp where hiredate < (select  sysdate - interval '30' year from dual)

29. 显示员工的详细资料，按名称排序。

select * from emp order by ename

from Emp order by ename

30. 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。

select ename,hiredate from emp order by hiredate asc

select ename,hiredate from Emp order by hiredate asc

31. 显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序。

select ename,job,sal from emp order by job desc,sal desc

select ename,job,sal from Emp order by job desc,sal desc

32. 显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。

select ename,to_char(hiredate,'yyyy/mm')from emp order by to_char(hiredate,'mm'),to_char(hiredate,'yyyy') asc

33. 显示在一个月为30天的情况所有员工的日薪金，忽略余数。

select ename,floor(sal/30) from emp where to_char(last_day(hiredate),'dd') = 30

34. 找出在(任何年份的)2月受聘的所有员工。

select * from emp where to_char(hiredate,'mm') = 2

35. 对于每个员工显示其加入公司的天数。

select sysdate - hiredate from emp 

36. 显示姓名字段的任何位置包含“A”的所有员工的姓名。

select ename from emp where ename like '%A%'

select ename from Emp where ename like '%A%'

37. 以年月日的方式显示所有员工的服务年限(大概)。

select floor((sysdate-hiredate)/365)||'年'||floor((mod((sysdate-hiredate)/365,1)*12))||'月'||floor(mod((mod((sysdate-hiredate)/365,1)*12),1)*30)||'日' from emp 

38. 列出至少有一个员工的所有部门信息。

select * from dept d where d.deptno in(select deptno from emp) 

select * from Dept d where d.deptno in(select deptno from Emp) 

39. 列出薪金比“SMITH”多的所有员工。

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

select * from Emp where sal > (select sal from Emp where ename = 'SMITH')

40. 列出所有员工的姓名及其直接上级的姓名。

select ename,(select ename from emp e where  e.empno = t.mgr) as mgrname from emp t 

select ename,(select ename from Emp e where  e.empno = t.mgr) as mgrname from Emp t 

41. 列出受雇日期早于其直接上级的所有员工。

select ename from emp t where hiredate < (select hiredate from emp e where empno = t.mgr)

select ename from Emp t where hiredate < (select hiredate from Emp e where empno = t.mgr)

42. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

select d.dname,e.ename from dept d left join emp e on d.deptno = e.deptno

select d.dname,e.ename from Dept d left join Emp e on d.deptno = e.deptno

43. 列出所有“CLERK”(办事员)的姓名及其部门名称。

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

select e.ename,(select dname from Dept d where d.deptno = e.deptno) from Emp e where e.job = 'CLERK'

44. 列出最低薪金大于1500的各种工作。

select job from emp e where(select min(sal) from emp t where t.job = e.job)>1500 group by e.job

select job from Emp e where(select min(sal) from Emp t where t.job = e.job)>1500 group by e.job

45. 列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。

select ename from emp where deptno = (select deptno from dept where dname = 'SALES')

select ename from Emp where deptno = (select deptno from Dept where dname = 'SALES')

46. 列出与“SCOTT”从事相同工作的所有员工。

select * from emp  e where e.job = (select t.job from emp t where ename = 'SCOTT') and ename != 'SCOTT'

select * from Emp  e where e.job = (select t.job from Emp t where ename = 'SCOTT') and ename != 'SCOTT'

47. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

select e.ename,e.sal from emp e where e.sal in (select sal from emp t where t.deptno = 30)

select e.ename,e.sal from Emp e where e.sal in (select sal from Emp t where t.deptno = 30)

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

select e.ename,e.sal from emp e where e.sal > (select max(sal) from emp t where t.deptno = 30)

select e.ename,e.sal from Emp e where e.sal > (select max(sal) from Emp t where t.deptno = 30)

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

select deptno ,count(*) as employeeNum,avg(sal) as avgSal,avg(sysdate-hiredate) as workDays from emp group by deptno 

50. 列出所有员工的姓名、部门名称和工资。

select e.ename,(select d.dname from dept d where d.deptno = e.deptno) as deptName ,e.sal from emp e;

select e.ename,(select d.dname from Dept d where d.deptno = e.deptno),e.sal from Emp e;

51. 列出所有部门的详细信息和部门人数。

select d.deptno,d.dname,d.loc,(select count(*) as empnum from emp  e where e.deptno = d.deptno) from dept d 

select d.deptno,d.dname,d.loc,(select count(*) from Emp  e where e.deptno = d.deptno) from Dept d 

52. 列出各种工作的最低工资。

select job,min(sal) from emp group by job 

select job,min(sal) from Emp group by job 

53. 列出各个部门的MANAGER(经理)的最低薪金。

select e.deptno,(select min(t.sal) from emp t where  t.deptno = e.deptno) from emp e group by e.deptno

select e.deptno,(select min(t.sal) from Emp t where  t.deptno = e.deptno) from Emp e group by e.deptno

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

select sal*365 from emp order by sal desc

select sal*365 from Emp order by sal desc

55. 列出工资最高的员工信息。

select * from emp where sal = (select max(sal) from emp )

select * from Emp where sal = (select max(sal) from Emp )