Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

APPENDIX B. Answers to the Exercises

This appendix provides answers and solutions to the chapter-ending exercises presented earlier in this book. In some cases, we have presented multiple (alternative) solutions for a single exercise. Sometimes you will see warnings for possible incorrect solutions, in case of known pitfalls.

Of course, it is impossible to list all correct solutions for each exercise; the SQL language is too rich (or redundant?) for such an attempt. This implies that it is perfectly possible for you to approach and solve certain exercises in a completely different way. In that case, you can compare your results with the results listed in this appendix. However, always keep the following warning in mind.

Caution Although a query may produce the correct result, this doesn’t imply that you wrote the right query. Incorrect SQL statements sometimes produce the correct results by accident. These are the most treacherous queries, because they can start producing wrong results at any point in the future, based on the actual contents of the tables involved.

Some exercises in this book are quite tough. For some of them, it may be challenging to fully appreciate and understand the given solutions. The reasoning behind including such exercises is the following: to test your SQL knowledge, you can look at the abundance of relatively simple examples in Oracle’s online documentation – the Oracle Database SQL Language Reference, and you can easily come up with simple SQL experiments yourself.

Chapter 1, 2 and 3: No exercises

Chapter 4 Exercises

1. Provide the code and description of all courses with an exact duration of four days.

Solution 4-1.

SQL> select code, description
2 from courses
3 where duration = 4;
CODE DESCRIPTION
---- ------------------------------
SQL Introduction to SQL
JAV Java for Oracle developers
GEN System generation

2. List all employees, sorted by job, and per job by age (from young to old).

Solution 4-2.

SQL> select *
2 from employees
3 order by job, bdate desc;

EMPNO ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
----- -------- ----- -------- ----- ----------- ----- ----- -------
7900 JONES R ADMIN 7698 03-DEC-1969 800 30
7934 MILLER TJA ADMIN 7782 23-JAN-1962 1300 10
7839 KING CC DIRECTOR 17-NOV-1952 5000 10
7566 JONES JM MANAGER 7839 02-APR-1967 2975 20
7782 CLARK AB MANAGER 7839 09-JUN-1965 2450 10
7698 BLAKE R MANAGER 7839 01-NOV-1963 2850 30
7844 TURNER JJ SALESREP 7698 28-SEP-1968 1500 0 30
7521 WARD TF SALESREP 7698 22-FEB-1962 1250 500 30
7499 ALLEN JAM SALESREP 7698 20-FEB-1961 1600 300 30
7654 MARTIN P SALESREP 7698 28-SEP-1956 1250 1400 30
7876 ADAMS AA TRAINER 7788 30-DEC-1966 1100 20
7369 SMITH N TRAINER 7902 17-DEC-1965 800 20
7788 SCOTT SCJ TRAINER 7566 26-NOV-1959 3000 20
7902 FORD MG TRAINER 7566 13-FEB-1959 3000 20

14 rows selected.

3. Which courses have been held in Chicago and/or in Seattle?

Solution 4-3.

SQL> select distinct course
2 from offerings
3 where location in ('CHICAGO','SEATTLE');

COURSE
------
JAV
OAU
RSD
SQL
XML

Notice the DISTINCT keyword in the SELECT clause, to ensure that a course code doesn’t show up more than once. This way, you get the correct answer to the question if the same offering should be listed in both locations in the future.

4. Which employees attended both the Java course and the XML course? (Provide their employee numbers.)

Solution 4-4.

SQL> select attendee
2 from registrations
3 where course = 'JAV'
4 and attendee in (select attendee
5 from registrations
6 where course = 'XML');

ATTENDEE
--------
7499

You might want to add the DISTINCT keyword to the SELECT clause here, too, just as you did in the previous exercise; otherwise, what happens if someone attends the XML course once and attends the Java course twice?

This fourth exercise has many different solutions. For example, you can also use two subqueries instead of one. Obviously, the following solutions with AND or OR at the row level are wrong:

where course = 'JAV' and course = 'XML' -- Wrong: Gives "no rows selected."
where course = 'JAV' or course = 'XML' -- Wrong: Gives too many results.

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 INIT
-------- -----
SMITH N
ALLEN JAM
WARD TF
JONES JM
MARTIN P
BLAKE R
CLARK AB
SCOTT SCJ
KING CC
TURNER JJ
ADAMS AA
FORD MG
MILLER TJA

13 rows selected.

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> select empno, job, bdate
2 from employees
3 where bdate < date '1960-01-01'
4 and job in ('TRAINER','SALESREP');

EMPNO JOB BDATE
-------- -------- -----------
7654 SALESREP 28-SEP-1956
7788 TRAINER 26-NOV-1959
7902 TRAINER 13-FEB-1959

Here is an alternative solution; note the parentheses to force operator precedence.

Solution 4-6b. Second Solution

SQL> select empno, job, bdate
2 from employees
3 where bdate < date '1960-01-01'
4 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

Note This solution assumes that there is only one training department. And, indeed, in this table the DNAME column has a unique constraint. You could also use NOT IN instead of <>.

8. List the employee 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

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

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. So this is the answer to the question: Who attended courses other than the Java course?

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.

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.

9. a. 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 ENAME INIT
-------- -------- -----
7566 JONES JM
7698 BLAKE R
7782 CLARK AB
7788 SCOTT SCJ
7839 KING CC
7902 FORD 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 ENAME INIT
-------- -------- -----
7369 SMITH N
7499 ALLEN JAM
7521 WARD TF
7654 MARTIN P
7844 TURNER JJ
7876 ADAMS AA
7900 JONES R
7934 MILLER 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 Solution 4-12, as well.

10.Produce an overview of all general course offerings (course category GEN) in 1999.

Solution 4-10.

SQL> select *
2 from offerings
3 where begindate between date '1999-01-01'
4 and date '1999-12-31'
5 and course in (select code
6 from courses
7 where category = 'GEN');

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

SQL>

You can solve the “1999 condition” in many ways by using SQL functions (see Chapter 5). Here are some valid alternatives for lines 3 and 4:

where to_char(begindate,'YYYY') = '1999'
where extract(year from begindate) = 1999
where begindate between to_date('01-JAN-1999','DD-MON-YYYY')
and to_date('31-DEC-1999','DD-MON-YYYY')

Caution Avoid using column names as function arguments if it is possible to express the same functional result without having to do that, because it may have a negative impact on performance. In this case, Solution 4-10 and the last alternative are fine; the first two alternatives should be avoided.

11.Provide the name and initials of all employees who have ever attended a course taught by N. Smith. Hint: Use subqueries, and work “inside out” toward the result; that is, retrieve the employee number of N. Smith, search for the codes of all courses he ever taught, and so on.

Solution 4-11.

SQL> select ename, init
2 from employees
3 where empno in
4 (select attendee
5 from registrations
6 where (course, begindate) in
7 (select course, begindate
8 from offerings
9 where trainer =
10 (select empno
11 from employees
12 where ename = 'SMITH'
13 and init = 'N'
14 )
15 )
16 );

ENAME INIT
-------- -----
ALLEN JAM
BLAKE R
SCOTT SCJ
KING CC
JONES R
FORD MG

12.How could you redesign the EMPLOYEES table to avoid the problem that the COMM column contains null values meaning not applicable?

Answer: By dropping that column from the EMPLOYEES table and by creating a separate SALESREPS table, with the following rows:

EMPNO COMM
-------- --------
7499 300
7521 500
7654 1400
7844 0

In this table, the EMPNO column is not only the primary key, but it is also a foreign key referring to the EMPLOYEES table.

13.In Section 4.9, you saw the following statement: In SQL, NOT is not “not.” What is this statement trying to say?

Answer: In three-valued logic, the NOT operator is not the complement operator anymore:

NOT TRUE is equivalent with FALSE
not TRUE is equivalent with FALSE OR UNKNOWN

Referring to the brain-twister at the end of Section 4.9, what is the explanation of the result “no rows selected” in Listing 4-43?

Answer: The following WHERE clause:

2 where evaluation not in (1,2,3,NULL)

is logically equivalent with the following “iterated AND” condition:

2 where evaluation <> 1
3 AND evaluation <> 2
4 AND evaluation <> 3
5 AND evaluation <> NULL

If you consider a row with an EVALUATION value of 1, 2, or 3, it is obvious that out of the first three conditions, one of them returns FALSE, and the other two return TRUE. Therefore, the complete WHERE clause returns FALSE.

If the EVALUATION value is NULL, all four conditions return UNKNOWN. Therefore, the end result is also UNKNOWN. So far, there are no surprises.

If the EVALUATION value is 4 or 5 (the remaining two allowed values), the first three conditions all return TRUE, but the last condition returns UNKNOWN. So you have the following expression:

(TRUE) and (TRUE) and (TRUE) and (UNKNOWN)

This is logically equivalent with UNKNOWN, so the complete WHERE clause returns UNKNOWN.

14.At the end of Section 4.5, you saw the following statement.

The following two queries are logically equivalent:

select * from employees where NOT (ename = 'BLAKE' AND init = 'R')
select * from employees where ename <> 'BLAKE' OR init <> 'R'

Prove this, using a truth table.

Answer: First, we assign names to the two WHERE clause components.

· Let’s represent ename = 'BLAKE' with P.

· Let’s represent init = 'R' with Q.

Then we must show that NOT(P AND Q) and NOT(P) OR NOT(Q) are logically equivalent. The truth tables for both expressions look as follows:

As you can see, the last columns in the two truth tables are identical. This proves that the two expressions are logically equivalent.

Chapter 5 Exercises

1. For all employees, provide their last name, a comma, followed by their initials.

Solution 5-1.

SQL> select ename ||', '||init
2 as full_name
3 from employees;

FULL_NAME
---------------
SMITH, N
ALLEN, JAM
WARD, TF
JONES, JM
MARTIN, P
BLAKE, R
CLARK, AB
SCOTT, SCJ
KING, CC
TURNER, JJ
ADAMS, AA
JONES, R
FORD, MG
MILLER, TJA

14 rows selected.

SQL>

2. For all employees, list their last name and date of birth, in a format such as April 2nd, 1967.

Solution 5-2.

SQL> select ename
2 , to_char(bdate,'fmMonth ddth, yyyy')
3 from employees;

ENAME TO_CHAR(BDATE,'FMMON
-------- --------------------
SMITH December 17th, 1965
ALLEN February 20th, 1961
WARD February 22nd, 1962
JONES April 2nd, 1967
MARTIN September 28th, 1956
BLAKE November 1st, 1963
CLARK June 9th, 1965
SCOTT November 26th, 1959
KING November 17th, 1952
TURNER September 28th, 1968
ADAMS December 30th, 1966
JONES December 3rd, 1969
FORD February 13th, 1959
MILLER January 23rd, 1962

14 rows selected.

SQL>

Note You can change the language to display the month names in this result with the NLS_LANGUAGE parameter setting, as in this example:

SQL> alter session set nls_language=dutch;

Sessie is gewijzigd.

SQL>

3. a. On which day are (or were) you exactly 10,000 days old?

Solution 5-3a.

SQL> select date '1954-08-11' + 10000
2 as "10,000 days"
3 from dual;

10,000 days
-----------
27-DEC-1981

SQL>

9b. On which day of the week is (was) this?

Solution 5-3b.

SQL> select to_char(date '1954-08-11' + 10000,'Day')
2 as "On a:"
3 from dual;

On a:
---------
Sunday

SQL>

4. Rewrite the example in Listing 5-23 using the NVL2 function.

Solution 5-4.

SQL> select ename, msal, comm
2 , nvl2(comm,12*msal+comm,12*msal) as yearsal
3 from employees
4 where ename like '%T%';
ENAME MSAL COMM YEARSAL
-------- -------- -------- --------
SMITH 800 9600
MARTIN 1250 1400 16400
SCOTT 3000 36000
TURNER 1500 0 18000

SQL>

5. Rewrite the example in Listing 5-24 to remove the DECODE functions using CASE expressions, both in the SELECT clause and in the ORDER BY clause.

Solution 5-5.

SQL> select job, ename
2 , case
3 when msal <= 2500
4 then 'cheap'
5 else 'expensive'
6 end as class
7 from employees
8 where bdate < date '1964-01-01'
9 order by case job
10 when 'DIRECTOR' then 1
11 when 'MANAGER' then 2
12 else 3
13 end;

JOB ENAME CLASS
-------- -------- ---------
DIRECTOR KING expensive
MANAGER BLAKE expensive
SALESREP ALLEN cheap
SALESREP WARD cheap
ADMIN MILLER cheap
TRAINER FORD expensive
TRAINER SCOTT expensive
SALESREP MARTIN cheap

SQL>

Note The TO_DATE function expression is also replaced by a DATE literal.

6. Rewrite the example in Listing 5-20 using DATE and INTERVAL constants, in such a way that they become independent of the NLS_DATE_FORMAT setting.

Solution 5-6.

SQL> select date '1996-01-29' + interval '1' month as col_1
2 , date '1997-01-29' + interval '1' month as col_2
3 , date '1997-08-11' - interval '3' month as col_3
4 from dual;
, date '1997-01-29' + interval '1' month as col_2
*
ERROR at line 2:
ORA-01839: date not valid for month specified

SQL> select date '1996-01-29' + interval '1' month as col_1
2 , date '1997-01-28' + interval '1' month as col_2
3 , date '1997-08-11' - interval '3' month as col_3
4 from dual;

COL_1 COL_2 COL_3
----------- ----------- ---------
29-FEB-1996 28-FEB-1997 11-MAY-1997

SQL>

As you can see, January 29 plus a month causes problems for 1997, which is not a leap year. If you change 1997-01-29 to 1997-01-28 on the second line, there is no longer a problem.

7. Investigate the difference between the date formats WW and IW (week number and ISO week number) using an arbitrary date, and explain your findings.

Solution 5-7.

SQL> 1 select date '2005-01-01' as input_date
2 , to_char(date '2005-01-01', 'ww') as ww
3 , to_char(date '2005-01-01', 'iw') as iw
4* from dual

INPUT_DATE WW IW
----------- -- --
01-JAN-2005 06 07

SQL>

If you don’t get different results, try different dates within the same week. The difference between WW and IW has to do with the different definitions of week numbers. The WW format starts week number 1 on January 1, regardless of which day of the week that is. The ISO standard uses different rules: an ISO week always starts on a Monday. The rules around the new year are as follows: if January 1 is a Friday, a Saturday, or a Sunday, the week belongs to the previous year; otherwise, the week fully belongs to the new year. Similar rules apply for the ISO year numbering.

8. Look at Listing 5-15, where we use the REGEXP_INSTR function to search for words. Rewrite this query using REGEXP_LIKE. Hint: You can use {n,} to express “at least n times.”

Solution 5-8a. First Solution

SQL> select comments
2 from history
3 where regexp_like(comments, '([^ ]+ ){8,}');

COMMENTS
------------------------------------------------------------
Not a great trainer; let's try the sales department!
Sales also turns out to be not a success...
Hired as the new manager for the accounting department
Junior sales rep -- has lots to learn... :-)

SQL>

You could make your solution more readable by using character classes.

Solution 5-8b. Second Solution, Using Character Classes

SQL> select comments
2 from history
3 where regexp_like(comments, '([[:alnum:]+[:punct:]]+[[:space:]]+){8,}');

COMMENTS
------------------------------------------------------------
Not a great trainer; let's try the sales department!
Sales also turns out to be not a success...
Hired as the new manager for the accounting department
Junior sales rep -- has lots to learn... :-)

SQL>

Chapter 6: No exercises.

Chapter 7 Exercises

1. Listing 7-5 defines the constraint E_SALES_CHK in a rather cryptic way. Formulate the same constraint without using DECODE and NVL2.

Solution 7-1a. Solution 1

check ((job = 'SALESREP' and comm is not null) or
(job <>'SALESREP' and comm is null) )

Solution 7-1b. Solution 2

check ((job = 'SALESREP' or comm is null) and not
(job = 'SALESREP' and comm is null) )

2. Why do you think the constraint E_DEPT_FK (in Listing 7-7) is created with a separate ALTER TABLE command?

Answer: You must define this constraint with an ALTER TABLE command because you have a “chicken/egg” problem. A foreign key constraint can refer to only an existing table, and you have two tables (EMPLOYEES and DEPARTMENTS) referring to each other.

3. Although this is not covered in this chapter, try to come up with an explanation of the following phenomenon: when using sequences, you cannot use the pseudo column CURRVAL in your session without first calling the pseudo column NEXTVAL.

Answer: In a multiuser environment, multiple database users can use the same sequence generator at the same time. Therefore, they will be using differentCURRVAL values at the same time; that is, there is no database-wide “current” CURRVAL value. On the other hand, NEXTVAL is always defined as the next available sequence value.

4. Why is it better to use sequences in a multiuser environment, as opposed to maintaining a secondary table with the last/current sequence values?

Answer: A secondary table will become a performance bottleneck. Each update to a sequence value will lock the corresponding row. The next update can take place only after the first transaction has committed. In other words, all transactions needing a sequence value will be serialized. Sequences are better because they don’t have this problem. With sequences, multiple transactions can be served simultaneously and independently.

5. How is it possible that the EVALUATION column of the REGISTRATIONS table accepts null values, in spite of the constraint R_EVAL_CHK (see Listing 7-11)?

Answer: This is caused by three-valued logic. A CHECK constraint condition can result in TRUE, FALSE, or UNKNOWN. Moreover, a CHECK constraint reports a violation only if its corresponding condition returns FALSE.

Note This implies that you always need an explicit NOT NULL constraint if you want your columns to be mandatory; a CHECK constraint as shown in Listing 7-11 is not enough.

6. If you define a PRIMARY KEY or UNIQUE constraint, the Oracle DBMS normally creates a unique index under the covers (if none of the existing indexes can be used) to check the constraint. Investigate and explain what happens if you define such a constraint asDEFERRABLE.

Answer: If you define PRIMARY KEY or UNIQUE constraints as DEFERRABLE, the Oracle DBMS creates nonunique indexes. This is because indexes must be maintained immediately. Therefore, indexes for deferrable constraints must allow for temporary duplicate values until the end of your transactions.

7. You can use function-based indexes to implement “conditional uniqueness” constraints. Create a unique function-based index on the REGISTRATIONS table to check the following constraint: employees are allowed to attend the OAU course only once. They may attend other courses as many times as they like. Test your solution with the following command (it should fail):

SQL> insert into registrations values (7900,'OAU',trunc(sysdate),null);

Hint: You can use a CASE expression in the index expression.

Solution 7-7.

SQL> create unique index oau_reg on registrations
2 ( case course when 'OAU' then attendee else null end
3 , case course when 'OAU' then course else null end );

Index created.

SQL>

The trick is to create a function-based index on (ATTENDEE, COURSE) combinations, while ignoring all non-OAU course registrations.

Here’s the test:

SQL> insert into registrations values (7900,'OAU',sysdate,null);
insert into registrations values (7900,'OAU',sysdate,null)
*
ERROR at line 1:
ORA-00001: unique constraint (BOOK.OAU_REG) violated

SQL>

Note Notice the Oracle error message number for the unique constraint violation: 00001. This must have been one of the first error messages implemented in Oracle!

Chapter 8 Exercises

1. Produce an overview of all course offerings. Provide the course code, begin date, course duration, and name of the trainer.

Solution 8-1a. First Solution

SQL> select c.code
2 , o.begindate
3 , c.duration
4 , e.ename as trainer
5 from employees e
6 , courses c
7 , offerings o
8 where o.trainer = e.empno
9 and o.course = c.code;

CODE BEGINDATE DURATION TRAINER
---- ----------- -------- --------
XML 03-FEB-2000 2 SMITH
SQL 13-DEC-1999 4 SMITH
SQL 04-OCT-1999 4 SMITH
OAU 10-AUG-1999 1 JONES
JAV 13-DEC-1999 4 JONES
RSD 24-FEB-2001 2 SCOTT
PLS 11-SEP-2000 1 SCOTT
JAV 01-FEB-2000 4 ADAMS
SQL 12-APR-1999 4 FORD
OAU 27-SEP-2000 1 FORD

10 rows selected.

SQL>

If you also want to see all course offerings with an unknown trainer, you can change the solution as follows:

Solution 8-1b. Second Solution, Also Showing Course Offerings with Unknown Trainers

SQL> select DISTINCT c.code
2 , o.begindate
3 , c.duration
4 , case when o.trainer is not null
5 then e.ename
6 else null
7 end as trainer
8 from employees e
9 , courses c
10 , offerings o
11 where coalesce(o.trainer,-1) in (e.empno,-1)
12 and o.course = c.code;

CODE BEGINDATE DURATION TRAINER
---- ----------- -------- --------
ERM 15-JAN-2001 3
JAV 13-DEC-1999 4 JONES
JAV 01-FEB-2000 4 ADAMS
OAU 10-AUG-1999 1 JONES
OAU 27-SEP-2000 1 FORD
PLS 11-SEP-2000 1 SCOTT
PRO 19-FEB-2001 5
RSD 24-FEB-2001 2 SCOTT
SQL 12-APR-1999 4 FORD
SQL 04-OCT-1999 4 SMITH
SQL 13-DEC-1999 4 SMITH
XML 03-FEB-2000 2 SMITH
XML 18-SEP-2000 2

13 rows selected.

SQL>

Line 11 might look curious at first sight. It “relaxes” the join between OFFERINGS and EMPLOYEES a bit. Instead of –1, you can use any other arbitrary numeric value, as long as it could not be an existing employee number. Note also that this trick makes the addition of DISTINCT necessary.

2. Provide an overview, in two columns, showing the names of all employees who ever attended an SQL course, with the name of the trainer.

Solution 8-2.

SQL> select a.ename as attendee
2 , t.ename as trainer
3 from employees t
4 join
5 offerings o on (o.trainer = t.empno)
6 join
7 registrations r using (course, begindate)
8 join
9 employees a on (r.attendee = a.empno)
10 where course = 'SQL';

ATTENDEE TRAINER
-------- --------
ALLEN FORD
BLAKE FORD
ADAMS FORD
MILLER FORD
SCOTT SMITH
KING SMITH
FORD SMITH
BLAKE SMITH
FORD SMITH

SQL>

This solution uses the new ANSI/ISO join syntax, just for a change.

3. For all employees, list their name, initials, and yearly salary (including bonus and commission).

Solution 8-3.

SQL> select e.ename, e.init
2 , 12 * (e.msal + s.bonus)
3 + nvl(e.comm,0) as yearsal
4 from employees e
5 join
6 salgrades s
7 on (e.msal between s.lowerlimit
8 and s.upperlimit);

ENAME INIT YEARSAL
-------- ----- --------
SMITH N 9600
JONES R 9600
ADAMS AA 13200
WARD TF 16100
MARTIN P 17000
MILLER TJA 16200
TURNER JJ 19200
ALLEN JAM 20700
CLARK AB 31800
BLAKE R 36600
JONES JM 38100
SCOTT SCJ 38400
FORD MG 38400
KING CC 66000

14 rows selected.

SQL>

4. 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.

Solution 8-4.

SQL> select course
2 , begindate
3 , count(r.attendee) as reg_count
4 from offerings o
5 left outer join
6 registrations r
7 using (course, begindate)
8 group by course
9 , begindate
10 order by reg_count desc;

COURSE BEGINDATE REG_COUNT
------ ----------- ---------
JAV 13-DEC-1999 5
SQL 12-APR-1999 4
JAV 01-FEB-2000 3
OAU 10-AUG-1999 3
PLS 11-SEP-2000 3
SQL 04-OCT-1999 3
SQL 13-DEC-1999 2
XML 03-FEB-2000 2
OAU 27-SEP-2000 1
ERM 15-JAN-2001 0
XML 18-SEP-2000 0
PRO 19-FEB-2001 0
RSD 24-FEB-2001 0

13 rows selected.

SQL>

You need an outer join here, to see all courses without registrations in the result as well. Note also that COUNT(*) in the third line would give you wrong results.

5. List the course code, begin date, and the number of registrations for all course offerings in 1999 with at least three registrations.

Solution 8-5.

SQL> select course
2 , begindate
3 , count(*)
4 from registrations
5 where extract(year from begindate) = 1999
6 group by course
7 , begindate
8 having count(*) >= 3;

COURSE BEGINDATE COUNT(*)
------ ----------- --------
JAV 13-DEC-1999 5
OAU 10-AUG-1999 3
SQL 12-APR-1999 4
SQL 04-OCT-1999 3

SQL>

In this case, accessing the REGISTRATIONS table is enough, because you are not interested in offerings without registrations. The solution would have been more complicated if the question were “... with fewer than three registrations,” because zero is also less than three.

6. Provide the employee numbers of all employees who ever taught a course as a trainer, but never attended a course as an attendee.

Solution 8-6a. First Solution

SQL> select trainer from offerings
2 minus
3 select attendee from registrations;

TRAINER
--------
7369

SQL>

This solution looks good; however, if you look very carefully, the solution is suspect. You don’t see it immediately, but this result doesn’t contain a single row, but two rows, as becomes apparent if you set FEEDBACK to 1:

SQL> set feedback 1
SQL> /

TRAINER
--------
7369

2 rows selected.

SQL>

Because a null value obviously doesn’t represent a valid trainer, you need to exclude null values in the TRAINER column explicitly.

Solution 8-6b. Second Solution, Excluding Null Values

SQL> select trainer from offerings
2 where trainer is not null
3 minus
4 select attendee from registrations;

TRAINER
--------
7369

1 row selected.

SQL>

7. Which employees attended a specific course more than once?

Solution 8-7.

SQL> select attendee,course
2 from registrations
3 group by attendee,course
4 having count(*) > 1 ;

ATTENDEE COURSE
-------- ------
7698 SQL
7788 JAV
7902 SQL

SQL>

8. For all trainers, provide their name and initials, the number of courses they taught, the total number of students they had in their classes, and the average evaluation rating. Round the evaluation ratings to one decimal.

Solution 8-8.

SQL> select t.init, t.ename
2 , count(distinct begindate) courses
3 , count(*) attendees
4 , round(avg(evaluation),1) evaluation
5 from employees t
6 , registrations r
7 join
8 offerings o
9 using (course, begindate)
10 where t.empno = o.trainer
11 group by t.init, t.ename;

INIT ENAME COURSES ATTENDEES EVALUATION
----- -------- -------- --------- ----------
N SMITH 3 7 4
AA ADAMS 1 3 4
JM JONES 2 8 4.3
MG FORD 2 5 4
SCJ SCOTT 1 3

SQL>

Note While counting courses, this solution assumes that trainers cannot teach more than one course on the same day.

9. List the name and initials of all trainers who ever had their own manager as a student in a general course (category GEN).

Solution 8-9.

SQL> select distinct e.ename, e.init
2 from employees e
3 , courses c
4 , offerings o
5 , registrations r
6 where e.empno = o.trainer
7 and e.mgr = r.attendee
8 and c.code = o.course
9 and o.course = r.course
10 and o.begindate = r.begindate
11 and c.category = 'GEN';

ENAME INIT
-------- -----
SMITH N

SQL>

10.Did we ever use two classrooms at the same time in the same course location?

Solution 8-10.

SQL> select o1.location
2 , o1.begindate, o1.course, c1.duration
3 , o2.begindate, o2.course
4 from offerings o1
5 , offerings o2
6 , courses c1
7 where o1.location = o2.location
8 and (o1.begindate < o2.begindate or
9 o1.course <> o2.course )
10 and o1.course = c1.code
11 and o2.begindate between o1.begindate
12 and o1.begindate + c1.duration;

LOCATION BEGINDATE COUR DURATION BEGINDATE COURSE
-------- ----------- ---- -------- ----------- ------
DALLAS 01-FEB-2000 JAV 4 03-FEB-2000 XML

SQL>

The solution searches for two different course offerings (see lines 8 and 9) at the same location (see line 7) overlapping each other (see lines 11 and 12). Apparently, the Java course starting February 1, 2000, in Dallas overlaps with the XML course starting two days later (note that the Java course takes four days).

11.Produce a matrix report (one column per department, one row for each job) where each cell shows the number of employees for a specific department and a specific job. In a single SQL statement, it is impossible to dynamically derive the number of columns needed, so you may assume you have three departments only: 10, 20, and 30.

Solution 8-11.

SQL> select job
2 , count(case
3 when deptno <> 10
4 then null
5 else deptno
6 end ) as dept_10
7 , sum(case deptno
8 when 20
9 then 1
10 else 0
11 end ) as dept_20
12 , sum(decode(deptno,30,1,0)) as dept_30
13 from employees
14 group by job;

JOB DEPT_10 DEPT_20 DEPT_30
-------- -------- -------- --------
ADMIN 1 0 1
DIRECTOR 1 0 0
MANAGER 1 1 1
SALESREP 0 0 4
TRAINER 0 4 0

SQL>

This solution shows three different valid methods to count the employees: for department 10, it uses a searched CASE expression; for department 20, it uses a simple CASE expression and a SUM function; and for department 30, it uses the Oracle DECODE function, which is essentially the same solution as for department 20.

12.Listing 8-26 produces information about all departments with more than four employees. How can you change the query to show information about all departments with fewer than four employees?

Solution 8-12a. Incorrect Solution

SQL> select deptno, count(empno)
2 from employees
3 group by deptno
4 having count(*) < 4;

DEPTNO COUNT(EMPNO)
-------- ------------
10 3

SQL>

This solution is not correct, because it does not show departments with zero employees. You can fix this in several ways; for example, by using an outer join.

Solution 8-12b. Correct Solution

SQL> select deptno, count(empno)
2 from departments
3 left outer join
4 employees
5 using (deptno)
6 group by deptno
7 having count(*) < 4;

DEPTNO COUNT(EMPNO)
-------- ------------
10 3
40 0

SQL>

13.Look at Listings 8-44 and 8-45. Are those two queries logically equivalent? Investigate the two queries and explain the differences, if any.

Solution 8-13. Making the Difference Visible with FEEDBACK

SQL> set feedback 1

SQL> select o.location from offerings o
2 MINUS
3 select d.location from departments d;

LOCATION
--------
SEATTLE

2 rows selected.

SQL> select DISTINCT o.location
2 from offerings o
3 where o.location not in
4 (select d.location
5 from departments d);

LOCATION
--------
SEATTLE

1 row selected.

SQL>

If you change the SQL*Plus FEEDBACK setting to 1, the difference becomes apparent.

We have one course offering with unknown location, and (as you know by now) you cannot be too careful with null values. The first query produces two rows. The null value appears in the result because the MINUS operator does not remove the null value. However, if the second query checks the ERM course offering (with the null value) the WHERE clause becomes:

... where NULL not in ('NEW YORK','DALLAS','CHICAGO','BOSTON');

This WHERE clause returns UNKNOWN. Therefore, the row does not pass the WHERE clause filter, and as a consequence the result contains only one row.

Chapter 9 Exercises

1. It is normal practice that (junior) trainers always attend a course taught by a senior colleague before teaching that course themselves. For which trainer/course combinations did this happen?

Solution 9-1.

SQL> select o.course, o.trainer
2 from offerings o
3 where exists
4 (select r.*
5 from registrations r
6 where r.attendee = o.trainer
7 and r.course = o.course
8 and r.begindate < o.begindate)
9 and not exists
10 (select fo.*
11 from offerings fo
12 where fo.course = o.course
13 and fo.trainer = o.trainer
14 and fo.begindate < o.begindate);

COURSE TRAINER
------ --------
JAV 7876
OAU 7902

This exercise is not an easy one. You can solve it in many ways. The solution shown here uses the EXISTS and the NOT EXISTS operators. You can read it as follows:

“Search course offerings for which (1) the trainer attended an earlier offering of the same course as a student, and for which (2) the trainer is teaching that course for the first time.”

Note The second condition is necessary, because otherwise you would also get “teach/attend/teach” combinations.

2. Actually, if the junior trainer teaches a course for the first time, that senior colleague (see the previous exercise) sits in the back of the classroom in a supporting role. Try to find these course/junior/senior combinations.

Solution 9-2.

SQL> select o1.course
2 , o1.trainer as senior
3 , o2.trainer as junior
4 from offerings o1
5 , registrations r1
6 , offerings o2
7 , registrations r2
8 where o1.course = r1.course -- join r1 with o1
9 and o1.begindate = r1.begindate
10 and o2.course = r2.course -- join r2 with o2
11 and o2.begindate = r2.begindate
12 and o1.course = o2.course -- o1 and o2 same course
13 and o1.begindate < o2.begindate -- o1 earlier than o2
14 and o1.trainer = r2.attendee -- trainer o1 attends o2
15 and o2.trainer = r1.attendee -- trainer o2 attends o1
16 ;

COURSE SENIOR JUNIOR
------ -------- --------
JAV 7566 7876

This solution uses a join, for a change.

3. Which employees never taught a course?

Solution 9-3a. Using NOT IN

SQL> select e.*
2 from employees e
3 where e.empno not in (select o.trainer
4 from offerings o);

no rows selected

Solution 9-3b. Using NOT EXISTS

SQL> select e.*
2 from employees e
3 where not exists (select o.trainer
4 from offerings o
5 where o.trainer = e.empno);

EMPNO ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
----- -------- ----- -------- ----- ----------- ----- ----- ------
7499 ALLEN JAM SALESREP 7698 20-FEB-1961 1600 300 30
7521 WARD TF SALESREP 7698 22-FEB-1962 1250 500 30
7654 MARTIN P SALESREP 7698 28-SEP-1956 1250 1400 30
7698 BLAKE R MANAGER 7839 01-NOV-1963 2850 30
7782 CLARK AB MANAGER 7839 09-JUN-1965 2450 10
7839 KING CC DIRECTOR 17-NOV-1952 5000 10
7844 TURNER JJ SALESREP 7698 28-SEP-1968 1500 0 30
7900 JONES R ADMIN 7698 03-DEC-1969 800 30
7934 MILLER TJA ADMIN 7782 23-JAN-1962 1300 10

9 rows selected.

At first sight, you might think that both of these solutions are correct. However, the results are different. Now, which one is the correct solution?

You can come up with convincing arguments for both solutions. Note that you have three course offerings with a null value in the TRAINER column.

· If you interpret these null values as “trainer unknown,” you can never say with certainty that an employee never taught a course.

· The second query obviously treats the null values differently. Its result (with nine employees) is what you probably expected.

The different results are not caused by an SQL bug. You simply have two SQL statements with different results, so they must have a different meaning. In such cases, you must revisit the query in natural language and try to formulate it more precisely in order to eliminate any ambiguities.

Last but not least, our OFFERINGS table happens to contain only data from the past. If you want a correct answer to this exercise under all circumstances, you should also add a condition to check the course dates against SYSDATE.

4. Which employees attended all build courses (category BLD)? They are entitled to get a discount on the next course they attend.

Solution 9-4a. Using NOT EXISTS Twice

SQL> select e.empno, e.ename, e.init
2 from employees e
3 where not exists
4 (select c.*
5 from courses c
6 where c.category = 'BLD'
7 and not exists
8 (select r.*
9 from registrations r
10 where r.course = c.code
11 and r.attendee = e.empno
12 )
13 );

EMPNO ENAME INIT
-------- -------- -----
7499 ALLEN JAM

Solution 9-4b. Using GROUP BY

SQL> select e.empno, e.ename, e.init
2 from registrations r
3 join
4 courses c on (r.course = c.code)
5 join
6 employees e on (r.attendee = e.empno)
7 where c.category = 'BLD'
8 group by e.empno, e.ename, e.init
9 having count(distinct r.course)
10 = (select count(*)
11 from courses
12 where category = 'BLD');

EMPNO ENAME INIT
-------- -------- -----
7499 ALLEN JAM

This is not an easy exercise. Both of these solutions are correct.

5. Provide a list of all employees having the same monthly salary and commission as (at least) one employee of department 30. You are interested in only employees from other departments.

Solution 9-5.

SQL> select e.ename
2 , e.msal
3 , e.comm
4 from employees e
5 where e.deptno <> 30
6 and ( e.msal,coalesce(e.comm,-1) ) in
7 (select x.msal,coalesce(x.comm,-1)
8 from employees x
9 where x.deptno = 30 );

ENAME MSAL COMM
-------- -------- --------
SMITH 800

Note that this solution uses the COALESCE function, which you need to make comparisons with null values evaluate to true, in this case. The solution uses the value –1 based on the reasonable assumption that the commission column never contains negative values. However, if you check the definition of the EMPLOYEES table, you will see that there actually is no constraint to allow only nonnegative commission values. It looks like you found a possible data model enhancement here. Such a constraint would make your solution—using the negative value in the COALESCE function—correct under all circumstances.

6. Look again at Listings 9-4 and 9-5. Are they really logically equivalent? Just for testing purposes, search on a nonexisting job and execute both queries again. Explain the results.

Solution 9-6.

SQL> select e.empno, e.ename, e.job, e.msal
2 from employees e
3 where e.msal > ALL (select b.msal
4 from employees b
5 where b.job = 'BARTENDER');

EMPNO ENAME JOB MSAL
-------- -------- -------- --------
7369 SMITH TRAINER 800
7499 ALLEN SALESREP 1600
7521 WARD SALESREP 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESREP 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT TRAINER 3000
7839 KING DIRECTOR 5000
7844 TURNER SALESREP 1500
7876 ADAMS TRAINER 1100
7900 JONES ADMIN 800
7902 FORD TRAINER 3000
7934 MILLER ADMIN 1300

14 rows selected.

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

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 for 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 o.*
4 from offerings o
5 where o.course = 'SQL'
6 and o.trainer = e.empno);

EMPNO ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
----- -------- ----- -------- ----- ----------- ----- ----- ------
7499 ALLEN JAM SALESREP 7698 20-FEB-1961 1600 300 30
7521 WARD TF SALESREP 7698 22-FEB-1962 1250 500 30
7566 JONES JM MANAGER 7839 02-APR-1967 2975 20
7654 MARTIN P SALESREP 7698 28-SEP-1956 1250 1400 30
7698 BLAKE R MANAGER 7839 01-NOV-1963 2850 30
7782 CLARK AB MANAGER 7839 09-JUN-1965 2450 10
7788 SCOTT SCJ TRAINER 7566 26-NOV-1959 3000 20
7839 KING CC DIRECTOR 17-NOV-1952 5000 10
7844 TURNER JJ SALESREP 7698 28-SEP-1968 1500 0 30
7876 ADAMS AA TRAINER 7788 30-DEC-1966 1100 20
7900 JONES R ADMIN 7698 03-DEC-1969 800 30
7934 MILLER TJA ADMIN 7782 23-JAN-1962 1300 10

12 rows selected.

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 ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
----- -------- ----- -------- ----- ----------- ----- ----- ------
7499 ALLEN JAM SALESREP 7698 20-FEB-1961 1600 300 30
7521 WARD TF SALESREP 7698 22-FEB-1962 1250 500 30
...
7934 MILLER TJA ADMIN 7782 23-JAN-1962 1300 10

12 rows selected.

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 ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
----- -------- ----- -------- ----- ----------- ----- ----- ------
7369 SMITH N TRAINER 7902 17-DEC-1965 800 20
7566 JONES JM MANAGER 7839 02-APR-1967 2975 20
7788 SCOTT SCJ TRAINER 7566 26-NOV-1959 3000 20
7876 ADAMS AA TRAINER 7788 30-DEC-1966 1100 20
7902 FORD MG 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 Chapter 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 Chapter 8

SQL> select course
2 , begindate
3 , (select count(*)
4 from registrations r
5 where r.course = o.course
6 and r.begindate = o.begindate)
7 as registrations
8 from offerings o
9 order by registrations desc;

COURSE BEGINDATE REGISTRATIONS
------ ----------- -------------
------ --------- -------------
JAV 13-DEC-99 5
SQL 12-APR-99 4
SQL 04-OCT-99 3
OAU 10-AUG-99 3
PLS 11-SEP-00 3
JAV 01-FEB-00 3
XML 03-FEB-00 2
SQL 13-DEC-99 2
OAU 27-SEP-00 1
RSD 24-FEB-01 0
XML 18-SEP-00 0
ERM 15-JAN-01 0
PRO 19-FEB-01 0

13 rows selected.

9. Who attended (at least) the same courses as employee 7788?

Solution 9-9.

SQL> select e.ename, e.init
2 from employees e
3 where e.empno <> 7788
4 and not exists
5 (select r1.course
6 from registrations r1
7 where r1.attendee = 7788
8 MINUS
9 select r2.course
10 from registrations r2
11 where r2.attendee = e.empno);

ENAME INIT
-------- -----
ALLEN JAM
BLAKE R
KING CC
ADAMS AA

This is not an easy exercise. The elegant solution shown here uses the MINUS set operator and a correlated subquery. Note the correct position of the negation on the fourth line. You can read the solution as follows:

“List all employees (except employee 7788 himself/herself) for which you cannot find a course attended by employee 7788 and not attended by those employees.”

The first subquery (see lines 5 through 7) is not correlated, and it results in all courses attended by employee 7788. The second subquery (see lines 9 through 11) is correlated, and it produces all courses attended by employee e.

Note This exercise is similar to Exercise 4 in this chapter. Both exercises belong to the same category of “subset problems.” This means that the solutions of Chapter 9’s Exercises 4 and 9 are interchangeable (not verbatim, of course, because the exercises are different; however, they can be solved with the same approach).

10.Give the name and initials of all employees at the bottom of the management hierarchy, with a third column showing the number of management levels above them.

Solution 9-10.

SQL> select ename, init
2 , (level - 1) as levels_above
3 from employees
4 where connect_by_isleaf = 1
5 start with mgr is null
6 connect by prior empno = mgr;

ENAME INIT LEVELS_ABOVE
-------- ----- ------------
ADAMS AA 3
SMITH N 3
ALLEN JAM 2
WARD TF 2
MARTIN P 2
TURNER JJ 2
JONES R 2
MILLER TJA 2

8 rows selected.

Chapter 10 Exercises

1. Look at the example discussed in Listings 10-7, 10-8, and 10-9. Rewrite the query in Listing 10-9 without using a view, by using the WITH operator.

Solution 10-1. Listing 10-9 Rewritten to Use the WITH Operator

SQL> with course_days as
2 (select e.empno, e.ename
3 , sum(c.duration) as days
4 from registrations r
5 , courses c
6 , employees e
7 where e.empno = r.attendee
8 and c.code = r.course
9 group by e.empno, e.ename)
10 select *
11 from course_days
12 where days > (select avg(days)
13 from course_days);

EMPNO ENAME DAYS
-------- -------- --------
7499 ALLEN 11
7698 BLAKE 12
7788 SCOTT 12
7839 KING 8
7876 ADAMS 9
7902 FORD 9

SQL>

2. Look at Listing 10-12. How is it possible that you can delete employee 7654 via this EMP view? There are rows in the HISTORY table, referring to that employee via a foreign key constraint.

Answer: You can delete that employee because you created the foreign key constraint with the CASCADE DELETE option, so all corresponding HISTORY rows are deleted implicitly.

3. Look at the view definition in Listing 10-18. Does this view implement the foreign key constraints from the REGISTRATIONS table to the EMPLOYEES and COURSES tables? Explain your answer.

Answer: No, it doesn’t. The view checks insertions and updates, but it doesn’t prevent you from deleting any rows from the EMPLOYEES and COURSES tables; that is, the view implements only one side of those foreign key constraints.

Tip Don’t try to program your own referential integrity constraint checking. Your solution will probably overlook something, and it will always be less efficient than the declarative constraints of the Oracle DBMS.

4. Create a SAL_HISTORY view providing the following overview for all employees, based on the HISTORY table: For each employee, show the hire date, the review dates, and the salary changes as a consequence of those reviews.

Solution 10-4. The SAL_HISTORY View

SQL> create or replace view sal_history as
2 select empno
3 , min(begindate) over
4 (partition by empno)
5 as hiredate
6 , begindate as reviewdate
7 , msal - lag(msal) over
8 (partition by empno
9 order by empno, begindate)
10 as salary_raise
11 from history;

View created.

SQL> break on empno on hiredate
SQL> select * from sal_history;

EMPNO HIREDATE REVIEWDATE SALARY_RAISE
----- ----------- ----------- ------------
7369 01-JAN-2000 01-JAN-2000
01-FEB-2000 -150
7499 01-JUN-1988 01-JUN-1988
01-JUL-1989 300
01-DEC-1993 200
01-OCT-1995 200
01-NOV-1999 -100
7521 01-OCT-1986 01-OCT-1986
...
7934 01-FEB-1998 01-FEB-1998
01-MAY-1998 5
01-FEB-1999 10
01-JAN-2000 10

79 rows selected.

SQL>

Chapter 11 Exercises

1. Look at Listings 11-26 and 11-37. Apart from aesthetics, there is another important reason why the lines surrounding the script headers in those two listings switch from minus signs to equal signs. Obviously, the first two minus signs are mandatory to turn the lines into comments. What would be wrong with using only minus signs?

Answer: It is the last minus sign that causes trouble. It will make SQL*Plus interpret the next line as a continuation of the current line. Since the current line is a comment, the next line will be considered a continuation of that comment. Therefore, the SQL or SQL*Plus command on the next line will be ignored by SQL*Plus.

2. Create a SQL*Plus script to create indexes. The script should prompt for a table name and a column name (or list of column names), and then generate the index name according to the following standard: i_<tab-id>_<col-id>.

Solution 11-2. SQL*Plus Script to Create Indexes

accept table_name -
default &&table_name -
prompt 'Create index on table [&table_name]: '
accept column_name -
default &&column_name -
prompt 'on column(s) [&column_name]: '
set termout off
store set sqlplus_settings replace
save buffer.sql replace
column dummy new_value index_name
set heading off feedback off verify off
set termout on

select 'Creating index'
, upper(substr( 'i_' ||
substr('&table_name',1,3) ||
'_' ||
translate
( replace
( '&column_name'
, ' ', '')
, ',', '_')
, 1, 30)
) as dummy
, '...'
from dual;

create index &index_name
on &table_name(&column_name);

get buffer.sql nolist
@sqlplus_settings
set termout on

The following are some comments on this solution:

· The script “remembers” table names and column names, and offers them as default values on consecutive executions. This may save you some time when creating multiple indexes.

· The script saves all current SQL*Plus settings before changing the SQL*Plus environment. This enables the script to restore the original SQL*Plus environment at the end of the script.

· The script saves the current contents of the SQL buffer, and then restores the contents at the end with the GET ... NOLIST command. This way, you can resume working on that SQL statement.

· The COLUMN DUMMY NEW_VALUE INDEX_NAME command captures the result of the query against the DUAL table, which generates the index name.

· The index name generation contains many SQL functions. It takes the first three characters of the table name as the table identifier. The script removes all spaces from the column name list, and then replaces the commas with underscores. To avoid error messages for too-long index names, the script truncates the result to a maximum length of 30.

3. Create a SQL*Plus script to produce an index overview. The script should prompt for a table name, allowing you to specify any leading part of a table name. That is, the script should automatically append a % wildcard to the value entered. Then, it should produce a report of all indexes, showing the table name, index name, index type, and number of columns on which the index is based.

Solution 11-3. SQL*Plus Script to Produce an Index Overview

set termout off
store set sqlplus_settings.sql replace
save buffer.sql replace
set verify off feedback off
set termout on
break on table_name skip 1 on index_type

accept table_name default &&table_name -
prompt 'List indexes on table [&table_name.%]: '

select ui.table_name
, decode(ui.index_type
,'NORMAL', ui.uniqueness
,ui.index_type) as index_type
, ui.index_name
, (select count(*)
from user_ind_columns uic
where uic.table_name = ui.table_name
and uic.index_name = ui.index_name) as col_count
from user_indexes ui
where ui.table_name like upper('&table_name.%')
order by ui.table_name
, ui.uniqueness desc;

get buffer.sql nolist
@sqlplus_settings
set termout on

Many SQL*Plus tricks in this script are similar to the ones used in the script for the previous exercise. Here are some additional comments on this solution:

· The BREAK command enhances the readability.

· You use the same default value trick for the table name.

· You need the period character in the ACCEPT command as a separator between the TABLE_NAME variable and the percent sign.

4. Create a script that disables all constraints in your schema.

Answer: First, you must find out which SQL statement allows you to disable constraints, because your script is going to generate that statement. The following SQL command is the most obvious choice:

SQL> ALTER TABLE <table-name> DISABLE CONSTRAINT <constraint-name> [CASCADE]

As the next step, you must figure out how to retrieve relevant information about your constraints. The SQL*Plus DESCRIBE command is useful:

SQL> describe user_constraints
Name Null? Type
--------------------------------- -------- ------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)

By executing some test queries, it becomes apparent which columns of the USER_CONSTRAINTS view you need. Let’s look at a first attempt to generate the ALTER TABLE commands.

Solution 11-4a. First Attempt to Generate the Correct SQL

SQL> select 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT
2 '||constraint_name||';'
3 from user_constraints;

However, if you capture the output from this query in a script file and execute it, you will discover that there is room for improvement. Some ALTER TABLE commands may fail with the following message:

ORA-02297: cannot disable constraint (BOOK.xxx) - dependencies exist

You can fix this problem in two ways:

· Add the CASCADE keyword to the generated ALTER TABLE commands.

· Sort the ALTER TABLE commands in such a way that all primary keys are disabled before the foreign key constraints.

Let’s implement both fixes. Also, let’s add a WHERE clause to the query to avoid generating ALTER TABLE commands for constraints that are disabled already.

Solution 11-4b. Second Attempt to Generate the Correct SQL

SQL> select 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name
2 ||' CASCADE;'
3 from user_constraints
4 where status <> 'DISABLED'
5 order by case constraint_type when 'P' then 1 else 2 end;

Finally, now that you are satisfied with the result of the query, you add the appropriate SQL*Plus commands to capture and execute the query result. The final script looks like the following.

Solution 11-4c. SQL*Plus Script to Disable All Constraints of a Schema

set pagesize 0 verify off feedback off trimspool on
spool doit.sql replace
select 'ALTER TABLE '||table_name||
' DISABLE CONSTRAINT '||constraint_name||' CASCADE;'
from user_constraints
where status <> 'DISABLED'
order by case constraint_type when 'P' then 1 else 2 end;
spool off
@doit
exit

You can build many useful SQL*Plus scripts, once you have discovered how you can use SQL*Plus as a command generator.

Chapter 12 Exercises

1. The SALGRADES table has two columns to indicate salary ranges: LOWERLIMIT and UPPERLIMIT. Define your own SALRANGE_T type, based on a varray of two NUMBER(6,2) values, and use it to create an alternative SALGRADES2 table.

Solution 12-1.

SQL> create or replace type salrange_t
2 as varray(2) of number(6,2);
3 /

Type created.

SQL> create table salgrades2
2 ( grade number(2) constraint S2_PK
3 primary key
4 , salrange salrange_t constraint S2_RANGE_NN
5 not null
6 , bonus NUMBER(6,2) constraint S2_BONUS_NN
7 not null
8 ) ;

Table created.

SQL>

2. Fill the new SALGRADES2 table with a single INSERT statement, using the existing SALGRADES table.

Solution 12-2.

SQL> insert into salgrades2
2 select grade
3 , salrange_t(lowerlimit,upperlimit)
4 , bonus
5 from salgrades;

5 rows created.

SQL> col salrange format a25
SQL> select * from salgrades2;

GRADE SALRANGE BONUS
-------- ------------------------- --------
1 SALRANGE_T(700, 1200) 0
2 SALRANGE_T(1201, 1400) 50
3 SALRANGE_T(1401, 2000) 100
4 SALRANGE_T(2001, 3000) 200
5 SALRANGE_T(3001, 9999) 500

5 rows selected.

SQL>

3. Create a table TESTNESTwith two columns: column X and column MX. Column X is NUMBER(1,0) with values 2, 3, 4, ..., 9. Column MX is a nested table, based on a MX_TAB_T type, containing all multiples of X less than or equal to 20.

Solution 12-3a. Table TESTNEST Creation

SQL> create or replace type mx_tab_t
2 as table of number(2);
3 /

Type created.

SQL> create table testnest
2 ( x number(1,0)
3 , mx mx_tab_t
4 ) nested table mx store as mx_tab;

Table created.

SQL>

You can use pure INSERT statements to populate the TESTNEST table. The following solution uses PL/SQL to insert all rows in an efficient way. The PL/SQL syntax is straightforward.

Solution 12-3b. Table TESTNEST Population

SQL> declare
2 i number;
3 j number;
4 begin
5 for i in 2..9 loop
6 insert into testnest (x, mx)
7 values (i, mx_tab_t());
8 for j in 1..20 loop
9 exit when i*j > 20;
10 insert into table (select mx from testnest where x=i)
11 values (i*j);
12 end loop;
13 end loop;
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>

Now, let’s check the contents of the TESTNEST table.

Solution 12-3c. Table TESTNEST Query

SQL> col x format 9
SQL> col mx format a80
SQL> select * from testnest;

X MX
-- --------------------------------------------
2 MX_TAB_T(2, 4, 6, 8, 10 ,12, 14, 16, 18, 20)
3 MX_TAB_T(3, 6, 9, 12, 15, 18)
4 MX_TAB_T(4, 8, 12, 16, 20)
5 MX_TAB_T(5, 10, 15, 20)
6 MX_TAB_T(6, 12, 18)
7 MX_TAB_T(7, 14)
8 MX_TAB_T(8, 16)
9 MX_TAB_T(9, 18)

8 rows selected.

SQL>

4. Use multiset operators to solve the following problems, using the TESTNEST table you created and populated in the previous exercise:

a. Which rows have a nested table containing value 12?

Answer: 2, 3, 4, 6

Solution 12-4a.

SQL> select *
2 from testnest
3 where 12 member of mx;

X MX
-- --------------------------------------------
2 MX_TAB_T(2, 4, 6, 8, 10, 12, 14, 16, 18, 20)
3 MX_TAB_T(3, 6, 9, 12, 15, 18)
4 MX_TAB_T(4, 8, 12, 16, 20)
6 MX_TAB_T(6, 12, 18)

SQL>

b. Which nested tables are not a subset of any other subset?

Answer: 2, 3, 5, 7

Solution 12-4b.

SQL> select t1.*
2 from testnest t1
3 where not exists
4 (select t2.*
5 from testnest t2
6 where t2.x <> t1.x
7 and t1.mx submultiset of t2.mx);

X MX
-- --------------------------------------------
2 MX_TAB_T(2, 4, 6, 8, 10, 12, 14, 16, 18, 20)
3 MX_TAB_T(3, 6, 9, 12, 15, 18)
5 MX_TAB_T(5, 10, 15, 20)
7 MX_TAB_T(7, 14)

SQL>

c. Which nested tables have more than 42 different nonempty subsets?

Answer: 2, 3

Solution 12-4c.

SQL> select x
2 , cardinality(powermultiset(mx))
3 from testnest
4 where cardinality(powermultiset(mx)) > 42;

X CARDINALITY(POWERMULTISET(MX))
-- ------------------------------
2 1023
3 63

SQL>