--3.3 SQL数据查询

-- 数据准备

insert into S values('S1','李涛','男',19,'信息');
insert into S values('S2','王林','女',18,'计算机');
insert into S values('S3','陈高','女',21,'自动化');
insert into S values('S4','张杰','男',17,'自动化');
insert into S values('S5','吴小莉','女',19,'信息');
insert into S values('S6','徐敏敏','女',20,'计算机');

insert into C values('C1','C语言',4);
insert into C values('C2','离散数学',2);
insert into C values('C3','操作系统',3);
insert into C values('C4','数据结构',4);
insert into C values('C5','数据库',4);
insert into C values('C6','汇编语言',3);
insert into C values('C7','信息急促',2);

insert into SC values('S1','C1',90);
insert into SC values('S1','C2',85);
insert into SC values('S2','C1',84);
insert into SC values('S2','C2',94);
insert into SC values('S2','C3',83);
insert into SC values('S3','C1',73);
insert into SC values('S3','C7',68);
insert into SC values('S3','C4',88);
insert into SC values('S3','C5',85);
insert into SC values('S4','C2',65);
insert into SC values('S4','C5',90);
insert into SC values('S4','C6',79);
insert into SC values('S5','C2',89);


--3.3 SQL数据查询


--select
--count sum avg max min
--where
--having
use myjxgl;
--1 查询全体学生的 学号 和姓名
select SNO,SN  from S;
--2
select * from S;
--3
select SN,2005-AGE from S;
--4
select SN,2005-AGE,lower(DEPT) from S;

--where
--3.16
select distinct SNO from SC;

--3.17
select SN from S where DEPT='计算机';

--3.18
select SN,AGE from S where AGE<20;

--3.19
select SNO from SC where SCORE<60;


--3.20
select SN,DEPT,AGE from S where AGE>=20 and AGE<=23;
select SN,DEPT,AGE from S where AGE between 20 and 23;

--3.21
select SN,DEPT,AGE from S where AGE not between 20 and 23;

--3.22
select SN,SEX from S where DEPT not in('信息','计算机');

--3.23
--3.24
select SN,SNO,SEX FROM  S where SN LIKE '王%';

--3.25

--3.28
select CNO,CT from C where CN like 'DB\_Design' escape '\';


--3.29
select SNO,CNO from SC where SCORE is null;


--3.30
select SNO,CNO from SC where SCORE is not null;

--3.31
select SN from S where DEPT='计算机' and AGE<20;

--3.33
select COUNT(*) from S;

--3.34
select COUNT(distinct SNO) from SC;

--3.35
select COUNT(*) ,MAX(SCORE),MIN(SCORE),AVG(SCORE) from SC where CNO='C1';

--3.36 group by
select CNO,COUNT(SNO) from SC group by CNO;

--3.37  having
select CNO,COUNT(SNO) from SC group by CNO
having COUNT(*) >=3;

--3.38  查询排序
select SNO,SCORE from SC where CNO='C3'
order by SCORE DESC;

--3.39
select * from S order by DEPT,AGE DESC;

--连接查询


--3.40  等值连接
select * from S,SC where S.SNO=SC.SNO;
-------还有自然连接
--3.42
select  X.SN as 姓名,X.AGE as 年龄,Y.AGE as 李涛的年龄
from S as X,S as Y
where X.AGE>Y.AGE and Y.SN='李涛';
--自身连接

--3.43
--外连接
select S.SNO ,SN,SEX,AGE,DEPT,CNO,SCORE
from S left join SC on S.SNO=SC.SNO;


--3.43 合并查询

select SNO,SUM(SCORE)
from SC where(SNO='S1')
group by SNO
union
select SNO ,SUM(SCORE)
from SC where( SNO='S5')
group by SNO;

--嵌套查询
select SN from S
where SNO in
(select SNO from SC where CNO='S2');


--3.45
select SNO,SN from S where SNO in(select SNO from SC where CNO in(
select CNO from C where CN='数据库')
);


--3.46
select SN,AGE from S where AGE<ALL(select AGE from S where DEPT='信息') and DEPT!='信息'
order by AGE DESC;

--3.47 exists
select SN from S where exists(select * from SC where S.SNO=SNO and SNO='C1');

--3.48 not exists

--3.49  查询选修了全部课程的学生姓名
select SN from S where not exists(select * from C where not exists( select * from SC where SNO=S.SNO and CNO=C.CNO) );
-- 查询这样的学生姓名 没有一门课程是它不选的
---------------------------------此后得到的是没有被选了的课   

--3.50
--在选修C2课程 成绩大于该课的平均成绩的学生中 查询还选C1课的学生学号姓名与C1课程的成绩
select S.SNO,S.SN,SCORE
from SC,S,
(select SNO from SC where CNO='C2' and SCORE >
    (select AVG(SCORE) from SC where CNO='C2')) as T1(SNO)
    where SC.SNO=T1.SNO and S.SNO=T1.SNO and CNO='C1';
    


--3.51   
--3.52    我擦 不写了。。妈蛋。。
--3.53