oracle笔试题及答案（基础练习题）

/*1)      创建一张学生表，包含以下信息，学号，姓名，年龄，性别，家庭住址，联系电话*/
create table tb_student
(
     sid int not null unique,
     sname varchar2(50),
     sage int,
     ssex varchar2(50),
     saddress varchar2(500),
     sphonenum int
)

select * from tb_student

drop table tb_student
delete   tb_student
truncate table tb_student
truncate table tb_student
/*2)      修改学生表的结构，添加一列信息，学历*/

alter table tb_student add sgraduation varchar2(50);

alter table tb_student add stodelete varchar2(50);

/* 3)      修改学生表的结构，删除一列信息，家庭住址*/

alter table tb_student drop column stodelete;

/*4)      向学生表添加如下信息：

学号姓名年龄性别联系电话学历
1 A 22 男 123456 小学
2 B 21 男 119 中学
3 C 23 男 110 高中
4 D 18 女 114 大学 */

insert into   tb_student(sid,sname,sage,ssex,sphonenum,sgraduation)
values('1','A','22','男','123456','小学');
insert into   tb_student(sid,sname,sage,ssex,sphonenum,sgraduation)
values('2','B','21','男','119','中学');
insert into   tb_student(sid,sname,sage,ssex,sphonenum,sgraduation)
values('3','C','23','女','110','高中');
insert into   tb_student(sid,sname,sage,ssex,sphonenum,sgraduation)
values('4','D','18','男','114','大学');
insert into   tb_student(sid,sname,sage,ssex,sphonenum,sgraduation)
values('5','E','25','女','110','高中');

/*5)      修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”*/
update tb_student a set a.sgraduation = '大专' where a.sphonenum like '11%'

select * from tb_student
/*6)      删除学生表的数据，姓名以C开头，性别为‘男’的记录删除*/

delete tb_student a where a.sname like 'C%' and a.ssex = '男'

/*7)      查询学生表的数据，将所有年龄小于22岁的，学历为“大专”的，学生的姓名和学号示出来*/
select a.sname,a.sid from tb_student a where a.sage <22 and a.sgraduation ='大专'

/*8)      查询学生表的数据，查询所有信息，列出前25%的记录*/

select * from tb_student a where rownum < (select count(*) from tb_student)*0.25

/*9)      查询出所有学生的姓名，性别，年龄降序排列*/
select a.sname,a.ssex,a.sage from tb_student a order by a.sage desc

/*10)   按照性别分组查询所有的平均年龄*/

select avg(a.sage) from tb_student a group by a.ssex

select * from tb_student

/*11) 查询学生表的数据，查询所有信息，列出第2-3的记录*/

select * from tb_student a where rownum <3 and a.sid not in ( select a.sid from tb_student a where rownum <2 )

select * from (select rownum rn ,a.* from tb_student a) where rn >1 and rn <4

rownum是对结果集的编序排列，始终是从1开始，所以你的rownum>2永远为假，所以就没记录返回。。。



将rownum实例化就可以了
SELECT empno, ename, job, mgr, hiredate
 FROM (SELECT empno, ename, job, mgr, hiredate, 
     rownum rk
    FROM emp) t
WHERE rk > 2

秒客网

oracle笔试题及答案（基础练习题）

相关文章