Mysql数据库习题

数据库中有三张表，分别为s,c,sc（即学生表，课程表，选课表）

  

1.分别查询学生表和学生修课表中的全部数据。

SELECT * FROM s查询学生表 SELECT * FROM c查询课程表

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 LIMIT 0,1

4.查询学生都选修了哪些课程,要求列出课程号。

SELECT Cname AS 学生选修的课程,Cno AS 课程号 FROM c 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 s GROUP BY Sdept

7.统计每门课程的修课人数和考试最高分。

SELECT Cname 课程名,COUNT(*) 修课人数,MAX(Grade) 考试最高分 FROM SC,c WHERE SC.Cno IN(SELECT DISTINCT Cno FROM SC ) AND c.Cno=SC.Cno GROUP BY c.Cname

9.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。

SELECT s.Sname 学生姓名,s.Sno 学生学号,COUNT(SC.Sno) 选课门数 FROM s inner join SC ON SC.Sno=s.Sno GROUP BY s.Sname,s.Sno ORDER BY COUNT(SC.Sno) ASC

9.统计选修课的学生总数和考试的平均成绩。

SELECT COUNT(DISTINCT(Sno)) AS 学生总数,AVG(Grade) AS 平均成绩 FROM SC

//用DISTINCT消除重复的行

10.查询选课门数超过2门的学生的平均成绩和选课门数。

SELECT S.Sname,AVG(Sc.Grade) 平均成绩,COUNT(SC.Sno) 选课门数 FROM SC 内联接join或inner join,内联系是比较运算符，只返回符合条件的行     JOIN S ON (SC.Sno = S.Sno)     JOIN C ON (SC.Cno = C.Cno) GROUP BY S.Sname HAVING COUNT(C.Cno) >2分组条件

或 SELECT S.Sname,AVG(Sc.Grade) 平均成绩,COUNT(SC.Sno) 选课门数 FROM SC  JOIN S ON (SC.Sno = S.Sno) GROUP BY S.Sname HAVING COUNT(DISTINCT sc.Cno) >2

11.列出总成绩超过200分的学生,要求列出学号、总成绩。

SELECT Sno 学号,SUM(Grade) 总成绩 FROM SC GROUP BY Sno HAVING SUM(Grade)>200

12.查询选修了c02号课程的学生的姓名和所在系。

SELECT Sname 学生姓名,Sdept 所在系,SC.Cno AS 选修课程 FROM s inner join SC ON s.Sno=SC.Sno WHERE SC.Cno='C02'

13.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。

SELECT Sname 学生姓名,SC.Cno 课程号,SC.Grade 成绩 FROM s inner join SC ON s.Sno=SC.Sno内连接表SC查询 WHERE SC.Grade>80 ORDER BY SC.Grade DESC

14.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。

SELECT Sname 姓名,Ssex 性别,SC.Grade 成绩 FROM s inner join SC ON Cno IN(SELECT Cno FROM c WHERE Cname='数据库基础') 显示成绩的条件 AND s.Sno=SC.Sno 显示成绩的学生的学号 WHERE Sdept='计算机系' AND Ssex='男'

或 SELECT Sname 姓名,Ssex 性别,SC.Grade 成绩 FROM s,sc where s.sno=sc.sno and sc.cno in (SELECT Cno FROM c WHERE Cname='数据库基础') and  Sdept='计算机系' AND Ssex='男'

15.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。 这题使用到了表的自连接，所以需要给表取两个别名，如A和B

SELECT A.Sname 年龄相同的学生的姓名,A.Sage 年龄 FROM s A inner join s B ON A.Sage IN(SELECT Sage FROM s WHERE A.Sage=B.Sage AND A.Sname!=B.Sname) GROUP BY A.Sname,A.Sage ORDER BY A.Sage

16.查询哪些课程没有人选,要求列出课程号和课程名。

SELECT Cno AS 课程号,Cname AS 课程名 FROM c WHERE Cno NOT IN(SELECT DISTINCT SC.Cno FROM SC)

17.查询有考试成绩的所有学生的姓名、修课名称及考试成绩 要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。

create table  new_sc SELECT s.Sname AS 有考试成绩的学生的姓名,c.Cname AS 修课名称,SC.Grade AS 考试成绩 FROM s,c,SC WHERE SC.Grade IS NOT NULL AND s.Sno=SC.Sno AND c.Cno=SC.Cno

18.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩, 并要求将这两个查询结果合并成一个结果集, 并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。 //此题用到了并union查询

SELECT Sdept 系名,Sname 姓名,Ssex 性别,c.Cname 修课名称,SC.Grade 修课成绩 FROM s inner join SC     ON s.Sno=SC.Sno inner join c ON c.Cno=SC.Cno WHERE Sdept='信息系'

UNION SELECT Sdept 系名,Sname 姓名,Ssex 性别,c.Cname 修课名称,SC.Grade 修课成绩 FROM s inner join SC     ON s.Sno=SC.Sno inner join c ON c.Cno=SC.Cno WHERE Sdept='计算机系'

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

19.用子查询实现如下查询:

(1)查询选修了C01号课程的学生的姓名和所在系。

SELECT Sname AS 选修了C01号课程的学生的姓名,Sdept AS 所在系 FROM s WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='C01')

(2)查询数学系成绩80分以上的学生的学号、姓名。

SELECT Sno AS 数学系成绩80分以上的学生的学号,Sname AS 姓名 FROM s WHERE Sno IN(SELECT Sno FROM SC WHERE Grade>80) AND   Sno IN(SELECT Sno FROM s WHERE Sdept='数学系')

(3)查询计算机系学生所选的课程名.

SELECT Cname AS 计算机系学生所选的课程名 FROM c WHERE c.Cno IN(SELECT DISTINCT Cno FROM SC WHERE SC.Sno IN(SELECT Sno FROM s WHERE Sdept='计算机系'))

20.将计算机系成绩高于80分的学生的修课情况插入到另一张表中,分两种情况实现：

(1)在插入数据过程中建表。 使用create table的方法，是在插入数据的过程中建立新表

create table SC_Info1 SELECT s.Sname AS 学生名字,c.Cname AS 选修课程,SC.Cno AS 课程编号 FROM s,c,SC WHERE s.Sdept='计算机系' AND SC.Grade>80 AND c.Cno=SC.Cno AND s.Sno=SC.Sno

(2)先建一个新表,然后再插入数据。 创建表SC_Info2

CREATE TABLE SC_Info2 (     Sname char(7),     Cname char(20),     Cno   char(10) ) 往表SC_info2插入查询得到的结果 INSERT SC_Info2 SELECT s.Sname AS 学生名字,c.Cname AS 选修课程,SC.Cno AS 课程编号 FROM s,c,SC WHERE s.Sdept='计算机系' AND SC.Grade>80 AND c.Cno=SC.Cno AND s.Sno=SC.Sno

21.删除修课成绩小于50分的学生的修课记录

DELETE SC WHERE Grade<50 OR Grade IS NULL

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