SQL练习

说明：这是我在学习SQL时写的这篇文章，写这篇文章的目的完全是为了加深我对SQL语句的理解，文中难免有错误之处，请见谅！！！

练习环境为：MySQL

首先创建三张表

创建STUDENT（学生表）表：

CREATE TABLE STUDENT

(sno varchar(20) not null,

sname varchar(10) not null,

ssex varchar(2) not null,

sdept varchar(10) not null);

给STUDENT表添加数据：

insert into student (sno, sname, ssex, sage, sdept) values ('9512101', '李勇', '男', '19', '计算机系');

insert into student (sno, sname, ssex, sage, sdept) values ('9512102', '刘晨', '男', '20', '计算机系');

insert into student (sno, sname, ssex, sage, sdept) values ('9512103', '王敏', '女', '20', '计算机系');

insert into student (sno, sname, ssex, sage, sdept) values ('9521101', '张立', '男', '22', '信息系');

insert into student (sno, sname, ssex, sage, sdept) values ('9521102', '吴宾', '女', '21', '信息系');

insert into student (sno, sname, ssex, sage, sdept) values ('9521103', '张海', '男', '20', '信息系');

insert into student (sno, sname, ssex, sage, sdept) values ('9531101', '钱小力', '女', '18', '数学系');

insert into student (sno, sname, ssex, sage, sdept) values ('9531102', '王大力', '男', '19', '数学系');

注：sno（学生学号），sname（学生姓名），ssex（学生性别），sage（学生年龄），sdept（系别）

创建COURSE（课程表）表：

CREATE TABLE COURSE

(cno varchar(10) not null,

cname varchar(20) not null,

hours varchar(10) not null);

给COURSE表添加数据：

insert into course (cno, cname, hours) values ('c01', '计算机文化学', '70');

insert into course (cno, cname, hours) values ('c02', 'VB', '90');

insert into course (cno, cname, hours) values ('c03', '计算机网络', '80');

insert into course (cno, cname, hours) values ('c04', '数据库基础', '108');

insert into course (cno, cname, hours) values ('c05', '高等数学', '180');

insert into course (cno, cname, hours) values ('c06', '数据结构', '72');

注：cno（课程编号），cname（课程名称），hours（课时）

创建SC（成绩表）表：

CREATE TABLE SC

(sno varchar(10) not null,

cno varchar(10) not null,

grade varchar(10));

给SC表添加数据：

insert into sc (sno, cno, grade) values ('9512101', 'c01', '90');

insert into sc (sno, cno, grade) values ('9512101', 'c02', '86');

insert into sc (sno, cno, grade) values ('9512101', 'c06', '');

insert into sc (sno, cno, grade) values ('9512102', 'c02', '78');

insert into sc (sno, cno, grade) values ('9512102', 'c04', '66');

insert into sc (sno, cno, grade) values ('9521102', 'c01', '82');

insert into sc (sno, cno, grade) values ('9521102', 'c02', '75');

insert into sc (sno, cno, grade) values ('9521102', 'c04', '92');

insert into sc (sno, cno, grade) values ('9521102', 'c05', '50');

insert into sc (sno, cno, grade) values ('9521103', 'c02', '68');

insert into sc (sno, cno, grade) values ('9521103', 'c06', '');

insert into sc (sno, cno, grade) values ('9531101', 'c01', '80');

insert into sc (sno, cno, grade) values ('9531101', 'c05', '95');

insert into sc (sno, cno, grade) values ('9531102', 'c05', '85');

注：sno（学生学号），cno（课程编号），grade（成绩）

练习题：

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

查询学生表：select * from student;

查询课程表：select * from course;

2、查询成绩在70到80分之间的学生的学号、课程号和成绩。

select sno as 学号, cno as 课程号, grade as 成绩 from sc where grade between 70 and 80;

3、查询c01号课程成绩最高的分数。

select grade as c01课程成绩最高分 from sc where cno='c01' order by grade desc limit 1;

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

select cname as 学生选修的课程, cno as 课程号 from course where cno in (select distinct cno from sc);

5、查询选了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。

select avg(grade) as 平均成绩, max(grade) as 最高成绩, min(grade) as 最低成绩 from sc where cno='c02';

6、统计每个系的学生人数。

select sdept as 系别, count(*) as 人数 from student where sdept in (select distinct sdept from student) group by sdept;

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

select cname as 课程名, count(*) as 选课人数, max(grade) as 考试最高分 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 as 学生姓名, student.sno as 学生学号, count(sc.cno) as 选课门数 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;

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

select a.sname, avg(c.grade) as 平均成绩, count (c.sno) as 选课门数 from sc c

join (student a, course b) on c.sno=a.sno and c.cno=b.cno group by a.sname having count(distinct b.cno)>2;

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

select sno as 学号, sum(grade) as 总成绩 from sc group by sno having sum(grade)>200;

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

select a.sname as 学生姓名, a.sdept as 系别, c.cno as 课程号 from student a

join sc c on a.sno=c.sno where c.cno='c02';

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

select a.sname as 学生姓名, c.cno as 课程号, c.grade as 成绩 from student a join sc c on a.sno=c.sno

where c.grade>80 order by c.grade desc;

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

select a.sname as 学生姓名, a.ssex as 学生性别, c.grade as 成绩 from student a

join (sc c, course b) on a.sno=c.sno and b.cno=c.cno

where b.cname='数据库基础' and a.ssex='男';

15、查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

select a.sname as 年龄相同的学生姓名, a.sage as 年龄 from student a join student b

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 course where cno not in (select distinct cno from sc);

17、查询有考试成绩的所有学生的姓名、修课名称及考试成绩。

select a.sname as 有考试成绩的学生姓名, b.cname as 课程名称, c.grade as 成绩 from student a

jion (course b, sc c) on a.sno=c.sno and b.cno=c.cno where c.grade is not null and c.ggrade!='';

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

19、将所有选修了"c01"课程的学生的成绩加10分。