oracle 联表查询 多表查询 例子

一. 创建三张表：
学生信息表(stuInfo): 学号(主键), 姓名(不能为空), 性别(只能是男或女), 生日

课程信息表(subInfo)：编号（主键），课程名（不能为空）

分数信息表(scoreInfo)：编号（主键），学号（外键），课程号（外键），分数（0-100之间）

create table stuInfo(
        id number(3) primary key,
        name varchar2(20) not null,
        sex varchar2(2) check(sex='男' or sex='女') not null，
        birthday date
    )
/

create table subInfo(
        id number(3) primary key,
        courseName varchar2(80) not null
    )
/

create table scoreInfo(
        id number(3) primary key,
        stuId number(3) references stuInfo(id),
        scoreId number(3) references subInfo(id),
        score number(3) check(score>=0 and score<=100),
    )
/

二. 分别向三张表中插入数据，不少于5行
insert into stuInfo (id,name,sex,birthday) values(1,'王五','男',to_date('1998-01-01','yyyy-MM-dd'));
    insert into stuInfo (id,name,sex,birthday) values(2,'李四','男',to_date('2000-02-02','yyyy-MM-dd'));
    insert into stuInfo (id,name,sex,birthday) values(3,'张三','男',to_date('1998-01-01','yyyy-MM-dd'));
    insert into stuInfo (id,name,sex,birthday) values(4,'赵四','女',to_date('2001-04-04','yyyy-MM-dd'));
    insert into stuInfo (id,name,sex,birthday) values(5,'李二','女',to_date('2001-05-05','yyyy-MM-dd'));
    insert into stuInfo (id,name,sex,birthday) values(6,'王六','男',to_date('1996-01-01','yyyy-MM-dd'));
    insert into stuInfo (id,name,sex,birthday) values(7,'王七','男',to_date('1998-02-02','yyyy-MM-dd'));

    insert into subInfo (id,courseName) values(1,'java');
    insert into subInfo (id,courseName) values(2,'mysql');
    insert into subInfo (id,courseName) values(3,'javascript');
    insert into subInfo (id,courseName) values(4,'android');
    insert into subInfo (id,courseName) values(5,'C++');
    insert into subInfo (id,courseName) values(6,'html');
    insert into subInfo (id,courseName) values(7,'jsp');

    insert into scoreInfo (id,stuId,scoreId,score) values(1,1,1,100);
    insert into scoreInfo (id,stuId,scoreId,score) values(2,1,2,90);
    insert into scoreInfo (id,stuId,scoreId,score) values(3,2,2,60);
    insert into scoreInfo (id,stuId,scoreId,score) values(4,4,1,50);
    insert into scoreInfo id,stuId,scoreId,score) values(5,5,5,100);
    insert into scoreInfo (id,stuId,scoreId,score) values(6,6,6,100);
    insert into scoreInfo (id,stuId,scoreId,score) values(7,7,7,100);

三. 创建新表stu1并将stuinfo中的性别为女的学生的学号，姓名和性别的信息插入到该表中
create table stu1 as select id,name,sex from stuInfo where sex='女';

四. 完成下列查询：
1.查询所有姓张的学员信息(模糊查询)
select * from stuInfo where name like '张%';

2.查询所有20岁以上的学员信息(用日期函数实现)
select * from stuInfo where months_between(sysdate,birthday)/12>20;

3.查询同一天出生的学员信息（表连接）
select stuInfo.*,scoreInfo.score from stuInfo

left join scoreInfo on stuInfo.id = scoreInfo.stuId

where birthday in (select birthday from stuInfo group bybirthday having count(birthday) > 1);

4.查询参加考试的所有学员的名称，科目和成绩(按成绩排序)
select stuInfo.name,subInfo.courseName,scoreInfo.score from stuInfo,subInfo,scoreInfo

where stuInfo.id = scoreInfo.stuId and subInfo.id=scoreInfo.stuId

order by scoreInfo.score asc;

5.查询所有没有参加考试的学员学号,姓名，只要有1门课没考都要查询出来(子查询)
select stuInfo.id,stuInfo.name from stuInfo

where id=(select stuId from scoreInfo where score is null);

6.查询出没有学生考试的课程名称
select subInfo.courseName from stuInfo,subInfo,scoreInfo

where stuInfo.id = scoreInfo.stuId and subInfo.id=scoreInfo.stuId and scoreInfo.score is null;

7.参加考试的学员的姓名和总分按总分从大到小排序
select stuInfo.name,(select sum(scoreInfo.score) from stuInfo)from stuInfo,subInfo,scoreInfo

where stuInfo.id = scoreInfo.stuId and subInfo.id=scoreInfo.stuId

and scoreInfo.score is not null

order by (select sum(scoreInfo.score) from stuInfo) desc;