数据库经典50道题

-- 建表
-- 学生表
CREATE TABLE `Student`(
 `s_id` VARCHAR(20),
 `s_name` VARCHAR(20) NOT NULL DEFAULT '',
 `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
 `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
 PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
 `c_id` VARCHAR(20),
 `c_name` VARCHAR(20) NOT NULL DEFAULT '',
 `t_id` VARCHAR(20) NOT NULL,
 PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
 `t_id` VARCHAR(20),
 `t_name` VARCHAR(20) NOT NULL DEFAULT '',
 PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
 `s_id` VARCHAR(20),
 `c_id` VARCHAR(20),
 `s_score` INT(3),
 PRIMARY KEY(`s_id`,`c_id`)
);

-- 学生表测试数据
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');

SELECT * FROM Student

-- 课程表测试数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

SELECT * FROM Course

-- 教师表测试数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

SELECT * FROM Teacher

-- 成绩表测试数据
INSERT INTO Score VALUES('01' , '01' , 80);
INSERT INTO Score VALUES('01' , '02' , 90);
INSERT INTO Score VALUES('01' , '03' , 99);
INSERT INTO Score VALUES('02' , '01' , 70);
INSERT INTO Score VALUES('02' , '02' , 60);
INSERT INTO Score VALUES('02' , '03' , 80);
INSERT INTO Score VALUES('03' , '01' , 80);
INSERT INTO Score VALUES('03' , '02' , 80);
INSERT INTO Score VALUES('03' , '03' , 80);
INSERT INTO Score VALUES('04' , '01' , 50);
INSERT INTO Score VALUES('04' , '02' , 30);
INSERT INTO Score VALUES('04' , '03' , 20);
INSERT INTO Score VALUES('05' , '01' , 76);
INSERT INTO Score VALUES('05' , '02' , 87);
INSERT INTO Score VALUES('06' , '01' , 31);
INSERT INTO Score VALUES('06' , '03' , 34);
INSERT INTO Score VALUES('07' , '02' , 89);
INSERT INTO Score VALUES('07' , '03' , 98);

SELECT * FROM Score

四张表关系图

学生表

+------+--------+-----------+-------+
| s_id | s_name |  s_brith  | s_sex |
+------+--------+-----------+-------+
| 01   | 赵雷   | 1990-01-01 |  男   |
| 02   | 钱电   | 1990-12-21 |  男   |
| 03   | 孙风   | 1990-05-20 |  男   |
| 04   | 李云   | 1990-08-06 |  男   |
| 05   | 周梅   | 1991-12-01 |  女   |
| 06   | 吴兰   | 1992-03-01 |  女   |
| 07   | 郑竹   | 1989-07-01 |  女   |
| 08   | 王菊   | 1990-01-20 |  女   |
+------+--------+-----------+-------+

课程表

+------+--------+-------+
| c_id | c_name |  t_id |
+------+--------+-------+
| 01   |  语文  |  02   |
| 02   |  数学  |  01   |
| 03   |  英语  |  03   |
+------+--------+-------+

教师表

+------+--------+
| t_id | t_name |
+------+--------+
| 01   |  张三  |
| 02   |  李四  |
| 03   |  王五  | 
+------+--------+

成绩表

+------+------+-------+
| s_id | c_id |s_score|
+------+------+-------+
|  01  |  01  |   80  |
|  01  |  02  |   90  |
|  01  |  03  |   99  |
|  02  |  01  |   70  |
|  02  |  02  |   60  |
|  02  |  03  |   80  |
|  03  |  01  |   80  |
|  03  |  02  |   80  |
|  03  |  03  |   80  |
|  04  |  01  |   50  |
|  04  |  02  |   30  |
|  04  |  03  |   20  |
|  05  |  01  |   76  |
|  05  |  02  |   87  |
|  06  |  01  |   31  |
|  06  |  03  |   34  |
|  07  |  02  |   89  |
|  07  |  03  |   98  |
+------+------+-------+

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT st.*, sc.s_score AS `01分数`, sc2.s_score AS `02分数`
FROM student st
	JOIN score sc
	ON st.s_id = sc.s_id
		AND sc.c_id = '01'
	LEFT JOIN score sc2
	ON st.s_id = sc2.s_id
		AND sc2.c_id = '02'
WHERE sc.s_score > sc2.s_score

+------+--------+-----------+-------+--------+--------+
| s_id | s_name |  s_brith  | s_sex | 01分数  | 02分数 |
+------+--------+-----------+-------+--------+--------+
| 02   | 钱电   | 1990-12-21 |  男   |  70    |   60   |
| 04   | 李云   | 1990-08-06 |  男   |  50    |   30   |
+------+--------+-----------+-------+--------+--------+

2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT st.*, sc.s_score AS `01分数`, sc2.s_score AS `02分数`
FROM student st
	JOIN score sc
	ON st.s_id = sc.s_id
		AND sc.c_id = '01'
	LEFT JOIN score sc2
	ON st.s_id = sc2.s_id
		AND sc2.c_id = '02'
WHERE sc.s_score < sc2.s_score

+------+--------+-----------+-------+--------+--------+
| s_id | s_name |  s_brith  | s_sex | 01分数  | 02分数 |
+------+--------+-----------+-------+--------+--------+
| 01   | 赵雷   | 1990-01-01 |  男   |  80    |   90   |
| 05   | 周梅   | 1991-12-01 |  女   |  76    |   87   |
+------+--------+-----------+-------+--------+--------+

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT st.s_id