oracle 一对多怎么关联查询,请写一个多对多关系表的关联查询语句

--等值连接

select * from a,b where a.id=b.id

--内连接,和等值连接效果一样

select a.*,b.* from a inner join b on a.id=b.id

--自然连接

select * from a natural join b

--非等值连接

select a.*,b.* from a,b where a.id!=b.id

--无条件连接，笛卡尔积

select a.*,b.* from a,b

--交叉连接,笛卡尔积

select a.*,b.* from a cross join b

--左连接，又称为左外连接

select a.*,b.* from a left join b on a.id=b.id

select a.*,b.* from a,b where a.id=b.id(+)

select a.*,b.* from b left join a on a.id=b.id

--右连接，右外连接

select a.*,b.* from a right join b on a.id=b.id

select a.*,b.* from a,b where a.id(+)=b.id

--全连接，全外连接

select a.*,b.* from a full join b on a.id=b.id

实例：

--删除表

drop table score;

drop table student;

drop table course;

--创建学生表

create table student(

id varchar2(12) not null primary key,

name varchar2(10),

sex varchar2(4),

age number(4),

st_number varchar2(12)

);

--提交

commit;

--创建科目表

create table course(

id varchar2(12) not null primary key,

name varchar2(20)

);

--提交

commit;

--创建成绩表

create table score(

studentId varchar2(12),

courseId varchar2(12),

foreign key(studentId) references student(id),

foreign key(courseId) references course(id),

score varchar2(10)

);

--提交

commit;

--多表查询

--查询出学生姓名及其参与的考试科目的成绩的总成绩(如果成绩不及格<60,那么此课程为0分)

select *

from student s,

(select studentId, sum(sc)

from (select studentId,

score,

decode(floor(score / 60), 1, score, 0, 0) sc

from score)

group by studentId) t

where s.id = t.studentId

--列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

select s.name

from student s,

(select studentId, avg(score) a

from (select score, studentId

from score

where studentId in (select studentId

from score

where score < 60

group by studentId

having count(1) >= 2)) t

group by studentId) f

where s.id = f.studentId