职工

use ClassNorthwind

go

--创建职工表

create table Employee

(

EmployeeID int identity(100,1) not null,

Ename varchar(20)not null,

Age int not null,

Sex varchar(2)not null

)

go

--创建社会团体表

create table Caste

(

CID int identity(1,1) not null,

Ctitle varchar(32)not null,

CnameID int not null,

Address varchar(50)not null,

)

go

--创建参加表

create table Attend

(

EmployeeID int not null,

EID int not null,

ADate datetime ,

)

 

go

 

alter table Employee add constraint PK_EmployeeID primary key(EmployeeID),

constraint Ck_Age check(Age>0),

constraint CK_Sex check(Sex='男' or Sex='女'),

constraint DF_Sex2 default('男')for Sex

 

alter table Caste add constraint PK_CID primary key(CID),

constraint FK_CnameID foreign key(CnameID)references Employee(EmployeeID)

 

alter table Attend add constraint FK_EmployeeID2 foreign key(EmployeeID)references Employee(EmployeeID),

constraint FK_EID foreign key(EID)references Caste(CID),

constraint PK_EmpEID primary key(EmployeeID,EID),

constraint DF_ADate default(getdate())for ADate

 

 

insert Employee values('one',20,default)

insert Employee values('two',22,'女')

insert Employee values('three',23,default)

insert Employee values('four',20,'女')

insert Employee values('five',24,default)

insert Employee values('six',22,'女')

insert Employee values('seven',25,'女')

insert Employee values('eight',20,default)

insert Employee values('nine',26,default)

insert Employee values('eleven',25,'女')

insert Employee values('twelve',19,'男')

insert Employee values('thirteen',29,'女')

 

insert Caste values('开心到底',100,'北京')

insert Caste values('想玩就玩',101,'上海')

insert Caste values('一起来',102,'湖南')

 

insert Attend values(103,1,getdate())

insert Attend values(104,2,getdate())

insert Attend values(105,3,getdate())

insert Attend values(106,1,getdate())

insert Attend values(107,1,getdate())

insert Attend values(108,2,getdate())

insert Attend values(109,1,getdate())

insert Attend values(102,1,getdate())

insert Attend values(102,2,getdate()+2)

insert Attend values(102,3,getdate()+4)

insert Attend values(103,3,getdate()+4)

insert Attend values(103,2,getdate()+2)

insert Attend values(104,3,getdate()+2)

insert Attend values(108,3,getdate()+2)

insert Attend values(109,2,getdate()+2)

insert Attend values(110,2,getdate())

insert Attend values(101,2,getdate())

 

select * from Employee

select * from Caste

select * from Attend

 

--    2）建立下列两个视图

--社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；

if exists(select * from sysobjects where name='view_Caste')

drop view view_Caste

go

create view view_Caste

as

select  编号=EmployeeID,名称=Ctitle,负责人职工号=CnameID,负责人姓名=Ename,负责人性别=Sex from Employee join Caste on Employee.EmployeeID=Caste.CnameID

go

select * from view_Caste

go

 

--  参加人情况（职工号，姓名，社团编号，社团名称，参加日期）

if exists(select * from sysobjects where name='view_Attend')

drop view view_Attend

go

create view view_Attend

as

select 职工号=Employee.EmployeeID ,姓名=Ename,社团编号=CID,社团名称=Ctitle,参加日期=Adate from Employee join Attend on Employee.EmployeeID=Attend.EmployeeID join Caste on Attend.EID=Caste.CID

go

select * from view_Attend

 

--  3）查找参加开心到底或一起来的职工号和姓名

select Employee.EmployeeID,Ename from Employee join Attend on Employee.EmployeeID=Attend.EmployeeID join Caste on Attend.EID=Caste.CID

where CID in (select CID from Caste where Ctitle='开心到底' or Ctitle='一起来')

 

-- 4）查找没有参加任何社会团体的职工情况。

select Employee.EmployeeID, Ename,Age,Sex from Employee where EmployeeID not in (select EmployeeID from Attend)

 

--  5）查找参加了全部社会团体的职工情况。

select EmployeeID, Ename,Age,Sex from Employee

where  (select count(EmployeeID)from Attend where EmployeeID=Employee.EmployeeID)=(select count(CID)from Caste)

 

--  6）查找参加了职工号为“”的职工所参加的全部社会团体的职工号

select EmployeeID from Attend where EID in (select EID from Attend where EmployeeID=108) group by EmployeeID having count(EID)=(select count(EID) from Attend where EmployeeID=108) and EmployeeID<>108

 

--  7）求每个社会团体的参加人数。

select Ctitle, count(EmployeeID) from Attend join Caste on Attend.EID=Caste.CID group by EID,Ctitle

 

--  8）求参加人数最多的社会团体的名称和参加人数。

select top 1 Ctitle , count(EmployeeID)from Attend join Caste on Attend.EID=Caste.CID group by EID,Ctitle order by count(EmployeeID) desc

 

--  9）求参加人数超过人的社会团体的名称和负责人。

select Ctitle,CnameID from Caste join Attend on Attend.EID=Caste.CID group by CnameID,Ctitle having count(EmployeeID)>5

 

--  10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。

exec sp_addlogin '李平','123456'

go

use ClassNorthwind

go

exec sp_grantdbaccess '李平','liping'

go

grant select,insert,delete on Caste to liping

grant select,insert,delete on Attend to liping

go

 

 

 

--  3. 设工程_零件数据库中有四个基本表：

--创建 供应商表( 供应商（供应商代码，姓名，所在城市，联系电话）)

if exists(select * from sysobjects where name='victualer')

drop table victualer

go

create table victualer

(

VID int identity(1000,1) ,

Vname varchar(20) not null,

Adress varchar(32),

Phone varchar(13)

)

go

 

--创建工程表( 工程（工程代码，工程名，负责人，预算）；)

if exists(select * from sysobjects where name='project')

drop table project

go

create table project

(

PID int identity(1,1),

Ptitle varchar(50)not null,

Pname varchar(20)not  null,

Pbudget int not null

)

go

 

--创建零件表( 零件（零件代码，零件名，规格，产地，颜色）；)

if exists(select * from sysobjects where name='hardware')

drop table hardware

go

create table hardware

(

HID int identity(100,1),

Hname varchar(50) not null,

Hspec varchar(32)not null,

Haddress varchar(32),

Hcolor varchar(20)

)

go

 

--创建供应零件表( 供应零件（供应商代码，工程代码，零件代码，数量）)

if exists(select * from sysobjects where name='supply')

drop table supply

go

create table supply

(

SvID int not null,

SpID int not null,

ShID int not null,

Snum int not null

)

go

alter table victualer add constraint PK_VID primary key(VID),

constraint DF_Adress default('地址不详') for Adress,

constraint CK_Phone2 check(len(Phone)=13 or Phone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

go

alter table project add constraint PK_PID primary key(PID),

constraint CK_Pbudget check(Pbudget>=0)

go

alter table hardware add constraint PK_HID primary key(HID),

constraint DF_Haddress default('地址不详')for Haddress,

constraint DF_Hcolor default('白色')for Hcolor

go

alter table supply add constraint FK_SvID foreign key(SvID)references victualer(VID),

constraint FK_SpID foreign key(SpID)references project(PID),

constraint FK_ShID foreign key(ShID)references hardware(HID),

constraint CK_Snum check(Snum>=0)

go

 

 

insert victualer values('联想','上海','1234568901123')

insert victualer values('华硕','天津','1236734456098')

insert victualer values('七喜','深圳','1232096724765')

insert victualer values('宏基','北京','1394565892156')

 

insert project values('myProject1','one',150000)

insert project values('myProject2','two',550000)

insert project values('myProject3','three',35000000)

insert project values('myProject4','four',2500000)

insert project values('myProject5','five',550000)

insert project values('myProject6','six',35000000)

insert project values('myProject7','seven',12500000)

insert project values('myProject8','eight',85000000)

insert project values('myProject9','nine',650000)

insert project values('myProject10','ten',9500000)

 

insert hardware values('键盘','合格','上海','黑色')

insert hardware values('鼠标','合格','上海','白色')

insert hardware values('内存条','合格','天津','绿色')

insert hardware values('U盘','合格','天津','红色')

insert hardware values('主板','合格','北京','黑色')

insert hardware values('保护膜','合格','上海','红色')

 

--创建供应零件表( 供应零件（供应商代码，工程代码，零件代码，数量）)

insert supply values(1001,2,100,1000)

insert supply values(1001,4,101,100)

insert supply values(1002,5,102,10000)

insert supply values(1002,1,101,1000)

insert supply values(1003,4,102,100)

insert supply values(1002,6,103,1000)

insert supply values(1002,7,104,1000)

insert supply values(1003,2,100,100)

insert supply values(1003,4,103,100)

insert supply values(1001,4,104,100)

 

 

 

 -- l）找出天津市供应商的姓名和电话。

select Vname,Phone from victualer where Adress='天津'

 

--  2）查找预算在-1000000元之间的工程的信息，并将结果按预算降序排列

select * from project where Pbudget between 500000 and 1000000 order by Pbudget desc

 

-- 3）找出使用供应商七喜所供零件的工程号码。

select SpID from supply where SvID in(select VID from victualer where Vname='七喜')

 

-- 4）找出工程项目myProject3使用的各种零件名称及其数量。

select Hname,Snum from hardware join supply on hardware.HID=supply.ShID where SpID=(select PID from project where Ptitle='myProject3')

 

-- 5）找出上海厂商供应的所有零件号码。

select HID from hardware where Haddress='上海'

 

-- 6）找出使用上海产的零件的工程名称。

select SpID from supply where ShID in(select HID from hardware where Haddress='上海') group by SpID

 

-- 7）找出没有使用天津产零件的工程号码。

select SpID from supply where ShID not in(select HID from hardware where Haddress='天津')group by SpID

 

-- 8）把全部红色零件的颜色改成蓝色。

update hardware set Hcolor='蓝色'where Hcolor='红色'

 

-- 9）将由供应商供给工程代码为myProject1的零件键盘改为由供应，并作其他必要的修改。

update supply set SvID=1000 where SpID=(select PID from project where Ptitle='myProject1') and ShID=(select HID from hardware where Hname='键盘')

 

 

-- 10）从供应商关系中删除记录，并从供应零件关系中删除相应的记录。

if exists (select * from sysobjects where name='trig_delete')

drop trigger trig_delete

go

create trigger trig_delete

on victualer

 instead of delete

as

declare @VID int

select @VID =VID from deleted

delete from supply where SvID=@VID

delete from victualer where VID=@VID

go

delete from victualer where VID=(select VID from victualer where Vname='联想')

 

select * from victualer

select * from project

select* from hardware

select * from supply