1Z1-047無料問題集「Oracle Database SQL Expert」

質問 1

View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO cust VALUES (1 /RAJ1); --row 1
INSERT INTO cust VALUES (1 ,'SAM); --row 2
COMMIT;
SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1 /LATA1); --row 3
INSERT INTO cust VALUES (2 .KING1); --row 4
COMMIT;
Which rows would be made permanent in the CUST table?

（A）rows 2 and 4

（B）rows 3 and 4

（C）row 4 only

（D）rows 1 and 4

正解：B 解答を投票する

質問 2

View the Exhibit and examine the structure of the ORDER_ITEMS table.
You need to display the ORDER_ID of the order that has the highest total value among all the
orders in the ORDER_ITEMS table.
Which query would produce the desired output?

（A）SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items)
GROUP BY order_id;

（B）SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity)
GROUP BY order_id;

（C）SELECT order_id
FROM order_items
GROUP BY order_id
HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity))
FROM order_items GROUP BY order_id);

（D）SELECT order_id
FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items
GROUP BY order_id);

正解：C 解答を投票する

質問 3

View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement: SELECT order_id, product_id, unit_price
FROM order_jtems
WHERE unit_price = (SELECT MAX(unit_price)
FROM order_items
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per
ORDER_ID.
What correction should be made in the above SQL statement to achieve this?

（A）Replace = with the >ALL operator.

（B）Remove the GROUP BY clause from the subquery and place it in the main query.

（C）Replace = with the IN operator.

（D）Replace = with the >ANY operator.

正解：C 解答を投票する

質問 4

View the Exhibit and examine the description of the EMPLOYEES table. You want to calculate the total remuneration for each employee. Total remuneration is the sum of
the annual salary and the percentage commission earned for a year. Only a few employees earn
commission.
Which SOL statement would you execute to get the desired output?

（A）SELECTfirst_name, salary, salary*12+ (salary*NVL2(commission_pct,
salary,salary+commission_pct))"Total"
FROM EMPLOYEES;

（B）SELECTfirst_name, salary, salary*12+NVL((salary*commission_pct), 0) "Total"
FROM EMPLOYEES;

（C）SELECTfirst_name, salary, salary*12+salary*commission_pct "Total"
FROM EMPLOYEES;

（D）SELECTfirst_name, salary, salary*12 + NVL(salary, O)*commission_pct "Total"
FROM EMPLOYEES;

正解：B 解答を投票する

質問 5

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）MERGE

（B）Conditional ALL INSERT

（C）Unconditional INSERT

（D）Conditional FIRST INSERT

正解：A 解答を投票する

質問 6

View the Exhibit and examine the description of the ORDER_ITEMS and PRODUCT_INFORMATION tables.
The ORDER_ITEM table has records pertaining to details for each product in an order. The PRODUCT_INFORMATION table has records for all the products available for ordering.
Evaluate the following SOL statement:
SELECT oi.order_id, pi.product_id
FROM orderjtems oi RIGHT OUTER JOIN product_information pi ON (oi. product_id=pi. Product_id);
Which statement is true regarding the output of this SOL statement?

（A）The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered but not listed in the PRODUCT_INFORMATION table.

（B）The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered.

（C）The query would return the ORDER_ID and PRODUCT_ID for those products that are ordered as well as for the products that have never been ordered, and for the products that are not listed in the PRODUCT INFORMATION table.

（D）The query would return the ORDER_ID and PRODUCT_ID for only those products that are ordered.

正解：B 解答を投票する

質問 7

View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104;
When you execute the statement it does not produce the desired output. What is the reason for this?

（A）The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.

（B）The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order.

（C）The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.

（D）The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator.

正解：B 解答を投票する

質問 8

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 all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

（B）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.

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

正解：B 解答を投票する

質問 9

View the Exhibitl and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables.
The following SQL statement was executed:
SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total,
GROUPING(e.department_id)GRP_DEPT,
GROUPING(e.job_id) GRPJOB,
GROUPING(d. location_id) GRP_LOC
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id);
View the Exhibit2 and examine the output of the command.
Which two statements are true regarding the output? (Choose two.)

（A）The value 0 in GRP_DEPT, GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columns are taken into account to generate the subtotal

（B）The value 1 in GRP_JOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal.

（C）The value 1 in GRP_JOB and GRP_LOC means that the NULL value in JOB_ID and LOCATIONJD columns are taken into account to generate the subtotal.

（D）The value 1 in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal.

正解：A、B 解答を投票する

質問 10

You executed the following SQL statements in the given order:
CREATE TABLE orders
(order_id NUMBER(3) PRIMARY KEY,
order_date DATE,
customer_id number(3));
INSERT INTO orders VALUES (100,'10-mar-2007,,222);
ALTER TABLE orders MODIFY order_date NOT NULL;
UPDATE orders SET customer_id=333;
DELETE FROM order;
The DELETE statement results in the following error:
ERROR at line 1:
ORA-00942: table or view does not exist
What would be the outcome?

（A）All the statements before the DELETE statement would be implicitly committed within the session.

（B）All the statements up to the ALTER TABLE statement would be committed and the outcome of the UPDATE statement is retained uncommitted within the session.

（C）All the statements before the DELETE statement would be rolled back.

（D）All the statements up to the ALTER TABLE statement would be committed and the outcome of UPDATE statement would be rolled back.

正解：B 解答を投票する

質問 11

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id; What would happen when the above statement is executed?

（A）The statement would not execute because inline views and outer joins cannot be used together.

（B）The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

（C）The statement would not execute because the GROUP BY clause cannot be used in the inline view.

（D）The statement would execute successfully to produce the required output.

正解：D 解答を投票する

質問 12

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100
INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?

（A）an error

（B）1

（C）10

（D）100

正解：B 解答を投票する

質問 13

Evaluate the following statement: INSERT ALL WHEN order_total < 10000 THEN INTO small_orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

（A）They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

（B）They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

（C）The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.

（D）They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

正解：D 解答を投票する

質問 14

View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id=manager_id ;
Which two statements are true regarding the output of this command? (Choose two.)

（A）The LEVEL column displays the level in the hierarchy at which the employee is placed under the employee having the EMPLOYEE_ID 101

（B）The LEVEL column displays the number of employees in the hierarchy under the employee having the EMPLOYEE_ID 101.

（C）The output would be in bottom-up hierarchy starting with EMPLOYEE_ID having value 101.

（D）The output would be in top-down hierarchy starting with EMPLOYEE_ID having value 101.

正解：C 解答を投票する

解説: (JPNTest メンバーにのみ表示されます)

質問 15

View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
You want to display the expiration date of the warranty for a product. Which SQL statement would you execute?

（A）SELECT product_id, SYSDATE + warranty_period FROM product_information;

（B）SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information;

（C）SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information;

（D）SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information;

正解：A 解答を投票する

質問 16

Which SQL statement would display the view names and definitions of all the views owned by you?

（A）SELECTviewjiame, text FROM user_objects;

（B）SELECTview_name, text FROM user views;

（C）SELECTviewjiame, text FROM user_view;

（D）SELECTviewjiame, text FROM user_object;

正解：B 解答を投票する

質問 17

View the Exhibit and examine the structure of the ORDERS table.
NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and
ORD_TOTAL that have the same data types and size as the corresponding columns in the
ORDERS table.
Evaluate the following INSERT statement:
INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES(SELECT
order_id.order_date.customer_id.order_total FROM orders WHERE order_date > '31-dec-1999');
Why would the INSERT statement fail?

（A）because the VALUES clause cannot be used in an INSERT with a subquery

（B）because column names in NEWORDERS and ORDERS tables do not match

（C）because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table

（D）because the WHERE clause cannot be used in a subquery embedded in an INSERT statement

正解：A 解答を投票する

質問 18

View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
Evaluate the following MERGE statement:
MERGE INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total);
What would be the outcome of the above statement?

（A）The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

（B）The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3.

（C）The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4.

（D）The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.

正解：D 解答を投票する

質問 19

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）MERGE

（B）Conditional ALL INSERT

（C）Unconditional INSERT

（D）Conditional FIRST INSERT

正解：A 解答を投票する

1Z1-047 無料問題集「Oracle Database SQL Expert」

弊社を連絡する

関連リンク

トップ試験