实验名称          SQL语言进行简单查询

实验目的及要求：

1. 掌握各种查询的SQL脚本写法，包括SELECT、FROM、WHERE、GROUP BY、HAVING子句的用法，特别要求比较内连接、外连接的结果
2. 在XSGL数据库中，使用各种查询条件完成指定的查询操作。

实验内容及步骤：

注：所有程序第一行均在XSGL数据库中进行，即每次运行程序第一行均为use XSGL

1. 查询选修了课程的学生人数。

select count(distinct sno) 学生人数--distinct表示去掉重复行

from sc

1. 查询学生200515001选修课程的总学分数。

select sum(credit)

from course,sc

where sno = '200515001'

and sc.cno = course.cno and grade>=60; --成绩超过60才有学分，并连接两个表

1. 查所有有成绩的学生学号和课程号。

select sno,cno

from sc

where grade is not null; --排除空值

1. 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

select sname,sdept,sage

from student

where sage>=20and sage<=23; --范围条件语句  也可以用between…and…

1. 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

select sno,grade

from sc

where cno=3

order by grade desc;--降序排列;

1. 求哪些学生还没有被分配系?

select *--查询所有表的所有信息

from student

where sdept=' 'or sdept is null;--sdept 为空或者为空格

1. 求CS系中所有男同学的学生姓名和年龄。

select sname,sage

from student

where ssex='男'and sdept='CS';

1. 我校开设的课程中哪些课程名以“数据”两个字开头？

select cname

from course--like语句表示求相似的，

where cname like '数据%';--数据%表示以数据开头，后面0到无穷多个字符

1. 求哪些学生的姓名中第2个字是“立”？

select sname

from student

where sname like '_向%';--_表示一个字符

1. 求哪些学生的成绩为优秀，求出该学生的学号及相应的课程号。

select sno,cno

from sc

where grade>90;--以90分以上为优秀

1. 求既不是CS系，也不是MA系的学生中年龄不小于20的学生姓名。

select sname

from student

where (sdept<>'cs'and sdept<>'ma')and sage>=20;--注意优先级，最好加括号

1. 求CS系中男女学生的数量分别是多少？

select ssex,count(ssex) 人数--也可以用*代替ssex

from student

where sdept='cs'

group by ssex--分别输出男女需要分组

1. 求各系中每个年龄段的学生总人数，要求结果中对系进行排序，同一个系的按年龄排序。

select count(sno) 人数,sdept,sage

from student

group by sdept,sage—group by 语句后面的词数与 select后单个词数一致

order by sdept desc,sage ;--降序为desc 升序为asc可省略

1. 查询各系的学生的人数并按人数从多到少排序 

select count(sno) 人数,sdept

from student

group by sdept

order by count(sno) desc;--降序为desc 升序为asc可省略

1. 查询各系的男女生学生总数, 并按系别升序排列, 女生排在前

select sdept,ssex,count(sno) 人数

from student

group by sdept,ssex

order by sdept,ssex desc;--女生在前，按照第二字母顺序降序为desc

1. 查询选修了3门课程已上的学生的学号和姓名

select student.sno,sname--必须加上student.或者sc.因为两个表都有sno

from sc,student

where student.sno=sc.sno--连接两个表

group by student.sno,sname

having count(*)>3--分组后显示选了三门以上的，而不是直接显示整个表三门以上的

1. 查询每个学生所选课程的平均成绩, 最高分, 最低分和选课门数

select sno,avg(grade) 平均分,max(grade) 最高分,

min(grade) 最低分,count(cno) 选课门数

from sc

group by sno

1. 查询至少选修了2门课程的学生的平均成绩.

select sno,avg(grade) 平均分

from sc

group by sno--查询的是选修至少两门的每个学生，然后计算每个学生的课程的信息，则对学生分组，每一个学生为一组

having  count(cno)>=2; --having后面可以使用聚合函数。聚合函数就是对一组值进行计算并且返回单一值的函数：sum---求和，count---计数，max---最大值，avg---平均值等。所以不用where。

1. 查询平均分超过80分的学生的学号和平均分.

select sno,avg(grade) 平均分

from sc

group by sno

having  avg(grade)>=80;

比较: 求各学生的60分以上课程的平均分.

select sno,avg(grade) 平均分

from sc

where grade>60

group by sno--having既然是对查出来的结果进行过滤，那么就不能对没有查出来的值使用having。

1. 查询”信息系”(IS)中选修了2门课程以上的学生的学号.

select student.sno

from sc,student

where sdept='is'and student.sno=sc.sno--一定要记得连接两个表

group by student.sno

having count(cno)>2;

1. 打印李勇的成绩单（即求李勇所选修的课程名及其成绩）。

select cname,grade

from sc,student,course--需要连接三个表

where course.cno=sc.cno

and student.sno=sc.sno and sname='李勇 ';

1. 求不及格和缺考的学生所在系、学号、姓名及相应课程名，要求按系排序，同一个系的按学号排序。

select sdept,student.sno,sname,cname

from student,course,sc

--sno是student表的主码，cno是course表的主码，需要用主码与sc表中的外码连接

where student.sno=sc.sno

and course.cno=sc.cno

and (grade<60 or grade is null)

--不用and因为成绩小于六十与成绩为空不能同时发生

--有or出现注意通过题目判断是否加括号

order by sdept ,student.sno--先按系排序，则系别在前面

1. 求CS系不及格和缺考的学生学号、姓名及相应课程名，要求按学号排序。

select student.sno,sname,cname

from student,course,sc

where student.sno=sc.sno

and course.cno=sc.cno

and (grade<60 or grade is null)

and sdept='cs'

order by student.sno

1. 求既不是CS系，也不是MA系缺考学生的学号、姓名及相应课程名。

select student.sno,sname,cname

from student, course, sc

where student.sno=sc.sno and course.cno=sc.cno

and grade is null

and sdept not in('cs','ma')--与sdept<>'cs'and sdept<>'ma'一致

1. 求选修BD课程的学生平均成绩。

select avg(grade)

from course,sc

where course.cno=sc.cno and cname='DB'

1. 求每一门课程的学生平均成绩，要求输出课程名及对应的平均成绩，并按平均成绩由大到小排序。

select cname, avg(grade) --成绩在sc表，课程名在course表

from course, sc

where course.cno=sc.cno

group by cname--求每一门课程，所以分组

order by avg(grade) desc--从大到小即为倒序

1. 求李勇所选修的总学分（即成绩及格的课程学分总和）。

select sum(credit) 总学分

from student,course,sc--成绩在sc表，学分在course表，李勇在student表

where student.sno=sc.sno and course.cno=sc.cno

and sname='李勇' and grade>=60