Use Master
If exists
(Select [name] from sys.databases
Where [name]='Projects')
Begin
Drop Database Projects
End
Go
Create Database Projects
Go
Use Projects
Go
Create Table Employee
(
EmployeeKey int identity(1000,1),
Constraint PK_Employee Primary Key(EmployeeKey),
EmployeeLastName NVarchar(255) not null unique,
EmployeeFirstName Nvarchar(255) null,
EmployeeSalary Money default 0,
--Constraint chk_Salary check (EmployeeSalary between 0 and 100000)
)
Go
Alter table Employee
Add Constraint chk_Salary check (EmployeeSalary between 0 and 100000)
Go
Create Table Project
(
ProjectKey int identity(1,1) primary key,
ProjectName Nvarchar(255) not null,
ProjectStartDate Date,
ProjectDescription xml
)
Go
Alter Table Project
Add ProjectEndDate Date
--Alter Table Project
--Drop column ProjectEndDate
Go
--Drop table Project
Create Table EmployeeProject
(
EmployeeKey int,
ProjectKey int,
Constraint FK_Employee Foreign Key (EmployeeKey)
References Employee(EmployeeKey) on delete cascade on update cascade,
Constraint FK_Project Foreign Key (ProjectKey)
References Project(ProjectKey) on delete cascade on update cascade,
Constraint PK_EmployeeProject primary key(EmployeeKey, ProjectKey)
)
--default
--unique
--check
--cascade
Insert into Employee( EmployeeLastName, EmployeeFirstName, EmployeeSalary)
Values('Smith','Joe',100000)
Insert into Employee( EmployeeLastName, EmployeeFirstName)
Values('Smythe','Joe')
Select * from Employee
Insert into Project(ProjectName, ProjectStartDate, ProjectDescription, ProjectEndDate)
Values('pitchfork','2/9/2011',
'<projectDescription>
<header>
Project for something or other
</header>
<body>
This project will cost a lot of money and take a lot of time.
</body>
</projectDescription>'
,null)
Select * from Project
No comments:
Post a Comment