SQL基础第二章课后作业

use master
go
if exists (select * from sys.sysdatabases where name = 'companyDB' )
	drop database companyDB
go
create database companyDB
go
use companyDB 
go
--职位表
create table jobs
(
	job_id nvarchar(10)  not null, -- 主键职位编号
	job_title nvarchar(20) not null, --职位名称
	min_salary money not null,  --最低底薪、
	max_salary money not null  -- 最高底薪
)
go
--部门表
create table departments
(
	department_id int identity(1,1) not null ,--部门编号
	department_name nvarchar(30) not null,   --部门名称
	manager_id int null                       --部门主管编号外键引用employees_id
)
go
--职员表
create table employees
(
	employees_id int identity(100,1) not null ,    --职员编号
	first_name nvarchar(16) not null, --职员名称
	last_name nvarchar(16) not null , --职员姓
	email nvarchar(16) not null,    --职员邮件
	phone_number nvarchar(20) not null, --职员电话
    hire_date datetime  not null,   ---入职时间
	job_id nvarchar(10) not null,--职员职位编号，外键引用job_id
	salay money   null,    --职员薪资大于0
	manager_id int null,   --职员主管编号外键引用employees_id
	department_id int null  --职员部门编号，外键引用department_id
)
go
--任职记录表
create table history
(
	employees_id int  not null ,    --职员编号
    start_time datetime  not null,   ---开始时间
	end_time datetime  not null,   ---结束时间
	job_id nvarchar(10) not null,--职员职位编号，外键引用job_id
	department_id int null  --职员部门编号，外键引用department_id
)
go

--职位表约束
alter table jobs
		add constraint PF_jobs_job_id primary key(job_id),
			constraint CK_jobs_min_salary  check(min_salary > 1000 or min_salary = 1000),
			constraint CK_jobs_max_salary check(   max_salary > min_salary  or max_salary = min_salary )
insert into  jobs  values('Yuan1','主管',1000,1000)
insert into  jobs  values('Yuan2','经理',1000,1000)
drop table jobs
select * from jobs
alter table jobs
		drop constraint CK_jobs_max_salary
--部门表约束
alter table departments
		add constraint PK_departments_department_id primary key(department_id)
go
alter table departments
		add constraint FK_departments_employees_manager_id foreign key(manager_id) references employees(employees_id)
--为职员表添加约束
alter  table employees
		add constraint PK_employees_employees_id primary key(employees_id)
alter  table employees
		add constraint DF_employees_hire_date default getdate() for hire_date
alter  table employees
		add constraint CK_employees_salay check(salay>0)
alter  table employees
		add constraint FK_employees_manager_id_employees_id foreign key(manager_id) references employees(employees_id)
alter  table employees
		add constraint FK_employees_departments_department_id foreign key(department_id) references departments(department_id)
--为任职记录表添加约束
alter table history
		add constraint PK_history_employees_id_start_time primary key(employees_id,start_time)
alter table history
		add constraint  DF_history_end_time default getdate() for end_time
alter table history 
		add constraint FK_history_jobs_job_id foreign key(job_id) references jobs(job_id)
alter table history
		add constraint FK_history_departments_department_id foreign key(department_id) references departments(department_id)