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

-- TAO DATABASE
create database BT1821
go

-- Kich Hoat Database
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

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

select * from QuanHuyen
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

select * from DuongPho
go

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

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

-- Du lieu dau ra: TenQH (QuanHuyen), TenDuong (DuongPho), NgayDuyetTen (DuongPho), ChuHo (NhaTrenPho), DienTich (NhaTrenPho), SoNhanKhau (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 QuanHuyen.MaQH = DuongPho.MaQH
	and NhaTrenPho.DuongID = DuongPho.DuongID
go

select * from vw_all_Nha_Tren_Pho
go

-- View
---- Du lieu hien thi: TenDuong (DuongPho), DienTichTrungBinh (Ket qua tinh NhaTrenPho), So Nha Khau Trung Binh (Ket qua tinh NhaTrenPho)
create view vw_AVG_Nha_Tren_Pho
as
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) 'DienTichTrungBinh', avg(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 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

---- Test
exec sp_NgayDuyetTen_DuongPho '1998-12-30'
go

declare @NgayDuyet datetime
set @NgayDuyet = Convert(datetime, '30/12/1998', 103) -- '1998-12-30' -> @NgayDuyet
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 N'You don"t update SoNhaKhau <= 0'
		rollback transaction
	end
end

drop trigger for_update_nha_tren_pho

select * from NhaTrenPho

update NhaTrenPho set SoNhanKhau = -2 where NhaID = 3
go

-- Trigger: instead of delete
create trigger InsteadOf_Delete_DuongPho on DuongPho
instead of delete
as
begin
	-- Xoa foreign key: NhaTrenPho
	delete from NhaTrenPho where DuongID in (select DuongID from deleted)

	-- Xoa primary key: DuongPho
	delete from DuongPho where DuongID in (select DuongID from deleted)
end

select * from DuongPho
select * from NhaTrenPho

delete from DuongPho where DuongID = 1
go