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

問題1

View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;
Which statement is true regarding the effect of the command?

A. The PRIMARY KEY would be created in a disabled state because it is using an existing index.

B. The PRIMARY KEY is created along with a new index.

C. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.

D. The PRIMARY KEY is created and it would use an existing unique index.

正確答案: D

問題2

View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city FROM locations
SELECT location_id, city FROM locations JOIN departments USING(location_id);

A. INTERSECT

B. UNION

C. MINUS

D. UNION ALL

正確答案: A

問題3

View the Exhibit and examine the structure of the
PRODUCTINFORMATION and INVENTORIES tables.
You want to display the quantity on hand for all the products available in the PRODUCTINFORMATION table that have the PRODUCT_STATUS as 'orderable'
QUANTITY_ON_HAND is a column in the INVENTORIES table. The following SQL statement
was written to accomplish the task:
SELECT pi. Product_id, pi.product_status, sum(i.quantity_on_hand)
FROM product_information pi LEFT OUTER JOIN inventories i
ON (pi. Product_id = i. product_id)
WHERE (pi.product_status = 'orderable")
GROUP BY pi.product_id, pi.product_status;
Which statement is true regarding the execution of this SQL statement?

A. The statement would not execute because the WHERE clause is not allowed with LEFT OUTER JOIN.

B. The statement would not execute because prefixing table alias to column names is not allowed with the ON clause.

C. The statement would execute and produce the desired output.

D. The statement would not execute because the WHERE clause is used before the GROUP BY clause.

正確答案: C

問題4

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. Which INSERT statement should be used to add a row into the ORDERS table for the customer
whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?

A. INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600) .order_total)
VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);

B. INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600), 1000);

C. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total
FROM orders o, customers c WHERE o.customer_id = c.customer_id
AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND Credit_limit=600), 1000);

D. INSERT INTO orders (order_id.order_date.order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit _limit=600) .order_total)
VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);

正確答案: B

問題5

User OE, the owner of the ORDERS table, issues the following command:
GRANT SELECT .INSERT ON orders TO hr WITH GRANT OPTION;
The user HR issues the following command:
GRANT SELECT ON oe. orders
TO scott;
Then, OE issues the following command: REVOKE ALL ON orders FROM hr;
Which statement is correct?

A. The user SCOTT retains the privilege to select rows from OE.ORDERS.

B. The user SCOTT loses the privilege to select rows from OE.ORDERS.

C. The REVOKE statement generates an error because the ALL keyword cannot be used for privileges that have been granted using WITH GRANT OPTION.

D. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.

正確答案: B

問題6

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_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

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

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

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

正確答案: C

問題7

The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_DRDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use?

A. Pivoting INSERT

B. Unconditional INSERT

C. Conditional ALL INSERT

D. Conditional FIRST INSERT

正確答案: C

問題8

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

A. A subquery used in a complex view definition cannot contain group functions or joins.

B. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.

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

D. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

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

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

正確答案: B,D

問題9

Evaluate the following statement:
CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100);
The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table.
Which option should be used to perform this task most efficiently?

A. Unconditional INSERT

B. Conditional ALL INSERT

C. Conditional FIRST INSERT

D. MERGE

正確答案: D

問題10

View the Exhibit and examine the description of the ORDERS table.
Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result?

A. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
GROUP BY sales_rep_id;

B. SELECT sales_rep_id, COUNT(order_id) total_orders
FROM orders;

C. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
WHERE sales_rep_id IS NOT NULL;

D. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
GROUP BY sales_rep_id, total_orders;

正確答案: A

問題11

View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of
items in each order.
Which CREATE VIEW statement would create the view successfully?

A. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;

B. CREATE OR REPLACE VIEW ord_vu (order_id,order_date)
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;

C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;

D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)| NO OF ITEMS'
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date
WITH CHECK OPTION;

正確答案: A

問題12

View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o
USING(order_id);
Which statement is true regarding the execution of this SQL statement?

A. The statement would not execute because the table alias prefix is not used in the USING clause.

B. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.

C. The statement would not execute because table aliases are not allowed in the JOIN clause.

D. The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases.

正確答案: B

問題13

View the Exhibit and examine the description of the ORDERS table.
You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement:
SELECT customer_id,COUNT(order_id) FROM orders WHERE COUNT(order_id)>3 AND order_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE GROUP BY customer_id;
Which statement is true regarding the execution of the above statement?

A. It would not execute because the WHERE clause cannot have an aggregate function.

B. It would not execute because the ORDER_ID column is not included in the GROUP BY clause.

C. It would execute successfully and provide the desired result.

D. It would not execute because the GROUP BY clause should be placed before the WHERE clause.

正確答案: A

問題14

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite
PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)

A. CREATE INDEX ord_idx
ON ord_items(ord_no);

B. CREATE INDEX ord_idx
ON ord,ord_items(ord_no, ord_date,qty);

C. CREATE INDEX ord_idx
ON ord_items(item_no);

D. CREATE INDEX ord_idx
ON ord(ord_no);

正確答案: A,C

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

聯系我們

站內鏈接

最新更新