DB2 Universal Database™ for iSeries sample tables

 

These sample tables are referred to and used in the SQL programming and the SQL reference topic collections.

Along with the tables are the SQL statements for creating the tables.

As a group, the tables include information that describes employees, departments, projects, and activities. This information makes up a sample application demonstrating some of the features of the DB2^® UDB Query Manager and SQL Development Kit licensed program. All examples assume the tables are in a schema named CORPDATA (for corporate data). A stored procedure is shipped as part of the system that contains the DDL statements to create all of these tables, and the INSERT statements to populate them. The procedure will create the schema specified on the call to the procedure. Since this is an SQL external stored procedure, it can be called from any SQL interface, including interactive SQL and iSeries™ Navigator. To call the procedure where SAMPLE is the schema you want to create, issue the following statement:

CALL QSYS.CREATE_SQL_SAMPLE ('SAMPLE')

The schema name must be specified in uppercase. The schema must not already exist.

In these sample tables, a question mark (?) indicates a null value.

• Department table (DEPARTMENT)
  The department table describes each department in the enterprise and identifies its manager and the department that it reports to.

• Employee table (EMPLOYEE)
  The employee table identifies every employee by an employee number and lists basic personnel information.

• Employee photo table (EMP_PHOTO)
  The employee photo table contains a photo of each employee identified by an employee number.

• Employee resumé table (EMP_RESUME)
  The employee resumé table contains a resumé for each employee identified by an employee number.

• Employee to project activity table (EMPPROJACT)
  The employee to project activity table identifies the employee who participates in each activity listed for each project. The employee's level of involvement (full-time or part-time) and the activity schedule are also included in the table.

• Project table (PROJECT)
  The project table describes each project that the business is currently undertaking. Data contained in each row include the project number, name, person responsible, and schedule dates.

• Project activity table (PROJACT)
  The project activity table describes each project activity that the business is currently undertaking. Data contained in each row includes the project number, activity number, and schedule dates.

• Activity table (ACT)
  The activity table describes each activity.

• Class schedule table (CL_SCHED)
  The class schedule table describes each class, the start time for the class, the end time for the class, and the class code.

• In-tray table (IN_TRAY)
  The in-tray table describes an electronic in-basket that contains the timestamp when a message is received, the user ID of the person who sent the message, and the content of the message.

• Organization table (ORG)
  The organization table describes the organization of the corporation.

• Staff table (STAFF)
  The staff table describes the background information about employees.

• Sales table (SALES)
  The sales table describes the information about each sale for each sales person.

 

Parent topic:

Reference

 

Related reference

Referential integrity and tables
Example: DELETE rules
Multiple-row FETCH using a row storage area