-- 学生表
create table T_STUDENT(
SID VARCHAR(44) PRIMARY KEY,
SN VARCHAR(100),
SD VARCHAR(100),
SA INT
);
-- 课程表
create table T_CLASSES(
CID VARCHAR(44) PRIMARY KEY,
CN VARCHAR(100)
);
-- 选课表
create table T_STUDENT_CLASSES(
SID VARCHAR(44),
CID VARCHAR(44),
G NUMBER(10, 2),
FOREIGN KEY (SID) REFERENCES T_STUDENT(SID),
FOREIGN KEY (CID) REFERENCES T_CLASSES(CID)
);
-- 查询每个学生的学号、总成绩
select
a.SID 学号,
a.SN 姓名,
sum(b.G) 总成绩
from T_STUDENT a
left join T_STUDENT_CLASSES b on b.SID = a.SID
group by a.SID, a.SN;
-- 查询学生表的第二页记录(每页显示数据30条)
select * from (
select a.*, ROWNUM no from T_STUDENT a
) t1
where t1.no between 31 and 40;
-- 查询选修课程超过5门的学员学号和所属单位
select t1.SID, t1.SD from T_STUDENT t1
left join (
select a.SID SID, count(b.CID) COUNTS from T_STUDENT a left join T_STUDENT_CLASSES b on b.SID = a.SID group by a.SID
) t2 on t1.SID = t2.SID
where t2.COUNTS >= 5;
-- 查询每个科目成绩排行第二的学员信息
select
table1.SID 学号,
table1.SN 姓名,
table1.SD 单位,
table1.SA 年龄,
table3.CN 课程,
table2.G 成绩
from T_STUDENT table1
left join (
select t1.CID CID, max(t1.G) G, t1.SID SID from T_STUDENT_CLASSES t1
left join (
select MAX(a.G) MAXG, a.CID from T_STUDENT_CLASSES a group by a.CID
) t2 on t1.CID = t2.CID
where t1.G < t2.MAXG group by t1.CID, t1.SID
) table2 on table2.SID = table1.SID
left join T_CLASSES table3 on table2.CID = table3.CID;