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

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;

use B8;

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 unique index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)

alter table NhaTrenPho

add SoNhanKhau int

alter table QuanHuyen

add primary key (MaQH)

alter table DuongPho

add primary key (DuongID)

alter table NhaTrenPho

add 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 '2023 - 12 - 27')

insert QuanHuyen(TenQH)

values

(N'Ba Dinh'),

(N'Hoang Mai')

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

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)

update DuongPho

set TenDuong * 'Giai Phong'

where TenDuong * 'Giai Toa'

create view vw_all_Nha_Tren_Pho

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

select * from vw_all_Nha_Tren_Pho

create view vw_AVG_Nha_Tren_Pho

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

select * from vw_AVG_Nha_Tren_Pho

create proc sp_NgayQuyetTen_DuongPho

@NgayDuyet datetime

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

begin

if(select SoNhanKhau from NhaTrenPho) = 0

begin

print 'update tren bang NhaTrenPho loi'

rollback transaction

end

create trigger InsteadOf_Delete_DuongPho on DuongPho

instead of delete

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

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

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

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

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

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

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

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

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

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

Dang Bach

Nguyễn Hùng

Võ Như Việt

Đỗ Minh Tâm

TRẦN VĂN ĐIỆP

TRẦN VĂN ĐIỆP

Vũ Trung Kiên

#1821.sql

Trinh Huy Hung

Lê Sĩ Tuyển

Lê Trọng Nghĩa