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

-- Create database: QuanLyNhanKhau
create database QuanLyNhanKhau

-- Active database: QuanLyNhanKhau
use QuanLyNhanKhau

-- Create tables
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
)

-- Clustered Index
create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho (NhaID)

-- Non-clustered Index
create nonclustered index UI_QuanHuyen_TenQH on QuanHuyen (TenQH)

-- Add column: SoNhanKhau tren bang NhaTrenPho
alter table NhaTrenPho
add SoNhanKhau int

-- Constraint
---- 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 data
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, 2, 'Tran Van Diep', 100, 4),
(2, 1, 'Le Hong Hai', 20, 12),
(3, 2, 'Tran Khanh Toan', 40, 1)

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

-- update DuongPho: Giai Toa -> Giai Phong
update DuongPho set TenDuong = 'Giai Phong' where TenDuong = 'Giai Toa'
go

-- Tao view
create view vw_all_Tra_Tren_Pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo, NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from QuanHuyen join DuongPho on QuanHuyen.MaQH = DuongPho.MaQH
	join NhaTrenPho on NhaTrenPho.DuongID = DuongPho.DuongID
go

select * from vw_all_Tra_Tren_Pho
go

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

select * from vw_AVG_Nha_Tren_Pho
order by 'Dien Tich Trung Binh', 'So Nhan Khau Trung Binh'
go

-- Proc
create proc sp_NgayDuyetTen_DuongPho
	@NgayDuyetTen datetime
as
begin
	select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen
	from QuanHuyen, DuongPho
	where QuanHuyen.MaQH = DuongPho.MaQH
		and DuongPho.NgayDuyetTen = @NgayDuyetTen
end

exec sp_NgayDuyetTen_DuongPho '1998-12-30'
go

declare @NgayDuyetTen datetime
set @NgayDuyetTen = convert(datetime, '30/12/1998', 103)
-- select @NgayDuyetTen = convert(datetime, '30/12/1998', 103)
exec sp_NgayDuyetTen_DuongPho @NgayDuyetTen
go

-- Trigger
create trigger for_update_nha_tren_pho on NhaTrenPho
for update
as
begin
	if(select count(*) from inserted where SoNhanKhau <= 0) > 0
	begin
		print N'Khong duoc thay doi SoNhanKhau <= 0'
		rollback transaction
	end
end

update NhaTrenPho set SoNhanKhau = -10
go

-- trigger
create trigger instead_of_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

-- test
delete from DuongPho where DuongID = 2