oracle学生选课查询(学生表student、成绩表sc、课程表c)

一、创建学生表，表结构（学号，姓名，年龄，性别，系别，出生地），插入几条记录

create table student(sid number(3),sname varchar2(10),age number(2),sex char(3),department varchar2(10),birthplace varchar2(10));
insert into s values(100,'曾华',21,'男','信息系','威海');
insert into s values(105,'张明',20,'男','信息系','烟台');
insert into s values(108,'王小丽',21,'女','旅游系','威海');
insert into s values(101,'李军',19,'男','金融系','青岛');
insert into s values(107,'王芳',24,'女','机电系','临沂');
insert into s values(102,'刘红英',22,'男','信息系','枣庄');
insert into s values(109,'唐奇',21,'男','机电系','威海');
insert into s values(103,'赵晨',21,'女','金融系','济南');

二、创建成绩表，表结构（学号，课程号，成绩），插入几条记录

create table sc(sid number(3),cid number(4),grade number(5,2));
insert into sc values(103,3245,86);
insert into sc values(105,3245,75);
insert into sc values(109,3245,68);
insert into sc values(103,3105,92);
insert into sc values(105,3105,88);
insert into sc values(109,3105,76);
insert into sc values(101,3105,64);
insert into sc values(107,3105,91);
insert into sc values(108,3105,78);
insert into sc values(101,6166,85);
insert into sc values(107,6166,58);
insert into sc values(108,6166,81);
insert into sc values(100,9888,66);
insert into sc values(102,9888,59);

三、创建课程表，表结构（课程号，课程名，教师）

create table c(cid number(4),cname varchar2(20),teacher varchar2(10));
insert into c values(3105,'计算机导论','王萍');
insert into c values(3245,'操作系统','李诚');
insert into c values(6166,'英语','张旭');
insert into c values(9888,'数学','刘冰');

四、完成查询

1. 李老师所教的课程号、课程名称

select cid,cname,teacher from c where teacher like '李%';

2. 年龄大于23岁的女学生的学号和姓名

select sid,sname,age,sex from s where age>23 and sex='女';

3. “王小丽”所选修的全部课程名称

select cname from c,sc,s where c.cid=sc.cid and sc.sid=s.sid and sname='王小丽';

4. 所有成绩都在80分以上的学生姓名及所在系

select sname,department from s where sid=(select sid from sc group by sid having min(grade)>=80);

5. 没有选修“操作系统”课的学生姓名

select distinct s.sid, sname from s where s.sid not in(select sc.sid from sc,c where cname='操作系统'and sc.cid=c.cid);

6. 与“王小丽”同乡的男生姓名及所在系

select sname,department from s where sex='男' and birthplace=(select birthplace from s where sname='王小丽');

7. 英语成绩比数学成绩好的学生

select * from s where (select grade from sc join c on sc.cid=c.cid where sc.sid=s.sid and cname='英语') >(select grade from sc join c on sc.cid=c.cid where sc.sid=s.sid and cname='数学');

8. 选修同一门课程时，女生比男生成绩好的学生名单

select DISTINCT sname from s,sc x where sex='女' and s.sid=x.sid and grade>all(select grade from sc,s where sex='男' and sc.cid=x.cid and sc.sid=s.sid);

9. 至少选修两门以上课程的学生姓名、性别

select sname,sex from s where sid in(select sid from sc group by sid having count(sid)>=2);

10. 选修了李老师所讲课程的学生人数;

select count(*) from sc where cid=(select cid from c where teacher like '李%');

11. 没有选修李老师所讲课程的学生人数；

select count(DISTINCT sid) from sc where sid not in(select sid from sc,c where sc.cid=c.cid and teacher like '李%');

12. “操作系统”课程得最高分的学生姓名、性别、所在系;

select sname,sex,department from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and cname='操作系统'  and grade=(select max(grade)from sc,c where sc.cid=c.cid and cname='操作系统');