SQL查询语句练习题20道

数据库中有三张表，分别为student,course,SC（即学生表，课程表，选课表）

//三张表截图如下：

 

--1.分别查询学生表和学生修课表中的全部数据。
SELECT * FROM student--查询学生表
SELECT * FROM course--查询课程表


--2.查询成绩在70到80分之间的学生的学号、课程号和成绩。
SELECT Sno 学号,Cno 课程号,Grade 成绩
FROM SC
WHERE Grade BETWEEN 70 AND 80

--3．查询C01号课程成绩最高的分数
SELECT TOP 1 Grade 
FROM SC 
WHERE Cno='C01' 
ORDER BY Grade DESC--降序DESC，升序ASC

--4.查询学生都选修了哪些课程,要求列出课程号。
SELECT Cname AS 学生选修的课程,Cno AS 课程号
FROM course
WHERE Cno IN(SELECT DISTINCT Cno FROM SC)--DISTINCT用来去除重复

--5.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。
SELECT AVG(Grade) 平均成绩,MAX(Grade) 最高成绩,MIN(Grade) 最低成绩
FROM SC 
WHERE Cno='C02' 

--6.统计每个系的学生人数。
SELECT Sdept 系别,COUNT(*) 人数
FROM student 
WHERE Sdept IN(SELECT DISTINCT Sdept FROM student) 
GROUP BY Sdept

--7.统计每门课程的修课人数和考试最高分。
SELECT Cname 课程名,COUNT(*) 修课人数,MAX(Grade) 考试最高分
FROM SC,course
WHERE SC.Cno IN(SELECT DISTINCT Cno FROM SC ) AND course.Cno=SC.Cno
GROUP BY course.Cname

--8.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。
SELECT student.Sname 学生姓名,student.Sno 学生学号,COUNT(SC.Sno) 选课门数
FROM student 
inner join SC ON SC.Sno=student.Sno
GROUP BY student.Sname,student.Sno
ORDER BY COUNT(SC.Sno) ASC

--9.统计选修课的学生总数和考试的平均成绩。
SELECT COUNT(DISTINCT(Sno)) AS 学生总数,AVG(Grade) AS 平均成绩
FROM SC  --//用DISTINCT消除重复的行

--11.列出总成绩超过200分的学生,要求列出学号、总成绩。
SELECT Sno 学号,SUM(Grade) 总成绩
FROM SC
GROUP BY Sno
HAVING SUM(Grade)>200

--17.查询选修了c02号课程的学生的姓名和所在系。
SELECT Sname 学生姓名,Sdept 所在系,SC.Cno AS 选修课程
FROM student
inner join SC ON student.Sno=SC.Sno
WHERE SC.Cno='C02'

--12.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
SELECT Sname 学生姓名,SC.Cno 课程号,SC.Grade 成绩
FROM student
inner join SC ON student.Sno=SC.Sno--内连接表SC查询
WHERE SC.Grade>80
ORDER BY SC.Grade DESC

--13.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。
SELECT Sname 姓名,Ssex 性别,SC.Grade 成绩
FROM student
inner join SC ON Cno IN(SELECT Cno FROM course WHERE Cname='数据库基础') --显示成绩的条件
AND student.Sno=SC.Sno --显示成绩的学生的学号
WHERE Sdept='计算机系' AND Ssex='男' 

--21.查询哪些课程没有人选,要求列出课程号和课程名。
SELECT Cno AS 课程号,Cname AS 课程名
FROM course
WHERE Cno NOT IN(SELECT DISTINCT SC.Cno FROM SC)


--15.查询有考试成绩的所有学生的姓名、修课名称及考试成绩
--要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。
SELECT student.Sname AS 有考试成绩的学生的姓名,course.Cname AS 修课名称,SC.Grade AS 考试成绩
INTO [new_sc] --将查询结果放入新表new_sc中
FROM student,course,SC
WHERE SC.Grade IS NOT NULL AND student.Sno=SC.Sno AND course.Cno=SC.Cno


--16.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
--并要求将这两个查询结果合并成一个结果集,
--并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
--//此题用到了并union查询
SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩
FROM student 
inner join SC     ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept='信息系'
UNION 
SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩
FROM student 
inner join SC     ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept='计算机系'

或

select sdept, sname,ssex,cname,grade from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and (sdept='信息系' or sdept='计算机系')


--17.用子查询实现如下查询:
--(1)    查询选修了C01号课程的学生的姓名和所在系。
SELECT Sname AS 选修了C01号课程的学生的姓名,Sdept AS 所在系
FROM student
WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='C01')

--(2)    查询数学系成绩80分以上的学生的学号、姓名。
SELECT Sno AS 数学系成绩80分以上的学生的学号,Sname AS 姓名
FROM student
WHERE Sno IN(SELECT Sno FROM SC WHERE Grade>80) 
AND   Sno IN(SELECT Sno FROM student WHERE Sdept='数学系')

--(3)    查询计算机系学生所选的课程名.
SELECT Cname AS 计算机系学生所选的课程名
FROM course
WHERE 
course.Cno IN(SELECT DISTINCT Cno FROM SC WHERE SC.Sno IN(SELECT Sno FROM student WHERE Sdept='计算机系'))

--19.删除修课成绩小于50分的学生的修课记录
DELETE SC WHERE Grade<50 OR Grade IS NULL

--20.将所有选修了"c01"课程的学生的成绩加10分。
UPDATE SC 
SET Grade=Grade+10
WHERE Cno='C01'