oracle_sql

create table sporter(
       sporterid number(8) primary key,
       sname nvarchar2(50) not null,
       sex number(2) check (sex in(0,1)),
       department nvarchar2(50) not null
);

create table item(
       itemid nvarchar2(12) primary key,
       itemname nvarchar2(50) unique,
       locations nvarchar2(50) not null
);
drop table item;

create table grade(
       gid number(8),
       itemid nvarchar2(12) not null,
       mark number(8) check (mark in(0,2,4,6)),
       constraint grade_item_fk foreign key(itemid) references item(itemid)，
       constraint grade_sporter_fk foreign key(gid) references sporter(sporterid)
);
drop table grade;
--
--创建序列
create sequence item_seq;
drop sequence item_seq;
insert into item(itemid,itemname,locations) values(‘X001‘,‘男子五千米‘,‘一操场‘);
insert into item(itemid,itemname,locations) values(‘X002‘,‘男子标枪‘,‘一操场‘);
insert into item(itemid,itemname,locations) values(‘X003‘,‘男子跳远‘,‘二操场‘);
insert into item(itemid,itemname,locations) values(‘X004‘,‘女子跳高‘,‘二操场‘);
insert into item(itemid,itemname,locations) values(‘X005‘,‘女子三千米‘,‘三操场‘);
select * from item

--create sequence STUSEQ

create sequence sporter_seq
minvalue 1001
maxvalue 9999
start with 1001
increment by 1
cache 20;

insert into sporter(sporterid,sname,sex,department)
values(sporter_seq.nextval,‘张三‘,1,‘计算机系‘);
insert into sporter(sporterid,sname,sex,department)
values(sporter_seq.nextval,‘李明‘,1,‘物理系‘);
insert into sporter(sporterid,sname,sex,department)
values(sporter_seq.nextval,‘李娜‘,0,‘心里系‘);
insert into sporter(sporterid,sname,sex,department)
values(sporter_seq.nextval,‘孙丽‘,0,‘美术系‘);
insert into sporter(sporterid,sname,sex,department)
values(sporter_seq.nextval,‘李明‘,1,‘美术系‘);
insert into sporter(sporterid,sname,sex,department)
values(sporter_seq.nextval,‘站名‘,0,‘化学系‘);

commit;
--1001,  x001, 6   --1002,  x001, 4      --1004,  x001, 0
--1001,  x003, 4   --1002,  x003, 6    --1004,  x003, 2
--1005,  x004, 6   --    1006,  x004, 4  --    1003,  x002, 6
--1005 , x002 ,4   --    1006,  x002,2   --    1001,  x002,0)
insert into grade(gid,itemid,mark) values(1001,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1002,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1004,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1001,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1002,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1004,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1005,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1006,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1003,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1005,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1006,‘X001‘,6);
insert into grade(gid,itemid,mark) values(1001,‘X001‘,6);

--A、    求出目前总积分最高的系名，及其积分。

--B、    找出在一操场进行比赛的各项目名称及其冠军的姓名。
--C、    找出参加了张三所参加的所有项目的其他同学的姓名。
--D、    经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。
--E、    经组委会协商，需要删除女子跳高比赛项目。

oracle_sql

标签：