AllExam Dumps

DUMPS, FREE DUMPS, VCP5 DUMPS| VMWARE DUMPS, VCP DUMPS, VCP4 DUMPS, VCAP DUMPS, VCDX DUMPS, CISCO DUMPS, CCNA, CCNA DUMPS, CCNP DUMPS, CCIE DUMPS, ITIL, EXIN DUMPS,


READ Free Dumps For Oracle- 1z0-007





Question ID 7873

Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. EMPLOYEES NOT NULL, EMPLOYEE_ID NUMBER Primary Key VARCHAR2 EMP_NAME (30) VARCHAR2 JOB_ID (20) SALARY NUMBER References MGR_ID NUMBER EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table DEPARTMENTS NOT NULL, Primary DEPARTMENT_ID NUMBER Key VARCHAR2 DEPARTMENT_NAME (30) References NGR_ID MGR_ID NUMBER column of the EMPLOYEES table Foreign key to LOCATION_ID NUMBER LOCATION_ID column of the LOCATIONS table LOCATIONS NOT NULL, Primary LOCATION_ID NUMBER Key VARCHAR2 CITY |30) Which two SQL statements produce the name, department name, and the city of all the employees who earn more then 10000? (Choose two)

Option A

SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;

Option B

SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 JOIN ON (e.department_id = d.department id) AND (d.location_id =1.location_id) AND salary > 10000;

Option C

SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE salary > 10000;

Option D

SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000;

Option E

SELECT emp_name, department_name, city FROM employees e NATURAL JOIN departments, locations WHERE salary > 10000;

Correct Answer AD
Explanation Explanation/Reference: Explanation: A is a correct response (JOIN USING). D: This is the correct syntax for a traditional (Non-ANSI Standard) JOIN. The join is defined in the WHERE Clause. Incorrect Answers B: Not the correct syntax for an ANSI Standard JOIN C: This query will built Cartesian product because there is no join conditions in WHERE clause to join tables. E: NATURAL JOIN is a join between two where Oracle joins the tables according to the column(s) in the two tables sharing the same name. It is required to add one more NATURAL JOIN clause to join additional table. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 98-118 Chapter 3: Advanced Data Selection in Oracle


Question ID 7874

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHAR2(30) SALARY NUMBER(8,2) Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?

Option A

SELECT dept_id, MIN(salary(, MAX(salary) FROM employees WHERE MIN(salary) 15000;

Option B

SELECT dept_id, MIN(salary), MAX(salary) FROM employees WHERE MIN(salary) < 5000 AND MAX(salary) > 15000 GROUP BY dept_id;

Option C

SELECT dept_id, MIN(salary), MAX(salary) FROM employees HAVING MIN(salary) 15000;

Option D

SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN (salary) < 5000 AND MAX(salary)

Option E

SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id, salary HAVING MIN (salary) 15000;

Correct Answer D
Explanation Explanation/Reference: Explanation: This SELECT statement shows correct result. Incorrect Answers A: To provide correct data statement needs also GROUP BY clause. B: This statement will not provide correct results. C: HAVING clause can be used only in conjunction with GROUP BY clause. E: You need only grouping by department, not by salary.

Send email to admin@getfreedumps for new dumps request!!!