以文本方式查看主题

-  课外天地 李树青  (http://www.njcie.com/bbs/index.asp)
--  数据库系统原理课件  (http://www.njcie.com/bbs/list.asp?boardid=19)
----  关于Oracle数据库中练习关系运算查询的一些脚本  (http://www.njcie.com/bbs/dispbbs.asp?boardid=19&id=567)

--  作者: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编辑过]