一套Oracle面试题笔试题及参考答案.pdf

_ _一套一套 OracleOracle 面试题笔试题及参考答案面试题笔试题及参考答案 完成下列操作，写出相应的 SQL 语句 创建表空间 neuspace，数据文件命名为 neudata.dbf，存放在 d:data 目录下，文件大小为 200MB，设为自动增长，增量 5MB，文件最大为 500MB。(8 分) 答：create tablespace neuspace datafile d:dataneudata.dbf size 200m auto extend on next 5m maxsize 500m; 2. 假设表空间neuspace已用尽500MB空间， 现要求增加一个数据文件， 存放在 e:appdata 目录下，文件名为 appneudata，大小为 500MB，不 自动增长。 （5 分） 答：altertablespaceneuspaceadddatafile e:appdataappneudata.dbf size 500m; 3. 以系统管理员身份登录，创建账号 tom，设置 tom 的默认表空间为 neuspace。为 tom 分配 connect 和 resource 系统角色，获取基本的系 统权限。然后为 tom 分配对用户 scott 的表 emp 的 select 权限和对 SALARY, MGR 属性的 update 权限。(8 分) 答：create user tom identified by jack default tablespace neuspace; Grant connect, resource to tom; Grant select, update(salary, mgr) on scott.emp to tom; 4. 按如下要求创建表 class 和 student。 （15 分） 属性 类型(长度) 默认值 约束 含义 CLASSNO 数值 (2) 无 主键 班级编号 CNAME 变长字符 (10) 无 非空 班级名称 属性 类型(长度) 默认值 约束 含义 STUNO 数值 (8) 无 主键 学号 SNAME 变长字符 (12) 无 非空 姓名 SEX 字符 (2) 男 无 性别 BIRTHDAY 日期 无 无 生日 EMAIL 变长字符 (20) 无 唯一 电子邮件 SCORE 数值 (5, 2) 无 检查 成绩 CLASSNO 数值 (2) 无 外键，关联到表 CLASS 的 CLASSNO 主键 班级 编号 答：create table class (classno number(2) constraint class_classno_pk primary key, cname varchar2(10) not null); create table student (stuno number(8) constraint student_stuno_pk primary key, sname varchar2(12) not null, sex char(2) default 男, birthday date, email varchar2(20) constraint student_email_uk unique, scorenumber(5,2)constraintstudent_score_ck check(score=0 and score=100), classnonumber(2)constraintstudent_classno_fkreferences class(classno) ); 5. 在表 student 的 SNAME 属性上创建索引 student_sname_idx （5 分） 答：create index student_sname_idx on student(sname); 6. 创建序列 stuseq，要求初值为 20050001，增量为 1，最大值为 20059999。 （6 分） 答：create sequence stuseq increment by 1 start with 20050001 maxvalue 20059999 nocache nocycle; 7. 向表 student 中插入如下 2 行。 （5 分） STUNO SNAME SEX BIRTHDAY EMAIL SCORE CLASSNO 从 stuseq 取值 tom 男 1979-2-3 14:30:25 tom 89.50 1 从 stuseq 取值 jerry 默认值 空 空 空 2 答：insert into student values(stuseq.nextval, tom, 男, to_date(1979-2-3 14:30:25, yyyy-mm-dd fmhh24:mi:ss), tom , 89.50, 1); insertintostudent(stuno,sname,classno) values(stuseq.nextval, jerry, 2); 8. 修改表 student 的数据，将所有一班的学生成绩加 10 分。 （4 分 ） 答：update student set score=score+10 where classno=1; 9. 删除表 student 的数据，将所有 3 班出生日期小于 1981 年 5 月 12 日的记录删除。 （4 分） 答： delete from student where classno=3 and birthday 12-5 月-81; 10. 完成以下 SQL 语句。(40 分) (1) 按班级升序排序，成绩降序排序，查询 student 表的所有记录。 答：select * from student order by classno, score desc; (2) 查询 student 表中所有二班的成绩大于 85.50 分且出生日期大于 1982-10-31 日的男生的记录。 答： select * from student where classno=2 and score85.50 and birthday 31-10 月-82 and sex=男; (3) 查询 student 表中所有三班成绩为空的学生记录。 答：select * from student where classno=3 and score is null; (4) 表 student 与 class 联合查询，要求查询所有学生的学号，姓名，成 绩，班级名称。 （使用 oracle 与 SQL 99 两种格式） 答：select s.stuno, s.sname, s.score, ame from student s, class c where s.classno=c.classno; (5) 按班级编号分组统计每个班的人数，最高分，最低分，平均分，并按 平均分降序排序。 答 ： select classno, count(*), max(score), min(score), avg(score) from student group by classno order by avg(score) desc; (6) 查询一班学生记录中所有成绩高于本班学生平均分的记录。 答：select * from student where classno=1 and score (select avg(score) from student where classno=1); (7) 统计二班学生中所有成绩大于所有班级平均分的人数。 答： select count(*) from student where classno=2 and score all (select avg(socre) from student group by classno); (8) 查询平均分最高的班级编号与分数。 答 ： select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score) from student group by classno); (9) 查询所有学生记录中成绩前十名的学生的学号、姓名、成绩、班级编 号。 答：select stuno, sname, score, classno from (select * from student order by score desc) where rownum=10; (10) 创建视图 stuvu，要求视图中包含 student 表中所有一班学生的 stuno, sname, score, classno 四个属性，并具有 with check option 限制。 答：create view stuvu as select stuno, sname,score,classno from student where classno=1 with check option; 1.简单 SQL 1.1 T-SQL 中的 Switch 语句 Q：case 第一种用法:直接对列进行值的判断,相当于程序中的 Switch取单个值 注意：then 的值必须是同一种类型（因为列的类型只能有一个） SELECT pId, CASE pTypeId WHEN 1 then 最爱 WHEN 2 THEN 亲爱 else 默认 end, pName,pCellPhone from PhoneNum 第二种用法,一般用来为列的取值范围，相当于,if else取一个值的,取值 范围 select sid,成绩= CASE when english between 90 and 100 THEN A whe n english 60 THAN E ELSE 缺考 end from score 例子：select *, (case TotalMoney when TotalMoney6000 then 金牌 when TotalMoney5000 then 银牌 else 普通 end) from table 1.2 T-SQL 中的子查询 Q :在结果集的基础上，再次查询, 注意:一定以要给结果集取别名,否则会报错 （取别名有两种方法： 1， .as 别名；2，别名=（） ） 例子：select * from (selec * from bc) as temp select * fromtemp = (selec * from bc) 1.3 T-SQL 中的 Exists 用法 Q：1.3.1 先执行 的是主查询，然后再执行子查询，将匹配行的数据显示 出来 例子： select * from tbl where exists(select * from phonTYpewhere tbl.pId = phonType.pId) 1.3.2 判断结果集是否存在(效率低) Q ：例子：if exists(select * from tbl where ptId=001) select 1 else select 2 1.4 T-SQL 中的 Any Q: /*Anyor 的意思*/ 例 子 ： select * from PhoneNum where pTypeId = any(select pTypeId from b) select * from PhoneNum where pTypeId = 1 or pTypeId = 2 注意：in/any/all 关键字匹配结果集的时候，结果集只能有一个列 any 相当于条件是很多个 or all 相当于条件是很多个 and 1.5 T-SQL 中的 All Q：/*ALLand 的意思*/ 例子：select * from PhoneNum where pTypeId = all(select pTypeId from b) select * from PhoneNum where pTypeId = 1 and pTypeId = 2 ANY 例子： SELECT * from PhoneNum where pTypeId = any（select ptId from PhoneType） ALL 例子： SELECT * from PhoneNum where pTypeId = all（select ptId from PhoneType） 2.SQL 分页 2.1 SQL2008 微软官方推荐分页方式 Q：例子：select * from ( select ROW_NUMBER() over (order by ar_id) as rnum,* from Area ) as t where runm between 10 and 14 3.SQL 中表连接查询 3.1 等值连接(内连接) inner join Q：任何一方都必须满足连接条件,如果有一方不满足连接条件就不显示 例 子 ： select * from PhoneNum n inner join PhoneType t on n.pTypeId = t.ptId 3.2 左连接(左外连接) left join Q：保证左边的数据都有，根据左边数据匹配 例子：select * from PhoneNum n left join PhoneType t on n.pTypeId = t.ptId 3.3 右连接(右外连接) right join Q：保证右边的数据都有，根据右边数据匹配 例 子 ： select * from PhoneNum n right join PhoneType t on n.pTypeId = t.ptId 3.4 交叉连接 cross join(两张表的乘积)（ 笛卡尔积） Q :例子：select * from student cross join Score 3.5 全连接 full join（就是不管两边的表是否满足条件，全都显示，对方 如果没有对应的数据，则显示 null） Q：左右两边的数据都进行匹配，相当于左连接和右连接相加。和 i nner join 刚好相反 例子：select * from PhoneNum n full join PhoneType t on n.pTypeId = t.ptId 4. SQL 进阶 4.1 SQL 中变量 4.1.1 声明变量 declare age int 4.1.2 变量赋值 set age=3 4.1.3 声明并赋值 select age = 29 4.1.4 如果表数据出现多行，则将最后一行的列赋值给变量（相当于循环 赋值，自然取到最后一个值） select age=age from Student 4.1.5 输出变量的值 print age 4.1.6 局部变量-先声明，再赋值 全局变量-系统变量，系统已经定义好的，用户不能定义和修改，只能 读取 5. SQL 中的事务 （ACID 特性：原子性，一致性，隔离性，持久性） begin transaction 开始事务 rollback transaction 回滚事务（当事务中语句出现异常） commit transaction 提交事务（更新数据） 银行事务转账列子: declare err int set err = 0 begin transaction begin print 开始事务 updatebanksetbalance=balance-1000where cid=0001 set err=err+ERROR updatebanksetbalance=balance+1000where cid=0002 set err=err+ERROR if(err0) begin rollback transaction print 回滚事务 end else begin commit transaction print 提交事务 end end 6. SQL 中存储过程 6.1 如何创建存储过程 例子：Create procedure usp_transferMoney intPerson varchar(20) outPerson varchar(20) 123 -可以给默认值，当参数有 默认值的时候，执行的时候可以不传该参数 abcPerson varchar(20) as select intPerson,outPerson,abcPerson 6.2 存储过程带输出参数 Create procedure usp_outPut maxEnglish int output as begin select * from Score select maxEnglish = Max(English) from Score end declare maxEnglish int select maxEnglish = 0 exec usp_outPut maxEnglish output print maxEnglish 6.3 调用存储过程 exec usp_transferMoney -不带参数 exec usp_transferMoney 001-带参数 exec usp_transferMoney abcPerson=234-指定参 数 注意：在执行存储过程的时候，如果带输出参数 output，则在执行的时 候必须显示执行，即加上“exec” 6.4 C#中如果参数是一个整型值，那么最好通过如下方式创建和赋值 SqlParameterp=new SqlParameter(id,SqldbType.Int);/注意：这里可以 直接给参数的值，但是如果是 0 的话，就有个漏洞，它会默认认为是个枚 举 p.Direction = ParameterDirection.OutPut; returnp.value; 7. SQL 中触发器 7.1 触发器定义 Q： (1)触发器是一种特殊的存储过程 (2)触发器不能传参数，通过事件进行触发执行 例子：Create trigger tg_class on class-表名 AFTER-instead of（操作完成之 前-替换语句）|after(操作完成之后) INSERT -、instead、update、delete AS BEGIN -触发器代码 end Select * from inserted -保存了引发新增触发器的新增数据，只能在触 发器中访问 -删除触发器 drop trigger tg_class 7.2 触发类型: Q :(1)after 和 for 是在执行操作后触发 (2)instead of 是执行操作之前触发(替换触发器)， 但是不会执行原语 句 7.3 触发器触发条件: Q:(1)update (2)insert (3)delete 7.4 触发器实例 -针对 tbL_abc 表的新增之后的触发器 Q：例子：Create Trigger triggerName on tbL_abc after insert as begin select * from i