没有合适的资源？快使用搜索试试~ 我知道了~

首页11g OCP题库：SQL基础教程与考试策略

11g OCP题库：SQL基础教程与考试策略

需积分: 31 1 下载量 33 浏览量更新于2024-07-26 收藏 811KB PDF 举报

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

领优惠券(最高得80元）

试读

99页

"Oracle Certified Professional (OCP) 11g 题库中的SQL基础部分提供了一个关于创建新表的示例和考察。题目涉及到了数据库设计和SQL语法的理解。在这个特定的试题中，考生被要求评估一个CREATE TABLE命令，该命令试图创建一个新的表（new_sales）并基于sales表中的prod_id, cust_id, 和 time_id列，同时为order_date列设置默认值为SYSDATE。题目详细描述了四个选项： A. 这个选项认为命令不会创建新的new_sales表，因为不能在列定义中指定默认值。这是不正确的，因为Oracle 11g允许在CREATE TABLE语句中为列指定默认值。 B. 选项B认为新的新_sales表会成功创建，并且所有指定列的NOT NULL约束会被传递到新表中。这也不对，因为默认值并不会自动将原有表的约束转移到新表上。默认值只是在列没有提供具体值时使用的预设值。 C. 这个选项指出新表不会创建，原因是CREATE TABLE命令中的列名问题。但根据提供的信息，这个问题没有提及，所以这个选项可能基于其他未提供的上下文判断，但仅凭给出的信息，无法断定。正确答案可能是B，因为在SQL中，CREATE TABLE命令可以创建新表并继承源表的部分属性，包括数据类型和约束条件，但默认值不会自动携带NOT NULL约束。然而，如果没有进一步的上下文说明或测试环境的具体规则，这仍然只能作为推测。通过这个题目，考生需要掌握Oracle 11g中关于创建表、默认值和约束传递的知识，理解如何正确处理默认值和约束的设定，以及它们在数据迁移或子集创建过程中的作用。这对于理解和准备OCP考试至关重要，因为这类题目旨在测试对SQL语言核心概念的实际应用能力。"

资源详情

资源推荐

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:

The safer ,

easier way to help you pass any IT exams.

1) The order ID will be unique and cannot have null values.

2) The order date cannot have null values and the default should be the current date.

3) The order amount should not be less than 50.

4) The order status will have va

lues either shipped or not shipped.

5) The order payment mode should be cheque, credit card, or cash on delivery (COD).

Which is the valid DDL statement for creating the ORD_DETAIL table?

A. CREATE TABLE ord_details

(ord_id NUMBER(2) CONSTRAINT ord_id_n

n NOT NULL,

ord_date DATE DEFAULT SYSDATE NOT NULL,

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_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')));

B. CREATE TABLE ord_details

(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,

ord_da

te DATE DEFAULT SYSDATE NOT NULL,

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_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')));

C. CREATE TABLE ord_details

(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,

ord_date DATE DEFAULT SYSDATE N

OT NULL,

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_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

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:

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.

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

剩余98页未读，继续阅读

粉丝: 1
资源: 3

上传资源快速赚钱

我的内容管理展开

我的资源快来上传第一个资源

我的收益

登录查看自己的收益

我的积分登录查看自己的积分

我的C币登录后查看C币余额

我的收藏

我的下载

下载帮助

11g OCP题库：SQL基础教程与考试策略

oracle 11g ocp题库

Oracle11g OCP题库.zip

Oracle 11g OCP题库 O51 052 053 pdf版

mysql8.0 ocp题库

mysql5.7 ocp题库

老哥讲数据库mysql ocp 8.0 1z0-908题库

mysql 8 ocp 1z0-908 题库

联瑞25G网卡iperf测速

ocp3.0 pcie 区别

ocp nic的main power domain

ocp考试071,062,063

oracle ocp

Oracle的OCP认证

easyopc_da ocp server

如何将ocp镜像密钥复制到${OCP_PATH}/secret/redhat-secret.json

ocp mysql几科

OCA/OCP/OCM

ocp 3.0 接口定义

oracle 9i dba ocp

verilog中，OCP接口是什么

最新资源