Oracle Database SQL Expert - 1Z0-047 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

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 orde_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. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. B. It would return the subtotals for the Order Amount for every PRODUCT_ID. C. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group. D. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. E. It would return the subtotals for the Order Amount of every CUSTOMER_ID.

Discussion 0

Correct Answer: A,B,E Vote an answer

Question 2

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

A. DELETE FROM empdet
WHERE ename LIKE 'J%'; B. CREATE INDEX empdet_dx
ON empdet(empno); C. UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234; D. CREATE VIEW empvu AS
SELECT* FROM empdept;

Discussion 0

Correct Answer: D Vote an answer

Question 3

The user SCOTT who is the owner of ORDERS and ORDERJTEMS tables issues the following GRANT command: GRANT ALL ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?

A. Separate GRANT statements are required for ORDERS and ORDERJTEMS tables. B. PUBLIC should be replaced with specific usernames. C. WITH GRANT OPTION should be added to the statement. D. ALL should be replaced with a list of specific privileges.

Discussion 0

Correct Answer: A Vote an answer

Question 4

Which statement correctly differentiates a system privilege from an object privilege?

A. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema. B. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database. C. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object. D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.

Discussion 0

Correct Answer: D Vote an answer

Question 5

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, total_orders; B. SELECT sales_rep_id, COUNT(order_id) total_orders
FROM orders; C. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
GROUP BY sales_rep_id; D. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
WHERE sales_rep_id IS NOT NULL;

Discussion 0

Correct Answer: C Vote an answer

Question 6

Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement?

A. It produces only the grand totals for the groups specified in the GROUP BY clause. B. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause. C. It produces only the subtotals for the groups specified in the GROUP BY clause. D. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.

Discussion 0

Correct Answer: D Vote an answer

Question 7

Evaluate the following statements:
CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE');
UPDATE digits SET description =TWO'WHERE id=1;
INSERT INTO digits VALUES (2.'TWO ');
COMMIT;
DELETE FROM digits;
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;
What would be the outcome of the above query?

A. It would not display any values. B. It would display the value TWO twice. C. It would display the value TWO once. D. It would display the values ONE, TWO, and TWO.

Discussion 0

Correct Answer: B Vote an answer

Question 8

Which three statements are true regarding group functions? (Choose three.)

A. They can be used only with a SQL statement that has the GROUP BY clause. B. They can be used on only one column in the SELECT clause of a SQL statement. C. They can be used along with the single-row function in the SELECT clause of a SQL statement. D. They can be used on columns or expressions. E. They can be passed as an argument to another group function.

Discussion 0

Correct Answer: C,D,E Vote an answer

Question 9

Which two statements are true regarding operators used with subqueries? (Choose two.

A. The IN operator cannot be used in single-row subqueries. B. The <ANY operator means less than the maximum. C. The NOT IN operator is equivalent to IS NULL D. The NOT operator can be used with IN, ANY and ALL operators. E. =ANY and =ALL operators have the same functionality.

Discussion 0

Correct Answer: B,D Vote an answer

Question 10

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 is created along with a new index. B. The PRIMARY KEY is created and it would use an existing unique index. C. The PRIMARY KEY would be created in a disabled state because it is using an existing index. D. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.

Discussion 0

Correct Answer: B Vote an answer

Question 11

View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement: SELECT employee_id, last_name, jobjd, 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 output would be in top-down hierarchy starting with EMPLOYEE_ID having value 101. C. The output would be in bottom-up hierarchy starting with EMPLOYEE_ID having value 101. D. The LEVEL column displays the number of employees in the hierarchy under the employee having the EMPLOYEE_ID 101.

Discussion 0

Correct Answer: A,B Vote an answer

Question 12

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-xxx-xxxx C. (xxx) xxxxxxx D. xxx-(xxx)-xxxx

Discussion 0

Correct Answer: A Vote an answer

Question 13

You need to create a table for a banking application with the following considerations:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and
subtracted with 3) date type data without using the conversion functions.
4) The maximum period of the credit provision in the application is 30 days.
5) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?

A. TIMESTAMP WITH LOCAL TIME ZONE B. INTERVAL YEAR TO MONTH C. TIMESTAMP WITH TIME ZONE D. INTERVAL DAY TO SECOND

Discussion 0

Correct Answer: D Vote an answer

Question 14

View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as
CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about
the customers.
What is the error in the following MERGE statement?
MERGE INTO customers c
USING customer_vu cv
ON (c.customer_id = cv.customer_id)
WHEN MATCHED THEN
UPDATE SET
c.customer_id = cv.customer_id,
c.cust_name = cv.cust_name,
c.cust_email = cv.cust_email,
c.income_level = cv. Income_level
WHEN NOT MATCHED THEN
INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level)
WHERE cv. Income_level >100000;

A. CUSTOMER VU cannot be used as a data source B. The WHERE clause cannot be used with INSERT. C. The INTO clause is misplaced in the command. D. The CUSTOMERJD column cannot be updated.

Discussion 0

Correct Answer: D Vote an answer