关联案例2

[sql]代码库

drop table student;
drop table score;
create table student(
  stuno number(8) primary key,
  name varchar2(20),
  sex char(4) default '男',
  age int
);
create table score(
  stuno number(8) references student(stuno),
  chinese number(3),
  math number(3),
  english number(3)
);
insert into student
  select 1,'刘备','男',33 from dual union
  select 2,'小乔','女',19 from dual union
  select 3,'貂蝉','女',22 from dual union
  select 4,'曹操','男',36 from dual union
  select 5,'貂蝉','女',24 from dual ;
insert into score values(1,88,77,99);
insert into score values(2,79,84,72);
insert into score values(3,76,84,66);
insert into score values(4,72,69,97);
insert into score values(5,75,83,64);
--查询曹操的学号，姓名，性别，语文，数学，英语
select s.stuno,s.name,s.age,c.chinese,c.math,c.english from
  student s join score c on s.stuno=c.stuno and name='曹操';
--查询语文比数学好的同学
select s.name,c.chinese,c.math from student s join score c
 on s.stuno=c.stuno and chinese>math;
--查询姓名相同的学生学号
select stuno from student where name in(select name from student
  group by name having count(name)>1);