数据库期末大作业（非计算机专业）

数据库期末大作业，老师布置的很简单，非常基础，仅供参考
公司：

create database company

drop database school


create table departmentone
(
	no nvarchar(50) not null primary key,
	name nvarchar(50) not null,
	sex nvarchar(50) not null,
	years nvarchar(50) not null

)



  insert into departmentone(no,name,sex,years)
  values(52101,'一','男',9),(52102,'二','女',5),
		(52103,'三','女',8),(52104,'十','男',7),
		(52105,'四','男',1),(52106,'七','女',4),
		(52107,'五','女',9),(52108,'八','女',5),
		(52109,'六','男',8),(52110,'九','男',9)


create table departmenttwo
(
	no nvarchar(30) not null primary key,
	name nvarchar(30) not null,
	sex nvarchar(30) not null,
	years nvarchar(30) not null
)


  insert into departmenttwo(no,name,sex,years)
  values(52101,'一','男',9),(52102,'二','女',5),
		(52103,'三','女',8),(52104,'十','男',7),
		(52105,'四','男',1),(52106,'七','女',4),
		(52107,'五','女',9),(52108,'八','女',5),
		(52109,'六','男',8),(52110,'九','男',9)

create table departmentthree
(
	no nvarchar(30) not null primary key,
	name nvarchar(30) not null,
	sex nvarchar(30) not null,
	years nvarchar(30) not null
)



  insert into departmentthree(no,name,sex,years)
  values(52101,'一','男',9),(52102,'二','女',5),
		(52103,'三','女',8),(52104,'十','男',7),
		(52105,'四','男',1),(52106,'七','女',4),
		(52107,'五','女',9),(52108,'八','女',5),
		(52109,'六','男',8),(52110,'九','男',9)


		alter table departmentthree
		 add constraint CK_Employees_EmpAge 
		 check(years>=0 and years<=100)

	
		 alter table departmentthree 
		 add constraint UQ_Employees_EmpName 
		 unique(name)
		
				alter table departmentthree
			add constraint DF_time 
			 default(getdate()) for years

select * from departmentthree where name='一'
select name 姓名,sex 性别,years 成绩 from departmentthree where sex='男'


delete from departmentthree where name='三'


alter table departmentthree
add eva1 nvarchar(30)  null


  execute sp_rename 'departmentthree.name','departmentthree'


select sex 性别, max(years)工龄
 from departmentthree group by sex

 select sex 性别, min(years)分数
 from departmentthree group by sex




select departmentthree.name ,departmentone.name  from departmentthree,departmentone

where departmentthree.name=departmentone.name 



select no 工号 from departmentthree
where years in
(
	select years from departmentthree
	 where years>=10
)


create view shitu 
as
select * from departmentthree
where sex='男'
with check option


create index suoyin_1
on departmentthree(name)






use school
go
create proc 
统计人数
as
begin
delete from departmentthree _num(班级编号, 总人数),
select班级编号,count(*) from 部门 group by 工号
end
go