继续请教一个面试题目,高手帮忙啊,先谢了!
Scenario:
A company has several small tables in their database. These tables consisted of two columns and less than one thousand rows.

Below an example of table schema,
create table hairColor
(
hairColorIDint identity(1,1),
descriptionvarchar(16) not null

)
Go
insert into hairColor values(‘Brown’)
insert into hairColor values(‘Blonde’)


create table eyeColor
(
eyeColorIDint identity(1,1),
namevarchar(10) not null

)
Go
insert into hairColor values(‘Black’)
insert into hairColor values(‘Grey’)

create table submitStatus
(
statusIDint identity(1,1),
descriptionvarchar(30) not null

)
Go

Question:
How would you combine these tables into one table and enforce the data integrity? Please provide the table schema and few insert statement to demonstration how to fill the table.


[解决办法]
create table T
(
id int identity(1,1),
description varchar(30) not null,
flag varchar(02) not null
)

insert into T(description,flag)
select 'Brown ', 'H ' union all
select 'Blonde ', 'H ' union all
select 'Black ', 'E ' union all
select 'Gray ', 'E ' union all
select 'xxx ', 'S '
.....
[解决办法]
create table T
(
id int identity(1,1),
description varchar(30) not null,
eyeColorID int not null,
hairColorID int not null,
statusID int not null
)
[解决办法]
create table hairColor
(
hairColorIDint identity(1,1),
descriptionvarchar(16) not null

)
Go
insert into hairColor values(‘Brown’)
insert into hairColor values(‘Blonde’)


create table eyeColor
(
eyeColorIDint identity(1,1),
namevarchar(10) not null

)
Go
insert into hairColor values(‘Black’)
insert into hairColor values(‘Grey’)

create table submitStatus
(
statusIDint identity(1,1),
descriptionvarchar(30) not null

)
Go

select id=identity(int,1,1),* into #t from
(
select description,1 as flag from hairColor
union all
select description,2 from eyeColor
union all
select description,3 from submitStatus


)a