部分常见ORACLE面试题以及SQL注意事项
部分常见ORACLE面试题以及SQL注意事项
一、表的创建：
一个通过单列外键联系起父表和子表的简单例子如下：
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
)
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
)
建表时注意不要用关键字当表名或字段名，如insert,use等。
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
InnoDB Tables 概述
InnoDB给MySQL提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，
因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。
InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
InnoDB 的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。
从一个表中查询出数据插入到另一个表中的方法：
select * into destTbl from srcTbl ;
insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl ;以上两句都是将 srcTbl 的数据插入到 destTbl，但两句又有区别的。
第一句（select into from）要求目标表（destTbl）不存在，因为在插入时会自动创建。
第二句（insert into select from）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5。
如果只想要结构而不要数据。
create table s_emp_42 as select * from s_emp where 1=2;//永假式
SQL查询练习题
1.
表1：book表，字段有id(主键)，name (书名)；
表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间），state（1.借出 2.归还）。
id name
1 English
2 Math
3 JAVA
id bookId dependDate state
1 1 2009-01-02 1
2 1 2009-01-12 2
3 2 2009-01-14 1
4 1 2009-01-17 1
5 2 2009-02-14 2
6 2 2009-02-15 1
7 3 2009-02-18 1
8 3 2009-02-19 2
要求查询结果应为：(被借出的书和被借出的日期)
Id Name dependDate
1 English 2009-01-17
2 Math 2009-02-15
Select e.bookId,b.name,e.dependDate from book b,bookEnrol e where
第二个表是用来登记的，不管你是借还是还，都要添加一条记录。
请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息。
参考语句：
select book.id,book.name,max(dependDate)
from book inner join bookEnrol on book.id=bookEnrol.bookid AND booker.state=1
group by book.id ;
2
第(1)题练习使用group by /having 子句。类似的笔试题还有：
表一：各种产品年销售量统计表 sale
年 产品 销量
2005 a 700
2005 b 550
2005 c 600
2006 a 340
2006 b 500
2007 a 220
2007 b 350
要求得到的结果应为：
年 产品 销量
2005 a 700
2006 b 500
2007 b 350
即：每年销量最多的产品的相关信息。
参考答案：
Select * from sale a where not exists(select * from sale where 年=a.年 and 销量>a.销量);
--or:
select * from sale a inner join (select 年,max(销量) as 销量from sale group by 年) b
on a.年=b.年 and a.销量=b.销量
3.查询语句排名问题：
名次 姓名 月积分(char) 总积分（char）
1 WhatIsJava 1 99
2 水王 76 981
3 新浪网 65 96
4 牛人 22 9
5 中国队 64 89
6 北林信息 66 66
7 加太阳 53 66
8 中成药 11 33
9 西洋参 25 26
10 大拿 33 23
如果用总积分做降序排序..因为总积分是字符型，所以排出来是这样子(9,8,7,6,5...)，要求按照总积分的数字大小排序。
select * from tablename order by cast(总积分 as int) desc
表tb
uid mark
1 7
1 6
2 3
2 2
2 5
3 4
3 3
4 8
4 1
4 3
想查出uid=4的名次：
uid mc
4 3
select uid, sum(mark) as total from tab_name group by uid order by total desc;
4
表A字段如下
month name income
月份 人员 收入
1 a 1000
2 a 2000
3 a 3000
要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入
要求列表输出为
月份 当月收入 上月收入 下月收入
2 2000 1000 3000
Select (Select Month From Table Where Month = To_Char(Sysdate, 'mm')) 月份,
(Select Sum(Income) From Table Where Month = To_Char(Sysdate, 'mm')) 当月收入,
(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate)) - 1) 上月收入,
(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate)) + 1) 下月收入
From Dual
5.删除重复记录
方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中
那些具有最大rowid的就可以了，其余全部删除。
实现方法：
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
查询到8记录.
查出重复记录
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
ROWID BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
删除重复记录
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
删除4个记录.
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
其他组合函数
Group by 子句
Distinct 关键字
伪列ROWNUM，用于为子查询返回的每个行分配序列值注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。where 后只能跟单行函数，不能有组函数。
使用TOP-N分析法
TOP-N分析法基于条件显示表中最上面N条记录或最下面N条记录
TOP-N查询包含以下内容：
1，一个用于排序数据的内联视图
2，使用ORDER BY子句或DESC参数的子查询
3，一个外层查询。由它决定最终记录中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE子句
//语法：
SELECT ROWNUM,column_list
FROM (SELECT column_list FROM table_name ORDER BY Top-n-column_name)
WHERE ROWNUM <= N
例1：查询Employee表的顶部10条记录
//方法1：单表时可以用
select cEmployeeCode,vFirstName,vLastName from employee where rownum <= 10
//方法2：较复杂的查询，建议使用这种
select * from (select rownum as num,cEmployeeCode,vFirstName,vLastName from employee)
where num <= 10
例2: 查询Employee表的 第1 到 第10条 记录，可以用于分页显示
//注意:因为这里子查询的rownum需要被外层查询所使用，因此要使用别名，否则将被认为是两个不同的rownum
select * from (select rownum as num,Employee.* from Employee) where num between 10 and 20
select * from (select rownum as num,Employee.* from Employee) where num between 1 and 10
SQL注入 1=1永远成立,相当于查询所有记录
select * from person_zdk where 1=1 or name like '%a%' and age=13;
DECODE函数
是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee.
SQL中的单记录函数
1.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;高乾竞电话
----------------
010-88888888转23
2.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
3..SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
4日期函数
如:LAST_DAY 返回本月日期的最后一天
具体参见oracle笔记.
其他主要函数：.TRUNC 按照指定的精度截取一个数；SQRT 返回数字n的根；POWER（n1,n2）返回n1的n2次方根；MOD(n1,n2) 返回一个n1除以n2的余数；FLOOR 对给定的数字取整数；REPLACE('string','s1','s2') string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换的字符串；LOWER 返回字符串,并将所有的字符小写；UPPER返回字符串,并将所有的字符大写；LENGTH
返回字符串的长度。
ORALCE常识 及 SQL 基本语法
1，ORACLE安装完成后的初始口令？
internal/oracle
sys/change_on_install
system/manager
scott/tiger scott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字
sysman/oem_temp
例：conn scott/tiger@jspdev;
conn system/manager@jspdev as sysdba;
2，IBM的Codd （Edgar Frank Codd）博士提出《大型共享数据库数据的关系模型》
3,ORACLE 9i 中的 i （internet）是因特网的意思
4,ORACLE的数据库的物理结构：数据文件、日志文件、控制文件
5,ORACLE的数据库的逻辑结构：表空间——表——段——区间——块
表空间 类似于SQLSERVER中数据库的概念
6,SYSDATE 返回当前系统日期（说明：当函数没有参数时可以省略括号）
7,在SQL PLUS中 执行缓冲区中的SQL命令的方式：
SQL> run
SQL> r
SQL> /
8,在SQL PLUS中 修改当前会话的日期显示格式
SQL> alter session set nls_date_format = 'YYYY-MM-DD'
9,使用临时变量，提高输入效率
SQL> insert into emp(empno,ename,sal) values(&employeeno,'&employeename',&employeesal);
10,从其他表中复制数据并写入表
SQL> insert into managers(id,name,salary,hiredate)
SQL> select empno,ename,sal,hiredate
SQL> from emp
SQL> where job = 'MANAGER';
11,修改表中的记录
SQL> update table set column = value [,column = value,……] [where condition];
12,删除表中的记录
SQL> delete [from] table [where condition];
13,数据库事务，事务是数据库一组逻辑操作的集合
一个事务可能是：
多个DML语句
单个DDL语句
单个DCL语句
14,事务控制使用 savepoint,rollback,commit 关键字
SQL> savepoint aaa;
SQL> rollback to aaa;
SQL> commit;
15,查询表中的数据
select * from table_name;
select column_list from table_name;
16,Number and Date 可以用于算术运算
因为 Date 类型 其实存储为 Number 类型
17,用运算表达式产生新列
SQL> select ename,sal,sal+3000 from emp;
SQL> select ename,sal,12*sal+100 from emp;
18,算术表达式中NULL值错误的处理
因为任何数与NULL运算无意义，所以为避免错误，需要用其他值替换NULL值
例如：
SQL> select ename "姓名",12*sal+comm "年薪" from emp where ename = 'KING';
姓名 薪水
---------- ----------
KING
因为comm（提成工资）列为NULL值，结果也出现了NULL值，所以需要用0来替换NULL
注意函数nvl的使用 NVL(原值,新值)
SQL> select ename "姓名",12*sal+NVL(comm,0) "年薪" from emp where ename = 'KING';
员工姓名 员工薪水
---------- ----------
KING 60000
——————————————
19,使用友好的列名,有下面三种形式
SQL> select ename as 姓名, sal 月薪, sal*12 "年薪" from emp
20,过滤重复行，使用关键字 distinct
SQL> select distinct * from emp;
21,SQL PLUS访问ORACLE数据库的原理
SQL*Plus —> Buffer —> Server —> Query Result
22,where 子句中 字符型 是区分大小写的，最好都转成大写
因为在ORACLE库中，字符会转换成大写来保存
23,比较运算符：等于"=",不等于有两种"<>"或者"!="
24,复杂的比较运算符：
between …… and ……
in (……value list……)
like （% 代表匹配至多个任意字符，_ 代表单个任意字符）
null （与NULL进行比较时，需要使用 is null 或者 is not null）
25,逻辑运算符，按优先级从高到低排列
Not , And , Or
26,Order by 子句 中 （ asc 表示 升序，desc 表示降序）
27,ORACLE 函数，分为
单行函数：每条记录返回一个结果值
多行函数：多条记录返回一个结果值
28,字符函数——转换函数
LOWER：转为小写
UPPER：转为大写
INITCAP：将每个单词的首字母大写，其他字母小写
29,字符函数——操纵函数（注意：ORACLE以UNICODE存储字符）
CONCAT:连接两个字符串，与并置运算符“||”类似
SUBSTR:substr(string,position,length) 从string中的position开始取length个字符
LENGTH:返回字符串的长度
INSTR: instr(string,value) 返回 value 在 string 的起始位置
LPAD: lpad(string,number,value) 若string不够number位，从左起用vlaue字符串填充(不支持中文)
30,四舍五入函数 round(数值,小数位)
SQL> SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
31,数值截取函数 trunct
SQL> SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
--------------- --------------- ----------------
45.92 45 40
32,求模函数 MOD(a,b) 返回a被b整除后的余数
33,Oracle内部默认的日期格式: DD-MON-YY （24-9月 -06）
34,DUAL ：哑元系统表，是名义表，只能范围唯一值
35,Date类型的算术运算，以天为单位
例如：部门编号为10的员工分别工作了多少年
SQL> select ename,(sysdate-hiredate)/365 as years from emp where deptno = 10;
ENAME YEARS
---------- ----------
CLARK 25.3108341
KING 24.8697382
MILLER 24.6861766
36,日期函数
MONTHS_BETWEEN 返回两个日期之间相差多少个月
ADD_MONTHS 在日期上加上月份数
NEXT_DAY 下一个日子 select next_day(sysdate,'星期一') from dual;
LAST_DAY 该月的最后一天
ROUND 四舍五入日期 round(sysdate,'year') 或者 round(sysdate,'month')
TRUNC 截取日期 trunc(sysdate,'year') 或者 trunc(sysdate,'month')
37,数据类型转换 —— Oracle 可隐式转换的情况有：
From To
varchar2 or char —— number （当字符串是数字字符时）
varchar2 or char —— date
number —— varchar2
date —— varchar2
38,数据类型转换 —— Oracle 数据类型转换函数
to_char
to_number
to_date
39,日期格式模型字符
YYYY 代表完整的年份
YEAR 年份
MM 两位数的月份
MONTH 月份的完整名称
DY 每星期中天的三个字符缩写
DAY 表示星期日——星期六
另外还有 D,DD,DDD 等。。。
40,NVL(value,substitute)
value:是可能有null的列，substitute是缺省值
这个函数的作用就是当出现null值的时候，后缺省值替换null
41,Coalesce(exp_name1,exp_name2……exp_n)
42,Decode 函数： Decode(exp,testvalue1,resultvalue1,testvalue2,resultvalue2)
例如，根据国家名称显示相应的国家代码:
1>创建国家表
create table countrys
(
vCountryName varchar2(50)
);
2>写入几行，分别为中国、日本、韩国
insert into countrys values ('&name');
3>用DECODE函数，进行匹配和显示
select vCountryName as "国家名称",
DECODE(vCountryName,'中国','086','日本','116') as "国家编号" from countrys;
国家名称 国家编号
-------------------------------------------------- ---
中国 086
日本 116
韩国
结果，在DECODE中存在且成功匹配的值将会被显示，否则显示为NULL
SQL语句书可以提高执行效率的方法
1、操作符号： NOT IN操作符
此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替 "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", "LIKE '%500'"，因为他们不走索引全是表扫描。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作。
2、注意union和union all的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
如: 两个表A和B都有一个序号字段ID,要求两个表中的ID字段最大的值：
select max(id) as max_id
from(
select id from 表A
union all
select id from 表B ) t
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。
4、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，
直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。
5、创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。
7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'是一样的。由于in会在比较多次，所以有时会慢些。
8、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。
9、WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，如果dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的 比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。所以尽量 将范围小的条件放在前面。。
10、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用 UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
11、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。
12、使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数
13、当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，在另一个连接中 SELECT * from sysobjects可以看到 SELECT INTO 会锁住系统表， Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。
14、一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快
15、一次更新多条记录比分多次更新每次一条快,就是说批处理好
16、慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。
17、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的 SQL语句，是控制流语言的集合，速度当然快。
18、不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。
19、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。
oracle Certification Program (OCP认证)的题目
(1) A 表中有100条记录.
Select * FROM A Where A.COLUMN1 = A.COLUMN1
这个语句返回几条记录? (简单吧,似乎1秒钟就有答案了:)
(2) Create SEQUENCE PEAK_NO
Select PEAK_NO.NEXTVAL FROM DUAL --> 假设返回1
10秒中后,再次做
Select PEAK_NO.NEXTVAL FROM DUAL --> 返回多少?
(3) SQL> connect sys as sysdba
Connected.
SQL> insert into dual values ( 'Y');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from dual;
COUNT(*)
----------
2
SQL> delete from dual;
commit;
-->DUAL里还 剩几条记录?
JUST TRY IT
一些高难度的SQL面试题
以 下的null代表真的null，写在这里只是为了让大家看清楚
根据如下表的查询结果，那么以下语句的结果是（知识点：not in/not exists+null）
SQL> select * from usertable;
USERID USERNAME
----------- ----------------
1 user1
2 null
3 user3
4 null
5 user5
6 user6
SQL> select * from usergrade;
USERID USERNAME GRADE
---------- ---------------- ----------
1 user1 90
2 null 80
7 user7 80
8 user8 90
执行语句：
select count(*) from usergrade where username not in (select username from usertable);
select count(*) from usergrade g where not exists
(select null from usertable t where t.userid=g.userid and t.username=g.username);
结 果为：语句1( 0 ) 语句2 ( 3 )
A: 0 B:1 C:2 D:3 E:NULL
2
在以下的表的显示结果中，以下语句的执行结果是（知识 点：in/exists+rownum）
SQL> select * from usertable;
USERID USERNAME
----------- ----------------
1 user1
2 user2
3 user3
4 user4
5 user5
SQL> select * from usergrade;
USERNAME GRADE
---------------- ----------
user9 90
user8 80
user7 80
user2 90
user1 100
user1 80
执行语句
Select count(*) from usertable t1 where username in
(select username from usergrade t2 where rownum <=1);
Select count(*) from usertable t1 where exists
(select 'x' from usergrade t2 where t1.username=t2.username and rownum <=1);
以上语句的执行结果 是：( ) ( )
A: 0 B: 1 C: 2 D: 3
根 据以下的在不同会话与时间点的操作，判断结果是多少，其中时间T1原始表记录为；
select * from emp;
EMPNO DEPTNO SALARY
----- ------ ------
100 1 55
101 1 50
select * from dept;
DEPTNO SUM_OF_SALARY
------ -------------
1 105
2
可以看到，现在因为还没有部门2的员工，所以总薪水为null，现在，
有两个 不同的用户（会话）在不同的时间点（按照特定的时间顺序）执行了一系列的操作，那么在其中或最后的结果为：
time session 1 session2
----------- ------------------------------- -----------------------------------
T1 insert into emp
values(102,2,60)
T2 update emp set deptno =2
where empno=100
T3 update dept set sum_of_salary =
(select sum(salary) from emp
where emp.deptno=dept.deptno)
where dept.deptno in(1,2);
T4 update dept set sum_of_salary =
(select sum(salary) from emp
where emp.deptno=dept.deptno)
where dept.deptno in(1,2);
T5 commit;
T6 select sum(salary) from emp group by deptno;
问题一：这里会话2的查询结果为：
T7 commit;
=======到这里为此，所有事务都已完成，所以以下查询与会话已没有关系========
T8 select sum(salary) from emp group by deptno;
问题二：这里查询结果为
T9 select * from dept;
问题三：这里查询的结果为
问题一的结果( ) 问题 二的结果是( ) 问题三的结果是( )
A: B:
---------------- ----------------
1 50 1 50
2 60 2 55
C: D:
---------------- ----------------
1 50 1 115
2 115 2 50
E: F:
---------------- ----------------
1 105 1 110
2 60 2 55
有表一的查询结果如下，该表为学生成绩表（知识点：关联更新）
select id,grade from student_grade
ID GRADE
-------- -----------
1 50
2 40
3 70
4 80
5 30
6 90
表二为补考成绩表
select id,grade from student_makeup
ID GRADE
-------- -----------
1 60
2 80
5 60
现在有一个dba通过如下语句把补考成绩更新到成绩表中，并提交：
update student_grade s set s.grade =
(select t.grade from student_makeup t
where s.id=t.id);
commit;
请问之后查询：
select GRADE from student_grade where id = 3;结果为：
A: 0 B: 70 C: null D: 以上都不对
根据以下的在不同会话与时间点的操作，判断结果是多少，
其中时间T1
session1 session2
-------------------------------------- ----------------------------------------
T1 select count(*) from t;
--显示结果(1000)条
T2 delete from t where rownum <=100;
T3 begin
delete from t where rownum <=100;
commit;
end;
/
T4 truncate table t;
T5 select count(*) from t;
-- 这里显示的结果是多少
A: 1000 B: 900 C: 800 D: 0
1、表：table1(FId,Fclass,Fscore),用最高效最简单的SQL列出各班成绩最高的列表，显示班级，成绩两个字段。
select fclass,max(fscore) from table1 group by fclass,fid
2、有一个表table1有两个字段FID，Fno，字都非空，写一个SQL语句列出该表中一个FID对应多个不同的Fno的纪录。
类如：
101a1001
101a1001
102a1002
102a1003
103a1004
104a1005
104a1006
105a1007
105a1007
105a1007
结果：
102a1002
102a1003
104a1005
104a1006
select t2.* from table1 t1, table1 t2 where t1.fid = t2.fid and t1.fno <> t2.fno;
3、有员工表empinfo
(
Fempno varchar2(10) not null pk,
Fempname varchar2(20) not null,
Fage number not null,
Fsalary number not null
);
假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人：
fsalary>9999 and fage > 35
fsalary>9999 and fage < 35
fsalary <9999 and fage > 35
fsalary <9999 and fage < 35
每种员工的数量；
select sum(case when fsalary > 9999 and fage > 35
then 1
else 0end) as "fsalary>9999_fage>35",
sum(case when fsalary > 9999 and fage < 35
then 1
else 0
end) as "fsalary>9999_fage<35",
sum(case when fsalary < 9999 and fage > 35
then 1
else 0
end) as "fsalary<9999_fage>35",
sum(case when fsalary < 9999 and fage < 35
then 1
else 0
end) as "fsalary<9999_fage<35"
from empinfo;
4、表A字段如下
month person income
月份 人员 收入
要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入
要求列表输出为
月份 当月收入 上月收入 下月收入
MONTHS PERSON INCOME
---------- ---------- ----------200807 mantisXF 5000200806 mantisXF2 3500200806 mantisXF3 3000200805 mantisXF1 2000200805 mantisXF6 2200200804 mantisXF7 1800200803 8mantisXF 4000200802 9mantisXF 4200200802 10mantisXF 3300200801 11mantisXF 4600200809 11mantisXF 6800
11 rows selected
select months, max(incomes), max(prev_months), max(next_months)
from (select months,
incomes,
decode(lag(months) over(order by months),
to_char(add_months(to_date(months, 'yyyymm'), -1), 'yyyymm'), lag(incomes) over(order by months), 0) as prev_months, decode(lead(months) over(order by months), to_char(add_months(to_date(months, 'yyyymm'), 1), 'yyyymm'), lead(incomes) over(order by months), 0) as next_months from (select months, sum(income) as incomes from a group by months) aa) aaagroup by months;
MONTHS MAX(INCOMES) MAX(PREV_MONTHS) MAX(NEXT_MONTHS)---------- ------------ ---------------- ----------------200801 4600 0 7500200802 7500 4600 4000200803 4000 7500 1800200804 1800 4000 4200200805 4200 1800 6500200806 6500 4200 5000200807 5000 6500 0200809 6800 0 0
5，表B
C1 c2
2005-01-01 1
2005-01-01 3
2005-01-02 5
要求的处数据
2005-01-01 4
2005-01-02 5
合计 9
试用一个Sql语句完成。
select nvl(to_char(t02,'yyyy-mm-dd'),'合计'),sum(t01)from test
group by rollup(t02)
6，数据库1，2，3 范式的概念与理解。
7，简述oracle行触发器的变化表限制表的概念和使用限制，行触发器里面对这两个表有什么限制。
8、oracle临时表有几种。
临时表和普通表的主要区别有哪些，使用临时表的主要原因是什么？
9，怎么实现：使一个会话里面执行的多个过程函数或触发器里面都可以访问的全局变量的效果，并且要实现会话间隔离？
10，aa，bb表都有20个字段，且记录数量都很大，aa，bb表的X字段（非空）上有索引，
请用SQL列出aa表里面存在的X在bb表不存在的X的值，请写出认为最快的语句，并解译原因。
11，简述SGA主要组成结构和用途？
12什么是分区表？简述范围分区和列表分区的区别，分区表的主要优势有哪些？
13，背景：某数据运行在archivelog，且用rman作过全备份和数据库的冷备份，
且所有的归档日志都有，现控制文件全部损坏，其他文件全部完好，请问该怎么恢复该数据库，说一两种方法。
14，用rman写一个备份语句：备份表空间TSB，level 为2的增量备份。
15，有个表a(x number(20),y number(20))用最快速高效的SQL向该表插入从1开始的连续的1000万记录。
1、表：table1(FId,Fclass,Fscore),用最高效最简单的SQL列出各班成绩最高的列表，显示班级，成绩两个字段。
2、有一个表table1有两个字段FID，Fno，字都非空，写一个SQL语句列出该表中一个FID对应多个不同的Fno的纪录。
类如：
101 a1001
101 a1001
102 a1002
102 a1003
103 a1004
104 a1005
104 a1006
105 a1007
105 a1007
105 a1007
结果：
102 a1002
102 a1003
104 a1005
104 a1006
3、有员工表empinfo
(
Fempno varchar2(10) not null pk,
Fempname varchar2(20) not null,
Fage number not null,
Fsalary number not null
);
假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人：
fsalary>9999 and fage > 35
fsalary>9999 and fage < 35
fsalary<9999 and fage > 35
fsalary<9999 and fage < 35
每种员工的数量；
4、表A字段如下
month person income
月份 人员 收入
要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入
要求列表输出为
月份 当月收入 上月收入 下月收入
5，表B
C1 c2
2005-01-01 1
2005-01-01 3
2005-01-02 5
要求的处数据
2005-01-01 4
2005-01-02 5
合计 9
试用一个Sql语句完成。
6，数据库1，2，3 范式的概念与理解。
7，简述oracle行触发器的变化表限制表的概念和使用限制，行触发器里面对这两个表有什么限制。
8、oracle临时表有几种。
临时表和普通表的主要区别有哪些，使用临时表的主要原因是什么？
9，怎么实现：使一个会话里面执行的多个过程函数或触发器里面都可以访问的全局变量的效果，并且要实现会话间隔离？
10，aa，bb表都有20个字段，且记录数量都很大，aa，bb表的X字段（非空）上有索引，
请用SQL列出aa表里面存在的X在bb表不存在的X的值，请写出认为最快的语句，并解译原因。
11，简述SGA主要组成结构和用途？
12什么是分区表？简述范围分区和列表分区的区别，分区表的主要优势有哪些？
13，背景：某数据运行在archivelog，且用rman作过全备份和数据库的冷备份，
且所有的归档日志都有，现控制文件全部损坏，其他文件全部完好，请问该怎么恢复该数据库，说一两种方法。
14，用rman写一个备份语句：备份表空间TSB，level 为2的增量备份。
15，有个表a(x number(20),y number(20))用最快速高效的SQL向该表插入从1开始的连续的1000万记录。
答案：
1、select Fclass,max(Fscore) from table1 group by Fclass
2、select * from table1 where FID in (select FID from table1 group by FID having (count(Distinct Fno))>=2)
3、select sum(case when fsalary>9999 and fage>35 then 1 else 0 end),
sum(case when fsalary>9999 and fage<35 then 1 else 0 end),
sum(case when fsalary<9999 and fage>35 then 1 else 0 end),
sum(case when fsalary<9999 and fage<35 then 1 else 0 end) from empinfo
4、
Select (Select Month From Table Where Month = To_Char(Sysdate, 'mm')) 月份,
(Select Sum(Income) From Table Where Month = To_Char(Sysdate, 'mm')) 当月收入,
(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate)) - 1) 上月收入,
(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate)) + 1) 下月收入
From Dual
5、select nvl(c1,'合计'),sum(c2) from B group by rollup(c1)
6.
关系数据库设计之时是要遵守一定的规则的。尤其是数据库设计范式
简单介绍1NF（第一范式），2NF（第二范式），3NF（第三范式），
第一范式（1NF）：在关系模式R中的每一个具体关系r中，如果每个属性值 都是不可再分的最小数据单位，则称R是第一范式的关系。
例：如职工号，姓名，电话号码组成一个表（一个人可能有一个办公室电话 和一个家里电话号码） 规范成为1NF有三种方法：
一是重复存储职工号和姓名。这样，关键字只能是电话号码。
二是职工号为关键字，电话号码分为单位电话和住宅电话两个属性
三是职工号为关键字，但强制每条记录只能有一个电话号码。
以上三个方法，第一种方法最不可取，按实际情况选取后两种情况。
第二范式（2NF）：如果关系模式R（U，F）中的所有非主属性都完全依赖于任意一个候选关键字，则称关系R 是属于第二范式的。
例：选课关系 SCI（SNO，CNO，GRADE，CREDIT）其中SNO为学号， CNO为课程号，GRADEGE 为成绩，CREDIT 为学分。 由以上
条件，关键字为组合关键字（SNO，CNO）
在应用中使用以上关系模式有以下问题：
a.数据冗余，假设同一门课由40个学生选修，学分就 重复40次。
b.更新异常，若调整了某课程的学分，相应的元组CREDIT值都要更新，有可能会出现同一门课学分不同。
c.插入异常，如计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。
d.删除异常，若学生已经结业，从当前数据库删除选修记录。某些门课程新生尚未选修，则此门课程及学分记录无法保存。
原因：非关键字属性CREDIT仅函数依赖于CNO，也就是CREDIT部分依赖组合关键字（SNO，CNO）而不是完全依赖。
解决方法：分成两个关系模式 SC1（SNO，CNO，GRADE），C2（CNO，CREDIT）。新关系包括两个关系模式，它们之间通过SCN中
的外关键字CNO相联系，需要时再进行自然联接，恢复了原来的关系
第三范式（3NF）：如果关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递信赖，则称关系R是属于第三范式的。
例：如S1（SNO，SNAME，DNO，DNAME，LOCATION） 各属性分别代表学号，
姓名，所在系，系名称，系地址。
关键字SNO决定各个属性。由于是单个关键字，没有部分依赖的问题，肯定是2NF。但这关系肯定有大量的冗余，有关学生所在的几个
属性DNO，DNAME，LOCATION将重复存储，插入，删除和修改时也将产生类似以上例的情况。
原因：关系中存在传递依赖造成的。即SNO -> DNO。 而DNO -> SNO却不存在，DNO -> LOCATION, 因此关键辽 SNO 对 LOCATIO
N 函数决定是通过传递依赖 SNO -> LOCATION 实现的。也就是说，SNO不直接决定非主属性LOCATION。
解决目地：每个关系模式中不能留有传递依赖。
解决方法：分为两个关系 S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）
注意：关系S中不能没有外关键字DNO。否则两个关系之间失去联系。
7.
变化表mutating table
被DML语句正在修改的表
需要作为DELETE CASCADE参考完整性限制的结果进行更新的表也是变化的
限制:对于Session本身，不能读取正在变化的表
限制表constraining table
需要对参考完整性限制执行读操作的表
限制:如果限制列正在被改变，那么读取或修改会触发错误，但是修改其它列是允许的。
8.
在Oracle中，可以创建以下两种临时表：
a。会话特有的临时表
CREATE GLOBAL TEMPORARY ( )
ON COMMIT PRESERVE ROWS；
b。事务特有的临时表
CREATE GLOBAL TEMPORARY ( )
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的临时表虽然是存在的，但是你试一下insert 一条记录然后用别的连接登上去select，记录是空的，明白了吧。
下面两句话再贴一下：
--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
9.--个人理解就是建立一个包,将常量或所谓的全局变量用包中的函数返回出来就可以了,摘抄一短网上的解决方法
Oracle数据库程序包中的变量，在本程序包中可以直接引用，但是在程序包之外，则不可以直接引用。对程序包变量的存取，可以为每个变量配套相应的存储 过程<用于存储数据>和函数<用于读取数据>来实现。
3.2 实例
--定义程序包
create or replace package PKG_System_Constant is
C_SystemTitle nVarChar2(100):='测试全局程序变量'; --定义常数
--获取常数<系统标题>
Function FN_GetSystemTitle
Return nVarChar2;
G_CurrentDate Date:=SysDate; --定义全局变量
--获取全局变量<当前日期>
Function FN_GetCurrentDate
Return Date;
--设置全局变量<当前日期>
Procedure SP_SetCurrentDate
(P_CurrentDate In Date);
End PKG_System_Constant;
/
create or replace package body PKG_System_Constant is
--获取常数<系统标题>
Function FN_GetSystemTitle
Return nVarChar2
Is
Begin
Return C_SystemTitle;
End FN_GetSystemTitle;
--获取全局变量<当前日期>
Function FN_GetCurrentDate
Return Date
Is
Begin
Return G_CurrentDate;
End FN_GetCurrentDate;
--设置全局变量<当前日期>
Procedure SP_SetCurrentDate
(P_CurrentDate In Date)
Is
Begin
G_CurrentDate:=P_CurrentDate;
End SP_SetCurrentDate;
End PKG_System_Constant;
/
3.3 测试
--测试读取常数
Select PKG_System_Constant.FN_GetSystemTitle From Dual;
--测试设置全局变量
Declare
Begin
PKG_System_Constant.SP_SetCurrentDate(To_Date('2001.01.01','yyyy.mm.dd'));
End;
/
--测试读取全局变量
Select PKG_System_Constant.FN_GetCurrentDate From Dual;
10.
select aa.x from aa
where not exists (select 'x' from bb where aa.x = bb.x) ;
以上语句同时使用到了aa中x的索引和的bb中x的索引
11
SGA是Oracle为一个实例分配的一组共享内存缓冲区，它包含该实例的数据和控制信息。SGA在实例启动时被自动分配，当实例关闭时被收回。数据库的 所有数据操作都要通过SGA来进行。
SGA中内存根据存放信息的不同，可以分为如下几个区域：
a.Buffer Cache：存放数据库中数据库块的拷贝。它是由一组缓冲块所组成，这些缓冲块为所有与该实例相链接的用户进程所共享。缓冲块的数目由初始化参数 DB_BLOCK_BUFFERS确定，缓冲块的大小由初始化参数DB_BLOCK_SIZE确定。大的数据块可提高查询速度。它由DBWR操作。
b. 日志缓冲区Redo Log Buffer：存放数据操作的更改信息。它们以日志项（redo entry）的形式存放在日志缓冲区中。当需要进行数据库恢复时，日志项用于重构或回滚对数据库所做的变更。日志缓冲区的大小由初始化参数 LOG_BUFFER确定。大的日志缓冲区可减少日志文件I/O的次数。后台进程LGWR将日志缓冲区中的信息写入磁盘的日志文件中，可启动ARCH后台 进程进行日志信息归档。
c. 共享池Shared Pool：包含用来处理的SQL语句信息。它包含共享SQL区和数据字典存储区。共享SQL区包含执行特定的SQL语句所用的信息。数据字典区用于存放数 据字典，它为所有用户进程所共享。
12.
使用分区方式建立的表叫分区表
范围分区
每个分区都由一个分区键值范围指定（对于一个以日期列作为分区键的表，“2005 年 1 月”分区包含分区键值为从“2005 年 1 月 1 日”
到“2005 年 1 月 31 日”的行）。
列表分区
每个分区都由一个分区键值列表指定（对于一个地区列作为分区键的表，“北美”分区可能包含值“加拿大”“美国”和“墨西哥”）。
分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以 极大简化常见的管理任务。通过分区,数据库设计人员和管理员能够解决前沿应用程序带来的一些难题。分区是构建千兆字节数据系统或超高可用性系统的关键工 具。
13
回复的方法:
一.使用冷备份,直接将冷备份的文件全部COPY到原先的目录下,在从新启动数据库就可以
二.使用归档日志,
1.启动数据库NOMOUNT
2.创建控制文件,控制文件指定数据文件和重做日志文件的位置.
3.使用RECOVER DATABASE using backup controlfile until cancel 命令回复数据库,这时可以使用归档日志
4.ALETER DATABASE OPEN RESETLOGS;
5.重新备份数据库和控制文件
14的话参考RMAN的使用手册
15略
1、对数据库SQL2005、ORACLE熟悉吗？
SQL2005是微软公司的数据库产品。是一个RDBMS数据库，一般应用在一些中型数据库的应用，不能跨平台。
ORACLE是ORACLE公司的数据产品，支持海量数据存储，支持分布式布暑，支持多用户，跨平台，数据安全完整性控制性能优越，是一个ORDBMS， 一般用在大型公司。
2、能不能设计数据库？如何实现数据库导入与导出的更新
使用POWERDISINE工具的使用，一般满足第三范式就可以了。EXP与IMP数据库的逻辑导入与导出
3、如何只显示重复数据，或不显示重复数据
显示重复：select * from tablename group by id having count(*)>1
不显示重复：select * from tablename group by id having count(*)=1
4、什么是数据库的映射
就是将数据库的表与字段对应到模型层类名与属性的过程
5、写分页有哪些方法，你一般用什么方法？用SQL语句写一个分页？
如何用存储过程写分页？
在SQLSERVER中使用TOP分页，在ORACLE中用ROWNUM，或分析函数ROW_NUMBER
使用TOP：
select top 20,n.* from tablename n minus select top 10,m.* from tablename m
使用分析函数：
select * from
(select n.*,row_number() over(order by columnname) num from tablename n)
where num>=10 and num <=20;
使用过程时，只要将分页的范围用两个参数就可以实现。在ORACLE中，要将过程封装在包里，还要用动态游标变量才能实现数据集的返回。
6、ORACLE中左连接与右连接
左连接：LEFT JOIN 右连接：RIGHT JOIN
select n.column,m.column from tablename1 n left join tablename2 m
on n.columnname=m.columnname
用WHERE实现：
select n.column,m.column from tablename1 n， tablename2 m
where n.columnname(+)=m.columnname
7、什么是反射、序列化、反序列化？事务有几种级别？
反射是在程序运行时动态访问DDL的一种方式。序列化是将对象对二进制、XML等方式直接向文件的存储。反序列化是将存储到文件的对象取出的过程。事务的 级别的三种：页面级、应用程序级、数据库级。
8、数据测试如何测试？
在PLSQL里对过程或函数可能通过专用的测试工具，通过对
9、用事务的时候，如果在业务逻辑层中，调用数据库访问层中的方法，访问层中有很多类，类又有很多方法，每个方法都要实现，那么如何处理？
通用数据访问层的实现
10、什么时候会用到触发器
A安全管理、B日志管理、C复杂业务逻辑实现
11、如何在数据库中显示树控制？
用父ID与子ID来实现
12、如何实现数据库的优化？
A、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索 引等。
B、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的 Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是 不同的。
C、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。 ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。
D、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲 区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而 引起虚拟内存的页面交换，这样反而会降低系统。
E、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。
F、调整操作系统参数，例如：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。
3、BLOB和CLOB的区别在于
A CLOB只能存放字符类型的数据，而BLOB没有任何限制
B BLOB只能存放字符类型的数据，而CLOB没有任何限制
C CLOB只能存放小于4000字节的数据，而BLOB可以存放大于4000字节的数据
D BLOB只能存放小于4000字节的数据，而CLOB可以存放大于4000字节的数据
4、BFILE属于（）数据类型
A 简单
B 标量
C 大对象
D 文件
三号题选A，blob和clob都是大对象数据类型，4000字节数据限制是在已被建议不再使用的long/long-raw中的限制，lob类型没有4000字节限制。clob 指 charactor lob，blob 指 binary lob，因此 clob 只能存放字符型数据，而 blob 没有限制。
四号题应该选 D, bfile代表文件，但是不想 lob 类型内容是存放在数据库表内部的，而是存放在数据库所在主机的文件系统中，因此 bfile 不是大对象。
完成下列操作，写出相应的SQL语句
创建表空间neuspace，数据文件命名为neudata.dbf，存放在 d:\data目录下，文件大小为200MB，设为自动增长，增量5MB，文件最大为500MB。(8分)
答：create tablespace neuspace datafile ‘d:\data\neudata.dbf’ size 200m auto extend on next 5m maxsize 500m;
2. 假设表空间neuspace已用尽500MB空间，现要求增加一个数据文件，存放在e:\appdata目录下，文件名为appneudata，大小为 500MB，不自动增长。（5分）
答：alter tablespace neuspace add datafile ‘e:\appdata\appneudata.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 |
日期 |
无 |
无 |
生日 |
|
|
变长字符 (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,
score number(5,2) constraint student_score_ck check(score>=0 and score<=100),
classno number(2) constraint student_classno_fk references 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 |
|
SCORE |
CLASSNO |
|
从stuseq取值 |
tom |
男 |
1979-2-3 14:30:25 |
tom@163.net |
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@163.net’, 89.50, 1);
insert into student (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 score>85.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, c.cname 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) 在关系R中，代数表达式 3 <4（R） 表示 ( )
A. 从R中选择值为3的分量小于第4个分量的元组组成的关系
B. 从R中选择第3个分量值小于第4个分量的元组组成的关系
C. 从R中选择第3个分量的值小于4的元组组成的关系
D. 从R中选择所有元组组成的关系
(2) 下面那些内容通常不属于Oracle数据库管理员的职责（）
A.创建新用户 B. 创建数据库对象 C.安装Oracle软件 D. 操纵数据库数据的应用程序开发
(3) 在Oracle 10G中，下面的命令快为什么会失败，选择一个最佳答案（）
run
{
connect target sys/oracle@ocp10g;
backup database including current controlfile();
}
A. 不能作为SYS进行连接，必须作为SYSDBA进行连接
B. 命令块中不能出现CONNECT关键字
C. 命令块中缺少ALLOCATE CHANNEL 命令
D.以上都不正确
(4)下面那些内容通常不属于Oracle数据库管理员的职责（）
A.创建新用户 B.创建数据库对象
C.安装Oracle软件 D.操纵数据库数据的应用程序开发
(5)Hibernate中关于使用HQL语句描述不正确的是（）
A.是一种符合对象语言的查询语句
B.能够避免使用 sql 的情况下依赖数据库特征的情况出现
C.能够根据 OO 的习惯去进行实体的查询
D.理解SQL的人很难理解HQL
(6)在使用JDBC连接到数据源过程中，我们使用了以下getConnection方法调用： Connection conn=DriverManager.getConnection( jdbc:odbc:thin:@host:1521:mydb”, “scott”, “tiger”);则（ ）
A该连接字符串是错误的
B该语句建立了一个到本地ODBC数据源的连接
C该语句建立了一个到本地Oracle数据库的连接
D该语句建立了一个到本地JDataStore数据库的连接
(7)试图创建一个表空间，但是却收到无法为这个表空间创建数据文件的错误信息，希望创建的数据文件的大小为3GB，同时为表空间指定了 SMALLFILE选项，指定驻留数据文件的操作系统目录经验证属于与Oracle相同的用户，并且该用户具有完整的读/写权限，作为SYSTEM登录数 据库，而硬盘上具有足够的磁盘空间，那么出现错误的原因可能是什么（）
A指定SALLFILE选项时，不能在Oracle数据库内创建大于2GB的文件
B操作系统无法创建大于2GB的文件
C必须为数据文件规范指定WITH OVERWRITE选项
D必须为数据文件规范指定REUSE选项
(8) Hibernate中关于使用HQL语句描述不正确的是（）
A.是一种符合对象语言的查询语句
B.能够避免使用 sql 的情况下依赖数据库特征的情况出现
C.能够根据 OO 的习惯去进行实体的查询
D.理解SQL的人很难理解HQL
(9)下列哪个术语描述了栈(Stack)类使用List的内部实例实现。（）
A关联 B特化 C泛化 D组装
(10)有关系模式A(C，T，H，R，S)，其中各属性的含义是：C：课程 T：教员 H：上课时间 R：教室 S：学生 根据语义有如下函数依赖集：F={C→T，(H，R)→C，(H，T)→R，(H，S)→R} 现将关系模式A分解为两个关系模式A1（C,T），A2（H,R,S），则其中的A1的规范化程度达到
A. 1NF B.2NF C.3NF D.BCNF
(11).在Oracle 10G中，下列哪个进程负责实现 Automatic Shared Memory Management ()
A. MMAN 进程
B. MMON进程
C. MMNL进程
D. PMON进程
(12) .在Oracle 10G中，下列哪一个选项不是PGA的一部分（）
A.绑定信息
B.分析信息
C.会话变量
D.排序空间
(13).在Oracle 10G中，下列那些内容是创建数据库所必须的，选择一个最佳答案（）
A. 操作系统根用户（针对Unix/Linux系统）或Administrator(针对Windows系统)的口令
B. 运行DBCA的权限
C. RAM的大小不少于SGA的大小
D. 以上都不是
(14).在Oracle 10G中，如何能够减少一个索引段所占有的空间（）
A.聚结这个索引
B.缩小这个索引
C.重构这个索引
D.使用CASCADE选项缩小这个索引表
(15) .在Oracle 10G中，Database Control是一种多层WEB应用程序，哪一层负责窗口管理（）
A.dbconsole 中间层
B.数据库层内的过程
C.客户浏览
D. OC4J应用程序运行时环境
(16) 在Oracle 10G中，下列那些内容是创建数据库所必须的，选择一个最佳答案（）
A.操作系统根用户（针对Unix/Linux系统）或Administrator(针对Windows系统)的口令
B.运行DBCA的权限
C.RAM的大小不少于SGA的大小
D.以上都不是
(17) .有关系模式A(C，T，H，R，S)，其中各属性的含义是：
C：课程 T：教员 H：上课时间 R：教室 S：学生
根据语义有如下函数依赖集：
F={C→T，(H，R)→C，(H，T)→R，(H，S)→R}
关系模式A的规范化程度最高达到______
A 1NF
B 2NF
C 3NF
D BCNF
(18) .在Oracle 10G中，如何能够减少一个索引段所占有的空间（）
A聚结这个索引
B缩小这个索引
C重构这个索引
D使用CASCADE选项缩小这个索引表
(19) .在Oracle 10G中，闪回存在外键关系的两个表的最佳方法是什么（）
A先闪回子表，然后再闪回父表
B先闪回父表，然后再闪回子表
C在一个操作中闪回这两个表
D没有其他办法，闪回操作不保护外键约束
(20) 在Oracle 10G中，AWR快照在何时生成（）
A每隔一个小时
B每隔十分钟
C根据要求决定
D根据要求定期生成
(21) .在Oracle 10G中，打开数据库时，下列那些文件必须被同步，选择一个最佳答案（）
A数据文件、联机重做日志文件以及控制文件
B参数文件和口令文件
C所有多元化控制文件副本
D不需要同步任何文件，SMON进程会在打开数据库之后通过实例恢复来同步所有文件
(22) .在Oracle 10G中，如果已经创建了一个数据库，但是无法使用Database Control进行连接，这是什么原因，选择一个最佳答案（）
A没有通过操作系统的身份验证，或者没有进行口令文件身份验证
B没有运行脚本创建Database Control
CGrid Control是Database Control的必备条件
D没有被许可使用Database Control
(23) .在Oracle 10数据库中，如果需要以秒为单位记录日期/时间值，下列那种数据类型的列适合存储这个信息（）
A TIME
B DATETIME
C DATE或TIMESTAMP
D 因为Oracle的内部数据类型只能存储日期和时间，必须开发一种自定义的数据类型
实现
(24) .在Oracle 10G中，如何连接ASM实例（）
A只使用操作系统身份验证
B只使用口令文件身份验证
C只使用数据字典身份验证
D以上选项都不正确
(25).在Oracle 10G中，当在执行一条多记录更新语句时会违反某个约束，那会出现什么情况，选择一个最佳答案（）
A违反约束的更新会被回滚，这条语句的剩余部分则保持不变
B整条语句都会被回滚
C整个事务都会被回滚
D取决于是否执行了alter session enable resumable
(26) .在Oracle 10G中，自动工作负荷库（简写AWR）被存储在哪个位置（）
A存储在SYSAUX表空间内
B储在SYSTEM表空间内
C在系统全局区内
D我们可以在数据库创建阶段选择AWR的存储位置，随后还可以重新定位这个位置
(27)在关系模式R(U,F)中,X，Y，Z是U中属性，则多值依赖的传递律是 ( )
A如果X→→Y，Y→→Z，则X→→Z
B如果X→→Y，Y→→Z，则X→→YZ
C如果X→→Z，Y→→Z，则X→→YZ
D如果X→→Y，Y→→Z，则X→→Z-Y
(28) .数据库中全体数据的整体逻辑结构描述称为 ( )
A存储模式
B内模式
C外模式
D右外模式
(29) .在使用JDBC连接到数据源过程中，我们使用了以下getConnection方法调用： Connection conn=DriverManager.getConnection( jdbc:odbc:thin:@host:1521:mydb”, “scott”, “tiger”);则（ ）
A该连接字符串是错误的
B该语句建立了一个到本地ODBC数据源的连接
C该语句建立了一个到本地Oracle数据库的连接
D该语句建立了一个到本地JDataStore数据库的连接
(30) 物理结构设计的任务是设计数据库的( )
A存储格式
B存取方法
C存储结构与存取方法
D存储模式
(31)分布式数据库两阶段提交协议是指( )
A加锁阶段、解锁阶段
B扩展阶段、收缩阶段
C获取阶段、运行阶段
D表决阶段、执行阶段
(32) 在Oracle 10G中，创建一个新的用户帐户时，如果没有指定TEMPORAAY TABLESPACE ，那么该参数在这个用户被创建时具有怎样的值（）
A SYSTEM
B TEMP
C NULL
D数据库默认的临时表空间
(33)在以下的DBMS中，可用于UNIX和Windows操作系统的是( )
A SQLServer 2000
B foxpro
C Sybase
D access
(34) 在Oracle 10G中，下列哪种文件可以被视为非关键的（）
A临时数据文件
B撤销数据文件
C复用的控制文件
D以上所有文件
(35) 在Oracle 10G中，用户如何改变其有效的配置文件（）
A ALTER USER SET PROFILE=NewProfile
B ALTER SYSTEM SET PROFILE=NewProfile
C ALTER SESSION SET PROFILE=NewProfile
D用户无法改变其有效的配置文件
(36)在Oracle 10G中，RDBMS实例能够访问ASM文件之前，ASM实例必须位于哪一种模式中（）
A NOMOUNT模式
B MOUNT模式
C OPEN模式
D MOUNT或者OPEN模式
(2010年01月08日) 发表于 JavaEye博客
一、 求1-100之间的素数
declare
fag boolean:=true;
begin
for i in 1..100 loop
for j in 2..i-1 loop
if mod(i,j)=0 then
fag:=false;
end if;
end loop;
if fag then
dbms_output.put_line(i);
end if;
fag:=true;
end loop;
end;
二、 对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理
declare
cursor c1 is select * from emp;
c1rec c1%rowtype;
v_loc varchar2(20);
begin
for c1rec in c1 loop
select loc into v_loc from dept where deptno = c1rec.deptno;
if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then
update emp set sal = sal * 1.15 where empno = c1rec.empno;
elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then
update emp set sal = sal * 0.95 where empno = c1rec.empno;
else
null;
end if;
end loop;
end;
三、对直接上级是'BLAKE'的所有员工，按照参加工作的时间加薪：
81年6月以前的加薪10％
81年6月以后的加薪5％
declare
cursor c1 is select * from emp where mgr = (select
empno from emp where ename='BLAKE'); --直接上级是'BLAKE'的所有员工
c1rec c1%rowtype;
begin
for c1rec in c1 loop
if c1rec.hiredate < '01-6月-81' then
update emp set sal = sal * 1.1 where empno = c1rec.empno;
else
update emp set sal = sal * 1.05 where empno = c1rec.empno;
end if;
end loop;
end;
三、 根据员工在各自部门中的工资高低排出在部门中的名次(允许并列).
<1> 一条SQL语句
select deptno,ename,sal,(select count(*) + 1
from emp where deptno = a.deptno
and sal > a.sal) as ord
from emp a
order by deptno,sal desc;
<2> PL/SQL块
declare
cursor cc is
select * from dept;
ccrec cc%rowtype;
cursor ck(no number) is
select * from emp where deptno = no order by sal desc;
ckrec ck%rowtype;
i number;
j number;
v_sal number:=-1;
begin
for ccrec in cc loop
i := 0;
for ckrec in ck(ccrec.deptno) loop
i := i + 1;
--写入临时表
if ckrec.sal = v_sal then
null;
else
j:=i;
end if;
--显示
DBMS_OUTPUT.put_line(ccrec.deptno||chr(9)||ccrec.ename||chr(9)||ckrec.sal||chr(9)||j);
v_sal := ckrec.sal;
end loop;
end loop;
end;
四、编写一个触发器实现如下功能：
对修改职工薪金的操作进行合法性检查：
a) 修改后的薪金要大于修改前的薪金
b) 工资增量不能超过原工资的10%
c) 目前没有单位的职工不能涨工资
create or replace trigger tr1
after update of sal on emp
for each row
begin
if :new.sal <= :old.sal then
raise_application_error(-20001,'修改后的薪金要大于修改前的薪金');
elsif :new.sal > :old.sal * 1.1 then
raise_application_error(-20002,'工资增量不能超过原工资的10%');
elsif :old.deptno is null then
raise_application_error(-20003,'没有单位的职工不能涨工资');
end if;
end;
四、 编写一个PL/SQL程序块，对名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪。
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE SUBSTR(ename,1,1)=´A´ OR SUBSTR(ename,1,1)=´S´ FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
UPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF c1;
END LOOP;
END;
/
五、编写一PL/SQL，对所有的"销售员"(SALESMAN)增加佣金500.
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE job=´SALESMAN´ FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
UPDATE emp SET sal=NVL(sal,0)+500 WHERE CURRENT OF c1;
END LOOP;
END;
/
六、编写一PL/SQL，以提升两个资格最老的"职员"为"高级职员"。（工作时间越长，优先级越高）
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE job=´CLERK´ ORDER BY hiredate FOR UPDATE OF job;
--升序排列，工龄长的在前面
BEGIN
FOR i IN c1
LOOP
EXIT WHEN c1%ROWCOUNT>2;
DBMS_OUTPUT.PUT_LINE(i.ename);
UPDATE emp SET job=´HIGHCLERK´ WHERE CURRENT OF c1;
END LOOP;
END;
/
七、编写一PL/SQL，对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于5000，则取消加薪。
DECLARE
CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
IF (i.sal+i.sal*0.1)<=5000 THEN
UPDATE emp SET sal=sal+sal*0.1 where
Empno=i.empno
DBMS_OUTPUT.PUT_LINE(i.sal);
END IF;
END LOOP;
END;
/
八、显示EMP中的第四条记录。
DECLARE
CURSOR c1 IS SELECT * FROM emp;
BEGIN
FOR i IN c1
LOOP
IF c1%ROWCOUNT=4 THEN
DBMS_OUTPUT.PUT_LINE(i. EMPNO || ´ ´ ||i.ENAME || ´ ´ || i.JOB || ´ ´ || i.MGR || ´ ´ || i.HIREDATE || ´ ´ || i.SAL || ´ ´ || i.COMM || ´ ´ || i.DEPTNO);
EXIT;
END IF;
END LOOP;
END;
/
九、.编写一个给特殊雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过60个月，则给他额外加薪3000.
CREATE OR REPLACE PROCEDURE Raise_Sal(no IN NUMBER) AS
vhiredate DATE;
vsal emp.sal%TYPE;
BEGIN
SELECT hiredate,sal INTO vhiredate,vsal FROM emp WHERE empno=no;
IF MONTHS_BETWEEN(SYSDATE,vhiredate)>60 THEN
vsal:=NVL(vsal,0)*1.1+3000;
ELSE
vsal:=NVL(vsal,0)*1.1;
END IF;
UPDATE emp SET sal=vsal WHERE empno=no;
END;
/
VARIABLE no NUMBER
BEGIN
:no:=7369;
END;
/
十、编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：
Designation Raise
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Others 4501 and above.
如果薪水在此范围内，则显示消息"Salary is OK"，否则，更新薪水为该范围内的最水值。
CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR AS
vjob emp.job%TYPE;
vsal emp.sal%TYPE;
vmesg CHAR(50);
BEGIN
SELECT job,sal INTO vjob,vsal FROM emp WHERE empno=no;
IF vjob=´CLERK´ THEN
IF vsal>=1500 AND vsal<=2500 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=1500;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
ELSIF vjob=´SALESMAN´ THEN
IF vsal>=2501 AND vsal<=3500 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=2501;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
ELSIF vjob=´ANALYST´ THEN
IF vsal>=3501 AND vsal<=4500 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=3501;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
ELSE
IF vsal>=4501 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=4501;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
END IF;
UPDATE emp SET sal=vsal WHERE empno=no;
RETURN vmesg;
END;
/
DECLARE
vmesg CHAR(50);
vempno emp.empno%TYPE;
BEGIN
vempno:=&empno;
vmesg:=Sal_Level(vempno);
DBMS_OUTPUT.PUT_LINE(vmesg);
END;
/
--SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no;
十二、有如下MyTable:
日期 日产
1 3.3333
2 4.2222
3 1.5555
4 9.8888
5 ………
要求用SQL语句生成如下查询
日期 日产 累计日产
1 3.3333 3.3333
2 4.2222 7.5555
3 1.5555 9.0000
4 9.8888 18.8888
5………
select id,quantity,(select sum(quantity)from mytable where id<=t.id) as acount from mytable t
十三、创建一个序列，第一次从5循环到10，以后再从0开始循环
create sequence test_seq
start with 5
increment by 1
maxvalue 10
minvalue 0
cycle
nocache
面试sql题
中软面试的一个sql题。
题目:
1、每个科目的最高分。
2、java成绩最高的姓名
3、java成绩第二高的姓名
第一个没什么好说的。
对第二题和第三题，取得成绩可能有多的。可以使用分析函数。DENSE_RANK
SELECT t.name,
t.kemu,
t.score,
DENSE_RANK() OVER (PARTITION BY t.kemu ORDER BY t.score) seq
FROM ke_chengji t;
一道SQL面试题
有两个表, table1, table2,
Table table1:
SELLER | NON_SELLER
----- -----
A B
A C
A D
B A
B C
B D
C A
C B
C D
D A
D B
D C
Table table2:
SELLER | COUPON | BAL
----- --------- ---------
A 9 100
B 9 200
C 9 300
D 9 400
A 9.5 100
B 9.5 20
A 10 80
要求用SELECT 语句列出如下结果:------如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和.......
且用的方法不要增加数据库负担,如用临时表等.
NON-SELLER| COUPON | SUM(BAL) ------- --------
A 9 900
B 9 800
C 9 700
D 9 600
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10 0
B 10 80
C 10 80
D 10 80
下面是我的方法，不知道哪位高手有更好的方法请出招。
select distinct(a.seller),b.coupon,
nvl((select sum(bal) from table2 where seller in
(select non_seller from table1 where seller=a.seller)
and coupon=b.coupon),0) as sumbal
from table1 a
left join table2 b on 1=1 order by b.coupon ;
题目:
表B
C1 C2
2005-01-01 1
2005-01-01 3
2005-01-02 5
要求的输出数据
C1 C2
2005-01-01 4
2005-01-02 5
合计 9
试用一个Sql语句完成。
该题目主要考的是分析函数函数over (partition by)的使用
--创建表B
create table b
(c1 varchar2(14),c2 number);
insert into b
values('2005-01-01',1) ;
insert into b
values('2005-01-01',3) ;
insert into b
values('2005-01-02',5) ;
commit;
select c1,sum(c2) over(partition by c1)
from b
union
select '合计:',sum(c2) over(partition by null)
from b;
C1 SUM(C2)OVER(PARTITIONBYC1)
-------------- --------------------------
2005-01-01 4
2005-01-02 5
合计: 9
一.SQL问答题
SELECT * FROM TABLE
和
SELECT * FROM TABLE
WHERE NAME LIKE '%%' AND ADDR LIKE '%%'
AND (1_ADDR LIKE '%%' OR 2_ADDR LIKE '%%'
OR 3_ADDR LIKE '%%' OR 4_ADDR LIKE '%%' )
的检索结果为何不同?
(1).like通配符一个个比较肯定影响效率，
(2).数据库中存在null的时候，如果字段中有null存在select * from table 可以显示所有的内容，但是like不会通配null，所以字段为null它显示不出来！！
二.select count(*) from table
和select count(1) from talbe的区别？？
只明白count(字段)时它是不检索null的！
但是在count中1和*现在还没有明确答案，求高手！！！~
