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

APPENDIX A. The Seven Case Tables

This appendix offers an overview of the seven case tables used throughout this book, in various formats. Its main purpose is to help you in writing SQL commands and checking your results.

The first section shows an Entity Relationship Modeling (ERM) diagram, indicating the entities of the underlying data model, including their unique identifiers and their relationships. Then you can find descriptions of the seven case tables, with names and datatypes of all their columns and short explanations, when necessary. The next section shows a table diagram, focusing on all primary key and foreign key constraints. This diagram may be especially helpful when you are writing joins.

The biggest component of this appendix (with the highest level of detail) is a complete listing of the seven case tables with all their rows. This overview may be useful to check your query results for correctness.

At the end of this appendix, you will find two alternative representations of the case table data, showing the table rows in a compact format. The first diagram shows an overview of the 14 employees. It clearly shows the department populations and the hierarchical (manager/subordinate) relationships. The second illustration shows a matrix overview of all course offerings, with starting dates, locations, attendees (A), and trainers (T). Again, these representations may be useful to check your query results for correctness.

ERM Diagram

The ERM diagram, shown in Figure A-1, shows the seven entities (the rounded-corner boxes) with their unique identifiers and their mutual relationships.

Figure A-1. ERM diagram of the case entities

The ten crow’s feet indicate one-to-many relationships. The diagram shows two types of one-to-many relationships: three relationships are completely optional (indicated by all dashed lines) and the remaining ones are mandatory in one direction (indicated by the solid part of the line).

Hash signs (#) in front of an attribute mean that the attribute is part of the unique identifier; relationship cross-lines indicate that the relationship is part of the unique identifier. Note that the diagram shows only attributes that are part of unique identifiers, for enhanced readability.

You can interpret the relationships in this diagram as follows:

· Every employee has at most one manager (and employees may have multiple subordinates).

· Every employee belongs to precisely one salary grade and is employed by at most one department (employees without a department are allowed).

· Each department has precisely one manager (and employees may be manager of multiple departments).

· Each course offering refers to precisely one existing course, with at most one employee as trainer.

· Each registration is for precisely one employee and for precisely one course offering.

· Each history record refers to precisely one employee and precisely one department.

Table Structure Descriptions

This section presents descriptions of the table structures. In the listings, * means NOT NULL and P means primary key.

EMPLOYEES: EMPNO N(4) P Unique employee number
ENAME VC(8) * Last name
INIT VC(5) * Initials (without punctuation)
JOB VC(8) Job description
MGR N(4) Manager (references EMPLOYEES)
BDATE DATE * Date of birth
MSAL N(6,2) * Monthly salary (excluding net bonus)
COMM N(6,2) Commission (per year, for sales reps)
DEPTNO N(2) Department (references DEPARTMENTS)

DEPARTMENTS: DEPTNO N(2) P Unique department number
DNAME VC(10) * Name of the department
LOCATION VC(8) * Location (city)
MGR N(4) Manager (references EMPLOYEES)

SALGRADES: GRADE N(2) P Unique salary grade number
LOWERLIMIT N(6,2) * Minimum salary for this grade
UPPERLIMIT N(6,2) * Maximum salary for this grade
BONUS N(6,2) * Net bonus on top of monthly salary

COURSES: CODE VC(6) P Unique course code
DESCRIPTION VC(30) * Course description (title)
CATEGORY C(3) * Course category (GEN,BLD, or DSG)
DURATION N(2) * Course duration (in days)

OFFERINGS: COURSE VC(6) P Course code (references COURSES)
BEGINDATE DATE P First course day
TRAINER N(4) Instructor (references EMPLOYEES)
LOCATION VC(8) Location of the course offering

REGISTRATIONS: ATTENDEE N(4) P Attendee (references EMPLOYEES)
COURSE VC(6) P Course code (references OFFERINGS)
BEGINDATE DATE P First course day (references OFFERINGS)
EVALUATION N(1) Attendee's opinion (scale 1 - 5)

HISTORY: EMPNO N(4) P Employee (references EMPLOYEES)
BEGINYEAR N(4) * Year component of BEGINDATE
BEGINDATE DATE P Begin date interval
ENDDATE DATE End date interval
DEPTNO N(2) * Department (references DEPARTMENTS)
MSAL N(6,2) * Monthly salary during the interval
COMMENTS VC(60) Free text space

Columns and Foreign Key Constraints

Figure A-2 shows the columns and foreign key constraints in the case tables. The primary key components have a dark-gray background, and all arrows point from the foreign keys to the corresponding primary keys. Boxes surrounding multiple columns indicate composite keys.

Figure A-2. Columns and foreign key constraints

Contents of the Seven Tables

This section lists the contents of each of the seven case tables.

EMPLOYEES

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

14 rows selected.

DEPARTMENTS

DEPTNO DNAME LOCATION MGR
------ ---------- -------- -----
10 ACCOUNTING NEW YORK 7782
20 TRAINING DALLAS 7566
30 SALES CHICAGO 7698
40 HR BOSTON 7839

SALGRADES

GRADE LOWERLIMIT UPPERLIMIT BONUS
----- ---------- ---------- ------
1 700 1200 0
2 1201 1400 50
3 1401 2000 100
4 2001 3000 200
5 3001 9999 500

COURSES

CODE DESCRIPTION CATEGORY DURATION
------ ---------------------------- -------- --------
JAV Java for Oracle developers BLD 4
PLS Introduction to PL/SQL BLD 1
XML XML for Oracle developers BLD 2
ERM Data modeling with ERM DSG 3
GEN System generation DSG 4
PMT Process modeling techniques DSG 1
PRO Prototyping DSG 5
RSD Relational system design DSG 2
OAU Oracle for application users GEN 1
SQL Introduction to SQL GEN 4

10 rows selected.

OFFERINGS

COURSE BEGINDATE TRAINER LOCATION
------ ---------- ------- --------
SQL 12-04-1999 7902 DALLAS
OAU 10-08-1999 7566 CHICAGO
SQL 04-10-1999 7369 SEATTLE
SQL 13-12-1999 7369 DALLAS
JAV 13-12-1999 7566 SEATTLE
JAV 01-02-2000 7876 DALLAS
XML 03-02-2000 7369 DALLAS
PLS 11-09-2000 7788 DALLAS
XML 18-09-2000 SEATTLE
OAU 27-09-2000 7902 DALLAS
ERM 15-01-2001
PRO 19-02-2001 DALLAS
RSD 24-02-2001 7788 CHICAGO

13 rows selected.

REGISTRATIONS

ATTENDEE COURSE BEGINDATE EVALUATION
-------- ------ ---------- ----------
7499 SQL 12-04-1999 4
JAV 13-12-1999 2
XML 03-02-2000 5
PLS 11-09-2000
7521 OAU 10-08-1999 4
7566 JAV 01-02-2000 3
PLS 11-09-2000
7698 SQL 12-04-1999 4
SQL 13-12-1999
JAV 01-02-2000 5
7782 JAV 13-12-1999 5
7788 SQL 04-10-1999
JAV 13-12-1999 5
JAV 01-02-2000 4
7839 SQL 04-10-1999 3
JAV 13-12-1999 4
7844 OAU 27-09-2000 5
7876 SQL 12-04-1999 2
JAV 13-12-1999 5
PLS 11-09-2000
7900 OAU 10-08-1999 4
XML 03-02-2000 4
7902 OAU 10-08-1999 5
SQL 04-10-1999 4
SQL 13-12-1999
7934 SQL 12-04-1999 5

26 rows selected.

HISTORY (formatted, and without COMMENTS column values)

EMPNO BEGINYEAR BEGINDATE ENDDATE DEPTNO MSAL
----- --------- ---------- ---------- ------ ------
7369 2000 01-01-2000 01-02-2000 40 950
2000 01-02-2000 20 800

7499 1988 01-06-1988 01-07-1989 30 1000
1989 01-07-1989 01-12-1993 30 1300
1993 01-12-1993 01-10-1995 30 1500
1995 01-10-1995 01-11-1999 30 1700
1999 01-11-1999 30 1600

7521 1986 01-10-1986 01-08-1987 20 1000
1987 01-08-1987 01-01-1989 30 1000
1989 01-01-1989 15-12-1992 30 1150
1992 15-12-1992 01-10-1994 30 1250
1994 01-10-1994 01-10-1997 20 1250
1997 01-10-1997 01-02-2000 30 1300
2000 01-02-2000 30 1250

7566 1982 01-01-1982 01-12-1982 20 900
1982 01-12-1982 15-08-1984 20 950
1984 15-08-1984 01-01-1986 30 1000
1986 01-01-1986 01-07-1986 30 1175
1986 01-07-1986 15-03-1987 10 1175
1987 15-03-1987 01-04-1987 10 2200
1987 01-04-1987 01-06-1989 10 2300
1989 01-06-1989 01-07-1992 40 2300
1992 01-07-1992 01-11-1992 40 2450
1992 01-11-1992 01-09-1994 20 2600
1994 01-09-1994 01-03-1995 20 2550
1995 01-03-1995 15-10-1999 20 2750
1999 15-10-1999 20 2975

7654 1999 01-01-1999 15-10-1999 30 1100
1999 15-10-1999 30 1250

7698 1982 01-06-1982 01-01-1983 30 900
1983 01-01-1983 01-01-1984 30 1275
1984 01-01-1984 15-04-1985 30 1500
1985 15-04-1985 01-01-1986 30 2100
1986 01-01-1986 15-10-1989 30 2200
1989 15-10-1989 30 2850

7782 1988 01-07-1988 10 2450
7788 1982 01-07-1982 01-01-1983 20 900
1983 01-01-1983 15-04-1985 20 950
1985 15-04-1985 01-06-1985 40 950
1985 01-06-1985 15-04-1986 40 1100
1986 15-04-1986 01-05-1986 20 1100
1986 01-05-1986 15-02-1987 20 1800
1987 15-02-1987 01-12-1989 20 1250
1989 01-12-1989 15-10-1992 20 1350
1992 15-10-1992 01-01-1998 20 1400
1998 01-01-1998 01-01-1999 20 1700
1999 01-01-1999 01-07-1999 20 1800
1999 01-07-1999 01-06-2000 20 1800
2000 01-06-2000 20 3000

7839 1982 01-01-1982 01-08-1982 30 1000
1982 01-08-1982 15-05-1984 30 1200
1984 15-05-1984 01-01-1985 30 1500
1985 01-01-1985 01-07-1985 30 1750
1985 01-07-1985 01-11-1985 10 2000
1985 01-11-1985 01-02-1986 10 2200
1986 01-02-1986 15-06-1989 10 2500
1989 15-06-1989 01-12-1993 10 2900
1993 01-12-1993 01-09-1995 10 3400
1995 01-09-1995 01-10-1997 10 4200
1997 01-10-1997 01-10-1998 10 4500
1998 01-10-1998 01-11-1999 10 4800
1999 01-11-1999 15-02-2000 10 4900
2000 15-02-2000 10 5000

7844 1995 01-05-1995 01-01-1997 30 900
1998 15-10-1998 01-11-1998 10 1200
1998 01-11-1998 01-01-2000 30 1400
2000 01-01-2000 30 1500

7876 2000 01-01-2000 01-02-2000 20 950
2000 01-02-2000 20 1100

7900 2000 01-07-2000 30 800

7902 1998 01-09-1998 01-10-1998 40 1400
1998 01-10-1998 15-03-1999 30 1650
1999 15-03-1999 01-01-2000 30 2500
2000 01-01-2000 01-08-2000 30 3000
2000 01-08-2000 20 3000

7934 1998 01-02-1998 01-05-1998 10 1275
1998 01-05-1998 01-02-1999 10 1280
1999 01-02-1999 01-01-2000 10 1290
2000 01-01-2000 10 1300

79 rows selected.

Hierarchical Employees Overview

Figure A-3 illustrates an overview of the employees and management structure. Note that department 40 has no employees.

Figure A-3. Employee overview with management structure

Course Offerings Overview

This section shows an overview of the course offerings. In the listing A stands for Attendee and T stands for Trainer.

Course code: SQL OAU SQL JAV SQL JAV
Begindate: 12/04/99 10/08/99 04/10/99 13/12/99 13/12/99 01/02/00
Location: Dallas Chicago Seattle Seattle Dallas Dallas

Smith, N 7369 . . T . T .
Allen, JAM 7499 A . . A . .
Ward, TF 7521 . A . . . .
Jones, JM 7566 . T . T . A
Martin, P 7654 . . . . . .
Blake, R 7698 A . . . A A
Clark, AB 7782 . . . A . .
Scott, SCJ 7788 . . A A . A
King, CC 7839 . . A A . .
Turner, JJ 7844 . . . . . .
Adams, AA 7876 A . . A . T
Jones, R 7900 . A . . . .
Ford, MG 7902 T A A . A .
Miller, TJA 7934 A . . . . .

Course code: XML PLS ... OAU ... RSD
Begindate: 03/02/00 11/09/00 ... 27/09/00 ... 24/02/01
Location: Dallas Dallas ... Dallas ... Chicago

Smith, N 7369 T . ... . ... .
Allen, JAM 7499 A A ... . ... .
Ward, TF 7521 . . ... . ... .
Jones, JM 7566 . A ... . ... .
Martin, P 7654 . . ... . ... .
Blake, R 7698 . . ... . ... .
Clark, AB 7782 . . ... . ... .
Scott, SCJ 7788 . T ... . ... T
King, CC 7839 . . ... . ... .
Turner, JJ 7844 . . ... A ... .
Adams, AA 7876 . A ... . ... .
Jones, R 7900 A . ... . ... .
Ford, MG 7902 . . ... T ... .
Miller, TJA 7934 . . ... . ... .

Course code: XML ERM PRO Scheduled; however:
Begindate: 18/09/00 15/01/01 19/02/01 - No trainer assigned
Location: Seattle Dallas - No registrations yet