Purchase Oracle : 1Z0-007 Exam Materials and then pass exam easily

Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

• A. SELECT employees.last_name, departments.department_name,
  departments.location_id
  FROM employees e, departments D
  WHERE e.department_id =d.department_id;
• B. SELECT e.last_name, d.department_name, d.location_id
  FROM employees e, departments D
  WHERE manager_id =manager_id;
• C. SELECT last_name, department_name, location_id
  FROM employees , departments ;
• D. SELECT e.last_name, d.department_name, d.location_id
  FROM employees e, departments D
  WHERE e.department_id =d.department_id;

Examine these statements:
CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar;
GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?

• A. The set of statements contains an error and does not work.
• B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
• C. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
• D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
• E. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.
• F. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

• A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
• B. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
• C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
• D. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;

Examine the data in the EMPLOYEES and DEPARTMENTS tables.

You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

• A. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
• B. SELECT last_name, department_name FROM employees , departments(+);
• C. SELECT last_name, department_name FROM employees JOIN departments (+);
• D. SELECT last_name, department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
• E. SELECT last_name, department_name FROM employees(+) e JOIN departments d ON (e.department_id = d.department_id);
• F. SELECT last_name, department_name FROM employees(+) , departments ON (e.department_id = d.department_id);

The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4,3)
The register has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this?

• A. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
• B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
• C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
• D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa DESC;
• E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end ASC, gpa ASC;