最新的Other Oracle Certification認證 1Z1-047考試題庫

問題1

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

A. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

C. The outer query stops evaluating the result set of the inner query when the first value is found.

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

正確答案: A,C

問題2

Evaluate the following CREATE TABLE command:
CREATE TABLE order_item (order_id NUMBER(3),
Item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id jtem_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id item_id)));
Which statement is true regarding the above SOL statement?

A. It would give an error because the USING INDEX clause cannot be used on a composite primary key.

B. It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.

C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.

D. It would execute successfully and only ORD_ITM_IDX index would be created.

正確答案: D

問題3

Evaluate the following command:
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2), 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 statements are true regarding the above view? (Choose all that apply.)

A. It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used in multitable INSERT statements.

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

C. It allows you to insert details of all new staff into the EMPLOYEES table.

D. It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table.

正確答案: A,B

問題4

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'.
Which SQL statement would give you the desired result?

A. SELECTfirst_name, salary
FROM employees WHERE (manager_id, salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena';

B. SELECT first_name, salary FROM employees WHERE (manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena') AND salary >= (SELECT salary FROM employees WHERE first_name = 'Neena')) AND first name <> 'Neena';

C. SELECTfirst_name, salary
FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena';

D. SELECT first_name, salary
FROM employees WHERE (manager_id, salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena';

正確答案: B

問題5

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

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

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

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

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

正確答案: A,C,D

問題6

Which statement is true regarding synonyms?

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

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

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

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

正確答案: A

問題7

View the Exhibit and examine the description of the ORDERS table.
The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers. Which statement would achieve this purpose?

A. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING total > 10;

B. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING COUNT(customer_id) > 10;

C. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
WHERE COUNT(customer_id) > 10
GROUP BY sales_rep_id;

D. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
HAVING COUNT(customer_id) > 10;

正確答案: B

問題8

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

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

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

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

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

正確答案: A,C

問題9

Which statement is true regarding external tables?

A. The data and metadata for an external table are stored outside the database.

B. The default REJECT LIMIT for external tables is UNLIMITED.

C. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.

D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

正確答案: C

問題10

View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT phone_number,
REGEXP_REPLACE(phone_number,'([[: digit: ]]{3})\.([[: digit: ]]{3})\.([[: digit: ]]{4})', ,(\1)\2-\3')
"PHONE NUMBER"
FROM employees;
The query was written to format the PHONE_NUMBER for the employees. Which option would
be the correct format in the output?

A. xxx-(xxx)-xxxx

B. (xxx) xxxxxxx

C. (xxx) xxx-xxxx

D. xxx-xxx-xxxx

正確答案: C

問題11

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

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

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

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

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

E. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.

正確答案: A,E

問題12

View the Exhibit and examine the description of the EMPLOYEES table. Evaluate the following SQL statement: SELECTfirst_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 not execute because date functions cannot be nested.

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

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

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

正確答案: C

問題13

A non-correlated subquery can be defined as________ .

A. a set of sequential queries, all of which must always return a single value

B. a set of sequential queries, all of which must return values from the same table

C. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

D. a SELECT statement that can be embedded in a clause of another SELECT statement only

正確答案: C

問題14

EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?

A. CREATE VIEW empvu AS
SELECT* FROM empdept;

B. DELETE FROM empdet
WHERE ename LIKE 'J%';

C. CREATE INDEX empdet_dx
ON empdet(empno);

D. UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234;

正確答案: A

最新的Oracle Database SQL Expert - 1Z1-047免費考試真題

最新更新

站內鏈接

聯系我們