Oracle OCP 1Z0-051题库：SQL命令创建带默认值的新表

需积分: 10 87 浏览量更新于2024-07-18 收藏 948KB PDF 举报

Oracle Certified Professional (OCP) 1Z0-051 题库是一份针对Oracle数据库管理员的专业认证考试资料，包含了193道不重复的题目。本题库重点关注数据库设计和管理，特别是在处理外键关系时的SQL语句理解和操作。在提供的部分问题中，第1题考察了关于创建新表（new_sales）的SQL命令的理解。在该问题中，命令试图通过子查询将SALES表的部分列（prod_id, cust_id, time_id）复制到新表中，并为order_date列设置默认值SYSDATE。选项A认为无法创建新表，因为不允许在列定义中指定默认值，这是错误的，因为SQL确实允许在CREATE TABLE语句中为列设置默认值。选项B是正确答案，表示新表new_sales将被创建，并且所有指定列上的NOT NULL约束也会被传递到新表中，这符合SQL语句的逻辑。选项C提到列名不匹配不是创建新表的问题，如果命令结构正确，这不是问题；选项D提到了外键约束，虽然在示例中没有明确提及是否涉及外键，但新表的外键约束通常会随数据一起迁移，如果这些列是外键。第2题的描述部分可能涉及到数据库表的其他方面，比如表的字段描述、索引、约束或存储特性等。这部分内容可能询问考生如何正确解释或处理表的元数据，例如查看某个字段的描述或者理解如何确保数据一致性，比如检查约束的完整性。这部分内容通常会测试考生对数据库设计原则和SQL语句执行的理解，包括对DDL（数据定义语言）的运用，如ALTER TABLE和CREATE TABLE AS SELECT。总结来说，Oracle OCP 1Z0-051 题库涵盖了丰富的知识，不仅包含基础的SQL语法，还包括数据库设计、索引优化、事务管理、安全性、备份与恢复等多个方面。学习者可以通过这份题库深入了解Oracle数据库的管理和维护，为实际的OCP认证考试做好充分准备。在解题过程中，理解并掌握SQL语句的最佳实践、约束的作用以及数据库设计的最佳实践是关键。

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: D

Question No: 35

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 primary 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: B,E

Question No: 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 (prod_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.

C. 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 command generates an error because the column PROD_ID cannot be used in the

PRIMARY KEY and FOREIGN KEY constraints.

Answer: B

Question No: 37

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: A,B,D

Question No: 38

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

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

B. Rows cannot 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

recreating it.

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

through the view.

Answer: B,D

Question No: 39

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 in the

organization:

CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name, job_id FROM

employees WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;

Which two statements are true regarding the above view? (Choose 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: B,D

Question No: 40

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:

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

GROUP BY p.prod_id, cust_id;

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

D. The view will not be created because the GROUP BY clause is not allowed for creating a view.

Answer: B

B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for

the sequence have not been specified.

C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the

increment value have not been specified.

D. 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: A

Question No: 45

Which two statements are true about sequences created in a single instance database? (Choose two.)

A. The numbers generated by a sequence can be used only for one table.

B. DELETE <sequencename> would remove a sequence from the database.

C. CURRVAL is used to refer to the last sequence number that has been generated.

D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by

using the ALTER SEQUENCE statement.

E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used

would be available once again when the database instance is restarted.

Answer: C,D

Question No: 46

Which statements are correct regarding indexes? (Choose all that apply.)

A. When a table is dropped, the corresponding indexes are automatically dropped.

B. A FOREIGN KEY constraint on a column in a table automatically creates a nonunique index.

C. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique

index.

D. For each data manipulation language (DML) operation performed, the corresponding indexes are

automatically updated.

Answer: A,C,D

Question No: 47

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_idx1 ON ord(ord_no);

B. CREATE INDEX ord_idx2 ON ord_items(ord_no);

C. CREATE INDEX ord_idx3 ON ord_items(item_no);

D. CREATE INDEX ord_idx4 ON ord, ord_items(ord_no, ord_date, qty);

Answer: B,C

剩余71页未读，继续阅读

KlarKent

粉丝: 4
资源: 25

Oracle OCP 1Z0-051题库：SQL命令创建带默认值的新表

Oracle 1z0-051题库OCP考试题

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

mysql 8 ocp 1z0-908 题库

mysql8.0 ocp题库

oracle ocp 知识点

mysql5.7 ocp题库

oracle ocp 19c

oracle ocp报名费

oracle ocp

oracle 12c OCP

最新资源