SQL Reference

The Sample Database

The examples in this book use a sample database. To use these examples, you must create the SAMPLE database. To use it, the database manager must be installed.

To Create the Sample Database

An executable file creates the sample database. 116 To create a database you must have SYSADM authority.

To Erase the Sample Database

If you do not need to access the sample database, you can erase it by using the DROP DATABASE command:
db2 drop database sample

CL_SCHED Table


Name: CLASS_CODE DAY STARTING ENDING
Type: char(7) smallint time time
Desc: Class Code (room:teacher) Day # of 4 day schedule Class Start Time Class End Time

DEPARTMENT Table


Name: DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
Type: char(3) not null varchar(29) not null char(6) char(3) not null char(16)
Desc: Department number Name describing general activities of department Employee number (EMPNO) of department manager Department (DEPTNO) to which this department reports Name of the remote location
Values: A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
  B01 PLANNING 000020 A00 -
  C01 INFORMATION CENTER 000030 A00 -
  D01 DEVELOPMENT CENTER - A00 -
  D11 MANUFACTURING SYSTEMS 000060 D01 -
  D21 ADMINISTRATION SYSTEMS 000070 D01 -
  E01 SUPPORT SERVICES 000050 A00 -
  E11 OPERATIONS 000090 E01 -
  E21 SOFTWARE SUPPORT 000100 E01 -

EMPLOYEE Table


Names: EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE
Type: char(6) not null varchar(12) not null char(1) not null varchar(15) not null char(3) char(4) date
Desc: Employee number First name Middle initial Last name Department (DEPTNO) in which the employee works Phone number Date of hire

JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
char(8) smallint not null char(1) date dec(9,2) dec(9,2) dec(9,2)
Job Number of years of formal education Sex (M male, F female) Date of birth Yearly salary Yearly bonus Yearly commission

See the following page for the values in the EMPLOYEE table.
  EMPNO   FIRSTNME MID INIT   LASTNAME WORK DEPT PHONE NO   HIREDATE   JOB ED LEVEL   SEX   BIRTHDATE   SALARY   BONUS   COMM
char(6) not null varchar(12) not null char(1) not null varchar(15) not null char(3) char(4) date char(8) smallint not null char(1) date dec(9,2) dec(9,2) dec(9,2)
000010 CHRISTINE I HAAS A00 3978 1965-01-01 PRES 18 F 1933-08-24 52750 1000 4220
000020 MICHAEL L THOMPSON B01 3476 1973-10-10 MANAGER 18 M 1948-02-02 41250 800 3300
000030 SALLY A KWAN C01 4738 1975-04-05 MANAGER 20 F 1941-05-11 38250 800 3060
000050 JOHN B GEYER E01 6789 1949-08-17 MANAGER 16 M 1925-09-15 40175 800 3214
000060 IRVING F STERN D11 6423 1973-09-14 MANAGER 16 M 1945-07-07 32250 500 2580
000070 EVA D PULASKI D21 7831 1980-09-30 MANAGER 16 F 1953-05-26 36170 700 2893
000090 EILEEN W HENDERSON E11 5498 1970-08-15 MANAGER 16 F 1941-05-15 29750 600 2380
000100 THEODORE Q SPENSER E21 0972 1980-06-19 MANAGER 14 M 1956-12-18 26150 500 2092
000110 VINCENZO G LUCCHESSI A00 3490 1958-05-16 SALESREP 19 M 1929-11-05 46500 900 3720
000120 SEAN   O'CONNELL A00 2167 1963-12-05 CLERK 14 M 1942-10-18 29250 600 2340
000130 DOLORES M QUINTANA C01 4578 1971-07-28 ANALYST 16 F 1925-09-15 23800 500 1904
000140 HEATHER A NICHOLLS C01 1793 1976-12-15 ANALYST 18 F 1946-01-19 28420 600 2274
000150 BRUCE   ADAMSON D11 4510 1972-02-12 DESIGNER 16 M 1947-05-17 25280 500 2022
000160 ELIZABETH R PIANKA D11 3782 1977-10-11 DESIGNER 17 F 1955-04-12 22250 400 1780
000170 MASATOSHI J YOSHIMURA D11 2890 1978-09-15 DESIGNER 16 M 1951-01-05 24680 500 1974
000180 MARILYN S SCOUTTEN D11 1682 1973-07-07 DESIGNER 17 F 1949-02-21 21340 500 1707
000190 JAMES H WALKER D11 2986 1974-07-26 DESIGNER 16 M 1952-06-25 20450 400 1636
000200 DAVID   BROWN D11 4501 1966-03-03 DESIGNER 16 M 1941-05-29 27740 600 2217
000210 WILLIAM T JONES D11 0942 1979-04-11 DESIGNER 17 M 1953-02-23 18270 400 1462
000220 JENNIFER K LUTZ D11 0672 1968-08-29 DESIGNER 18 F 1948-03-19 29840 600 2387
000230 JAMES J JEFFERSON D21 2094 1966-11-21 CLERK 14 M 1935-05-30 22180 400 1774
000240 SALVATORE M MARINO D21 3780 1979-12-05 CLERK 17 M 1954-03-31 28760 600 2301
000250 DANIEL S SMITH D21 0961 1969-10-30 CLERK 15 M 1939-11-12 19180 400 1534
000260 SYBIL P JOHNSON D21 8953 1975-09-11 CLERK 16 F 1936-10-05 17250 300 1380
000270 MARIA L PEREZ D21 9001 1980-09-30 CLERK 15 F 1953-05-26 27380 500 2190
000280 ETHEL R SCHNEIDER E11 8997 1967-03-24 OPERATOR 17 F 1936-03-28 26250 500 2100
000290 JOHN R PARKER E11 4502 1980-05-30 OPERATOR 12 M 1946-07-09 15340 300 1227
000300 PHILIP X SMITH E11 2095 1972-06-19 OPERATOR 14 M 1936-10-27 17750 400 1420
000310 MAUDE F SETRIGHT E11 3332 1964-09-12 OPERATOR 12 F 1931-04-21 15900 300 1272
000320 RAMLAL V MEHTA E21 9990 1965-07-07 FIELDREP 16 M 1932-08-11 19950 400 1596
000330 WING   LEE E21 2103 1976-02-23 FIELDREP 14 M 1941-07-18 25370 500 2030
000340 JASON R GOUNOT E21 5698 1947-05-05 FIELDREP 16 M 1926-05-17 23840 500 1907

EMP_ACT Table


Name: EMPNO PROJNO ACTNO EMPTIME EMSTDATE EMENDATE
Type: char(6) not null char(6) not null smallint not null dec(5,2) date date
Desc: Employee number Project number Activity number Proportion of employee's time spent on project Date activity starts Date activity ends
Values: 000010 AD3100 10 .50 1982-01-01 1982-07-01
  000070 AD3110 10 1.00 1982-01-01 1983-02-01
  000230 AD3111 60 1.00 1982-01-01 1982-03-15
  000230 AD3111 60 .50 1982-03-15 1982-04-15
  000230 AD3111 70 .50 1982-03-15 1982-10-15
  000230 AD3111 80 .50 1982-04-15 1982-10-15
  000230 AD3111 180 1.00 1982-10-15 1983-01-01
  000240 AD3111 70 1.00 1982-02-15 1982-09-15
  000240 AD3111 80 1.00 1982-09-15 1983-01-01
  000250 AD3112 60 1.00 1982-01-01 1982-02-01
  000250 AD3112 60 .50 1982-02-01 1982-03-15
  000250 AD3112 60 .50 1982-12-01 1983-01-01
  000250 AD3112 60 1.00 1983-01-01 1983-02-01
  000250 AD3112 70 .50 1982-02-01 1982-03-15
  000250 AD3112 70 1.00 1982-03-15 1982-08-15
  000250 AD3112 70 .25 1982-08-15 1982-10-15
  000250 AD3112 80 .25 1982-08-15 1982-10-15
  000250 AD3112 80 .50 1982-10-15 1982-12-01
  000250 AD3112 180 .50 1982-08-15 1983-01-01
  000260 AD3113 70 .50 1982-06-15 1982-07-01
  000260 AD3113 70 1.00 1982-07-01 1983-02-01
  000260 AD3113 80 1.00 1982-01-01 1982-03-01
  000260 AD3113 80 .50 1982-03-01 1982-04-15
  000260 AD3113 180 .50 1982-03-01 1982-04-15
  000260 AD3113 180 1.00 1982-04-15 1982-06-01
  000260 AD3113 180 .50 1982-06-01 1982-07-01
  000270 AD3113 60 .50 1982-03-01 1982-04-01
  000270 AD3113 60 1.00 1982-04-01 1982-09-01
  000270 AD3113 60 .25 1982-09-01 1982-10-15
  000270 AD3113 70 .75 1982-09-01 1982-10-15
  000270 AD3113 70 1.00 1982-10-15 1983-02-01
  000270 AD3113 80 1.00 1982-01-01 1982-03-01
  000270 AD3113 80 .50 1982-03-01 1982-04-01
  000030 IF1000 10 .50 1982-06-01 1983-01-01
  000130 IF1000 90 1.00 1982-01-01 1982-10-01
  000130 IF1000 100 .50 1982-10-01 1983-01-01
  000140 IF1000 90 .50 1982-10-01 1983-01-01
  000030 IF2000 10 .50 1982-01-01 1983-01-01
  000140 IF2000 100 1.00 1982-01-01 1982-03-01
  000140 IF2000 100 .50 1982-03-01 1982-07-01
  000140 IF2000 110 .50 1982-03-01 1982-07-01
  000140 IF2000 110 .50 1982-10-01 1983-01-01
  000010 MA2100 10 .50 1982-01-01 1982-11-01
  000110 MA2100 20 1.00 1982-01-01 1982-03-01
  000010 MA2110 10 1.00 1982-01-01 1983-02-01
  000200 MA2111 50 1.00 1982-01-01 1982-06-15
  000200 MA2111 60 1.00 1982-06-15 1983-02-01
  000220 MA2111 40 1.00 1982-01-01 1983-02-01
  000150 MA2112 60 1.00 1982-01-01 1982-07-15
  000150 MA2112 180 1.00 1982-07-15 1983-02-01
  000170 MA2112 60 1.00 1982-01-01 1983-06-01
  000170 MA2112 70 1.00 1982-06-01 1983-02-01
  000190 MA2112 70 1.00 1982-02-01 1982-10-01
  000190 MA2112 80 1.00 1982-10-01 1983-10-01
  000160 MA2113 60 1.00 1982-07-15 1983-02-01
  000170 MA2113 80 1.00 1982-01-01 1983-02-01
  000180 MA2113 70 1.00 1982-04-01 1982-06-15
  000210 MA2113 80 .50 1982-10-01 1983-02-01
  000210 MA2113 180 .50 1982-10-01 1983-02-01
  000050 OP1000 10 .25 1982-01-01 1983-02-01
  000090 OP1010 10 1.00 1982-01-01 1983-02-01
  000280 OP1010 130 1.00 1982-01-01 1983-02-01
  000290 OP1010 130 1.00 1982-01-01 1983-02-01
  000300 OP1010 130 1.00 1982-01-01 1983-02-01
  000310 OP1010 130 1.00 1982-01-01 1983-02-01
  000050 OP2010 10 .75 1982-01-01 1983-02-01
  000100 OP2010 10 1.00 1982-01-01 1983-02-01
  000320 OP2011 140 .75 1982-01-01 1983-02-01
  000320 OP2011 150 .25 1982-01-01 1983-02-01
  000330 OP2012 140 .25 1982-01-01 1983-02-01
  000330 OP2012 160 .75 1982-01-01 1983-02-01
  000340 OP2013 140 .50 1982-01-01 1983-02-01
  000340 OP2013 170 .50 1982-01-01 1983-02-01
  000020 PL2100 30 1.00 1982-01-01 1982-09-15

EMP_PHOTO Table


Name: EMPNO PHOTO_FORMAT PICTURE
Type: char(6) not null varchar(10) not null blob(100k)
Desc: Employee number Photo format Photo of employee
Values: 000130 bitmap db200130.bmp

000130 gif db200130.gif

000130 xwd db200130.xwd

000140 bitmap db200140.bmp

000140 gif db200140.gif

000140 xwd db200140.xwd

000150 bitmap db200150.bmp

000150 gif db200150.gif

000150 xwd db200150.xwd

000190 bitmap db200190.bmp

000190 gif db200190.gif

000190 xwd db200190.xwd

EMP_RESUME Table


Name: EMPNO RESUME_FORMAT RESUME
Type: char(6) not null varchar(10) not null clob(5k)
Desc: Employee number Resume Format Resume of employee
Values: 000130 ascii db200130.asc

000130 script db200130.scr

000140 ascii db200140.asc

000140 script db200140.scr

000150 ascii db200150.asc

000150 script db200150.scr

000190 ascii db200190.asc

000190 script db200190.scr

IN_TRAY Table


Name: RECEIVED SOURCE SUBJECT NOTE_TEXT
Type: timestamp char(8) char(64) varchar(3000)
Desc: Date and Time received User id of person sending note Brief description The note

ORG Table


Name: DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION
Type: smallint not null varchar(14) smallint varchar(10) varchar(13)
Desc: Department number Department name Manager number Division of corporation City
Values: 10 Head Office 160 Corporate New York

15 New England 50 Eastern Boston

20 Mid Atlantic 10 Eastern Washington

38 South Atlantic 30 Eastern Atlanta

42 Great Lakes 100 Midwest Chicago

51 Plains 140 Midwest Dallas

66 Pacific 270 Western San Francisco

84 Mountain 290 Western Denver

PROJECT Table


Name: PROJNO PROJNAME DEPTNO RESPEMP PRSTAFF PRSTDATE PRENDATE MAJPROJ
Type: char(6) not null varchar(24) not null char(3) not null char(6) not null dec(5,2) date date char(6)
Desc: Project number Project name Department responsible Employee responsible Estimated mean staffing Estimated start date Estimated end date Major project, for a subproject
Values: AD3100 ADMIN SERVICES D01 000010 6.5 1982-01-01 1983-02-01 -
  AD3110 GENERAL ADMIN SYSTEMS D21 000070 6 1982-01-01 1983-02-01 AD3100
  AD3111 PAYROLL PROGRAMMING D21 000230 2 1982-01-01 1983-02-01 AD3110
  AD3112 PERSONNEL PROGRAMMING D21 000250 1 1982-01-01 1983-02-01 AD3110
  AD3113 ACCOUNT PROGRAMMING D21 000270 2 1982-01-01 1983-02-01 AD3110
  IF1000 QUERY SERVICES C01 000030 2 1982-01-01 1983-02-01 -
  IF2000 USER EDUCATION C01 000030 1 1982-01-01 1983-02-01 -
  MA2100 WELD LINE AUTOMATION D01 000010 12 1982-01-01 1983-02-01 -
  MA2110 W L PROGRAMMING D11 000060 9 1982-01-01 1983-02-01 MA2100
  MA2111 W L PROGRAM DESIGN D11 000220 2 1982-01-01 1982-12-01 MA2110
  MA2112 W L ROBOT DESIGN D11 000150 3 1982-01-01 1982-12-01 MA2110
  MA2113 W L PROD CONT PROGS D11 000160 3 1982-02-15 1982-12-01 MA2110
  OP1000 OPERATION SUPPORT E01 000050 6 1982-01-01 1983-02-01 -
  OP1010 OPERATION E11 000090 5 1982-01-01 1983-02-01 OP1000
  OP2000 GEN SYSTEMS SERVICES E01 000050 5 1982-01-01 1983-02-01 -
  OP2010 SYSTEMS SUPPORT E21 000100 4 1982-01-01 1983-02-01 OP2000
  OP2011 SCP SYSTEMS SUPPORT E21 000320 1 1982-01-01 1983-02-01 OP2010
  OP2012 APPLICATIONS SUPPORT E21 000330 1 1982-01-01 1983-02-01 OP2010
  OP2013 DB/DC SUPPORT E21 000340 1 1982-01-01 1983-02-01 OP2010
  PL2100 WELD LINE PLANNING B01 000020 1 1982-01-01 1982-09-15 MA2100

SALES Table


Name: SALES_DATE SALES_PERSON REGION SALES
Type: date varchar(15) varchar(15) int
Desc: Date of sales Employee's last name Region of sales Number of sales
Values: 12/31/1995 LUCCHESSI Ontario-South 1
  12/31/1995 LEE Ontario-South 3
  12/31/1995 LEE Quebec 1
  12/31/1995 LEE Manitoba 2
  12/31/1995 GOUNOT Quebec 1
  03/29/1996 LUCCHESSI Ontario-South 3
  03/29/1996 LUCCHESSI Quebec 1
  03/29/1996 LEE Ontario-South 2
  03/29/1996 LEE Ontario-North 2
  03/29/1996 LEE Quebec 3
  03/29/1996 LEE Manitoba 5
  03/29/1996 GOUNOT Ontario-South 3
  03/29/1996 GOUNOT Quebec 1
  03/29/1996 GOUNOT Manitoba 7
  03/30/1996 LUCCHESSI Ontario-South 1
  03/30/1996 LUCCHESSI Quebec 2
  03/30/1996 LUCCHESSI Manitoba 1
  03/30/1996 LEE Ontario-South 7
  03/30/1996 LEE Ontario-North 3
  03/30/1996 LEE Quebec 7
  03/30/1996 LEE Manitoba 4
  03/30/1996 GOUNOT Ontario-South 2
  03/30/1996 GOUNOT Quebec 18
  03/30/1996 GOUNOT Manitoba 1
  03/31/1996 LUCCHESSI Manitoba 1
  03/31/1996 LEE Ontario-South 14
  03/31/1996 LEE Ontario-North 3
  03/31/1996 LEE Quebec 7
  03/31/1996 LEE Manitoba 3
  03/31/1996 GOUNOT Ontario-South 2
  03/31/1996 GOUNOT Quebec 1
  04/01/1996 LUCCHESSI Ontario-South 3
  04/01/1996 LUCCHESSI Manitoba 1
  04/01/1996 LEE Ontario-South 8
  04/01/1996 LEE Ontario-North -
  04/01/1996 LEE Quebec 8
  04/01/1996 LEE Manitoba 9
  04/01/1996 GOUNOT Ontario-South 3
  04/01/1996 GOUNOT Ontario-North 1
  04/01/1996 GOUNOT Quebec 3
  04/01/1996 GOUNOT Manitoba 7

STAFF Table


Name: ID NAME DEPT JOB YEARS SALARY COMM
Type: smallint not null varchar(9) smallint char(5) smallint dec(7,2) dec(7,2)
Desc: Employee number Employee name Department number Job type Years of service Current salary Commission
Values: 10 Sanders 20 Mgr 7 18357.50 -

20 Pernal 20 Sales 8 18171.25 612.45

30 Marenghi 38 Mgr 5 17506.75 -

40 O'Brien 38 Sales 6 18006.00 846.55

50 Hanes 15 Mgr 10 20659.80 -

60 Quigley 38 Sales - 16808.30 650.25

70 Rothman 15 Sales 7 16502.83 1152.00

80 James 20 Clerk - 13504.60 128.20

90 Koonitz 42 Sales 6 18001.75 1386.70

100 Plotz 42 Mgr 7 18352.80 -

110 Ngan 15 Clerk 5 12508.20 206.60

120 Naughton 38 Clerk - 12954.75 180.00

130 Yamaguchi 42 Clerk 6 10505.90 75.60

140 Fraye 51 Mgr 6 21150.00 -

150 Williams 51 Sales 6 19456.50 637.65

160 Molinare 10 Mgr 7 22959.20 -

170 Kermisch 15 Clerk 4 12258.50 110.10

180 Abrahams 38 Clerk 3 12009.75 236.50

190 Sneider 20 Clerk 8 14252.75 126.50

200 Scoutten 42 Clerk - 11508.60 84.20

210 Lu 10 Mgr 10 20010.00 -

220 Smith 51 Sales 7 17654.50 992.80

230 Lundquist 51 Clerk 3 13369.80 189.65

240 Daniels 10 Mgr 5 19260.25 -

250 Wheeler 51 Clerk 6 14460.00 513.30

260 Jones 10 Mgr 12 21234.00 -

270 Lea 66 Mgr 9 18555.50 -

280 Wilson 66 Sales 9 18674.50 811.50

290 Quill 84 Mgr 10 19818.00 -

300 Davis 84 Sales 5 15454.50 806.10

310 Graham 66 Sales 13 21000.00 200.30

320 Gonzales 66 Sales 4 16858.20 844.00

330 Burke 66 Clerk 1 10988.00 55.50

340 Edwards 84 Sales 7 17844.00 1285.00

350 Gafney 84 Clerk 5 13030.50 188.00

STAFFG Table

Note:STAFFG is only created for double-byte code pages.

Name: ID NAME DEPT JOB YEARS SALARY COMM
Type: smallint not null vargraphic(9) smallint graphic(5) smallint dec(9,0) dec(9,0)
Desc: Employee number Employee name Department number Job type Years of service Current salary Commission
Values: 10 Sanders 20 Mgr 7 18357.50 -

20 Pernal 20 Sales 8 18171.25 612.45

30 Marenghi 38 Mgr 5 17506.75 -

40 O'Brien 38 Sales 6 18006.00 846.55

50 Hanes 15 Mgr 10 20659.80 -

60 Quigley 38 Sales - 16808.30 650.25

70 Rothman 15 Sales 7 16502.83 1152.00

80 James 20 Clerk - 13504.60 128.20

90 Koonitz 42 Sales 6 18001.75 1386.70

100 Plotz 42 Mgr 7 18352.80 -

110 Ngan 15 Clerk 5 12508.20 206.60

120 Naughton 38 Clerk - 12954.75 180.00

130 Yamaguchi 42 Clerk 6 10505.90 75.60

140 Fraye 51 Mgr 6 21150.00 -

150 Williams 51 Sales 6 19456.50 637.65

160 Molinare 10 Mgr 7 22959.20 -

170 Kermisch 15 Clerk 4 12258.50 110.10

180 Abrahams 38 Clerk 3 12009.75 236.50

190 Sneider 20 Clerk 8 14252.75 126.50

200 Scoutten 42 Clerk - 11508.60 84.20

210 Lu 10 Mgr 10 20010.00 -

220 Smith 51 Sales 7 17654.50 992.80

230 Lundquist 51 Clerk 3 13369.80 189.65

240 Daniels 10 Mgr 5 19260.25 -

250 Wheeler 51 Clerk 6 14460.00 513.30

260 Jones 10 Mgr 12 21234.00 -

270 Lea 66 Mgr 9 18555.50 -

280 Wilson 66 Sales 9 18674.50 811.50

290 Quill 84 Mgr 10 19818.00 -

300 Davis 84 Sales 5 15454.50 806.10

310 Graham 66 Sales 13 21000.00 200.30

320 Gonzales 66 Sales 4 16858.20 844.00

330 Burke 66 Clerk 1 10988.00 55.50

340 Edwards 84 Sales 7 17844.00 1285.00

350 Gafney 84 Clerk 5 13030.50 188.00

Footnotes:

116
For information related to this command, see the DB2SAMPL command in the Command Reference.

117
If the path parameter is not specified, the sample database is created in the default path specified by the DFTDBPATH parameter in the database manager configuration file.

118
If the drive parameter is not specified, the sample database is created on the same drive as DB2.

[ Top of Page | Previous Page | Next Page ]