oracle课下作业代码

admin · 发表于 2015-3-9 16:01:19

-新建学生信息表
create table stuInfo
(
stuid number primary key,
stuname varchar(10) not null,
stusex varchar(2) check (stusex='男' or stusex='女'),
stubir date
);
--新建课程信息表
create table subInfo
(
subid number primary key,
subname varchar(20) not null
);
--新建分类信息表
create table scoreInfo
(
scoreid number primary key,
scorexh number references stuinfo(stuid),
scoresubid number references subInfo(subid),
score number check (score>=0 or score <=100)
);
--向学生表插入数据
insert into stuInfo values(1,'张山','男',to_date('2005-1-2','yyyy-mm-dd'));
insert into stuInfo values(2,'历史','男',to_date('2001-3-6','yyyy-mm-dd'));
insert into stuInfo values(3,'技术','女',to_date('1995-8-2','yyyy-mm-dd'));
insert into stuInfo values(4,'张山','男',to_date('2006-1-22','yyyy-mm-dd'));
insert into stuInfo values(5,'说道','女',to_date('2013-10-2','yyyy-mm-dd'));
insert into stuInfo values(6,'虽然','男',to_date('1996-10-20','yyyy-mm-dd'));
insert into stuInfo values(7,'谈一谈','女',to_date('2005-1-2','yyyy-mm-dd'));
insert into stuInfo values(8,'一样','女',to_date('2005-1-2','yyyy-mm-dd'));
insert into stuInfo values(9,'方法','男',to_date('1981-6-6','yyyy-mm-dd'));
insert into stuInfo values(10,'尺寸','男',to_date('1980-8-8','yyyy-mm-dd'));
--向课程信息表插入数据
insert into subinfo values(1,'数据库');
insert into subinfo values(2,'java');
insert into subinfo values(3,'web开发');
insert into subinfo values(4,'linux维护');
insert into subinfo values(5,'安全维护');
--向分类信息表插入数据
insert into scoreInfo values(1,1,2,50);
insert into scoreInfo values(2,1,3,70);
insert into scoreInfo values(3,2,1,100);
insert into scoreInfo values(4,3,1,80);
insert into scoreInfo values(5,1,1,60);
insert into scoreInfo values(6,1,1,70);
--创建新表stu1并将stuinfo中的性别为女的学生的学号，姓名和性别的信息插入到该表中
create table stu1 as select * from stuinfo where stusex='女';
--删除stu1表
--drop table stu1;
select * from stu1
1.查询所有姓张的学员信息(模糊查询)
select * from stuinfo where stuname like '%张%';
2.查询所有20岁以上的学员信息(用日期函数实现)
select * from stuinfo where MONTHS_BETWEEN(sysdate,stubir)/12>20;
3.查询同一天出生的学员信息（表连接）
select * from stuinfo where stubir in (select stubir from stuinfo group by stubir having count(*)>1)
4.查询参加考试的所有学员的名称，科目和成绩(按成绩排序)
select stuinfo.stuname,scoreInfo.Score,scoreInfo.Scoresubid,subinfo.subname from stuinfo inner join scoreInfo on stuinfo.stuid=scoreInfo.Scorexh inner join subinfo on scoreInfo.Scoresubid=subinfo.subid order by scoreInfo.Score;
5.查询所有没有参加考试的学员学号,姓名，只要有1门课没考都要查询出来(子查询)
--方法一（错误）：
select stuinfo.stuid,stuinfo.stuname from stuinfo inner join (select stuid from stuinfo MINUS select Scorexh from scoreInfo) tmp on stuinfo.stuid=tmp.stuid;
--方法二（错误）：
select stuinfo.stuid,stuinfo.stuname from stuinfo where stuinfo.stuid not in (select Scorexh from scoreInfo);
--正确答案：
6.查询出没有学生考试的课程名称
--方法一：
select subInfo.Subname from subInfo inner join (select subInfo.Subid from subInfo MINUS select Scoresubid from scoreInfo) tmp on subinfo.subid=tmp.subid;
--方法二：
select subinfo.subname from subinfo where subinfo.subid not in (select scoresubid from scoreInfo);
7.参加考试的学员的姓名和总分按总分从大到小排序
select stuinfo.stuname,tmp.fen from stuinfo inner join (select sum(score) fen,scorexh from scoreInfo group by scorexh) tmp on stuinfo.stuid=tmp.scorexh order by fen DESC;
8.将所有的分数按升序排列并产生并列不跳空的序号
select score,row_number() over(order by score Asc) from scoreInfo order by score Asc

复制代码

femq · 发表于 2018-11-25 21:50:36

第5题是没有正确答案吗？

		自动登录	找回密码
密码			注册[Register]

[资料] oracle课下作业代码

相关帖子