- -新建学生信息表
- 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
|
Posted on 3/9/2015 4:01:19 PM
|
|
|
Posted on 11/25/2018 9:50:36 PM