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 子句可以让我们筛选分组后的各组数据。