需要数据库表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

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 )