Want to know Examcollection oracle 1z0 047 Exam practice test features? Want to lear more about Oracle Oracle Database SQL Expert certification experience? Study Tested Oracle 1z0 047 dumps answers to Refresh 1z0 047 dumps questions at Examcollection. Gat a success with an absolute guarantee to pass Oracle oracle database sql expert 1z0 047 (Oracle Database SQL Expert) test on your first attempt.
2017 NEW RECOMMEND
Free VCE & PDF File for Oracle 1z0-047 Real Exam
Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions
Q11. View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure.
Choose the correct SET operator to fill in the blank space and complete the following query.
WHERE job_id = 'SA_MAN'
Q12. 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
WHERE(unit_price*quantity) = MAX(unit_price*quantity)
GROUP BY order_id;
B. SELECT order_id
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items)
GROUP BY order_id;
C. SELECT order_id
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items
GROUP BY order_id);
D. SELECT order_id
GROUP BY order_id
HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity))
FROM order_items GROUP BY order_id);
Q13. View the Exhibit and examine the table structure of DEPARTMENTS and LOCATIONS tables.
You want to display all the cities that have no departments and the departments that have not been allocated cities.
Which type of join between DEPARTMENTS and LOCATIONS tables would produce this information as part of its output?
A. NATURAL JOIN
B. FULL OUTERJOIN
C. LEFT OUTERJOIN
D. RIGHT OUTER JOIN
Q14. View the Exhibit and examine the data in the LOCATIONS table.
Evaluate the following SOL statement:
REGEXP_INSTR(street_address,'[^[: alpha:]]â) = 1;
Which statement is true regarding the output of this SOL statement?
A. It would displayallthe street addresses thatdo nothaveasubstring 'alpha'.
B. It would displayallthestreetaddresseswhere the first character isaspecial character.
C. It would display allthe streetaddresses wherethefirst character is aletterofthealphabet.
D. It would displayall thestreet addresses where the first character isnota letter of the alphabet.
Q15. View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?
A. UPDATE orders
SETorder_date = 12-mar-2007\\ order_total IS NULL WHERE order_id = 2455;
B. UPDATE orders
SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455;
C. UPDATE orders
SET order_date = '12-mar-2007'
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455;
D. UPDATE orders
SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')lSET order_total =
TO_NUMBER(NULL) WHERE order id = 2455;
Q16. View the Exhibit and examine the description of the EMPLOYEES table.
You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000.
Which SOL statement would get you the desired result?
A. SELECT employee_id, first_name
WHERE job_id likeâMAN%' OR job_id like 'CLERK%'
AND department_id = 30 AND salary > 3000;
B. SELECT employee_ d, first_name
WHERE job_id likeâ%MAN' OR job_id like '%CLERK'
AND (department_id = 30 OR salary > 3000);
C. SELECT employee_id, first_name
WHERE (job_id likeâ%MAN' AND job_id like '%CLERKâ)
AND department_id = 30 OR salary > 3000;
D. SELECT employee_id, first_name
WHERE (job_id like '%MAN' OR job_id like '%CLERK')
AND department_id= 30 AND salary > 3000;
Q17. View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?
A. Itwould executebut theoutput would return no rows.
B. It would execute and the outputwould displaythedesired result.
C. It wouldnotexecute because the entireWHEREclause conditionisnot enclosedwithinthe parentheses.
D. Itwould not execute becausethesame column has been used in both sidesoftheANDlogical operatortoform the condition.
Q18. View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)
A. ORDERBY 3;
B. ORDER BY 'b';
C. ORDER BY DEPT_ID;
D. ORDER BY DEPT NAME;
Q19. Which statement correctly grants a system privilege?
A. GRANT EXECUTE ON prod TO PUBLIC;
B. GRANT CREATE VIEW ON tablel TO used;
C. GRANT CREATE TABLE TO used ,user2;
D. GRANT CREATE SESSION TO ALL;
Q20. Which three statements indicate the end of a transaction? (Choose three.)
A. after a COMMIT is issued
B. aftera ROLLBACK is issued
C. afteraSAVEPOINTis issued
D. afteraSELECT statement is issued
E. aftera CREATE statementis issued