Oracle SQL语句（运动员）

• SQL-创建用户和赋予权限
• SQL-视图
• SQL-建表实例

---

• 数据表

drop table Athlete;
create table Athlete (
Ano Char(10) not null primary key,
Aname Char(20),
Asex char(5),
Ateam char(16)
);
insert into Athlete values('1001','金贤重','男','大连');
insert into Athlete values('1002','李敏镐','男','大连');
insert into Athlete values('1003','金钟国','女','沈阳');
insert into Athlete values('1007','司马','男','吉林');
insert into Athlete values('1004','李白','女','吉林');
insert into Athlete values('1005','马忠','女','火星');
insert into Athlete values('1008','司马迁','男','沈阳');


drop table Item;
create table Item(
Ino Char(10) not null primary key,
Iname Char(20),
Itime char(5),
Iplace char(20)
);
insert into Item values('1000','跳水',1000,'1');
insert into Item values('1001','跳高',1005,'2');
insert into Item values('1002','游泳',1010,'3');
insert into Item values('1003','篮球',1020,'4');


drop table Games;
create table Games(
Ano Char(10),
Ino Char(10),
Score int,
constraint AI_pk primary key(Ano ,Ino),
constraint AI_fk1 foreign key(Ano) references Athlete(Ano),
constraint AI_fk2 foreign key(Ino) references Item(Ino),
constraint AI_Score check (Score between 0 and 10 or Score is null)
);
insert into Games values('1001','1000',1);
insert into Games values('1003','1001',3);
insert into Games values('1004','1001',4);
insert into Games values('1002','1000',2);
insert into Games values('1002','1001',5);
insert into Games values('1002','1002',6);
insert into Games values('1002','1003',7);
insert into Games values('1003','1000',3);
insert into Games values('1003','1002',3);
insert into Games values('1003','1003',3);

---

• 建立运动员表

drop table Athlete;
create table Athlete (
Ano Char(10) not null primary key,
Aname Char(20),
Asex char(5),
Ateam char(16)
);

• 建立大连队运动员的视图A

create view A as
select * from Athlete
where Ateam='大连';

• 把对参赛表成绩的修改权限授予用户”USER1”

create user USER1 identified by 123456;
grant update(Score) on Games to USER1;

• 查询”1002”号运动员参加的所有项目及比赛时间和地点，要求对查询结构按比赛时间的先后顺序排序

select Item.Ino, Iname, Itime, Iplace
from Item, Athlete, Games
where Item.Ino = Games.Ino and Athlete.Ano = Games.Ano and Athlete.Ano = '1002'
order by Itime;

• 查询参加”1000”号项目的所有运动员信息

select Games.Ano, Aname, Asex, Ateam
from Athlete, Games
where Games.Ano = Athlete.Ano and Games.Ino = '1000';

• 统计各队参加比赛的”男”运动员的人数分别是多少

select Ateam, count(Ano)
from Athlete
where Asex = '男'
group by Ateam;

• 查询不在”1”号场地比赛的项目名

select Iname from Item where Iplace != '1';

• 查询每个场地都有比赛的运动员号

select Athlete.Ano from
(select count(*) as cou from Item) temp,
Athlete,
(select Ano, count(*) as cou2 from Games group by Ano) temp2
where temp2.Ano = Athlete.Ano and cou2 = cou;

• 查询参赛项目超过3项的”女”运动员的姓名

select Aname from
Athlete,
(select Ano, count(*) as cou from Games group by Ano) temp2
where temp2.Ano = Athlete.Ano and cou >= 3 and Asex = '女';

• 查询参加”跳高”比赛运动员的姓名和成绩

select Aname, Score
from Games, Athlete, Item
where Athlete.Ano = Games.Ano and Games.Ino = Item.Ino and Iname = '跳高';

• 求选课课程包含所有’801’号学生所修课程的学生的学号

select s.sno from
(select s.sno as sn, count(*) as cu from sc, s where sc.sno = s.sno group by s.sno) t,
(select sno, count(*) as cu1 from sc
    where cno != all(select cno from sc, s where sc.sno = s.sno and s.sno = '801')
    group by sno) t1,
(select count(*) as cu2 from sc, s
    where sc.sno = s.sno and s.sno = '801' group by s.sno) t2,
s
where
t.sn = s.sno
and
(
    (t1.sno = s.sno
    and
    t.cu - t1.cu1 = t2.cu2)
or
t.cu = t2.cu2);