学生信息与选课信息、课程表信息、“李小波”所选修的全部课程名称

随着科学技术的发展，我们校园的各项管理也日趋完善，本次的数据库设计主要涉及到学生信息与选课信息、课程表信息的相关信息查询。任务概述，具体关系数据库如下：

设有学生选修课程数据库，

学生表（学号，姓名，年龄，性别，所在系，地址，出生日期）

选课表（学号，课程号，成绩）

课程表（课程号，课程名称，教师姓名）

表的创建、字段约束的设置以及示例数据的插入：

CREATE TABLE 学生表 (  
    学号 VARCHAR(20) PRIMARY KEY,  
    姓名 VARCHAR(50) NOT NULL,  
    年龄 INT CHECK (年龄 >= 0 AND 年龄 <= 100),  
    性别 VARCHAR(10) CHECK (性别 IN ('男', '女')),  
    所在系 VARCHAR(50),  
    地址 VARCHAR(100),  
    出生日期 DATE NOT NULL  
);
INSERT INTO 学生表 (学号, 姓名, 年龄, 性别, 所在系, 地址, 出生日期)  
VALUES ('20230001', '张三', 20, '男', '计算机系', '北京市海淀区', '2003-05-12');  
 
INSERT INTO 学生表 (学号, 姓名, 年龄, 性别, 所在系, 地址, 出生日期)  
VALUES ('20230002', '李小波', 22, '男', '数学系', '上海市浦东新区', '2001-07-15');  
  
CREATE TABLE 课程表 (  
    课程号 VARCHAR(20) PRIMARY KEY,  
    课程名称 VARCHAR(50) NOT NULL,  
    教师姓名 VARCHAR(50) NOT NULL  
);
INSERT INTO 课程表 (课程号, 课程名称, 教师姓名)  
VALUES ('C001', '数据库系统', '李老师');  
  
INSERT INTO 课程表 (课程号, 课程名称, 教师姓名)  
VALUES ('C002', '高等数学', '王老师');  

CREATE TABLE 选课表 (  
    学号 VARCHAR(20),  
    课程号 VARCHAR(20),  
    成绩 INT CHECK (成绩 >= 0 AND 成绩 <= 100),  
    PRIMARY KEY (学号, 课程号),  
    FOREIGN KEY (学号) REFERENCES 学生表(学号),  
    FOREIGN KEY (课程号) REFERENCES 课程表(课程号)  
);

INSERT INTO 选课表 (学号, 课程号, 成绩)  
VALUES ('20230001', 'C001', 85);  
  
INSERT INTO 选课表 (学号, 课程号, 成绩)  
VALUES ('20230002', 'C001', 92);

预览：

用SQL语言查询下列问题：

1）李老师所教的课程号、课程名称。

SELECT 课程号, 课程名称 FROM 课程表 WHERE 教师姓名 = '李老师';

2）年龄大于23岁的女学生的学号和姓名。

SELECT 学号, 姓名 FROM 学生表 WHERE 年龄 > 23 AND 性别 = '女';

3）“李小波”所选修的全部课程名称。

SELECT 课程名称 FROM 课程表
 JOIN 选课表 ON 
 课程表.课程号 = 选课表.课程号 
 WHERE 选课表.学号 = (SELECT 学号 FROM 学生表 WHERE 姓名 = '李小波');

4）所有成绩都在80分以上的学生姓名及所在系。

SELECT 学生表.姓名, 学生表.所在系 FROM 学生表
 JOIN 选课表 ON 学生表.学号 = 选课表.学号
  GROUP BY 学生表.学号 HAVING MIN(选课表.成绩) >= 80;

5）没有选修“操作系统”课的学生姓名。

SELECT 姓名 FROM 学生表 WHERE 学号
 NOT IN ( SELECT 学号 FROM 选课表 WHERE 课程号 = (SELECT 课程号 FROM 课程表 WHERE 课程名称 = '操作系统') );

6）英语成绩比数学成绩好的学生。

SELECT a.学号, 学生表.姓名 FROM 选课表
 a JOIN 选课表 b ON a.学号 = b.学号 
 JOIN 学生表 ON a.学号 = 学生表.学号 
 WHERE a.课程号 = (SELECT 课程号 FROM 课程表 WHERE 课程名称 = '英语') 
 AND b.课程号 = (SELECT 课程号 FROM 课程表 WHERE 课程名称 = '数学') 
 AND a.成绩 > b.成绩;

7）至少选修两门以上课程的学生姓名、性别。

SELECT 学生表.姓名, 学生表.性别 FROM 学生表
 JOIN 选课表 ON 学生表.学号 = 选课表.学号
  GROUP BY 学生表.学号 HAVING COUNT(选课表.课程号) >= 2;

8）选修了李老师所讲课程的学生人数。

SELECT COUNT(DISTINCT 学号) FROM 选课表
 WHERE 课程号 IN
  ( SELECT 课程号 FROM 课程表 WHERE 教师姓名 = '李老师' );

9）没有选修李老师所讲课程的学生。

SELECT 姓名 FROM 学生表 WHERE 学号
 NOT IN ( SELECT 学号 FROM 选课表 WHERE 课程号 IN ( SELECT 课程号 FROM 课程表 WHERE 教师姓名 = '李老师' ) );

10）“操作系统”课程得最高分的学生姓名、性别、所在系。

SELECT 学生表.姓名, 学生表.性别, 学生表.所在系
 FROM 学生表 
 JOIN 选课表 ON 学生表.学号 = 选课表.学号
  WHERE 选课表.课程号 = (SELECT 课程号 FROM 课程表 WHERE 课程名称 = '操作系统')
   ORDER BY 选课表.成绩 DESC LIMIT 1;