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分。