Oracle-11g期末考试复习题

合集下载

(完整)oracle期末复习题及答案

1、以下（）内存区不属于SGA。

A．PGA B．日志缓冲区 C．数据缓冲区 D．共享池2、（ )模式存储数据库中数据字典的表和视图。

A．DBA B．SCOTT C．SYSTEM D．SYS3、在Oracle中创建用户时，若未提及DEFAULT TABLESPACE关键字,则Oracle就将( ）表空间分配给用户作为默认表空间。

A．HR B．SCOTT C．SYSTEM D．SYS4、（）服务监听并按受来自客户端应用程序的连接请求。

A．OracleHOME_NAMETNSListenerB．OracleServiceSIDC．OracleHOME_NAMEAgentD．OracleHOME_NAMEHTTPServer5、（）函数通常用来计算累计排名、移动平均数和报表聚合等.A．汇总 B．分析 C．分组 D．单行6、( ）SQL语句将为计算列SAL＊12生成别名Annual SalaryA．SELECT ename，sal＊12 ‘Annual Salary' FROM emp；B．SELECT ename,sal＊12 “Annual Salary” FROM emp;C．SELECT ename,sal＊12 AS Annual Salary FROM emp;D．SELECT ename,sal*12 AS INITCAP(“Annual Salary”） FROM emp；7、锁用于提供（ )。

A．改进的性能B．数据的完整性和一致性C．可用性和易于维护D．用户安全8、（）锁用于锁定表,允许其他用户查询表中的行和锁定表,但不允许插入、更新和删除行。

A．行共享 B．行排他 C．共享 D．排他9、带有（ )子句的SELECT语句可以在表的一行或多行上放置排他锁. A．FOR INSERT B．FOR UPDATEC．FOR DELETE D．FOR REFRESH10、使用（ )命令可以在已分区表的第一个分区之前添加新分区。

Oracle 11g试卷(A)

系别：____________________ 专业________________ 班级：__________________ 姓名：_______________ 学号：____________―――――密――――――――――――――――――――封―――――――――――――――――――――――――――线―――――― __________________答__________题__________不__________得__________超__________过__________此__________线_______________SSS 学院考试试卷课程名称： Oracle 11g (A 卷)_ _201q －_201w__学年第_4_学期《Oracle 11g 期末考试题》一、选择题（每题2分，共50分）1、以下文件不属于Oracle 数据库物理存储结构的是（）A) 数据文件 B) 重做日志文件 C) 控制文件 D) 备份文件2、当启动Oracle 实例时，默认情况下，哪个后台进程不会被启动（） A) DBWn B ）LGWR C) CKPT D) ARCn3、在数据库存储结构中，按从小到大的次序排列正确的是（） A) 区、块、段、表空间 B) 表空间、段、区、块 C) 块、区、段、表空间 D) 块、段、区、表空间4、下列哪个内存结构记录实例对数据库所做的所有修改（） A) 数据库缓冲区缓存 B) 共享池 C) 数据字典缓存 D) 重做日志缓冲区5、脏（DIRTY ）缓冲区指的是 ( ) A) 正在被访问的数据缓冲区 B) 已经被修改，但还没有写到文件的数据缓冲区 C) 空闲的数据缓冲区 D) 已经被修改，并已经写到文件的数据缓冲区6、向数据库发出一个COMMIT 命令提交事物时，哪个后台进程将重做日志缓冲区的内容写到联机重做日志文件（） A) LGWR B) CMMT C) CKPT D) DBWn7、如果需要操作系统中对数据库的文件进行物理备份，应当令数据库进入哪种状态（） A) 受限状态 B) 挂起状态 C) 静默状态 D) 加载但不打开状态8、下列哪一个是错误的启动语句（） A) STARTUP NORMAL B) STARTUP NOMOUNT C) STARTUP MOUNT D) STARTUP FORCE9、使用以下哪一条SHUTDOWN 语句关闭数据库之后，在下次打开数据库时必须进行回复操作（）A) SHUTDOWN NORMAL B) SHUTDOWN IMMEDIATE C) SHUTDOWN TRANSACTION D) SHUTDOWN ABORT 10、下列哪个关键字或子句可以用来限定查询结果集中的行（） A) SELECT B) WHERE C) UPDA TE D) INSERT 11、为了除去结果集中重复的行，可在SELECT 语句中使用下列哪一个关键字 ( ) A) ALL B) DISTINCT C) SPOOL D) HA VING 12、查询一个表的总记录数，可以采用（）统计函数 A) A VG （*） B) SUM(*) C) COUNT(*) D) MAX(*) 13、当COL_NAME 取（）值时，表达式COL_NAME<ALL(5,10,13)为真 A) 12 B) 11 C) 8 D) 2 14、当COL_NAME 取（）值时，表达式COL_NAME<ANY(5,10,13)为假 A) 12 B) 11 C) 8 D) 2 15、HA VING 子句的作用是什么（） A) 查询结果的分组条件 B) 组的筛选条件 C) 限定返回的行的判断条件 D) 对结果集进行排序 16、在SQL*PLUS 中如果要控制列的显示格式，使用哪个命令 ( ) A) SHOW B) DEFINE C) COLUMN D) SPOOL 17、如果要设置SQL*PLUS 每页打印的数量，使用下列哪个命令（） A) SET PAGE B) SET PAGESIZE C) SIZE D) PAGESIZE 18、以下哪个变量是非法的（） A) var_ab number B) var_ab number not null:=’0’ C) var_ab number default:= 1 D) var_ab number:=3 19、声明%TYPE 类型的变量时，服务器将会做什么操作（） A) 为该变量检索数据表中列的数据类型 B) 复制一个变量 C) 检索数据库中的数据D) 为该变量检索列的数据类型和值 20、下列哪一项关于主键约束是正确的（） A) 在一个表中最多只能有两个主键约束B) 主键约束用于唯一地确定表中的每一行数据 C) 主键约束只能由一个列组成 D) 主键约束不能由一个单列组成 21、Oracle 11g 系统提供4种常用的大对象类型，下列哪个不对（） A) CLOB B) NCLOB C) LOB D) BFILE 22、下列哪一项不是伪列ROWID 的作用（） A) 保存记录的物理地址 B) 快速查询指定记录 C) 标示各条记录D) 保存记录的头信息 23、如果一个表EMPLOYEES 添加一个字段EMAIL ，并且规定每个员工都必须有唯一的EMAIL，则应当为EMAIL字段建立哪种约束（）A) PRIMARY KAY B) UNIQUEC) CHECK D) NOT NULL24、Oracle提供的EXPDP工具，能导出4种数据，但不包括（）A) 表B) 表空间C) 用户D) 全数据库25、以下关于使用SQL*LOADER导入外部数据说法不正确的是（）A) 必须编制一个控制文件B) 必须要有一个数据文件C) 导入的表必须是空的D) 被导入的数据必须是固定格式二、判断题（每题1分，共10分）1、资源配置文件可以对系统中所有文件进行限制（）2、只要是Oracle合法的用户就能做想做的任何操作（）3、在ORDER BY子句中，ASC关键字代表升序排序，DESC代表降序（）4、在进行模糊查询的时候使用关键字LIKE，通配符可以用“-”或“&”（）5、存储过程是一种命名PL/SQL程序块，只能通过EXECUT或PL/SQL程序块内部调用（）6、表的主键约束只能由一列组成（）7、唯一性约束强调所在的列不允许有相同的值，但允许有空值（）8、索引是一种与表相关的可选数据结构，用于加速数据的存取（）9、DBA角色不具有SYSDBA和SYSOPER特权，而SYSDBA特权自动具有DBA角色所有的权限。

oracle期末考试卷

oracle期末考试卷Oracle期末考试卷一、选择题（每题2分，共20分）1. Oracle数据库的默认端口号是什么？A. 1521B. 3306C. 1433D. 54322. 在Oracle数据库中，以下哪个命令用于查看当前数据库的所有用户？A. SELECT USER FROM DUAL;B. SELECT DISTINCT USERNAME FROM DBA_USERS;C. SELECT * FROM ALL_USERS;D. SELECT * FROM USER_USERS;3. 以下哪个不是Oracle数据库的体系结构组件？A. 数据库B. 实例C. 表空间D. 索引4. Oracle数据库的哪个组件负责管理数据的存储和检索？A. SQLB. PL/SQLC. RDBMSD. DBMS5. 在Oracle数据库中，以下哪个命令用于创建一个新的表？A. CREATE TABLE;B. CREATE DATABASE;C. CREATE INDEX;D. CREATE VIEW;6. Oracle数据库中，哪个命令用于删除一个表？A. DROP TABLE;B. REMOVE TABLE;C. DELETE TABLE;D. ERASE TABLE;7. Oracle数据库中，哪个命令用于查看表的结构？A. DESCRIBE table_name;B. EXPLAIN table_name;C. SHOW table_name;D. SELECT table_name;8. 在Oracle数据库中，以下哪个命令用于添加新的列到一个已存在的表？A. ADD COLUMN column_name column_type;B. INSERT COLUMN column_name column_type;C. CREATE COLUMN column_name column_type;D. APPEND COLUMN column_name column_type;9. Oracle数据库中，哪个命令用于更新表中的记录？A. UPDATE table_name SET column_name = new_value WHERE condition;B. MODIFY table_name SET column_name = new_value WHERE condition;C. CHANGE table_name SET column_name = new_value WHERE condition;D. ALTER table_name SET column_name = new_value WHERE condition;10. 在Oracle数据库中，以下哪个命令用于删除表中的记录？A. ERASE FROM table_name WHERE condition;B. REMOVE FROM table_name WHERE condition;C. DELETE FROM table_name WHERE condition;D. DROP FROM table_name WHERE condition;二、简答题（每题5分，共30分）1. 简述Oracle数据库的体系结构。

Oracle复习题及答案

Oracle复习题及答案考试复习题⼀、填空1、oracle数据库中的数据⽂件的扩展名是什么？.dbf2、控制⽂件的扩展名是什么？.ctl3、刚建⽴完成数据库，在第⼀次使⽤OMS之前需要建⽴? 资料档案库4、在SQL*plus中，输⼊⼀⾏语句之后，在语句结尾处要写? 分号( ; )5、在ORACLE中，程序块分为命名程序块、匿名程序块。

⼆、概念：1、什么是DBA？答：DBA是英⽂Data Base Administrator的缩写，也就是数据库管理员。

2、SQL语⾔的主要特点是什么？答：SQL语句的特点主要有：①综合统⼀；②⾼度⾮过程化；③⾯向集合的操作⽅式；④以同⼀种语法提供两种操作⽅式；⑤语⾔简洁，易学易⽤。

3、简单介绍⼏种当前流⾏的关系数据库系统？答：Oracle,MySql,MsSql,DB2,Sybase.4、从硬件⾓度指出提⾼ORACLE运⾏速度的⽅法？答：加⼤内存，加⼤硬盘缓存。

5、简单介绍ORACLE的物理结构？答：物理结构：在oracle中四类⽂件数据⽂件.dbf ⽇志⽂件.log 控制⽂件.ctl 配置⽂件.ora6、介绍⽇志的种类，各种⽇志的运⾏机制，各有什么优缺点？答：⽇志可分为：归档模式和不归档模式两种。

归档模式运⾏机制是：重复写数据，当⽂件写满重新备份再写、恢复强、占空间。

特点是：缺点占地⽅优点恢复能⼒强。

⾮归档模式的运⾏机制是：不备份，恢复能⼒有限。

特点是：缺点占空间⼩优点恢复能⼒差。

7、写出序列在实际操作中的作⽤。

答：序列(sequence)是多个⽤户能够在此产⽣唯⼀整数的数据库对象，可以⽤序列⾃动产⽣主键值。

序列是⼀个连续数字⽣成器。

序列通过提供唯⼀数值的顺序来简化程序设计⼯作。

8、写出⼀个SQL语句，语句当中需要使⽤到“同义词”。

答：Select * from system.table1 t1,system.table2 t2 where /doc/628cf3d776eeaeaad1f33070.html=/doc/628cf3d776eeaeaad1f33070.html ;9、写出⼀个数据字典的应⽤（所有⽤户：all_users）答：select * from all_users;10、写出数据库默认的两个⽤户的名称和密码答：⽤户名：system 密码：manager⽤户名：sys 密码：change_on_install;11、在⽤OMS/OEM建⽴数据库时，什么情况下使⽤“数据仓库”类型，什么情况使⽤“事务处理”类型？答：当数据库必须处理⼤量的复杂查询，这些复杂查询将涉及到数据库中⼤量的表和记录，并且⼤部分查询都是只读的时候，使⽤“数据仓库”类型。

ORACLE11g试题答案

. .. .目录第一章Oracle 11g 介绍 (2)第二章ORACLE 11g 的体系结构 (4)第三章ORACLE 11g 的数据库管理 (8)第四章ORACLE 11g 的表空间管理 (10)第五章ORACLE 11g 的表管理 (13)第六章ORACLE 11g 的数据查询 (18)第七章ORACLE 数据的基本操作 (23)第八章索引 (28)第九章视图 (33)第十章PL/SQL基础 (37)第十一章存储过程与函数 (43)第十二章触发器 (48)第十三章游标 (51)第十四章安全管理 (54)第十五章数据库备份与恢复 (57). .资料. ..第一章Oracle 11g 介绍一、选择题1．在数据库系统中，将满足以下两个条件的基本层次联系集合称为层次模型：( B )。

①有一个结点无双亲②其它结点无双亲③有且仅有一个结点无双亲④其它结点有且仅有一个双亲⑤允许其它结点有多个双亲A．①和②B．③和④C．③和⑤D．②和⑤2．下列有关数据库的描述，正确的是( C )A.数据库是一个DBF文件B.数据库是一个关系C.数据库是一个结构化的数据集合D.数据库是一组文件3．根据关系数据基于的数据模型——关系模型的特征判断下列正确的一项：( B )A.只存在一对多的实体关系，以图形方式来表示。

B.以二维表格结构来保存数据，在关系表中不允许有重复行存在。

C.能体现一对多、多对多的关系，但不能体现一对一的关系。

D.关系模型数据库是数据库发展的最初阶段。

4．Oracle 11g 是基于（ A ）的A.关系型B.文件系统C.层次型D.网络型5. 用二维表结构表达实体集的模型是（D ）A．概念模型B．层次模型C．网状模型D．关系模型6．下列四项中说法不正确的是（C ）A.数据库减少了数据冗余B.数据库中的数据可以共享C.数据库避免了一切数据的重复D.数据库具有较高的数据独立性7．下列四项中，不属于关系数据库特点的是（D）A.数据冗余小B.数据独立性高C.数据共享性好D.多用户访问下面系统中不属于关系数据库管理系统的是（C ）A. OracleB. MS SQL ServerC. IMSD. DB29．MS SQL Server是（D ）A. 数据库B. 数据库系统C. 数据处理系统D. 数据库管理系统二、填空题1．在数据库系统中管理数据的软件称为___数据库管理系统________。

最新Oracle_OCP11g考试题库

Oracle_OCP11g考试题库1.Which statement is true regarding the COALESCE function? A. It can have a maximum of five expressions in a list B. It returns the highest NOT NULL value in the list for all rows C. It requires that all expressions in the list must be of the same data type D. It requires that at least one of the expressions in the list must have a NOT NULL valueAnswer: C2.View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.) A. SELECT promo_id, DECODE (NVL(promo_cost,0), promo_cost,promo_cost * 0.25, 100) "Discount"FROM promotions;B. SELECT promo_id, DECODE (promo_cost, 10000,DECODE (promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost"FROM promotions;C SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL,promo_cost*.25, 'N/A') "Catcost"FROM promotions;D. SELECTpromo_id,DECODE(promo_cost, >10000, 'High',<10000, 'Low')"Range"FROM promotions;Answer: A, B3.View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust _last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600? A. INSERT INTO orders VALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust last name= 'Roberts' ANDcredit_limit=600), 1000);B. INSERT INTO orders (order_id,order_date,order_mode,(SELECT customer_idFROM customersWHERE cust last name= 'Roberts' ANDcredit_limit=600),order_total)VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000); C. INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id=c.customer_idAND c.cust_last_name='Roberts' ANDc.credit_limit=600 |VALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name= 'Roberts' AND credit_limit=600 ), 1000);D. INSERT INTO orders (order_id,order_date,order_mode,(SELECT customer_idFROM customersWHERE cust_last_name= 'Roberts' ANDcredit_limit=600),order_total)VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000);Answer: A4.View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statementSQL> SELECT cust_city, COUNT(cust_last_name)FROM customersWHERE cust_credit_limit > 1000GROUP BY cust_cityHAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;Which statement is true regarding the outcome of the above query? A. It executes successfullyB. It returns an error because the BETWEEN operator cannot be usedin the HAVINGclauseC. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statementD. It returns an error because WHERE and HAVING clauses cannot be used to applyconditions on the same columnAnswer: A5.View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:Which statement is true regarding the above two SQL statements? A. statement 1 gives an error, statement 2 executes successfully B. statement 2 gives an error, statement 1 executes successfully C. statement 1 and statement 2 execute successfully and give the same output D. statement 1 and statement 2 execute successfully and give a different outputAnswer: D6.You created an ORDERS table with the following description:You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?A. You cannot have two constraints on one columnB. You cannot add a primary key constraint if data exists in the columnC. The primary key constraint can be created only at the time of table creationD. You can add the primary key constraint even if data exists, provided that there are no duplicate valuesAnswer: D7.When does a transaction complete? (Choose all that apply.)A. when a DELETE statement is executedB. when a ROLLBACK command is executedC. when a PL/SQL anonymous block is executedD. when a data definition language (DDL) statement is executedE. when a TRUNCATE statement is executed after the pending transact ion Answer: B, D, E8.You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character'a' in the second last position Which query would give the required output?A. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e') <>0 ANDSUBSTR(cust_first_name, -2, l) ='a';B. SELECT cust first nameFROM customersWHERE INSTR(cust_first_name, 'e') <>" ANDSUBSTR(cust_first_name, -2, l)='a';C. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e') IS NOT NULL ANDSUBSTR(cust_first_name, l, -2)='a';D. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a';Answer: A9.The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.Which statement would create a synonym ORD so that HR can execute the following query successfully?SELECT * FROM ord;A. CREATE SYNONYM ord FOR orders; This command is issued by OEB. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OEC. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administratorD. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issuedby the database administratorAnswer: D10.View the Exhibit and examine the structure of the PRODUCTS table You need to generate a report in the following format:Which two queries would give the required output? (Choose two.) A. SELECT prod_name || q"'s category is ' || prod_category CATEGORIES FROM products;B. SELECT prod_name || q'['s]'category is' || prod_category CATEGORIESFROM products;C. SELECT prod_name || q'\'s\' category is '|| prod_category CATEGORIESFROM products;D. SELECT prod_name ||q'<'s>'|| 'category is ' || prod_category CATEGORIESFROM products;Answer: C, D11.Which statement is true regarding the INTERSECT operator?A. It ignores NULL valuesB. Reversing the order of the intersected tables alters the resultC. The names of columns in all SELECT statements must be identicalD. The number of columns and data types must be identical for all SELECT statements in the queryAnswer: D12.Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)A. Both USING and ON clauses can be used for equijoins and nonequijoinsB. A maximum of one pair of columns can be joined between two tables using the ON clauseC. The ON clause can be used to join tables on columns that have different names but compatible data typesD. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clauseAnswer: C, D13.Examine the structure of the PROGRAMS tableWhich two SQL statements would execute successfully? (Choose two.) A. SELECT NVL(ADD_MONTHS(END_DATE,l),SYSDATE)FROM programs;B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))FROM programs;C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')FROM programs;D. SELECTNVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')FROM programs;Answer: A, D14.Where can subqueries be used? (Choose all that apply.) A. fieldnames in the SELECT statementB. the FROM clause in the SELECT statementC. the HAVING clause in the SELECT statementD. the GROUP BY clause in the SELECT statementE. the WHERE clausein only the SELECT statement F. the WHERE clause in SELECT as well asall DML statementsAnswer: A, B, C, F15.View the Exhibits and examine the structures of the PRODUCTS, SAL ES, andCUSTOMERS tablesYou need to generate a report that gives details of the customer'slast name, name ofthe product, and the quantity sold for all customers in 'Tokyo' Which two queries give the required result? (Choose two.) A. SELECT c.cust_last_name,p.prod_name,s.quantity_soldFROM sales s JOIN products pUSING(prod_id)JOIN customers cB. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales s JOIN customers cON(p.prod_id=s.prod_id)ON(s.cust_id=c.cust_id)WHERE c.cust_city='Tokyo';C. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales sON(p.prod_id=s.prod_id)JOIN customers cON(s.cust_id=c.cust_id)AND c.cust_city='Tokyo';D. SELECT c.cust_id, c.cust_last_name, p.prod_id, p.prod_name, s.quantity_soldFROM products p JOIN sales sUSING(prod_id)JOIN customers cUSING(cust_id)WHERE c.cust_city='Tokyo';Answer: A, C16.View the Exhibit and evaluate the structure and data in theCUST_STATUS table You issue the following SQL statementSQL> SELECT custno, NVL2(NULLIF(amt_spent,credit_limit), 0, 1000) "BONUS"FROM cust_status;Which statement is true regarding the execution of the above query?A. It produces an error because the AMT_SPENT column contains a null valueB. It displays a bonus of 1000forall customers whose AMT_SPENT is less than CREDIT_LIMITC. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is nullD. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 functionAnswer: C17.Examine the structure and data in the PRICE LIST tableYou plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.Which SQL statement would give the required result?A. SELECT TO_CHAR(prod_price* .25, '$99,999.99')FROM PRICE_LIST;B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, '$99,999.00')FROM PRICE_LIST;C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;D. SELECT TO_NUMBER(TO_NUMBER(prod_price,$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;Answer: C18.You need to generate a list of all customer last names with their credit limits from theCUSTOMERS table. Those customers who do not have a credit limitshould appear last in the list.Which two queries would achieve the required result? (Choose two.) A. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit DESC;B. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit;C. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit NULLS LAST;D. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_last_name, cust_credit_limit NULLSLAST;Answer: B, C19.Which two statements are true regarding the COUNT function? (Choose two.) A. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data typesB. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columnsC. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID columnD. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT columnE. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clauseAnswer: B, D20.Which two statements are true regarding single row functions? (Choose two.) A. They accept only a single argumentB. They can be nested only to two levelsC. Arguments can only be column values or constantsD. They always return a single result row for every row of a queried tableE. They can return a data type value different from the one that is referencedAnswer: D, E21.View the Exhibit and examine the data in the COSTS table.You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.You issue the following query:SQL>SELECT prod_idFROM costsWHERE unit_price >= unit_cost * 1.25ORDER BY unit_cost * 0.25 DESC;Which statement is true regarding the above query?A. It executes and produces the required resultB. It produces an error because an expression cannot be used in the ORDER By clauseC. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clauseD. It produces an error because the expression in the ORDER BYclause should also be specified in the SELECT clauseAnswer: A22.View the Exhibit and examine the structure of the CUSTOMERS table Which statement would display the highest credit limit available in each income levelin each city in the CUSTOMERS table?A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level, cust_credit_limit; B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level;C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_credit_limit, cust_income_level, cust_city; D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);Answer: B23.Which two statements are true regarding subqueries? (Choose two.) A.A subquery can retrieve zero or more rowsB. Only two subqueries can be placed atone levelC. A subquery can be used only in SQL query statementsD. A subquery can appear on either side of a comparison operatorE. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statementAnswer: A, D24.View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:The above query generates an error on execution.Which clause in the above SQL statement causes the error? A. WHEREB. SELECTC. GROUP BYD. ORDER BYAnswer: C25.You need to create a table for a banking application One of the columns in the table has the following requirements:1) You want a column in the table to store the duration of the credit period 2) The data in the column should be stored in a format such that it can be easily addedand subtracted with DATE data type without using conversion functions 3) The maximum period of the credit provision in the application is 30days 4) The interest has to be calculated for the number of days an individual has taken acredit for.Which data type would you use for such a column in the table? A. DATEB. NUMBERC. TIMESTAMPD. INTERVAL DAY TO SECONDE. INTERVAL YEAR TO MONTHAnswer: D26.View the Exhibit to examine the description for the SALES table Which views can have all DML operations performed on it? (Choose all that apply.)A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;B. CREATE VIEW vlAS SELECT * FROM SALESWHERE time_id <= SYSDATE - 2*365WITH CHECK OPTION;C. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time id <= SYSDATE -2*365WITH CHECK OPTION;D. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time id <= SYSDATE -2*365GROUP BY prod_id, cust_idWITH CHECK OPTION;Answer: A, B27.Which is the valid CREATE TABLE statement?A. CREATE TABLE emp9$# (emp_no NUMBER(4));B. CREATE TABLE 9emp$# (emp_no NUMBER(4));C. CREATE TABLE emp*123 (emp_no NUMBER(4));D. CREATE TABLE emp9$# (emp_no NUMBER (4), date DATE);Answer: A28.View the Exhibit and examine the data in the PRODUCTS table. Youneed to display product names from the PRODUCTS table that belong to the'Software/Other' category with minimum prices as either $2000 or $4000 and no unitof measure.You issue the following query:SQL>SELECT prod_name, prod_category, prod_min_priceFROM productsWHERE prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> "; Which statement is true regarding the above query? A. It executes successfully but returns no result B. It executes successfully and returns the required result C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not validD. It generates an error because the condition specified for the PROD_CATEGORYColumn is not validAnswer: A29.View the Exhibit to examine the description for the SALES and PRODUCTS tables. You want to create a SALE _PROD view by executing the following SQL statement:Which statement is true regarding the execution of the above statement? A. The view will be created and you can perform DML operations on the view B. The view will be created but no DML operations will be allowed on the view C. The view will not be created because the join statements are not allowed for creating a viewD. The view will not be created because the GROUP BY clause is not allowed for creating a viewAnswer: B30.Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)A. Only one LONG column can be used per tableB. A TIMESTAMP data type column stores only time values with fractional secondsC. The BLOB data type column is used to store binary data in an operating system fileD. The minimum column width that can be specified for a VARCHAR2 data type column is oneE. The value for a CHAR data type column is blank-padded to the maximum defined column widthAnswer: A, D, E31.View the Exhibit and examine the structure of the PRODUCTS table.Evaluate the following query:What would be the outcome of executing the above SQL statement?A. It produces an errorB. It shows the names of all products in the tableC. It shows the names of products whose list price is the second highest in the tableD. It shows the names of all products whose list price is less than the maximum list priceAnswer: C32.View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. The ORD_NO column is PRIMARY KEY in the ORD table and the ORD NO and ITEM_NO columns are composite PRIMARY KEY in the ORD ITEMS table. Which two CREATE INDEX statements are valid? (Choose two.) A. CREATE INDEX ord_idxlON ord(ord_no);B. CREATE INDEX ord_idx2ON ord_items(ord_no);C. CREATE INDEX ord_idx3ON ord_items(item_no);D. CREATE INDEX ord_idx4ON ord,ord_items(ord_no, ord_date,qty);Answer: B, C33.Which statement is true regarding subqueries?A. The LIKE operator cannot be used with single-row subqueriesB. The NOT IN operator is equivalent to IS NULL with single-row subqueriesC. =ANY and =ALL operators have the same functionality in multiple-row subqueriesD. The NOT operator can be used with IN, ANY, and ALL operators in multiple-row subqueriesAnswer: D34.You are currently located in Singapore and have connected to a remote database in Chicago.You issue the following command:SQL> SELECT ROUND (SYSDATE-promo_begin_date, 0)FROM promotionsWHERE (SYSDATE-promo_begin_date)/365 >2;PROMOTIONS is the public synonym for the public database link forthe PROMOTIONS table.What is the outcome?A. an error because the ROUND function specified is invalidB. an error because the WHERE condition specified is invalidC. number of days since the promo started based on the current Chicago date and timeD. number of days since the promo started based on the current Singapore date and timeAnswer: C35.View the Exhibit and examine the structure of the PRODUCTS table. Using the PRODUCTS table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price fallsbelow $10 after a discount of 25% is applied on it.SQL>SELECT prod_name, prod_list_price,prod_list_price-(prod_list_price*.25) "DISCOUNTED_PRICE"FROM productsWHERE discounted_price < 10;The query generates an error.What is the reason for the error?A. The parenthesis should be added to enclose the entire expressionB. The double quotation marks should be removed from the column aliasC. The column alias should be replaced with the expression in the WH ERE clauseD. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clauseAnswer: C36.View the Exhibit for the structure of the STUDENT and FACULTY tables. You need to display the faculty name followed by the number of students handled by the faculty at the base location.Examine the following two SQL statements:Which statement is true regarding the outcome?A. Only statement 1 executes successfully and gives the required resultB. Only statement 2 executes successfully and gives the required resultC. Both statements 1 and 2 execute successfully and give different resultsD. Both statements 1 and 2 execute successfully and give the same required resultAnswer: D37.View the Exhibit and examine the structure of CUSTOMERS and SALES tables. Evaluate the following SQL statement:Which statement is true regarding the execution of the above UPD ATE statement? A. It would not execute because two tables cannot be used in a single UPDATE statementB. It would not execute because the SELECT statement cannot be used in place of the table nameC. It would execute and restrict modifications to only the columns specified in the SELECT statementD. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statementAnswer: C38.Evaluate the following query:SQL> SELECT TRUNC(ROUND(156.00,-1),-1)FROM DUAL;What would be the outcome?A. 16B. 100C. 160D. 200E. 150Answer: C39.View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.Evaluate the following SQL statement:Which statement is true regarding the output of the above query? A. It gives the details of promos for which there have been sales B. It gives the details of promos for which there have been no sales C. It gives details of all promos irrespective of whether they have resulted in a sale ornotD. It gives details of product 105 that have been sold irrespective of whether they had a promo or notAnswer: C40.View the Exhibit and examine the description of SALES and PROMOTIONS tables You want to delete rows from the SALES table, where thePROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values Which DELETE statements are valid? (Choose all that apply.)A. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' )AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price' );B. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' )OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price' ); C. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' )OR promo_name = 'everyday low price' ); D. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN ('blowout sale', 'everyday lowprice' ));Answer: B, C, D41.View the Exhibit and examine the structure of the PROMOTIONS table.You have to generate a report that displays the promo name and start date for allpromos that started after the last promo in the' INTERNET' category. Which query would give you the required output?A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date)FROM promotions )ANDpromo_category = 'INTERNET');B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_date)FROM promotionsWHERE promo_category = 'INTERNET');C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');Answer: C42.View the Exhibit and examine the data in the CUSTOMERS table Evaluate the following query:SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT, MIDPOINT+100AS "MAX LOWER LIMIT"FROM customers;The above query produces an error on executionWhat is the reason for the error?A. An alias cannot be used in an expressionB. The alias NAME should not be enclosed within double quotation marksC. The MIDPOINT+100 expression gives an error because CUST_CREDIT _LIMIT contains NULL valuesD. The alias MIDPOINT should be enclosed within double quotation marks for theCUST_CREDIT_LIMIT/2 expressionAnswer: A43.View the Exhibit and examine the structure of the ORD tableEvaluate the following SQL statements that are executed in a user session in the specified order.What would be the outcome of the above statements?A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST ID 101B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specifiedC. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specifiedD. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20 Answer: A44.Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.Which query would give the required result?A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50AS "50% Credit Limit"FROM customers;B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit *0.50AS "50% Credit Limit"FROM customers;C. SELECT DISTINCT cust_income level || ' ' || cust_credit_limit *0.50AS "50% Credit Limit"FROM customers;D. SELECT cust income level || ' ' || cust_credit_limit * 0.50 AS "50% Credit Limit"ROM customers;Answer: C45.View the Exhibit and examine the structure of the CUSTOMERS table Evaluate the query statement:What would be the outcome of the above statement?A. It executes successfully。

ORCALE11G 期末试题及答案B卷

学年第一学期期末考试试题（卷）
专业：班级：姓名：学号：
装订线装订线以内不准作任何标记装订线
学院
课程考试参考答案与评分标准
2013 /2014 学年第一学期
课程名称：ORCALE 考试性质：考查试卷类型：B
考试班级：计科考试方法：命题教师：
一、选择题（每小题2分，共30分）
Ccdab CBBDC CCBDC
二、判断（每题2分，共20分）
YNNYY,NNNNY
三：简答题（每小题5分，共10分）
1、创建并启动与数据库对应的实例
为实例加载数据库
将数据库设置为打开状态
2、数值、字符、日期、LOB 、ROWID类型
四、读程题（每小题10分，共20分）
1、hello world
2、15,12,9,6,3
五、编程题（每小题10分，共20分）
1. Begin update emp
Set sal=sal*1.5;
Where empno=’7369’;
If sql%notfound then
Dbms_output.putline(”未更新任何记录”);
Else
Dbms_output.putline(”更新’|| sql%rowcount || ‘条记录”);
End if；
End；
2、
Create or replace trigger tg_emp
Before insert or update or delete on emp
Insert into emp_log（who ，when）values （user ，sysdate）；End；
/。

2012下半年理工Oracle11g期末模拟试卷及答案

2012下半年理⼯Oracle11g期末模拟试卷及答案2012下半年理⼯Oracle11g期末模拟试卷⼀、选择题（每空1分，共10分）1、SQL语⾔中不属于数据定义的命令动词是（）A.CREATEB.DROPC.GRANTD.ALTER2、（）SQL语句将为计算列SAL*12⽣成别名Annual SalaryA．SELECT ename,sal*12 …Annual Salary? FROM emp;B．SELECT ename,sal*12 “Annual Salary” FROM emp;C．SELECT ename,sal*12 AS Annual Salary FROM emp;D．SELECT ename,sal*12 AS INITCAP(“Annual Salary”) FROM emp;3、带有错误的视图可使⽤（）选项来创建。

A．FORCEB．WITH CHECK OPTIONC．CREATE VIEW WITH ERRORD．CREATE ERROR VIEW4、要以⾃⾝的模式创建私有同义词，⽤户必须拥有（）系统权限A．CREATE PRIVATE SYNONYMB．CREATE PUBLIC SYNONYMC．CREATE SYNONYMD．CREATE ANY SYNONYM5、PL/SQL块中不能直接使⽤的SQL命令是（）。

A．SELECT B．INSERTC．UPDATE D．DROP6、⽤于处理得到单⾏查询结果的游标为（）。

A．循环游标 B．隐式游标C．REF游标 D．显式游标7、公⽤的⼦程序和常量在（）中声明。

A．过程 B．游标C．包说明 D．包主体8、以下不属于命名的PL/SQL块的是（）。

A．程序包 B．过程C．游标 D．函数9、以下哪种备份⽅式需要在完全关闭数据库进⾏（）。

A．⽆归档⽇志模式下的数据库备份B．归档⽇志模式下的数据库备份C．使⽤导出实⽤程序进⾏逻辑备份D．以上都不对10、（）参数⽤于确定是否要导⼊整个导出⽂件。

oracle期末考试题及答案

oracle期末考试题及答案一、选择题（每题2分，共20分）1. Oracle数据库中的用户和模式是什么关系？A. 完全相同B. 完全不同C. 模式是用户的集合D. 用户是模式的集合答案：C2. 在Oracle数据库中，以下哪个命令用于查看当前用户？A. SELECT USER FROM DUAL;B. SELECT CURRENT_USER FROM DUAL;C. SELECT SESSION_USER FROM DUAL;D. SELECT SYSTEM_USER FROM DUAL;答案：A3. Oracle数据库中的哪个参数用于设置数据库的字符集？A. DB_CHARSETB. NLS_CHARACTERSETC. DB_LANGUAGED. NLS_LANGUAGE答案：B4. 在Oracle数据库中，以下哪个不是SQL语句？A. SELECTB. UPDATEC. DELETED. PRINT答案：D5. Oracle数据库中的触发器可以在哪些时刻执行？A. 数据插入前B. 数据更新前C. 数据删除后D. 数据查询时答案：ABC二、填空题（每空2分，共20分）1. Oracle数据库的默认表空间名称是 _ 。

答案：SYSTEM2. 在Oracle数据库中，用来查看当前数据库版本信息的命令是 _ 。

答案：SELECT * FROM V$VERSION;3. Oracle数据库中，用来创建索引的SQL语句是 _ 。

答案：CREATE INDEX;4. 在Oracle数据库中，存储过程和函数的区别在于存储过程可以有_ ，而函数不能。

答案：OUT参数5. Oracle数据库中，用于实现数据完整性的约束包括主键约束、外键约束、 _ 和CHECK约束。

答案：UNIQUE约束三、简答题（每题10分，共30分）1. 解释Oracle数据库中的视图是什么，并说明其优点。

答案：视图是Oracle数据库中的一种虚拟表，它是基于一个或多个表的查询结果。

Oracle OCP11g考试题库

Which statement is true regarding the COALESCE function?A. It can have a maximum of five expressions in a listB. It returns the highest NOT NULL value in the list for all rowsC. It requires that all expressions in the list must be of the same data typeD. It requires that at least one of the expressions in the list must have a NOT NULL valueAnswer: C2.View the Exhibit and examine the structure of the PROMOTIONS table.Which SQL statements are valid? (Choose all that apply.)A. SELECT promo_id, DECODE (NVL(promo_cost,0), promo_cost,promo_cost * 0.25, 100) "Discount"FROM promotions;B. SELECT promo_id, DECODE (promo_cost, 10000,DECODE (promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost"FROM promotions;C SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL,promo_cost*.25, 'N/A') "Catcost"FROM promotions;D. SELECTpromo_id,DECODE(promo_cost, >10000, 'High',<10000, 'Low')"Range"FROM promotions;Answer: A, BView the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust _last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?A. INSERT INTO orders V ALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust last name= 'Roberts' ANDcredit_limit=600), 1000);B. INSERT INTO orders (order_id,order_date,order_mode,(SELECT customer_idFROM customersWHERE cust last name= 'Roberts' ANDcredit_limit=600),order_total)V ALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);C. INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id,o.order_totalFROM orders o, customers cWHERE o.customer_id=c.customer_idAND c.cust_last_name='Roberts' ANDc.credit_limit=600 |V ALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name= 'Roberts' AND credit_limit=600 ), 1000);D. INSERT INTO orders (order_id,order_date,order_mode,(SELECT customer_idFROM customersWHERE cust_last_name= 'Roberts' ANDcredit_limit=600),order_total)V ALUES(1,'10-mar-2007', 'direct', &customer_id, 1000);Answer: A4.View the Exhibit and examine the structure of the CUSTOMERS table.Evaluate the following SQL statementSQL> SELECT cust_city, COUNT(cust_last_name)FROM customersWHERE cust_credit_limit > 1000GROUP BY cust_cityHA VING A VG(cust_credit_limit) BETWEEN 5000 AND 6000;Which statement is true regarding the outcome of the above query?A. It executes successfullyB. It returns an error because the BETWEEN operator cannot be used in the HA VING clauseC. It returns an error because WHERE and HA VING clauses cannot be used in the same SELECT statementD. It returns an error because WHERE and HA VING clauses cannot be used to apply conditions on the same columnAnswer: AView the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:Which statement is true regarding the above two SQL statements?A. statement 1 gives an error, statement 2 executes successfullyB. statement 2 gives an error, statement 1 executes successfullyC. statement 1 and statement 2 execute successfully and give the same outputD. statement 1 and statement 2 execute successfully and give a different output Answer: DYou created an ORDERS table with the following description:You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?A. You cannot have two constraints on one columnB. You cannot add a primary key constraint if data exists in the columnC. The primary key constraint can be created only at the time of table creationD. You can add the primary key constraint even if data exists, provided that there are no duplicate valuesAnswer: D7.When does a transaction complete? (Choose all that apply.)A. when a DELETE statement is executedB. when a ROLLBACK command is executedC. when a PL/SQL anonymous block is executedD. when a data definition language (DDL) statement is executedE. when a TRUNCATE statement is executed after the pending transact ionAnswer: B, D, E8.You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last positionWhich query would give the required output?A. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e') <>0 ANDSUBSTR(cust_first_name, -2, l) ='a';B. SELECT cust first nameFROM customersWHERE INSTR(cust_first_name, 'e') <>" ANDSUBSTR(cust_first_name, -2, l)='a';C. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e') IS NOT NULL ANDSUBSTR(cust_first_name, l, -2)='a';D. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a';Answer: A9.The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.Which statement would create a synonym ORD so that HR can execute the following query successfully?SELECT * FROM ord;A. CREATE SYNONYM ord FOR orders; This command is issued by OEB. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OEC. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administratorD. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administratorAnswer: D10.View the Exhibit and examine the structure of the PRODUCTS tableYou need to generate a report in the following format:Which two queries would give the required output? (Choose two.)A. SELECT prod_name || q"'s category is ' || prod_category CATEGORIESFROM products;B. SELECT prod_name || q'['s]'category is' || prod_category CATEGORIESFROM products;C. SELECT prod_name || q'\'s\' category is '|| prod_category CATEGORIESFROM products;D. SELECT prod_name ||q'<'s>'|| 'category is ' || prod_category CATEGORIESFROM products;Answer: C, D11.Which statement is true regarding the INTERSECT operator?A. It ignores NULL valuesB. Reversing the order of the intersected tables alters the resultC. The names of columns in all SELECT statements must be identicalD. The number of columns and data types must be identical for all SELECT statements in the queryAnswer: D12.Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)A. Both USING and ON clauses can be used for equijoins and nonequijoinsB. A maximum of one pair of columns can be joined between two tables using the ON clauseC. The ON clause can be used to join tables on columns that have different names but compatible data typesD. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clauseAnswer: C, D13.Examine the structure of the PROGRAMS tableWhich two SQL statements would execute successfully? (Choose two.)A. SELECT NVL(ADD_MONTHS(END_DATE,l),SYSDATE)FROM programs;B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))FROM programs;C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')FROM programs;D. SELECTNVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;Answer: A, D14.Where can subqueries be used? (Choose all that apply.)A. field names in the SELECT statementB. the FROM clause in the SELECT statementC. the HA VING clause in the SELECT statementD. the GROUP BY clause in the SELECT statementE. the WHERE clause in only the SELECT statementF. the WHERE clause in SELECT as well as all DML statementsAnswer: A, B, C, F15.View the Exhibits and examine the structures of the PRODUCTS, SAL ES, and CUSTOMERS tablesYou need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in 'Tokyo'Which two queries give the required result? (Choose two.)A. SELECT c.cust_last_name,p.prod_name,s.quantity_soldFROM sales s JOIN products pUSING(prod_id)JOIN customers cB. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales s JOIN customers cON(p.prod_id=s.prod_id)ON(s.cust_id=c.cust_id)WHERE c.cust_city='Tokyo';C. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales sON(p.prod_id=s.prod_id)JOIN customers cON(s.cust_id=c.cust_id)AND c.cust_city='Tokyo';D. SELECT c.cust_id, c.cust_last_name, p.prod_id, p.prod_name, s.quantity_soldFROM products p JOIN sales sUSING(prod_id)JOIN customers cUSING(cust_id)WHERE c.cust_city='Tokyo';Answer: A, C16.View the Exhibit and evaluate the structure and data in the CUST_STATUS table You issue the following SQL statementSQL> SELECT custno, NVL2(NULLIF(amt_spent,credit_limit), 0, 1000) "BONUS"FROM cust_status;Which statement is true regarding the execution of the above query?A. It produces an error because the AMT_SPENT column contains a null valueB. It displays a bonus of 1000forall customers whose AMT_SPENT is less than CREDIT_LIMITC. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is nullD. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 functionAnswer: C17.Examine the structure and data in the PRICE LIST tableYou plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.Which SQL statement would give the required result?A. SELECT TO_CHAR(prod_price* .25, '$99,999.99')FROM PRICE_LIST;B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, '$99,999.00')FROM PRICE_LIST;C. SELECT TO_CHAR(TO_NUMBER(prod_price, '$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;D. SELECT TO_NUMBER(TO_NUMBER(prod_price,$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;Answer: C18.You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list.Which two queries would achieve the required result? (Choose two.)A. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit DESC;B. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit;C. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_credit_limit NULLS LAST;D. SELECT cust_last_name, cust_credit_limitFROM customersORDER BY cust_last_name, cust_credit_limit NULLSLAST;Answer: B, C19.Which two statements are true regarding the COUNT function? (Choose two.)A. The COUNT function can be used only for CHAR, V ARCHAR2, and NUMBER data typesB. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columnsC. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID columnD. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT columnE. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clauseAnswer: B, D20.Which two statements are true regarding single row functions? (Choose two.)A. They accept only a single argumentB. They can be nested only to two levelsC. Arguments can only be column values or constantsD. They always return a single result row for every row of a queried tableE. They can return a data type value different from the one that is referencedAnswer: D, E21.View the Exhibit and examine the data in the COSTS table.You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.You issue the following query:SQL>SELECT prod_idFROM costsWHERE unit_price >= unit_cost * 1.25ORDER BY unit_cost * 0.25 DESC;Which statement is true regarding the above query?A. It executes and produces the required resultB. It produces an error because an expression cannot be used in the ORDER By clauseC. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clauseD. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clauseAnswer: A22.View the Exhibit and examine the structure of the CUSTOMERS tableWhich statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level, cust_credit_limit;B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level;C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_credit_limit, cust_income_level, cust_city;D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customersGROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);Answer: B23.Which two statements are true regarding subqueries? (Choose two.)A. A subquery can retrieve zero or more rowsB. Only two subqueries can be placed atone levelC. A subquery can be used only in SQL query statementsD. A subquery can appear on either side of a comparison operatorE. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statementAnswer: A, D24.View the Exhibit and examine the structure of the PROMOTIONS table.Evaluate the following SQL statement:The above query generates an error on execution.Which clause in the above SQL statement causes the error?A. WHEREB. SELECTC. GROUP BYD. ORDER BYAnswer: C25.You need to create a table for a banking application One of the columns in the table has the following requirements:1) You want a column in the table to store the duration of the credit period2) The data in the column should be stored in a format such that it can be easily addedand subtracted with DATE data type without using conversion functions3) The maximum period of the credit provision in the application is 30days4) The interest has to be calculated for the number of days an individual has taken a credit for.Which data type would you use for such a column in the table?A. DATEB. NUMBERC. TIMESTAMPD. INTERV AL DAY TO SECONDE. INTERV AL YEAR TO MONTHAnswer: D26.View the Exhibit to examine the description for the SALES tableWhich views can have all DML operations performed on it? (Choose all that apply.)A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;B. CREATE VIEW vlAS SELECT * FROM SALESWHERE time_id <= SYSDATE - 2*365WITH CHECK OPTION;C. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time id <= SYSDATE -2*365WITH CHECK OPTION;D. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time id <= SYSDATE -2*365GROUP BY prod_id, cust_idWITH CHECK OPTION;Answer: A, B27.Which is the valid CREA TE TABLE statement?A. CREATE TABLE emp9$# (emp_no NUMBER(4));B. CREATE TABLE 9emp$# (emp_no NUMBER(4));C. CREATE TABLE emp*123 (emp_no NUMBER(4));D. CREATE TABLE emp9$# (emp_no NUMBER (4), date DATE);Answer: A28.View the Exhibit and examine the data in the PRODUCTS table.You need to display product names from the PRODUCTS table that belong to the'Software/Other' category with minimum prices as either $2000 or $4000 and no unit of measure.You issue the following query:SQL>SELECT prod_name, prod_category, prod_min_priceFROM productsWHERE prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> ";Which statement is true regarding the above query?A. It executes successfully but returns no resultB. It executes successfully and returns the required resultC. It generates an error because the condition specified forPROD_UNIT_OF_MEASURE is not validD. It generates an error because the condition specified for the PROD_CATEGORY Column is not validAnswer: AView the Exhibit to examine the description for the SALES and PRODUCTS tables. You want to create a SALE _PROD view by executing the following SQL statement:Which statement is true regarding the execution of the above statement?A. The view will be created and you can perform DML operations on the viewB. The view will be created but no DML operations will be allowed on the viewC. The view will not be created because the join statements are not allowed for creating a viewD. The view will not be created because the GROUP BY clause is not allowed for creating a viewAnswer: BWhich three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)A. Only one LONG column can be used per tableB. A TIMESTAMP data type column stores only time values with fractional secondsC. The BLOB data type column is used to store binary data in an operating system fileD. The minimum column width that can be specified for a V ARCHAR2 data type column is oneE. The value for a CHAR data type column is blank-padded to the maximum defined column widthAnswer: A, D, E31.View the Exhibit and examine the structure of the PRODUCTS table.Evaluate the following query:What would be the outcome of executing the above SQL statement?A. It produces an errorB. It shows the names of all products in the tableC. It shows the names of products whose list price is the second highest in the tableD. It shows the names of all products whose list price is less than the maximum list priceAnswer: CView the Exhibit and examine the structure of ORD and ORD_ITEMS tables.The ORD_NO column is PRIMARY KEY in the ORD table and the ORD NO and ITEM_NO columns are composite PRIMARY KEY in the ORD ITEMS table. Which two CREATE INDEX statements are valid? (Choose two.)A. CREATE INDEX ord_idxlON ord(ord_no);B. CREATE INDEX ord_idx2ON ord_items(ord_no);C. CREATE INDEX ord_idx3ON ord_items(item_no);D. CREATE INDEX ord_idx4ON ord,ord_items(ord_no, ord_date,qty);Answer: B, C33.Which statement is true regarding subqueries?A. The LIKE operator cannot be used with single-row subqueriesB. The NOT IN operator is equivalent to IS NULL with single-row subqueriesC. =ANY and =ALL operators have the same functionality in multiple-row subqueriesD. The NOT operator can be used with IN, ANY, and ALL operators in multiple-row subqueriesAnswer: DYou are currently located in Singapore and have connected to a remote database in Chicago.You issue the following command:SQL> SELECT ROUND (SYSDATE-promo_begin_date, 0)FROM promotionsWHERE (SYSDATE-promo_begin_date)/365 >2;PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.What is the outcome?A. an error because the ROUND function specified is invalidB. an error because the WHERE condition specified is invalidC. number of days since the promo started based on the current Chicago date and timeD. number of days since the promo started based on the current Singapore date and timeAnswer: C35.View the Exhibit and examine the structure of the PRODUCTS table.Using the PRODUCTS table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.SQL>SELECT prod_name, prod_list_price,prod_list_price-(prod_list_price*.25) "DISCOUNTED_PRICE"FROM productsWHERE discounted_price < 10;The query generates an error.What is the reason for the error?A. The parenthesis should be added to enclose the entire expressionB. The double quotation marks should be removed from the column aliasC. The column alias should be replaced with the expression in the WH ERE clauseD. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clauseAnswer: C36.View the Exhibit for the structure of the STUDENT and FACULTY tables.You need to display the faculty name followed by the number of students handled by the faculty at the base location.Examine the following two SQL statements:Which statement is true regarding the outcome?A. Only statement 1 executes successfully and gives the required resultB. Only statement 2 executes successfully and gives the required resultC. Both statements 1 and 2 execute successfully and give different resultsD. Both statements 1 and 2 execute successfully and give the same required resultAnswer: DView the Exhibit and examine the structure of CUSTOMERS and SALES tables. Evaluate the following SQL statement:Which statement is true regarding the execution of the above UPD ATE statement? A. It would not execute because two tables cannot be used in a single UPDATE statementB. It would not execute because the SELECT statement cannot be used in place of the table nameC. It would execute and restrict modifications to only the columns specified in the SELECT statementD. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statementAnswer: CEvaluate the following query:SQL> SELECT TRUNC(ROUND(156.00,-1),-1)FROM DUAL;What would be the outcome?A. 16B. 100C. 160D. 200E. 150Answer: C39.View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.Evaluate the following SQL statement:Which statement is true regarding the output of the above query?A. It gives the details of promos for which there have been salesB. It gives the details of promos for which there have been no salesC. It gives details of all promos irrespective of whether they have resulted in a sale or notD. It gives details of product 105 that have been sold irrespective of whether they had a promo or notAnswer: C40.View the Exhibit and examine the description of SALES and PROMOTIONS tables You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values Which DELETE statements are valid? (Choose all that apply.)A. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' ) AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price' );B. DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' ) OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = 'everyday low price' );C. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name = 'blowout sale' )OR promo_name = 'everyday low price' );D. DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN ('blowout sale', 'everyday lowprice' ));Answer: B, C, D41.View the Exhibit and examine the structure of the PROMOTIONS table.You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the' INTERNET' category.Which query would give you the required output?A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date)FROM promotions )ANDpromo_category = 'INTERNET');B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_date)FROM promotionsWHERE promo_category = 'INTERNET');C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET'); Answer: C42.View the Exhibit and examine the data in the CUSTOMERS tableEvaluate the following query:SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100AS "MAX LOWER LIMIT" FROM customers;The above query produces an error on executionWhat is the reason for the error?A. An alias cannot be used in an expressionB. The alias NAME should not be enclosed within double quotation marksC. The MIDPOINT+100 expression gives an error because CUST_CREDIT _LIMIT contains NULL valuesD. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expressionAnswer: A43.View the Exhibit and examine the structure of the ORD tableEvaluate the following SQL statements that are executed in a user session in the specified order.What would be the outcome of the above statements?A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST ID 101B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specifiedC. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specifiedD. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20Answer: A44.Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.Which query would give the required result?A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50AS "50% Credit Limit"FROM customers;B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50AS "50% Credit Limit"FROM customers;C. SELECT DISTINCT cust_income level || ' ' || cust_credit_limit * 0.50AS "50% Credit Limit"FROM customers;D. S ELECT cust income level || ' ' || cust_credit_limit * 0.50 AS "50% Credit Limit"ROM customers;Answer: C45.View the Exhibit and examine the structure of the CUSTOMERS tableEvaluate the query statement:What would be the outcome of the above statement?A. It executes successfullyB. It produces an error because the condition on CUST_LAST NAME is invalidC. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null valuesD. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clausesAnswer: A。

Oracle11g基础与应用教程期末总复习

第一章数据库技术基础1.2 数据模型数据模型按不同的应用层次分为3中模型:概念数据模型、逻辑数据模型、物理模型。

逻辑数据模型中比较成熟的种类有层次模型、网状模型、关系模型、对象模型。

数据模型的三大要素为：数据结构、数据操纵和完整性约束。

1.2.2 关系模型关系模式：R(U,D,F)关系数据模型的操作主要包括查询、插入、删除和修改数据。

关系的完整性约束条件包括三大类：实体完整性、参照完整性和用户自定义的完整性。

1.4.3 范式1NF:如果一个关系模式的所有属性都是不可分的基本数据项，则R为1NF。

2NF：若关系模式R为1NF，并且每一个非主属性都完全依赖于R的码，则R为2NF。

关系模式R不仅满足1NF ，且R 中只存在一个主码，所有非主属性都应该完全依赖于该主码。

3NF:如果关系模式R 为2NF ，X 是R 的候选，Y ，Z 是R 的非主属性组，如果不存在Y-Z ，亦即不存在属性是通过其他属性（组）传递依赖于码，则R 为3NF 。

图 1.17 关系模式规范化步骤1.5 数据库设计第二章初始Oracle数据库2.3 Oracle11g体系结构概述Oracle体系结构由内存结构、进程结构、存储结构组成。

2.3.1 存储结构存储结构分为逻辑存储结构和物理存储结构。

逻辑存储结构主要描述Oracle数据库的内部存储结构。

从逻辑上来看，数据库时由系统表空间、用户表空间等组成。

表空间是最大的逻辑单元，块是最小的逻辑单元。

从物理上看，数据库由控制文件、数据文件、重做日志文件等操作系统文件组成。

2.3.3 进程结构检查点进程（CKRT）的作用主要就是同步数据文件、日志文件和控制文件。

第四章 SQL语言基础4.1.3 HR示例方案中的表结构在用户连接到数据库后，就可以通过DESC命令查询各个表的结构。

4.2.3 功能1、数据定义（DDL），基本的DDL包括3类，即定义（CREATE）、修改(ALTER)和删除(DROP)。

ORCALE11G 期末试题及答案A

学年第一学期期末考试试题（卷）
专业：班级：姓名：学号：
装订线装订线以内不准作任何标记装订线
学院
课程考试参考答案与评分标准
学年第一学期
课程名称：ORCALE 考试性质：考查试卷类型：A
考试班级：计科考试方法：命题教师：
一、选择（每题2分，共30分）
DBBBA，DCCBD，CBCAB
二、判断（每题2分，共20分）
YNNYY，YYNNY
三：简答题（每题5分，共10分）
1、脏缓存块、空闲缓存块、命中缓存块
2、自动生成数据
强制复杂的完整性约束
自定义复杂的安全权限
提供审计和日志记录
启动复杂的业务逻辑
四、读程题（每题10分，共20分）
1、12,9,6,3
2、1的平方是1；2的平方是4；3的平方是9；4的平方是16；5的平方是25；五：（每题10分，共20分）
1．Create procedure sp_proc is
Begin
Dbms_output.putline(”hello world”);
End;
/
2、Create or replace trigger tg_md before
Update
On emp
For each row
Begin
Update DEPT
Set deptno=:new.deptno where deptno=:old.deptno;
End;。

Oracle-11g期末考试复习题

一、选择题1、对于Oracle数据库软件的应用，主要分为哪两大块（AB ）。

〖2个答案〗A.开发B.管理C.安装D.调试2、以下哪项不是Oracle数据库物理组件的类型（D）。

〖1个答案〗A. 数据文件B. 控制文件C. 日志文件D. 表空间3、以下哪项不是Oracle数据库的逻辑组件的类型（D）。

〖1个答案〗A. 表空间B. 段C. 扩展区D. 日志文件4、关于数据控制语言中，收回所授予的权限的语句是（B）。

〖1个答案〗A. CreateB. RevokeC. GrantD. Update5、Oracle数据库中，取余数的运算符是（B）。

〖1个答案〗A. %B. modC. /D. ||6、Oracle数据库中，连接字符串的运算符是（D）。

〖1个答案〗A. +B. &C. isD. ||7、Oracle数据库中返回字符串长度的函数是（D）。

〖1个答案〗A. SubstrB. ReplaceC. InStrD. Length8、Oracle数据库中按指定的精度进行四舍五入的函数是（C）。

〖1个答案〗A. SignB. TruncC. RoundD. Mod9、转换日期类型为字符串的转换函数是（C）。

〖1个答案〗A. to_dateB. to_datetimeC. to_charD. to_number10、在数据库中，对象Index表示（D）。

〖1个答案〗A.同义词B.表C.包D.索引11、关于通配符中的“%”，以下说法正确的两项是（BD）。

〖2个答案〗A. 代表任意一个字符，与Like结合使用B. 代表任意多个字符，与Like结合使用C. 代表任意一个字符，在Like后的表达式中只能使用一次“%”D. 代表任意多个字符，在Like后的表达式中可以使用多次“%”12、要统计某表中记录的总个数，以下哪项是正确的SQL语句（C）。

〖1个答案〗A. select Max(*) from 表名B. select Sum(*) from 表名C. select Count(*) from 表名D. select Min(*) from 表名13、要求表中数据的最大值，应使用什么函数（A）。

Oracle11g基础与应用教程期末总复习

第一章数据库技术基础1.2 数据模型数据模型按不同的应用层次分为3中模型:概念数据模型、逻辑数据模型、物理模型。

逻辑数据模型中比较成熟的种类有层次模型、网状模型、关系模型、对象模型。

数据模型的三大要素为：数据结构、数据操纵和完整性约束。

1.2.2 关系模型关系模式：R(U,D,F)关系数据模型的操作主要包括查询、插入、删除和修改数据。

关系的完整性约束条件包括三大类：实体完整性、参照完整性和用户自定义的完整性。

1.4.3 范式1NF:如果一个关系模式的所有属性都是不可分的基本数据项，则R为1NF。

2NF：若关系模式R为1NF，并且每一个非主属性都完全依赖于R的码，则R为2NF。

关系模式R不仅满足1NF，且R中只存在一个主码，所有非主属性都应该完全依赖于该主码。

3NF:如果关系模式R为2NF，X是R的候选，Y，Z是R的非主属性组，如果不存在Y-Z，亦即不存在属性是通过其他属性（组）传递依赖于码，则R为3NF。

图 1.17 关系模式规范化步骤1.5 数据库设计消除决定属性集非码的平凡函数依赖消除非主属性对码的部分函数依赖消除非主属性对码的传递函数依赖2NF1NF3NF第二章初始Oracle数据库2.3 Oracle11g体系结构概述Oracle体系结构由内存结构、进程结构、存储结构组成。

2.3.1 存储结构存储结构分为逻辑存储结构和物理存储结构。

逻辑存储结构主要描述Oracle数据库的内部存储结构。

从逻辑上来看，数据库时由系统表空间、用户表空间等组成。

表空间是最大的逻辑单元，块是最小的逻辑单元。

从物理上看，数据库由控制文件、数据文件、重做日志文件等操作系统文件组成。

2.3.3 进程结构检查点进程（CKRT）的作用主要就是同步数据文件、日志文件和控制文件。

第四章 SQL语言基础4.1.3 HR示例方案中的表结构在用户连接到数据库后，就可以通过DESC命令查询各个表的结构。

4.2.3 功能1、数据定义（DDL），基本的DDL包括3类，即定义（CREATE）、修改(ALTER)和删除(DROP)。

Oracle期末考试试题与答案

号学线名姓栏息信级订班级生业考专装系一、选择题：（本题共 20 个小题，每小题 2 分，共 40 分）1 当 Oracle 服务器启动时，下列哪种文件不是必须的( ) 。

A. 数据文件B. 控制文件C. 日志文件D. 归档日志文件2. 在 Oracle 中，一个用户拥有的所有数据库对象统称为( )。

A. 数据库B. 模式C. 表空间D. 实例3．在 Oracle 数据库的逻辑结构中有以下组件：1. 表空间2. 数据块3. 区4. 段这些组件从大到小依次是（）。

A. 1→2→3→4B.1→4→3→2C.1→3→2→4D. 4→1→3→24.下列哪个子句实现对一个结果集进行分组和汇总( )。

A.HAVINGB.ORDER BYC.WHERED.GROUP BY5. 在 Oracle 数据库中， ( )用户模式存储数据字典表和视图对象。

A. SYSB. SYSTEMC. SCOTTD. SYSDBA6.以下不属于命名的PL/SQL 块的是 ( )。

A ．程序包B．过程C．游标D．函数7.在 Oracle 中创建用户时，若未提及 DEFAULT TABLESPACE 关键字，则 Oracle 就将（）表空间分配给用户作为默认表空间。

A ．HR B． SCOTT C．SYSTEM D．SYS第 1 页共 6 页8．在 Windows 操作系统中， Oracle 的( )服务是使用 iSQL*Plus 必须的。

A ．OracleHOME_NAMETNSListener B. OracleServiceSIDC. OracleHOME_NAMEAgentD. OracleHOME_NAMEHTTPServer9.下列哪个语句可以在SQL*Plus 中直接调用一个过程 ( )。

A. RETURNB. CALLC. SETD. EXEC10．如果服务器进程无法在数据缓冲区中找到空闲块，为添加从数据文件中读出的数据块，则系统会启动如下哪一个进程 ( )A.CKPTB. DBWRC.LGWRD. SMON11.下列哪一个锁模式不属于Oracle( )。

oracle数据库期末考试试题及答案

oracle数据库期末考试试题及答案一、单选题（每题2分，共20分）1. Oracle数据库中，用于创建数据库表的命令是：A. CREATE DATABASEB. CREATE TABLEC. CREATE INDEXD. CREATE VIEW答案：B2. 在Oracle数据库中，以下哪个选项不是数据类型？A. NUMBERB. CHARC. DATED. FILE答案：D3. Oracle数据库中，用于删除表的命令是：A. DROP TABLEB. DELETE TABLEC. REMOVE TABLED. REMOVE答案：A4. 在Oracle数据库中，以下哪个命令用于查询表中的数据？A. SELECTB. INSERTC. UPDATED. DELETE答案：A5. Oracle数据库中，以下哪个选项不是SQL语句的组成部分？A. SELECTB. FROMC. WHERED. PRINT答案：D6. 在Oracle数据库中，以下哪个选项不是事务控制命令？A. COMMITB. ROLLBACKC. SAVEPOINTD. PRINT答案：D7. Oracle数据库中，以下哪个选项不是索引类型？A. B-treeB. BitmapC. HashD. List答案：D8. 在Oracle数据库中，以下哪个选项不是表约束类型？A. PRIMARY KEYB. FOREIGN KEYC. UNIQUED. CHECK答案：D9. Oracle数据库中，以下哪个命令用于添加表列？A. ADD COLUMNB. ALTER TABLEC. CREATE COLUMND. MODIFY COLUMN答案：B10. 在Oracle数据库中，以下哪个选项不是PL/SQL的组成部分？A. DECLAREB. BEGINC. ENDD. PRINT答案：D二、多选题（每题3分，共15分）1. Oracle数据库中，以下哪些选项是数据类型？A. VARCHAR2B. TIMESTAMPC. BOOLEAND. BLOB答案：A, B, D2. 在Oracle数据库中，以下哪些命令用于数据操纵？A. SELECTB. INSERTC. UPDATED. DELETE答案：B, C, D3. Oracle数据库中，以下哪些选项是表约束类型？A. PRIMARY KEYB. FOREIGN KEYC. CHECKD. UNIQUE答案：A, B, C, D4. 在Oracle数据库中，以下哪些选项是事务控制命令？A. COMMITB. ROLLBACKC. SAVEPOINTD. PRINT答案：A, B, C5. Oracle数据库中，以下哪些选项是索引类型？A. B-treeB. BitmapC. HashD. List答案：A, B, C三、简答题（每题5分，共20分）1. 请简述Oracle数据库中事务的概念及其重要性。

ORACLE期末考试试题及答案

ORACLE期末考试试题及答案一、选择题（每题2分，共40分）1. ORACLE数据库系统属于以下哪一种数据库类型？A. 关系型数据库B. 层次型数据库C. 网状型数据库D. 对象-关系型数据库答案：A2. 在ORACLE中，以下哪个命令用来创建一个表？A. CREATE TABLEB. CREATE VIEWC. CREATE INDEXD. CREATE SEQUENCE答案：A3. 数据库的三级模式结构包括以下哪几个层次？A. 外模式、概念模式、内模式B. 概念模式、内模式、物理模式C. 外模式、内模式、物理模式D. 外模式、概念模式、物理模式答案：A4. 在ORACLE中，以下哪个命令用来删除一个表？A. DROP TABLEB. DELETE TABLEC. DROP VIEWD. DELETE VIEW答案：A5. 数据库事务的四个特性包括以下哪些？A. 原子性、一致性、隔离性、持久性B. 原子性、一致性、独立性、持久性C. 原子性、一致性、并发性、持久性D. 原子性、一致性、并发性、独立性答案：A6. 在ORACLE中，以下哪个命令用来修改表的结构？A. ALTER TABLEB. MODIFY TABLEC. UPDATE TABLED. ALTER VIEW答案：A7. 数据库的备份分为以下哪几种类型？A. 冷备份、热备份、逻辑备份B. 冷备份、热备份、物理备份C. 冷备份、热备份、完全备份D. 冷备份、热备份、增量备份答案：D8. 在ORACLE中，以下哪个命令用来创建一个索引？A. CREATE INDEXB. CREATE TABLEC. CREATE VIEWD. CREATE SEQUENCE答案：A9. 数据库的并发控制主要包括以下哪些方法？A. 乐观并发控制、悲观并发控制、时间戳并发控制B. 乐观并发控制、悲观并发控制、锁并发控制C. 乐观并发控制、悲观并发控制、令牌并发控制D. 乐观并发控制、悲观并发控制、版本并发控制答案：B10. 在ORACLE中，以下哪个命令用来创建一个序列？A. CREATE SEQUENCEB. CREATE TABLEC. CREATE VIEWD. CREATE INDEX答案：A二、填空题（每题2分，共20分）1. 在ORACLE中，使用______命令可以创建一个表。

Oracle11g考试题B卷及答案

北京工商管理学院2011-2012学年第二学期期末考试试卷B卷《Oracle11g》课程__级:_______专业学生姓名__________学号：_______成绩_____________一、单项选择题 (本大题共10小题，每小题2分，共20分) 在每小题列出的四个选项中只有一个是符合题目要求的，请将其代码填在题后的括号内。

错选或未选均无分。

1. 单个用户使用的数据视图的描述称为【】A. 外模式B. 概念模式C. 内模式D. 存储模式2. 子模式DDL用来描述【】A. 数据库的总体逻辑结构B. 数据库的局部逻辑结构C. 数据库的物理存储结构D. 数据库的概念结构3. 在DBS中，DBMS和OS之间的关系是【】A. 相互调用B. DBMS调用OSC. OS调用DBMSD. 并发运行4．SGA区中包的存储结构有【】A．相容性B．独立性C．大型池D．独立区5. 正确的五种基本关系代数运算是【】A. ∪，-，×，π和σB. ∪，-，∞，π和σC. ∪，*，×，π和σD. ∪，∩，∞，π和σ6. 下列聚合函数中不忽略空值 (null) 的是【】A. SUM (列名)B. MAX (列名)C. COUNT ( * )D. AVG (列名)7．实现关系代数投影运算的SQL子句是【】A．SELECT B．ORDER BYC．FROM D．WHERE8. 在数据库设计中，将ER图转换成关系数据模型的过程属于【】A. 需求分析阶段B. 逻辑设计阶段C. 概念设计阶段D. 物理设计阶段9. 在数据库设计中，创建数据库的命令式【】A. CREATE DATABASEB. CREATE TABLEC. CREATE VIEWD. CREATE RULE10. 打开数据库后，若想更改数据库的状态的尾装载状态，正确的语句是【】A. ALTER DATABASET MOUNTB. ALTER SYSTEM MOUNTC. SHUTDOWN IMMEDIATE; STARTUP MOUNTD. SHOTDOWN ABORT; STARTUP FORCE二：名词解释（本大题共5小题，每小题2分，共10分，）11. 第二范式12. 共享服务器13. 永久表空间14. 回滚记录15.事物三、填空题 (本大题共10小题，每小题1分，共10分，将正确的答案写在每小题的空格内。