6 sql中的习题

--求出每个员工的姓名 部门编号 薪水 和 薪水等级
--ok
select  "E".ename "员工姓名" , "E".deptno "部门编号 ", "E".sal "薪水" , "S".GRADE "薪水等级"
from emp "E"
join SALGRADE "S"
on "E".sal <= "S".HISAL AND "E".sal >="S".LOSAL

select  "E".ename "员工姓名" , "E".deptno "部门编号 ", "E".sal "薪水" , "S".GRADE "薪水等级"
from emp "E"
join SALGRADE "S"
on "E".sal  between "S".HISAL and "S".LOSAL

--查找每个部门的编号 该部门所有员工平均工资  平均工资的等级
--ok
select emp.deptno,avg(sal)"部门平均工资" ,grade
from emp,dept,salgrade
group by emp.deptno,grade,salgrade.hisal,salgrade.losal
having avg(sal)<salgrade.hisal and avg(sal)>=salgrade.losal

SELECT "T".DEPTNO, "T"."AVG_SAL" "部门平均工资", "S".grade "工资等级"
 FROM (
SELECT DEPTNO,AVG(SAL) AS "AVG_SAL"
FROM EMP
GROUP BY DEPTNO 
) "T"
JOIN SALGRADE "S"
ON "T".AVG_SAL BETWEEN "S".LOSAL AND "S".HISAL

--求出平均薪水最高的部门的编号 和部门平均工资
select top 1  deptno, avg(sal) 
from emp
group by deptno
order by avg(sal) desc

--把工资大于所有员工中工资最低的前三个人的姓名 工资 部门 编号 部门名称 和工资等级输出
--ok
select top 3  ename, sal, grade , emp.deptno
from emp,dept,salgrade
where emp.deptno = dept.deptno and (sal>=salgrade.losal and sal<=salgrade.hisal)
order by sal desc

select top 3  "e".ename,"e".sal,"s".grade,"e".deptno
from  emp "e"
join dept "d"
on "e".deptno ="d".deptno
join salgrade "s"
on "e".sal >="s".losal and "e".sal<="s".hisal
order by sal desc

--工资大于2000的员工的姓名和部门名称输出  和 工资的等级
--ok
select ename,dname,GRADE
from emp,dept,SALGRADE
where emp.deptno=dept.deptno and sal>2000 AND (sal<=SALGRADE.HISAL
AND sal>= SALGRADE.LOSAL)

select "E".ename , "D".dname, "S".GRADE
from emp "E"
join dept "D"
on "E".deptno = "D".deptno and sal>2000
join SALGRADE "S"
ON  "E".SAL >="S".LOSAL AND "E".SAL <="S".HISAL 
WHERE "E".sal >2000

--求出emp表中所有领导的姓名

select DISTINCT ename 
from emp 
join (select mgr
from emp
where mgr is not null) "T"
on emp.empno ="T".mgr


SELECT ENAME FROM EMP
WHERE EMPNO IN (SELECT MGR FROM EMP)

 

 

 

转载于:https://www.cnblogs.com/yoyov5123/archive/2013/02/24/2924595.html