初学数据库

使用Group By子句的时候，一定要记住下面的一些规则：
（1）不能Group By非标量基元类型的列，如不能Group By text，image或bit类型的列
（2）Select指定的每一列都应该出现在Group By子句中，除非对这一列使用了聚合函数；
（3）不能Group By在表中不存在的列；
（4）进行分组前可以使用Where子句消除不满足条件的行；
（5）使用Group By子句返回的组没有特定的顺序，可以使用Order By子句指定次序。
单表查询：
/1.列出不姓刘的所有学生；/
select *
from student
where sname not like ‘刘%’;
/2. 列出姓“沈”且全名为3个汉字的学生；/
select *
from student
where sname like ‘沈__’;
/3. 显示在1985年以后出生的学生的基本信息；/
select *
from student
where year(getdate()) - sage > 1985;
/4. 查询出课程名含有“数据”字串的所有课程基本信息；/
select *
from course
where cname like ‘%数据%’;
/5. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；/
select sno,sname,ssex,sage,sdept
from student
where sno like ‘_[12349]%’ OR sno like ‘__[12349]%’;
/6. 列出选修了‘1’课程的学生，按成绩的降序排列；/
select sno,grade
from sc
where cno = ‘1’
order by grade desc;
/7. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；/
select distinct first.Sno
from sc first,sc second
where first.cno = ‘1’ and second.cno = ‘2’ and first.sno = second.sno;

select sno
from sc
where sno in (select sno from sc where cno = ‘1’) and cno = ‘2’;

select sno
from sc
where cno = ‘1’ or cno = ‘2’
group by sno
having count(*) = 2;
/8. 列出课程表中全部信息，按先修课的升序排列；/
select *
from course
order by cpno;
/9. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；/
select *
from student
where sage > (select avg(sage) from student)
order by sage;
/10. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；/
select sno 学号,sname 姓名, ssex 性别,year(getdate()) - sage 出生年份,sdept 院系
from student
order by year(getdate()) - sage;
/11. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；/
select sno,cno,grade
from sc
where grade between 70 and 80
order by cno desc,grade desc;
–12. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
select count(*) 学生总人数,AVG(sage) 平均年龄
from student;
–13. 显示选修的课程数大于3的各个学生的选修课程数；
select sno , count(cno)
from sc
group by sno
having count(cno) > 3;
–14. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
select cno 课程号,count(sno) 总人数,max(grade) 最高成绩,min(grade) 最小成绩,avg(grade) 平均成绩
from sc
group by cno;
–15. 显示选修各个课程的及格的人数、及格比率；round取小数点后几位
select sc.cno,count(sno) 及格人数,总人数 总人数,Round((count(sno) * 1.0/总人数)*100,2) 及格比例
from sc,(select cno,count(*) 总人数
from sc
group by Cno
) as x
where grade >= 60 and sc.cno = x.cno
group by sc.cno,总人数;
–16. 显示选修课程数最多的学号及选修课程数最少的学号；
select x.sno 选修课程数最多学号,y.sno 选修课程数最少学号
from (select top 1 with ties sno from sc group by sno order by count(cno) desc ) as x,
(select top 1 with ties sno from sc group by sno order by count(cno) asc) as y
order by y.Sno;

select top 1 sno 选修课程数最多学号,(select top 1 sno from sc group by sno order by count(*)) 选修课程数最多学号
from sc
group by sno
order by count(*) desc;
–17. 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；
select sdept 院系名称,sum(case when ssex = ‘男’ then 1 else 0 end) 男生人数, sum(case when ssex = ‘女’ then 1 else 0 end) 女生人数
from student
where sdept is not null
group by sdept;

select sdept 院系名称,count() 男生人数,(select count() from student where x.sdept = sdept group by sdept) - count(*) 女生人数
from student x
where Ssex = ‘男’ and sdept is not null
group by sdept,ssex;
–18. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
select sno,avg(grade) 平均成绩
from sc
where sno in (
select sno
from sc
where grade < 60
group by sno
having count(*) >= 2
)group by sno;