数据库系统概论——数据查询实验

1.查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

use student
SELECT student.sno,sname,sdept,sc.cno,grade
FROM student,sc
WHERE student.sno=sc.sno 
AND cno IN(
SELECT cno
FROM course
where cname='数学' OR cname='大学英语'
)

2.查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

use student 
select *
from student 
where sage!=(
select sage
from student
where sname='张力'
)

3.按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。

use student 
select student.sno'学号',sname'姓名',sdept'所在院系',sum(ccredit)'已修学分'
from student,course,sc
where student.sno=sc.sno 
and course.cno=sc.cno 
and grade>=60 
group by student.sno,sname,sdept

4.查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

use student 
select distinct student.sno,sname,sc.cno
from student,sc
where sc.sno=student.sno and sc.sno in(
select sc.sno
from sc,student
where cno in(
select cno
from sc,student
where sname='张力' and sc.sno=student.sno
)
)

 5.查询只被一名学生选修的课程的课程号、课程名；

select cno,sname
from sc,student
where sc.sno=student.sno 
and sc.sno in (
select sno 
from sc
group by sno
having count(cno)=1)

6.使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；

use student 
select student.sno,sname
from student 
where sno in(
select sno
from sc
where cno in(
select cno 
from course
where cname='数据结构'
)
)

7.使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

use student 
select sname,sage,sdept
from student
where sdept!='CS' and sage < any(
select sage
from student 
where sdept='CS'
)

8.使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；

use student 
select sname
from student 
where sdept!='WM' and sage < all(
select sage
from student
where sdept='WM'
)

9.使用集合查询查询选修1号课程同时选修2号课程的同学的学号与姓名；

use student 
select student.sno,sname
from student,sc
where student.sno = sc.sno and cno='1'
intersect 
select student.sno,sname
from student,sc
where student.sno = sc.sno and cno='2'

10.显示选修02号课程的成绩前两名的学生学号及成绩。

use student 
select top 2 sno,grade
from sc
where cno='2'
order by grade desc

11.显示选修各个课程的及格的人数，及格比率；

use student
select cno,及格人数=sum(
case
when grade >=60 then 1 else 0
end),
及格比率=100*sum(
case 
when grade>=60 then 1 else 0
end
)/10
from sc
group by cno

12.显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

use student 
select sdept'院系名称',男生人数=sum(
case 
when ssex='男' then 1 else 0
end)
,女生人数=sum(
case 
when ssex='女' then 1 else 0
end
)
from student
group by sdept

13.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

select sno,avg(grade)'平均成绩'
from sc
group by sno
having sum(case
when grade < 60 then 1 else 0
end
)>=2

14.显示选修课程数最多的学号及选修课程数最少的学号；

select sno,count(cno)'选修课程数'
from sc
group by sno
having count(cno)>=all(
select count(cno) 
from sc 
group by sno
)
or 
count(cno)<=all(
select count(cno) 
from sc 
group by sno)

---

补充知识：

1.ANY语句

2.集合查询

并集（UNION）、交集（INTERSECT)、差集（EXCEPT）

3.ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

4.GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

5.在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。