数据库-多表查询（SQL SERVER)

1降序 desc
升序 asc
2最多的几个，top2 和百分比表达
查询女生人数最多的两个学院，查询结果显示：学院、女生人数。
select top 40 PERCENT s.Sdept,count() AS NUM from s
where s.Ssex=‘女’ GROUP BY S.Sdept ORDER BY COUNT() DESC;

3 不重复
distinct
查询最低分低于50分的学生信息
select DISTINCT * FROM S,SC
WHERE S.Sno=SC.Sno AND SC.Score<50

4嵌套查询 in 不能用=
查询与“张三”同岁的“计算机学院”的学生信息
select * FROM S AS S1
WHERE S1.Sdept=‘计算机学院’ AND year(S1.Sbirthday) IN
( select YEAR(S2.Sbirthday) FROM S AS S2
WHERE S2.Sname=‘张三’);

查找“高等数学1”成绩最好的10个学生的信息。
select * FROM S
WHERE S.Sno IN
( SELECT TOP 11 SC.Sno FROM SC,C
WHERE C.Cno=SC.Cno AND C.Cname=‘高数’ order BY SC.Score DESC);

5SOME ALL 子查询
查询其它学院比“计算机学院”的学生年龄都大的学生信息
select * FROM S
WHERE S.Sdept!=‘计算机学院’ and YEAR(S.Sbirthday) >ALL
(SELECT YEAR(S.Sbirthday) FROM S
WHERE S.Sdept=‘计算机学院’)

6子查询＋聚集函数
查询只被一个学生选修了的课程信息
select * FROM c
WHERE c.Cno IN
(SELECT SC.cno FROM SC
GROUP BY SC.cno HAVING COUNT(*)=1);