que_042.htm
来自「oracle DBA的资料」· HTM 代码 · 共 380 行
HTM
380 行
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<title>Ocp test </title>
</head>
<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<p align="left" color="black" style="font-size:16pt;">
<b>QUESTION 042
</b></p><hr color="#99CCFF">
<p>Examine the data in the EMPLOYEES and EMP_HIST tables: </p><p>EMPLOYEES</p><table border="1" bordercolor="gray">
<span style="font-size:18pt;">
<tr>
<td align="center" bgcolor="#99CCFF">
<p><b>EMPLOYEE_ID</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>NAME</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>DEPT_ID</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>MGR_ID</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>JOB_ID</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>SALARY</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>101</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Smith</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>20</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>120</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>SA_REP</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>4000</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>102</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Martin</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>10</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>105</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>CLERK</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>2500</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>103</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Chris</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>20</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>120</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>IT_ADMIN</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>4200</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>104</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>John</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>30</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>108</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>HR_CLERK</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>2500</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>105</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Diana</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>30</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>108</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>IT_ADMIN</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>5000</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>106</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Smith</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>40</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>110</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>AD_ASST</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>3000</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>108</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Jennifer</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>30</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>110</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>HR_DIR</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>6500</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>110</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Bob</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>40</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b> </b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>EX_DIR</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>8000</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>120</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Ravi</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>20</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>110</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>SA_DIR</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>6500</b></p></b>
</td>
</tr>
</span>
</table>
<p>EMP_HIST</p><table border="1" bordercolor="gray">
<span style="font-size:18pt;">
<tr>
<td align="center" bgcolor="#99CCFF">
<p><b>EMPLOYEE_ID</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>NAME</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>JOB_ID</b></p></b>
</td>
<td align="center" bgcolor="#99CCFF">
<p><b>SALARY</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>101</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Smith</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>SA_CLERK</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>2000</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>103</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Chris</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>IT_CLERK</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>2200</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>104</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>John</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>HR_CLERK</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>2000</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>106</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Smith</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>AD_ASST</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>3000</b></p></b>
</td>
</tr>
<tr>
<td align="center" bgcolor="white">
<p><b>108</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>Jennifer</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>HR_MGR</b></p></b>
</td>
<td align="center" bgcolor="white">
<p><b>4500</b></p></b>
</td>
</tr>
</span>
</table>
<p>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? </p><table border="1" bordercolor="gray" width="665">
<form name="youAnsForm">
<span style="font-size:18pt;">
<tr>
<td align="center" width="89">
<p><b><input type="radio" name="AN" value="A"> A.</b></p>
</td>
<td align="left" width="566">
<p> UPDATE emp_hist <br>
SET employee_id, name, job_id, salary = <br>
(SELECT employee_id, name, job_id, salary <br>
FROM employees) <br>
WHERE employee_id IN <br>
(SELECT employee_id <br>
FROM employees);
</p>
</td>
</tr>
<tr>
<td align="center" width="89">
<p><b><input type="radio" name="AN" value="B"> B.</b></p>
</td>
<td align="left" width="566">
<p> MERGE INTO emp_hist eh <br>
USING employees e <br>
ON (eh.employee_id = e.employee_id) <br>
WHEN MATCHED THEN <br>
UPDATE <br>SET eh.name = e.name,
eh.job_id = e.job_id, <br>
eh.salary = e.salary <br>
WHEN NOT MATCHED THEN <br>
INSERT VALUES (e.employee id, e.name,
e.job id, e.salary);
</p>
</td>
</tr>
<tr>
<td align="center" width="89">
<p><b><input type="radio" name="AN" value="C"> C.</b></p>
</td>
<td align="left" width="566">
<p> MERGE INTO emp_hist eh <br>
USING employees e <br>
ON (eh.employee_id = e.employee_id) <br>
WHEN MATCHED THEN <br>
UPDATE emp hist SET eh.name = e.name, <br>
eh.job_id = e.job_id, <br>
eh.salary = e.salary <br>
WHEN NOT MATCHED THEN <br>
INSERT INTO emp_hist <br>
VALUES (e.employee_id, e.name, e.job_id, e.salary);
</p>
</td>
</tr>
<tr>
<td align="center" width="89">
<p><b><input type="radio" name="AN" value="D"> D.</b></p>
</td>
<td align="left" width="566">
<p> MERGE INTO emp_hist eh <br>
USING employees e <br>
WHEN MATCHED THEN <br>
UPDATE emp_hist SET eh.name = e.name, <br>
eh.job_id = e.job_id, <br>
eh.salary = e.salary <br>
WHEN NOT MATCHED THEN <br>
INSERT INTO emp_hist <br>
VALUES (e.employee_id, e.name, e.job_id, e.salary);</p>
</td>
</tr>
</span>
</form>
</table>
<br></body>
</html>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?