百度360必应搜狗淘宝本站头条
当前位置:网站首页 > 技术教程 > 正文

Oracle Database 12c SQL OCA/OCP 1Z0-071题库(11-15题)

mhr18 2024-09-23 09:43 21 浏览 0 评论

QUESTION 11

You issue the following command to drop the PRODUCTS table:

SQL > DROP TABLE products;

Which three statements are true about the implication of this command? (Choose three.)

A. All data along with the table structure is deleted.

B. A pending transaction in the session is committed.

C. All indexes on the table remain but they are invalidated.

D. All views and synonyms on the table remain but they are invalidated.

E. All data in the table is deleted but the table structure remains.

答案:ABD

解析:DROP table 注意点:

  1. 删除数据以及表结构,作为DDL不可回滚,但是可以恢复(DROP table Purge 不可恢复);
  2. 有约束限制,可增加CASCADE CINSTRAINTS;
  3. 索引被删除;
  4. 视图和别名保留,但是无效。

QUESTION 12

You execute the following commands:

SQL > DEFINE hiredate = '01-APR-2011';

  SQL >SELECT 	employee_id, first_name, salary
					FROM	employees
					WHERE hire_date > '&hiredate'
					AND manager_id > &mgr_id;

For which substitution variables are you prompted for the input?

A. none, because no input required

B. both the substitution variables ''hiredate' and 'mgr_id'.

C. only hiredate'

D. only 'mgr_id'

答案:D

解析:&符号替换变量是一项SQL*PLus功能,注意点:

  1. 使用 DEFINE 语句给替换变量的赋值, 可用UNDEFINE 语句取消赋值;
  2. SET VARIFY /SET DEFINE来改变系统的默认值,这两个参数的默认值是 ON和&

QUESTION 13

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.

ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

A. DELETE orders o, order_items i
    WHERE o.order_id = i.order_id;
B. DELETE
    FROM orders
    WHERE (SELECT order_id
    FROM order_items);
C. DELETE orders
    WHERE order_total < 1000;
D. DELETE order_id
    FROM orders
    WHERE order_total < 1000;

答案:C

解析:DELETE语句用来从数据库的表中删除行,注意点:

  1. DELETE FROM table 中的FROM为可选字;
  2. WHERE子句 为可选字,如省略,则删除所有行。

QUESTION 14

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"

FROM customers;
B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"

FROM customers;
C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"

FROM customers;
D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"

FROM customers;

答案:A

解析:NVL函数注意点:

  1. NVL(exp1,exp2):如果exp1为NULL,则返回exp2,如果exp1非空,则返回原值exp1;
  2. NVL2(exp1,expr2,exp3):如果exp1为NULL,则返回exp3,如果exp1非空,则返回exp2。

QUESTION 15

View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

Name                          NULL?                 Type

------------------              --------------         ----------------
EMPLOYEE_ID              NOT NULL           NUMBER(6)
FIRST_NAME                                           VARCHAR(20)
LAST_NAME                NOT NULL           VARCHAR(25)
HIRE_DATA                  NOT NULL           DATE          
JOB_ID                         NOT NULL            VARCHAR2(10)
SALARY                                                    NUMBER(10,2)
COMMISSION                                         NUMBER(6,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                   NUMBER(4)

You want to update EMPLOYEES table as follows:

  • Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
  • Set department_id for these employees to the department_id corresponding to London (location_id 2100).
  • Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
  • Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

You issue the following command:

SQL > UPDATE  employees
									SET   department_id  =
                          (SELECT department_id
                               FROM departments
                               WHERE location_id =2100),
                          (salary,commission) =
                          (SELECT 1.1*AVG(salary),1.5*AVG(commission))
          									FROM employees ,departments
                            WHERE departments.location_id IN (2900,2700,2100))
                  WHERE department_ID IN
                               (SELECT department_id 
                                  FROM departments 
            										WHERE location_id =2900
            																	or location_id =2700)

What is outcome?

A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.

B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.

C. It executes successfully and gives the desired update

D. It executes successfully but does not give the desired update

答案:D

解析:WHERE子句有问题,WHERE departments.location_id IN (2900,2700,2100)。


后续陆续更新,转载请注明出处。

本人水平有限,欢迎指正

相关推荐

【预警通报】关于WebLogic存在远程代码执行高危漏洞的预警通报

近日,Oracle官方发布了2021年1月关键补丁更新公告CPU(CriticalPatchUpdate),共修复了包括CVE-2021-2109(WeblogicServer远程代码执行漏洞)...

医院信息系统突发应急演练记录(医院信息化应急演练)

信息系统突发事件应急预案演练记录演练内容信息系统突发事件应急预案演练参与人员信息科参与科室:全院各部门日期xxxx-xx-xx时间20:00至24:00地点信息科记录:xxx1、...

一文掌握怎么利用Shell+Python实现完美版的多数据源备份程序

简介:在当今数字化时代,无论是企业还是个人,数据的安全性和业务的连续性都是至关重要的。数据一旦丢失,可能会造成无法估量的损失。因此,如何有效地对分布在不同位置的数据进行备份,尤其是异地备份,成为了一个...

docker搭建系统环境(docker搭建centos)

Docker安装(CentOS7)1.卸载旧版Docker#检查已安装版本yumlistinstalled|grepdocker#卸载旧版本yumremove-ydocker.x...

基础篇:数据库 SQL 入门教程(sql数据库入门书籍推荐)

SQL介绍什么是SQLSQL指结构化查询语言,是用于访问和处理数据库的标准的计算机语言。它使我们有能力访问数据库,可与多种数据库程序协同工作,如MSAccess、DB2、Informix、M...

Java21杀手级新特性!3行代码性能翻倍

导语某券商系统用这招,交易延迟从12ms降到0.8ms!本文揭秘Oracle官方未公开的Record模式匹配+虚拟线程深度优化+向量API神操作,代码量直降70%!一、Record模式匹配(代码量↓8...

一文读懂JDK21的虚拟线程(java虚拟线程)

概述JDK21已于2023年9月19日发布,作为Oracle标准Java实现的一个LTS版本发布,发布了15想新特性,其中虚拟线程呼声较高。虚拟线程是JDK21中引入的一项重要特性,它是一种轻量级的...

效率!MacOS下超级好用的Linux虚拟工具:Lima

对于MacOS用户来说,搭建Linux虚拟环境一直是件让人头疼的事。无论是VirtualBox还是商业的VMware,都显得过于笨重且配置复杂。今天,我们要介绍一个轻巧方便的纯命令行Linux虚拟工具...

所谓SaaS(所谓三维目标一般都应包括)

2010年前后,一个科技媒体的主编写一些关于云计算的概念性问题,就可以作为头版头条了。那时候的云计算,更多的还停留在一些概念性的问题上。而基于云计算而生的SaaS更是“养在深闺人未识”,一度成为被IT...

ORA-00600 「25027」 「x」报错(报错0xc0000001)

问题现象:在用到LOB大对象的业务中,进行数据的插入,失败了,在报警文件中报错:ORA-00600:内部错误代码,参数:[25027],[10],[0],[],[],[],[],[...

安卓7源码编译(安卓源码编译环境lunch失败,uname命令找不到)

前面已经下载好源码了,接下来是下载手机对应的二进制驱动执行编译源码命令下载厂商驱动https://developers.google.com/android/drivers?hl=zh-cn搜索NGI...

编译安卓源码(编译安卓源码 电脑配置)

前面已经下载好源码了,接下来是下载手机对应的二进制驱动执行编译源码命令下载厂商驱动https://developers.google.com/android/drivers?hl=zh-cn搜索NGI...

360 Vulcan Team首战告捷 以17.5万美金强势领跑2019“天府杯“

2019年11月16日,由360集团、百度、腾讯、阿里巴巴、清华大学与中科院等多家企业和研究机构在成都联合主办了2019“天府杯”国际网络安全大赛暨2019天府国际网络安全高峰论坛。而开幕当日最激荡人...

Syslog 日志分析与异常检测技巧(syslog发送日志配置)

系统日志包含有助于分析网络设备整体运行状况的重要信息。然而,理解并从中提取有效数据往往颇具挑战。本文将详解从基础命令行工具到专业日志管理软件的全流程分析技巧,助你高效挖掘Syslog日志价值。Gr...

从Oracle演进看数据库技术的发展(从oracle演进看数据库技术的发展的过程)

数据库技术发展本质上是应用需求驱动与基础架构演进的双向奔赴,如何分析其技术发展的脉络和方向?考虑到oracle数据库仍然是这个领域的王者,以其为例,管中窥豹,对其从Oracle8i到23ai版本的核...

取消回复欢迎 发表评论: