软件实施笔试题目2

姓名： 期望薪水

1、查询姓张的学生的记录,并按年级降序排序;

Select * from student where NAME=”张” order by grade desc

select * from student t where t.name like '%张%'

2、查询‘赵二’同学各科目的成绩，＜60显示不及格，否则及格; Select score AS (case when score<60 then “不及格” else “及格” end ) form score where s_no=(select s_no form student where name=” 赵二”)

select t.score, case when t.score >= 60 then '合格' when t.score < 60 then '不合格' end from score t,student s

where t.s_no = s.s_no and s.name = '赵二'

3、查询出生年月为‘1989-4-14’的学生的姓名;

Select name from student where birthdry=”1989-4-14”

select * from student t where to_char(t.birthday,'YYYY-MM')='1989-04'

4、查询所有科目的分数总和小于180分的同学的名单;

Select NAME from student where s_no=(select s_no from score group by s_no having sum(score)<180)

select s.name,sum(t.score) as 总分 from score t,student s where t.s_no = s.s_no group by s.name having sum(t.score) > 180

5、查询钱六同学分数最高的两门科目的分数; Select score top 2 from score where s_no=(selcet s_no from student where name=”钱六”) order by score desc

select rownum, g.* from (

select t.* from student s,score t where s.s_no = t.s_no and s.name='钱六' order by score desc ) g

where rownum <=2

6、查询score和prj中总共出现过哪些P_NO，剔除重复记录;

7、查询各个科目中的最高分及相应学生姓名;

Select name from student where s_no=(select s_no from scroe where score=(select max(score) from score group by p_no))

select g.*,s.name from (

select r.p_no,max(r.score) as 分数 from score r,student s where r.s_no = s.s_no group by r.p_no

) g,score p,student s where g.p_no = p.p_no and g.分数 = p.score and s.s_no = p.s_no

8、删除score表中在prj表中不存在的课程的记录;

delete from score c where c.p_no not in(select p_no from prj)

Delete score where prj not In(select prj from prj) 9、将‘孙俪’同学的年级改为4;

Update student set grade=”4” where name=” 孙俪”

Update student t set grade='4' where name='孙俪'

10、创建课程扩展表prj_ext(课程编号 P_NO number(4),授课教师 teacher varchar2(32)，课时 hour number(4,1));

Greate table prj_ext (p_no number(4),teacher varchar2(32),hour number(4,1))

create table PRJ_EXT (

P_NO NUMBER(4), TEACHER VARCHAR2(32), HOUR NUMBER(4,1) );

alter table PRJ_EXT

add constraint PRJ_OBJ foreign key (P_NO) references PRJ (P_NO);

11、用insert 语句在表prj_ext中插入1条记录; Insert into prj_ext values (1, 孙俪,2) Insert into prj_ext values (1, '孙俪',2)

12、在表prj_ext中添加‘remark’列，字段类型为varchar2,字段大小为32个字符; Alter table prj_ext add (remark varchar2(32))

Alter table prj_ext add (remark varchar2(32))

13、删除表prj_ext; Alter table prj_ext

DROP TABLE prj_ext

14、创建student表关于S_NO，NAME的视图; Greate view strudent

CREATE OR REPLACE VIEW VW_ST AS

SELECT s_no,NAME FROM STUDENT

15、通过exp备份test用户的所有表; EXP TEST/TEST@EICDB FILE = C:/TEST.DMP