Untitled

use master
go
create database QLSV
go
use QLSV
go
create table SV(
masv nchar(10) not null primary key,
hoten nvarchar(20),
quequan nvarchar(20),
gioitinh nchar(10),
ngaysinh date,
malop nchar(10)
)
--
create table LOP(
malop nchar(10) not null primary key,
tenlop nvarchar(20),
phong nvarchar(20),
magv char(10)
)
--
create table GV(
magv char(10) not null primary key,
hotengv nvarchar(20),
sodt nchar(10)
)
alter table SV add constraint fk_sv_lop foreign key(malop) references LOP(malop)
alter table LOP add constraint fk_lop_gv foreign key(magv) references GV(magv)
--
go
insert into GV Values('GV01', N'Nguyen Mai Linh', '0961982703'),
('GV02', N'Tran Manh Hung', '0973514904')
--
insert into LOP Values('HTTT01', N'Co So Du Lieu', N'P701', 'GV01'),
('HTTT02', N'SQL SERVER', N'P702', 'GV02'),
('HTTT03', N'Ki Thuat Lap Trinh', N'P703', 'GV01'),
('HTTT04', N'Do Hoa May Tinh', N'P701', 'GV02')
--
insert into SV Values('601375',N'Bui Viet Tuan',N'Nam Dinh',N'Nam','09-19-2005',N'HTTT02'),
('601234', N'Pham Duc Thinh', N'Hai Duong', N'Nam', '09-21-2005', 'HTTT01'),
('601345', N'Bui Anh Quan', N'Hung Yen', N'Nam', '06-07-2005', 'HTTT04'),
('601789', N'Dao Xuan Xam', N'Soc Son', N'Nam', '10-22-2005', 'HTTT03'),
('601268', N'Nguyen Xuan Tu', N'Hoai Duc', N'Nu', '11-22-2005', 'HTTT02'),
('601475', N'Nguyen Thanh Tung', N'Hai Phong', N'Nam', '12-26-2005', 'HTTT04')

--
create view vw_DSSVND
as 
       select * from SV where quequan = N'Nam Dinh'
 select * from vw_DSSVND
--
create function fn_vd1(@msv nchar(10))
returns nvarchar(20)
as
begin
  declare @ten nvarchar(20)
   set @ten = (select hoten from SV where masv = @msv)
   return @ten
end
 --
 select dbo.fn_vd1('601375')
 select dbo.fn_vd1('601234')
--
create function fn_vd2(@x int, @y int, @z nvarchar(20))
returns int
as
begin
  declare @tong int
  set @tong = (select count(*)
              from SV inner join LOP on  SV.malop=LOP.malop
			  where tenlop =@z
			  and year(getdate())-year(ngaysinh) between @x and @y
			  )
  return @tong
  end
  select dbo.fn_vd2(14,20,'HTTT01')
  --
create function fn_vd3(@x nvarchar(20), @y nvarchar(20))
returns int
as
begin
  declare @dem int
  set @dem = (select count(*)
              from SV inner join LOP on SV.malop=LOP.malop
			          inner join GV on LOP.maGV=GV.magv
					  where gv.hotengv =@x and tenlop = @y)
	return @dem
end
select dbo.fn_vd3(N'Tran Manh Hung','HTTT02')
--
create function fn_vd4(@x nvarchar(20), @y nchar(10), @z nvarchar(20))
returns @bang table(
                   masv nchar(10),
				                hoten nvarchar(20),
								quequan nvarchar(20),
								tuoi int,
								tenlop nvarchar(20),
								phong nvarchar(20))
as
begin
  insert into @bang
              select masv,sv.hoten,quequan,year(getdate())-year(ngaysinh),
			              tenlop,phong
						  from SV inner join LOP on SV.malop=LOP.malop
						          inner join GV on LOP.magv=GV.magv
								  where tenlop = @x and gioitinh = @y and gv.hotengv =@z
	return
end
select * from fn_vd4('HTTT01', N'Nam', N'Pham Duc Thinh')