Transact-SQL Reference
employee
| 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( ) |
1 The 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]').
2 The 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 |