-- Tao database QuanLyNhanKhau
create database QuanLyNhanKhau
go

use QuanLyNhanKhau
go

-- Tao Tables
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

-- INDEX
create clustered index [CI_NhaTrenPho_NhaID] on NhaTrenPho (NhaID)
go

create nonclustered index [UI_QuanHuyen_TenQH] on QuanHuyen (TenQH)
go

alter table NhaTrenPho
add SoNhanKhau int
go

-- CONSTRAINT
---- PRIMARY KEY
alter table QuanHuyen
add constraint PK_QuanHuyen primary key (MaQH)
go

alter table DuongPho
add constraint PK_DuongPho primary key (DuongID)
go

alter table NhaTrenPho
add constraint PK_NhaTrenPho primary key (NhaID)
go

---- FOREIGN KEY
alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)
go

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

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

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

select * from QuanHuyen

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

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

-- Query
select * from DuongPho

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

-- View
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 QuanHuyen.MaQH = DuongPho.MaQH
	and NhaTrenPho.DuongID = DuongPho.DuongID

select * from [vw_all_Nha_Tren_Pho]

create view [vw_AVG_Nha_Tren_Pho]
as
select DuongPho.TenDuong, AVG(NhaTrenPho.DienTich) DienTichTB, AVG(NhaTrenPho.SoNhanKhau) SoNhanKhauTB
from DuongPho, NhaTrenPho
where DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong

select * from [vw_AVG_Nha_Tren_Pho]
order by DienTichTB, SoNhanKhauTB asc

-- PROCEDUCE
create proc [sp_NgayQuyetTen_DuongPho]
	@NgayDuyetTen datetime
as
	select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
	from DuongPho, QuanHuyen
	where DuongPho.MaQH = QuanHuyen.MaQH
		and DuongPho.NgayDuyetTen = @NgayDuyetTen

exec [sp_NgayQuyetTen_DuongPho] '1998-12-30'

declare @NgayDuyetTen datetime
select @NgayDuyetTen = convert(datetime, '30/12/1998', 103)
exec [sp_NgayQuyetTen_DuongPho] @NgayDuyetTen

-- Trigger
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = 0 where NhaID = 3

update NhaTrenPho set SoNhanKhau = 0

create trigger [FOR_UPDATE_NHA_TREN_PHO] on NhaTrenPho
for update
as
begin
	if (select SoNhanKhau from inserted) <= 0
	begin
		print N'So nhan khau yeu cau > 0'
		rollback transaction
	end
end

alter trigger [FOR_UPDATE_NHA_TREN_PHO] on NhaTrenPho
for update
as
begin
	if (select count(SoNhanKhau) from inserted where SoNhanKhau <= 0) > 0
	begin
		print N'So nhan khau yeu cau > 0'
		rollback transaction
	end
end

-- Trigger
create trigger [InsteadOf_Delete_DuongPho] on DuongPho
instead of delete
as
begin
	-- xoa tat ca foreign key
	delete from NhaTrenPho where DuongID in (select DuongID from deleted)

	-- xoa chi no
	delete from DuongPho where DuongID in (select DuongID from deleted)
end

delete from DuongPho where DuongID = 1

Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL