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

-- Tao CSDL BT1821
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

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')
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 DuongPho set TenDuong = 'Giai Phong' where TenDuong = 'Giai Toa'
go

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

select * from vw_all_Nha_Tren_Pho --vw_all_Nha_Tren_Pho: template table (Duoc tao ra khi chay select)

-- Views
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' asc
go

alter view vw_AVG_Nha_Tren_Pho
as
select top(10) 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
order by 'Dien Tich Trung Binh', 'So Nhan Khau Trung Binh' asc
go

select * from vw_AVG_Nha_Tren_Pho

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

declare @NgayDuyet datetime -- Khai bao tham so @NgayDuyet
select @NgayDuyet = convert(datetime, '30/12/1998', 103) -- Chuyen 30/12/1998 -> dua ve format chuan 1998-12-30 -> set du lieu vao @NgayDuyet
exec sp_NgayDuyetTen_DuongPho @NgayDuyet
go

declare @NgayDuyet datetime -- Khai bao tham so @NgayDuyet
-- Khi muon gan 1 du lieu cho 1 bien -> su dung them keyword: set
set @NgayDuyet = convert(datetime, '30/12/1998', 103) -- Nen su dung cach nay cho de.
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'Khong dc update column SoNhanKhau <= 0'
		rollback transaction
	end
end

-- Test
update NhaTrenPho set SoNhanKhau = -10 where NhaID = 1

-- Trigger
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho

delete from DuongPho where DuongID = 1
go

delete from NhaTrenPho where DuongID = 1

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

	-- Xoa du lieu chinh no
	delete from DuongPho where DuongID in (select DuongID from deleted)
end

delete from DuongPho where DuongID = 1
go