oracle中有学生表吗,oracle sql 学生表 选课程表

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;