#建表语句
Student(sid,Sname,Sage,Ssex) 学生表
Course(cid,Cname,tid) 课程表
SC(sid,cid,score) 成绩表
Teacher(tid,Tname) 教师表
–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
#创建测试数据
create table Student(sid varchar(10),Sname varchar(20),Sage date,Ssex varchar(10));
create table Course(cid varchar(10),Cname varchar(10),tid varchar(10));
create table Teacher(tid varchar(10),Tname varchar(20));
create table SC(sid varchar(10),cid varchar(10),score number(3,0));
begin
insert into Student values(‘01’,‘Zhao Lei’,to_date(‘1990-01-01’,‘yyyy-mm-dd’),‘MAN’);
insert into Student values(‘02’,‘Qian Dian’,to_date(‘1990-12-21’,‘yyyy-mm-dd’),‘MAN’);
insert into Student values(‘03’,‘Sun Feng’,to_date(‘1990-05-20’,‘yyyy-mm-dd’),‘MAN’);
insert into Student values(‘04’,‘Li Yun’,to_date(‘1990-08-06’,‘yyyy-mm-dd’),‘MAN’);
insert into Student values(‘05’,‘Zhou Mei’,to_date(‘1991-12-01’,‘yyyy-mm-dd’),‘WOMAN’);
insert into Student values(‘06’,‘Wu Lan’,to_date(‘1992-03-01’,‘yyyy-mm-dd’),‘WOMAN’);
insert into Student values(‘07’,‘Zheng Zhu’,to_date(‘1989-07-01’,‘yyyy-mm-dd’),‘WOMAN’);
insert into Student values(‘08’,‘Wang Ju’,to_date(‘1990-01-20’,‘yyyy-mm-dd’),‘WOMAN’);
insert into Course values(‘01’,‘CHINESE’,‘02’);
insert into Course values(‘02’,‘MATH’,‘01’);
insert into Course values(‘03’,‘ENGLISH’,‘03’);
insert into Teacher values(‘01’,‘Li Pengfei’);
insert into Teacher values(‘02’,‘Wang Wen’);
insert into Teacher values(‘03’,‘Zhang Zhichao’);
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);
end;
/
问题:
01、查询’01’课程比’02’课程成绩高的所有学生的学号;
SELECT a.sid FROM (SELECT sid,score FROM sc WHERE sc.cid=‘01’) AS a
JOIN (SELECT