不值得定律：它反映出人们的一种心理。一个人如果从事的是一份自认为不值得的事情，往往会持冷嘲热

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 NATURAL JOIN departments;

B.SELECT last_name, department_name FROM employees JOIN departments ;

C.SELECT last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id);

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 FULL JOIN departments ON (e.department_id = d.department_id);

F.SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

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？（）

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 NATURAL JOIN departments;

B. SELECT last_name, department_name FROM employees JOIN departments ;

C. SELECT last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id);

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 FULL JOIN departments ON (e.department_id = d.department_id);

F . SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id)

hat create the EMPLOYEES and DEPARTMENTS tables: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: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.

ype RemarksEMPLOYEE_ID NUMBER NOT NULL, Primary KeyEMP_NAME VARCHAR2 (30)JOB_ID VARCHAR2 (20)SALARY NUMBERMGR_ID NUMBER References EMPLOYEE_ID COLUMNDEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID column of the DEPARTMENTS tableDEPARTMENTSColumn name Data type RemarksDEPARTMENT_ID NUMBER NOT NULL, Primary Key DEPARTMENT_NAME VARCHAR2(30)MGR_ID NUMBER References MGR_ID column of the EMPLOYEES tableEvaluate this SQL statement:SELECT employee_id, e.department_id, department_name, salaryFROM employees e, departments dWHERE 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);

want to retrieve all employees‘ last names, along with their managers‘ last names and their department names. Which query would you use？（）

A.SELECT last_name, manager_id, department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);

B.SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

C.SELECT e.last_name, m.last_name, department_name FROM employees e RIGHT OUTER JOIN employees m on (e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

D.SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.employee_id) RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

E.SELECT e.last_name, m.last_name, department_name FROM employees e RIGHT OUTER JOIN employees m on (e.manager_id = m.employee_id) RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

F.SELECT last_name, manager_id, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) ;

A．

B．

C．

D．

A．

B．

C．

D．

EPARTMENT_ID NUMBERMANAGER_ID NUMBERLAST_NAME VARCHAR2(25)DEPARTMENTSDEPARTMENT_ID NUMBERMANAGER_ID NUMBERDEPARTMENT_NAME VARCHAR2(35)LOCATION_ID NUMBERYou want to create a report displaying employee last names, department names, and locations. Which query should you use？（）

A.SELECT e.last_name, d. department_name, d.location_id FROM employees e NATURAL JOIN departments D USING department_id ;

B.SELECT last_name, department_name, location_id FROM employees NATURAL JOIN departments WHERE e.department_id =d.department_id;

C.SELECT e.last_name, d.department_name, d.location_id FROM employees e NATURAL JOIN departments d;

D.SELECT e.last_name, d.department_name, d.location_id FROM employees e JOIN departments d USING (department_id );