CREATE DATABASE Quanlynhanvien_Hotensinhvien;

USE Quanlynhanvien_Hotensinhvien;

CREATE TABLE Phong (

MAPH VARCHAR(20) PRIMARY KEY,
TENPH VARCHAR(100),
DIACHIPHONG VARCHAR(255)
)
CREATE TABLE Chucvu (
MACV VARCHAR(20) PRIMARY KEY,
TENCV VARCHAR(100),
HESOPC FLOAT
)

CREATE TABLE Nhanvien (

MANV VARCHAR(20) PRIMARY KEY,
TENNV VARCHAR(100),
DIACHI VARCHAR(255),
HSLuong FLOAT,
MACV VARCHAR(20)FOREIGN KEY REFERENCES Chucvu(MACV),
MAPH VARCHAR(20) FOREIGN KEY (MAPH) REFERENCES
Phong(MAPH),
LUONG FLOAT,
THUCLINH FLOAT
)

INSERT INTO Chucvu (MACV, TENCV, HESOPC) VALUES

('CV001', 'Giam doc', 3.0),
('CV002', 'Truong phong', 2.5),
('CV003', 'Nhan vien', 1.0);

INSERT INTO Phong (MAPH, TENPH, DIACHIPHONGB) VALUES

('PH001', 'Phòng Kinh doanh', ' [Link]'),
('PH002', 'Phòng Nhân so', 'Ha Noi'),
('PH003', 'Phòng IT', 'Ha Nam');

INSERT INTO Nhanvien (MANV, TENNV, DIACHI, HSLuong, MACV, MAPH,

LUONG, THUCLINH) VALUES
('NV001', 'Nguy?n V?n A', 'HPhong', 5.0, 'CV001', 'PH001', 20000000, 21000000),
('NV002', 'Tr?n Th? B', 'HCM', 4.5, 'CV002', 'PH002', 15000000, 16000000),
('NV003', 'Lê Minh C', 'Thanh Hoa', 3.0, 'CV003', 'PH003', 10000000, 11000000);

--Hien thi dia chi co phong ten la Tai chinh

SELECT DIACHIPHONG
FROM Phong
WHERE TENPH = 'Tài chính';

--Hien thi nhung nhan vien co dia chi Ha Noi

SELECT MANV, TENNV, DIACHI
FROM Nhanvien
WHERE DIACHI = 'Hà N?i';

-- Hien thi nhung nhan vien co ma P01

SELECT [Link], [Link], [Link]
FROM Nhanvien
JOIN Phong ON [Link] = [Link]
WHERE [Link] = 'P01';

--Hien thi nhung nhan vien khong co ma P01

SELECT MANV, TENNV, MAPH
FROM Nhanvien
WHERE MAPH <> 'P01';

--Hien thi cac phong co dia chi Nha B

SELECT MAPH, TENPH
FROM Phong
WHERE DIACHIPHONG = 'Nhà B';

--Hien thi chuc vu Truong Phong --

SELECT [Link], [Link], [Link]
FROM Nhanvien
JOIN Chucvu ON [Link] = [Link]
WHERE [Link] = 'Truong phong';

--Hien thi nvien co hsl cao nhat --

SELECT MANV, TENNV, HSLuong
FROM Nhanvien
WHERE HSLuong = (SELECT MAX(HSLuong) FROM Nhanvien);

--Thong tin nhan vien co nha A

SELECT MAPH, TENPH, DIACHIPHONG
FROM Phong
WHERE DIACHIPHONG= 'nhà A';

--Hien thi phong co tu 3 nhan vien

SELECT [Link], [Link], COUNT([Link]) AS
TongSoNhanVien
FROM Phong
JOIN Nhanvien ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) >= 3;

--Hien thi phong co dia chi khac HN va hsl 2.34

SELECT MANV, TENNV, DIACHI, HSLuong
FROM Nhanvien
WHERE DIACHI != 'Hà Nội' AND HSLuong = 2.34;

--Hs phu cap cao nhat

SELECT MANV, TENNV, HSLuong
FROM Nhanvien
WHERE HSLuong = (SELECT MAX(HSLuong) FROM Nhanvien);

--Thuc linh cao nhat

SELECT MANV, TENNV, THUCLINH
FROM Nhanvien
WHERE THUCLINH = (SELECT MAX(THUCLINH) FROM Nhanvien);

--Nhieu nhan vien nhat

SELECT [Link], [Link], COUNT([Link]) AS
TongSoNhanVien
FROM Phong
JOIN Nhanvien ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) = MAX(COUNT([Link]));

--Tao bo sung rang buoc Defaul cho cot Thuclinh = 0

CREATE DEFAULT thuclinh_default
AS 0
EXEC sp_bindefault 'thuclinh_default', '[Link]';

--Tao bo sung rang buoc Defaul cho cot Dia chi "Chua xd"
CREATE DEFAULT diachi_default AS 'Ch?a xác ??nh';
EXEC sp_bindefault 'diachi_default', '[Link]';

--Tao rule cho cot hsopc tu 0 den 4

CREATE RULE hesoPC_rule AS
@Hspc BETWEEN 0 AND 0.4
EXEC sp_bindrule 'hesoPC_rule', '[Link]'

--Tao thu tuc hien thi Manv, Tennv khi biet Macv
CREATE PROCEDURE pr1 @MACV VARCHAR(20)
AS
SELECT [Link], [Link], [Link]
 FROM Nhanvien
JOIN Chucvu ON [Link] = [Link]
WHERE [Link] = @MACV
EXEC pr1 @MACV = 'CV001';

--Tao thu tuc khi biet ma nhan vien

CREATE PROCEDURE HienThiThongTinNhanVien
@maNhanVien VARCHAR(20)
AS
BEGIN
SELECT MANV, TENNV, DIACHI
FROM Nhanvien
WHERE MANV = @maNhanVien;
END;
EXEC HienThiThongTinNhanVien @maNhanVien = 'NV001';

--Tao thu tuc khi biet dc

CREATE PROCEDURE HienThiThongTinNhanVien
@maNhanVien VARCHAR(20)
AS
BEGIN
SELECT MANV, TENNV, DIACHI
FROM Nhanvien
WHERE MANV = @maNhanVien;
END;
EXEC HienThiThongTinNhanVien @maNhanVien = 'NV001';

--Tao thu tuc khi bt hsluong

CREATE PROCEDURE HienThiThongTinNhanVienTheoHeSoLuong
@heSoLuong FLOAT
AS
BEGIN
SELECT MANV, TENNV, HSLuong
FROM Nhanvien
WHERE HSLuong = @heSoLuong;
END;
EXEC HienThiThongTinNhanVienTheoHeSoLuong @heSoLuong = 2.34;

--Tao thu tuc khi bt ma phong

CREATE PROCEDURE pr4 @MAPH VARCHAR(20)
AS
SELECT [Link], [Link], [Link]
FROM Nhanvien, Phong
WHERE
[Link] = [Link]
 AND [Link] = @MAPH;
EXEC pr4 @MAPH = 'PH001';

--Tao thu tuc hien thi bt ma phong, tinh tong so nvien

CREATE PROCEDURE pr2 @MAPH VARCHAR(20)
AS
SELECT [Link], COUNT([Link]) AS TongSoNhanVien
FROM Phong, Nhanvien
WHERE [Link] = [Link]
AND [Link] = @MAPH
GROUP BY [Link];
EXEC pr2 @MAPH = 'PH001';

--Tao thu tuc khi bt hsoluonng

CREATE PROCEDURE HienThiNhanVienTheoHeSoLuong
@heSoLuong FLOAT
AS
BEGIN
SELECT MANV, TENNV, HSLuong
FROM Nhanvien
WHERE HSLuong = @heSoLuong;
END;
EXEC HienThiNhanVienTheoHeSoLuong @heSoLuong = '3.5';

--Tao thu tuc khi bt ma chuc vu

CREATE PROCEDURE HienThiNhanVienTheoMaChucVu
@maChucVu VARCHAR(20)
AS
BEGIN
SELECT [Link], [Link], [Link]
FROM Nhanvien
JOIN Chucvu ON [Link] = [Link]
WHERE [Link] = @maChucVu;
END;
EXEC HienThiNhanVienTheoMaChucVu @maChucVu = 'CV001';

--Tao thu tuc khi bt ma chuc vu, tinh tong so nvien

CREATE PROCEDURE HienThiChucVuVaSoNhanVien
@maChucVu VARCHAR(20)
AS
BEGIN
SELECT [Link], [Link], COUNT([Link]) AS
TongSoNhanVien
FROM Chucvu
LEFT JOIN Nhanvien ON [Link] = [Link]
 WHERE [Link] = @maChucVu
GROUP BY [Link], [Link];
END;
EXEC HienThiChucVuVaSoNhanVien @maChucVu = 'CV001';

--Tao thu tuc khi biet ten phong, tinh tong so nhan vien
CREATE PROCEDURE HienThiSoNhanVienTheoTenPhong
@tenPhong VARCHAR(100)
AS
BEGIN
SELECT [Link], [Link], COUNT([Link]) AS
TongSoNhanVien
FROM Phong
LEFT JOIN Nhanvien ON [Link] = [Link]
WHERE [Link] = @tenPhong
GROUP BY [Link], [Link];
END;
EXEC HienThiSoNhanVienTheoTenPhong @tenPhong = 'Phòng Kỹ Thuật';

--Tao thu tuc tinh thuc linh

CREATE PROCEDURE TinhThucLinh
@maNhanVien VARCHAR(20)
AS
BEGIN
SELECT [Link], [Link], [Link],
[Link],
([Link] + [Link] * [Link]) AS
THUCLINH
FROM Nhanvien
JOIN Chucvu ON [Link] = [Link]
WHERE [Link] = @maNhanVien;
END;
EXEC TinhThucLinh @maNhanVien = 'NV001';

-- Tao view luu thong tin nvien cua phong 'Tai chinh'
CREATE VIEW c4 AS
SELECT [Link], [Link], [Link]
FROM Nhanvien
JOIN Phong ON [Link] = [Link]
WHERE [Link] = 'Tài chính';
SELECT * FROM c4
-- Tao view l?u thong tin nhung nhan vien co chuc vu pho phong
create view c55
as
select manv, tennv, maph, tencv
from nhanvien, Chucvu
where [Link] = [Link]
and tencv = 'Pho phong'
SELECT * FROM c55
-- Tao view luu thong tin cua phong 'Xay dung'
CREATE VIEW c5 AS
SELECT [Link], [Link], [Link]
FROM Nhanvien
JOIN Phong ON [Link] = [Link]
WHERE
[Link] = 'Xây d?ng'
SELECT * FROM c5

--Tao view luu thong tin phong co nhieu nvien nhat

create view c6 as
select [Link], tenph, count ([Link]) as Tongsonhanvien
from phong, nhanvien
where [Link] = [Link]
group by [Link], tenph
having count ([Link])>= all (select count ([Link])
from nhanvien
group by maph)
SELECT * FROM c5

-- Tao view luu thong tin nvien co hsl cao nhat

create view c44
as
select manv, tennv, hsluong
from nhanvien
where hsluong =(select max (hsluong) from nhanvien)
SELECT * FROM c44

-- Tao trigger tinh tien luong cua nvien khi luong thay doi luong = hsluong
*1450000
create trigger c5 nhanvien
for update
as
update nhanvien
set luong = hsluong *145000

-- Tao trigger tinh tien thuc linh cho nhan vien khi hspc thay doi
-- thuclinh = luong + Luong * hesopc
create trigger c555 on chucvu
for update
as
 update nhanvien
set thuclinh = luong + luong * (set hesopc
from chucvu
where chucvu, macv=
[Link])
-- Tao trigger kiem tra hsl cua nhan vien chi nhan gia tri tu 2.34 den 9.4
-- khi them du lieu vao bang nhan vien, ngoai gtri bao loi
create trigger c54 on nhanvien
for insert
as
if ((select hsluong
from inserted) <2.34 or ((select hsluong
from inserted)> 9.0)
begin
print 'Nhap hsl tu 2.34 den 9.1'
rollback tran
end

CREATE DATABASE Quanlybanhang_Hotensv;

USE Quanlybanhang_Hotensv;

CREATE TABLE HANGHOA (

Mahang varchar(20) PRIMARY KEY,
Tenhang VARCHAR(20),
Donvitinh VARCHAR(20),
Soluong INT
)

CREATE TABLE HOADON (

Mahd varchar(20) PRIMARY KEY,
NgayHD DATE,
 Tienban int ,
giamgia float,
thanhtoan int
)

CREATE TABLE CTHOADON (

Mahd varchar(20) FOREIGN KEY REFERENCES HOADON(Mahd),
Mahang varchar(20)FOREIGN KEY REFERENCES HANGHOA(Mahang)
PRIMARY KEY (Mahd, Mahang),
Soluong INT,
Dongia int
)

INSERT INTO HANGHOA

VALUES
('H01', 'bimbim', 'goi', 50),
('H02', 'mitom', 'goi', 30),
('H03', 'sua', 'tui', 70

INSERT INTO HOADON

VALUES
('HD01', '2024-11-01', 'Nguyen Van A', 500000),
('HD02', '2024-11-02', 'Tran Thi B', 300000),
('HD03', '2024-11-03', 'Le Van C', 700000)

INSERT INTO CTHOADON

VALUES
('HD01', 'MH01', 2, 250000),
('HD01', 'MH02', 1, 300000),
('HD02', 'MH03', 3, 100000),
('HD03', 'MH01', 1, 250000)

--Hien thi thong tin cac mat hang co so luong > 20 don vi tinh--
SELECT *
FROM HANGHOA
WHERE Soluong > 20

--Hien thi ma hoa don, so tien thanh toan cua hoa don HD01 --
SELECT mahd, thanhtoan
FROM HOADON
WHERE Mahd = 'HD01'

--Hien thi ma hang, so luong hang duoc ban boi HD01--

SELECT Mahang, Soluong
FROM CTHOADON
WHERE Mahd = 'HD01'

--Hien thi thong tin cac hoa don co tien thanh toan lon nhat--
SELECT *
FROM HOADON
WHERE thanhtoan = (SELECT MAX(Thanhtoan) FROM HOADON)

--Hien thi thong tin cac mat hang co don vi tinh la bo--
SELECT *
FROM HANGHOA
WHERE Donvitinh = 'Bo'

--Hien thi ma hoa don duoc lap trong nam 2020--

SELECT Mahd
FROM HOADON
WHERE YEAR(NgayHD) = 2020;

--Hien thi tien ban cua hoa don co ma la 'HD01'--

SELECT Tienban
FROM HOADON
WHERE Mahd = 'HD01'

--Hien thi thong tin mat hang duoc ban voi so luong it nhat,thong tin hien thi co:
ma hang, ten hang, tong so luong hang duoc ban--
select [Link], tenhang, sum ([Link]) as Tongsohangduocban
from hanghoa, CTHOADON
where [Link] = [Link]
group by [Link], Tenhang
having sum ([Link])<= all (select sum (Soluong)
from CTHOADON
group by Mahang)
--Hien thi thong tin cac hoa don co tien thanh toan lon nhat
select *
from hoadon
where thanhtoan = (select max (thanhtoan)
from hoadon)
--Tao bo sung rang buoc default cho cot Ngayhd la ngay hien tai --
create default ngayhd
as getdate ()
exec sp_bindefault 'ngayhd','[Link]'

--Tao bo sung rang buoc default cho cot tienban la 0--

CREATE DEFAULT tienban
AS 0
EXEC sp_bindefault 'tienban_default', '[Link]'
--Tao bsung Rule cho cột Soluong của bảng Hanghoa nhận giá trị lớn hơn 0.
CREATE RULE soluong
AS
@Sl > 0
sp_bindrule 'soluong', '[Link]'

-- Tạo bsung ràng buộc Rule cho cột Donvitinh chỉ nhận các giá trị: Chiếc, Bộ
create rule dv
as
@dvt in ('Chiec', 'Bo')

sp_bindrule 'dv', '[Link]'

--Tao thu tuc hien thi thong tin tong so mat hang ban tren moi hoa don
--khi biet ma hoa don, thong tin hien thi co: mhd, nhd, tong so mat hang ban
create proc c1 @mahoadon varchar (20)
as
select [Link], ngayhd, count (*) as Tongsomathang
from hoadon, cthoadon
where [Link] = @mahoadon
group by [Link], ngayhd
exec c1'hd04'

--Tao thu tuc hien thi tong so hang duoc ban tren moi hoa don
-- thong tin hien thi co: mhd, ngayhd, tong so luong hang
create proc c2
as
select [Link], ngayhd, sum(soluong) as Tongsoluong
from hoadon, cthoadon
where [Link]= [Link]
group by [Link], ngayhd
exec c2

--Tao thu tuc hien thi tien ban cua hoa don khi bt ma hoa don
--thong tin hien thi: mhd, ngayhd, tien ban
create proc c3 @mahoadon varchar (20)
as
select mahd, ngayhd, tienban
from hoadon
where madhd = @mahoadon
exec c3 'hd01'
--Tao thu tuc hien thi thong tin cac hoa don khi biet ngay
-- thong tin hien thi: mhd, ngayhd, tienban
create proc c4 @ngayhoadon date
as
select mahd, ngayhd, tienban
from hoadon
where ngayhd = @ngayhoadon
exec c4 '2/2/2024'

--tao thu tuc cap nhat lai tien ban

CREATE PROCEDURE C6
AS
BEGIN
UPDATE HOADON
SET Tienban = (
SELECT SUM(Soluong * Dongia)
FROM CTHOADON
WHERE [Link] = [Link]
GROUP BY Mahd
)
WHERE Mahd IN (SELECT DISTINCT Mahd FROM CTHOADON)
-- Tao trigger soluongton trong hang hoa phai lon hon 0
--moi khi them mot hang moi vao co so, khong dung phai thong bao
create trigger trig on hanghoa
for insert
as
if (select soluongton
from inserted)<=0
begin
print 'Nhap so luong ton phai >0'
rollback tran
end
--tao trigger ktra sluong trong bang cthoadon lon hon 0 moi khi them mot hang
moi
create trigger trig2 on cthoadon
for insert
as
if (select soluong
from inserted) <=0
begin
print 'Nhap so luong ton phai >0'
rollback tran
end

--tao trigger cap nhat lai tien ban cua moi hoadon khi xoa thong tin
-- mat hang duoc ban trg bang cthoadon
--tienban= sum (soluong * dongia)
create trigger c3 on cthoadon
for delete
as
update hoadon
set tienban = (select sum (soluong * dongia)
from cthoadon
where [Link] = [Link]
group by [Link])
--tao trigger cap nhat lai tien thanh toan cua moi hoa don khi sua thong tin giam
gia
-- thanhtoan = tienban - (tienban*giamgia)
create trigger trig3 on hoadon
for update
as
update hoadon
set thanhtoan = tienban - (tienban * giamgia)
--tao trigger cap nhat so luong ton trong hang hoa moi khi sua mat hang duoc ban
trong cthoadon
--biet soluongton = soluongton - soluong
create trigger trig4 on cthoadon
for update
as
update hanghoa
set [Link] = [Link] - (select inserted soluong
from inserted ) + (select [Link]

from deleted)
where mahang = (select mahang from inserted)

--tao trigger cap nhat so luong ton trong hang hoa moi khi them mat hang duoc ban
trong cthoadon
--biet soluongton = soluongton - soluong
create trigger trig4 on cthoadon
for update
as
update hanghoa
set [Link] = [Link] - (select inserted soluong from
inserted)

where mahang = (select mahang from inserted)

--Tao view hien thi thong tin mat hang duoc khach mua voi sluong nhieu nhat
create view c5
as
select [Link], tenhang, sum ([Link])as
Tongsoluongban
from hanghoa, cthoadon
 where [Link] = [Link]
group by [Link], tenhang
having sum ([Link])>= all (select sum ([Link])
from
cthoadon
group by
mahang)
select * from c5

--backup cơ sở dữ liệu sang ổ đĩa khác--

BACKUP DATABASE [database_name] TO DISK = 'E:\database_backup.bak'

-- Tạo cơ sở dữ liệu QUANLYDULICH

CREATE DATABASE QUANLYDULICH;
USE QUANLYDULICH;

-- Tạo bảng DIADIEM

CREATE TABLE DIADIEM (
MADD varCHAR(20) PRIMARY KEY,
TENDD VARCHAR(20),
TP VARCHAR(20)
)

-- Tạo bảng CHUYENDI

CREATE TABLE CHUYENDI (
MACD varCHAR(20) PRIMARY KEY,
TENCD VARCHAR(20) ,
NGKH DATE ,
NGKT DATE ,
KHDK INT
);
-- Tạo bảng CTIETCD
CREATE TABLE CTIETCD (
MACD CHAR(20)FOREIGN KEy REFERENCES CHUYENDI(MACD),
MADD CHAR(10)FOREIGN KEY REFERENCES DIADIEM(MADD),
SNLUU INT ,
PRIMARY KEY (MACD, MADD)
)

-- Nhập dữ liệu mẫu cho bảng DIADIEM

INSERT INTO DIADIEM (MADD, TENDD, TP) VALUES
('DD001', 'Vịnh Hạ Long', 'Quảng Ninh'),
('DD002', 'Phố cổ Hội An', 'Quảng Nam'),
('DD003', 'Thác Bản Giốc', 'Cao Bằng');

-- Nhập dữ liệu mẫu cho bảng CHUYENDI

INSERT INTO CHUYENDI (MACD, TENCD, NGKH, NGKT, KHDK) VALUES
('CD001', 'Du lịch Hạ Long', '2024-11-10', '2024-11-15', 20),
('CD002', 'Tham quan Hội An', '2024-12-01', '2024-12-05', 15),
('CD003', 'Khám phá Thác Bản Giốc', '2025-01-05', '2025-01-10', 10);

-- Nhập dữ liệu mẫu cho bảng CTIETCD

INSERT INTO CTIETCD (MACD, MADD, SNLUU) VALUES
('CD001', 'DD001', 3),
('CD002', 'DD002', 2),
('CD003', 'DD003', 4);
--Hien thi cac dia diem du lich cua chuyen di co ma CD02
SELECT [Link], [Link]
FROM DIADIEM
JOIN CTIETCD ON [Link] = [Link]
JOIN CHUYENDI ON [Link] = [Link]
WHERE [Link] = 'CD002';

--Hien thi ma chuyen di, ma dia diem voi so ngay luu lon nhat
SELECT MACD, MADD, SNLUU
FROM CTIETCD
WHERE SNLUU = (SELECT MAX(SNLUU) FROM CTIETCD);

--Hien thi thong tin chuyen di co ngay khoi hang la 2020/09/02

SELECT *
FROM CHUYENDI
WHERE NGKH = '2020-09-02';

--Hien thi thong tin cac chuyen di co so khach du kien it nhat

SELECT MACD, TENCD
FROM CHUYENDI
WHERE KHDK = (SELECT MIN(KHDK) FROM CHUYENDI);

--Hien thi thong tin co ten dia diem la Hanoi

SELECT MADD, TENDD
FROM DIADIEM
WHERE TP = 'Hà Nội';

--Hien thi so ngay luu lon nhat, nho nhat chuyen di CD2
SELECT MAX(SNLUU) AS Max_SNLUU, MIN(SNLUU) AS Min_SNLUU
FROM CTIETCD
WHERE MACD = 'CD002';

--Tao bo sung rang buoc Defaul cho cot SNLUU bang 0

create defaul snluu
as 0
exec sp_bindsp_bindefault 'snluu', '[Link]'

--Tao bo sung rang buoc Defaul cho cot NGKH bang ngay hien tai
create defaul ngkh
as
getdate ()
exec sp_bindefault 'ngkh', '[Link]'
--Tao bo sung rang Rule cho cot KHDK lon hon 0
create rule khdk
as
@khdk >0
EXEC sp_bindrule 'KHDK', '[Link]'

--Tao bo sung rang buoc Rule cho cot NGKH, NGKT >= ngày hien tai
CREATE RULE c3
AS
@value >= GETDATE()
EXEC sp_bindrule 'c3', '[Link]'
EXEC sp_bindrule 'c3', '[Link]'

--Tao view lưu thong tin chuyen di co so ngay luu lai lon nhat
CREATE VIEW c5 AS
SELECT [Link], [Link], SUM([Link]) AS TongSoNgayLuu
FROM CHUYENDI CD
JOIN CTIETCD CT ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link]) >= ALL (SELECT SUM(SNLUU) FROM
CTIETCD
GROUP BY MACD)
SELECT * FROM c5

--Tao view luu thong tin cac chuyen di co so khach du kien nhieu nhat
CREATE VIEW c7 AS
SELECT MACD, TENCD, KHDK
FROM CHUYENDI
WHERE KHDK = (SELECT MAX(KHDK) FROM CHUYENDI);
SELECT * FROM c7

--Tao view hien thi thong tin chuyen di va so luong dia diem cua cac chuyen di
CREATE VIEW c44 AS
SELECT [Link], COUNT([Link]) AS SoLuongDiaDiem
FROM CHUYENDI CD
JOIN CTIETCD CT ON [Link] = [Link]
GROUP BY [Link]
SELECT * FROM c44

--Tao view hien thi thong tin chuyen di co ngay khoi hang 2020
CREATE VIEW c55 AS
SELECT MACD, TENCD
FROM CHUYENDI
WHERE YEAR(NGKH) = 2020
SELECT * FROM c55

--Tao thu tuc hien thi khi biet thong tin chuyen di: mcd,tcd, tong so dia diem
CREATE PROC pro1 @MaChuyenDi varCHAR(20)
AS
SELECT [Link], [Link], COUNT([Link]) AS TongSoDiaDiem
From CHUYENDI CD
JOIN CTIETCD CT ON [Link] = [Link]
WHERE [Link] = @MaChuyenDi
GROUP BY [Link], [Link]

--Tao thu tuc hien thi thong tin co tong so ngay luu lon hon 10
CREATE PROCEDURE c4
AS
SELECT [Link], [Link], SUM([Link]) AS TongSoNgayLuu
FROM CHUYENDI CD
JOIN CTIETCD CT ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link]) > 10

--Tao thu tuc khi biet ma dia diem--

CREATE PROCpro11 @MaDiaDiem varCHAR(20)
AS
 SELECT MADD, TENDD, TP
FROM DIADIEM
WHERE MADD = @MaDiaDiem

--Tao trigger thuc hien ktra ngay khoi hanh va ngay ket thuc khi them/sua
--lon hon hoac bang ngay hien tai
CREATE TRIGGER ktra ON CHUYENDI
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (
SELECT *
FROM CHUYENDI
WHERE NGKH < GETDATE()
)
BEGIN
PRINT 'Ngày khởi hành phải lớn hơn hoặc bằng ngày hiện tại';
ROLLBACK TRANSACTION;
RETURN;
END

IF EXISTS (
SELECT *
FROM CHUYENDI
WHERE NGKT < GETDATE()
)
BEGIN
PRINT 'Ngày kết thúc phải lớn hơn hoặc bằng ngày hiện tại';
ROLLBACK TRANSACTION;
RETURN;
END

IF EXISTS (
SELECT *
FROM CHUYENDI
WHERE NGKH > NGKT
)
BEGIN
PRINT 'Ngày khởi hành phải nhỏ hơn hoặc bằng ngày kết thúc';
ROLLBACK TRANSACTION;
RETURN;
END
END
--Viet trigger ktra SNLUU khi them/sua phai >=1
CREATE TRIGGER CheckSoNgayLuu
ON CTIETCD
FOR INSERT, UPDATE
AS
BEGIN
-- Kiểm tra điều kiện cho INSERT
IF EXISTS (
SELECT *
FROM CTIETCD
WHERE SNLUU < 1
)
BEGIN
PRINT 'Số ngày lưu lại phải lớn hơn hoặc bằng 1';
ROLLBACK TRANSACTION;
RETURN;
END

-- Kiểm tra điều kiện cho UPDATE

IF EXISTS (
SELECT *
FROM CTIETCD
WHERE SNLUU < 1
)
BEGIN
PRINT 'Số ngày lưu lại phải lớn hơn hoặc bằng 1';
ROLLBACK TRANSACTION;
RETURN;
END
END;

--Viet trigger thuc hien KHDK khi them/sua >=1

CREATE TRIGGER CheckSoKhachDangKy
ON CHUYENDI
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (
SELECT *
FROM CHUYENDI
WHERE KHDK < 1
)
BEGIN
PRINT 'Số khách đăng ký phải lớn hơn hoặc bằng 1';
ROLLBACK TRANSACTION;
 RETURN;
END

IF EXISTS (
SELECT *
FROM CHUYENDI
WHERE KHDK < 1
)
BEGIN
PRINT 'Số khách đăng ký phải lớn hơn hoặc bằng 1';
ROLLBACK TRANSACTION;
RETURN;
END
END;

--Tao trigger ktra ngay khoi hanh < ngay ket thuc khi them du lieu
CREATE TRIGGER CheckNgayKhoiHanh
ON CHUYENDI
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT *
FROM CHUYENDI
WHERE NGKH >= NGKT
)
BEGIN
PRINT 'Ngày khởi hành phải nhỏ hơn ngày kết thúc';
ROLLBACK TRANSACTION;
RETURN;
END
END;

BACKUP DATABASE [database_name] TO DISK = 'E:\database_backup.bak'

CREATE DATABASE QLThucTap_HoVaTenSV;
USE QLThucTap_HoVaTenSV;

CREATE TABLE SinhVien (

MaSV VARCHAR(10) PRIMARY KEY,
HoTenSV VARCHAR(100),
Lop VARCHAR(50)
);

CREATE TABLE GiangVien (

MaGV VARCHAR(10) PRIMARY KEY,
HoTenGV VARCHAR(100)
);

CREATE TABLE HuongDan (

MaGV VARCHAR(10)FOREIGN KEY REFERENCES GiangVien(MaGV),
MaSV VARCHAR(10) FOREIGN KEY REFERENCES SinhVien(MaSV),
TenDT VARCHAR(100),
NoiThucTap VARCHAR(100),
PRIMARY KEY (MaGV, MaSV)
);
INSERT INTO SinhVien (MaSV, HoTenSV, Lop) VALUES
('SV01', 'Vuong Tuan Anh', 'D14CNPM1'),
('SV02', 'Nguyen Van Sang', 'D14CNPM3'),
('SV03', 'Nguyen Thu Huyen', 'D14HTTMĐT1'),
('SV04', 'Phan Dinh Anh', 'D14TTNT&TGMT'),
('SV05', 'Tran Bao Ngoc', 'D14QT&ANM');

INSERT INTO GiangVien (MaGV, HoTenGV) VALUES

('GV01', 'Nguyen Quynh Anh'),
('GV02', 'Bui Khanh Linh'),
('GV03', 'Nguyen Khanh Tung'),
('GV04', 'Phuong Van Canh'),
('GV05', 'Nguyen Hong Khanh');

INSERT INTO HuongDan (MaGV, MaSV, TenDT, NoiThucTap) VALUES

('GV03', 'SV05', 'Xay dung he thong canh bao email spam', 'Bkav'),
('GV05', 'SV02', 'Xay dung he thong tu van khach hang', 'FSI'),
('GV04', 'SV01', 'Xay dung App food', 'FPT Software'),
('GV02', 'SV04', 'Khai pha quan diem nguoi dung san pham', 'SamSung'),
('GV01', 'SV03', 'Xay dung he thong AI ho tro ban hang online', 'Sapo');

-- Thong tin danh sach sv

SELECT [Link], [Link], [Link], [Link], [Link]
FROM HuongDan HD
JOIN SinhVien SV ON [Link] = [Link]
JOIN GiangVien GV ON [Link] = [Link];

--Thong tin sinh vien ma gv Canh huong dan

SELECT [Link]
FROM SinhVien SV
JOIN HuongDan HD ON [Link] = [Link]
JOIN GiangVien GV ON [Link] = [Link]
WHERE [Link] = 'Phương Văn Cảnh';

--Thong tin sv thuc tap tai CMNC

SELECT [Link], [Link], [Link]
FROM SinhVien SV
JOIN HuongDan HD ON [Link] = [Link]
JOIN GiangVien GV ON [Link] = [Link]
WHERE [Link] = 'CMC';

--Hien thi giang vien huong dan sv tai Sam Sung

SELECT [Link]
FROM GiangVien GV
JOIN HuongDan HD ON [Link] = [Link]
WHERE [Link] = 'Samsung'
GROUP BY [Link];

--Hien thi sinh vien va giang vien co de tai

SELECT [Link], [Link]
FROM SinhVien SV
JOIN HuongDan HD ON [Link] = [Link]
JOIN GiangVien GV ON [Link] = [Link]
WHERE [Link] = 'Xây dựng hệ thống AI hỗ trợ bán hàng online';

--So svien lop D14CNPM5 di thuc tap

SELECT COUNT(*)
FROM SinhVien
WHERE Lop = 'D14CNPM5' AND MaSV IN (SELECT MaSV FROM
HuongDan);

--Default cot Noi thuc tap la 'Chua co noi thu tap'

CREATE DEFAULT ntt
AS
'Chua co noi thuc tap'
EXEC sp_bindefault 'ntt', '[Link]';

--Viet thu tuc nhap ma gv

CREATE PROCEDURE HienThiSinhVienTheoGiangVien
@MaGV VARCHAR(10) -- Tham số đầu vào: mã giảng viên
AS
BEGIN

SELECT [Link]
FROM SinhVien SV
JOIN HuongDan HD ON [Link] = [Link]
WHERE [Link] = @MaGV;
END;
EXEC HienThiSinhVienTheoGiangVien @MaGV = 'GV03';

--Thu tuc nhap ma sv

CREATE PROCEDURE HienThiGiangVienTheoSinhVien
@MaSV VARCHAR(10) -- Input parameter: student ID
AS
BEGIN
SELECT [Link]
FROM GiangVien GV
JOIN HuongDan HD ON [Link] = [Link]
JOIN SinhVien SV ON [Link] = [Link]
WHERE [Link] = @MaSV;
END;
EXEC HienThiGiangVienTheoSinhVien @MaSV = 'SV01';

--Tao view hien thi Ho ten sv, gv

CREATE VIEW vw_HienThi_SV AS
SELECT [Link] AS StudentName, [Link] AS InstructorName
FROM SinhVien SV
JOIN HuongDan HD ON [Link] = [Link]
JOIN GiangVien GV ON [Link] = [Link];
select * from vw_HienThi_SV

--Tao view hien thi ho ten gv, svinh giang vien huong dan
CREATE VIEW vw_HienThi_GV AS
SELECT [Link] AS InstructorName, [Link] AS StudentName
FROM GiangVien GV
JOIN HuongDan HD ON [Link] = [Link]
JOIN SinhVien SV ON [Link] = [Link]
WHERE [Link] = 'GV01';
SELECT * FROM vw_HienThi_GV;
CREATE DATABASE QUANLYKHACHSAN;
USE QUANLYKHACHSAN;

CREATE TABLE PHONG (

MAPH VARCHAR(10) PRIMARY KEY,
Loaiph VARCHAR(100),
DT varchar (20),
GIAPHONG INT
);

CREATE TABLE KHACHHANG (

MAKH VARCHAR(10) PRIMARY KEY,
TENKH VARCHAR(100),
dienthoai varchar (30),
DIACHI VARCHAR(255)
);

CREATE TABLE THUEPHONG (

MAHD VARCHAR(10) PRIMARY KEY,
MAKH VARCHAR(10)FOREIGN KEY REFERENCES
KHACHHANG(MAKH),
NGBD DATE
);

create table ctthuephong

MAHD VARCHAR(10) FOREIGN KEY REFERENCES Thuephong
(mahd),
MAPH VARCHAR(10)FOREIGN KEY REFERENCES Phong (maph),
primary key (mahd, maph),
ngkt date,
thanhtien int)

INSERT INTO PHONG (MAPH, Loaiph, DT, GIAPHONG)

VALUES
('P001', 'Single', '20m2', 500000),
('P002', 'Double', '30m2', 800000),
('P003', 'Suite', '50m2', 1200000);

INSERT INTO KHACHHANG (MAKH, TENKH, dienthoai, DIACHI)

VALUES
('KH001', 'Nguyen Thi Khanh Linh', '0123456789', 'Hanoi, Vietnam'),
('KH002', 'Le Minh Tu', '0987654321', 'HCM, Vietnam'),
('KH003', 'Tran Hoang Son', '0912345678', 'Da Nang, Vietnam');
INSERT INTO THUEPHONG (MAHD, MAKH, NGBD)
VALUES
('HD001', 'KH001', '2024-11-01'),
('HD002', 'KH002', '2024-11-05'),
('HD003', 'KH003', '2024-11-07');

INSERT INTO CTTHUEPHONG (MAHD, MAPH, NGKT, THANHTIEN)

VALUES
('HD001', 'P001', '2024-11-03', 1500000),
('HD001', 'P002', '2024-11-04', 1600000),
('HD002', 'P003', '2024-11-06', 2400000),
('HD003', 'P001', '2024-11-10', 1000000);

--Hien thi ten khach hang thue phong trong hop dong co ma HD01
SELECT [Link]
FROM KHACHHANG
JOIN THUEPHONG ON [Link] = [Link]
JOIN CTTHUEPHONG ON [Link] = [Link]
WHERE [Link] = 'HD001';

--Hien thi thong tin khach hang chua thue bao gio
SELECT MAKH, TENKH, DIACHI
FROM KHACHHANG
WHERE MAKH NOT IN (
SELECT MAKH
FROM THUEPHONG
);

--Hien thi thong tin cac phong chua bao gio duoc thue
SELECT [Link], [Link], [Link], [Link]
FROM PHONG
WHERE [Link] NOT IN (
SELECT MAPH
FROM CTTHUEPHONG
);
--Hien thi thong tin da thue phong HD01
SELECT [Link], [Link],
[Link]
FROM KHACHHANG
JOIN THUEPHONG ON [Link] = [Link]
JOIN CTTHUEPHONG ON [Link] = [Link]
WHERE [Link] = 'HD01';

--Hien thi cac phong duoc khach KH01 thue

SELECT [Link], [Link], [Link], [Link]
FROM PHONG
JOIN CTTHUEPHONG ON [Link] = [Link]
JOIN THUEPHONG ON [Link] = [Link]
WHERE [Link] = 'KH01';

--Hien thi cac phong thue NGBD 2020-09-02

SELECT [Link], [Link], [Link], [Link]
FROM PHONG
JOIN CTTHUEPHONG ON [Link] = [Link]
JOIN THUEPHONG ON [Link] = [Link]
WHERE [Link] = '2020-09-02';

--Hien thi cac phong thue 7 ngay

SELECT [Link], [Link], [Link]
FROM PHONG
JOIN CTTHUEPHONG ON [Link] = [Link]
WHERE DATEDIFF(DAY, [Link], [Link])
= 7;

--Hien thi ten khach, so lan thu voi so phong nhieu nhat
SELECT [Link], COUNT([Link]) AS
SoLanThue
FROM KHACHHANG
JOIN THUEPHONG ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) >= (
SELECT MAX(SoLanThue)
FROM (
SELECT COUNT([Link]) AS SoLanThue
FROM THUEPHONG
GROUP BY [Link]
) AS SubQuery
);
-- Phong duoc thue nhieu nhat
SELECT [Link], COUNT([Link]) AS
SoLanThue
FROM CTTHUEPHONG
GROUP BY [Link]
HAVING COUNT([Link]) = (
SELECT MAX(SoLanThue)
FROM (
SELECT COUNT([Link]) AS SoLanThue
FROM CTTHUEPHONG
GROUP BY [Link]
 ) AS SubQuery
);
--Khach hang Mai Hoa da thue bao phong
SELECT [Link], COUNT( [Link]) AS
SoPhongThue
FROM KHACHHANG
JOIN THUEPHONG ON [Link] = [Link]
JOIN CTTHUEPHONG ON [Link] = [Link]
group by [Link]
having [Link] = 'Mai Hoa'

--Hien thi gia phong cao nhat

select maph, loaiph, giaphong
from phong
where giaphong = (select max (giaphong) from phong)

--Tao bot sung rang buoc Defaul cho cot Thanh Tien = 0
CREATE DEFAULT DF_ThanhTien
as 0;
EXEC sp_bindefault 'DF_ThanhTien', '[Link]';

-- Tao rule cho cot Giaphong >=0

CREATE RULE Rule_GIAPHONG_Positive
AS
CHECK (GIAPHONG >= 0);
EXEC sp_bindrule 'Rule_GIAPHONG_Positive',
'[Link]';

-- Tao bo sung Defaul Diachi 'Chua xac dinh'

CREATE DEFAULT DF_DIACHI AS 'CHUA XAC DINH';
EXEC sp_bindefault 'DF_DIACHI', '[Link]';

--Tao thu tuc hien thi thong tin Khach hang khi biet ma phong va ngaybd
CREATE PROCEDURE GetCustomerInfoByRoomAndDate
@MAPH VARCHAR(10),
@NGBD DATE
AS
BEGIN
SELECT [Link], [Link], [Link], [Link]
FROM KHACHHANG KH
JOIN THUEPHONG TP ON [Link] = [Link]
JOIN CTTHUEPHONG CTP ON [Link] = [Link]
WHERE [Link] = @MAPH
AND [Link] = @NGBD;
END;
EXEC GetCustomerInfoByRoomAndDate 'PH01', '2020-09-02';

--Tao thu tuc thong tin Phong khi biet ma phong

CREATE PROCEDURE GetPhongInfoByMaph
@MAPH VARCHAR(10)
AS
BEGIN
SELECT MAPH, TENPH, DT, GIAPHONG
FROM PHONG
WHERE MAPH = @MAPH;
END;
EXEC GetPhongInfoByMaph @MAPH = 'PH01';

--Tao thu tuc xoa phong chua thue bao gio

CREATE PROCEDURE DeletePhongChuaThue
AS
BEGIN
DELETE FROM PHONG
WHERE MAPH NOT IN (SELECT DISTINCT MAPH FROM
CTTHUEPHONG);
ENDEXEC DeletePhongChuaThue;

--Tao thu tuc hien thi phong co nhieu khach thue nhat
CREATE PROCEDURE GetMostRentedRooms
AS
SELECT [Link], [Link], [Link], [Link], COUNT(*) AS SoLanThue
FROM CTTHUEPHONG CTP
JOIN PHONG P ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link]
HAVING COUNT(*) = (SELECT MAX(SoLanThue) FROM (
SELECT COUNT(*) AS SoLanThue
FROM CTTHUEPHONG
GROUP BY MAPH
) AS RoomRentCounts)
ORDER BY SoLanThue DESC;
END;
EXEC GetMostRentedRooms;

--Tao thu tuc hien thi phong co gia thap nhat

create proc c6
as
begin
select loaiph, giaphong
from Phong
where giaphong = (select min (GIAPHONG) from Phong)
end
exec c5
--tao view hien thi khach hang co dia chi 'HNoi'
CREATE VIEW View_KhachHang_HaNoi AS
SELECT MAKH, TENKH
FROM KHACHHANG
WHERE DIACHI = 'HA NOI';
SELECT * FROM View_KhachHang_HaNoi

--tao view hien thi ten khach hang, so luong phong ma KH thuê
CREATE VIEW View_KhachHang_SoLuongPhong AS
SELECT
[Link],
COUNT([Link]) AS SoLuongPhong
FROM
KHACHHANG KH
JOIN
THUEPHONG TH ON [Link] = [Link]
JOIN
CTTHUEPHONG CP ON [Link] = [Link]
GROUP BY
[Link];
SELECT * FROM View_KhachHang_SoLuongPhong;

--tao view hien thi phong co gia lon nhat

CREATE VIEW View_Phong_GiaMax
AS
SELECT MAPH, TENPH, DT, GIAPHONG
FROM PHONG
WHERE GIAPHONG = (SELECT MAX(GIAPHONG) FROM PHONG)
SELECT * FROM View_Phong_GiaMax;

--Tao trigger ktra dien tich trong phong phai lon hon 0 khi them thong tin44
create trigger c5 on phong
for insert
as
if (select dt from inserted)<=0
begin
print 'Nhap dien tich > 0'
rollback tran
end
-- tao trigger ktra NGBD và NGKT khi them/sua lon hon hoac bang ngay HT
CREATE TRIGGER trg_CheckDate
ON THUEPHONG
FOR INSERT, UPDATE
AS
BEGIN
-- Kiểm tra nếu ngày bắt đầu hoặc ngày kết thúc nhỏ hơn ngày hiện tại
IF EXISTS (
SELECT *
FROM inserted
WHERE NGBD < GETDATE() OR NGKT < GETDATE()
)
BEGIN
-- Thông báo lỗi và hoàn tác giao dịch nếu không thỏa mãn điều kiện
PRINT 'Ngày bắt đầu và ngày kết thúc phải lớn hơn hoặc bằng ngày hiện tại!';
ROLLBACK TRANSACTION;
END
END;

--Tao trigger cap nhat Thanh Tien khi them du lieu bang Thuephonh
CREATE TRIGGER UpdateThanhTien
ON THUEPHONG
FOR INSERT
AS
BEGIN
UPDATE THUEPHONG
SET THANHTIEN = (SELECT GIAPHONG * SONG
FROM CTTHUEPHONG
WHERE MAHD = (SELECT MAHD FROM inserted))
WHERE MAHD = (SELECT MAHD FROM inserted);
END;

--backup cơ sở dữ liệu sang ổ đĩa khác--

BACKUP DATABASE [database_name] TO DISK = 'E:\database_backup.bak'
-- T?o c? s? d? li?u
CREATE DATABASE QLKH_MaSinhVien;

-- Chuy?n sang c? s? d? li?u v?a t?o

USE QLKH_MaSinhVien;

-- T?o b?ng GiangVien

CREATE TABLE GiangVien (
GV VARCHAR(5) PRIMARY KEY,
HoTen VARCHAR(100),
DiaChi VARCHAR(100),
NgaySinh DATE
);

-- Nh?p d? li?u vào b?ng GiangVien

INSERT INTO GiangVien (GV, HoTen, DiaChi, NgaySinh) VALUES
('GV01', 'Nguy?n H?ng Khánh', 'M? ?ình, Hà N?i', '1982-10-10'),
('GV02', 'Ph??ng V?n C?nh', 'Nam T? Liêm, Hà N?i', '1986-11-03'),
('GV03', 'Ph?m Quang Huy', '??ng ?a, Hà N?i', '1990-06-04'),
('GV04', 'Ngô Hoàng Huy', 'Tây H?, Hà N?i', '1993-12-10'),
('GV05', 'Lê Trang Linh', 'C?u Gi?y, Hà N?i', '1986-10-10');

-- T?o b?ng DeTai

CREATE TABLE DeTai (
DT VARCHAR(5) PRIMARY KEY,
TenDT VARCHAR(100),
Cap VARCHAR(50),
KinhPhi INT
);

-- Nh?p d? li?u vào b?ng DeTai

INSERT INTO DeTai (DT, TenDT, Cap, KinhPhi) VALUES
('DT01', 'Phát hi?n tri th?c', 'Nhà n??c', 700),
('DT02', 'Tính toán l??i', 'B?', 300),
('DT03', 'An toàn thông tin', 'B?', 270),
('DT04', 'Nh?n d?ng khuôn m?t', 'EPUface', 30);

-- T?o b?ng ThamGia

CREATE TABLE ThamGia (
GV VARCHAR(5),
DT VARCHAR(5),
SoGio INT,
PRIMARY KEY (GV, DT),
 FOREIGN KEY (GV) REFERENCES GiangVien(GV),
FOREIGN KEY (DT) REFERENCES DeTai(DT)
);

-- Nh?p d? li?u vào b?ng ThamGia

INSERT INTO ThamGia (GV, DT, SoGio) VALUES
('GV01', 'DT01', 100),
('GV01', 'DT02', 80),
('GV01', 'DT03', 80),
('GV02', 'DT01', 120),
('GV02', 'DT03', 140),
('GV03', 'DT03', 150),
('GV04', 'DT04', 180);

-- Them cot thanh tien cho bang Tham gia

ALTER TABLE ThamGia
ADD ThanhTien int

-- Cap nhat lai gv

UPDATE GiangVien
SET DiaChi = 'C?u Gi?y, Hà N?i'
WHERE HoTen = 'Nguy?n H?ng Khánh';

-- Them thanh vien

INSERT INTO GiangVien (GV, HoTen, DiaChi, NgaySinh)
VALUES ('GV06', 'Nguy?n V?n Hi?u', '??ng ?a, Hà N?i', '1995-09-08');

-- Dua ra thong tin giang vien co dia chi Cau Giay, sx theo th? t? ten giam dan
SELECT GV, HoTen, DiaChi, NgaySinh
FROM GiangVien
WHERE DiaChi LIKE '%C?u Gi?y%'
ORDER BY HoTen DESC

-- Dua ra dsach tham gia vao de tai'Tinh toan luoi'

SELECT [Link], [Link], [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
JOIN DeTai ON [Link] = [Link]
WHERE [Link] = 'Tính toán l??i';

--Dua ra dsach gom ho ten, dia chi, ngay sinh tgia ?e tai 'Phat hien tri thuc'/ 'Nhan
dien khuon mat EPU'
SELECT [Link], [Link], [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
JOIN DeTai ON [Link] = [Link]
WHERE [Link] IN ('Phát hi?n tri th?c', 'Nh?n di?n khuôn m?t EPU');

-- Cho bt giang vien tham gia it nhat 2 de tai

SELECT [Link], [Link], [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
GROUP BY [Link], [Link], [Link],
[Link]
HAVING COUNT([Link]) >= 2;

-- Dua ra ma giang vien, ten gv, tong so tgia nghien cuu khoc hoc
SELECT [Link], [Link], SUM([Link]) AS
TongSoGio
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
GROUP BY [Link], [Link];

-- Cho biet giang vien tham gia nhieu de tai nhat

SELECT [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) >= ALL (
SELECT COUNT([Link])
FROM ThamGia
GROUP BY [Link]
)
-- Cho bt ten nhung gvien sinh truoc nam 1989 va tgia 'An toan thong tin'
SELECT [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
JOIN DeTai ON [Link] = [Link]
WHERE [Link] < '1989-01-01'
AND [Link] = 'An toàn thông tin';

--De tai nao it kinh phi nhat

SELECT TenDT
FROM DeTai
WHERE KinhPhi = (SELECT MIN(KinhPhi) FROM DeTai);

-- Cho biet ten, ngay sinh song Ho Tay va de tai

SELECT [Link], [Link], [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
JOIN DeTai ON [Link] = [Link]
WHERE [Link] = 'Tây H?'

-- Dua ra toan bo thong tin ma de tai, ten de tai, giang vien tgia, dc
SELECT [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
JOIN DeTai ON [Link] = [Link];

-- T?o view de hien thi thong tin gv, de tai co ma gv 01

CREATE VIEW vw_HienThi_GV AS
SELECT [Link], [Link], [Link],
[Link], [Link], [Link]
FROM GiangVien
JOIN ThamGia ON [Link] = [Link]
JOIN DeTai ON [Link] = [Link]
WHERE [Link] = 'GV01';
SELECT * FROM vw_HienThi_GV;

-- Tao view hien thi thong tin de tai va giang vien co ma dt 01

CREATE VIEW vw_HienThi_DT AS
SELECT [Link], [Link], [Link], [Link],
[Link], [Link]
FROM DeTai
JOIN ThamGia ON [Link] = [Link]
JOIN GiangVien ON [Link] = [Link]
WHERE [Link] = 'DT01';
SELECT * FROM vw_HienThi_DT;

--tao view liet ke de tai cap Bo

SELECT TenDT
FROM vw_DeTai_Bo
WHERE Cap = 'B?';

-- tao view liet ke kinh phi > 270

CREATE VIEW vw_DeTai_loc AS
SELECT TenDT, KinhPhi
FROM DeTai
WHERE KinhPhi > 270;

-- Tao thu tuc hien thi Ten de tai cua nhung de tai co kinh phi nho hon so chi dinh
CREATE PROCEDURE sp_HienThiDeTaiTheoKinhPhi (@KinhPhiMax
DECIMAL)
AS
BEGIN
-- Ki?m tra và hi?n th? tên ?? tài có kinh phí nh? h?n giá tr? ch? ??nh
IF EXISTS (SELECT 1 FROM DeTai WHERE KinhPhi < @KinhPhiMax)
BEGIN
SELECT TenDT
FROM DeTai
WHERE KinhPhi < @KinhPhiMax;
END
ELSE
BEGIN
PRINT 'Không có ?? tài nào';
END
END;
EXEC sp_HienThiDeTaiTheoKinhPhi @KinhPhiMax = 300;

-- Tao trigg? ktra so gio > 50

CREATE TRIGGER trg_CheckSoGio
ON ThamGia
FOR INSERT
AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted WHERE SoGio <= 50)
BEGIN
PRINT 'Không nh?p ???c';
ROLLBACK TRANSACTION;
END
END;

-- viet trigger cap nhat thanh tien tu bang tham gia, Thanhtien = sogio* 50000
CREATE TRIGGER trigger_UpdateThanhTien
ON ThamGia
FOR INSERT, UPDATE
AS
BEGIN
-- C?p nh?t giá tr? thành ti?n cho m?i b?n ghi trong b?ng inserted
UPDATE ThamGia
SET ThanhTien = SoGio * 50000
FROM ThamGia AS ThamGiaTable
INNER JOIN inserted AS InsertedTable
ON [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link];
END;
--backup c? s? d? li?u sang ? ??a khác--
BACKUP DATABASE [database_name] TO DISK = 'E:\database_backup.bak'
CREATE DATABASE Quanthuvien_Hotensv;
USE Quanthuvien_Hotensv;

CREATE TABLE Sach (

Masach VARCHAR(20) PRIMARY KEY,
Tensach VARCHAR(255),
NXB VARCHAR(255),
NamXB YEAR,
Theloai VARCHAR(255),
Soluong INT
);
CREATE TABLE Docgia (
MaDG VARCHAR(20) PRIMARY KEY,
Hoten VARCHAR(255),
DT VARCHAR(20),
Diachi VARCHAR(255)
);
CREATE TABLE Phieumuon (
Mapm VARCHAR(20) PRIMARY KEY,
Madg VARCHAR(20),
Ngaymuon DATE,
FOREIGN KEY (Madg) REFERENCES Docgia(MaDG)
);
CREATE TABLE CTPhieumuon (
Mapm VARCHAR(20),
Masach VARCHAR(20),
Soluong INT,
Ngayhentra DATE,
PRIMARY KEY (Mapm, Masach),
FOREIGN KEY (Mapm) REFERENCES Phieumuon(Mapm),
FOREIGN KEY (Masach) REFERENCES Sach(Masach)
);
INSERT INTO Sach (Masach, Tensach, NXB, NamXB, Theloai, Soluong)
VALUES
('S001', 'Harry Potter va Hon da Phu thuy', 'Nha xuat ban Kim Dong', 2000,
'Fantasy', 5),
('S002', 'De Men Phieu Luu Ky', 'Nha xuat ban Tre', 1990, 'Tre em', 3),
('S003', 'Song nhu nguoi Nhat', 'Nha xuat ban The gioi', 2020, 'Ky nang song', 4);

INSERT INTO Docgia (MaDG, Hoten, DT, Diachi)

VALUES
('DG001', 'Nguyen Thi Khanh Linh', '0123456789', 'Ha Noi'),
('DG002', 'Tran Van A', '0987654321', 'Ho Chi Minh');
INSERT INTO Phieumuon (Mapm, Madg, Ngaymuon)
VALUES
('PM001', 'DG001', '2024-11-01'),
('PM002', 'DG002', '2024-11-03');

INSERT INTO CTPhieumuon (Mapm, Masach, Soluong, Ngayhentra)

VALUES
('PM001', 'S001', 2, '2024-11-15'),
('PM001', 'S002', 1, '2024-11-15'),
('PM002', 'S003', 1, '2024-11-17');

--Hien thi ten ban PM01-

SELECT [Link]
FROM Phieumuon
JOIN Docgia ON [Link] = [Link]
WHERE [Link] = 'PM001';

--Hien thi sach sx 2022

SELECT *
FROM Sach
WHERE NXB = 'Nha xuat ban Giao duc' AND NamXB = 2022;

--Hien thi doc gia Ha Noi

SELECT *
FROM Docgia
WHERE Diachi = 'Ha Noi';

--Hien thi cuon sach ngay 20/11/2023

SELECT [Link], [Link], [Link]
FROM Phieumuon
JOIN CTPhieumuon ON [Link] = [Link]
JOIN Sach ON [Link] = [Link]
WHERE [Link] = '2023-11-20';

--Hien thi tong so sach ngay 20/11/2023

SELECT [Link], SUM([Link]) AS
TongSoSachMuon
FROM Phieumuon
JOIN CTPhieumuon ON [Link] = [Link]
WHERE [Link] = '2023-11-20'
GROUP BY [Link];

--Tong so sach muon boi DG01

SELECT [Link], [Link], SUM([Link]) AS
TongSoSachMuon
FROM Phieumuon
JOIN Docgia ON [Link] = [Link]
JOIN CTPhieumuon ON [Link] = [Link]
WHERE [Link] = 'DG001'
GROUP BY [Link], [Link];

--Tong so sach The loai Thieu nhi

SELECT [Link], SUM([Link]) AS TongSoSachMuon
FROM CTPhieumuon
JOIN Sach ON [Link] = [Link]
WHERE [Link] = 'Thieu nhi'
GROUP BY [Link];

--Tong so sach muon theo the loai

SELECT [Link], SUM([Link]) AS TongSoSachMuon
FROM CTPhieumuon
JOIN Sach ON [Link] = [Link]
GROUP BY [Link];

--Tong so sach muon theo ten tgia

SELECT [Link], SUM([Link]) AS TongSoSachMuon
FROM CTPhieumuon
JOIN Sach ON [Link] = [Link]
GROUP BY [Link];

--Cac doc gia muon nhieu sach nhat

SELECT [Link], [Link], SUM([Link]) AS
TongSoSachMuon
FROM Phieumuon
JOIN Docgia ON [Link] = [Link]
JOIN CTPhieumuon ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link]) >=
(SELECT MAX(SUM([Link]))
FROM Phieumuon
JOIN CTPhieumuon ON [Link] = [Link]
GROUP BY [Link]);

-- Doc gia muon > 3 quyenr

SELECT [Link], [Link], SUM([Link]) AS
TongSoSachMuon
FROM Phieumuon
JOIN Docgia ON [Link] = [Link]
JOIN CTPhieumuon ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link]) > 3;

--Cuon sach muon >3 lan

SELECT [Link], [Link], SUM([Link]) AS
TongSoLuotMuon
FROM CTPhieumuon
JOIN Sach ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link]) > 3;

--Cuon sach chua co khach muon

SELECT [Link], [Link], [Link]
FROM Sach
LEFT JOIN CTPhieumuon ON [Link] = [Link]
WHERE [Link] IS NULL;

-- Doc gia khong o Ha Noi

SELECT MaDG, Hoten, DT, Diachi
FROM Docgia
WHERE Diachi <> 'Hà Nội';

--Cuong sach khong duoc sx 'NXS Giao duc'

SELECT Masach, Tensach, NXB
FROM Sach
WHERE NXB <> 'Giáo dục';

-- Nam 2020 co quyen sach the loai gi

SELECT Theloai
FROM Sach
WHERE NamXB = 2020
GROUP BY Theloai;

--Tao default cho cot muon bang ngay hien tai

create default ngaymuon
as getdate ()
exec sp_bindefault 'ngaymuon','[Link]'

--Tao rule soluongsach >0

CREATE RULE soluong
AS
@Sl > 0
EXEC sp_bindrule 'soluong', '[Link]';
-- Tao thu tuc khi bt ma phieu muon
CREATE PROCEDURE GetReaderInfoByPhieuMuon
@Mapm VARCHAR(20)
AS
BEGIN
SELECT [Link], [Link], [Link], [Link]
FROM Phieumuon
JOIN Docgia ON [Link] = [Link]
WHERE [Link] = @Mapm;
END;
EXEC GetReaderInfoByPhieuMuon 'PM01';

--Tao thu tuc khi bt ma doc gia, hien thi sluong sach moi loai
CREATE PROCEDURE GetBookQuantityByReader
@MaDG VARCHAR(20)
AS
BEGIN
SELECT [Link], SUM([Link]) AS TongSoLuong
FROM CTPhieumuon
JOIN Phieumuon ON [Link] = [Link]
JOIN Sach ON [Link] = [Link]
WHERE [Link] = @MaDG
GROUP BY [Link];
END;
EXEC GetBookQuantityByReader 'DG01';

--Tao trigger cap nhat lai so luong sach trong thu vien khi sl sach trong
CTPhieumuon thay doi
CREATE TRIGGER UpdateBookQuantity
ON CTPhieumuon
for INSERT, UPDATE, DELETE
AS
BEGIN
-- Nếu có bản ghi INSERT hoặc UPDATE
IF EXISTS (SELECT * FROM inserted)
BEGIN
UPDATE Sach
SET Soluong = Soluong - (SELECT Soluong FROM inserted WHERE
[Link] = [Link])
WHERE Masach IN (SELECT Masach FROM inserted);
END

-- Nếu có bản ghi DELETE

IF EXISTS (SELECT * FROM deleted)
 BEGIN
UPDATE Sach
SET Soluong = Soluong + (SELECT Soluong FROM deleted WHERE
[Link] = [Link])
WHERE Masach IN (SELECT Masach FROM deleted);
END
END;

--Tao trigger cap nhat so luong sach trong thu vien khi xoa sach muon trong
CTPhieumuon
CREATE TRIGGER UpdateSoluongSach
ON CTPhieumuon
FOR DELETE
AS
BEGIN
-- Cập nhật lại số lượng sách trong bảng Sach
UPDATE Sach
SET Soluong = Soluong + (SELECT Soluong FROM deleted WHERE Masach
= [Link])
FROM Sach
WHERE [Link] = (SELECT Masach FROM deleted);
END;