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

-- Tao CSDL
create database BT1821
go

-- Kich hoat CSDL
use BT1821
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
)
go

create table NhaTrenPho (
	NhaID int not null,
	DuongID int not null,
	ChuHo nvarchar(50),
	DienTich money
)
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

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

-- Insert Data
insert into QuanHuyen (TenQH)
values
('Ba Dinh'),
('Hoang Mai')
go

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')
go

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

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

select * from DuongPho

-- 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 DuongPho.DuongID = NhaTrenPho.DuongID
go

select * from vw_all_Nha_Tren_Pho

-- Views
create view vw_AVG_Nha_Tren_Pho
as
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) 'DienTichTrungBinh', count(NhaTrenPho.SoNhanKhau) 'SoNhanKhauTrungBinh'
from DuongPho, NhaTrenPho
where DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
go

select * from vw_AVG_Nha_Tren_Pho
order by DienTichTrungBinh, SoNhanKhauTrungBinh asc
go

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

exec sp_NgayDuyetTen_DuongPho '1998-12-30'
go

---- https://www.w3schools.com/sql/func_sqlserver_convert.asp
declare @NgayDuyet datetime
set @NgayDuyet = Convert(datetime, '30/12/1998', 103)
exec sp_NgayDuyetTen_DuongPho @NgayDuyet
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 'Yeu cau sonhankhau > 0'
		rollback transaction
	end
end

select * from NhaTrenPho

update NhaTrenPho set SoNhanKhau = 0 where NhaID = 2
go

-- Trigger
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

select * from DuongPho
select * from NhaTrenPho

delete from DuongPho where DuongID = 1