目录
Oracle复习
题型
选择题15题 每题2分，共30分
判断题10题 每题1分 共10分
填空题10个空 每空1分 共10分
简答题4题 每题5分 共20分
应用题3题 每题10分 共30分
复习大纲
1、ORACLE服务器的监听器配置文件叫什么？ tnsname.ora是干什么用的？
listener.ora
主要记录了服务别名和对应的信息
#你所要连接的时候输入得TNSNAME
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
#下面是这个TNSNAME对应的主机，端口，协议
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
#使用专用服务器模式去连接需要跟服务器的模式匹配，如果没有就根据服务器的模式自动调节
(SERVER = DEDICATED)
#对应service_name，SQLPLUS>;show parameter service_name; 进行查看
(SERVICE_NAME = orcl)
)
)
#下面这个类似
SALES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.219)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
2、ORACLE文本初始化参数文件和服务器初始化参数文件的正确理解
初始化参数文件是oracle实例运行所需要的参数配置文件，oracle实例启动是必须先从参数文件中读取数据。
初始化参数文件分为pfile跟spfile。
1、pfile也叫文本初始化参数文件。
oracle9i以前版本只有这种参数文件，内容是纯文本的可以直接进行编辑。
oracle在安装每一个库是都会建立一个pfile文件，默认名字是init<SID>.ora，SID为数据库实例名。
在windows环境下一般该文件是放在oracle_homedatabase目录下，UNIX的环境下一般是装在oracle_homedbs目录下。
例如在windows2000的环境下d:oracleora92databaseinit<SID>.ora,但是在92010的版本中由于采用的是spfile为默认的参数文件，
所以只能在oracle_baseadmin<SID>pfile目录先才能找到pfile文件的一个备份，
如：init.ora.4212004195359，可以直接用文本编辑，另外在oracle_homeadminsamplepfile可以找到一个pfile的参考备份文件，DBA可以利用它建立新的初始化文件。
因为修改初始化参数文件必须先关闭数据库，这样会使数据库性能下降，而且有时候中断是不能容忍的，而且DBA要在远程启动数据库就必须在客户端保存一个pfile文本的副本，
如果内容不一致的话就可能启动不了数据库，在8i以后引入了动态参数的设置但是pfile不能直接保存，
因此在数据库重新启动后原先修改的数据还会丢失，在92010以后的版本就引入了spfile这种新的参数文件。
2、spfile也叫服务器初始化参数文件，它是二进制的无法用文本进行修改。在9i中它的默认位置是oracle_homedatabase，默认文件名是spfile<SID>.ora。
服务器参数文件
服务器文件SPFILE(Server Parameter File)是二进制文件，用来记录Oracle数据库的基本参数信息(数据库名、控制文件所在路径、日志缓冲大小)。
数据库实例在启动之前，Oracle首先会读取SPFILE参数文件中设置的各种参数，并根据这些初始化参数来配置和启动实例。
数据库启动时oracle会自动在默认目录oracle_homedatabase下搜索初始化参数文件，搜索顺序为：
spfile<SID>.ora oracle数据库创建时自动建立的spfile文件
spflie.ora oracle缺省的spfile文件
init<SID>.ora oracle数据库创建时自动建立的pfile文件
init.ora oracle缺省的pfile文件
如果不希望时候默认的初始化参数文件，DBA可以在startup命令指定相应的初始化参数文件，如：
SQL>startup pfile=d:oracleadmin<SID>pfileinit<SID>.ora
pfile中还有两个特殊的关键字：ifile跟spfile
ifile是用来指定另外一个pfile文件的，例如在d:oraclepflie中的init.ora初始化参数文件中有这样的参数设置：
ifile="c:oraclepfileinit001.ora"
那么执行:startup pfile=d:oraclepflieinit.ora
就相当于执行:startup pfile=c:oraclepfileinit001.ora
spfile关键字也一样只不过是用来执行spfile文件的而已。
创建初始化参数文件
创建pfile文件
可以直接复制一个pfile文件，然后直接通过syartup pfile=这样来启动数据库，或者是用sysdba的身份登陆sql*plus执行以下命令：
SQL>create pfile='.:....init.ora'
from spfile='.:....spfile.ora'
或者是执行不带参数的：
SQL>create pfile from spfile
该命名将在oracle_homedatabase下创建一个pfile默认名字是init<SID>.ora
创建spfile文件
由于spfile是二进制文件所以不能直接复制粘贴的，可以用这个命令来通过pfile建立新的spfile
SQL>create spfile='.:....spfile.ora'
from pfile='.:....init.ora'
配置初始化参数文件
通过命令：
SQL>alter system set 参数名=参数值 spoce=spfile|memory|both
在oracle11g中的oracle_home的database目录下不存在pfile文件init<SID>.ora，因为oracle11g启动时默认加载的是spfile文件spfile<SID>.ora文件。
但是在oracle的D:appAdministratoradminorclygpfile目录下存在oracle university installer生成的pfile的一个模板文件init.ora.XXXX，
如果需要pfile文件的情况下，可以根据数据库的需要通过修改该模板文件生成对应的pfile然后使用startup命令重启数据库同时加载指定的修改过的pfile文件。
使用create命令无论是创建pfile还是创建spfile生成的文件都是放在oracle_home/database目录下
3、ORACLE系统管理员的名字（sys） conn / as sysdba
SYS模式
SYS是一个Oracle管理员用户或根用户；由于它具有全能的性质，你应尽量避免作为SYS注册到系统中工作。
SYSTEM模式
也是在数据库创建时安装的，是用于DBA任务的缺省帐户。
SYSTEM也对所有的数据库对象拥有完全的权限，而且许多第三方工具软件依赖于SYSTEM模式的存在及特权。
Oracle数据库的另一管理员用户SYSTEM的缺省口令是MANAGER，并且像SYS帐户的口令一样，在数据库创建后应该立即被更改
4、ORACLE服务器由哪两个部分组成 （实例+数据库文件）
Oracle数据库用于存储和检索信息，是数据的集合。包括逻辑结构和物理结构。
Oracle实例是指数据库服务器的内存及相关处理程序。
5、ORACLE数据库的文件构成以及各种文件的作用 （数据文件）*
（1）数据文件：数据库中的数据在物理上都保存在一些操作系统文件中，这些操作系统的文件就是数据文件，通常是后缀名为.dbf的文件。
（2）控制文件：每个Oracle数据库都有相应的控制文件，是一个二进制文件，它定义了数据库的状态。
（3）重做日志文件：用于记录数据库所做的全部变更（如增加、删除、修改），以便在系统发生故障时，用它对数据库进行恢复。
6、常用数据字典表要了解 （dba_users, dba_tables, user_tables,user_sequences,user_views, user_constraints）
Oracle数据字典主要分为五类：
dba_： 包含数据库实例的所有对象信息；
v$_： 当前实例的动态视图，包含系统管理和系统优化所使用的视图；
user_： 记录用户对象信息；
gv_： 分布式环境下所有实例的动态视图，包含系统管理和系统优化使用的视图；
all_： 记录用户的对象信息与被授权访问的对象信息。
| 数据字典名称 | 说明 |
|---|---|
| dba_tablespaces | 关于表空间的信息 |
| dba_ts_quotas | 所有用户表空间限额 |
| dba_free_space | 所有表空间中的自由分区 |
| dba_segments | 描述数据库中所有段的存储空间 |
| dba_extents | 数据库中所有分区的信息 |
| dba_tables | 数据库中所有数据表的描述 |
| dba_sequences | 所有用户序列信息 |
| dba_constraints | 所有用户表的约束信息 |
| dba_indexs | 数据表中所有索引的描述 |
| dba_users | 关于数据库中所有用户的信息 |
| user_tables | 关于当前用户的表信息 |
| user_sequences | 关于当前用户的序列 |
| user_views | 关于当前用户的视图 |
| user_constraints | 关于当前用户的约束 |
7、ORACLE常见的几个进程的作用（dbwr,lgwr,ckpt...）
| oracle进程名 | 作用 |
|---|---|
| pmon （Process Monitor） | 进程监视器；进程非正常中断后，做清理工作 |
| dbwr (Database Write) | 数据库书写器；该服务器进程在缓冲存储区中记录所有的变化和数据 |
| LGWR(Log Write) | 日志书写器；把重做日志缓冲存储区中的数据写入到重做日志文件中 |
| ckpt(Checkpoint) | 检查点；同步化数据库的文件，把日志文件写入到数据库中。 |
8、了解ORACLE分区表
定义：
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
优点：
a.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
b.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。
c.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
d.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点：
分区表相关，已经存在的表没有方法可以直接转化为分区表。不过oracle提供了在线重定义表的功能。
9、了解各种约束（主键约束，外键约束，唯一约束，检查约束，默认约束...）
| 约束 | 作用 |
|---|---|
| primary key（主键） | 该列不但不能重复而且不能为NULL |
| foreign key（外键） | 外键约束要定义在从表上，主要则必须具有主键约束或是unique约束 |
| unique（唯一） | 列，不重复 |
| check（检查性约束） | 限制列数据的有效范围，以最大限度的保证列数据的有效； |
| not null（非空） | 必须为列提供，数据不能为NULL。约束只能在列级定义，不能在表级定义 |
| 默认约束（default） | 没有显式提供数据插入时，以默认值填充； |
10、oracle 11g, 12c (g:网格运算，c:云计算）
g是“Grid”缩写，支持网格计算，即，多台结点服务器利用高速网络组成一个虚拟的高性能服务器，负载在整个网格中均衡
c是“Cloud”缩写，支持云计算，一种能够方便地，按需从网络访问共享的可配置资源的可配置计算资源或服务池模型，资源或服务，设计网络，服务器，存储，应用和IT服务。
11、oracle网络拓补结构（本地访问，外机访问）
12、了解oracle三种进程： 客户端进程（sqlplus,toad,plsqldev），服务器进程，后台进程(dbwr,lgwr,ckpt...) *
Oracle SQL Developer 免费，一般开发使用足矣，常用。
PL/SQL DEVELOPER 比较适合开发人员，界面简单易用，常用。
Toad 比较专业一些，适合DBA人员使用，功能强大，主要是数据分析(Toad for Data Analysts 2.7)，使用也是很容易上手的
DBWR
数据写入进程主要是将内存中的脏数据块回写到数据文件中。
脏数据块是指高速数据缓冲区中被修改过的数据块，这些数据块的内容与数据文件的数据块内容不一致。
CKPT
检查点进程可以看做一个事件，当检查点事件发生时，CKPT会要求DBWR将某些脏数据块回写到数据文件。
LGWR
日志写入进程用于将重做日志缓冲区中的数据写入重做日志文件。
Oracle首先将用户所做的修改日志信息写入日志文件，然后再将修改结果写入数据文件。
Oracle 服务器结构(体系结构)
Oracle服务器主要由instance、database、PGA、前台进程组成
instance
SGA
SGA使用操作系统内存资源
后台进程
后台进程需要CPU与内存资源
database
database包含data files、control files、redo log file，database存放在硬盘中。
PGA
PGA是个非共享的内存区域，用于管理用户进程的私有资源
前台进程
用户进程
服务器进程
逻辑存储结构
逻辑存储结构是Oracle数据存储结构的核心内容，对Oracle所有操作都会涉及逻辑存储结构。
逻辑结构是一种层次结构，主要有表空间(tablespace)、段(Sement)、区间(Extent)和数据块(Data Blocks)等概念组成。
它是面向用户的，当用户使用Oracle设计数据库时，其使用的就是逻辑存储结构。
物理存储结构
Oracle数据库物理存储结构由多种物理文件组成，
主要有数据文件、控制文件、日志文件(重做日志、归档日志、警告日志)、参数文件、口令文件、密码文件、警告文件、跟踪文件等。
13、了解登录的时候，使用sqlplus /nolog是为什么？
这样登录在安全性上来说是不好的
可以直接使用ps命令来直接看到这个用户的用户名和密码：
ps -ef | grep sqlplus;
14、了解oracle各种对象： 表，索引，约束，序列，视图，存储过程，函数
表
oracle中的表就是一张存储数据的表。
索引
如果要在表中查询指定记录，在没有索引时必须遍历整表，有索引时只要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID快速找到表中对应的记录。
-- 在goods表的goods_click_num列建立索引clicknum_idx。
create index clicknum_idx on goods(goods_click_num);
-- 调用查询语句
select * from goods where goods_click_num=100;
约束
主键约束（primary key）
非空约束（not null）
唯一约束（unique）
外键约束（foreign key）
检查性约束（check）
序列
序列(sequence) 是Oracle提供的用于生成一系列唯一数字的数据库对象。
它会自动生成顺序递增或者递减的序列号，以实现自动提供唯一的主键值。
序列可以在多用户并发环境中使用，并且可以为所有用户生成不重复的顺序数字，而不需要任何额外的I/O开销。
create sequence seq_test_id；
查看当前值与下一个值
select seq_test_id.currval, seq_test_id.nextval from dual;
insert into t_test(id, name) values (seq_test_id.nextval, 'le');
删除序列
drop sequence seq_test_id;
查询当前用户的序列信息
select * from user_sequences;
select seq_test_id.nextval from dual;
//当在当前会话中调用过一次nextval之后，currval在紧接着的调用中就可以生效了；currval是一个会话绑定的方法
select seq_test_id.currval from dual;
视图
oracle视图可以理解为数据库中一张虚拟的表，他是通过一张或者多张基表进行关联查询后组成一个虚拟的逻辑表。
查询视图，本质上是对表进行关联查询。
视图的本身是不包含任何数据，只是一个查询结果，当基表的数据发生变化时，视图里面的数据也会跟着发生变化。
create view goods as
select * from goods;
存储过程
create sequence seq_emp_no;
create procedure sp_new_emp is
begin
insert into tbl_employee values(seq_emp_no.nextval,'mary',23);
end;
/
create or replace procedure sp_add_emp（emp_name in varchar,emp_age in int） is
begin
if emp_age>=18 and emp_age<=60 then
insert into tbl_employee values(seq_emp_no.nextval,'mary',23);
dbms_output.put_line('员工'||emp_name||'信息构建完毕！');
else
dbms_output.put_line('员工'||emp_name||'不符合录取条件，被拒绝了！');
end if;
end sp_add_emp;
exec sp_add_emp('emp',23);
函数
create or replace function getScopedSum(begin_scope in number, end_scope in number) return number is
v_sum number := 0;
begin
for v_i in begin_scope .. end_scope loop
v_sum := v_sum + v_i;
end loop;
return(v_sum);
end getScopedSum;
select getscopedsum(1,100) from dual;
15、理解DBWR和LGWR这两个进程的作用
DBWR
数据写入进程主要是将内存中的脏数据块回写到数据文件中。
脏数据块是指高速数据缓冲区中被修改过的数据块，这些数据块的内容与数据文件的数据块内容不一致。
LGWR
日志写入进程用于将重做日志缓冲区中的数据写入重做日志文件。
Oracle首先将用户所做的修改日志信息写入日志文件，然后再将修改结果写入数据文件。
16、理解ORACLE的表组织方式。
1个表空间可以建立在1个文件上，也可以建立在多个文件上。
17、懂的表空间概念，能够创建表空间
Oracle使用表空间将相关的逻辑结构组合在一起，表空间是数据库最大逻辑划分区域，通常用来存放数据表、索引、回滚段等数据对象。
任何数据对象在创建时都必须指定存储在某个表空间中。
表空间与数据文件相对应，一个表空间由一个或多个数据文件组成，一个数据文件只属于一个表空间。
-- 创建数据表空间
create tablespace mytbs datafile '/u01/app/oracle/oradata/orcl/data_tmall.dbf' size 100m;
18、懂的如何创建用户，并在构建用户的过程中给这个用户指定数据表空间和临时表空间，知道如何授权给这个用户。
-- 创建数据表空间
create tablespace mytbs datafile '/u01/app/oracle/oradata/orcl/data_tmall.dbf' size 100m;
-- 创建临时表空间
create temporary tablespace mytemp tempfile '/u01/app/oracle/oradata/orcl/my_temp.dbf' size 100m;
-- 创建一个用户，并指定专有的永久表空间和临时表空间
create user le identified by 123
default tablespace mytbs
temporary tablespace mytemp
-- 授权给用户连接数据库和使用资源权限
grant connect,resource,dba to le;
19、ORACLE数据库的启动过程 （1.启动实例 2.挂接数据库 3、打开数据库）
1 NOMOUNT：启动数据库实例， 此时读取参数文件，但是不加载数据库；
2 MOUNT：启动数据库实例，加载数据库，但是数据库处于关闭状态；
3 OPEN: 启动数据库实例，加载并打开数据库；
4 FORCE: 终止实例并重启数据库，这种模式在数据库关闭或者启动遇到问题时使用，这种方式不到万不得已时不要使用，会有数据丢失；
20、用来监控ORACLE进程的进程是什么？ （SMON, PMON）
系统监控进程（SMON）
数据库系统启动时执行恢复工作的强制性进程
实例启动时如有需要，系统监控进程（system monitor process，SMON）将负责进行恢复（recovery）工作。
此外，SMON 还负责清除系统中不再使用的临时段（temporary segment），以及为数据字典管理的表空间（dictionary managed tablespace）合并相邻的可用数据扩展（extent）。
进程监控进程（PMON）
用于监控其他进程的状态，当有进程启动失败时，PMON会清除失败的用户进程，释放用户进程所用的资源。
当一个用户进程（user process）失败后，进程监控进程（process monitor，PMON）将对其进行恢复。
PMON 进程负责清理数据缓冲区（database buffer cache）并释放用户进程使用的资源。
21、理解ORACLE段的类型
段segment
数据段是与数据库对象相对应，一般一个数据库对象对应一个数据段。
多个extent是对应一个数据段，每个数据段实际上就是数据库一个对象的代表。
22、理解常用的ORACLE启动命令
开启数据库
1、startup
2、startup nomount
3、startup mount
4、startup open
关闭数据库
shutdown immediate
23、所有的SQL CRUD指令，组合查询，子查询，模糊查询，聚合函数，分组以及分组基础上的行过滤（having）
CRUD指令
crud是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写
INSERT INTO 语句可以有两种编写形式。
第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
INSERT INTO table_name VALUES (value1,value2,value3,...);
第二种形式需要指定列名及被插入的值：
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
SELECT DISTINCT 语法 去重
SELECT DISTINCT column_name,column_name
FROM table_name;
UPDATE 语法
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
请注意 UPDATE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！
DELETE 语法
DELETE FROM table_name
WHERE some_column=some_value;
请注意 DELETE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！
组合查询
执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并或复合查询
有两种情况需要使用组合查询：
在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据
使用UNION
可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。
假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品(不考虑价格)。
SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products WHERE vend_id IN (1001,1002);
UNION必须由两条或以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。
列数据类型必须兼容: 类型不必完全相同，但必须是DBMS可以隐含地转换的类型(例如，不同的数值类型或不同的日期类型)。
对组合查询结果排序
SELECT语句输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY 子句，它必须出现在最后一条SELECT语句之后。
SELECT vend_id, prod_id, prod_price
FROM products WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;
子查询
子查询就是嵌套在主查询中的查询
子查询运算符
分类：
–ALL运算符
和子查询的结果逐一比较，必须全部满足时表达式的值才为真。
–ANY运算符
和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。
–EXISTS/NOT EXISTS运算符
EXISTS判断子查询是否存在数据，如果存在则表达式为真，反之为假。NOT EXISTS相反。
在子查询或相关查询中，要求出某个列的最大值，通常都是用ALL来比较，大意为比其他行都要大的值即为最大值。
SELECT * FROM t_student
WHERE student_subject='C语言'
AND student_score>=ALL
(
SELECT student_score
FROM t_student
WHERE student_subject='C语言'
) ;
模糊查询
LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
通配符
在 SQL 中，通配符与 SQL LIKE 操作符一起使用。
SQL 通配符用于搜索表中的数据。
通配符 描述
% 匹配任意0个或多个字符
_ 匹配任意单个字符
[charlist] 匹配字符列中的任何单一字符
[^charlist]或[!charlist] 匹配不在字符列中的任何单一字符
-- 将会把name为“张三”，“三脚猫”，“唐三藏”找出
Select * FROM user Where name LIKE '%三%';
-- 只找出“唐三藏”，只找出name长度为3，第2位为'三'的数据
Select * FROM user Where name LIKE '_三_';
-- 将找出“张三”、“李三”、“王三”
Select * FROM user Where name LIKE '[张李王]三';
-- 将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等
Select * FROM user Where name LIKE '[^张李王]三';
聚合函数
常用聚合函数
求个数：count
求总和：sum
求最大值：max
求最小值：min
求平均值：avg
分组
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;
分组基础上的行过滤
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id
having SUM(access_log.count)>10
24、懂的删除用户以及其所有创建的对象
-- 级联删除
drop user tester cascade;
25、ORACLE分页要能熟练写出来（分页公式）
select *
from
(
select rownum rn, a.*
from
(
select * from tbl_student order by stu_mark
) a
where rownum<=6
)
where rn>=4
//ORACLE分页公式
select * from
(
select rownum rn, a.* from
(SQL CLAUSE) a
where rownum<=:endScope
)
where rn>=:beginScope
26、以前讲过的所有SQL题 ****
27、理解ORACLE SGA，知道SGA中有哪些部件。
SGA是所有用户进程共享的一块内存区域，
主要由
高速缓冲区(Database buffer cache)、
共享池(Shared Pool)、
重做日志缓存区(Redo log buffer cache)、
Java池(Java Pool)、
大池(Large Pool)、
流池(Streams Pool)等组成。
SGA随着instance启动而加载到内存中，instance关闭时，SGA也会跟着消失。
1、shared pool（共享池）
a、library cache (SQL的执行计划缓存在这里)
b、data dictionary cache (dd cache的访问是非常频繁的，不做缓存将对性能造成巨大影响)
2、database buffer cache （数据高速缓冲区）
最重要的，最大的真正的数据信息缓存。
数据文件中的数据和即将保存到数据文件中的数据全部缓存在这里。
3、redo log buffer（重做日志缓冲区）
记录所有对数据库中的数据块的改变
4、Large Pool （大型池）
最主要用途就是供RMAN备份和共享连接模式。
5、java pool（Java池）
oracle支持JAVA语言进行编程和书写存储过程，这个POOL主要是对JAVA对象进行缓存。
6、Streams Pool（流池）
流池用于在数据库与数据库之间进行信息共享。
28、理解dba_users、dba_tables和user_tables的区别
dba_users：系统里所有用户的信息，需要DBA权限才能查询
dba_tables : 系统里所有的表的信息，需要DBA权限才能查询
user_tables: 当前用户名下的表的信息
29、知道ORACLE的开发公司是谁
甲骨文公司（Oracle）
30、知道索引的作用是什么以及如何构建索引
加快查找速度
如果要在表中查询指定记录，在没有索引时必须遍历整表，有索引时只要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID快速找到表中对应的记录。
索引建立之后，是由oracle自动决定是否使用索引的，建立完索引，直接使用查询语句即可；
-- 在goods表的goods_click_num列建立索引clicknum_idx。
create index clicknum_idx on goods(goods_click_num);
-- 调用查询语句
select * from goods where goods_click_num=100;
31、能够描述ORACLE存储过程的优缺点
不用存储过程，存在的问题：
1、需要反复构建连接和释放连接，效率低
2、网络延迟大，网络负载大
3、安全性不够，数据库设计细节被暴露
4、用人成本高，管理成本高
5、如果SQL写的不好，很可能命中率很低，导致编译开销大
优点：
1.存储过程可以使得程序执行效率更高、安全性更好。
因为过程建立之后 已经编译并且储存到数据库，直接写sql就需要先分析再执行因此过程效率更高，但是直接写sql语句会带来安全性问题，如：sql注入
2.建立过程不会很耗系统资源，因为过程只是在调用才执行。
3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。
4.使用存储过程使您能够增强对执行计划的重复使用。
由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。
RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。
5.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
6.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。
7.增强安全性：
a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；
b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；
c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。
缺点就是：
1、不可移植，如果更换数据库，必须重新写过。SQL有标准，而存储过程没有。
2、大量的利用过程，会对服务器压力比较大。
附录
SQL题目一
表结构：
1、表名：g_cardapply
字段(字段名/类型/长度)：
applyno varchar8;//申请单号（关键字）
applydate date; //申请日期
state varchar2;//申请状态
2、表名：g_cardapplydetail
字段(字段名/类型/长度)：
applyno varchar8;//申请单号（关键字）
name varchar30;//申请人姓名
idcard varchar18;//申请人身份证号
state varchar2;//申请状态
其中，两个表的关联字段为申请单号。
-- 创建工卡表
create table g_cardapply(
apply_no varchar2(8),
apply_date date not null,
state varchar2(2) not null,
constraint PK_APPLY_NO PRIMARY KEY (apply_no)
);
-- 创建工卡申请明细表，apply_no即是主键又是外键，我们称为主外键重合，是一种经典的一对一设计方案
create table g_cardapplydetail(
apply_no varchar2(8) ,
apply_name varchar2(30) not null,
idcard char(18) not null,
state varchar2(2) not null,
constraint PK_APPLY_DETAIL_NO PRIMARY KEY (apply_no),
constraint FK_CARDAPPLY_DETAIL foreign key (apply_no) references g_cardapply(apply_no)
);
-- oracle字符串转日期，必须使用to_date函数，然后做好日期格式的适配
insert into g_cardapply values('00000010',to_date('2011-11-12','yyyy-mm-dd'),'01');
insert into g_cardapply values('00000011',to_date('2011-11-13','yyyy-mm-dd'),'01');
insert into g_cardapply values('00000012',to_date( '2011-11-14','yyyy-mm-dd'),'02');
insert into g_cardapply values('00000013',to_date('2011-11-15','yyyy-mm-dd'),'03');
insert into g_cardapply values('00000014',to_date('2011-11-16','yyyy-mm-dd'),'03');
insert into g_cardapplydetail values('00000010','mary', '440401430103082','01');
insert into g_cardapplydetail values('00000011','david ', '440401430103083','01');
insert into g_cardapplydetail values('00000012','mary', '440401430103082','02');
insert into g_cardapplydetail values('00000013','mike ', '440401430103084','03');
insert into g_cardapplydetail values('00000014','mary', '440401430103082','03');
commit;
题目：
1、查询身份证号码为440401430103082的申请日期
-- 内关联
select a.apply_date
from g_cardapply a inner join g_cardapplydetail b
on a.apply_no=b.apply_no and b.idcard='440401430103082';
select a.apply_date
from g_cardapply a inner join g_cardapplydetail b
on a.apply_no=b.apply_no
where b.idcard='440401430103082';
2、查询同一个身份证号码有两条以上记录的身份证号码及记录个数
select idcard,count(*)
from g_cardapplydetail
group by idcard
having count(*)>=2;
3、将身份证号码为440401430103082的记录在两个表中的申请状态均改为07
update g_cardapplydetail
set state='07'
where idcard='440401430103082';
-- 子查询
update g_cardapply
set state='07'
where apply_no in (
select apply_no
from g_cardapplydetail
where idcard='440401430103082'
);
commit;
4、删除cardapplydetail表中所有姓李的记录
-- 模糊查询
delete from g_cardapplydetail
where apply_name like '李%';
SQL题目二
有一个工厂，非常繁忙，同时在进行多个订单的生产任务。
每个订单都有自己的订单编号(WORKORDER_ID),每个订单要生产的物品要拆分成多个工序，这些工序并行进行，每个工序都有自己的编号STEP_NBR。
测试数据如下:
create table projects(
workorder_id varchar2(10) not null,
step_nbr int not null,
step_status char(1) not null,
constraint PK_PROJECTS PRIMARY KEY (workorder_id,step_nbr)
);
insert into projects values('ww023',0,'C');
insert into projects values('ww023',1,'W');
insert into projects values('ww023',2,'W');
insert into projects values('ww024',0,'W');
insert into projects values('ww024',1,'W');
insert into projects values('ww025',0,'C');
insert into projects values('ww025',1,'C');
C-完成 W-等待
请编写SQL语句，找出STEP_NBR=0,其 STEP STATUS='C',同时本订单其它工序STEP_STATUS均为W的订单，比如对以上数据的分析结果就是:
WORKORDER ID
ww023
要求:至少实现2种写法(多写更好)，语句越短越好。
-- 一解：
-- 条件1:
where step_nbr=0 and step_status='C'
-- 条件2:
'W'= ALL
(
select step_status from projects where step_nbr> = 1
)
select workorder_id
from projects p
where p.step_nbr=0 and p.step_status='C'
and 'W'= ALL
(
select step_status
from projects
where step_nbr>=1 and workorder_id=p.workorder_id
);
-- 二解：
select workorder_id
from projects p
where step_status='C'
group by workorder_id
having sum(step_nbr)=0;
SQL题目三
Northwind商贸公司，业务日益发展，公司OA系统正不断推出新版本以紧跟公司的发展。
在OA系统中，有一员工角色表，情况如下：
create table roles(
emp_name varchar2(20) not null,
emp_role char(1) not null,
constraint pk_roles primary key(emp_name,emp_role)
);
insert into roles values('陈城','W');
insert into roles values('刘海','D');
insert into roles values('刘海','O');
insert into roles values('田亮','O');
insert into roles values('王晓刚','D');
insert into roles values('张玲','S');
insert into roles values('张天明','D');
insert into roles values('张天明','O');
数据：
EMP_NAME EMP_ROLE
陈城 W
刘海 D
刘海 O
田亮 O
王晓刚 D
张玲 S
张天明 D
张天明 O
其中： W – 搬运工人 D – 主任 O – 高级职员 S – 秘书
OA开发组的SQL程序员张明得到了上级的一个任务：
领导要求得到的高级职员信息表如下：
EMP_NAME COMBINE_ROLE
刘海 B
田亮 O
王晓刚 D
张天明 B
题目：
1）只列出主任和高级职员的信息
2）如果即是高级职员又是主任，用B表示其角色，其它信息不用再显示（只一条记录）。
你能不能用单条SQL语句帮助张明实现这个查询？
-- 一解：
select emp_name,
case when count(*)=1
then max(emp_role)
else 'B'
end
as emp_role
from roles
where emp_role in ('D','O')
group by emp_name;
-- 二解：
select emp_name, substr('DOB',sum(instr('DO',emp_role)),l) combine_role
from roles
where emp_role in ('D','O')
group by emp_name;
-- 三解：
select emp_name,'B' combine_role
from roles
where emp_role in ('D','O')
group by emp_name
having count(*)=2
union
select emp_name,max(emp_role) combine_role
from roles
where emp_role in ('D','O')
group by emp_name
having count(*)=1 ;
SQL题目四
最近，经过你的努力，你得到了一份工作，成为了百货公司的一位经理。
到位后，你发现你的销售数据库中有两张表，一个是商店促销时间的日历，另一个是在促销期间的销售额列表。
你需要编写一个查询，告诉我们在每次促销中哪位职员的销售额最高，这样可以给那个职员发绩效奖金。
-- 商店促销时间的日历
create table promotions (
promo_name varchar2(50) not null primary key, -- 促销活动名称
start_date date not null, -- 开始时间
end_date date not null, -- 终止时间
check(start_date<=end_date)
);
-- 促销期间的销售额表 （注意：该表只是保存促销期间的销售额）
create table sales
(
ticket_nbr int not null primary key, --销售票据编号 （自增）
clerk_name varchar2(20) not null, --销售员姓名
sale_date date not null, --销售日期
sale_amount number(9,2) not null --销售金额
);
insert into promotions values('spring sales',to_date('2009-2-1','yyyy/mm/dd'),to_date('2009-2-15','yyyy/mm/dd'));
insert into promotions values('worker sale',to_date('2009-5-1','yyyy/mm/dd'),to_date('2009-5-4','yyyy/mm/dd'));
insert into promotions values('children sale',to_date('2009-6-1','yyyy/mm/dd'),to_date('2009-6-1','yyyy/mm/dd'));
insert into promotions values('national day sale',to_date('2009-10-1','yyyy/mm/dd'),to_date('2009-10-7','yyyy/mm/dd'));
create sequence seq_nbr;
insert into sales values(seq_nbr.nextval,'david',to_date('2009-2-1','yyyy/mm/dd'),30);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-2-1','yyyy/mm/dd'),73);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-2-1','yyyy/mm/dd'),110);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-2-2','yyyy/mm/dd'),190);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-2-2','yyyy/mm/dd'),30);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-2-2','yyyy/mm/dd'),92);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-2-3','yyyy/mm/dd'),130);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-2-3','yyyy/mm/dd'),90);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-2-3','yyyy/mm/dd'),110);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-2-4','yyyy/mm/dd'),70);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-2-4','yyyy/mm/dd'),9);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-2-5','yyyy/mm/dd'),88);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-2-5','yyyy/mm/dd'),70);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-2-13','yyyy/mm/dd'),50);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-2-13','yyyy/mm/dd'),170);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-2-14','yyyy/mm/dd'),270);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-2-15','yyyy/mm/dd'),67.5);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-5-1','yyyy/mm/dd'),280.5);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-5-1','yyyy/mm/dd'),190);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-5-1','yyyy/mm/dd'),113);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-5-2','yyyy/mm/dd'),88);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-5-2','yyyy/mm/dd'),35.5);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-5-2','yyyy/mm/dd'),125);
insert into sales values(seq_nbr.nextval,'david', to_date('2009-5-3','yyyy/mm/dd'),92);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-5-3','yyyy/mm/dd'),93);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-5-3','yyyy/mm/dd'),167);
insert into sales values(seq_nbr.nextval,'david', to_date('2009-5-4','yyyy/mm/dd'),123.5);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-5-4','yyyy/mm/dd'),200);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-5-4','yyyy/mm/dd'),2);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-6-1','yyyy/mm/dd'),190);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-6-1','yyyy/mm/dd'),110.5);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-6-1','yyyy/mm/dd'),213);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-10-1','yyyy/mm/dd'),1123);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-10-1','yyyy/mm/dd'),780);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-10-1','yyyy/mm/dd'),310);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-10-2','yyyy/mm/dd'),139);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-10-2','yyyy/mm/dd'),1110.5);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-10-2','yyyy/mm/dd'),998);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-10-3','yyyy/mm/dd'),120);
insert into sales values(seq_nbr.nextval,'mary',to_date('2009-10-4','yyyy/mm/dd'),10);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-10-4','yyyy/mm/dd'),234);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-10-5','yyyy/mm/dd'),110.5);
insert into sales values(seq_nbr.nextval,'tom',to_date('2009-10-6','yyyy/mm/dd'),23);
insert into sales values(seq_nbr.nextval,'david',to_date('2009-10-7','yyyy/mm/dd'),10.5);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-2-1','yyyy/mm/dd'),30);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-2-1','yyyy/mm/dd'),73);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-2-1','yyyy/mm/dd'),110);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-2-2','yyyy/mm/dd'),190);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-2-2','yyyy/mm/dd'),30);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-2-2','yyyy/mm/dd'),92);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-2-3','yyyy/mm/dd'),130);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-2-3','yyyy/mm/dd'),90);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-2-3','yyyy/mm/dd'),110);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-2-4','yyyy/mm/dd'),70);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-2-4','yyyy/mm/dd'),9);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-2-5','yyyy/mm/dd'),88);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-2-5','yyyy/mm/dd'),70);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-2-13','yyyy/mm/dd'),50);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-2-13','yyyy/mm/dd'),170);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-2-14','yyyy/mm/dd'),270);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-2-15','yyyy/mm/dd'),67.5);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-5-1','yyyy/mm/dd'),280.5);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-5-1','yyyy/mm/dd'),190);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-5-1','yyyy/mm/dd'),113);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-5-2','yyyy/mm/dd'),88);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-5-2','yyyy/mm/dd'),35.5);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-5-2','yyyy/mm/dd'),125);
insert into sales values(seq_nbr.nextval,'王海', to_date('2009-5-3','yyyy/mm/dd'),92);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-5-3','yyyy/mm/dd'),93);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-5-3','yyyy/mm/dd'),167);
insert into sales values(seq_nbr.nextval,'王海', to_date('2009-5-4','yyyy/mm/dd'),123.5);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-5-4','yyyy/mm/dd'),200);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-5-4','yyyy/mm/dd'),2);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-6-1','yyyy/mm/dd'),190);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-6-1','yyyy/mm/dd'),110.5);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-6-1','yyyy/mm/dd'),213);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-10-1','yyyy/mm/dd'),1123);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-10-1','yyyy/mm/dd'),780);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-10-1','yyyy/mm/dd'),310);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-10-2','yyyy/mm/dd'),139);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-10-2','yyyy/mm/dd'),1110.5);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-10-2','yyyy/mm/dd'),998);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-10-3','yyyy/mm/dd'),120);
insert into sales values(seq_nbr.nextval,'高春梅',to_date('2009-10-4','yyyy/mm/dd'),10);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-10-4','yyyy/mm/dd'),234);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-10-5','yyyy/mm/dd'),110.5);
insert into sales values(seq_nbr.nextval,'刘万理',to_date('2009-10-6','yyyy/mm/dd'),23);
insert into sales values(seq_nbr.nextval,'王海',to_date('2009-10-7','yyyy/mm/dd'),10.5);
找出在各次促销活动中，销售量最高的销售员。
请编制一条SQL来完成这个查询。(尽量考虑多种写法)
-- 一解：
-- 外层搜索 每个销售员的每次活动销售总额
-- 内层搜索 其它销售员的每次活动销售总额
-- 允许同时出现两个销售总额相同的销售员
select p.promo_name,s.clerk_name,sum(s.sale_amount) top_sale
from promotions p inner join sales s
on s.sale_date between p.start_date and p.end_date
group by p.promo_name,s.clerk_name,p.start_date,p.end_date
having sum(s.sale_amount) >= all(
select sum(s2.sale_amount)
from sales s2
where s.clerk_name <> s2.clerk_name and s2.sale_date between p.start_date and p.end_date
group by s2.clerk_name
);
-- 二解：
SELECT s1.clerk_name,p.promo_name,p.start_date,p.end_date,SUM(s1.sale_amount)
FROM sales s1
INNER JOIN promotions p
ON s1.sale_date BETWEEN p.start_date AND p.end_date
group by s1.clerk_name,p.promo_name,p.start_date,p.end_date
HAVING SUM(s1.sale_amount)>= ALL
(
SELECT SUM(s2.sale_amount)
FROM sales s2
WHERE s2.sale_date BETWEEN p.start_date AND p.end_date
GROUP BY s2.clerk_name
);
SQL题目五
ABC在线销售公司业务系统
表结构：
1、表名：t_category (商品类别表)
字段(字段名/类型/长度)：
类别编号 category_id INT
类别名称 category_name VARCHAR2(30)
2、表名：t_goods (商品表)
字段(字段名/类型/长度)：
商品编号 goods_no CHAR(3)
商品名称 goods_name VARCHAR2(30)
商品价格 goods_price number(7,2)
所属类别 goods_category INT
点击次数 goods_click_num INT
3、表名: t_saleinfo (销售信息表)
字段(字段名/类型/长度)：
销售流水号 sid INT
商品编号 goods_no CHAR(3)
销售日期 sale_date date
销售数量 quantity INT
销售金额 amount number(10,2)
create table t_category(
category_id int primary key,
category_name varchar2(30)
);
create table t_goods(
goods_no char(3) primary key,
goods_name varchar2(30) not null,
goods_price number(7,2) not null,
goods_category int not null,
goods_click_num int default 0,
constraint FK_GOODS_CATEGORY FOREIGN KEY (goods_category) references t_category(category_id)
);
create table t_saleinfo(
sid int primary key,
goods_no char(3) not null,
sale_date date not null,
quantity int not null,
amount number(10,2) not null,
constraint FK_SALEINFO_GOODS FOREIGN KEY (goods_no) references t_goods(goods_no)
);
-- 增加类别数据
insert into t_category values(1,'酒类');
insert into t_category values(2,'服装');
insert into t_category values(3,'书籍');
-- 商品数据
insert into t_goods values('G01','贵州茅台',550.56,1,128);
insert into t_goods values('G02','福建老酒',5.43,1,24);
insert into t_goods values('G03','泸州老窖',90.56,1,67);
insert into t_goods values('G04','剑南春',80.56,1,88);
insert into t_goods values('G05','七匹狼夹克',350.56,2,348);
insert into t_goods values('G06','七匹狼衬衫',105.43,2,908);
insert into t_goods values('G07','七匹狼男长裤',130.50,2,167);
insert into t_goods values('G08','七匹狼领带',280.00,2,388);
insert into t_goods values('G09','J2EE开发',50.50,3,236);
insert into t_goods values('G10','STRUTS应用',24.50,3,654);
insert into t_goods values('G11','ORACLE 11G',100.50,3,145);
insert into t_goods values('G12','dotnet技术',80.00,3,988);
-- 销售数据
insert into t_saleinfo values(1,'G01',to_date('2008-1-1','yyyy-MM-dd'),50,50*550.56);
insert into t_saleinfo values(2,'G01',to_date('2008-1-2','yyyy-MM-dd'),25,25*550.56);
insert into t_saleinfo values(3,'G01',to_date('2008-1-3','yyyy-MM-dd'),31,31*550.56);
insert into t_saleinfo values(4,'G01',to_date('2008-1-4','yyyy-MM-dd'),43,43*550.56);
insert into t_saleinfo values(5,'G01',to_date('2008-1-5','yyyy-MM-dd'),55,55*550.56);
insert into t_saleinfo values(6,'G01',to_date('2008-1-6','yyyy-MM-dd'),102,102*550.56);
insert into t_saleinfo values(7,'G11',to_date('2008-1-6','yyyy-MM-dd'),82,82*100.5);
insert into t_saleinfo values(8,'G11',to_date('2008-1-7','yyyy-MM-dd'),202,202*100.5);
题目：
1、查询酒类商品的总点击量
2、查询各个类别所属商品的总点击量,并按降序排列
3、查询所有类别中最热门的品种(点击量最高),并按点击量降顺序排列
4、查询茅台的销售情况,按日期升序排列
格式如下:
商品编号 商品名称 销售日期 销售数量 销售金额 累计数量 累计金额
1 茅台 2011-12-1 10 7000 10 7000
1 茅台 2011-12-2 15 10500 25 17500
-- 第一个问题（查询酒类商品的总点击量）
select '酒类' category_name,sum(goods_click_num) total_click from t_goods
where goods_category in
(
select category_id from t_category where category_name='酒类'
);
-- 第二个问题（查询各个类别所属商品的总点击量,并按降序排列）
select a.goods_category,b.category_name,sum(a.goods_click_num) total_click
from t_goods a inner join t_category b on a.goods_category = b.category_id
group by goods_category,category_name
order by sum(a.goods_click_num) desc;
-- 第三个问题（查询所有类别中最热门的品种(点击量最高),并按点击量降顺序排列 ）
select c.category_id,c.category_name,b.goods_no,b.goods_name,a.max_click from (
select goods_category, max(goods_click_num) max_click
from t_goods
group by goods_category
)a,t_goods b,t_category c
where a.goods_category=b.goods_category and a.max_click=b.goods_click_num
and c.category_id=a.goods_category
order by a.max_click desc;
-- 第四个问题思路（查询茅台的销售情况,按日期升序排列）
select d.goods_no,d.goods_name,e.category_name,c.*
from (
select max(a.goods_no) goods_no,a.sale_date,a.quantity day_quantity,max(a.amount) day_amount,sum(b.quantity) total_quantity,sum(b.amount) total_amount
from t_saleinfo a inner join t_saleinfo b
on a.goods_no=b.goods_no and a.goods_no= (
select goods_no from t_goods where goods_name='贵州茅台'
)
and a.sale_date>=b.sale_date group by a.sale_date,a.quantity order by a.sale_date
)c,t_goods d, t_category e
where c.goods_no=d.goods_no and d.goods_category=e.category_id;
SQL题目六
1、财务咨询顾问的收入统计问题
金太阳财务咨询服务公司，聘请了一些财务顾问提供财务操作咨询服务。
这些顾问都以兼职形式为公司服务，公司将按小时为顾问工作计算薪酬。
具体表信息如下：
-- a、顾问信息表 (consultant)
create table consultant(
cst_id int primary key,
cst_name varchar2(30) not null
);
insert into consultant values(1, 'david');
insert into consultant values(2, 'henry');
insert into consultant values(3, 'mary');
insert into consultant values(4, 'kent');
-- b、顾问费率表 (billings)
-- 不同级别的顾问，在不同的时期，每小时的收费是不同的，公司会根据情况上调或者下调顾问的小时佣金待遇。
create table billings(
cst_id int not null,
bill_date date not null,
bill_rate int not null,
constraint PK_BILLING PRIMARY KEY(cst_id,bill_date),
constraint FK_BILLING_EMP FOREIGN KEY (cst_id) references consultant(cst_id)
);
insert into billings values(1, to_date('2010-1-1' ,'yyyy/mm/dd'),50);
insert into billings values(2, to_date('2010-1-1' ,'yyyy/mm/dd'),60);
insert into billings values(3, to_date('2010-1-1' ,'yyyy/mm/dd'),70);
insert into billings values(4, to_date('2010-1-1' ,'yyyy/mm/dd'),40);
insert into billings values(1, to_date('2011-1-1' ,'yyyy/mm/dd'),60);
insert into billings values(4, to_date('2011-1-1' ,'yyyy/mm/dd'),45);
-- c、顾问工作情况记录表 (hoursworked)
create table hoursworked(
list_id int primary key,
cst_id int not null,
work_date date not null,
bill_hrs decimal(5,2) not null,
constraint FK_HW_EMP FOREIGN KEY (cst_id) references consultant(cst_id)
);
insert into hoursworked values(1,1, to_date('2010-7-1','yyyy/mm/dd'),3);
insert into hoursworked values(2,1, to_date('2010-8-1','yyyy/mm/dd'),5);
insert into hoursworked values(3,2, to_date('2010-7-1','yyyy/mm/dd'),2);
insert into hoursworked values(4,1, to_date('2011-7-1','yyyy/mm/dd'),4);
insert into hoursworked values(5,3, to_date('2011-8-1','yyyy/mm/dd'),3.5);
insert into hoursworked values(6,4, to_date('2010-9-1','yyyy/mm/dd'),10);
insert into hoursworked values(7,4, to_date('2011-8-1','yyyy/mm/dd'),6);
求解：
我们需要你编写一个SQL查询语句，显示顾问的名字以及其总的顾问费用。
-- 外层搜索 根据最新日期适用最新顾问费率 计算总顾问费用
-- 内层搜索 衍生新表 生成最新日期适用最新顾问费率
select c.cst_id,e.cst_name,sum(d.bill_rate*c.bill_hrs) total_fee
from (
select a.cst_id,a.work_date,max(b.bill_date) bill_date, a.bill_hrs
from billings b inner join hoursworked a
on a.cst_id=b.cst_id and a.work_date>=b.bill_date
group by a.cst_id,a.work_date,a.bill_hrs
)c,billings d,consultant e
where c.cst_id=d.cst_id and c.bill_date=d.bill_date and c.cst_id=e.cst_id
group by c.cst_id,e.cst_name
order by c.cst_id;