一. 创建三张表:
学生信息表(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;