Free Oracle 1Z1-047 Exam Dumps Questions & Answers

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.PUBLIC should be replaced with specific usernames.
• B.Separate GRANT statements are required for ORDERS and ORDERJTEMS tables.
• C.WITH GRANT OPTION should be added to the statement.
• D.ALL should be replaced with a list of specific privileges.

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.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.
• C.They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
• D.They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

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 index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
• C.Any constraints defined on the MGR_ID column would be removed by the above command.
• D.Any synonym existing on the EMP table would have to be re-created.

View the Exhibit and examine the description of the EMPLOYEES table.
Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary:
SELECT last_name, departmentjd, salary450*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;
When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?

• A.Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation".
• B.Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
• C.Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".
• D.Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".

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 not a letter of the alphabet.
• B.It would display all the street addresses where the first character is a special character.
• C.It would display all the street addresses where the first character is a letter of the alphabet.
• D.It would display all the street addresses that do not have a substring 'alpha'.