Student(SID, Sname, Sage, Ssex) 学生表
Course(CID, Cname, TID) 课程表
SC(SID, CID, score) 成绩表
Teacher(TID, Tname) 教师表
student学生表:
create table student
(
SID char(7) primary key not null,
Sname varchar(11) not null,
Sage number not null,
Ssex char(2) not null
);
course课程表
create table course
(
CID char(3) primary key not null,
Cname varchar(20) not null,
TID varchar(10) not null
);
sc成绩表
create table SC
(
SID char(7) not null,
CID char(3) not null,
score number,
primary key(SID,CID)
);
teacher教师表
create table Teacher
(
TID char(5) primary key not null,
Tname varchar(10) not null
);
插入数据:
insert into student values('0000001','张三',18,'男');
insert into student values('0000002','张四',17,'男');
insert into student values('0000003','王五',18,'女');
insert into student values('0000004','李六',20,'男');
insert into course values('001','Java','00001');
insert into course values('002','C++','00002');
insert into course values('003','C','00003');
insert into course values('004','JavaScript','00004');
insert into course values('005','python','00001');
insert into course values('006','C#','00001');
insert into course values('007','SqlServer','00001');
insert into course values('008','Oracle','00001');
insert into SC values('0000001','001',80);
insert into SC values('0000001','002',80);
insert into SC values('0000001','003',88);
insert into SC values('0000001','006',80);
insert into SC values('0000001','007',80);
insert into SC values('0000001','008',88);
insert into SC values('0000001','005',58);
insert into SC values('0000002','001',21);
insert into SC values('0000002','002',55);
insert into SC values('0000002','003',39);
insert into SC values('0000002','004',58);
insert into SC values('0000002','007',31);
insert into SC values('0000002','005',45);
insert into SC values('0000002','006',55);
insert into SC values('0000003','001',48);
insert into SC values('0000003','003',83);
insert into SC values('0000003','004',90);
insert into SC values('0000003','005',88);
insert into SC values('0000003','007',70);
insert into SC values('0000004','001',45);
insert into SC values('0000004','002',65);
insert into SC values('0000004','004',61);
insert into SC values('0000004','007',78);
insert into SC values('0000003','006',68);
insert into teacher values('00001','叶平');
insert into teacher values('00002','叶凡');
insert into teacher values('00003','李平');
insert into teacher values('00004','王平');
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select SC1.SID from SC SC1,SC SC2
where SC1.SID = SC2.SID
and SC1.CID = '001'
and SC2.CID = '002'
and SC1.score > Sc2.score;
2、查询平均成绩大于60分的同学的学号和平均成绩;
select SID,avg(score) from SC
group by SID
having avg(score) > 60
3、查询所有同学的学号、姓名、选课数、总成绩;
select S.SID,S.Sname,count(*) 选课数,sum(score) 总成绩 from student S,SC
where S.SID = SC.SID
group by S.SID,S.Sname
4、查询姓“李”的老师的个数;
select count(*) 姓李老师的个数 from Teacher
where Tname like '李%'
5、查询没学过“叶平”老师课的同学的学号、姓名
select SID, Sname
from student
where SID not in
(
select distinct sid
from sc
where CID in
(
select CID
from course
where TID = (select TID from teacher where Tname = '叶平')
)
);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select SID,Sname
from student
where SID in
(
select SC1.SID
from SC SC1,SC SC2
where SC1.SID = SC2.SID
and SC1.CID = '001'
and SC2.CID = '002'
);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select student.SID,student.sname
from student,
(
select sid,count(*) cnt from sc
where cid in
( select cid from course
where tid =
(select tid from teacher where Tname ='叶平')
)
group by sid
) c
where student.sid = c.sid
and c.cnt =
(
select count(*) from course
where tid = (select tid from teacher where Tname ='叶平')
)
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
select sid,sname from student
where sid in
(
select sc1.sid from sc sc1,sc sc2
where sc1.sid = sc2.sid and sc1.cid = '001'
and sc2.cid = '002' and sc1.score < sc2.score
)
9、查询所有课程成绩小于60分的同学的学号、姓名;
select sid,sname from student
where sid not in
(
select distinct sid from sc
where score >= 60
);
—10、查询没有学全所有课的同学的学号、姓名;
select sid,sname from student
where sid in
(
select sc.sid from sc,(select sid,count(*) cnt from sc group by sid) c
where sc.sid = c.sid and c.cnt <(select count(*) from course)
);
select t.sid,t.sname from
( select student.sid sid,student.sname sname,count(distinct(sc.sid)) cnt
from student,sc
where student.sid = sc.sid
group by student.sid,student.sname
) t
where t.cnt <(select count(distinct(cid)) from sc)