SQL EXPERT (1Z0-047)

Please enter your email:

1. You executed the following multitable INSERT statement:
INSERT FIRST
WHEN credit_limit >= 5000 THEN
INTO cust_1 VALUES(cust_id, credit_limit, grade, gender)
WHEN grade = THE
INTO cust_2 VALUES(cust_id, credit_limit, grade, gender)
WHEN gender = THE
INTO cust_3 VALUES(cust_id, credit_limit, grade, gender)
INTO cust_4 VALUES(cust_id, credit_limit, grade, gender)
ELSE
INTO cust_5 VALUES(cust_id, credit_limit, grade, gender)
SELECT * FROM cust_det;
The row will be inserted in________.




 

 

 

 

2. 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?




 

 

 

 

3. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use?




 

 

 

 

4. 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?




 

 

 

 

5. Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?




 

 

 

 

6. 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?



 

 

 

 

7. Which three statements are true regarding single-row functions? (Choose three.)




 

 

 

 

 

 

8. 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?




 

 

 

 

9. Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.)




 

 

 

 

 

10. 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.)



 

 

 

 

11. 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?




 

 

 

 

12. View the Exhibit and examine the data in the
PRODUCT INFORMATION table.

Which two tasks would require subqueries? (Choose two.)




 

 

 

 

 

13. Which statement is true regarding synonyms?




 

 

 

 

14. View the Exhibit and examine the structure of the ORDERS and ORDERJTEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_jd, order_date
FROM order_items oi JOIN orders o
USING(order_id);
Which statement is true regarding the execution of this SQL statement?




 

 

 

 

15. Which CREATE TABLE statement is valid?



 

 

 

 

16. Which statements are correct regarding indexes? (Choose all that apply.)




 

 

 

 

17. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)




 

 

 

 

18. Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?




 

 

 

 

19. 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?




 

 

 

 

20. 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?



 

 

 

 

21. Which statement best describes the GROUPING function?



 

 

 

 

22. Which statement is true regarding Flashback Version Query?



 

 

 

 

23. A non-correlated subquery can be defined as________ .




 

 

 

 

24. 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?




 

 

 

 

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




 

 

 

 

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




 

 

 

 

27. 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?



 

 

 

 

28. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS 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?



 

 

 

 

29. 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?




 

 

 

 

30. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)




 

 

 

 

 

31. Which two statements are true regarding roles? (Choose two.)



 

 

 

 

 

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




 

 

 

 

Share this
Share

Share this
Share
Share