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 7881

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?

Option A

SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;

Option B

SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester _end, ASC,gpa ASC;

Option C

SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester _end, gpa DESC;

Option D

SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC,semester_end DESC;

Option E

SELECT student_id, semester_end, gpa FROM student_grades

Correct Answer C
Explanation Explanation/Reference: Explanation: This answer shows correct syntax and semantics to receive desired result. Incorrect Answers A: Semesters will be sorted started from the oldest date, not the earliest. B: GPA data will be sorted in ascending order, what is opposite to our task. D: Semesters will be sorted started from the oldest date, not the earliest. Only difference with answer A is order of columns in the ORDER BY clause. E: This query has wrong order of columns to sort: results need to be sorted first by semester, than by grade point average. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-61 Chapter 2: Limiting, Sorting, and Manipulating Return Data


Question ID 7883

Examine the data in the EMPLOYEES and EMP_HIST tables:

EMPLOYEES

NAME DEPT_ID MGR_ID JOB_ID SALARY

EMPLOYEE_ID

101 Smith 20 120 SA_REP 4000

102 Martin 10 105 CLERK 2500

103 Chris 20 120 IT_ADMIN 4200

104 John 30 108 HR_CLERK 2500

105 Diana 30 108 IT_ADMIN 5000

106 Smith 40 110 AD_ASST 3000

108 Jennifer 30 110 HR_DIR 6500

 110 Bob 40 EX_DIR 8000

120 Ravi 20 110 SA_DIR 6500 EMP HIST

EMPLOYEE_ID NAME JOB_ID SALARY

101 Smith SA_CLERK 2000

103 Chris IT_CLERK 2200

104 John HR_CLERK 2000

106 Smith AD_ASST 3000

108 Jennifer HR_MGR 4500

The EMP_HIST table is updated at the end of every year. The employee ID, name, job ID, and salary of each existing employee are modified with the latest data. New employee details are added to the table. Which statement accomplishes this task?

Option A

UPDATE emp_hist SET employee_id, name, job_id, salary = (SELECT employee_id, name, job_id, salary FROM employees) WHERE employee_id IN (SELECT employee_id FROM employees);

Option B

MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT VALUES (e.employee id, e.name,

Option C

. job id, e.salary);

Option D

MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE emp hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employees_id, e.name, e.job_id, e.salary);

Option E

MERGE INTO emp_hist eh USING employees e WHEN MATCHED THEN UPDATE emp_hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employees_id, e.name, e.job_id, e.salary);

Correct Answer B
Explanation Explanation/Reference: Explanation: This task can be done using the MERGE command. Correct syntax for the MERGE command is MERGE INTO table1 USING table2 on (join_condition) WHEN MATCHED UPDATE SET col1 = value WHEN NOT MATCHED INSERT (column_list) values (column_values). Incorrect Answers A: MERGE command can handle this task, not UPDATE: new employee details will not be added to the table C: This statement would by correct if UPDATE SET is syntax used, not UPDATE table_name SET syntax as in usual UPDATE command. D: "ON condition" clause of the MERGE command is absent. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 273-275 Chapter 6: Manipulating Oracle Data

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