表格
-- 学生表
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' , '女');
-- 课程表测试数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
-- 教师表测试数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
-- 成绩表测试数据
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);
题目
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT stu.*,s1.s_score 课程一成绩,s2.s_score 课程二成绩 FROM student stu,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '01') s1
,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '02') s2
WHERE stu.`s_id` = s1.s_id AND stu.`s_id` = s2.s_id HAVING 课程一成绩 > 课程二成绩;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT stu.*,s1.s_score 课程一成绩,s2.s_score 课程二成绩 FROM student stu,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '01') s1
,(SELECT s.`s_id`,s.`s_score` FROM score s INNER JOIN course c ON s.c_id = c.c_id AND c.c_id = '02') s2
WHERE stu.`s_id` = s1.s_id AND stu.`s_id` = s2.s_id HAVING 课程一成绩 < 课程二成绩;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT stu.*,t.平均成绩 FROM student stu,(SELECT AVG(s.`s_score`) 平均成绩,s.`s_id` FROM score s INNER JOIN course c ON s.`c_id` = c.`c_id` GROUP BY s.`s_id`) t
WHERE t.s_id = stu.`s_id` HAVING 平均成绩 >= 60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
SELECT stu.*,IFNULL(t.平均成绩,0) AS r FROM student stu LEFT JOIN (SELECT AVG(s.`s_score`) 平均成绩, s.`s_id` FROM score s INNER JOIN course c ON s.`c_id` = c.`c_id` GROUP BY s.`s_id`) t
ON t.s_id = stu.`s_id` HAVING r < 60;
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT stu.*,COUNT(c.`c_id`)选课数,IFNULL(SUM(s.`s_score`),0) 总成绩 FROM student stu LEFT JOIN score s ON s.`s_id` = stu.s_id LEFT JOIN course c ON s.`c_id` = c.`c_id`
GROUP BY stu.`s_id`
