-- 作者:admin
-- 发布时间:2008/10/10 21:58:06
-- 关于Oracle数据库中练习关系运算查询的一些脚本
1、数据准备 CREATE TABLE student ( "NUMBER" varchar2(6) NOT NULL, name varchar2(8) NULL, sex number(1), birthday timestamp NULL, height number(3,2) NULL );
insert into student ("NUMBER",name,sex,birthday,height) values ( \'000001\',\'黎明\',1,TO_DATE(\'15-4月 -1980\',\'dd-Mon- yyyy\'),1.78); insert into student ("NUMBER",name,sex,birthday,height) values ( \'000002\',\'赵怡春\',0,TO_DATE(\'17-12月 -1982\',\'dd-Mon- yyyy\'),1.77); insert into student ("NUMBER",name,sex,birthday,height) values ( \'000003\',\'张富平\',1,TO_DATE(\'1-2月 -1981\',\'dd-Mon- yyyy\'),1.80); insert into student ("NUMBER",name,sex,birthday,height) values ( \'000004\',\'白丽\',0,TO_DATE(\'20-11月 -1986\',\'dd-Mon- yyyy\'),1.73); insert into student ("NUMBER",name,sex,birthday,height) values ( \'000005\',\'牛玉德\',1,TO_DATE(\'1-5月 -1983\',\'dd-Mon- yyyy\'),1.74); insert into student ("NUMBER",name,sex,birthday,height) values ( \'000006\',\'姚华\',0,TO_DATE(\'12-5月 -1983\',\'dd-Mon- yyyy\'),1.77);
CREATE TABLE grade ( "NUMBER" varchar2(6) NULL, course varchar2(50) NULL, grade number(3) NULL );
insert into grade ("NUMBER",course,grade) values (\'000001\',\'A03\',56); insert into grade ("NUMBER",course,grade) values (\'000002\',\'A02\',90); insert into grade ("NUMBER",course,grade) values (\'000004\',\'A02\',77); insert into grade ("NUMBER",course,grade) values (\'000005\',\'B01\',91); insert into grade ("NUMBER",course,grade) values (\'000004\',\'A03\',76); insert into grade ("NUMBER",course,grade) values (\'000003\',\'A03\',67); insert into grade ("NUMBER",course,grade) values (\'000001\',\'B01\',78); insert into grade ("NUMBER",course,grade) values (\'000002\',\'A03\',74); insert into grade ("NUMBER",course,grade) values (\'000002\',\'B01\',86); insert into grade ("NUMBER",course,grade) values (\'000003\',\'A02\',80); insert into grade ("NUMBER",course,grade) values (\'000004\',\'B01\',90);
2、查询 select * from student where sex=1 union select * from student where height>1.75;
select * from student where sex=1 intersect select * from student where height>1.75;
select * from student where sex=1 minus select * from student where height>1.75;
select * from student cross join grade;
select * from student natural join grade; select student."NUMBER",name,sex,birthday,height,course,grade from student inner join grade on student."NUMBER"=grade."NUMBER";
[此贴子已经被作者于2010-12-11 19:57:19编辑过]
|