1Z1-047問題集、Oracle実際の試験問題

質問 1

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

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

B. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

C. For each DML operation performed, the corresponding indexes are automatically updated.

D. Indexes should be created on columns that are frequently referenced as part of an expression.

正解: A,B,C

質問 2

View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id FROM ORDERS
WHERE order_total = 1000 WITH CHECK OPTION)
VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?

A. It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.

B. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.

C. It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.

D. It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.

正解: B

質問 3

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an
index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)

A. The FIRST_NAME column would be dropped provided it does not contain any data.

B. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.

C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.

D. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.

正解: B,D

質問 4

View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or
1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)

A. DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1));

B. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));

C. DELETE FROM (SELECT* FROM order_items i.orders o
WHERE i.order_id = o.order_id AND order_status IN (0,1));

D. DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o
WHERE i. order_id = o. order_id AND
order_status IN (0,1));

正解: B,C,D

質問 5

View the Exhibit and examine the data in EMPLOYEES and
DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and
DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the
PRIMARY KEY.
Evaluate the following UPDATE statement:
UPDATE employees a
SET department_jd =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a. department_jd = b. department_id)
WHERE first_name|| '||last_name = 'Amit Banda';
What would be the outcome of the above statement?

A. It would not execute successfully because there is no LOCATION_ID 2100 in the DEPARTMENTS table.

B. It would not execute successfully because multiple columns (SALARY,COMMISSION_PCT)cannot be used in an UPDATE statement.

C. It would execute successfully and update the relevant data.

D. It would not execute successfully because the condition specified with the concatenation operator is not valid.

正解: C

質問 6

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

A. A user can be granted only one role at any point of time.

B. A role can be granted to PUBLIC.

C. A role can be granted to itself.

D. The REVOKE command can be used to remove privileges but not roles from other users.

E. Roles are named groups of related privileges that can be granted to users or other roles.

正解: B,E

質問 7

Evaluate the SQL statements: CREATE TABLE new_order
(orderno NUMBER(4),
booking_date TIMESTAMP WITH LOCAL TIME ZONE);
The database is located in San Francisco where the time zone is -8:00.
The user is located in New York where the time zone is -5:00.
A New York user inserts the following record:
INSERT INTO new_order
VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?);
Which statement is true?

A. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'

B. When the New York user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'

C. When the San Francisco user selects the row, booking_date is displayed as 007-05-10
3.00.00.000000 -8:00'

D. When the New York user selects the row, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'.

正解: A

質問 8

Which statement is true regarding synonyms?

A. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.

B. Synonyms are used to reference only those tables that are owned by another user.

C. A public synonym and a private synonym can exist with the same name for the same table.

D. Synonyms can be created for tables but not views.

正解: C

質問 9

Evaluate the following SELECT statement and view the Exhibit to examine its output: SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)

A. In the second column, indicates a check constraint.

B. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

C. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

D. The STATUS column indicates whether the table is currently in use.

正解: A,C

質問 10

Evaluate the following SELECT statement and view the Exhibit to examine its output: SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)

A. In the second column, indicates a check constraint.

B. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

C. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

D. The STATUS column indicates whether the table is currently in use.

正解: A,C

質問 11

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the
PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

A. The NEW_IDRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

B. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

C. The NEW_IDRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

正解: A

質問 12

View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM
employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees.
The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.

Which statement is true regarding this query?

A. The query would execute but the output would give review dates that are Sundays.

B. The query would not execute because the NEXT_DAY function accepts a string as argument.

C. The query would not execute because date functions cannot be nested.

D. The query would execute to give the desired output.

正解: A

質問 13

Which two statements are true about the GROUPING function? (Choose two.)

A. It is used to form the group sets involved in generating the totals and subtotals.

B. It is used to identify the NULL value in the aggregate functions.

C. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause.

D. It is used to find the groups forming the subtotal in a row.

正解: C,D

Oracle Database SQL Expert - 1Z1-047 模擬練習