employee (T-SQL)

Column_name Data type Nullable Default Check Key/index
emp_id empid no   yes (1) PK, nonclust.
fname varchar(20) no     Composite, clust. (2)
minit char(1) yes     Composite, clust.  (2)
lname varchar(30) no     Composite, clust.  (2)
job_id smallint no 1   FK jobs(job_id)
job_lvl tinyint no 10    
pub_id char(4) no '9952'   FK publishers(pub_id)
hire_date datetime no GETDATE( )    
1The CHECK constraint is defined as (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]')  OR
(emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]').

2The composite, clustered index is defined on lname, fname, and minit.


  

These tables show the contents of the employee table. The first column (emp_id) is repeated in the second table, along with columns 6 through 8.

emp_id (1) fname (2) minit (3) lname (4) job_id (5)
PMA42628M Paolo M Accorti 13
PSA89086M Pedro S Alfonso 14
VPA30890F Victoria P Ashworth 6
H-B39728F Helen NULL Bennett 12
L-B31947F Lesley NULL Brown 7
F-C16315M Francisco NULL Chang 4
PTC11962M Philip T Cramer 2
A-C71970F Aria NULL Cruz 10
AMD15433F Ann M Devon 3
ARD36773F Anabela R Domingues 8
PHF38899M Peter H Franken 10
PXH22250M Paul X Henriot 5
CFH28514M Carlos F Hernández 5
PDI47470M Palle D Ibsen 7
KJJ92907F Karla J Jablonski 9
KFJ64308F Karin F Josephs 14
MGK44605M Matti G Karttunen 6
POK93028M Pirkko O Koskitalo 10
JYL26161F Janine Y Labrune 5
M-L67958F Maria NULL Larsson 7
Y-L77953M Yoshi NULL Latimer 12
LAL21447M Laurence A Lebihan 5
ENL44273F Elizabeth N Lincoln 14
PCM98509F Patricia C McKenna 11
R-M53550M Roland NULL Mendel 11
RBM23061F Rita B Müller 5
HAN90777M Helvetius A Nagy 7
TPO55093M Timothy P O'Rourke 13
SKO22412M Sven K Ottlieb 5
MAP77183M Miguel A Paolino 11
PSP68661F Paula S Parente 8
M-P91209M Manuel NULL Pereira 8
MJP25939M Maria J Pontes 5
M-R38834F Martine NULL Rancé 9
DWR65030M Diego W Roel 6
A-R89858F Annette NULL Roulet 6
MMS49649F Mary M Saveley 8
CGS88322F Carine G Schmitt 13
MAS70474F Margaret A Smith 9
HAS54740M Howard A Snyder 12
MFS52347M Martín F Sommer 10
GHT50241M Gary H Thomas 9
DBT39435M Daniel B Tonini 11

  

emp_id (1) job_lvl (6) pub_id (7) hire_date (8)
PMA42628M 35 0877 Aug 27 1992 12:00AM
PSA89086M 89 1389 Dec 24 1990 12:00AM
VPA30890F 140 0877 Sep 13 1990 12:00AM
H-B39728F 35 0877 Sep 21 1989 12:00AM
L-B31947F 120 0877 Feb 13 1991 12:00AM
F-C16315M 227 9952 Nov 3 1990 12:00AM
PTC11962M 215 9952 Nov 11 1989 12:00AM
A-C71970F 87 1389 Oct 26 1991 12:00AM
AMD15433F 200 9952 Jul 16 1991 12:00AM
ARD36773F 100 0877 Jan 27 1993 12:00AM
PHF38899M 75 0877 May 17 1992 12:00AM
PXH22250M 159 0877 Aug 19 1993 12:00AM
CFH28514M 211 9999 Apr 21 1989 12:00AM
PDI47470M 195 0736 May 9 1993 12:00AM
KJJ92907F 170 9999 Mar 11 1994 12:00AM
KFJ64308F 100 0736 Oct 17 1992 12:00AM
MGK44605M 220 0736 May 1 1994 12:00AM
POK93028M 80 9999 Nov 29 1993 12:00AM
JYL26161F 172 9901 May 26 1991 12:00AM
M-L67958F 135 1389 Mar 27 1992 12:00AM
Y-L77953M 32 1389 Jun 11 1989 12:00AM
LAL21447M 175 0736 Jun 3 1990 12:00AM
ENL44273F 35 0877 Jul 24 1990 12:00AM
PCM98509F 150 9999 Aug 1 1989 12:00AM
R-M53550M 150 0736 Sep 5 1991 12:00AM
RBM23061F 198 1622 Oct 9 1993 12:00AM
HAN90777M 120 9999 Mar 19 1993 12:00AM
TPO55093M 100 0736 Jun 19 1988 12:00AM
SKO22412M 150 1389 Apr 5 1991 12:00AM
MAP77183M 112 1389 Dec 7 1992 12:00AM
PSP68661F 125 1389 Jan 19 1994 12:00AM
M-P91209M 101 9999 Jan 9 1989 12:00AM
MJP25939M 246 1756 Mar 1 1989 12:00AM
M-R38834F 75 0877 Feb 5 1992 12:00AM
DWR65030M 192 1389 Dec 16 1991 12:00AM
A-R89858F 152 9999 Feb 21 1990 12:00AM
MMS49649F 175 0736 Jun 29 1993 12:00AM
CGS88322F 64 1389 Jul 7 1992 12:00AM
MAS70474F 78 1389 Sep 29 1988 12:00AM
HAS54740M 100 0736 Nov 19 1988 12:00AM
MFS52347M 165 0736 Apr 13 1990 12:00AM
GHT50241M 170 0736 Aug 9 1988 12:00AM
DBT39435M 75 0877 Jan 1 1990 12:00AM

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.