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)
GokiSoft.com
2023-12-27 13:24:09
-- Create database: QuanLyNhanKhau
create database QuanLyNhanKhau2
-- Active database: QuanLyNhanKhau
use QuanLyNhanKhau2
-- 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 (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 = N'Giải Phóng' where TenDuong = 'Giai Toa'
go
-- View
---- Data: 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 DuongPho.DuongID = NhaTrenPho.DuongID
go
select * from vw_all_nha_tren_pho
go
-- TenDuong (DuongPho), DienTichTrungBinh (AVG: NhaTrenPho), SoNhanKhauTrungBinh (AVG: NhaTrenPho)
create view vw_avg_nha_tren_pho
as
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) 'DienTichTrungBinh', avg(NhaTrenPho.SoNhanKhau) 'SoNhanKhauTrungBinh'
from DuongPho join NhaTrenPho on DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
go
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) 'DienTichTrungBinh', avg(NhaTrenPho.SoNhanKhau) 'SoNhanKhauTrungBinh'
from DuongPho left join NhaTrenPho on DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
go
select * from vw_avg_nha_tren_pho
order by DienTichTrungBinh, SoNhanKhauTrungBinh asc
go
-- Proc
create proc sp_ngayduyetten_duongpho
@NgayDuyet datetime
as
begin
--TenQH (QuanHuyen), TenDuong (DuongPho), NgayDuyetTen (DuongPho)
select QuanHuyen.TenQH, DuongPho.TenDuong, DuongPho.NgayDuyetTen
from QuanHuyen, DuongPho
where QuanHuyen.MaQH = DuongPho.MaQH
and DuongPho.NgayDuyetTen = @NgayDuyet
end
declare @NgayDuyet datetime
select @NgayDuyet = Convert(datetime, '30/12/1998', 103)
exec sp_ngayduyetten_duongpho @NgayDuyet
go
declare @NgayDuyet datetime
select @NgayDuyet = Convert(datetime, '30/12/1988', 103)
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 sonhankhau <= 0'
rollback transaction
end
end
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = -2 where NhaID = 1
go
update NhaTrenPho set SoNhanKhau = 0 where NhaID = 1
go
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
go
Nguyen Hai Anh
2023-12-27 12:55:26
create database QuanLyNhanKhau
go
use QuanLyNhanKhau
go
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)
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 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 into QuanHuyen (TenQH)
values
('Ba Dinh'),
('Hoang Mai');
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
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
update DuongPho
set TenDuong = 'Giai Phong'
where TenDuong = 'Giai Toa'
go
create view vw_all_Nha_Tren_Pho as
select TenQH, TenDuong, NgayDuyetTen, ChuHo, DienTich, SoNhanKhau
from NhaTrenPho, DuongPho, QuanHuyen
where (DuongPho.MaQH = QuanHuyen.MaQH and DuongPho.DuongID = NhaTrenPho.DuongID)
go
-- select * from vw_all_Nha_Tren_Pho
-- go
create view vw_AVG_Nha_Tren_Pho as
select DuongPho.TenDuong, avg(NhaTrenPho.DienTich) 'Dien tich trung binh', avg(NhaTrenPho.SoNhanKhau) 'So Nhan Khau TB'
from NhaTrenPho, DuongPho
where (NhaTrenPho.DuongID = DuongPho.DuongID) group by DuongPho.TenDuong
go
select * from vw_AVG_Nha_Tren_Pho
order by 'Dien tich trung binh', 'So Nhan Khau TB' asc
go
create proc sp_NgayQuyetTen_DuongPho @NgayDuyet datetime as
select DuongPho.NgayDuyetTen, DuongPho.TenDuong, QuanHuyen.TenQH
from DuongPho, QuanHuyen
where DuongPho.MaQH = QuanHuyen.MaQH and DuongPho.NgayDuyetTen = @NgayDuyet
go
declare @NgayDuyet datetime
select @NgayDuyet = convert (datetime, '30/12/1998', 103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
go
bui tien manh
2023-12-27 12:55:01
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
--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
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())
alter table DuongPho
drop constraint CK_DuongPho_NgayDuyetTen
--them du lieu vao bang--
--quanhuyen--
insert into QuanHuyen(TenQH)
values
('Ba dinh'),
('Hoang Mai')
select * from QuanHuyen
go
--duongpho--
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
--nhatrenpho--
select * from NhaTrenPho
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 option--
Update DuongPho
set TenDuong = 'Giai Phong'
where TenDuong ='Giai Toa'
go
select * from DuongPho
go
--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
go
--8.views--
create view vw_AVG_Nha_Tren_Pho as
select TenDuong,avg(NhaTrenPho.DienTich) 'avg dien tich',avg(NhaTrenPho.SoNhanKhau) 'avg sonhakhau'
from NhaTrenPho,DuongPho
where DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
select *from vw_AVG_Nha_Tren_Pho
order by 'avg dien tich','avg sonhakhau' asc
go
--procedurces--
--trigger--
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'loi vi so nhan khau nhap vao phai >0'
rollback transaction
end
end
update NhaTrenPho set SoNhanKhau = -5 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
Đầu Khánh Ly
2023-12-27 12:54:55
create database QuanLyNhanKhau
use QuanLyNhanKhau
create table QuanHuyen(
MaQH int primary key 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 CT_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
go
create index UL_QuanHuyen_TenQH on QuanHuyen(TenQH)
go
alter table NhaTrenPho
add SoNhanKhau int
go
--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(TenQH)
values
('Ba Dinh'),
('Hoang Mai')
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
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
select * from DuongPho
--update
update DuongPho set TenDuong='Giai Phong' where TenDuong='Giai Toa'
go
--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 QuanHuyen.MaQH=DuongPho.MaQH
go
select * from vw_all_Nha_Tren_Pho
create view vw_AVG_Nha_Tren_Pho
as
select TenDuong,avg (NhaTrenPho.DienTich) 'Dien Tich Trung Binh',
avg (NhaTrenPho.SoNhanKhau) 'So Nhan Khau Trung Binh'
from NhaTrenPho,DuongPho
where NhaTrenPho.DuongID=DuongPho.DuongID
group by TenDuong
go
select * from vw_AVG_Nha_Tren_Pho
order by 'Dien Tich Trung Binh' , 'So Nhan Khau Trung Binh' asc
--proc
create proc sp_NgayQuyetTen_DuongPho
@NgayDuyet datetime
as
begin
select NgayDuyetTen,DuongPho.TenDuong,QuanHuyen.TenQH
from QuanHuyen,DuongPho
where QuanHuyen.MaQH=DuongPho.MaQH
and DuongPho.NgayDuyetTen=@NgayDuyet
end
exec sp_NgayQuyetTen_DuongPho '1998-12-30'
--trigger
create trigger FOR_UPDATE_NHA_TREN_PHO on NhaTrenPho
for update
as
begin
if(select count(SoNhanKhau) from updated where SoNhanKhau <= 0 ) > 0
begin
print N'So nhan khau khum duoc > 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
Phạm Ngọc Ninh
2023-12-27 12:54:18
CREATE DATABASE QuanLyNhanKhau;
GO
USE QuanLyNhanKhau;
GO
CREATE TABLE QuanHuyen (
MaQH INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
TenQH NVARCHAR(100)
);
CREATE TABLE DuongPho (
DuongID INT PRIMARY KEY NOT NULL,
MaQH INT FOREIGN KEY REFERENCES QuanHuyen(MaQH) NOT NULL,
TenDuong NVARCHAR(MAX) NOT NULL,
NgayDuyetTen DATETIME NULL
);
CREATE TABLE NhaTrenPho (
NhaID INT PRIMARY KEY NOT NULL,
DuongID INT FOREIGN KEY REFERENCES DuongPho(DuongID) NOT NULL,
ChuHo NVARCHAR(50) NULL,
DienTich MONEY NULL
);
CREATE CLUSTERED INDEX CI_NhaTrenPho_NhaID
ON NhaTrenPho(NhaID);
CREATE UNIQUE NONCLUSTERED INDEX UI_QuanHuyen_TenQH
ON QuanHuyen(TenQH);
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 (MaQH, TenQH)
VALUES
(1, 'Ba Dinh'),
(2, 'Hoang Mai');
INSERT INTO DuongPho (DuongID, MaQH, TenDuong, NgayDuyetTen)
VALUES
(1, 1, 'Doi Can', '1946-10-19'),
(2, 1, 'Van Phitc', '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 Téa';
CREATE VIEW vw_all_Nha_Tren_Pho AS
SELECT
QH.TenQH,
DP.NgayDuyetTen,
NTP.ChuHo,
NTP.DienTich,
NTP.SoNhanKhau
FROM
NhaTrenPho NTP
JOIN DuongPho DP ON NTP.DuongID = DP.DuongID
JOIN QuanHuyen QH ON DP.MaQH = QH.MaQH;
SELECT * FROM vw_all_Nha_Tren_Pho;
CREATE VIEW vw_AVG_Nha_Tren_Pho AS
SELECT
DP.TenDuong,
AVG(NTP.DienTich) AS AVG_DienTich,
AVG(NTP.SoNhanKhau) AS AVG_SoNhanKhau
FROM
NhaTrenPho NTP
JOIN DuongPho DP ON NTP.DuongID = DP.DuongID
GROUP BY
DP.TenDuong;
SELECT *
FROM vw_AVG_Nha_Tren_Pho
ORDER BY AVG_DienTich, AVG_SoNhanKhau;
CREATE PROCEDURE sp_NgayQuyetTen_DuongPho
@NgayDuyet DATETIME
AS
BEGIN
SELECT
DP.NgayDuyetTen,
DP.TenDuong,
QH.TenQH
FROM
DuongPho DP
JOIN
QuanHuyen QH ON DP.MaQH = QH.MaQH
WHERE
DP.NgayDuyetTen = @NgayDuyet;
END;
CREATE TRIGGER FOR_UPDATE_NHA_TREN_PHO
ON NhaTrenPho
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(SoNhanKhau)
BEGIN
UPDATE NTP
SET NTP.SomeOtherColumn = SomeValue
FROM NhaTrenPho NTP
JOIN inserted I ON NTP.NhaID = I.NhaID
WHERE I.SoNhanKhau = 0;
UPDATE NTP
SET NTP.SomeOtherColumn = SomeOtherValue
FROM NhaTrenPho NTP
JOIN inserted I ON NTP.NhaID = I.NhaID
WHERE I.SoNhanKhau > 0;
END;
END;
CREATE TRIGGER InsteadOf_Delete_DuongPho
ON DuongPho
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO DeletedDuongPho (DuongID, MaQH, TenDuong, NgayDuyetTen)
SELECT DuongID, MaQH, TenDuong, NgayDuyetTen
FROM deleted;
DELETE DP
FROM DuongPho DP
JOIN deleted D ON DP.DuongID = D.DuongID;
END;
Nguyễn Thị Xuân Thu
2023-12-27 12:53:54
create database QuanLyNhapKhau
go
use QuanLyNhapKhau
go
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)
go
create unique index UI_QuanHyen_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 PK_NhaTrenPho_DuongPho foreign key (DuongID) references DuongPho(DuongID)
go
alter table DuongPho
add constraint PK_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 into QuanHuyen(TenQH)
values
(N'Ba Đình'),
(N'Hoàng Mai')
go
insert into DuongPho(DuongID, MaQH, TenDuong, NgayDuyetTen)
values
(1, 1, N'Đội Cấn', '1946-10-19'),
(2, 1, N'Vạn Phúc', '1998-12-30'),
(3, 2, N'Giải Tòa', '1975-09-21')
go
insert into NhaTrenPho(NhaID, DuongID, ChuHo, DienTich, SoNhanKhau)
values
(1, 1, N'Hà Khánh Toàn', 100, 4),
(2, 1, N'Lê Hồng Hải', 20, 12),
(3, 2, N'Trần Khánh', 40, 1)
go
update DuongPho set TenDuong = N'Giải Phóng' where TenDuong = N'Giải Tòa'
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 DuongPho.DuongID = NhaTrenPho.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
order by 'Dien Tich Trung Binh','So Nhan Khau Trung Binh' asc
go
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'
go
declare @NgayDuyet datetime
set @NgayDuyet = convert(datetime, '30/12/1998', 103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
go
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'Không được phép cập nhật'
rollback transaction
end
end
select * from NhaTrenPho
update NhaTrenPho set SoNhanKhau = 2 where NhaID = 1
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
go
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
delete from DuongPho where DuongID = 2
BOMIMI123
2023-12-27 12:53:36
create database QuanLyNhanKhau
go
use QuanLyNhanKhau
go
-- tao bang
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
--index
create clustered index CI_NhaTrenPho_NhaID on NhaTrenPho(NhaID)
go
create unique nonclustered index UI_QuanHuyen_TenQH on QuanHuyen(TenQH)
go
--alter table
alter table NhaTrenPho
add SoNhauKhau int
go
--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
--add 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
--insert data
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 querry
update DuongPho set TenDuong = 'Giai phong' where TenDuong = 'Giai toa'
go
-- 7. new view named
create view vw_all_Nha_Tren_pho
as
select TenQH, TenDuong, NgayDuyetTen, ChuHo, DienTich, SoNhauKhau
from NhaTrenPho, DuongPho, QuanHuyen
where DuongPho.DuongID = NhaTrenPho.DuongID
and DuongPho.MaQH= QuanHuyen.MaQH
go
-- new view named
create view vw_AVG_Nha_Tren_Pho
as
select TenDuong, AVG(NhaTrenPho.DienTich) 'Dien tich trung binh', AVG(NhaTrenPho.SoNhauKhau) 'So nhan khau trung binh'
from NhaTrenPho, DuongPho
where DuongPho.DuongID = NhaTrenPho.DuongID
group by DuongPho.TenDuong
go
-- create 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
go
--declare
declare @NgayDuyet datetime
select @NgayDuyet = CONVERT (datetime,'30/12/1998',103)
exec sp_NgayQuyetTen_DuongPho @NgayDuyet
go
-- create trigger updates
create trigger [FOR_UPDATE_NHA_TREN_PHO] on NhaTrenPho
for update
as
begin
if(select count(SoNhauKhau) from inserted where SoNhauKhau <= 0 ) > 0
begin
print N'So Nhan Khau Yeu Cau Lon Hon Khong 0'
rollback transaction
end
end
go
update NhaTrenPho set SoNhauKhau = 0 where NhaID = 1
go
-- create trigger delelte
create trigger [ISTEADOF_DELETE_DUONG_PHO] 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
go
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
select * from vw_all_Nha_Tren_pho
go
--NGUYEN HONG SON-C2311L
Đỗ Quốc Dũng
2023-12-27 12:49:38
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)
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,'Tranh Khanh',40,1)
select * from QuanHuyen
select * from DuongPho
select * from NhaTrenPho
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
select * from vw_all_Nha_Tren_Pho
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 DuongPho, NhaTrenPho
where DuongPho.DuongID = NhaTrenPho.NhaID
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
begin
if (select count(*) from inserted where SoNhanKhau <= 0) > 0
print N'So nhan khau phai lon hon 0'
rollback transaction
end
end
drop trigger FOR_UPDATE_NHA_TREN_PHO
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 deteled)
end
select*from NhaTrenPho
select*from DuongPho
delete from DuongPho where DuongID = 2
Nguyen Thanh Do
2023-12-27 12:40:51
create database QuanLyNhanKhau;
go
use QuanLyNhanKhau;
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 CL_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'HoangMai')
go
insert into 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 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
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
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
Đặng Thị Như Nguyệt
2023-12-27 12:40:45
create database QuanLyNhanKhau;
go
use QuanLyNhanKhau;
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 CL_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'HoangMai')
go
insert into 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 into NhaTrenPho(NhaID,DuongID,ChuHo,DienTich,SoNhanKhau)
values
(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
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
