By GokiSoft.com|
19:54 27/12/2023|
SQL Server/MySQL
Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL
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:39:31
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
Nguyễn Hùng
2023-12-27 12:38:51
create database QuanLyNhanKhau
use QuanLyNhanKhau
create table QuanHuyen (
MaQH int identity(1,1) not null,
TenQH nvarchar(50)
)
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 CL_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
go
create unique nonclustered index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)
go
alter table NhaTrenPho
add SoNhanKhau int
go
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
alter table NhaTrenPho
add constraint FK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)
go
alter table DuongPho
add constraint DuongPho_QuanHuyen foreign key (MaQH) references QuanHuyen(MaQH)
go
alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between '1945-09-02' and now)
go
insert into QuanHuyen
values
('Ba Dinh'),
('Hoang Mai')
go
insert into DuongPho
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
values
(1, 1, 'Ha Khanh Toan', 100, 4),
(2, 1, 'Le Hong Hai', 20, 12),
(3, 2, 'Tran Khanh', 40, 1)
go
update DuongPho set TenDuong = 'Giai Phong' where TenDuong = 'Giai Toa'
go
create view vw_all_NhaTrenPho
as
select TenQH, TenDuong, ChuHo, DienTich, SoNhanKhau
from NhaTrenPho, DuongPho, QuanHuyen
where NhaTrenPho.DuongID = DuongPho.DuongID
and DuongPho.MaQH = QuanHuyen.MaQH
go
select * from vw_all_NhaTrenPho
go
create view vw_AVG_NhaTrenPho
as
select DuongPho.TenDuong, AVG(NhaTrenPho.DienTich) 'Dien Tich Trung Binh', AVG(NhaTrenPho.SoNhanKhau) 'So Nhan Khau Trung Binh'
from DuongPho, NhaTrenPho
where NhaTrenPho.DuongID = DuongPho.DuongID
group by DuongPho.TenDuong
go
select * from vw_AVG_NhaTrenPho
order by 'Dien Tich Trung Binh', 'So Nhan Khau Trung Binh' asc
go
create proc sp_AVG_NhaTrenPho
@NgayDuyet datetime
as
select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
from QuanHuyen, DuongPho
where QuanHuyen.MaQH = DuongPho.MaQH
and DuongPho.NgayDuyetTen = @NgayDuyet
go
declare @NgayDuyet datetime
select @NgayDuyet = CONVERT(datetime, '30/12/1998', 103)
exec sp_AVG_NhaTrenPho @NgayDuyet
go
create trigger FOR_UPDATE_NHATRENPHO on NhaTrenPho
for update
as
begin
if(select count(SoNhanKhau) from inserted where SoNhanKhau <= 0) > 0
begin
print N'Update tren bang NhaTrenPho loi'
rollback transaction
end
end
update NhaTrenPho set SoNhanKhau = -1 where NhaID = 1
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
delete from DuongPho where DuongID = 1![Võ Như Việt [C2010L]](https://www.gravatar.com/avatar/fb93c99beb23339eb21f4d4ffe8981af.jpg?s=80&d=mm&r=g){kind=avatar}
Võ Như Việt
2021-04-16 17:17:07
-- tao database moi---
create database QuanLyNhanKhau
-- su dung database đã tạo--
use QuanLyNhanKhau
-- tao bang Quan Huyen--
create table QuanHuyen(
MaQH int identity(1,1) not null,
TenQH nvarchar(100)
)
sp_rename 'QuanHuyen.TemQH','TenQH','COLUMN';
-- tao bang DuongPho--
create table DuongPho(
DuongID int not null,
MaQH int not null,
TenDuong nvarchar(Max) not null,
NgayDuyetTen datetime null
)
alter table DuongPho
alter column NgayDuyetTen datetime null
-- tao bang NhaTrenPho--
create table NhaTrenPho(
NhaID int not null,
DuongID int not null,
ChuHo nvarchar(50) null,
DienTich Money null
)
--3. tao index --
create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
create unique nonclustered index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)
drop index UI_QuanHuyen_TenQH
alter table NhaTrenPho
add SoNhanKhau int
--4. Tao Contrasint--
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)
--tao check--
alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between '1945-09-02' and getdate())
alter table DuongPho
drop constraint CK_DuongPho_NgayDuyetTen
--5. inserting data--
insert into QuanHuyen(TenQH)
values
('Ba Dinh'),
('Hoang Mai')
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')
select * from DuongPho
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)
--6 query operations--
update DuongPho set TenDuong = 'Giai Phong' where TenDuong = 'Giai Toa'
--7. View--
create view vw_all_Nha_Tren_Pho
as
select TenQH,TenDuong,NgayDuyetTen,ChuHo,DienTich,SoNhanKhau
from NhaTrenPho,DuongPho,QuanHuyen
where DuongPho.DuongID = NhaTrenPho.DuongID
and DuongPho.MaQH = QuanHuyen.MaQH
select * from vw_all_Nha_Tren_Pho
--8. view 2--
create view vw_AVG_Nha_Tren_Pho
as
select TenDuong, AVG(NhaTrenPho.DienTich)'Dien Tich Trung Binh',AVG(NhaTrenPho.SoNhanKhau)'So Nhan Khau TB'
from NhaTrenPho,DuongPho
where DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
select * from vw_AVG_Nha_Tren_Pho
order by 'Dien Tich Trung Binh','So Nhan Khau TB' asc
--9. Tao Procedure--
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
declare @NgayDuyet datetime
select @NgayDuyet = Convert (datetime,'30/12/1998',103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
--10.Trigger --
select * from NhaTrenPho
create trigger [FOR_UPDATE_NHA_TREN_PHO] on NhaTrenPho
for update
as
begin
if(select count(SoNhanKhau) from inserted where SoNhanKhau <= 0 ) > 0
begin
print N'So Nhan Khau Yeu Cau >0'
rollback transaction
end
end
update NhaTrenPho set SoNhanKhau = 0 where NhaID = 1
select * from NhaTrenPho
---trigger delete--
create trigger [InsteaOf_Delete_DuongPho] on DuongPho
instead of delete
as
begin
-- bat buoc xoa Forein key--
delete from NhaTrenPho where DuongID in (select DuongID from deleted)
--xoa y chinh
delete from DuongPho where DuongID in (select DuongID from deleted)
end
![Đỗ Minh Tâm [community,C2010G]](https://www.gravatar.com/avatar/b2f111c1b0e4273177f902fd0c0f11ae.jpg?s=80&d=mm&r=g){kind=avatar}
Đỗ Minh Tâm
2021-04-07 03:17:26
create database QuanLyNhanKhau
-- Active database
use QuanLyNhanKhau
-- Tao 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
)
create table NhaTrenPho
(
NhaID int not null,
DuongID int not null,
ChuHo nvarchar(50),
DienTich money
)
alter table NhaTrenPho
add SoNhanKhau int
-- 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)
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)
insert into QuanHuyen(TenQH)
values
('Ba Dinh'),
('Haong 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', '1', 'Ha Khanh Toan', '100', '4'),
('2', '1', 'Le Hong Hai', '20', '12'),
('3', '2', 'Tran Khanh', '40', '1')
-- TEST
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
create proc [sp_NgayQuyetTen_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_NgayQuyetTen_DuongPho '1998-12-30'
declare @NgayDuyet datetime
select @NgayDuyet = Convert(datetime, '30/12/1998', 103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
-- Trigger
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = 0 where NhaID = 1
update NhaTrenPho set SoNhanKhau = 0
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 sua SoNhanKhau <= 0'
rollback transaction
end
end
delete from DuongPho where DuongID = 3
delete from DuongPho where DuongID = 2
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![TRẦN VĂN ĐIỆP [Teacher]](https://www.gravatar.com/avatar/fc6ba9324e017d540af3613b3a77dd21.jpg?s=80&d=mm&r=g){kind=avatar}
TRẦN VĂN ĐIỆP
2021-04-07 02:08:24
-- 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 (MaQH, TenQH)
values
(1, 'Ba Dinh'),
(2, 'Hoang Mai')
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
-- Query
update DuongPho set TenDuong = 'Giai Phong' where TenDuong = 'Giai Toa'
-- 7.Views
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
select * from vw_all_Nha_Tren_Pho
-- 8.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
select * from vw_AVG_Nha_Tren_Pho
order by 'Dien Tich Trung Binh', 'So Nhan Khau Trung Binh'
-- 9. Proc
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'
declare @NgayDuyet datetime
set @NgayDuyet = convert(datetime, '30/12/1998', 103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
-- 10.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 'Khong duoc phep cap nhat SoNhanKhau <= 0'
rollback transaction
end
end
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = -3 where NhaID = 1
-- 11. 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 QuanHuyen
select * from NhaTrenPho
delete from DuongPho where DuongID = 1TRẦN VĂN ĐIỆP
2021-03-06 07:49:40
-- Tao database
create database QuanLyNhanKhau
-- Active database
use QuanLyNhanKhau
-- Tao 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
)
create table NhaTrenPho
(
NhaID int not null,
DuongID int not null,
ChuHo nvarchar(50),
DienTich money
)
alter table NhaTrenPho
add SoNhanKhau int
-- 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)
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)
insert into QuanHuyen(TenQH)
values
('Ba Dinh'),
('Haong 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', '1', 'Ha Khanh Toan', '100', '4'),
('2', '1', 'Le Hong Hai', '20', '12'),
('3', '2', 'Tran Khanh', '40', '1')
-- TEST
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
create proc [sp_NgayQuyetTen_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_NgayQuyetTen_DuongPho '1998-12-30'
declare @NgayDuyet datetime
select @NgayDuyet = Convert(datetime, '30/12/1998', 103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
-- Trigger
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = 0 where NhaID = 1
update NhaTrenPho set SoNhanKhau = 0
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 sua SoNhanKhau <= 0'
rollback transaction
end
end
delete from DuongPho where DuongID = 3
delete from DuongPho where DuongID = 2
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![Vũ Trung Kiên [C2009I]](https://www.gravatar.com/avatar/abf994de48c0204239812f8d9a7dcf8c.jpg?s=80&d=mm&r=g){kind=avatar}
Vũ Trung Kiên
2021-02-27 09:42:08
#1821.sql
create database QuanLyNhanKhau
use QuanLyNhanKhau
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
)
create CLUSTERED INDEX CI_NhaTrenPho_NhaID on NhaTrenPho (NhaID)
create NonClustered Index UI_QuanHuyen_TenQH on QuanHuyen (TenQH)
alter table NhaTrenPho
add SoNhanKhau int
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)
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)
insert into QuanHuyen(TenQH)
values
('Ba Đình'),
('Hoàng Mai')
insert into DuongPho(DuongID, MaQH, TenDuong, NgayDuyetTen)
values
(1, 1, 'Đội Cấn', '1946-10-19'),
(2, 1, 'Vạn Phúc', '1998-12-30'),
(3, 2, 'Giải Tỏa', '1975-09-21')
insert into NhaTrenPho(NhaID, DuongID, ChuHo, DienTich, SoNhanKhau)
values
(1, 1, 'Hà Khánh Toàn', 100, 4),
(2, 1, 'Lê Hồng Hải', 20, 12),
(3, 2, 'Trần Khánh', 40, 1)
update DuongPho
set TenDuong = 'Giải Phóng'
where DuongID = 3
create view vw_all_Nha_Tren_Pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo, NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from DuongPho left join QuanHuyen on QuanHuyen.MaQH = DuongPho.MaQH left join NhaTrenPho on DuongPho.DuongID = NhaTrenPho.DuongID
create view vw_AVG_Nha_Tren_Pho
as
select top(100) DuongPho.TenDuong, avg(NhaTrenPho.DienTich) as 'Diện Tích Trung Bình', avg(NhaTrenPho.SoNhanKhau) as 'Số Nhân Khẩu Trung Bình'
from DuongPho left join NhaTrenPho on DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
order by avg(NhaTrenPho.DienTich) asc
create proc sp_NgayQuyetTen_DuongPho
@NgayDuyet datetime
as
begin
select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
from DuongPho left join QuanHuyen on DuongPho.MaQH = QuanHuyen.MaQH
where NgayDuyetTen = @NgayDuyet
end
create trigger FOR_UPDATE_NHA_TREN_PHO on NhaTrenPho
for update
as
begin
if (select count(*) from inserted where SoNhanKhau = 0) > 0
begin
print 'Can co nguoi o'
rollback transaction
end
end
create trigger InsteadOf_Delete_DuongPho on Duongpho
instead of delete
as
begin
delete from NhaTrenPho
delete from DuongPho
end
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
select * from vw_all_Nha_Tren_Pho
select * from vw_AVG_Nha_Tren_Pho
exec sp_NgayQuyetTen_DuongPho '19981230'![Trinh Huy Hung [community,C2009I]](https://www.gravatar.com/avatar/c5cd1f25c7a1fbe45b7ee35a66ceeb6c.jpg?s=80&d=mm&r=g){kind=avatar}
Trinh Huy Hung
2021-02-27 09:15:16
create database QuanLyNhanKhau
use QuanLyNhanKhau
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
)
create clustered index CI_NhaTrenPho_NhaID
on NhaTrenPho(NhaID)
create index UI_QuanHuyen_TenQH
on QuanHuyen(TenQH)
alter table NhaTrenPho
add SoNhankhau int
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)
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)
alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between '1945-09-02' and getdate())
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, 1, 'Ha Khanh Toan', 100, 4),
(2, 1, 'Le Hong Hai', 20, 12),
(3, 2, 'Tran Khanh', 40, 1)
Update DuongPho
set TenDuong = 'Giai Phong'
where TenDuong = 'Giai Toa'
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
create view vw_AVG_Nha_Tren_Pho as
select top 2 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
order by [Dien Tich Trung Binh],[So Nhan Khau Trung Binh] asc
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.NgayDuyetTen = @NgayDuyet and DuongPho.MaQH=QuanHuyen.MaQH
end
declare @NgayDuyet datetime
select @NgayDuyet = convert(datetime, '30/12/1998', 103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
create trigger FOR_UPDATE_NHA_TREN_PHO on NhaTrenPho
for update
as
begin
if (select SoNhanKhau from inserted) <= 0
begin
print N'So nhan khau phai lon hon 0'
rollback transaction
end
end
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=2
![Lê Sĩ Tuyển [community,C2009I]](https://www.gravatar.com/avatar/22f5881f5e83b4dfb378a4c0ca7d67cd.jpg?s=80&d=mm&r=g){kind=avatar}
Lê Sĩ Tuyển
2021-01-30 09:09:04
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
)
create table NhaTrenPho
(
NhaID int not null,
DuongID int not null,
ChuHo nvarchar(50),
DienTich money
)
alter table NhaTrenPho
add SoNhanKhau int
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)
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)
alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between '1945-09-02' and getdate())
insert into QuanHuyen(TenQH)
values
('Ba Dinh'),
('Haong 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', '1', 'Ha Khanh Toan', '100', '4'),
('2', '1', 'Le Hong Hai', '20', '12'),
('3', '2', 'Tran Khanh', '40', '1')
update DuongPho
set TenDuong = 'Giai Phong'
where TenDuong = 'Giai Toa'
create view vw_all_Nha_Tren_Pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo, NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from QuanHuyen, NhaTrenPho, DuongPho
where DuongPho.DuongID = NhaTrenPho.DuongID and QuanHuyen.MaQH = DuongPho.MaQH
create view vw_AVG_Nha_Tren_Pho
as
select DuongPho.TenDuong, AVG(NhaTrenPho.DienTich) as DienTichTrungBinh, AVG(NhaTrenPho.SoNhanKhau) as SoNhanKhauTrungBinh
from DuongPho, NhaTrenPho
where DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
select * from vw_AVG_Nha_Tren_Pho order by DienTichTrungBinh asc, SoNhanKhauTrungBinh asc
select * from vw_all_Nha_Tren_Pho![Lê Trọng Nghĩa [community,C2009I]](https://www.gravatar.com/avatar/1093e836f419101dc96b7e3f6aab4a56.jpg?s=80&d=mm&r=g){kind=avatar}
Lê Trọng Nghĩa
2021-01-30 09:00:06
create database QuanLyNhanKhau
use QuanLyNhanKhau
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
)
create table NhaTrenPho
(
NhaID int not null,
DuongID int not null,
ChuHo nvarchar(50),
DienTich money
)
alter table NhaTrenPho
add SoNhanKhau int
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)
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)
alter table DuongPho
add constraint CK_DuongPho_NgayDuyetTen check (NgayDuyetTen between '1945-09-02' and getdate())
insert into QuanHuyen(TenQH)
values
('Ba Dinh'),
('Haong 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', '1', 'Ha Khanh Toan', '100', '4'),
('2', '1', 'Le Hong Hai', '20', '12'),
('3', '2', 'Tran Khanh', '40', '1')
update DuongPho
set TenDuong = 'Giai Phong'
where TenDuong = 'Giai Toa'
create view vw_all_Nha_Tren_Pho
as
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen, NhaTrenPho.ChuHo, NhaTrenPho.DienTich, NhaTrenPho.SoNhanKhau
from QuanHuyen, NhaTrenPho, DuongPho
where DuongPho.DuongID = NhaTrenPho.DuongID and QuanHuyen.MaQH = DuongPho.MaQH
create view vw_AVG_Nha_Tren_Pho
as
select DuongPho.TenDuong, AVG(NhaTrenPho.DienTich) as DienTichTrungBinh, AVG(NhaTrenPho.SoNhanKhau) as SoNhanKhauTrungBinh
from DuongPho, NhaTrenPho
where DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
select * from vw_AVG_Nha_Tren_Pho order by DienTichTrungBinh asc, SoNhanKhauTrungBinh asc
select * from vw_all_Nha_Tren_Pho
