查询语句
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 t3where t1.sno=t3.sno and t2.cno=t3.cno order by t1.sno --查询同时选了“C09”，“C27”的学生；select t1.sno, t1.snamefrom student t1, course t2, sc t3where 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.snamefrom student t1where 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, cnofrom sc group by sc.cnoorder by count desc--每门课程的最高分，最低分，平均分, 学选修的人数select t2.cno, t2.cname, max(t3.grade), avg(t3.grade), min(t3.grade), count(t2.cno)from course t2, sc t3where t2.cno=t3.cno group by t2.cno, t2.cnameorder by max(t3.grade) desc--查询平均成绩大于75分的同学的学号和平均成绩； select t1.sno, t1.snamefrom student t1, course t2, sc t3where t1.sno=t3.sno and t2.cno=t3.cno group by t1.sno, t1.snamehaving avg(t3.grade)>80--查询所有同学的学号、姓名、选课数、总成绩； [b]外连接[/b] 包括没有分数的新人select t1.sno, t1.sname, count(t3.sno), sum(t3.grade) from student t1 ,course t2, sc t3where t1.sno=t3.sno(+) --and t2.cno = t3.cnogroup 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;