17,377
社区成员
发帖
与我相关
我的任务
分享
create table study (id number(10),name varchar2(20),class number(5));
insert into study values (11001,'张三',110);
insert into study values (11002,'李四',110);
insert into study values (11101,'张三',111);
insert into study values (11202,'王五',112);
create table exam (id number(10),subject varchar2(20),scores number(5));
insert into exam values (11001,'数学',80);
insert into exam values (11002,'数学',90);
insert into exam values (11101,'数学',67);
insert into exam values (11202,'数学',87);
insert into exam values (11001,'语文',54);
insert into exam values (11002,'语文',90);
insert into exam values (11101,'语文',98);
insert into exam values (11202,'语文',87);
commit;
select a.name,b.subject,b.scores
from study a,exam b
where a.id = b.id
order by a.id
id name subject scores
---------------------------------
1 11001 张三 数学 80
2 11001 张三 语文 54
3 11002 李四 数学 90
4 11002 李四 语文 90
5 11101 张三 语文 98
6 11101 张三 数学 67
7 11202 王五 语文 87
8 11202 王五 数学 87
alter table study add constraint pk_study(id) primary key
alter table exam add constraint pk_exam(id,subject) primary key
alter table exam add constraint fk_exam_id foreign key(id)
references study(id)