D ANSWERS TO THE EXERCISES in Font

Make ECC200 in Font D ANSWERS TO THE EXERCISES

5. List the names and initials of all employees, except for R. Jones. Solution 4-5a. Using Parentheses SQL> select ename, init 2 from employees 3 where not (ename = 'JONES' and init = 'R'); ENAME -------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS FORD MILLER INIT ----N JAM TF JM P R AB SCJ CC JJ AA MG TJA

13 rows selected. SQL> Solution 4-5b. Without Parentheses (Note the OR) SQL> select ename, init 2 from employees 3 where ename <> 'JONES' OR init <> 'R'; 6. Find the number, job, and date of birth of all trainers and sales representatives born before 1960. Solution 4-6a. First Solution SQL> 2 3 4 select from where and empno, job, bdate employees bdate < date '1960-01-01' job in ('TRAINER','SALESREP');

BDATE ----------28-SEP-1956 26-NOV-1959 13-FEB-1959

Here is an alternative solution; note the parentheses to force operator precedence. Solution 4-6b. Second Solution SQL> 2 3 4 select empno, job, bdate from employees where bdate < date '1960-01-01' and (job = 'TRAINER' or job = 'SALESREP');

7. List the numbers of all employees who do not work for the training department. Solution 4-7. SQL> select empno 2 from employees 3 where deptno <> (select deptno 4 from departments 5 where dname = 'TRAINING'); EMPNO -------7499 7521 7654 7698 7782 7839 7844 7900 7934 SQL>

8. List the numbers of all employees who did not attend the Java course. Solution 4-8a. Correct Solution SQL> select empno 2 from employees 3 where empno not in (select attendee 4 from registrations 5 where course = 'JAV'); EMPNO -------7369 7521 7654 7844 7900 7902 7934 SQL> The following two solutions are wrong. Solution 4-8b. Wrong Solution 1 SQL> select distinct attendee 2 from registrations 3 where attendee not in (select attendee 4 from registrations 5 where course = 'JAV'); ATTENDEE -------7521 7844 7900 7902 7934 SQL> This result shows only five employees because employees 7369 and 7654 never attended any course; therefore, their employee numbers do not occur in the REGISTRATIONS table.

Solution 4-8c. Wrong Solution 2 SQL> select distinct attendee attendee 2 from registrations 3 where course <> 'JAV'; ATTENDEE -------7499 7521 7566 7698 7788 7839 7844 7876 7900 7902 7934 11 rows selected. SQL> This result shows too many results, because it also shows employees who attended the Java course and at least one non-Java course; for example, employee 7566 attended the Java and the PL/SQL courses. 9a. Which employees have subordinates Solution 4-9a. Employees with Subordinates SQL> select empno, ename, init 2 from employees 3 where empno in (select mgr 4 from employees); EMPNO -------7566 7698 7782 7788 7839 7902 SQL> ENAME -------JONES BLAKE CLARK SCOTT KING FORD INIT ----JM R AB SCJ CC MG

9b. Which employees don t have subordinates Solution 4-9b. Employees Without Subordinates SQL> select empno, ename, init 2 from employees 3 where empno not in (select mgr 4 from employees 5 where mgr is not null); EMPNO -------7369 7499 7521 7654 7844 7876 7900 7934 SQL> ENAME -------SMITH ALLEN WARD MARTIN TURNER ADAMS JONES MILLER INIT ----N JAM TF P JJ AA R TJA

Note The WHERE clause on the fifth line of Solution 4-9b is necessary for a correct result,

assuming that null values in the MGR column always mean "not applicable." See also Solution 4-12.

10. Produce an overview of all general course offerings (course category GEN) in 1999. Solution 4-10. SQL> 2 3 4 5 6 7 select * from offerings where begindate between and and course in (select from where

date '1999-01-01' date '1999-12-31' code courses category = 'GEN');

BEGINDATE TRAINER LOCATION ----------- -------- -------10-AUG-1999 7566 CHICAGO 12-APR-1999 7902 DALLAS 04-OCT-1999 7369 SEATTLE 13-DEC-1999 7369 DALLAS