The human resources department requested an employee report to high light those employees who are due for 30 days 6 months and 1 year evaluation. The higher_data field should display blue text for 30 days employee, a red text for 6 months employees and a 

第1题：

第2题：

第3题：

Evaluate the SQL statement: 1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal 2 FROM employees a, 3 (SELECT dept_id, MAX(sal) maxsal 4. FROM employees 5 GROUP BY dept_id) b 6 WHERE a.dept_id = b.dept_id 7 AND a. asl < b. maxsal; What is the result of the statement? （）

• A、The statement produces an error at line 1.
• B、The statement produces an error at line 3.
• C、The statement produces an error at line 6.
• D、The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company.
• E、The statement returns the 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.

第4题：

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2 (60) Which DELETE statement is valid?（）

• A、DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
• B、DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_ employees);
• C、DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = 'carrey');
• D、DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = 'carrey');

第5题：

Examine the data in the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES EMP_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 DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME 10 Admin 20 Education 30 IT 40 Human Resources Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables: CREATE TABLE departments (department_id NUMBER PRIMARY KEY, department _ name VARCHAR2(30)); CREATE TABLE employees (EMPLOYEE_ID NUMBER PRIMARY KEY, EMP_NAME VARCHAR2(20), DEPT_ID NUMBER REFERENCES departments(department_id), MGR_ID NUMBER REFERENCES employees(employee id), MGR_ID NUMBER REFERENCES employees(employee id), JOB_ID VARCHAR2(15). SALARY NUMBER); ON the EMPLOYEES, On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table. On the DEPARTMENTS table, DEPARTMENT_ID is the primary key. Examine this DELETE statement: DELETE FROM departments WHERE department id = 40; What happens when you execute the DELETE statement?（）

• A、Only the row with department ID 40 is deleted in the DEPARTMENTS table.
• B、The statement fails because there are child records in the EMPLOYEES table with department ID 40.
• C、The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.
• D、The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.
• E、The row with department ID 40 is deleted in the DEPARTMENTS table. Also all the rows in the EMPLOYEES table are deleted.
• F、The statement fails because there are no columns specifies in the DELETE clause of the DELETE statement.

第6题：

You create a tabular employee report and are concerned about readability because the employee records are very close together. What is the best way to increase the amount of white space between the records?（）

• A、In the layout model increase the size of the employee record fields. 
• B、In the report wizard increase the display width values of the employee record fields. 
• C、In the property palette increase the size of the repeating frame surrounding the employee records. 
• D、In the life previewer, high light a field, click on the parent frame icon and make the frame bigger verticall.

第7题：

Examine the data from the EMP table: EMP_ID DEPT_ID COMMISSION 1 10 500 2 20 1000 3 10 4 10 600 5 30 800 6 30 200 7 10 8 20 300 The COMMISSION column shows the monthly commission earned by the employee. Which three tasks would require subqueries or joins in order to perform in a single step? （）

• A、Deleting the records of employees who do not earn commission.
• B、Increasing the commission of employee 3 by the average commission earned in department 20.
• C、Finding the number of employees who do NOT earn commission and are working for department 20.
• D、Inserting into the table a new employee 10 who works for department 20 and earns a commission that is equal to the commission earned by employee 3.
• E、Creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSIONS of the EMP table.
• F、Decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more then 800.

第8题：

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task? （）

• A、ALTER VIEW EMP_dept_vu (ADD manger_id NUMBER);
• B、MODIFY VIEW EMP_dept_vu (ADD manger_id NUMBER);
• C、ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department _ id = d.department_id;
• D、MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id = d.department_id;
• E、CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id = d.department_id;
• F、You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

第9题：

第10题：

第11题：

第12题：

第13题：

第14题：

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEESColumn name Data type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20) SALARY NUMBER MGR_ID NUMBER References EMPLOYEE_ID COLUMN DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID column of the DEPARTMENTS table DEPARTMENTSColumn name Data type Remarks DEPARTMENT_ID NUMBER NOT NULL, Primary Key DEPARTMENT_NAME VARCHAR2(30) MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table Evaluate this SQL statement: SELECT employee_id, e.department_id, department_name, salary FROM employees e, departments d WHERE e. department_id = d.department_id; Which SQL statement is equivalent to the above SQL statement? （）

• A、SELECT employee_id, department_id, department_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);
• B、SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;
• C、SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department _ id = d. department_id;
• D、SELECT employee_id, department_id, department_name, Salary FROM employees JOIN departments USING (e.department_id, d.department_id);

第15题：

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task?（）

• A、ALTER VIEW EMP_dept_vu (ADD manger_id NUMBER);
• B、MODIFY VIEW EMP_dept_vu (ADD manger_id NUMBER);
• C、ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department _ id = d.department_id;
• D、MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id = d.department_id;
• E、CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id = d.department_id;
• F、You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

第16题：

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEESColumn name Data type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20) SALARY NUMBER MGR_ID NUMBER References EMPLOYEE_ID COLUMN DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID column of the DEPARTMENTS table DEPARTMENTSColumn name Data type Remarks DEPARTMENT_ID NUMBER NOT NULL, Primary Key DEPARTMENT_NAME VARCHAR2(30) MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table Evaluate this SQL statement: SELECT employee_id, e.department_id, department_name, salary FROM employees e, departments d WHERE e. department_id = d.department_id; Which SQL statement is equivalent to the above SQL statement?（）

• A、SELECT employee_id, department_id, department_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);
• B、SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;
• C、SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department _ id = d. department_id;
• D、SELECT employee_id, department_id, department_name, Salary FROM employees JOIN departments USING (e.department_id, d.department_id);

第17题：

Examine the data of the EMPLOYEES table. EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID) EMP_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 HR_MGR 5000 106 Bryan 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 Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000?（）

• A、SELECT employee_id "Emp_id", emp_name "Employee", salary, employee_id "Mgr_id", emp_name "Manager" FROM employees WHERE salary > 4000;
• B、SELECT e.employee_id "Emp_id", e.emp_name "Employee",
• C、salary,
• D、employee_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;

第18题：

You created a tabular report for human resources department. After viewing layout users have requested that you change the label of the dept column to something more meaningful. How would you change the label in the live previewer?（）

• A、Use the report wizard to change the column label. 
• B、Click and high light the column label and replace it with desired text. 
• C、Click the column label and alter its property palette. 
• D、Click the dept column and alter its property palette. 

第19题：

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) DEFAULT 'SA_REP' SAL NUMBER COMM_PCT NUMBER MGR_ID NUMBER DEPARTMENT_ID NUMBER You need to update the records of employees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below: JOB_ID: Default value specified for this column definition. SAL: Maximum salary earned for the job ID SA_REP. COMM_PCT: Default value specified for this commission percentage column, if any. If no default value is specified for the column, the value should be NULL. DEPARTMENT_ID: Supplied by the user during run time through substitution variable. Which UPDATE statement meets the requirements?（）

• A、UPDATE employees SET job_id = DEFAULT AND Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') AND comm_pct = DEFAULT AND department_id = &did WHERE employee _id IN (103,115);
• B、UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department_id = &did WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP';
• C、UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP'), comm_pct = DEFAULT, department_id = &did WHERE employee_id IN (103,115);
• D、UPDATE employees SET job_id = DEFAULT, Sal = MAX(sal), comm_pct = DEFAULT, department_id = &did WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP';
• E、UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHERE employee_id IN (103,115);

第20题：

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which DELETE statement is valid?（）

• A、DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
• B、DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);
• C、DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name ='Carrey');
• D、DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name ='Carrey');

第21题：

第22题：

第23题：