Quan li nhan vien,trinh do ngoai ngu
create database bai13
create table nv
(Manv char(10) primary key
,Hoten nvarchar(30) not null
,diachi nvarchar(30)
,maP char(10)
,Luong money
,constraint nv_kn foreign key(MaP)references phong(MaP))
create table Phong
(MaP char(10) primary key
,TenP nvarchar(30) not null
,DiachiP nvarchar(30)
,DT char(15))
create table NN
(MaNN char(10) primary key
,TenNN nvarchar(30) not null)
create table TD_NN
(MaNV char(10) not null
,MaNN char(10) not null
,Trinhdo nvarchar(10)
,constraint khoachinh primary key (MaNV,MaNN)
,constraint kn1 foreign key(Manv)references nv(Manv)
,constraint kn2 foreign key(MaNN)references NN(MaNN))
create view v1
as
select phong.MaP,TenP,count(Manv) as SoNV
from nv,phong
where phong.MaP=nv.MaP
group by phong.MaP,TenP
create view v2
as
select *
from nv
where MaNV not in (select MaNV
from TD_NN)
create view v3
as
select phong.MaP,TenP,n.Hoten,Luong
from nv n, phong
where n.MaP=phong.MaP and luong=(select Max(luong)
from nv
where n.MaP=nv.MaP)
create proc t1
@TenNN nvarchar(30),@Trinhdo nvarchar(10)
as
select *
from nv
where Manv in (select Manv
from TD_NN
where MaNN in(select MaNN
from NN
where TenNN=@TenNN) and trinhdo=@trinhdo)
exec t1 N'tiếng anh','a'
create proc t2
@TenP nvarchar(30)
as
select phong.MaP,TenP,count(manv)
from nv,phong
where nv.MaP=phong.maP and tenP=@tenP
group by phong.MaP,TenP
exec t2 '103'
create proc t3
@TenNN nvarchar(30),@TenP nvarchar(30),@Trinhdo nvarchar(10)
as
select *
from nv
where MaP in(select MaP
from phong
where TenP=@TenP)and Manv in (select Manv
from TD_NN
where MaNN in(select MaNN
from NN
where TenNN=@TenNN) and trinhdo=@trinhdo)
exec t3 N'trung quốc','100','a'
create proc t4
@TenP nvarchar(30)
as
select phong.MaP,TenP,n.Hoten,Luong
from nv n, phong
where n.MaP=phong.MaP and luong=(select Max(luong)
from nv
where n.MaP=nv.MaP) and TenP=@TenP
exec t4 '103'
create trigger kt1
on nv
for insert
as
if (select luong
from inserted)<=0
begin
print N'dữ liệu nhập không hợp lệ'
rollback tran
end
else print N'dữ liệu nhập thành công'
insert into nv
values ('a7',N'hoàng',N'thái nguyên','4',-2)
declare nhap cursor
for
select * from nv
update nv
set luong=(luong+(luong*0.1))
declare @Manv char(10),@Hoten nvarchar(30),@diachi nvarchar(30),@MaP char(10),@luong money
open nhap
print cast(N'Mã NV' as nchar(10))+cast(N'Họ tên' as nchar(30))+cast(N'Địa chỉ' as nchar(30))+cast(N'Mã phòng' as nchar(10))+cast(N'Lương' as nchar(10))
fetch next from nhap
into @Manv,@Hoten,@diachi,@MaP,@luong
while @@fetch_status=0
begin
print cast(@Manv as nchar(10))+cast(@Hoten as nchar(30))+cast(@diachi as nchar(30))+cast(@MaP as nchar(10))+cast(@luong as nchar(10))
fetch next from nhap
into @Manv,@Hoten,@diachi,@MaP,@luong
end
close nhap
declare nhap1 cursor
for
select *
from nv
where MaNV in(select MaNV
from TD_NN
where trinhdo='c' and MaNN in(select MaNN
from NN
where TenNN=N'Tiếng anh'))
declare @Manv char(10),@Hoten nvarchar(30),@diachi nvarchar(30),@MaP char(10),@luong money
open nhap1
print cast(N'Mã NV' as nchar(10))+cast(N'Họ tên' as nchar(30))+cast(N'Địa chỉ' as nchar(30))+cast(N'Mã phòng' as nchar(10))+cast(N'Lương' as nchar(10))
fetch next from nhap1
into @Manv,@Hoten,@diachi,@MaP,@luong
while @@fetch_status=0
begin
print cast(@Manv as nchar(10))+cast(@Hoten as nchar(30))+cast(@diachi as nchar(30))+cast(@MaP as nchar(10))+cast(@luong as nchar(10))
fetch next from nhap1
into @Manv,@Hoten,@diachi,@MaP,@luong
end
close nhap1
Bạn đang đọc truyện trên: AzTruyen.Top