Tables Used in This Book

The majority of the examples in this book involve the use of two tables, EMP and DEPT. The EMP table is a simple 14-row table with only numeric, string, and date fields. The DEPT table is a simple four-row table with only numeric and string fields. These tables appear in many old database texts, and the many-to-one relationship between departments and employees is well understood.

While I'm on the topic of the example tables, I want to mention that all but a very few solutions in this book run against these tables. Nowhere do I tweak my example data to set up a solution that you would be unlikely to have a chance of implementing in the real world, as some books do.

And while I'm on the topic of solutions, let me just mention that whenever possible I've tried to provide a generic solution that will run on all five RDBMSs covered in this book. Often that's not possible. Even so, in many cases more than one vendor shares a solution. Because of their mutual support for window functions, for example, Oracle and DB2 often share solutions. Whenever solutions are shared, or at least are very similar, discussions are shared as well.

The contents of EMP and DEPT are shown below, respectively:

  select * from emp; EMPNO ENAME  JOB        MGR HIREDATE     SAL COMM  DEPTNO ----- ------ --------- ---- ----------- ---- ---- -------  7369 SMITH  CLERK     7902 17-DEC-1980  800           20  7499 ALLEN  SALESMAN  7698 20-FEB-1981 1600  300      30  7521 WARD   SALESMAN  7698 22-FEB-1981 1250  500      30  7566 JONES  MANAGER   7839 02-APR-1981 2975           20  7654 MARTIN SALESMAN  7698 28-SEP-1981 1250 1400      30  7698 BLAKE  MANAGER   7839 01-MAY-1981 2850           30  7782 CLARK  MANAGER   7839 09-JUN-1981 2450           10  7788 SCOTT  ANALYST   7566 09-DEC-1982 3000           20  7839 KING   PRESIDENT      17-NOV-1981 5000           10  7844 TURNER SALESMAN  7698 08-SEP-1981 1500    0      30  7876 ADAMS  CLERK     7788 12-JAN-1983 1100           20  7900 JAMES  CLERK     7698 03-DEC-1981  950           30  7902 FORD   ANALYST   7566 03-DEC-1981 3000           20  7934 MILLER CLERK     7782 23-JAN-1982 1300           10  select * from dept; DEPTNO DNAME          LOC ------ -------------- ---------     10 ACCOUNTING     NEW YORK     20 RESEARCH       DALLAS     30 SALES          CHICAGO     40 OPERATIONS     BOSTON 

Additionally, you will find four pivot tables used in this book; T1, T10, T100, and T500. Because these tables exist only to facilitate pivots, I did not find it necessary to give them clever names. The number following the "T" in each of the pivot tables signifies the number of rows in each table starting from 1. For example, the values for T1 and T10:

 select id from t1;         ID ----------          1 select id from t10;         ID ----------          1          2          3          4          5          6          7          8          9         10 

As an aside, some vendors allow partial SELECT statements. For example, you can have SELECT without a FROM clause. I don't particularly like this, thus I select against a support table, T1, with a single row, rather than using partial queries.

Any other tables are specific to particular recipes and chapters, and will be introduced in the text when appropriate.