[2026] Oracle Database SQL Expert - 1Z1-047 무료 시험 문제

문제1

View the Exhibit and examine the structure of the ORDERS table.
NEWJDRDERS 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 WHERE clause cannot be used in a subquery embedded in an INSERT statement

B. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table

C. because column names in NEW_ODRDERS and ORDERS tables do not match

D. because the VALUES clause cannot be used in an INSERT with a subquery

정답: D

문제2

You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?

A. external table

B. INSERT using WITH CHECK OPTION

C. the MERGE command

D. the multitable INSERT command

정답: D

문제3

Evaluate the following SQL statement:
ALTER TABLE hr.emp SET UNUSED (mgr_id);
Which statement is true regarding the effect of the above SQL statement?

A. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.

B. Any constraints defined on the MGR_ID column would be removed by the above command.

C. Any synonym existing on the EMP table would have to be re-created.

D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.

정답: B

문제4

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

A. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.

B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.

C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.

D. You can use column alias in the GROUP BY clause.

E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

정답: A,E

문제5

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_jd, TO_YMINTERVAL(SYSDATE + warranty_period)
FROM product_information;

B. SELECT product_id, SYSDATE + warranty_period
FROM product_information;

C. SELECT product_jd, TO_YMINTERVAL(warranty_period)
FROM product_information;

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

정답: B

문제6

View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

A. displaying the number of orders whose order total is more than the average order total for all online orders

B. displaying the number of orders that have order mode online and order date in 1999

C. displaying the total order value for sales representatives 161 and 163

D. displaying the order total for sales representative 161 in the year 1999

정답: A

문제7

View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
There are some products listed in the PRODUCT_INFORMATION table that have no value in the
LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME
for these products:
SELECT product_name, list_price
FROM product_information WHERE list_price = NULL;
The query returns no rows. What changes would you make in the statement to get the desired result?

A. In the WHERE clause, enclose NULL within single quotation marks.

B. Change the WHERE clause to WHERE list_price = ''.

C. Change the WHERE clause to WHERE list_price IS NULL.

D. In the WHERE clause, enclose NULL within double quotation marks.

E. Change the WHERE clause to WHERE list_price = 0

정답: C

문제8

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 execute successfully to produce the required output.

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 not execute because inline views and outer joins cannot be used together.

정답: A

문제9

The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT'FROM ord;

A. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.

C. CREATE SYNONYM ord FOR orders; This command is issued by OE.

D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

정답: D

문제10

View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables.
You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get
the maximum salary in their respective departments. The following SQL statement was written:
WITH
SELECT employee_id, last_name, salary
FROM employees
WHERE (department_id, salary) = ANY (SELECT*
FROM dept_max)
dept_max as (SELECT d.department_id, max(salary)
FROM departments d JOIN employees j
ON (d. department_id = j. department_id)
GROUP BY d. department_id);
Which statement is true regarding the execution and the output of this statement?

A. The statement would not execute because the = ANY comparison operator is used instead of=.

B. The statement would not execute because the comma is missing between the main query block and the query name.

C. The statement would execute and give the desired results.

D. The statement would not execute because the main query block uses the query name before it is even created.

정답: D

문제11

View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"

FROM order_items oi JOIN orders o ON oi.order_id = o.order_id GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)

A. t would return the subtotals for the Order Amount of every CUSTOMER_ID.

B. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.

C. t would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group.

D. t would return the subtotals for the Order Amount for every PRODUCT_ID.

E. t would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.

정답: A,B,D

문제12

View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statements:
Statement 1:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH
employee_id = 101
CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108;
Statement 2:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
WHERE manager_id != 108
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
Which two statements are true regarding the above SQL statements? (Choose two.)

A. Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.

B. The output for statement 1 would display the employee with MANAGERJD 108 and all the employees below him or her in the hierarchy.

C. The output for statement 2 would not display the employee with MANAGERJD 108 but it would display all the employees below him or her in the hierarchy.

D. The output of statement 1 would neither display the employee with MANAGERJD 108 nor any employee below him or her in the hierarchy.

정답: C,D

문제13

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

B. MINUS

C. UNION ALL

D. INTERSECT

정답: D

문제14

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 (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);

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
VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600), 1000);

정답: D

문제15

View the Exhibit and examine the data in the LOCATIONS table.
Evaluate the following SOL statement:
SELECT street_address FROM locations WHERE REGEXP_INSTR(street_address,'[^[: alpha:]] ') = 1;
Which statement is true regarding the output of this SOL statement?

A. It would display all the street addresses where the first character is a letter of the alphabet.

B. It would display all the street addresses where the first character is not a letter of the alphabet.

C. It would display all the street addresses where the first character is a special character.

D. It would display all the street addresses that do not have a substring 'alpha'.

정답: B

Oracle Database SQL Expert - 1Z1-047무료 덤프문제 풀어보기

우리와 연락하기

유용한 링크

최신 업데이트