SQL与HQL练习题

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

表结构定义：

员工表(emp){

员工编号empno

员工姓名ename

性别sex

职位job

主管mgr

参加工作时间hiredate

薪水sal

佣金comm

所在部门编号deptno

}

部门表(dept){

部门编号deptno

部门名称dname

备注loc

}

第一部分

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

S: Select * from emp where deptno=’30’;

H: from Emp where deptno=’30’;

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

S: select ename,empno,deptno from emp where job=’CLERK’;

H: select new Map(ename as name,empno as eno, deptno.deptno as dno) from Emp where job='CLERK';

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

S: SELECT * FROM emp where comm > sal or (sal is NULL and comm is not null and comm !=0)

H: FROM Emp where comm > sal

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

S: select * from emp where comm>sal*0.6 OR (sal IS NULL and comm is not null and comm !=0);

H: from Emp where comm>0.6*sal OR sal IS NULL;

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

料。

S: select * from emp where (job ='MANAGER' and deptno = 10) or (job =

'CLERK' and deptno=20);

H: from Emp where (job ='MANAGER' and deptno = 10) or (job = 'CLERK' and deptno=20);

6.找出部门10中所有经理(MANAGER)，部门20中所有办事员(CLERK)，既不是

经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。

S: select * from emp e where (job = 'MANAGER' and deptno = 10) or (job = 'CLERK' and deptno = 20) or (sal >= 2000 and job not in ('MANAGER','CLERK'));

H: from Emp where (job = 'MANAGER' and deptno = 10) or (job = 'CLERK' and deptno = 20) or (sal >= 2000 and job not in ('MANAGER','CLERK'));

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

S: select distinct job from emp where comm is not null ;

H: select distinct job from Emp where comm != 'null' ;

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

S: select * from emp where comm <1000 orcomm is null

H: from Emp where comm<1000;

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

S: select d.dname ,e.ename from emp e RIGHT JOIN dept d ON

e.deptno=d.deptno;

H: select new map(d.dname as dname ,e.ename as ename) from Emp e RIGHT JOIN e.deptno d

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

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

H: from Emp where sal > (select avg(sal) from Emp;

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

S: select * from emp e1 where sal >(select avg(sal) from emp e2 where e1.deptno=e2.deptno)

H: from Emp e1 where sal >(select avg(sal) from Emp e2 where e1.deptno=e2.deptno)

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

S: select * from emp LIMIT 3

H: List elist =session.createQuery("from Emp").setMaxResults(3).list(); 13. 把员工姓名和工作类型连接在一起，中间用“-”分割显示(concat函数)。

S: select CONCAT_WS('-',ename,job) from emp;

H:

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

S: select CONCAT(substr(ename,1,3),substr(ename,5))from emp;

H: select CONCAT(substr(ename,1,3),substr(ename,5))from Emp;

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

S: SELECT empno ,ename ,dname FROM emp e ,dept d where e.deptno =

d.deptno

H: SELECT empno ,ename ,dname FROM Emp e ,dept d where e.deptno =

d.deptno