实验2 连接查询,集合运算,子查询,TOP-N查询.doc

《数据库开发技术》课程之实验二
连接查询，集合运算，子查询和TOP-N分析
实验目的
掌握分组查询，连接查询和集合运算的使用方法。
掌握子查询及内嵌视图的使用方法。
掌握多列子查询和相关子查询的使用。
掌握TOP-N分析方法。
R(4) PRIMARY KEY,
CNAME VARCHAR2(30),
CPNO NUMBER(4),
CCREDIT NUMBER(2),
CLIMIT NUMBER(4));
CREATE TABLE SC(
SNO NUMBER(5) ,
CNO NUMBER(4) ,
GRADE NUMBER(3,1) ,
PRIMARY KEY(SNO,CNO));
INSERT INTO STUDENT VALUES(95001,'李明勇','男',20,'CS');
INSERT INTO STUDENT VALUES(95002,'刘晨','女',19,'IS');
INSERT INTO STUDENT VALUES(95003,'王名','女',18,'MA');
INSERT INTO STUDENT VALUES(95004,'张立','男',19,'CS');
INSERT INTO STUDENT VALUES(95005,'张军','男',21,'MA');
INSERT INTO STUDENT VALUES(95006,'王张凤','女',19,'FL');
INSERT INTO STUDENT VALUES(95007,'王敬','女',18,'IS');
INSERT INTO STUDENT VALUES(95008,'张名惠','男',19,'FL');
COMMIT;
INSERT INTO COURSE VALUES(1,'数据库',5,5,10);
INSERT INTO COURSE VALUES(2,'数学',NULL,3,10);
INSERT INTO COURSE VALUES(3,'信息系统',1,4,12);
INSERT INTO COURSE VALUES(4,'操作系统',6,4,12);
INSERT INTO COURSE VALUES(5,'数据结构',7,5,16);
INSERT INTO COURSE VALUES(6,'数据处理',NULL,3,15);
INSERT INTO COURSE VALUES(7,'PASCAL语言',6,2,NULL);
COMMIT;
INSERT INTO SC VALUES(95001,5,92);
INSERT INTO SC VALUES(95002,3,80);
INSERT INTO SC VALUES(95001,1,58);
INSERT INTO SC VALUES(95002,2,90);
INSERT INTO SC VALUES(95003,3,NULL);
INSERT INTO SC VALUES(95001,3,70);
INSERT INTO SC VALUES(95002,1,);
INSERT INTO SC VALUES(95003,2,67);