Màu nền Màu tối Mặc định Xám nhạt Xanh nhạt Xanh đậm Vàng nhạt Màu sepia Vàng đậm Vàng ố

Font chữ: Palatino Linotype Bookerly Segoe UI Patrick Hand Times New Roman Verdana Tahoma Arial

Chiều cao dòng: 100 120 140 160 180 200

Kích cỡ chữ:

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