A. ORACLE数据库面试题
1.
update t
set logdate=to_date('2003-01-01','yyyy-mm-dd')
where logdate=to_date('2001-02-11','yyyy-mm-dd');
2.
select *
from t
where name in (select name from t group by name having coung(*)>1)
order by name;--没说清楚，到底是升序还是降序
3.
select ID，NAME，ADDRESS，PHONE，LOGDATE
from
(
select t.*,row_number() over(partition by name order by name) rn
from t
)
where rn = 1;
4.
update t
set (address,phone)=
(select address,phone from e where e.name=t.name);
5.
select *
from t
where rownum <=5
minus
select *
from t
where rownum <=2;
也没什么特别的地方，有些题目用oracle特有的函数去做会比较简单，像在第三题中用到的oracle的分析函数，以及在第一题中用到的oracle的to_char()函数。
这几个题目主要是看你能不能使用oracle的函数去处理
B. 急求sql Server 数据库SQL语句面试题
What are two methods of retrieving SQL?
What cursor type do you use to retrieve multiple recordsets?
What is the difference between a “where” clause and a “having” clause? - “Where” is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause.
What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)
What are the tradeoffs with having indexes? - 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
C. 数据库SQL查询语句面试题
5.1
select a.username,b.deptname from users a,dept b where a.dept_id=b.id;
5.2
update users set dept_id='9' where dept_id='2';
5.3
select a.deptname,b.count_id from dept a,(select dept_id,count(id) as count_id from users group by dept_id having count(id)>1) b where a.id=b.dept_id;
5.4
select a.deptname,b.count_man,c.count_woman from dept a,(select dept_id,count(sex) as count_man from users where sex='男' group by dept_id) b,(select dept_id,count(sex) as count_woman from users where sex='女' group by dept_id) c where a.id=b.dept_id and a.id=c.dept_id;
5.5
添加历史记录表
create table history(
id number(8), -- 记录编号
dept_id varchar2(5), -- 部门ID
user_id varchar2(5), -- 用户ID
change_date date -- 变动日期
);
D. 数据库常见笔试面试题
数据库常见笔试面试题
数据库常见笔试面试题有哪些?数据库常见笔试面试会考什么?下面是数据库常见面试题总结，为大家提供参考。
1、SQL的表连接方式有哪些?
SQL中连接按结果集分为：内连接，外连接，交叉连接
内连接：inner join on，两表都满足的组合。内连接分为等值连接，不等连接，自然连接。
等值连接：两表中相同的列都会出现在结果集中。
自然连接：两表中具体相同列表的列会合并为同一列出现在结果集中。
外连接：分为左(外)连接，右(外)连接，全连接
左(外)连接：A left (outer) join B,以A表为基础，A表的全部数据，B表有的组合，没有的为null。
右(外)连接：A right(outer) join B,以B表为基础，B表的全部数据，A表有的组合，没有的位null。
全连接：A full (outer) join 两表相同的组合在一起，A表有，B表没有的数据(显示为null)，同样B表有，A表没有的显示为null。
交叉连接：cross join,就是笛卡尔乘积。
2、三范式
1NF:表中的字段都是单一属性，不再可分。
2NF：在1NF的基础上，表中所有的非主属性都必须完全依赖于任意一组候选键，不能仅依赖于候选键中的某个属性。
3NF：在2NF的基础上，表中所有的属性都不依赖其他非主属性。
简单的说就是：1NF表示每个属性不可分割，2NF表示非主属性不存在对主键的部分依赖，3NF表示不存在非主属性对主键的依赖传递。
3、表的操作
表的创建：create table 表名 (列名1 类型 约束，列2 类型 约束…)
表的删除： 表名
表的更改(结构的更改，不是记录的更新)：alter table 表名 add|drop 列名|约束名
插入记录： into 表名…values…
更新记录：表名 set 列名=值 where 条件
删除记录： from 表名 where 条件
4、数据的完整性
数据完整性指的是存储在数据库中的数据的一致性和准确性。
完整性分类：
(1)实体完整性：主键值必须唯一且非空。(主键约束)
(2) 引用完整性(也叫参照完整性)：外键要么为空，要么引用主表中存在的记录。(外键约束)。
(3)用户自定义完整性：针对某一具体关系数据库中的约束条件。
5、SQL的查询优化
(1)从表连接的角度优化：尽量使用内连接，因为内连接是两表都满足的行的组合，而外连接是以其中一个表的全部为基准。
(2)尽量使用存储过程代替临时写SQL语句：因为存储过程是预先编译好的SQL语句的集合，这样可以减少编译时间。
(3)从索引的角度优化：对那些常用的查询字段简历索引，这样查询时值进行索引扫描，不读取数据块。
(4)还有一些常用的select优化技巧：
(5)A.只查询那些需要访问的字段，来代替select*
B、将过滤记录越多的where语句向前移：在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。
6、索引的作用，聚集索引与非聚集索引的区别
索引是一个数据库对象，使用索引，可以是数据库程序无须对整个数据进行扫描，就可以在其中找到目标数据，从而提高查找效率。索引的底层采用的是B树。
聚集索引：根据记录的key再表中排序数据行。
非聚集索引：独立于记录的结构，非聚集所以包含的`key，且每个键值项都有指向该简直的数据行的指针。
聚集索引与非聚集索引的区别：
(1)聚集索引的物理存储按索引排序，非聚集所以的物理存储不按索引排序。
(2) 聚集索引插入，更新数据的速度比非聚集索引慢，单查询速度更快。
(3) 聚集索引的叶级结点保存的是时间的数据项，而非聚集结点的叶级结点保存的是指向数据项的指针。
(4)一个表只能有一个聚集索引(因为只有一种排序方式)，但可以有多个非聚集索引。
7、存储过程与函数的区别
(1)函数有返回值，存储过程没有返回值。
(2) 因为存储过程没有返回值，所以不能将存储过程的执行结果赋值给变量;函数有返回值类型，调用函数时，可以将函数的执行结果赋值给变量。也就是说，函数可以在select语句中使用，而存储过程则不能。
E. SQL数据库面试题 急急急
a)select pname as '商品名',avg(qty) as 平均销售量 from s,p,m where m.city='上海' and s.mno=m.mno and p.pno=s.pno，select p.Pno,p.pname,sum(s.qty)
from s left join p on s.pno=p.pno left join m on p.Mno=m.Mno
where m.city='上海市'
group by p.Pno,p.pname,p.city,p.color
b)、先删除Sale表的外键PNO，再删除gds表。
c)联系：视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系
区别：1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。
F. 问几题数据库面试题++++++++++++++++++
14: C
select e.ename form emp e where e.empno not in (select m.mgr from emp m)
1:
1)
select A.sid, S.sname from
(select sid from sc where cid in (select cid from sc where sid = 123456)
group by sid having count(*) = (select count(*) from sc where sid = 123456)) as A inner join student as S on A.sid = S.sid
2)
select sc.cid, s.sname, sc.cid, c.cname, sc.scscore from sc, student as s, course as c
where s.sid = sc.sid and c.cid = sc.cid and sc.sid in
(
select sc.sid from sc,
(select cid, avg(scscore) as avg_score from sc group by cid) as a
where sc.cid = a.cid and sc.scscore < a.avg_score
group by sc.sid having count(*) > 2
)
1:
select 日期字段,sum(case when 胜负字段 = '胜' then 1 else 0 end) as [胜],sum(case when 胜负字段 = '负' then 1 else 0 end) as [负] from 比赛表
group by 日期字段
G. 数据库经典笔试题和面试题答案
如下这些有关数据库知识考查的经典笔试题，非常全面，对计算机专业毕业生参加笔试会很有帮助，建议大家收藏。
一、选择题
1. 下面叙述正确的是___c___。
A、算法的执行效率与数据的存储结构无关
B、算法的空间复杂度是指算法程序中指令(或语句)的条数
C、算法的有穷性是指算法必须能在执行有限个步骤之后终止
D、以上三种描述都不对
2. 以下数据结构中不属于线性数据结构的是___c___。
A、队列B、线性表C、二叉树D、栈
3. 在一棵二叉树上第5层的结点数最多是__b____。2的(5-1)次方
A、8 B、16 C、32 D、15
4. 下面描述中，符合结构化程序设计风格的是___a___。
A、使用顺序、选择和重复(循环)三种基本控制结构表示程序的控制逻辑
B、模块只有一个入口，可以有多个出口
C、注重提高程序的执行效率 D、不使用goto语句
5. 下面概念中，不属于面向对象方法的是___d___。
A、对象 B、继承 C、类 D、过程调用
6. 在结构化方法中，用数据流程图(DFD)作为描述工具的软件开发阶段是___b___。
A、可行性分析 B、需求分析 C、详细设计 D、程序编码
7. 在软件开发中，下面任务不属于设计阶段的是__d____。
A、数据结构设计 B、给出系统模块结构 C、定义模块算法 D、定义需求并建立系统模型
8. 数据库系统的核心是___b___。
A、数据模型 B、数据库管理系统 C、软件工具 D、数据库
9. 下列叙述中正确的是__c____。
A、数据库是一个独立的系统，不需要操作系统的支持
B、数据库设计是指设计数据库管理系统
C、数据库技术的根本目标是要解决数据共享的问题
D、数据库系统中，数据的物理结构必须与逻辑结构一致
10. 下列模式中，能够给出数据库物理存储结构与物理存取方法的是___a___。
A、内模式 B、外模式 C、概念模式 D、逻辑模式
11. Visual FoxPro数据库文件是___d___。
A、存放用户数据的文件 B、管理数据库对象的系统文件
C、存放用户数据和系统的文件 D、前三种说法都对
12. SQL语句中修改表结构的命令是___c___。
A、MODIFY TABLE B、MODIFY STRUCTURE
C、ALTER TABLE D、ALTER STRUCTURE
13. 如果要创建一个数据组分组报表，第一个分组表达式是"部门"，第二个分组表达式是"性别"，第三个分组表达式是"基本工资"，当前索引的索引表达式应当是__b____。
A、部门+性别+基本工资 B、部门+性别+STR(基本工资)
C、STR(基本工资)+性别+部门 D、性别+部门+STR(基本工资)
14. 把一个项目编译成一个应用程序时，下面的叙述正确的是___a___。
A、所有的项目文件将组合为一个单一的应用程序文件
B、所有项目的包含文件将组合为一个单一的应用程序文件
C、所有项目排除的文件将组合为一个单一的应用程序文件
D、由用户选定的项目文件将组合为一个单一的应用程序文件
15. 数据库DB、数据库系统DBS、数据库管理系统DBMS三者之间的关系是_a___。
A、DBS包括DB和DBMS B、DBMS包括DB和DBS
C、DB包括DBS和DBMS D、DBS就是DB，也就是DBMS
16. 在"选项"对话框的"文件位置"选项卡中可以设置___b___。
A、表单的默认大小 B、默认目录
C、日期和时间的显示格式 D、程序代码的颜色
17. 要控制两个表中数据的完整性和一致性可以设置"参照完整性"，要求这两个表_a_。
A、是同一个数据库中的两个表 B、不同数据库中的两个表
C、两个自由表 D、一个是数据库表另一个是自由表
18. 定位第一条记录上的命令是___a___。
A、GO TOP B、GO BOTTOM C、GO 6 D、SKIP
19. 在关系模型中，实现"关系中不允许出现相同的元组"的约束是通过__b____。
A、候选键 B、主键 C、外键 D、超键
20. 设当前数据库有10条记录(记录未进行任何索引)，在下列三种情况下，当前记录号为1时;EOF()为真时;BOF()为真时，命令?RECN()的结果分别是___a___。
A、1,11,1 B、1,10,1 C、1,11,0 D、1,10,0
21. 下列表达式中结果不是日期型的是___c___。
A、CTOD("2000/10/01") B、{^99/10/01}+365
C、VAL("2000/10/01") D、DATE()
22. 只有满足联接条件的记录才包含在查询结果中，这种联接为___c___。
A、左联接 B、右联接 C、内部联接 D、完全联接
23. 索引字段值不唯一，应该选择的索引类型为___b___。
A、主索引 B、普通索引 C、候选索引 D、唯一索引
24. 执行SELECT 0选择工作区的结果是___b___。
A、选择了0号工作区 B、选择了空闲的最小号工作区
C、关闭选择的工作区 D、选择已打开的工作区
25. 从数据库中删除表的命令是___a___。
A、DROP TABLE B、ALTER TABLE C、DELETE TABLE D、USE
26. DELETE FROM S WHERE 年龄>60语句的功能是__b____。
A、从S表中彻底删除年龄大于60岁的记录
B、S表中年龄大于60岁的记录被加上删除标记
C、删除S表 D、删除S表的年龄列 1 2
H. 数据库面试题：
create database aaa;
create table users(id int NOT NULL AUTO_INCREMENT,
user_name char(10),money char(10),add_time datetime
PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
3.insert into users values(2,'a1','a11',current_date),(3,'a1','a11',current_date),(4,'a1','a11',current_date),(5,'a1','a11',current_date),(6,'a1','a11',current_date),(7,'a1','a11',current_date),(8,'a1','a11',current_date),(9,'a1','a11',current_date),(10,'a1','a11',current_date),(11,'a1','a11',current_date);
4.参考第二步
5.参考第三步
6.select a.username,a.money,b.email,b.user_phone,b.weigh from users a,user_info b
where a.id=b.id and a.id=XXX(你指定的)；
7.mysqlmp -uroot -p aaa > /tmp/aaa.sql （需要输入密码）
我用的是mysql上的写法
I. SQL查询面试题与答案
SQL查询面试题与答案
SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。下面是我搜集的SQL查询面试题与答案，欢迎大家阅读。
SQL查询面试题与答案一
1.一道SQL语句面试题，关于group by表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')
1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.胜,M.负 from (
select rq,胜=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,负=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001
2.请教一个面试中遇到的SQL语句的查询问题
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面试题：一个日期判断的sql语句?
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路)：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table
5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------
用户临时表:create table #xx(ID int, IDValues int)
系统临时表:create table ##xx(ID int, IDValues int)
区别:
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
6.sqlserver2000是一种大型数据库，他的`存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
------------------------------------------
它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
SQL Server 2000 数据库有三种类型的文件：
主要数据文件
主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
次要数据文件
次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
7.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
如使用存储过程也可以。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 (result)
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.华为一道面试题
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
SQL查询面试题与答案二
1、查询不同老师所教不同课程平均分从高到低显示
SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
2、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
SELECT DISTINCT top 3
SC.S# As 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
3、统计打印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
4、查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成绩 desc;
5、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
6、查询每门课程被选修的学生数
select c#,count(S#) from sc group by C#;
7、查询出只选修了一门课程的全部学生的学号和姓名
select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
9、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、删除学习“叶平”老师课的SC表记录;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩;
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001)，马克思(002)，OO&UML (003)，数据库(004)
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
;J. Oracle面试题(基础篇)
Oracle面试题(基础篇)
Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。以下是关于Oracle面试题(基础篇)，希望大家认真阅读!
1. Oracle跟SQL Server 2005的区别?
宏观上：
1). 最大的区别在于平台，oracle可以运行在不同的平台上，sql server只能运行在windows平台上，由于windows平台的稳定性和安全性影响了sql server的稳定性和安全性
2). oracle使用的脚本语言为PL-SQL，而sql server使用的脚本为T-SQL
微观上： 从数据类型,数据库的结构等等回答
2. 如何使用Oracle的游标?
1). oracle中的游标分为显示游标和隐式游标
2). 显示游标是用cursor...is命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理;隐式游标是在执行插入 (insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL/SQL自动定义的。
3). 显式游标的操作：打开游标、操作游标、关闭游标;PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它
3. Oracle中function和procere的区别?
1). 可以理解函数是存储过程的一种
2). 函数可以没有参数,但是一定需要一个返回值，存储过程可以没有参数,不需要返回值
3). 函数return返回值没有返回参数模式，存储过程通过out参数返回值, 如果需要返回多个参数则建议使用存储过程
4). 在sql数据操纵语句中只能调用函数而不能调用存储过程
4. Oracle的导入导出有几种方式，有何区别?
1). 使用oracle工具 exp/imp
2). 使用plsql相关工具
方法1. 导入/导出的是二进制的数据， 2.plsql导入/导出的是sql语句的文本文件
5. Oracle中有哪几种文件?
数据文件(一般后缀为.dbf或者.ora)，日志文件(后缀名.log)，控制文件(后缀名为.ctl)
6. 怎样优化Oracle数据库，有几种方式?
个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化， 物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化
物理优化的一些原则：
1). Oracle的运行环境(网络，硬件等)
2). 使用合适的优化器
3). 合理配置oracle实例参数
4). 建立合适的索引(减少IO)
5). 将索引数据和表数据分开在不同的表空间上(降低IO冲突)
6). 建立表分区，将数据分别存储在不同的分区上(以空间换取时间，减少IO)
逻辑上优化：
1). 可以对表进行逻辑分割，如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用
2). Sql语句使用占位符语句，并且开发时候必须按照规定编写sql语句(如全部大写，全部小写等)oracle解析语句后会放置到共享池中
如： select * from Emp where name=? 这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好
3). 数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能
4). 尽量不使用*号，如select * from Emp，因为要转化为具体的列名是要查数据字典，比较耗时
5). 选择有效的表名
对于多表连接查询，可能oracle的优化器并不会优化到这个程度， oracle 中多表查询是根据FROM字句从右到左的数据进行的，那么最好右边的表(也就是基础表)选择数据较少的表，这样排序更快速，如果有link表(多对多中间表)，那么将link表放最右边作为基础表，在默认情况下oracle会自动优化，但是如果配置了优化器的情况下，可能不会自动优化，所以平时最好能按照这个方式编写sql
6). Where字句 规则
Oracle 中Where字句时从右往左处理的，表之间的连接写在其他条件之前，能过滤掉非常多的数据的条件，放在where的末尾， 另外!=符号比较的列将不使用索引，列经过了计算(如变大写等)不会使用索引(需要建立起函数)， is null、is not null等优化器不会使用索引
7). 使用Exits Not Exits 替代 In Not in
8). 合理使用事务，合理设置事务隔离性
数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数
7. Oracle中字符串用什么符号链接?
Oracle中使用 || 这个符号连接字符串 如 ‘abc’ || ‘d’
8. Oracle分区是怎样优化数据库的`?
Oracle的分区可以分为：列表分区、范围分区、散列分区、复合分区。
1). 增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍可以使用;
2). 减少关闭时间：如果系统故障只影响表的一部份分区，那么只有这部份分区需要修复，可能比整个大表修复花的时间更少;
3). 维护轻松：如果需要得建表，独产管理每个公区比管理单个大表要轻松得多;
4). 均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能;
5). 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快
6). 分区对用户透明，最终用户感觉不到分区的存在。
9. Oracle是怎样分页的?
Oracle中使用rownum来进行分页, 这个是效率最好的分页方法，hibernate也是使用rownum来进行oralce分页的
select * from
( select rownum r,a from tabName where rownum <= 20 )
where r > 10
10. Oralce怎样存储文件，能够存储哪些文件?
Oracle 能存储 clob、nclob、 blob、 bfile
Clob 可变长度的字符型数据，也就是其他数据库中提到的文本型数据类型
Nclob 可变字符类型的数据，不过其存储的是Unicode字符集的字符数据
Blob 可变长度的二进制数据
Bfile 数据库外面存储的可变二进制数据
11. Oracle中使用了索引的列，对该列进行where条件查询、分组、排序、使用聚集函数，哪些用到了索引?
均会使用索引， 值得注意的是复合索引(如在列A和列B上建立的索引)可能会有不同情况
12. 数据库怎样实现每隔30分钟备份一次?
通过操作系统的定时任务调用脚本导出数据库
13. Oracle中where条件查询和排序的性能比较?
Order by使用索引的条件极为严格，只有满足如下情况才可以使用索引，
1). order by中的列必须包含相同的索引并且索引顺序和排序顺序一致
2). 不能有null值的列
所以排序的性能往往并不高，所以建议尽量避免order by
14. 解释冷备份和热备份的不同点以及各自的优点?
冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法
热备份是在数据库运行的情况下，采用归档方式备份数据的方法
冷备的优缺点：
1).是非常快速的备份方法(只需拷贝文件)
2).容易归档(简单拷贝即可)
3).容易恢复到某个时间点上(只需将文件再拷贝回去)
4).能与归档方法相结合，作数据库“最新状态”的恢复。
5).低度维护，高度安全。
冷备份不足：
1).单独使用时，只能提供到“某一时间点上”的恢复。
2).在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
3).若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
4).不能按表或按用户恢复。
热备的优缺点
1).可在表空间或数据文件级备份，备份时间短。
2).备份时数据库仍可使用。
3).可达到秒级恢复(恢复到某一时间点上)。
4).可对几乎所有数据库实体作恢复。
5).恢复是快速的，在大多数情况下在数据库仍工作时恢复。
热备份的不足是：
1).不能出错，否则后果严重。
2).若热备份不成功，所得结果不可用于时间点的恢复。
3).因难于维护，所以要特别仔细小心，不允许“以失败而告终”。
15. 解释data block , extent 和 segment的区别?
data block 数据块，是oracle最小的逻辑单位，通常oracle从磁盘读写的就是块
extent 区，是由若干个相邻的block组成
segment段，是有一组区组成
tablespace表空间，数据库中数据逻辑存储的地方，一个tablespace可以包含多个数据文件
;