1.1_创建studydb1数据库中的表.sql

这是我在学习《SQL Server数据库系统结构基础》时所用到的一个脚本文件

(
	cEmployeeCode	char(6) references Employee(cEmployeeCode),
	mAnnualSalary	money,
	siYear		smallint,
	constraint ast_pk primary key(cEmployeeCode,siYear)
)
go

raiserror('创建表  ExternalCandidate....',0,1)
create table ExternalCandidate
(
	cCandidateCode	char(6) constraint ectv_pk primary key,
	vFirstName	varchar(20),
	vLastName	varchar(20),
	vAddress	varchar(35),
	cCity		char(20),
	cState		char(15),
	cZip		char(10) constraint eczp_ck check(cZip like('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cCountryCode	char(3) references Country(cCountryCode),
	cPhone		char(15) constraint ecp_ck check(cPhone like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cPositionCode	char(4) references Position(cPositionCode),
	dDateOfApplication	datetime,
	cEmployeeReferralNo	char(6),
	cNewsAdNo	char(4) references NewsAd(cNewsAdNo),
	cAgencyCode	char(4) references RecruitmentAgencies(cAgencyCode),
	cContractRecruiterCode	char(4) references ContractRecruiter(cContractRecruiterCode),
	cJobFairCode	char(4) references JobFair(cJobFairCode),
	cCampusRecruitmentCode	char(4) references  CampusRecruitment(cCampusRecruitmentCode),
	cExEmployeeCode		char(6),
	vQualification	varchar(20),
	siPrevWorkExperience	smallint,
	dBirthDate	datetime,
	cSex		char,
	cCollegeCode	char(4),
	mPrevAnnualSalary	money,
	imPhotograph	image,
	vEmailId	varchar(20),
	cStatus		char,
	dTestDate	datetime,
	siTestScore	smallint,
	dInterviewDate	datetime,
	cInterviewer	char(20),
	vInterviewComments	varchar(256),
	cRating		char
)
go

raiserror('创建表  EmployeeReferrals....',0,1)
create table EmployeeReferrals
(
	cEmployeeReferralNo	char(6) constraint ert_pk primary key,
	cEmployeeCode	char(6) references Employee(cEmployeeCode),
	cCandidateCode	char(6) references ExternalCandidate(cCandidateCode),
)
go

raiserror('创建表  Skill....',0,1)
create table Skill
(
	cSkillCode	char(4) constraint stv_pk primary key,
	vSkill		varchar(35)
)
go

raiserror('创建表  EmployeeSkill....',0,1)
create table EmployeeSkill
(
	cEmployeeCode	char(6) references Employee(cEmployeeCode),
	cSkillCode	char(4)references Skill(cSkillCode),
	constraint vest_pk primary key(cEmployeeCode,cSkillCode)
)
go

raiserror('创建表  Requisition....',0,1)
create table Requisition
(
	cRequisitionCode	char(6),
	cPositionCode   char(4)references position(cPositionCode),
	dDateofRequisition	datetime,
	dDeadline       datetime,
	cDepartmentCode	char(4),
	vRegion		varchar(20),
	siNoOfVacancy	smallint,
	constraint RTP_PK primary key(cRequisitionCode,cPositionCode)
)
go

raiserror('创建表  CandidateSkill....',0,1)
create table CandidateSkill
(
	cCandidateCode	char(6) references ExternalCandidate(cCandidateCode),
	cSkillCode	char(4) references Skill(cSkillCode),
	constraint sctv_pk primary key(cCandidateCode,cSkillCode)
)

raiserror('创建表  PositionSkill....',0,1)
create table PositionSkill
(
	cPositionCode	char(4) references Position(cPositionCode),
	cSkillCode	char(4) references Skill(cSkillCode),
	constraint pstv_pk primary key(cPositionCode,cSkillCode)
)

raiserror('创建表  Payment....',0,1)
create table Payment
(
	cSourceCode	char(6) ,
	mAmount		Money,
	cChequeNo	char(12),
	dDate		datetime,
	constraint tp_pks primary key(cSourceCode,cChequeNo,dDate)
)
go

raiserror('插入数据到 RecruitmentUsers 表....',0,1)
insert into recruitmentusers values ('Sandra','Adams')
insert into recruitmentusers values ('William','Perez')
insert into recruitmentusers values ('David','Cooper')
go

raiserror('插入数据到 Department 表....',0,1)
insert into department values('0001','Production','Samuel Moore','New York')
insert into department values('0002','Sales','Donald Fleming','Houston')
insert into department values('0003','Marketing','Sandra Hawkins','Addison')
insert into department values('0004','Human Resource','Bill Mayse','Abilene')
insert into department values('0005','Quality Assurance','Robin Dmello','Hampstead')
insert into department values('0007','Training','Will Smith','Columbus')
insert into department values('0008','Transportation','Dabb Mike','Columbus')
insert into department values('0009','Finance','Faber Michel','New Jersy')
insert into department values('0010','Inventory','Taber James','New Jersy')
go

raiserror('插入数据到 Position 表....',0,1)
insert into position values('0001','Sales Manager',100,2001,82 )
insert into position values('0002','Maketing  Manager',100,2001,83 )
insert into position values('0003','Financial Analyst',50 ,2001,30 )
insert into position values('0004','Training Co-ord',20 ,2001,15 )
insert into position values('0005','Database Analyst',10 ,2001,8  )
insert into position values('0006','Staff Accountant',20 ,2001,18 )
insert into position values('0007','Audit Manager',20 ,2001,18 )
insert into position values('0008','Telephone Operator',20 ,2001,18 )
insert into position values('0009','Office Clerk',20 ,2001,18 )
insert into position values('0010','Legal Secretary',20 ,2001,18 )
insert into position values('0011','Administrative Assistant',20 ,2001,18 )
insert into position values('0012','Senior Receptionist',20 ,2001,18 )
insert into position values('0013','Consultant',20 ,2001,18 )
insert into position values('0014','Maintenance Technician',20 ,2001,18 )
insert into position values('0015','Receptionist',21,2001,18 )
go

raiserror('插入数据到 Country 表....',0,1)
insert into Country values('001','United states of America')
insert into Country values('002','Albania')
insert into Country values('003','Andorra')
insert into Country values('004','Argentina')
insert into Country values('005','Austria')
insert into Country values('006','Bangladesh')
insert into Country values('007','Belgium')
insert into Country values('008','Bosnia & Herzegovina')
insert into Country values('009','Bulgaria')
insert into Country values('010','Czech Republic')
insert into Country values('011','Denmark')
insert into Country values('012','Yugoslavia ')
insert into country values('013','France')
insert into country values('014','Gibraltar')
insert into country values('015','Greece')
insert into country values('016','Georgia')
insert into country values('017','Croatia')
insert into country values('018','Estonia')
insert into country values('019','Iceland')
insert into country values('020','Italy')
insert into country values('021','Israel')
insert into country values('022','India')
insert into country values('023','Cyprus')
insert into country values('024','Russia')
insert into country values('025','Liechtenstein')
insert into country values('026','Great Britain')
insert into country values('027','Lithuania')
insert into country values('028','Luxembourg')
insert into country values('029','Yugoslavia')
insert into country values('030','Malta')
insert into country values('031','Moldova')
insert into country values('032','Monaco')
insert into country values('033','Netherlands')
insert into country values('034','Norway')
insert into country values('035','Poland')
insert into country values('036','Portugal')
insert into country values('037','Romania')
insert into country values('038','San Marino')
insert into country values('039','Slovak Republic')
insert into country values('040','Slovenia')
insert into country values('041','Finland')
insert into country values('042','Spain')
insert into country values('043','Switzerland')
insert into country values('044','Turkey')
insert into country values('045','Ukraine')
insert into country values('046','Hungary')
insert into country values('047','Germany')
insert into country values('048','Sweden')
insert into country values('049','Latvia')
insert into country values('050','India')
go

raiserror('插入数据到 Newspaper 表....',0,1)
insert into newspaper values('0001','Texas Times','Texas','General','Jackson Demello','4723 West Alabama','Houston','Texas','77015-4568','001','[phone]','[phone]')