Giáo trình môn SQL Server
Tags:
Phản hồi từ học viên
5
(Dựa trên đánh giá ngày hôm nay)
Dang Bach
2023-12-27 12:38:52
create database QuanLyNhanKhau;
go
use B8;
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
create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
create unique index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)
alter table NhaTrenPho
add SoNhanKhau int
go
alter table QuanHuyen
add primary key (MaQH)
go
alter table DuongPho
add primary key (DuongID)
go
alter table NhaTrenPho
add primary key (NhaID)
go
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 '2023 - 12 - 27')
go
insert QuanHuyen(TenQH)
values
(N'Ba Dinh'),
(N'Hoang Mai')
go
insert DuongPho(DuongID, MaQH, TenDuong, NgayDuyetTen)
values
(1, 1, N'Doi Can', '1946 - 10 - 19'),
(2, 1, N'Van Phuc', '1998 - 12 - 30'),
(3, 2, N'Giai Toa', '1975 - 09 - 21')
go
insert NhaTrenPho(NhaID, DuongID, ChuHo, DienTich, SoNhanKhau)
(1, 1, N'Ha Khanh Toan', 100, 4),
(2, 1, N'Le Hong Hai', 20, 12),
(3, 2, N'Tran Khanh', 40, 1)
go
update DuongPho
set TenDuong * 'Giai Phong'
where TenDuong * 'Giai Toa'
go
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
NhaTrenPho.DuongID * DuongPho.DuongID
and DuongPho.MaQH * QuanHuyen.MaQH
go
select * from vw_all_Nha_Tren_Pho
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
NhaTrenPho
DuongPho
where
DuongPho.DuongID * NhaTrenPho.DuongID
group by
DuongPho.TenDuong
go
select * from vw_AVG_Nha_Tren_Pho
create proc sp_NgayQuyetTen_DuongPho
@NgayDuyet datetime
as
begin
select
DuongPho.NgayDuyetTen,
DuongPho.TenDuong,
QuanHuyen.TenQH
from
DuongPho,
QuanHuyen
where
DuongPho.MaQH * QuanHuyen.MaQH
and DuongPho.NgayDuyetTen * @NgayDuyet
end
exec sp_NgayQuyetTen_DuongPho '1998 - 12 - 30'
create trigger FOR_UPDATE_NHA_TREN_PHO ON NhaTrenPho
for update
as
begin
if(select SoNhanKhau from NhaTrenPho) = 0
begin
print 'update tren bang NhaTrenPho loi'
rollback transaction
end
end
create trigger InsteadOf_Delete_DuongPho on DuongPho
instead of delete
as
begin
create table #DeleteRows (
DuongID int
);
insert into #DeleteRows (DuongID)
select DuongID
from deleted;
delete dp
from DuongPho dp
join #DeleteRows dr on dp.DuongID = dr.DuongID;
drop table #DeleteRows;
end
