需要数据库表1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
添加测试数据1.学生表
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
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' , '女');
2.课程表
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
3.教师表
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
4.成绩表
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
以下是整理的题目、答案以及学习心得
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
n.*
FROM
sc m
left join student n on m.sid=n.sid
GROUP BY
m.sid
HAVING
a >b
学习心得：
一开始以为传统的sql可以实现，比如子查询之类的，但是发现不能比较前后两列的数据，后来想把同一个学生的成绩在一列展示，在网上找到一个解决方法
select stuName,
sum(decode(courseName,'语文',score,null)) as chineseScore,
sum(decode(courseName,'数学',score,null)) as mathScore,
sum(decode(courseName,'英语',score,null)) as englishScore
from stuScore group by stuName;
但是不能实现，报错，具体的原因没有找到
最后找到了一个可以实现的方法，先分组，然后用case...when...then...end，把同一个sid的数据在一行展示，这样就可以用having进行判断。
备注：
还有一种写法
SELECT
a.SID ,a.score
FROM
( SELECT SID, score FROM sc WHERE cid = '01' ) a,
( SELECT SID, score FROM sc WHERE cid = '02' ) b
WHERE
a.score > b.score
AND a.SID = b.SID;
2.查询同时存在"01"课程和"02"课程的情况
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
n.*
FROM
sc m
LEFT JOIN student n ON m.sid = n.sid
GROUP BY
m.sid
HAVING
a IS NOT NULL
AND b IS NOT NULL
3.查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
n.*
FROM
sc m
LEFT JOIN student n ON m.sid = n.sid
GROUP BY
m.sid
HAVING
( a IS NOT NULL AND b IS NOT NULL )
OR ( a IS NOT NULL AND b IS NULL )
4.查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
n.*
FROM
sc m
LEFT JOIN student n ON m.sid = n.sid
GROUP BY
m.sid
HAVING
a <b
5.查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
n.*
FROM
sc m
LEFT JOIN student n ON m.sid = n.sid
GROUP BY
m.sid
HAVING
( a IS NOT NULL AND b IS NOT NULL )
OR ( a IS NULL AND b IS NOT NULL )
6.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
sid,
a,
b,
c,
( a + b + c ) / 3
FROM
(
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
sum( CASE m.cid WHEN '03' THEN m.score END ) AS c
FROM
sc m
LEFT JOIN student n ON m.sid = n.sid
GROUP BY
m.sid
) w
GROUP BY
sid
HAVING
( a + b + c ) / 3 >= 60
7.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT
sid,
a,
b,
c,
( a + b + c ) / 3
FROM
(
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
sum( CASE m.cid WHEN '03' THEN m.score END ) AS c
FROM
sc m
LEFT JOIN student n ON m.sid = n.sid
GROUP BY
m.sid
) w
GROUP BY
sid
HAVING
( a + b + c ) / 3 < 60
学习心得：
主查询如果想用子查询得到的字段，必须在主查询select里输出，否则where条件或者having就不能用子查询得到的字段
8.查询在sc表存在成绩的学生信息的SQL语句
SELECT
m.sid,
sum( CASE m.cid WHEN '01' THEN m.score END ) AS a,
sum( CASE m.cid WHEN '02' THEN m.score END ) AS b,
sum( CASE m.cid WHEN '03' THEN m.score END ) AS c,
n.*
FROM
sc m
LEFT JOIN student n ON m.sid = n.sid
GROUP BY
m.sid
9.查询在sc表中不存在成绩的学生信息的SQL语句
SELECT
*
FROM
student
WHERE
sid NOT IN ( SELECT sid FROM sc GROUP BY sid )