SQL复杂查询(子查询)

USE STUDY
SELECT * from EMP
SELECT * FROM SALGRADE 

SELECT ename,EMP.deptno,DEPT.deptno FROM EMP INNER JOIN DEPT ON DEPT.deptno =EMP.deptno

SELECT e1.ename,e1.job,e2.ename FROM EMP e1 INNER JOIN EMP e2 on e1.mgr=e2.empno 

SELECT e1.ename,e1.job,e2.ename,DEPT.dname FROM DEPT , EMP e1 INNER JOIN EMP e2 ON e1.mgr=e2.empno WHERE DEPT.deptno =e1.deptno

SELECT ename,job ,sal,SALGRADE.grade FROM EMP left JOIN SALGRADE ON sal BETWEEN losal AND hisal 

SELECT EMP.ename,EMP.job,EMP.sal,
CASE SALGRADE.grade
WHEN ‘1‘ THEN ‘E‘ 
WHEN ‘2‘ THEN ‘D‘ 
WHEN ‘3‘ THEN ‘C‘ 
WHEN ‘4‘ THEN ‘B‘
WHEN ‘5‘ THEN ‘A‘ 
END AS grade 
FROM EMP left join SALGRADE on EMP.sal BETWEEN losal AND hisal 

SELECT e1.ename,e1.job,e2.ename FROM EMP e1 LEFT JOIN EMP e2 on e1.mgr=e2.empno

SELECT ename,sal*12 years FROM EMP ORDER BY years DESC 

--SELECT ename,job,sal,DEPT.dname FROM DEPT ,EMP where sal ORDER BY empno 
select a.* from emp a,(select deptno,max(sal) sal from emp group by deptno )b where a.deptno=b.deptno and a.sal=b.sal

SELECT deptno,count(*) from EMP GROUP BY deptno

SELECT deptno ,avg (sal) FROM EMP GROUP BY deptno 

SELECT count(*)FROM EMP GROUP BY deptno 

SELECT deptno,avg(sal)FROM EMP GROUP BY deptno HAVING avg(sal)>2000 

SELECT job,sum(sal) FROM EMP WHERE sal>1500 and job <>‘Salesman‘ GROUP BY job ORDER BY sum(sal) 

select top 1 avg(sal),deptno from emp group by deptno ORDER BY avg(sal) DESC 

select * from EMP WHERE sal>(SELECT sal FROM EMP where empno=7654)

select * from EMP WHERE sal>(SELECT sal FROM EMP where empno=7654)AND
job =(SELECT job FROM EMP WHERE empno =7788)

SELECT ename,job,sal FROM EMP WHERE sal =(SELECT min(sal)FROM EMP)