Oracle 11g OCP考试1Z0-051：SQL基础解析

oraccle

需积分: 13 177 浏览量更新于2024-07-18 收藏 1.64MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"Oracle 11g OCP 051考试详细解析，涵盖175道题目，基于个人考试经验和理解进行翻译分享。" 在Oracle数据库管理中，OCP（Oracle Certified Professional）认证是对数据库管理员专业技能的认可。Oracle 11g OCP考试051是其中的一部分，主要测试考生在Oracle Database SQL基础方面的能力。这里我们将深入探讨描述中提及的SQL基础知识，特别是涉及外键和创建表的语句。在Oracle数据库中，外键（Foreign Key）是用来维护两个表之间关系的关键字段。例如，在描述中提到的表格结构中，`SALES`表中的`PROD_ID`是外键，它引用了`PRODUCTS`表的主键；`CUST_ID`和`TIME_ID`也是外键，分别对应`CUSTOMERS`和`TIMES`表。这种设计确保了数据的完整性和一致性，因为通过外键，可以限制对关联表的更新和删除操作，防止出现不一致的数据状态。接下来，我们分析了给定的`CREATE TABLE`命令： ```sql CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE) AS SELECT prod_id, cust_id, time_id FROM sales; ``` 这个命令的目的是创建一个新的`NEW_SALES`表，其结构基于`SALES`表，但只选择了`prod_id`, `cust_id`两列，并为`order_date`列设置了默认值为当前系统日期（`SYSDATE`）。关于这个命令的陈述，正确的是： B. `NEW_SALES`表将被创建，所有在指定列上定义的非空约束（NOT NULL constraints）都会被传递到新表。这个选项是正确的，因为`CREATE TABLE AS SELECT`语句会复制源表中列的定义，包括约束条件。尽管在创建`NEW_SALES`表时，`order_date`列没有明确声明非空约束，但由于在`SALES`表中，如果`time_id`是非空的，那么这个属性会被继承。默认值`DEFAULT SYSDATE`允许`order_date`列在插入时如果没有提供值，自动填充当前系统日期。在实际的Oracle 11g OCP考试中，考生需要理解如何处理复杂的表结构，以及如何使用SQL语句来创建、修改和查询数据。此外，外键的概念和使用，以及如何通过`CREATE TABLE AS SELECT`创建新表，都是重要的考点。熟悉这些概念和操作对于通过考试和日常数据库管理至关重要。

资源详情

资源推荐

The safer ,

easier way to help you pass any IT exams.

expiry_date date CHECK (expiry_date > SYSDATE),

CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),

CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));

The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that

apply.)

A. You cannot use SYSDATE in the condition of a CHECK constraint.

B. You

cannot use the BETWEEN clause in the condition of a CHECK constraint.

C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is

also the FOREIGN

KEY.

Answer:

2. Which CREATE TABLE statement is valid?

A. CREATE TABLE ord_details

(ord_no NUMBER(2) PRIMARY KEY,

item_no NUMBER(3) PRIMARY KEY,

ord_date DATE NOT NULL);

B. CREATE TABLE ord_details

(ord_no NUMBER(2) UNIQUE, NOT NULL,

item_no NUMBER(3),

ord_date DATE DEFAULT SYSDATE NOT NULL);

C. CREATE TABLE ord_details

(ord_no NUMBER(2) ,

item_no NUMBER(3),

ord_date DATE DEFAULT NOT NULL,

CONSTRAINT ord_uq UNIQUE (ord_no),

CONSTRAINT ord_pk PRIMARY KEY (ord_no));

D. CREA

TE TABLE ord_details

(ord_no NUMBER(2),

item_no NUMBER(3),

ord_date DATE DEFAULT SYSDATE NOT NULL,

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer:

33. You want to create an ORD_DETAIL table to store details for an order placed having the fo

llowing

business requirement:

D、外键约束和字段可以和本表组合成主键，D错。

A选项不正确，因为一个表只能有一个主键。

B选项不正确，因为ord_no NUMBER(2) UNIQUE, NOT NULL

（UNIQUE与NOT NULL中间不应该加逗号）。

C选项不正确，因为ord_date DATE DEFAULT NOT NULL没有指定

默认值，并且ord_no列上不能同时添加唯一约束与主键约束，因

为主键约束其实就是唯一约束和非空约束的组合，但是非空约束

可以和主键约束一起创建

sys@TEST0910> CREATE TABLE ord_details

2 (ord_no NUMBER(2) ,

3 tem_no NUMBER(3),

4 ord_date DATE DEFAULT sysdate NOT NULL,

5 CONSTRAINT ord_uq UNIQUE (ord_no),

6 CONSTRAINT ord_pk PRIMARY KEY (ord_no));

CONSTRAINT ord_uq UNIQUE (ord_no),

ERROR at line 5:

ORA-02261: such unique or primary key already exists in

the table

The safer ,

easier way to help you pass any IT exams.

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',

'Cash On Delivery')));

D. CREATE TABLE ord_details

(ord_id NUMBER(2),

ord_date DATE NOT NULL DEFAULT SYSDATE,

ord_amount NUMBER(5, 2) CONSTRAINT ord_amoun

t_min

CHECK (ord_amount >= 50),

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk

CHECK (ord_status IN ('Shipped', 'Not Shipped')),

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',

'Cash On Delivery')));

Answer:

4. You created an ORDERS table with the following description:

name Null Type

ORD_ID NOT NULL NUMBER(2)

CUST_ID NOT NULL NUMBER(3)

ORD_DATE NOT NULL DATE

ORD_AMOUNT NOT NULL NUMBER (10,2)

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 column.

B. You cannot add a primary key constraint if data exists in the column.

C. The primary key constraint can be created only at the time of table creation .

D. You can add the primary key constraint even if data exists, provided that there are no duplicate

values.

Answer:

5. Which two statements are true regarding constraints? (Choose two.)

A. A table can have only one primary key and one foreign key.

B. A table can have only one prim

ary key but multiple foreign keys.

C. Only the primary key can be defined at the column and table levels.

D. The foreign key and parent table primary key must have the same name.

E. Both primary key and foreign key constraints can be defined at both column

and table levels.

Answer:

A. 一个列上不能有两个约束,错误，例如非空+唯一

可以作用在一个列上。

B. 如果数据已经存在不能添加主键约束，错误，有

数据存在，也可以插入主键约束，前提是数据没有重

复

C. 主键约束只有在表创建时才能被创建，错误。有

了数据之后一样可以增加，不一定只有在创建的时候

加

D. 即使数据存在，如果没有重复值，你能添加主键

约束，正确。

实验验证，有数据是否可以加主键约束。

A选项不正确，因为一个表只能有一个主

键，但可以有多个外键。

C选项不正确，因为除了NOT NULL只有被定

义成列级，其它的约束都可以被定义成列级

或表级。

D选项不正确，因为外键和父表主键名字可

以不同，但是数据类型必须一致

The safer ,

easier way to help you pass any IT exams.

36. Examine the following SQL commands:

SQL>CREATE TABLE products (

prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0),

prod_name CHAR(30),

prod_qty NUMBER(6),

CONSTRAINT p_name NOT NULL,

CONSTRAINT prod_pk PRIMARY KEY (pr

od_id));

SQL>CREATE TABLE warehouse (

warehouse_id NUMBER(4),

roomno NUMBER(10) CONSTRAINT r_id CHECK(roomno BETWEEN 101 AND 200),

location VARCHAR2(25),

prod_id NUMBER(3),

CONSTRAINT wr_pr_pk PRIMARY KEY (warehouse_id,prod_id),

CONSTRAINT prod_fk FOREIGN

KEY (prod_id) REFERENCES products(prod_id));

Which statement is true regarding the execution of the above SQL commands?

A. Both commands execute successfully.

B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.

The second CREATE TABLE command generates an error because the CHECK constraint is not valid.

D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY

constraints cannot be used for the same column.

E. The first CREATE TABLE comm

and generates an error because the column PROD_ID cannot be used

in the PRIMARY KEY and FOREIGN KEY constraints.

Answer:

7. You issued the following command to drop the PRODUCTS table:

SQL> DROP TABLE products;

What is the implication of this command? (

Choose all that apply.)

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

B. The pending transaction in the session is committed.

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

D. All views and synonyms will remain but they are

invalidated.

E. All data in the table are deleted but the table structure will remain.

Answer:

ABD

8. Which two statements are true regarding views? (Choose two.)

A. A simple view in which column aliases have been used cannot be updated.

第一个命令报错，因为CONSTRAINT p_name NOT NULL

不能定义为表级，第一个命令里的CHECK和主键约束

能用在相同的列上。

sh@TESTDB> create table pro

2 (prod_id number,

3 prod_name char(30),

4 constraint p_name not null);

constraint p_name not null)

ERROR at line 4:

ORA-00904: : invalid identifier

A错误，因为第一个报错。

B正确，NOT NULL不能定义为表级，只能定义为列级

C错误，between可以用在check约束里

D错误，check和主键约束可以用在同一列

E错误，可以把主键作为其他表的外键

A正确，表结构以及表中所有数据都删除。

B正确，提交所有待定的事务处理。

C错误，删除表后，在数据库中会失去表的所有数据以

及与表关联的所有索引。

D正确，保留视图和同义词，但不再有效。

E错误，表结构以及表中所有数据都删除。

The safer ,

easier way to help you pass any IT exams.

B. Rows cann

ot be deleted through a view if the view definition contains the DISTINCT keyword.

C. Rows added through a view are deleted from the table automatically when the view is dropped.

D. The OR REPLACE option is used to change the definition of an existing view without dropping and

creating it.

E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns

displayed through the view.

Answer:

9. Evaluate the following command:

CREATE TABLE employees

(employee_id NUMBER(2)

PRIMARY KEY,

last_name VARCHAR2(25) NOT NULL,

department_id NUMBER(2)NOT NULL,

job_id VARCHAR2(8),

salary

NUMBER(10,2));

You issue the following command to create a view that displays the IDs and last names of the sales staff

the organization:

CREATE OR REPLACE VIEW sales_staff_vu AS4 ? 4;

SELECT employee_id, last_name,job_id4 ? 4;

FROM employees4 ? 4;

WHERE job_id LIKE 'SA_%'4 ? 4;

WITH CHECK OPTION;

Which two statements are true regarding the above view? (Cho

ose two.)

A. It allows you to insert rows into the EMPLOYEES table .

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table.

C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table.

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in

multitable INSERT statements.

Answer:

0. View the Exhibit to examine the description for the SALES and PRODUCTS tables.

You want to crea

te a SALE_PROD view by executing the following SQL statement:

CREATE VIEW sale_prod

AS SELECT p.prod_id, cust_id, SUM(quantity_sold) "Quantity" , SUM(prod_list_price) "Price"

FROM products p, sales s

WHERE p.prod_id=s.prod_id

A错误，简单视图可以执行DML操作。

B正确，包含DISTINCT关键字不能被修改。

C错误，删除视图不会影响视图的基表。

D正确，使用OR REPLACE选项，可以创建一个视图，甚至可以创建一个与已存在的

视图同名的视图，以便替换旧版本的视图。这意味着可以更改视图，而不必经过删

除、重新创建对象和重新授予对象权限的过程。

E错误，WITH CHECK OPTION子句指定通过视图执行的INSERT和UPDATE不能创建该视

图无法选择的行。因此，能够对要插入或更新的数据强制执行完整性约束条件和数

据验证检查。而不是限制显示的值。

A错误，因为employees表的department_id有非空约束，如果通

过视图插入，则department_id列则为空，会报错。

B正确，可以通过视图来删除employees任意行。

C错误，因为有WITH CHECK OPTION，是根据job_id LIKE 'SA_%'

来创建的视图，所以不能更改为不是SA_开头的，但是可以更新

为SA_开头的。

D错误，也是因为department_id有非空约束，如果通过视图插

入，则department_id列则为空，会报错。

但是可以通过multitable INSERT在视图上插入，所有官方文档

有错误？

剩余98页未读，继续阅读

JiekeXu

粉丝: 911
资源: 3

Oracle 11g OCP考试1Z0-051：SQL基础解析

Oracle 11g OCP 051/052 题库

最新OCP-051-VCE模拟

Oracle 11g OCP试题VCE 051 052 053

oracle数据库ocp考试

oracle 9i dba ocp

Oracle的OCP认证

ocp考试071,062,063

oracle 19c ocp全程课堂笔记

oracle ocp

oracle19c ocp官方培训教材

oracle ocp报名费

oracle ocp 知识点

oracle 12c OCP

oracle ocp 19c

oracle数据库考试

mysql ocp报名

ocp 19c学习资料

OCA/OCP/OCM

mysql8.0ocp

mysql ocp 多少分合格

最新资源