SQL学习

with deptnosal as
(
select deptno,avg(sal) avgsal
from emp
group by DEPTNO
),mindeptsal as
(
select min(deptnosal.avgsal) minsal
from deptnosal
),t as
(
select deptno
from deptnosal
where avgsal=(select minsal from mindeptsal)

)
select * from emp
where DEPTNO=(select deptno from t)
– case 表达式

– select

select emp.* ,case deptno
when 10 then ‘f’
when 20 then ‘g’
when 30 then ‘d’
end ‘定位’from emp
–v2：
select emp.* ,case
when deptno=10 then ‘f’
when deptno=20 then ‘g’
when deptno=30 then ‘d’
end ‘定位’from emp
–select ename,isnull(varchar(comm),”无补助”)
– from EMP
select ename,sal,case when comm is null then ‘无补助’ end from emp
–聚集函数忽略null，没有定位case自动为null
select emp.* ,case deptno
when 10 then ‘f’
when 20 then ‘g’

         end '定位'from emp

with aaa as(
select 10 deptno,’好’ dingwei
union
select 20,’一般’
union
select 30,’穷’)
select * from emp,aaa where emp.DEPTNO=aaa.deptno

---

——–any,all,some
select * from emp where sal > all(select sal from emp where deptno=30)/*找出比30号部门工资更高的人，any（大于最低得人的工资），
some，any的同义词*/
select dname from dept where (select count(*) from emp where deptno=dept.deptno)>2
/*内层查询和外层查询的结合，嵌套的for循环，对于外层查询每一行执行一遍查询过程
1.看子查询是否相关，能否独立运行，不能的就是相关的
2.相关子查询exists（）结合在一起的
*/
/*not exits(),有之false,也可以用在不相关子查询
not in 立面有null，会什么都出来
*/
/关系除法…….找到选修全部课程的学生，找到所有平均工资为2500以上的职位的部门/
select dname
from DEPT where not exists(select * from emp where DEPTNO=dept.DEPTNO group by deptno)

with deptt as(
select Deptno from dept except(select deptno from emp))
select DNAME from DEPT,deptt where deptt.deptno=dept.DEPTNO

select dname from dept where deptno not in(
select deptno from emp group by DEPTNO
)