学生成绩管理系统

山师数据库练习OJ: http://db.itoi.sd.cn/

数据库初始化:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

/*学生表*/
CREATE TABLE Student
(
    Sno  INT(9) PRIMARY KEY,
    Sname VARCHAR(10),
    Ssex  CHAR(2),
    Sage  TINYINT(3),
    Sdept VARCHAR(20)
);
INSERT INTO Student
VALUES (201215121, '李勇', '男', 20, 'CS'),
    (201215122, '刘晨', '女', 19, 'CS'),
    (201215123, '王敏', '女', 18, 'MA'),
    (201215125, '张立', '男', 19, 'IS'),
    (201215133, '张三', '男', 21, 'TE'),
    (201215137, '赵四', '男', 23, 'TE'),
    (201215139, '田二', '女', 24, 'CS'),
    (201215140, '李四', '男', 21, 'CS'),
    (201215141, '郑五', '女', 22, 'IS');

/*课程表*/
CREATE TABLE Course
(
    Cno  	INT(4) PRIMARY KEY,
    Cname 	VARCHAR(40),
    Cpno  	INT(4),
    Ccredit 	TINYINT(3),
    FOREIGN 	KEY (Cpno) REFERENCES Course (Cno)
);
INSERT INTO Course
VALUES
    (1, '数据库', 5, 4),
    (2, '数学', NULL, 2),
    (3, '信息系统', 1, 4),
    (4, '操作系统', 6, 3),
    (5, '数据结构', 7, 4),
    (6, '数据处理', NULL, 2),
    (7, 'PASCAL语言', 6, 4);

/*选课表*/
CREATE TABLE SC
(
    Sno  INT(9),
    Cno  INT(4),
    Grade SMALLINT(3),
    PRIMARY KEY (Sno, Cno),
/* 主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student (Sno),
/* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno) REFERENCES Course (Cno)
/* 表级完整性约束条件， Cno是外码，被参照表是Course*/
);
INSERT INTO SC
VALUES (201215121, 1, 92),
    (201215121, 2, 85),
    (201215121, 3, 88),
    (201215122, 2, 90),
    (201215122, 3, 80),
    (201215122, 6, 59),
    (201215123, 1, 84),
    (201215125, 1, 60),
    (201215125, 3, 90),
    (201215133, 4, 87),
    (201215137, 2, 79),
    (201215139, 2, 80),
    (201215140, 2, 81);

SET FOREIGN_KEY_CHECKS = 1;

基础30题:

  1. 查询全体学生的学号(Sno)与姓名(Sname)
    1
    2
    select Sno, Sname
        from student
  2. 查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)
    1
    2
    select sname, sno, sdept
        from student
  3. 查询全体学生的详细记录(Sno, Sname, Ssex, Sage, Sdept)
    1
    2
    select *
        from student
  4. 查询选修了课程的学生学号(Sno) (去除重复的学号)
    1
    2
    select distinct sno
        from sc
  5. 查询计算机科学系全体学生的名单(Sname)
    1
    2
    3
    select sname
        from student
        where sdept = "CS"
  6. 查询所有年龄在20岁以下的学生姓名(Sname)及其年龄(Sage)
    1
    2
    3
    select sname, sage
        from student
        where sage < 20
  7. 查询考试成绩有不及格的学生的学号(Sno)
    1
    2
    3
    select distinct sno
        from sc
        where grade < 60
  8. 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名(Sname)、系别(Sdept)和年龄(Sage)
    1
    2
    3
    select sname, sdept, sage
        from student
        where sage between 20 and 23
  9. 查询年龄不在20~23岁之间的学生姓名(Sname)、系别(Sdept)和年龄(Sage)
    1
    2
    3
    select sname, sdept, sage
        from student
        where sage not between 20 and 23
  10. 查询计算机科学系(CS)、数学系(MA)和信息系（IS）学生的姓名(Sname)和性别(Ssex)
    1
    2
    3
    select sname, ssex
        from student
        where sdept in ("CS", "MA", "IS");
  11. 查询既不是计算机科学系(CS)、数学系(MA)，也不是信息系（IS）学生的姓名(Sname)和性别(Ssex)
    1
    2
    3
    select sname, ssex
        from student
        where sdept not in ("CS","MA","IS");
  12. 查询所有姓刘学生的姓名(Sname)、学号(Sno)和性别(Ssex)
    1
    2
    3
    select sname, sno, ssex
        from student
        where sname like "刘%"
  13. 查询名字中第2个字为”立”字的学生的姓名(Sname)和学号(Sno)
    1
    2
    3
    select sname, sno
        from student
        where sname like "%立%"
  14. 查询所有不姓刘学生的姓名(Sname)、学号(Sno)和性别(Ssex)
    1
    2
    3
    select sname, sno, ssex
        from student
        where sname not like "刘%"
  15. 查询所有有成绩的学生学号(Sno)和课程号(Cno)
    1
    2
    3
    select sno, cno
        from sc
        where grade is not null
  16. 查询计算机系(CS)年龄在20岁以下的学生姓名(Sname)
    1
    2
    3
    select sname
        from student
        where sage < 20 and sdept = "CS"
  17. 查询选修了3号课程的学生的学号(Sno)及其成绩(Grade)，查询结果按分数降序排列。
    1
    2
    3
    select sno, grade
        from sc
        where cno = 3 order by grade desc
  18. 查询全体学生情况(Sno, Sname, Ssex, Sage, Sdept)，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
    1
    2
    3
    select *
        from student
        order by sdept asc, sage desc
  19. 查询学生总人数(COUNT)
    1
    2
    select count(*) as count
        from student
  20. 查询选修了课程的学生人数(查询列名为COUNT)
    请使用AS指定列名
    1
    2
    select count(distinct sno) as count
        from sc
  21. 计算1号课程的学生平均成绩(查询列名为AVG)
    请使用AS指定列名
    1
    2
    3
    select avg(grade) as avg
        from sc
        where cno = 1
  22. 查询选修1号课程的学生最高分数(查询列名为MAX)
    请使用AS指定列名
    1
    2
    3
    select max(grade) as max
        from sc
        where cno = 1;
  23. 查询学生201215121选修课程的总学分数(查询列名为SUM)
    请使用AS指定列名
    1
    2
    3
    select sum(ccredit) as sum
        from course, sc
        where sno = 201215121 and course.cno = sc.cno
  24. 求各个有选课学生的课程号(Cno)及相应的选课人数(人数列名为COUNT)
    请使用AS指定列名
    1
    2
    3
    select Cno, Count(*) as count
        from SC
        group by Cno
  25. 查询选修了2门以上课程的学生学号(Sno)
    1
    2
    3
    select sno
        from sc
        group by sno having count(*) > 2
  26. 查询平均成绩大于等于88分的学生学号(Sno)和平均成绩(平均成绩列名为AVG)
    请使用AS指定列名
    1
    2
    3
    select sno, avg(grade) as avg
        from sc
        group by sno having avg(grade) >= 88
  27. 查询每个学生及其选修课程的情况(Sno, Sname, Ssex, Sage, Sdept, Cno, Grade)
    1
    2
    3
    select student.*, cno, grade
        from student, sc
        where student.sno = sc.sno
  28. 查询每一门课的间接先修课（即先修课的先修课）(Cno, Cpno)
    1
    2
    3
    select x.cno, y.cpno
        from course x, course y
            where x.cpno = y.cno
  29. 查询选修2号课程且成绩在80分以上的所有学生 (Sno, Sname)
    1
    2
    3
    select student.sno, sname
        from student left join sc on student.sno = sc.sno
        where cno = 2 and grade > 80
  30. 查询每个选过课学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)
    1
    2
    3
    select student.sno, sname, cname, grade
        from student, course, sc
            where student.sno = sc.sno and sc.cno = course.cno