최신Oracle Database SQL Expert - 1Z1-047무료샘플문제
문제1
View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables.
You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the
employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID.
The following SQL statement was written:
SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department"
FROM employees m JOIN employees e
ON (m.employee_id = e.manager_id)
WHERE e.manager_id=100
JOIN departments d
ON (e.department_id = d.department_id);
Which statement is true regarding the output of this SQL statement?
View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables.
You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the
employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID.
The following SQL statement was written:
SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department"
FROM employees m JOIN employees e
ON (m.employee_id = e.manager_id)
WHERE e.manager_id=100
JOIN departments d
ON (e.department_id = d.department_id);
Which statement is true regarding the output of this SQL statement?
정답: C
문제2
View the Exhibit and examine the description of the ORDERS table.
You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement:
SELECT customer_id,COUNT(order_id) FROM orders WHERE COUNT(order_id)>3 AND order_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE GROUP BY customer_id;
Which statement is true regarding the execution of the above statement?
View the Exhibit and examine the description of the ORDERS table.
You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement:
SELECT customer_id,COUNT(order_id) FROM orders WHERE COUNT(order_id)>3 AND order_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE GROUP BY customer_id;
Which statement is true regarding the execution of the above statement?
정답: B
문제3
Which statement is true regarding the SESSION_PRIVS dictionary view?
Which statement is true regarding the SESSION_PRIVS dictionary view?
정답: B
문제4
Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
정답: B,D,E
문제5
Which statement is true regarding the SESSION_PRIVS dictionary view?
Which statement is true regarding the SESSION_PRIVS dictionary view?
정답: B
문제6
View the Exhibit and examine the data in EMP and DEPT tables.
In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing
the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2.COTT 10); INSERT INTO emp VALUES (3,ING 55);
View the Exhibit and examine the data in EMP and DEPT tables.
In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing
the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2.COTT 10); INSERT INTO emp VALUES (3,ING 55);
정답: A
문제7
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.)
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,C
문제8
Which two statements are true regarding operators used with subqueries? (Choose two.
Which two statements are true regarding operators used with subqueries? (Choose two.
정답: A,E
문제9
View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT
NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id FROM ORDERS WHERE order_total
= 1000 WITH CHECK OPTION) VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT
NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id FROM ORDERS WHERE order_total
= 1000 WITH CHECK OPTION) VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
정답: C
문제10
Which statements are true? (Choose all that apply.)
Which statements are true? (Choose all that apply.)
정답: A,C,D
문제11
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?
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?
정답: B
문제12
View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104;
When you execute the statement it does not produce the desired output. What is the reason for this?
View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_ID 104. To accomplish the task, you execute the following SQL statement:
SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104;
When you execute the statement it does not produce the desired output. What is the reason for this?
정답: B
문제13
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?
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?
정답: B