📄 que_042.htm
字号:
<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 + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -