2021-01-30 08:53:03

create database QuanLyNhanKhau
use QuanLyNhanKhau

create table QuanHuyen
(
	MaQH int identity(1,1) not null,
	TenQH nvarchar(100)
)

create table DuongPho
(
	DuongID int not null,
	MaQH int not null,
	TenDuong nvarchar(max) not null,
	NgayDuyetTen datetime
)

create table NhaTrenPho
(
	NhaID int not null,
	DuongID int not null,
	ChuHo nvarchar(50),
	DienTich money
)

alter table NhaTrenPho
add SoNhanKhau int

alter table QuanHuyen
add constraint PK_QuanHuyen primary key (MaQH)

alter table DuongPho
add constraint PK_DuongPho primary key (DuongID)

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaID)

alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho (DuongID)

alter table DuongPho
add constraint FK_DuongPho_QuanHuyen foreign key (MaQH) references QuanHuyen (MaQH)

alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between '1945-09-02' and getdate())

insert into QuanHuyen(TenQH)
values
('Ba Dinh'),
('Haong Mai')

insert into DuongPho(DuongID, MaQH, TenDuong, NgayDuyetTen)
values
('1', '1', 'Doi Can', '1946-10-19'),
('2', '1', 'Van Phuc', '1998-12-30'),
('3', '2', 'Giai Toa', '1975-09-21')

insert into NhaTrenPho(NhaID, DuongID, ChuHo, DienTich, SoNhanKhau)
values
('1', '1', 'Ha Khanh Toan', '100', '4'),
('2', '1', 'Le Hong Hai', '20', '12'),
('3', '2', 'Tran Khanh', '40', '1')

update DuongPho
	set TenDuong = 'Giai Phong'
		where TenDuong = 'Giai Toa'

create view vw_all_Nha_Tren_Pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo, NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
	from QuanHuyen, NhaTrenPho, DuongPho
		where DuongPho.DuongID = NhaTrenPho.DuongID and QuanHuyen.MaQH = DuongPho.MaQH

create view vw_AVG_Nha_Tren_Pho
as 
select DuongPho.TenDuong, AVG(NhaTrenPho.DienTich) as DienTichTrungBinh, AVG(NhaTrenPho.SoNhanKhau) as SoNhanKhauTrungBinh 
	from DuongPho, NhaTrenPho
		where DuongPho.DuongID = NhaTrenPho.DuongID
			group by DuongPho.TenDuong

select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
select * from vw_AVG_Nha_Tren_Pho order by DienTichTrungBinh asc, SoNhanKhauTrungBinh asc
select * from vw_all_Nha_Tren_Pho

2020-12-09 06:23:27

create database QuanLyNhanKhau 

use QuanLyNhanKhau

create table QuanHuyen(
MaQH int identity(1,1) not null,
TenQH nvarchar(100)
)
go

create table DuongPho(
DuongID INT NOT NULL,
MaQH INT NOT NULL,
TenDuong NVARCHAR(MAX) NOT NULL,
NgayDuyetTen DATETIME NULL
)
go

create table NhaTrenPho(
NhaID INT NOT NULL,
DuongID INT NOT NULL,
ChuHo NVARCHAR(50) NULL,
DienTich MONEY NULL
)
go


create clustered index  [CI_NhaTrenPho_NhaID] on NhaTrenPho(NhaID)

create nonclustered index [UI_QuanHuyen_TenQH] on QuanHuyen(TenQH)


alter table NhaTrenPho
add SoNhanKhau int

alter table QuanHuyen
add constraint PK_QuanHuyen primary key (MaQH)

alter table DuongPho
add constraint PK_DuongPho primary key (DuongID)

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaID)


alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)

alter table DuongPho
add constraint FK_DuongPho_QuanHuyen foreign key (MaQH) references QuanHuyen(MaQH)


INSERT into QuanHuyen(TenQH)
values
('Duy Hung'),
('Cau Giay')

INSERT into DuongPho(DuongID,MaQH,TenDuong,NgayDuyetTen)
values
('1' ,'1' ,'VN' ,'1941-10-1'),
('2','1','Duy Hung','1991-12-30'),
('3', '2', 'Cau Giay','1971-010-1')

INSERT into NhaTrenPho(NhaID,DuongID,ChuHo,DienTich,SoNhanKhau)
values
(1, 1, 'Nguyen Phan Dong' ,100 ,4),
(2, 1, 'Do Mac Nam' ,20, 12),
(3, 2, 'Nguyen Anh Vu',40,1)


update DuongPho set TenDuong = N'VN' where TenDuong = 'Giai Phong'


select* from QuanHuyen
select* from DuongPho
select* from NhaTrenPho

create VIEW vw_all_Nha_Tren_Pho 
AS
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo,  NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from QuanHuyen, DuongPho, NhaTrenPho 
where NhaTrenPho.DuongID= DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH

select * from vw_all_Nha_Tren_Pho 

create view vw_AVG_Nha_Tren_Pho
AS
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) as Dientichtrungbinh, avg(NhaTrenPho.SoNhanKhau) as Sonhankhautrungbinh
FROM DuongPho, NhaTrenPho where NhaTrenPho.DuongID= DuongPho.DuongID
group by DuongPho.TenDuong

select * from vw_AVG_Nha_Tren_Pho
order by Dientichtrungbinh asc


select* from QuanHuyen
select* from DuongPho
select* from NhaTrenPho

create PROC [sp_NgayQuyetTen_DuongPho]
	@NgayDuyet datetime
AS
BEGIN
select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
from DuongPho inner join QuanHuyen on DuongPho.MaQH = QuanHuyen.MaQH where DuongPho.NgayDuyetTen =  @NgayDuyet
END

EXEC [sp_NgayQuyetTen_DuongPho] '1991-12-30'



create trigger SoNhanKhau on NhaTrenPho
for UPDATE
AS
BEGIN
 if ( select SoNhanKhau from inserted) <= 0 
 begin
	print N'So nhan khau phai lon hon 0'
	rollback transaction
 end
END

--test trigger update Sonhankhau
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = 0 where NhaID =1

----Tao trigger cho phep xoa ten Duong o bang Duong pho
select* from QuanHuyen
select* from DuongPho
select* from NhaTrenPho

create trigger InsteadOf_Delete_DuongPho on DuongPho
	instead of delete
AS
BEGIN
	delete from NhaTrenPho where DuongID in (select DuongID from deleted)
	delete from DuongPho where DuongID in (select DuongID from deleted)
END

delete from DuongPho where DuongID = 3
delete from DuongPho where DuongID = 2

2020-12-08 20:40:23

create database QuanLyNhanKhau
use QuanLyNhanKhau

-----bang quan huyen
create table QuanHuyen (
MaQH INT IDENTITY(1,1) NOT NULL,
TenQH NVARCHAR(100)
)
----bang duong pho
create table DuongPho (
DuongID INT NOT NULL,
MaQH INT NOT NULL,
TenDuong NVARCHAR(MAX) NOT NULL,
NgayDuyetTen DATETIME NULL
)
----bang nha tren pho
create table NhaTrenPho (
NhaID INT NOT NULL,
DuongID INT NOT NULL,
ChuHo NVARCHAR(50) NULL,
DienTich MONEY NULL
)

alter table NhaTrenPho 
add SoNhanKhau int

create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
create nonclustered index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)

-------primary key

alter table QuanHuyen
add constraint PK_QuanHuyen primary key (MaQH)

alter table DuongPho
add constraint PK_DuongPho primary key (DuongID)

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaID)

-------foreign key

alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)

alter table DuongPho
add constraint FK_DuongPho_QuanHuyen foreign key (MaQH) references QuanHuyen(MaQH)

-------check

alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between 1945-09-02 and getdate())

insert into QuanHuyen(TenQH)
values
('Ba Đinh'),
('Hoang Mai')

insert into DuongPho (DuongID,MaQH,TenDuong,NgayDuyetTen)
values
(1,1,'Đoi Can','1946-10-19'),
(2, 1,'Van Phuc','1998-12-30'),
(3, 2,'Giai Toa','1975-09-21')

insert into NhaTrenPho (NhaID , DuongID , ChuHo , DienTich , SoNhanKhau)
values
('1','1','Ha Khanh Toan','100','4'),
('2','1','Le Hong Hai','20','12'),
('3','2','Tran Khanh',' 40','1')

select * from NhaTrenPho
select * from DuongPho
select * from QuanHuyen

update DuongPho
set TenDuong = 'Giai Phong'  
where TenDuong = 'Giai Toa'

alter view vw_all_Nha_Tren_Pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong,DuongPho.NgayDuyetTen,NhaTrenPho.ChuHo,NhaTrenPho.DienTich,NhaTrenPho.SoNhanKhau
from  NhaTrenPho ,DuongPho,QuanHuyen
where NhaTrenPho.DuongID = DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH

select * from vw_all_Nha_Tren_Pho

alter view vw_AVG_Nha_Tren_Pho
as
select DuongPho.TenDuong,avg(NhaTrenPho.DienTich) as dien_tinh_trung_binh, avg (NhaTrenPho.SoNhanKhau) as so_nhan_khau_trung_binh
from DuongPho , NhaTrenPho,QuanHuyen
where NhaTrenPho.DuongID = DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH
group by DuongPho.TenDuong

select * from vw_AVG_Nha_Tren_Pho 
order by 'dien_tinh_trung_binh','so_nhan_khau_trung_binh' desc


create proc sp_NgayQuyetTen_DuongPho 
@NgayDuyet datetime 
as
select DuongPho.NgayDuyetTen,DuongPho.TenDuong ,QuanHuyen.TenQH
from DuongPho,QuanHuyen,NhaTrenPho
where NhaTrenPho.DuongID = DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH and DuongPho.NgayDuyetTen = @NgayDuyet 

exec sp_NgayQuyetTen_DuongPho '1998-12-30'

create trigger FOR_UPDATE_NHA_TREN_PHO on NhaTrenPho
for update
as
begin
 if (select SoNhanKhau from inserted) <= 0
 begin
 print N'nhap lai di ban oi '
 rollback transaction
 end
end

create trigger InsteadOf_Delete_DuongPho on DuongPho
instead of delete 
as
begin
delete from NhaTrenPho where DuongID in (select DuongID from deleted)
delete from DuongPho where DuongID in (select DuongID from deleted)
end

2020-12-07 06:46:29

create database QuanLyNhanKhau 
use QuanLyNhanKhau

create table QuanHuyen(
MaQH int identity(1,1) not null,
TenQH nvarchar(100),
)

create table DuongPho(
DuongID INT NOT NULL,
MaQH INT NOT NULL,
TenDuong NVARCHAR(MAX) NOT NULL,
NgayDuyetTen DATETIME NULL)

create table NhaTrenPho(
NhaID INT NOT NULL,
DuongID INT NOT NULL,
ChuHo NVARCHAR(50) NULL,
DienTich MONEY NULL,
)

--Danh Index
create clustered index  [CI_NhaTrenPho_NhaID] on NhaTrenPho(NhaID)
create nonclustered index [UI_QuanHuyen_TenQH] on QuanHuyen(TenQH)

--Tao them cot SoNhanKhau bang NhaTrenPho
alter table NhaTrenPho
add SoNhanKhau int

--ADD primary key, foreign key, check
----PRIMARY KEY
alter table QuanHuyen
add constraint PK_QuanHuyen primary key (MaQH)

alter table DuongPho
add constraint PK_DuongPho primary key (DuongID)

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaID)

----FOREIGN KEY KEY
select* from QuanHuyen
select* from DuongPho
select* from NhaTrenPho

alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)

alter table DuongPho
add constraint FK_DuongPho_QuanHuyen foreign key (MaQH) references QuanHuyen(MaQH)

--INSERT DATA
INSERT into QuanHuyen(TenQH)
values
('Ba Đình'),
('Hoàng Mai')

INSERT into DuongPho(DuongID,MaQH,TenDuong,NgayDuyetTen)
values
('1' ,'1' ,'Đội Cấn' ,'1946-10-19'),
('2','1','Vạn Phúc','1998-12-30'),
(3, 2, 'Giải Tỏa','1975-09-21')

INSERT into NhaTrenPho(NhaID,DuongID,ChuHo,DienTich,SoNhanKhau)
values
(1, 1, 'Hà Khánh Toà' ,100 ,4),
(2, 1, 'Lê Hồng Hải' ,20, 12),
(3, 2, 'Trần Khánh',40,1)

--Update ten duong 
update DuongPho set TenDuong = N'Giải Phóng' where TenDuong = 'Giải Tỏa'

--VIEWS
select* from QuanHuyen
select* from DuongPho
select* from NhaTrenPho

create VIEW vw_all_Nha_Tren_Pho 
AS
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo,  NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from QuanHuyen, DuongPho, NhaTrenPho 
where NhaTrenPho.DuongID= DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH

select * from vw_all_Nha_Tren_Pho 

create view vw_AVG_Nha_Tren_Pho
AS
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) as Dientichtrungbinh, avg(NhaTrenPho.SoNhanKhau) as Sonhankhautrungbinh
FROM DuongPho, NhaTrenPho where NhaTrenPho.DuongID= DuongPho.DuongID
group by DuongPho.TenDuong

select * from vw_AVG_Nha_Tren_Pho
order by Dientichtrungbinh asc

--PROCEDURE
select* from QuanHuyen
select* from DuongPho
select* from NhaTrenPho

create PROC [sp_NgayQuyetTen_DuongPho]
	@NgayDuyet datetime
AS
BEGIN
select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
from DuongPho inner join QuanHuyen on DuongPho.MaQH = QuanHuyen.MaQH where DuongPho.NgayDuyetTen =  @NgayDuyet
END

EXEC [sp_NgayQuyetTen_DuongPho] '1998-12-30'


--Trigger
----Update sonhankhau phai lon hon 0
create trigger SoNhanKhau on NhaTrenPho
for UPDATE
AS
BEGIN
 if ( select SoNhanKhau from inserted) <= 0 
 begin
	print N'So nhan khau phai lon hon 0'
	rollback transaction
 end
END

--test trigger update Sonhankhau
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = 0 where NhaID =1

----Tao trigger cho phep xoa ten Duong o bang Duong pho
select* from QuanHuyen
select* from DuongPho
select* from NhaTrenPho

create trigger InsteadOf_Delete_DuongPho on DuongPho
	instead of delete
AS
BEGIN
	delete from NhaTrenPho where DuongID in (select DuongID from deleted)
	delete from DuongPho where DuongID in (select DuongID from deleted)
END

delete from DuongPho where DuongID = 3
delete from DuongPho where DuongID = 2

2020-12-07 04:32:33

create database quanlynhankhau
use quanlynhankhau

create table QuanHuyen(
	MaQH int identity(1,1) not null,
	TenQH nvarchar(100)
)

create table DuongPho(
	DuongID int not null,
	MaQH int not null,
	TenDuong nvarchar(max) not null,
	NgayDuyetTen datetime null
)

create table NhaTrenPho (
NhaID INT NOT NULL,
DuongID INT NOT NULL,
ChuHo NVARCHAR(50) NULL,
DienTich MONEY NULL
)

create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
create nonclustered index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)

alter table NhaTrenPho
add SoNhanKhau int

alter table QuanHuyen
add constraint PK_QuanHuyen primary key (MaQH)

alter table DuongPho
add constraint PK_DuongPho primary key (DuongID)

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaID)

alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)

alter table DuongPho
add constraint FK_DuongPho_QuanHuyen foreign key (MaQH) references QuanHuyen(MaQH)

alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between 1945-09-02 and getdate())

insert into QuanHuyen(TenQH)
values
('Ba Dinh'),
('Hoang Mai')

select * from QuanHuyen

insert into DuongPho (DuongID,MaQH,TenDuong,NgayDuyetTen)
values
(1, 1,'Doi Can','1946-10-19'),
(2, 1,'Van Phuc','1998-12-30'),
(3, 2,'Giai Toa','1975-09-21')

select * from DuongPho

insert into NhaTrenPho (NhaID , DuongID , ChuHo , DienTich , SoNhanKhau)
values
('1','1','Ha Khanh Toan','100','4'),
('2','1','Le Hong Hai','20','12'),
('3','2','Tran Khanh',' 40','1')

select * from NhaTrenPho

update DuongPho set TenDuong = 'Giai Phong' where TenDuong = 'Giai Toa'

create view vw_all_nha_tren_pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo, NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from  NhaTrenPho, DuongPho, QuanHuyen
where NhaTrenPho.DuongID = DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH

select * from vw_all_nha_tren_pho

create view vw_avg_nha_tren_pho
as
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) as 'Dien Tich Trung Binh', avg(NhaTrenPho.SoNhanKhau) as 'So Nhan Khau Trung Binh'
from  NhaTrenPho, DuongPho
where NhaTrenPho.DuongID = DuongPho.DuongID
group by DuongPho.TenDuong

select * from vw_avg_nha_tren_pho
order by 'Dien Tich Trung Binh', 'So Nhan Khau Trung Binh' desc

create proc sp_ngayduyetten_duongpho
	@NgayDuyet datetime 
as
select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
from DuongPho, QuanHuyen
where DuongPho.MaQH = QuanHuyen.MaQH and DuongPho.NgayDuyetTen = @NgayDuyet 

declare @ngayduyet datetime
select @ngayduyet = convert(datetime, '30/12/1998', 103)
exec sp_ngayduyetten_duongpho @ngayduyet 

create trigger for_update_nhatrenpho on NhaTrenPho
for update
as
begin
	if (select SoNhanKhau from inserted) <= 0
	begin
		print N'Loi: So nhan khau phai > 0'
		rollback transaction
	end
end

create trigger insteadof_delete_duongpho on DuongPho
	instead of delete 
as
begin
	delete from NhaTrenPho where DuongID in (select DuongID from deleted)
	delete from DuongPho where DuongID in (select DuongID from deleted)
end

2020-12-06 03:59:43

create database QuanLyNhanKhau
use QuanLyNhanKhau

-----bang quan huyen
create table QuanHuyen (
MaQH INT IDENTITY(1,1) NOT NULL,
TenQH NVARCHAR(100)
)
----bang duong pho
create table DuongPho (
DuongID INT NOT NULL,
MaQH INT NOT NULL,
TenDuong NVARCHAR(MAX) NOT NULL,
NgayDuyetTen DATETIME NULL
)
----bang nha tren pho
create table NhaTrenPho (
NhaID INT NOT NULL,
DuongID INT NOT NULL,
ChuHo NVARCHAR(50) NULL,
DienTich MONEY NULL
)

alter table NhaTrenPho 
add SoNhanKhau int

create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
create nonclustered index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)

-------primary key

alter table QuanHuyen
add constraint PK_QuanHuyen primary key (MaQH)

alter table DuongPho
add constraint PK_DuongPho primary key (DuongID)

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaID)

-------foreign key

alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)

alter table DuongPho
add constraint FK_DuongPho_QuanHuyen foreign key (MaQH) references QuanHuyen(MaQH)

-------check

alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between 1945-09-02 and getdate())

insert into QuanHuyen(TenQH)
values
('Ba Đinh'),
('Hoang Mai')

insert into DuongPho (DuongID,MaQH,TenDuong,NgayDuyetTen)
values
(1,1,'Đoi Can','1946-10-19'),
(2, 1,'Van Phuc','1998-12-30'),
(3, 2,'Giai Toa','1975-09-21')

insert into NhaTrenPho (NhaID , DuongID , ChuHo , DienTich , SoNhanKhau)
values
('1','1','Ha Khanh Toan','100','4'),
('2','1','Le Hong Hai','20','12'),
('3','2','Tran Khanh',' 40','1')

select * from NhaTrenPho
select * from DuongPho
select * from QuanHuyen

update DuongPho
set TenDuong = 'Giai Phong'  
where TenDuong = 'Giai Toa'

alter view vw_all_Nha_Tren_Pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong,DuongPho.NgayDuyetTen,NhaTrenPho.ChuHo,NhaTrenPho.DienTich,NhaTrenPho.SoNhanKhau
from  NhaTrenPho ,DuongPho,QuanHuyen
where NhaTrenPho.DuongID = DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH

select * from vw_all_Nha_Tren_Pho

alter view vw_AVG_Nha_Tren_Pho
as
select DuongPho.TenDuong,avg(NhaTrenPho.DienTich) as dien_tinh_trung_binh, avg (NhaTrenPho.SoNhanKhau) as so_nhan_khau_trung_binh
from DuongPho , NhaTrenPho,QuanHuyen
where NhaTrenPho.DuongID = DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH
group by DuongPho.TenDuong

select * from vw_AVG_Nha_Tren_Pho 
order by 'dien_tinh_trung_binh','so_nhan_khau_trung_binh' desc


create proc sp_NgayQuyetTen_DuongPho 
@NgayDuyet datetime 
as
select DuongPho.NgayDuyetTen,DuongPho.TenDuong ,QuanHuyen.TenQH
from DuongPho,QuanHuyen,NhaTrenPho
where NhaTrenPho.DuongID = DuongPho.DuongID and DuongPho.MaQH = QuanHuyen.MaQH and DuongPho.NgayDuyetTen = @NgayDuyet 

exec sp_NgayQuyetTen_DuongPho '1998-12-30'

create trigger FOR_UPDATE_NHA_TREN_PHO on NhaTrenPho
for update
as
begin
 if (select SoNhanKhau from inserted) <= 0
 begin
 print N'nhap lai di ban oi '
 rollback transaction
 end
end

create trigger InsteadOf_Delete_DuongPho on DuongPho
instead of delete 
as
begin
delete from NhaTrenPho where DuongID in (select DuongID from deleted)
delete from DuongPho where DuongID in (select DuongID from deleted)
end

2020-12-05 09:51:10

create database QuanLyNhanKhau
use QuanLyNhanKhau

create table QuanHuyen (
	MaQH int identity(1, 1) not null,
	TenQH nvarchar(100)
)

create table DuongPho (
	DuongId int not null,
	MaQH int not null,
	TenDuong nvarchar(max) not null,
	NgayDuyetTen datetime null
)

create table NhaTrenPho (
	NhaId int not null,
	DuongId int not null,
	ChuHo nvarchar(50) null,
	DienTich money null
)

create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(NhaId)

create nonclustered index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)

alter table NhaTrenPho 
add SoNhanKhau int

alter table QuanHuyen 
add constraint PK_QuanHuyen primary key (MaQH)

alter table DuongPho 
add constraint PK_DuongPho primary key (DuongId)

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaId)

alter table NhaTrenPho 
add constraint FK_NhaTrenPho_DuongPho
foreign key (DuongId) references DuongPho (DuongId)

alter table DuongPho 
add constraint FK_DuongPho_QuanHuyen
foreign key (MaQH) references QuanHuyen(MaQH)

alter table DuongPho 
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between '1945-09-02' and getdate())

insert into QuanHuyen( TenQH)
values
( 'Ba Dinh'),
( 'Hoang Mai')

insert into DuongPho(DuongId, MaQH, TenDuong, NgayDuyetTen)
values 
(1, 1, 'Doi Can', '1946-10-19'),
(2, 1, 'Van Phuc', '1998-12-30'),
(3, 2, 'Giai Toa', '1975-09-21')

insert into NhaTrenPho(NhaId, DuongId, ChuHo, DienTich, SoNhanKhau)
values
(1, 1, 'Ha Khanh Toan', 100, 4),
(2, 1, 'Le Hong Hai', 20, 12),
(3, 2, 'Tran Khanh', 40, 1)

select * from DuongPho
select * from QuanHuyen
select * from NhaTrenPho

update DuongPho set TenDuong='Giai Phong' where TenDuong='Giai Toa'

create view vw_all_Nha_Tren_Pho 
as
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo, NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from NhaTrenPho, DuongPho, QuanHuyen
where QuanHuyen.MaQH=DuongPho.MaQH and DuongPho.DuongId=NhaTrenPho.DuongId 

select * from vw_all_Nha_Tren_Pho

create view vw_AVG_Nha_Tren_Pho
as
select DuongPho.TenDuong, AVG(NhaTrenPho.DienTich) 'Dien Tich Trung Binh', AVG(NhaTrenPho.SoNhanKhau) 'SNK Trung Binh'
from DuongPho, NhaTrenPho where DuongPho.DuongId=NhaTrenPho.DuongId
group by DuongPho.TenDuong

select * from vw_AVG_Nha_Tren_Pho
order by 'Dien Tich Trung Binh', 'SNK Trung Binh' desc

create proc sp_NgayQuyetTen_DuongPho
	@NgayDuyet datetime
as
select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
from DuongPho, QuanHuyen 
where QuanHuyen.MaQH=DuongPho.MaQH and DuongPho.NgayDuyetTen=@NgayDuyet

declare @NDT datetime
select @NDT=convert(datetime, '30-12-1998', 103)
exec sp_NgayQuyetTen_DuongPho @NDT 

create trigger [FOR_UPDATE_NHA_TREN_PHO] on NhaTrenPho
for update 
as
begin
	if(select SoNhanKhau from inserted) =0
	begin
		print N'khong duoc phep nhap so nhan khau =0'
		rollback transaction
	end
end

update NhaTrenPho set SoNhanKhau=0 where NhaId=1 

create trigger [InsteadOf_Delete_DuongPho] on DuongPho
instead of delete
as
begin
	delete from NhaTrenPho where DuongId in (select DuongId from deleted)
	delete from DuongPho where DuongId in (select DuongId from deleted)
end

delete from DuongPho where DuongId=2

2020-08-29 12:42:19

-- TEST
select * from DuongPho
select * from NhaTrenPho
select * from QuanHuyen

-- Index
create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(id)

create nonclustered index UI_QuanHuyen_TenQH on QuanHuyen (TenQH)

-- Trigger
create trigger FOR_UPDATE_NHA_TREN_PHO on NhaTrenPho
for update
as
begin
	if (select SoNhanKhau from inserted) = 0
	begin
		print N'Khong dc phep nhap SoNhanKhau = 0'
		rollback transaction
	end
end

update NhaTrenPho set SoNhanKhau = 0 where id = 2

update NhaTrenPho set OwnerName = 'TVD' where id = 2


delete from QuanHuyen where MaQH = 'HM'

-- Xoa du lieu DuongPho => Lien quan toi 'HM' => quay sang xoa du lieu DuongPho => QuanHuyen
create trigger InsteadOf_Delete_DuongPho on DuongPho
instead of delete
as
begin
	delete from NhaTrenPho where MaDP in (select MaDP from deleted)
	delete from DuongPho where MaDP in (select MaDP from deleted)
end

delete from DuongPho where MaDP = 'GP01'

-- Xoa du lieu MaQH
delete from QuanHuyen where MaQH = 'HM'

create trigger InsteadOf_Delete_QuanHuyen on QuanHuyen
instead of delete
as
begin
	delete from NhaTrenPho where MaDP in (select MaDP from DuongPho where MaQH in (select MaQH from deleted))
	delete from DuongPho where MaQH in (select MaQH from deleted)
	delete from QuanHuyen where MaQH in (select MaQH from deleted)
end