c# barcode image generation library ANSWERS TO THE EXERCISES in Java

Encode ECC200 in Java ANSWERS TO THE EXERCISES

3000 1300

SQL> select e.empno, e.ename, e.job, e.msal 2 from employees e 3 where e.msal > (select MAX(b.msal) 4 from employees b 5 where b.job = 'BARTENDER'); no rows selected SQL> This example searches for BARTENDER. The subquery returns an empty set, because the EMPLOYEES table contains no bartenders. Therefore, the > ALL condition of the first query is true for every row of the EMPLOYEES table. This outcome complies with an important law derived from mathematical logic. The following statement is always true, regardless of the expression you specify following the colon: For all elements x of the empty set:

This explains why you see all 14 employees in the result of the first query. The second query uses a different approach, using the MAX function in the subquery. The maximum of an empty set results in a null value, so the WHERE clause becomes WHERE E.MSAL > NULL, which returns unknown for every row. This explains why the second query returns no rows. 7. You saw a series of examples in this chapter about all employees that ever taught an SQL course (in Listings 9-9 through 9-11). How can you adapt these queries in such a way that they answer the negation of the same question ( all employees that never ) Solution 9-7a. Negation of Listing 9-9 SQL> select e.* 2 from employees e 3 where NOT exists (select 4 from 5 where 6 and EMPNO ----7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 ENAME -------ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS INIT ----JAM TF JM P R AB SCJ CC JJ AA

o.* offerings o o.course = 'SQL' o.trainer = e.empno);

JOB MGR BDATE MSAL COMM DEPTNO -------- ----- ----------- ----- ----- -----SALESREP 7698 20-FEB-1961 1600 300 30 SALESREP 7698 22-FEB-1962 1250 500 30 MANAGER 7839 02-APR-1967 2975 20 SALESREP 7698 28-SEP-1956 1250 1400 30 MANAGER 7839 01-NOV-1963 2850 30 MANAGER 7839 09-JUN-1965 2450 10 TRAINER 7566 26-NOV-1959 3000 20 DIRECTOR 17-NOV-1952 5000 10 SALESREP 7698 28-SEP-1968 1500 0 30 TRAINER 7788 30-DEC-1966 1100 20

7698 03-DEC-1969 7782 23-JAN-1962

800 1300

30 10

12 rows selected. SQL> Solution 9-7b. Negation of Listing 9-10 SQL> select e.* 2 from employees e 3 where e.empno NOT in (select o.trainer 4 from offerings o 5 where o.course = 'SQL'); EMPNO ----7499 7521 ... 7934 ENAME -------ALLEN WARD MILLER INIT ----JAM TF TJA JOB MGR BDATE MSAL COMM DEPTNO -------- ----- ----------- ----- ----- -----SALESREP 7698 20-FEB-1961 1600 300 30 SALESREP 7698 22-FEB-1962 1250 500 30 ADMIN 7782 23-JAN-1962 1300 10

12 rows selected. SQL> This looks good you get back the same 12 employees. However, you were lucky, because all SQL course offerings happen to have a trainer assigned. If you use the NOT IN and NOT EXISTS operators, you should always investigate whether your subquery could possibly produce null values and how they are handled. The following negation for Listing 9-11 is wrong. Solution 9-7c. Wrong Negation for Listing 9-11 SQL> select DISTINCT e.* 2 from employees e 3 join 4 offerings o 5 on e.empno = o.trainer 6 where o.course <> 'SQL'; EMPNO ----7369 7566 7788 7876 7902 SQL> ENAME -------SMITH JONES SCOTT ADAMS FORD INIT ----N JM SCJ AA MG JOB MGR BDATE MSAL COMM DEPTNO -------- ----- ----------- ----- ----- -----TRAINER 7902 17-DEC-1965 800 20 MANAGER 7839 02-APR-1967 2975 20 TRAINER 7566 26-NOV-1959 3000 20 TRAINER 7788 30-DEC-1966 1100 20 TRAINER 7566 13-FEB-1959 3000 20

It is not an easy task to transform this join solution into its negation. 8. Check out your solution for exercise 4 in 8: For all course offerings, list the course code, begin date, and number of registrations. Sort your results on the number of registrations, from high to low. Can you come up with a more elegant solution now, without using an outer join Solution 9-8. A More Elegant Solution for Exercise 4 in 8 SQL> 2 3 4 5 6 7 8 9 course begindate (select count(*) from registrations r where r.course = o.course and r.begindate = o.begindate) as registrations from offerings o order by registrations; BEGINDATE REGISTRATIONS ----------- ------------15-JAN-2001 0 19-FEB-2001 0 18-SEP-2000 0 24-FEB-2001 0 27-SEP-2000 1 13-DEC-1999 2 03-FEB-2000 2 01-FEB-2000 3 04-OCT-1999 3 11-SEP-2000 3 10-AUG-1999 3 12-APR-1999 4 13-DEC-1999 5 select , ,