oracle sql 学生表 选课表

查询语句

select * from student;
select * from course;
select * from sc;

--查询列表
select t1.sno, t1.sname, t2.cno, t2.cname, t3.grade 
from student t1, course t2, sc t3
where t1.sno=t3.sno and t2.cno=t3.cno 
order by t1.sno 

--查询同时选了“C09”，“C27”的学生；
select t1.sno, t1.sname
from student t1, course t2, sc t3
where t1.sno=t3.sno and t2.cno=t3.cno 
 and t2.cno in ('C09','C27') 
 group by t1.sname, t1.sno
 having count (t1.sname)=2


--查询“C09”课程比“C27”课程成绩高的学生； [b] 多层嵌套[/b]
select t1.sname
from student t1
where t1.sno in 
  (
    select a.sno
    from (select sc.sno,sc.grade from sc where sc.cno='C09') a, (select sc.sno,sc.grade from sc where sc.cno='C27') b
    where  a.sno=b.sno and a.grade > b.grade
  )

--选出哪门课程学的人最多(
select count(sc.cno) count, cno
from sc 
group by sc.cno
order by count desc

--每门课程的最高分，最低分，平均分, 学选修的人数
select t2.cno, t2.cname, max(t3.grade), avg(t3.grade), min(t3.grade), count(t2.cno)
from   course t2, sc t3
where  t2.cno=t3.cno 
group by t2.cno, t2.cname
order by  max(t3.grade) desc

--查询平均成绩大于75分的同学的学号和平均成绩； 
select t1.sno, t1.sname
from student t1, course t2, sc t3
where t1.sno=t3.sno and t2.cno=t3.cno 
group by t1.sno, t1.sname
having avg(t3.grade)>80

--查询所有同学的学号、姓名、选课数、总成绩； [b]外连接[/b] 包括没有分数的新人
select t1.sno, t1.sname, count(t3.sno), sum(t3.grade) 
from student t1 ,course t2, sc t3
where t1.sno=t3.sno(+) --and t2.cno = t3.cno
group by t1.sno, t1.sname 
order by t1.sno 

准备数据

--Oracle创建表结构
declare
  vcnt number;
begin
  select count(*) into vcnt from user_tables where table_name='SC';
  If vcnt = 1 Then
    Execute immediate 'drop table sc';
  end if;
  select count(*) into vcnt from user_tables where table_name='STUDENT';
  If vcnt = 1 Then
    Execute immediate 'drop table student';
  end if;
  select count(*) into vcnt from user_tables where table_name='COURSE';
  If vcnt = 1 Then
    Execute immediate 'drop table course';
  end if;
end;
/

Create table student
(sno char(10) primary key,
 sname varchar(20) not null,
 sage smallint,
 ssex char(2),
 sdept varchar(20));

Create table course
(cno char(10) primary key,
 cname varchar(50) not null,
 credit smallint);

Create table sc
(sno char(10),
 cno char(10),
 grade smallint,
 primary key(sno,cno));

--向表中插入数据
insert into student values('001','林波','24','m','CS');
insert into student values('002','辛林宇','21','f','E');
insert into student values('003','孙维丽','18','m','MA');
insert into student values('004','符志阶','16','m','CS');
insert into student values('005','汤静','24','f','C');
insert into student values('006','袁菲相','25','f','CS');
insert into student values('007','严维平','21','f','MA');
insert into student values('008','车显','25','f','CS');
insert into student values('009','虞金华','25','m','E');
insert into student values('010','谭平','18','f','C');
insert into student values('011','华黎毕','19','f','C');
insert into student values('012','赖骐','17','m','MA');
insert into student values('013','孔建福','16','m','CS');
insert into student values('014','薛宝','22','m','E');
insert into student values('015','华霞华','20','m','CS');
insert into student values('016','童友源','22','f','C');
insert into student values('017','翁权','20','m','E');
insert into student values('018','梅官睿','19','m','C');
insert into student values('019','汤睿黎','25','m','C');
insert into student values('020','邬军','16','m','E');
insert into student values('021','新人报道','18','m','E');

insert into course values('C01','C语言','2');
insert into course values('C02','数学','5');
insert into course values('C03','英语','4');
insert into course values('C04','计算机','2');
insert into course values('C05','数字电路','3');
insert into course values('C06','数据库','4');
insert into course values('C07','操作系统','4');
insert into course values('C08','信息安全','2');
insert into course values('C09','数据结构','5');
insert into course values('C10','可视化编程','4');
insert into course values('C11','VB程序设计','4');
insert into course values('C12','软件工程','4');
insert into course values('C13','物联网导论','5');
insert into course values('C14','射频自动识别与EPC编码技术','5');
insert into course values('C15','无线传感器网络技术','2');
insert into course values('C16','无线局域网','3');
insert into course values('C17','通信原理','5');
insert into course values('C18','安全交换协议基础','5');
insert into course values('C19','网络数据库技术','2');
insert into course values('C20','XML技术','3');
insert into course values('C21','分布式计算','3');
insert into course values('C22','数据存储与恢复技术','5');
insert into course values('C23','传感器技术','4');
insert into course values('C24','ARM体系结构与接口技术','4');
insert into course values('C25','电子支付与网上银行','4');
insert into course values('C26','移动终端编程','2');
insert into course values('C27','人工智能与机器人','5');
insert into course values('C28','模式识别','5');
insert into course values('C29','3G技术','5');
insert into course values('C30','Wince嵌入式应用','4');
insert into course values('C31','供应链与物流管理','2');
insert into course values('C32','物联网应用专题见习','4');
insert into course values('C33','传感器技术及应用专题见习','3');

--001号学生选修了5门课程
insert into sc values('001','C24','53');
insert into sc values('001','C02','85');
insert into sc values('001','C01','95');
insert into sc values('001','C05','94');
insert into sc values('001','C26','56');
--002号学生选修了5门课程
insert into sc values('002','C06','52');
insert into sc values('002','C14','97');
insert into sc values('002','C05','74');
insert into sc values('002','C33','68');
insert into sc values('002','C01','89');
--003号学生选修了8门课程
insert into sc values('003','C32','70');
insert into sc values('003','C27','80');
insert into sc values('003','C15','91');
insert into sc values('003','C13','60');
insert into sc values('003','C30','66');
insert into sc values('003','C33','82');
insert into sc values('003','C20','56');
insert into sc values('003','C29','97');
--004号学生选修了6门课程
insert into sc values('004','C20','41');
insert into sc values('004','C15','50');
insert into sc values('004','C19','66');
insert into sc values('004','C30','47');
insert into sc values('004','C26','78');
insert into sc values('004','C02','64');
--005号学生选修了9门课程
insert into sc values('005','C18','45');
insert into sc values('005','C19','81');
insert into sc values('005','C33','76');
insert into sc values('005','C27','100');
insert into sc values('005','C09','54');
insert into sc values('005','C22','60');
insert into sc values('005','C23','40');
insert into sc values('005','C24','62');
insert into sc values('005','C17','81');
--006号学生选修了4门课程
insert into sc values('006','C24','70');
insert into sc values('006','C32','44');
insert into sc values('006','C15','74');
insert into sc values('006','C07','51');
--007号学生选修了9门课程
insert into sc values('007','C09','64');
insert into sc values('007','C29','41');
insert into sc values('007','C10','44');
insert into sc values('007','C01','65');
insert into sc values('007','C33','78');
insert into sc values('007','C21','41');
insert into sc values('007','C07','49');
insert into sc values('007','C25','81');
insert into sc values('007','C08','60');
--008号学生选修了8门课程
insert into sc values('008','C22','45');
insert into sc values('008','C11','67');
insert into sc values('008','C02','91');
insert into sc values('008','C31','65');
insert into sc values('008','C21','95');
insert into sc values('008','C20','69');
insert into sc values('008','C18','48');
insert into sc values('008','C29','75');
--009号学生选修了7门课程
insert into sc values('009','C13','100');
insert into sc values('009','C27','68');
insert into sc values('009','C10','89');
insert into sc values('009','C30','84');
insert into sc values('009','C09','98');
insert into sc values('009','C22','53');
insert into sc values('009','C25','91');
--010号学生选修了5门课程
insert into sc values('010','C18','44');
insert into sc values('010','C09','41');
insert into sc values('010','C20','72');
insert into sc values('010','C07','70');
insert into sc values('010','C19','82');
--011号学生选修了8门课程
insert into sc values('011','C33','100');
insert into sc values('011','C01','65');
insert into sc values('011','C17','50');
insert into sc values('011','C19','80');
insert into sc values('011','C08','91');
insert into sc values('011','C09','79');
insert into sc values('011','C02','45');
insert into sc values('011','C18','53');
--012号学生选修了8门课程
insert into sc values('012','C10','45');
insert into sc values('012','C23','52');
insert into sc values('012','C06','64');
insert into sc values('012','C08','63');
insert into sc values('012','C16','53');
insert into sc values('012','C33','93');
insert into sc values('012','C03','64');
insert into sc values('012','C19','88');
--013号学生选修了6门课程
insert into sc values('013','C08','74');
insert into sc values('013','C20','42');
insert into sc values('013','C27','41');
insert into sc values('013','C02','53');
insert into sc values('013','C23','68');
insert into sc values('013','C04','97');
--014号学生选修了10门课程
insert into sc values('014','C21','40');
insert into sc values('014','C13','71');
insert into sc values('014','C18','88');
insert into sc values('014','C30','88');
insert into sc values('014','C22','84');
insert into sc values('014','C06','93');
insert into sc values('014','C26','70');
insert into sc values('014','C23','68');
insert into sc values('014','C07','72');
insert into sc values('014','C04','49');
--015号学生选修了6门课程
insert into sc values('015','C10','91');
insert into sc values('015','C02','72');
insert into sc values('015','C27','42');
insert into sc values('015','C20','86');
insert into sc values('015','C24','83');
insert into sc values('015','C17','77');
--016号学生选修了9门课程
insert into sc values('016','C22','49');
insert into sc values('016','C13','75');
insert into sc values('016','C17','71');
insert into sc values('016','C27','44');
insert into sc values('016','C02','43');
insert into sc values('016','C01','50');
insert into sc values('016','C26','42');
insert into sc values('016','C29','88');
insert into sc values('016','C31','88');
--017号学生选修了8门课程
insert into sc values('017','C09','89');
insert into sc values('017','C17','84');
insert into sc values('017','C29','89');
insert into sc values('017','C05','57');
insert into sc values('017','C06','63');
insert into sc values('017','C10','57');
insert into sc values('017','C26','93');
insert into sc values('017','C04','62');
--018号学生选修了6门课程
insert into sc values('018','C12','61');
insert into sc values('018','C26','100');
insert into sc values('018','C10','81');
insert into sc values('018','C14','61');
insert into sc values('018','C23','67');
insert into sc values('018','C07','42');
--019号学生选修了8门课程
insert into sc values('019','C08','58');
insert into sc values('019','C27','73');
insert into sc values('019','C16','40');
insert into sc values('019','C30','68');
insert into sc values('019','C33','45');
insert into sc values('019','C09','40');
insert into sc values('019','C32','73');
insert into sc values('019','C28','76');
--020号学生选修了6门课程
insert into sc values('020','C12','81');
insert into sc values('020','C22','76');
insert into sc values('020','C11','96');
insert into sc values('020','C16','85');
insert into sc values('020','C09','51');
insert into sc values('020','C23','65');

alter table course add snumber smallint;
update course set snumber=dbms_random.value(4,10);

commit;