BAI1 QUANLYBANHANG.sql
CREATE DATABASE QUANLYBANHANG
USE QUANLYBANHANG
CREATE TABLE DMKHACH(
MAK CHAR(10) PRIMARY KEY,
TENK CHAR(40) NOT NULL,
DC CHAR(50),
DT CHAR(15)
)
CREATE TABLE DMH(
MAH CHAR(10) PRIMARY KEY,
TENH CHAR(30) NOT NULL,
DVT CHAR(15)
)
CREATE TABLE HOADONBAN(
SOHD CHAR(10),
MAK CHAR(10),
NGAYHD SMALLDATETIME NOT NULL,
DIENGIAI CHAR(30)
CONSTRAINT KC_HOADONBAN PRIMARY KEY (SOHD),
CONSTRAINT KN_HOADONBAN FOREIGN KEY (MAK) REFERENCES DMKHACH(MAK)
)
CREATE TABLE CHITIETHOADON(
SOHD CHAR(10),
MAH CHAR(10),
SL INT,
DONGIA INT,
CONSTRAINT KC_CHITIETHOADON PRIMARY KEY (SOHD,MAH),
CONSTRAINT KN_CHITIETHOADON FOREIGN KEY (SOHD) REFERENCES HOADONBAN(SOHD),
CONSTRAINT KN1_CHITIETHOADON FOREIGN KEY (MAH) REFERENCES DMH(MAH)
)
--1.DUA RA DANH SACH MAT HANG CHUA DUOC BAN TRONG THANG 9 NAM 2009--
SELECT * FROM DMH
WHERE MAH NOT IN(SELECT MAH FROM CHITIETHOADON
WHERE SOHD IN(SELECT SOHD FROM HOADONBAN
WHERE MONTH(NGAYHD)='9' AND YEAR(NGAYHD)='2009'))
--DUA RA DANH SACH KHACH HANG CO DIA CHI O THAI NGUYEN VA TUNG MUA HANG TRONG THANG 9 NAM 2009--
SELECT * FROM DMKHACH
WHERE DC='THAI NGUYEN' AND MAK IN(SELECT MAK FROM HOADONBAN
WHERE MONTH(NGAYHD)='9' AND YEAR(NGAYHD)='2009')
--DUA RA SO LUONG DA BAN TUONG UNG CUA TUNG MAT HANG TRONG THANG 9 NAM 2009--
SELECT MAH,SUM(SL) AS SL
FROM CHITIETHOADON
WHERE SOHD IN(SELECT SOHD FROM HOADONBAN
WHERE MONTH(NGAYHD)='9' AND YEAR(NGAYHD)='2009')
GROUP BY MAH
--DUA RA TONG SO LUONG HANG DA BAN CHO MUC DICH MUA HANG 'TIEP KHACH'--
SELECT SUM(SL) AS SL
FROM CHITIETHOADON
WHERE SOHD IN(SELECT SOHD FROM HOADONBAN
WHERE DIENGIAI='TIEP KHACH')
--HIEN THI THONG TIN KHACH HANG DA TUNG MUA HANG TAI CUA HANG--
SELECT * FROM DMKHACH
WHERE MAK IN(SELECT MAK FROM HOADONBAN)
--2.TAO VIEW DE TONG HOP DU LIEU VE TUNG MAT HANG DA DUOC BAN (CAU TRUC VIEW GOM CAC THUOC TINH:
--MAH,TENH,DVT,SLBAN--
ALTER VIEW VD2
AS
SELECT DMH.MAH,TENH,DVT,SUM(SL) AS TSL
FROM DMH,CHITIETHOADON
WHERE DMH.MAH=CHITIETHOADON.MAH
GROUP BY DMH.MAH,TENH,DVT
SELECT * FROM VD2
--3.TAO VIEW DE TONG HOP DU LIEU VE CAC MAT HANG DA DUOC BAN VOI SO LUONG LON NHAT--
CREATE VIEW TG
AS
SELECT MAH,SUM(SL) AS TSL
FROM CHITIETHOADON
GROUP BY MAH
CREATE VIEW VD3
AS
SELECT DMH.*,TSL
FROM DMH,TG
WHERE DMH.MAH =TG.MAH AND TSL IN (SELECT MAX(TSL) FROM TG)
--4.TAO VIEW DE TONG HOP DU LIEU VE CAC KHACH HANG DA MUA HANG TRONG NGAY 20/10/2005--
CREATE VIEW VD4
AS
SELECT * FROM DMKHACH
WHERE MAK IN(SELECT MAK FROM HOADONBAN
WHERE NGAYHD='10/20/2005')
--5.TAO THU TUC CO THAM SO VAO LA @SOHD DE DUA RA DANH MUC CAC MAT HANG CO TRONG HOA DON TREN--
CREATE PROC VD5
@SOHD CHAR(10)
AS
SELECT * FROM DMH
WHERE MAH IN(SELECT MAH FROM CHITIETHOADON
WHERE SOHD=@SOHD)
EXEC VD5 'HD1'
--6.TAO THU TUC CO THAM SO VAO LA @NGAY DE DUA RA DANH MUA CAC MAT HANG DA DUOC BAN VAO NGAY TREN
--DANH SACH DUA RA GOM CAC THUOC TINH SAU:MAH,TENH,DVT,SL--
ALTER PROC VD6
@NGAY SMALLDATETIME
AS
SELECT DMH.MAH,TENH,DVT,SL
FROM DMH,CHITIETHOADON
WHERE DMH.MAH=CHITIETHOADON.MAH
AND SOHD IN(SELECT SOHD FROM HOADONBAN
WHERE CONVERT(CHAR(10),NGAYHD)=CONVERT(CHAR(10),@NGAY))
EXEC VD6 '10/10/2010'
--7.TAO TRIGGER DE KIEM TRA KHI NHAP DU LIEU VAO BANG CHITIETHOADON.NEU SO LUONG HOAC DON GIA
--NHAP VAO NHO HON 0 THI IN RA MAN HINH THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY
--KHONG DUOC PHEP NHAP VAO BANG.NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" --
CREATE TRIGGER TG7
ON CHITIETHOADON
FOR INSERT
AS
IF EXISTS (SELECT SL,DONGIA FROM CHITIETHOADON
WHERE SL<0 OR DONGIA<0)
BEGIN
PRINT 'DU LIEU NHAP VAO KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO CHITIETHOADON
VALUES('HD8','H03','-10','8000')
--8.TAO KIEU DU LIEU CURSOR DE LUU TRU THONG TIN VE CAC MAT HANG DA DUOC BAN TRONG NGAY 20/11/2006
--SAU DO DUA RA MAN HINH DANH SACH DU LIEU TREN--
--KHAI BAO--
DECLARE CS8 CURSOR FOR
SELECT * FROM DMH
WHERE MAH IN (SELECT MAH FROM CHITIETHOADON
WHERE SOHD IN (SELECT SOHD FROM HOADONBAN
WHERE NGAYHD='11/20/2006'))
--OPEN CURSOR--
OPEN CS8
--XU LY MAU TIN--
FETCH NEXT FROM CS8
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS8
END
--DONG CURSOR--
CLOSE CS8
--HUY--
DEALLOCATE CS8
--9.THEM MOI MOT TRUONG TONGTIEN VAO BANG HOADONBAN--
ALTER TABLE HOADONBAN
ADD TONGTIEN INT
--10.DUNG KIEU DU LIEU CURSOR DE CAP NHAP DU LIEU CHO TRUONG TONGTIEN--
--KHAI BAO--
DECLARE CS10 CURSOR FOR
SELECT SOHD,SUM(SL*DONGIA)
FROM CHITIETHOADON
GROUP BY SOHD
--MO--
OPEN CS10
--XU LY MAU TIN--
DECLARE @SOHD CHAR(10),@TONGTIEN FLOAT
FETCH NEXT FROM CS10
INTO @SOHD,@TONGTIEN
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE HOADONBAN
SET TONGTIEN=@TONGTIEN
WHERE SOHD=@SOHD
END
--DONG--
CLOSE CS10
--HUY--
DEALLOCATE CS10
quan ly ban nha
Create database QUANLYNHA1
use QUANLYNHA1
Create table Nha(
MaNnvarchar(10) not null Primary key,
TenChuNhanvarchar(30) not null,
GiaThueint not null,
DiaChiNhanvarchar(30) not null
)
Create table Khach(
MaKnvarchar(10) not null Primary key,
TenKnvarchar(30) not null,
DiaChinvarchar(30) not null,
SoDTnvarchar(15)
)
Create table HopDong(
SoHDnvarchar(10) not null Primary key,
MaNnvarchar(10) not null,
MaKnvarchar(10) not null,
NgayBDsmalldatetime not null,
NgayKTsmalldatetime not null,
Constraint KN1 Foreign key (MaN) References Nha(MaN),
Constraint KN2 Foreign key (MaK) References Khach(MaK)
)
Select *
From Nha
Select *
From Khach
From HopDong
Delete From Nha
Where MaN='N07'
Update Nha
Set GiaThue=GiaThue*1.05
/*Dua ra danh sach ve cac ngoi nha*/
Select *
From Nha
/*Dua ra thong tin ve cac khach hang da bat dau thue nha tu nam 2009*/
Select *
From Khach
Where MaK in (Select MaK From HopDong Where NgayBD>='01/01/2009')
/*Dua ra thong tin ve cac ngoi nha cho thue co dia chi o thai nguyen*/
Select *
From Nha
Where DiaChiNha='Thai Nguyen'
/*Dua ra thong tin ve cac ngoi nha chua tung duoc khach hang thue*/
Select *
From Nha
Where MaN not in (Select MaN From HopDong)
/*Dua ra thong tin ve cac khach hang da thue voi gia thue cao nhat*/
Select *
From Khach
Where MaK in (Select MaK
From HopDong
Where MaN in (Select MaN From Nha
Where GiaThue in (Select MAX(GiaThue) From Nha)))
/*Dua ra thong tin ve cac khach hang hom nay da het han hop dong thue nha*/
Select *
From Khach
Where MaK in (Select MaK From HopDong Where NgayKT=GETDATE())
/*Dua ra thong tin ve cac ngoi nha hien tai chua co khach hang thue*/
Select *
From Nha
Where MaN not in (Select MaN From HopDong Where GETDATE()< NgayKT)
/*Dua ra thong tin ve cac ngoi nha duoc thue vao ngay hom nay*/
Select *
From Nha
Where MaN in (Select MaN From HopDong Where NgayBD=GETDATE())
/*Tong hop tinh hinh thue nha cua nha co ma N01*/
Select Nha.*,Khach.*,SoHD,NgayBD,NgayKT
From Nha,Khach,HopDong
Where Nha.MaN=HopDong.MaN And HopDong.MaK=Khach.MaK And Nha.MaN='N01'
/*Tong hop thong tin ve cac khach hang da thue nha co ma KH01*/
Select *
From Khach
Where MaK in (Select MaK From HopDong Where MaK='KH01')
--2.TAO VIEW DE TONG HOP THONG TIN VE NHUNG NGOI NHA CO GIA THUE CAO NHAT--
CREATE VIEW VD2
AS
SELECT * FROM NHA
WHERE GIATHUE IN (SELECT MAX(GIATHUE) FROM NHA )
--3.TAO VIEW DE TONG HOP THONG TIN VE CAC KHACH HANG CO NGAY HOM NAY DA HET HAN HOP DONG--
CREATE VIEW VD3
AS
SELECT * FROM KHACH
WHERE MAK IN (SELECT MAK FROM HOPDONG
WHERE CONVERT(CHAR(10),NGAYKT)=CONVERT(CHAR(10),GETDATE()))
--4.TAO VIEW DE TONG HOP THONG TIN VE CAC NGOI NHA CHUA TUNG DUOC AI THUE--
CREATE VIEW VD4
AS
SELECT * FROM NHA
WHERE MAN NOT IN (SELECT MAN FROM HOPDONG)
--5.TAO THU TUC CO MOT THAM SO VAO LA @NGAY DE DUA RA DANH SACH CAC KHACH HANG DA HET HAN HOP DONG
--VAO NGAY TREN--
CREATE PROC VD5
@NGAY SMALLDATETIME
AS
SELECT * FROM KHACH
WHERE MAK IN (SELECT MAK FROM HOPDONG
WHERE CONVERT(CHAR(10),NGAYKT)=CONVERT(CHAR(10),@NGAY))
EXEC VD5 '10/30/2010'
--6.TAO THU TUC DE DUA RA MAN HINH DANH SACH CAC NGOI NHA HIEN TAI CHUA CO KHACH HANG THUE--
CREATE PROC VD6
AS
SELECT * FROM NHA
WHERE MAK NOT IN (SELECT MAK FROM HOPDONG
WHERE NGAYKT>GETDATE() )
--7.TAO THU TUC CO THAM SO LA @NGAY DE DUA RA DANH SACH CAC KHACH HANG BAT DAU THUE NHA VAO NGAY TREN--
CREATE PROC VD7
@NGAY SMALLDATETIME
AS
SELECT * FROM KHACH
WHERE MAK IN (SELECT MAK FROM HOPDONG
WHERE CONVERT(CHAR(10),NGAYBD)=CONVERT(CHAR(10),@NGAY))
EXEC VD7 '10/10/2010'
--8.TAO TRIGGER DE KIEM TRA KHI NHAP DU LIEU VAO BANG NHA.NEU GIA THUE NHAP VAO NHO HON 0 THI DUA RA
-- MAN HINH THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY KHONG DUOC PHEP NHAP VAO
--BANG.NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" --
CREATE TRIGGER VD8
ON NHA
FOR INSERT
AS
IF EXISTS (SELECT GIATHUE FROM NHA
WHERE GIATHUE<0)
BEGIN
PRINT 'DU LIEU NHAP VAO KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP VAO THANH CONG'
INSERT INTO NHA
VALUES('N05','THANH',-10000,'THAI NGUYEN')
--9.SU DUNG KIEU DU LIEU CURSOR DE DUA RA MAN HINH THONG TIN VE CAC KHACH HANG DANG THUE CAC NGOI NHA
--CO DIA CHI O "TO 10,PHUONG PHAN DINH PHUNG"--
--KHAI BAO--
DECLARE CS9 CURSOR FOR
SELECT KHACH.* FROM KHACH,HOPDONG,NHA
WHERE KHACH.MAK=HOPDONG.MAK AND HOPDONG.MAN=NHA.MAN AND DIACHINHA ='TO 10 PHUONG PHAN DINH PHUNG'
--MO--
OPEN CS9
--XU LY MAU TIN--
FETCH NEXT FROM CS9
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS9
END
--DONG--
CLOSE CS9
--HUY--
DEALLOCATE CS9
--10.SU DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH CAC NGOI NHA HIEN TAI CHUA CO KHACH HANG THUE--
--KHAI BAO--
DECLARE CS10 CURSOR FOR
SELECT * FROM NHA
WHERE MAN NOT IN (SELECT MAN FROM HOPDONG
WHERE NGAYKT>GETDATE())
--MO--
OPEN CS10
--XU LY MAU TIN--
FETCH NEXT FROM CS10
WHILE @@FETCH_STATUS =0
BEGIN
FETCH NEXT FROM CS10
END
--DONG--
CLOSE CS10
--HUY--
DEALLOCATE CS10
quan ly sinh vien
CREATE DATABASE QLSV
USE QLSV
CREATE TABLE LOP(
MAL CHAR(10) PRIMARY KEY,
TENL CHAR(20) NOT NULL,
)
CREATE TABLE TINH(
MAT CHAR(10) PRIMARY KEY,
TENT CHAR(40),
)
CREATE TABLE SV(
MASV CHAR(15) PRIMARY KEY,
HOTEN CHAR(50) NOT NULL,
NGAYS SMALLDATETIME,
GT BIT NOT NULL,
MAL CHAR(10) NOT NULL,
MAT CHAR(10) NOT NULL,
DTB REAL,
CONSTRAINT KN1 FOREIGN KEY (MAL) REFERENCES LOP,
CONSTRAINT KN2 FOREIGN KEY (MAT) REFERENCES TINH
)
/*1. DUA RA THONG TIN VE NHUNG SINH VIEN CO DIEM TRUNG BINH DUOI 5*/
SELECT *
FROM SV
WHERE DTB<5
/*2.DUA RA THONG TIN VE SINH VIEN CO DIA CHI O THAI NGUYEN*/
SELECT *
FROM SV
WHERE MAT IN(SELECT MAT FROM TINH
WHERE TENT='THAI NGUYEN')
/*3.DUA RA THONG TIN VE CAC LOP HOC KHONG CO SINH VIEN NAO O HA NOI*/
SELECT *
FROM LOP
WHERE MAL NOT IN(SELECT MAL FROM SV
WHERE MAT IN(SELECT MAT FROM TINH
WHERE TENT='HA NOI' ))
/*4.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN CO DIEM TRUNG BINH CAO NHAT*/
CREATE VIEW DTBMAX
AS
SELECT *
FROM SV
WHERE DTB IN (SELECT MAX(DTB) FROM SV)
SELECT * FROM DTBMAX
/*5.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN CO DIEM TRUNG BINH CAO NHAT THEO TUNG LOP HOC*/
CREATE VIEW DCNL
AS
SELECT MAL,MAX(DTB) AS DCNL
FROM SV
GROUP BY MAL
CREATE VIEW DTBMAXLOP
AS
SELECT SV.* FROM SV,DCNL
WHERE SV.MAL=DCNL.MAL AND DTB=DCNL
SELECT * FROM DTBMAXLOP
/*6.TAO THU TUC DE DUA RA SI SO SINH VIEN CHO TUNG LOP HOC(DANH SACH,DUA RA PHAI CO CAC THUOC TINH SAU:MALOP,TENLOP,SISO*/
CREATE PROC SSSV
AS
SELECT SV.MAL,TENL,COUNT(SV.MAL) AS SISO
FROM SV,LOP
WHERE SV.MAL=LOP.MAL
GROUP BY SV.MAL,TENL
EXEC SSSV
/*7.TAO THU TUC CO THAM SO LA @TENLOP DE XOA THONG TIN VE NHUNG SINH VIEN HOC O LOP TREN*/
CREATE PROC XOA
@TENL CHAR(20)
AS
DELETE FROM SV
WHERE MAL IN(SELECT MAL FROM LOP
WHERE TENL=@TENL)
XOA 'THKT'
--8.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG SV.NEU DTB NHAP VAO NHO HON 0 THI IN RA MAN HINH
--THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY KHONG DUOC PHEP NHAP VAO BANG.
--NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" --
CREATE TRIGGER TG8
ON SV
FOR INSERT
AS
IF EXISTS (SELECT DTB FROM SV
WHERE DTB<0)
BEGIN
PRINT 'DU LIEU NHAP VAO KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT'DU LIEU NHAP THANH CONG'
INSERT INTO SV
VALUES('SV8','THUONG','10/10/1995','1','N01','T01',-7.0)
--9.SU DUNG KIEU DU LIEU CURSOR DE DUA RA MAN HINH DANH SACH SINH VIEN HOC O LOP CO TEN LA 'CAO DANG 2A'--
--KHAI BAO--
DECLARE CS9 CURSOR FOR
SELECT * FROM SV
WHERE MAL IN (SELECT MAL FROM LOP
WHERE TENL='CAO DANG 2A')
--MO--
OPEN CS9
--XU LY MAU TIN--
FETCH NEXT FROM CS9
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS9
END
--DONG--
CLOSE CS9
--HUY--
DEALLOCATE CS9
quan ly thuc tap
CREATE DATABASE THUCTAP
USE THUCTAP
CREATE TABLE SV(
MASV CHAR(15) PRIMARY KEY,
HTSV CHAR(40) NOT NULL,
NGAYS SMALLDATETIME,
DC CHAR(50),
)
INSERT INTO SV
VALUES('SV01','NGOC LAN','10/30/1990','HA NOI')
INSERT INTO SV
VALUES('SV02','NGOC HUONG','11/28/1991','THAI NGUYEN')
INSERT INTO SV
VALUES('SV03','HUONG NHUNG','11/29/1991','NAM DINH')
INSERT INTO SV
VALUES('SV04','HONG NHUNG','11/11/1991','BINH DINH')
INSERT INTO SV
VALUES('SV05','HONG NHI','09/08/1989','NAM DINH')
CREATE TABLE DETAI(
MADT CHAR(10) PRIMARY KEY,
TENDT CHAR(50) NOT NULL,
GVHD CHAR(45),
)
INSERT INTO DETAI
VALUES('DT01','CSDL','NGUYEN HUONG')
INSERT INTO DETAI
VALUES('DT02','TIN','NGUYEN NGAN')
INSERT INTO DETAI
VALUES('DT03','TOAN','HONG NGAN')
INSERT INTO DETAI
VALUES('DT04','TOAN LY','HONG NAM')
INSERT INTO DETAI
VALUES('DT05','TOAN TIN','HONG NAM')
INSERT INTO DETAI
VALUES('DT06','TIN HOC','HONG NGAN')
CREATE TABLE SV_DETAI(
MADT CHAR(10),
MASV CHAR(15),
NTT CHAR(40) NOT NULL,
KQ INT,
CONSTRAINT KC PRIMARY KEY(MADT,MASV),
CONSTRAINT KN3 FOREIGN KEY (MADT) REFERENCES DETAI(MADT),
CONSTRAINT KN4 FOREIGN KEY (MASV) REFERENCES SV(MASV),
)
INSERT INTO SV_DETAI
VALUES('DT01','SV01','HA NOI','7')
INSERT INTO SV_DETAI
VALUES('DT02','SV02','NINH BINH','6')
INSERT INTO SV_DETAI
VALUES('DT03','SV03','NINH THUAN','8')
INSERT INTO SV_DETAI
VALUES('DT04','SV04','BINH THUAN','9')
INSERT INTO SV_DETAI
VALUES('DT05','SV05','BINH MINH','5')
--1.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN THAM GIA THUC TAP TAI QUE--
CREATE VIEW SVTT
AS
SELECT *
FROM SV
WHERE MASV IN (SELECT MASV FROM SV_DETAI
WHERE DC=NTT)
SELECT * FROM SVTT
--2.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN CO KET QUA CAO NHAT--
CREATE VIEW KQCN
AS
SELECT *
FROM SV
WHERE MASV IN(SELECT MASV FROM SV_DETAI
WHERE KQ IN(SELECT MAX(KQ) FROM SV_DETAI))
--3.TAO VIEW DE TONG HOP THONG TIN VE MOI GIAO VIEN DA VA DANG HUONG DAN BAO NHIEU SINH VIEN THUC TAP--
CREATE VIEW SL
AS
SELECT GVHD,COUNT(DETAI.MADT) AS SOSV
FROM DETAI,SV_DETAI
WHERE DETAI.MADT=SV_DETAI.MADT
GROUP BY GVHD
SELECT * FROM SL
--4.TAO THU TUC CO THAM SO VAO LA @GVHD DE DUA RA DANH SACH CAC SINH VIEN DO GIAO VIEN TREN DANG HUONG DAN--
CREATE PROC DSSV
@GVHD CHAR(45)
AS
SELECT * FROM SV
WHERE MASV IN(SELECT MASV FROM SV_DETAI
WHERE MADT IN(SELECT MADT FROM DETAI
WHERE GVHD=@GVHD))
DSSV 'HONG NAM'
--5.TAO THU TUC CO THAM SO VAO LA @NTT DE DUA RA DANH SACH CAC SINH VIEN THUC TAP TAI DIA DIEM TREN--
CREATE PROC DSSVTT
@NTT CHAR(40)
AS
SELECT * FROM SV
WHERE MASV IN(SELECT MASV FROM SV_DETAI
WHERE NTT=@NTT)
DSSVTT 'NINH BINH'
--6.TAO THU TUC DE DUA RA DANH SACH CAC DE TAI CHUA TUNG CO SINH VIEN NAO THAM GIA DANG KY THUC TAP--
CREATE PROC DSDT
AS
SELECT *
FROM DETAI
WHERE MADT NOT IN(SELECT MADT FROM SV_DETAI)
EXEC DSDT
--7.TAO TRIGGER DE KIEM TRA KHI NHAP DU LIEU VAO BANG SV_DETAI.NEU KET QUA THUC TAP NHO HON 0
--THI IN RA MAN HINH THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY
--KHONG DUOC PHEP NHAP VAO BANG.NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" --
CREATE TRIGGER TG7
ON SV_DETAI
FOR INSERT
AS
IF EXISTS (SELECT KQ FROM SV_DETAI
WHERE KQ<0)
BEGIN
PRINT 'DU LIEU NHAP VAO KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO SV_DETAI
VALUES ('DT07','SV07','THAI NGUYEN',-9)
--8.DUNG KIEU DU LIEU CURSOR DE DUA RA MAN HINH DANH SACH CAC SINH VIEN PHAI THUC TAP LAI (NHUNG
--SINH VIEN CO KET QUA THUC TAP<5 YEU CAU PHAI THUC TAP LAI)--
--KHAI BAO--
DECLARE CS8 CURSOR FOR
SELECT * FROM SV
WHERE MASV IN (SELECT MASV FROM SV_DETAI
WHERE KQ<5)
--MO--
OPEN CS8
--XU LY MAU TIN--
DECLARE @MASV CHAR(10),@HTSV CHAR(20),@NGAYS SMALLDATETIME,@DC CHAR(20)
FETCH NEXT FROM CS8
INTO @MASV,@HTSV,@NGAYS,@DC
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @MASV+@HTSV+CONVERT(CHAR(20),@NGAYS)+@DC
FETCH NEXT FROM CS8
INTO @MASV,@HTSV,@NGAYS,@DC
END
--DONG--
CLOSE CS8
--HUY--
DEALLOCATE CS8
--9.DUNG KIEU DU LIEU CURSOR DE DUA RA MAN HINH DANH SACH CAC DE TAI CO KET QUA THUC TAP CAO NHAT--
--KHAI BAO--
DECLARE CS9 CURSOR FOR
SELECT * FROM DETAI
WHERE MADT IN (SELECT MADT FROM SV_DETAI
WHERE KQ IN (SELECT MAX(KQ) FROM SV_DETAI))
--MO--
OPEN CS9
--XU LY MAU TIN--
FETCH NEXT FROM CS9
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS9
END
--DONG--
CLOSE CS9
--HUY--
DEALLOCATE CS9
quan ly thu vien
CREATE DATABASE QUANLYTHUVIEN
USE QUANLYTHUVIEN
CREATE TABLE SACH(
MAS CHAR(10) PRIMARY KEY,
TENS CHAR(50) NOT NULL,
NXB CHAR(50),
NAMXB INT,
TENTG CHAR(40),
)
CREATE TABLE DOCGIA(
MADG CHAR(15) PRIMARY KEY,
TENDG CHAR(40) NOT NULL,
DIACHIDG CHAR(50),
)
CREATE TABLE MUON_TRA(
MADG CHAR(15) NOT NULL,
MAS CHAR(10) NOT NULL,
NGAYMUON SMALLDATETIME,
SL INT,
NGAYHENTRA SMALLDATETIME,
NGAYTRA SMALLDATETIME,
CONSTRAINT KC PRIMARY KEY(MADG,MAS,NGAYMUON),
CONSTRAINT KN1 FOREIGN KEY (MADG) REFERENCES DOCGIA(MADG),
CONSTRAINT KN2 FOREIGN KEY (MAS) REFERENCES SACH(MAS),
)
/* 1.TAO VIEW DE TONG HOP THONG TIN VE CAC DOC GIA DA HET HAN TRA SACH NHUNG CHUA TRA*/
ALTER VIEW VD1
AS
SELECT *
FROM DOCGIA
WHERE MADG IN (SELECT MADG FROM MUON_TRA
WHERE NGAYHENTRA<GETDATE() AND NGAYTRA >GETDATE() OR NGAYTRA IS NULL)
SELECT *
FROM VD1
/* 2.TAO VIEW DE TONG HOP THONG TIN VE DANH MUC SACH CHUA TUNG DUOC DOC GIA NAO MUON*/
CREATE VIEW VD2
AS
SELECT *
FROM SACH
WHERE MAS NOT IN(SELECT MAS FROM MUON_TRA)
SELECT *
FROM VD2
/* 3.TAO VIEW DE TONG HOP THONG TIN VE TUNG LOAI SACH HIEN TAI CO DOC GIA DANG MUON( CAU TRUC VIEW
GOM CAC THUOC TINH SAU:MASACH,TENSACH,TENTG,SOLUONG)*/
CREATE VIEW VD3
AS
SELECT SACH.MAS,TENS,TENTG,SUM(SL) AS SOLUONG
FROM SACH,MUON_TRA
WHERE SACH.MAS=MUON_TRA.MAS AND NGAYTRA>GETDATE() OR NGAYTRA IS NULL
GROUP BY SACH.MAS,TENS,TENTG
SELECT *
FROM VD3
/* 4.TAO THU TUC CO THAM SO LA @MADG DE DUA RA DANH MUC SACH MA DOC GIA TREN DANG MUON*/
CREATE PROC VD4
@MADG CHAR(15)
AS
SELECT SACH.*
FROM SACH,DOCGIA,MUON_TRA
WHERE SACH.MAS=MUON_TRA.MAS AND DOCGIA.MADG=MUON_TRA.MADG
AND DOCGIA.MADG=@MADG AND NGAYTRA>GETDATE() OR NGAYTRA IS NULL
EXEC VD4 'DG1'
/* 5. TAO THU TUC CO THAM SO LA @NGAYMUON DE DUA RA DANH SACH CAC DOC GIA DA MUON SACH VAO NGAY TREN*/
CREATE PROC MUONSACH
@NGAYMUON SMALLDATETIME
AS
SELECT DOCGIA.*
FROM DOCGIA,MUON_TRA
WHERE DOCGIA.MADG=MUON_TRA.MADG AND NGAYMUON=@NGAYMUON
EXEC MUONSACH '10/7/2010'
/* 6. TAO THU TUC DE DUA RA DANH SACH CAC DOC GIA NGAY HOM NAY LA HAN CUOI CUNG PHAI TRA SACH CHO THU VIEN*/
CREATE PROC HANTRA
AS
SELECT DOCGIA.*
FROM DOCGIA,MUON_TRA
WHERE DOCGIA.MADG=MUON_TRA.MADG AND DATEDIFF(DAY,NGAYHENTRA,GETDATE())=0
EXEC HANTRA
/* 7.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG MUON_TRA NEU SO LUONG MUON NHO HON 0 THI
DUA RA MAN HINH THONG BAO LOI VA BAN GHI NAY KHONG DUOC PHEP NHAP VAO BANG DU LIEU, NGUOC LAI
DUA RA THONG BAO BAN GHI DA NHAP THANH CONG*/
CREATE TRIGGER TG7
ON MUON_TRA
FOR INSERT
AS
IF EXISTS (SELECT SL FROM MUON_TRA
WHERE SL<0)
BEGIN
PRINT 'DU LIEU NHAP VAO KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
/* 8. DUNG KIEU DU LIEU CUSTOR DE DUA RA MAN HINH DANH SACH CAC DOC GIA DA HIEN TAI VAN DANG
MUON SACH CUA THU VIEN*/
--KHAI BAO--
DECLARE CS8 CURSOR FOR
SELECT * FROM DOCGIA
WHERE MADG IN (SELECT MADG FROM MUON_TRA
WHERE NGAYTRA>GETDATE() OR NGAYTRA IS NULL)
--MO--
OPEN CS8
--XU LY MAU TIN--
FETCH NEXT FROM CS8
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS8
END
--DONG--
CLOSE CS8
--HUY--
DEALLOCATE CS8
quan ly du an
CREATE DATABASE QUANLYDUAN
USE QUANLYDUAN
CREATE TABLE NHANVIEN(
MANV CHAR(10) PRIMARY KEY,
HOTEN CHAR(40) NOT NULL,
NGAYS SMALLDATETIME,
GOITINH CHAR(3) --GIOI TINH--
)
CREATE TABLE DUAN(
MADA CHAR(10) PRIMARY KEY,
TENDA CHAR(30) NOT NULL,
NGANSACH MONEY
)
CREATE TABLE THAMGIA(
MADA CHAR(10),
MANV CHAR(10),
TGBD SMALLDATETIME NOT NULL, --THOI GIAN BAT DAU--
TGKT SMALLDATETIME NOT NULL, --THOI GIAN KET THUC--
CONSTRAINT KC_THAMGIA PRIMARY KEY (MADA,MANV),
CONSTRAINT KN_THAMGIA FOREIGN KEY (MADA) REFERENCES DUAN(MADA),
CONSTRAINT KN1_THAMGIA FOREIGN KEY (MANV) REFERENCES NHANVIEN(MANV),
)
/*2.tao view de tong hop thong tin ve cac nhan vien chua tham gia bat ky du an nao*/
CREATE VIEW VD2
AS
SELECT * FROM NHANVIEN
WHERE MANV NOT IN (SELECT MANV FROM THAMGIA)
--3.tao view de tong hop thong tin ve cac du an co ngan sach lon nhat--
CREATE VIEW VD3
AS
SELECT * FROM DUAN
WHERE NGANSACH IN (SELECT MAX(NGANSACH) FROM DUAN)
--tao view de tong hop thong tin ve moi nhan vien da tham gia bao nhieu du an--
CREATE VIEW VD31
AS
SELECT NHANVIEN.MANV,HOTEN,NGAYS,GOITINH,COUNT(MADA) AS SODUAN
FROM NHANVIEN,THAMGIA
WHERE NHANVIEN.MANV=THAMGIA.MANV
GROUP BY NHANVIEN.MANV,HOTEN,NGAYS,GOITINH
--4.tao thu tuc co tham so la @tenda de dua ra danh sach cac nhan vien tham gia du an tren--
CREATE PROC VD4
@TENDA CHAR(30)
AS
SELECT * FROM NHANVIEN
WHERE MANV IN (SELECT MANV FROM THAMGIA
WHERE MADA IN (SELECT MADA FROM DUAN
WHERE TENDA=@TENDA))
EXEC VD4 'DAU TU'
/* 5.TAO THU TUC CO THAM SO LA @TGKT VA @MADA DE DUA RA DANH SACH CAC NHAN VIEN PHAI HOAN THANH
DU AN DO VAO NGAY TREN*/
ALTER PROC VD5
@TGKT SMALLDATETIME,@MADA CHAR(10)
AS
SELECT * FROM NHANVIEN
WHERE MANV IN (SELECT MANV FROM THAMGIA
WHERE MADA=@MADA AND CONVERT(CHAR(10),TGKT)=CONVERT(CHAR(10),@TGKT))
DECLARE @TGKT SMALLDATETIME,@MADA CHAR (10)
SET @TGKT='10/10/2010'
EXEC VD5 @TGKT, 'DA1'
/*6.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG DUAN.NEU NGANSACH NHO HON 0 THI IN RA MAN HINH
THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY KHONG DUOC PHEP NHAP VAO BANG.
NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" */
CREATE TRIGGER TG6
ON DUAN
FOR INSERT
AS
IF EXISTS (SELECT NGANSACH FROM DUAN
WHERE NGANSACH<0)
BEGIN
PRINT 'DU LIEU NHAP VAO KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
/*7.DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH NHAN VIEN BAT DAU THUC HIEN DU AN "PHONG
CHONG BENH MAT HOT" TU NGAY 20/11/2005 */
--KHAI BAO--
DECLARE CS7 CURSOR FOR
SELECT * FROM NHANVIEN
WHERE MANV IN (SELECT MANV FROM THAMGIA
WHERE TGBD='11/20/2005'
AND MADA IN (SELECT MADA FROM DUAN
WHERE TENDA='PHONG CHONG BENH MAT HOT'))
--MO--
OPEN CS7
--XU LY MAU TIN--
FETCH NEXT FROM CS7
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS7
END
--DONG--
CLOSE CS7
--HUY--
DEALLOCATE CS7
/*8.DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH CAC DU AN CHUA TUNG CO NHAN VIEN NAO THAM GIA
DANG KY */
--KHAI BAO--
DECLARE CS8 CURSOR FOR
SELECT * FROM DUAN
WHERE MADA NOT IN (SELECT MADA FROM THAMGIA)
--MO--
OPEN CS8
--XU LY MAU TIN--
FETCH NEXT FROM CS8
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS8
END
--DONG--
CLOSE CS8
--HUY--
DEALLOCATE CS8
quan ly tai san
CREATE DATABASE QUANLYTAISAN
USE QUANLYTAISAN
create table dmphong
(map nvarchar(10)primary key,
tenp nvarchar(30)not null)
create table dmtaisan
(mats nvarchar(10)primary key,
tents nvarchar(30)not null,
donvi nvarchar(20),--DON VI TINH--
gia int)--DON GIA
create table phanphoi
(mats nvarchar(10)not null,
map nvarchar(10)not null,
soluong int,
ngaypp datetime,--NGAY PHAN PHOI TAI SAN--
ghichu nvarchar(20),
constraint kc primary key (mats,map),
constraint kn1 foreign key(mats)references dmtaisan(mats),
constraint kn2 foreign key (map)references dmphong(map) )
--2.TAO VIEW DE TONG HOP THONG TIN VE CAC TAI SAN CO DON GIA LON NHAT--
CREATE VIEW VD2
AS
SELECT * FROM DMTAISAN
WHERE GIA IN (SELECT MAX(GIA) FROM DMTAISAN)
--3.TAO VIEW DE TONG HOP THONG TIN VE NHUNG TAI SAN CHUA TUNG DUOC PHAN PHOI CHO CAC PHONG DE SU DUNG--
CREATE VIEW VD3
AS
SELECT * FROM DMTAISAN
WHERE MATS NOT IN (SELECT MATS FROM PHANPHOI)
--4.TAO THU TUC CO THAM SO LA @TENP DE DUA RA DANH SACH CAC TAI SAN DUOC PHAN PHOI VAO PHONG TREN--
CREATE PROC VD4
@TENP CHAR(30)
AS
SELECT * FROM DMTAISAN
WHERE MATS IN (SELECT MATS FROM PHANPHOI
WHERE MAP IN (SELECT MAP FROM DMPHONG
WHERE TENP=@TENP))
EXEC VD4 'LAN'
--5.TAO THU TUC CO THAM SO LA @TENTS DE DUA RA DANH SACH CAC PHONG DUOC PHAN PHOI TAI SAN TREN--
CREATE PROC VD5
@TENTS CHAR(20)
AS
SELECT DMTAISAN.*
FROM DMTAISAN,PHANPHOI
WHERE DMTAISAN.MATS=PHANPHOI.MATS AND TENTS=@TENTS
EXEC VD5 'SAT'
/*6.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG DMTAISAN.NEU DON GIA NHAP VAO NHO HON 0
THI IN RA MAN HINH THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY KHONG DUOC PHEP
NHAP VAO BANG.NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" */
CREATE TRIGGER TG6
ON DMTAISAN
FOR INSERT
AS
IF EXISTS (SELECT GIA FROM DMTAISAN
WHERE GIA<0)
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
quan ly diem thi
CREATE DATABASE QUANLYDIEM
USE QUANLYDIEM
CREATE TABLE SINHVIEN(
MASV CHAR(10) PRIMARY KEY,
HOTENSV CHAR(40) NOT NULL,
NGAYSINH SMALLDATETIME,
DIACHI CHAR(40),
DVHT INT--DON VI HOC TRINH--
)
CREATE TABLE MON(
MAM CHAR(10) PRIMARY KEY,
TENM CHAR(30)
)
CREATE TABLE DIEM(
MAM CHAR(10),
MASV CHAR(10),
PHACH CHAR(10),--SO PHACH--
DIEM FLOAT,--DIEM THI--
CONSTRAINT KC_DIEM PRIMARY KEY(MAM,MASV,PHACH),
CONSTRAINT KN_DIEM FOREIGN KEY (MAM) REFERENCES MON(MAM),
CONSTRAINT KN1_DIEM FOREIGN KEY (MASV) REFERENCES SINHVIEN(MASV)
)
/*2.TAP VIEW DE TONG HOP THONG TIN VE SINH VIEN CO DIEM THI CAO NHAT CUA MON "CSDL"*/
CREATE VIEW VD2
AS
SELECT * FROM SINHVIEN
WHERE MASV IN (SELECT MASV FROM DIEM
WHERE DIEM IN (SELECT MAX(DIEM) FROM DIEM)
AND MAM IN (SELECT MAM FROM MON
WHERE TENM='CSDL'))
/*3.TAO VIEW DE TONG HOP THONG TIN VE SINH VIEN CO DIA CHI O "THAI NGUYEN"*/
CREATE VIEW VD3
AS
SELECT * FROM SINHVIEN
WHERE DIACHI='THAI NGUYEN'
/*4.TAO VIEW CHO BIET THONG TIN VE CAC SINH VIEN SINH TRUOC NAM 1980*/
CREATE VIEW VD4
AS
SELECT * FROM SINHVIEN
WHERE YEAR(NGAYSINH)<1980
/*5.TAO THU TUC CO THAM SO DAU VAO LA @TENM DE DUA RA DANH SACH CAC SINH VIEN CO DIEM THI
CHUA DAT CUA MON HOC TREN*/
ALTER PROC VD5
@TENM CHAR(30)
AS
SELECT * FROM SINHVIEN
WHERE MASV IN (SELECT MASV FROM DIEM
WHERE DIEM<5 AND MAM IN (SELECT MAM FROM MON
WHERE TENM=@TENM))
EXEC VD5 'JAVA'
/*6.TAO THU TUC CO THAM SO DAU VAO @DIEM DE DUA RA DANH SACH CAC SINH VIEN CO DIEM THI
THAP HON DIEM TREN CUA MON HOC "CSDL"*/
CREATE PROC VD6
@DIEM FLOAT
AS
SELECT * FROM SINHVIEN
WHERE MASV IN (SELECT MASV FROM DIEM
WHERE DIEM<@DIEM AND MAM IN (SELECT MAM FROM MON
WHERE TENM='CSDL'))
EXEC VD6 9
/*7.TAO TRIGGER DE KIEM TRA VIEC NHAP DU LIEU CHO BANG DIEM.NEU DIEM NHO HON 0 HOAC DIEM
LON HON 10 THI IN RA MAN HINH THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY
KHONG DUOC PHEP NHAP VAO BANG.NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG"*/
CREATE TRIGGER TG8
ON DIEM
FOR INSERT
AS
IF EXISTS (SELECT DIEM FROM DIEM
WHERE DIEM<0 OR DIEM>10)
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO DIEM
VALUES ('M3','SV1','P07',12)
/*8.DUNG KIEU DU LIEU CURSOR DE DUA RA MAN HINH DANH SACH CAC SINH VIEN CO DIEM THI CAO
NHAT CUA CAC MON HOC*/
--TAO VIEW TRUNG GIAN--
ALTER VIEW MAXDIEM
AS
SELECT MAM,MAX(DIEM) AS DIEMCAONHAT
FROM DIEM
GROUP BY MAM
--KHAI BAO--
DECLARE CS8 CURSOR FOR
SELECT DIEM.MAM,SINHVIEN.MASV,HOTENSV,NGAYSINH,DIACHI,DVHT,DIEM
FROM SINHVIEN,MAXDIEM,DIEM
WHERE SINHVIEN.MASV=DIEM.MASV AND DIEM.MAM=MAXDIEM.MAM AND DIEM=DIEMCAONHAT
GROUP BY DIEM.MAM,SINHVIEN.MASV,HOTENSV,NGAYSINH,DIACHI,DVHT,DIEM
--MO--
OPEN CS8
--XU LY MAU TIN--
PRINT 'DANH SACH SINH VIEN CO DIEM THI CAO NHAT CUA CAC MON HOC LA'
PRINT 'MAM MASV TENSVNGAYSINHDVHTDIEM'
DECLARE @B1 CHAR(10),@B2 CHAR(10),@B3 CHAR(20),@B4 SMALLDATETIME,@B5 CHAR(20),@B6 INT,
@B7 FLOAT
FETCH NEXT FROM CS8
INTO @B1,@B2,@B3,@B4,@B5,@B6,@B7
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @B1+@B2+@B3+CONVERT(CHAR(15),@B4)+@B5+CONVERT(CHAR(10),@B6)+CONVERT(CHAR(5),@B7)
FETCH NEXT FROM CS8
INTO @B1,@B2,@B3,@B4,@B5,@B6,@B7
END
--DONG--
CLOSE CS8
--HUY--
DEALLOCATE CS8
quan ly khach san
CREATE DATABASE QUANLYKHACHSAN
USE QUANLYKHACHSAN
CREATE TABLE KHACHHANG(
MAK CHAR(10) PRIMARY KEY,
TENK CHAR(40) NOT NULL,
DIACHI CHAR(40),
SDT CHAR(15)
)
DROP TABLE PHONG (
MAP CHAR(10) PRIMARY KEY,
TENP CHAR(30) NOT NULL,
LOAIP CHAR(20),--LOAI PHONG--
DONGIA FLOAT
)
CREATE TABLE DATPHONG(
MAK CHAR(10),
MAP CHAR(10),
NGAYNHANSMALLDATETIME,--NGAY NHAN PHONG--
NGAYTRASMALLDATETIME,--NGAY TRA PHONG--
CONSTRAINT KC_DATPHONG PRIMARY KEY(MAK,MAP,NGAYNHAN),
CONSTRAINT KN_DATPHONG FOREIGN KEY (MAK) REFERENCES KHACHHANG(MAK),
CONSTRAINT KN1_DATPHONG FOREIGN KEY (MAP) REFERENCES PHONG(MAP),
)
/*2.TAO THU TUC DE TANG GIA CUA TAT CA CAC PHONG TRONG KHACH SAN LEN 5%*/
CREATE PROC VD2
AS
UPDATE PHONG
SET DONGIA=DONGIA*1.05
EXEC VD2
/*3.TAO VIEW DE TONG HOP THONG TIN VE CAC KHACH HANG HIEN TAI DANG THUE PHONG TAI KHACH SAN*/
CREATE VIEW VD3
AS
SELECT * FROM KHACHHANG
WHERE MAK IN(SELECT MAK FROM DATPHONG
WHERE NGAYTRA>GETDATE())
/*4.TAO VIEW DE TONG HOP THONG TIN HOA DON THANH TOAN CUA KHACH HANG(HOA DON GOM CAC
THUOC TINH SAU:(MAK,TENK,MAP,DONGIA,NGAYNHAN,NGAYTRA,THANH TIEN)*/
CREATE VIEW VD4
AS
SELECT KHACHHANG.MAK,TENK,PHONG.MAP,DONGIA,NGAYNHAN,NGAYTRA,
CONVERT(INT,(NGAYTRA-NGAYNHAN))*DONGIA AS THANHTIEN
FROM KHACHHANG,PHONG,DATPHONG
WHERE KHACHHANG.MAK=DATPHONG.MAK AND PHONG.MAP=DATPHONG.MAP
GROUP BY KHACHHANG.MAK,TENK,PHONG.MAP,DONGIA,NGAYNHAN,NGAYTRA
/*5.TAO VIEW DE TONG HOP THONG TIN VE CAC KHACH HANG CO SO NGAY DAT PHONG LON NHAT*/
CREATE VIEW TG
AS
SELECT KHACHHANG.MAK,TENK,DIACHI,SDT,SUM(CONVERT(INT,(NGAYTRA-NGAYNHAN))) AS NGAYTHUE
FROM KHACHHANG,DATPHONG
WHERE KHACHHANG.MAK=DATPHONG.MAK
GROUP BY KHACHHANG.MAK,TENK,DIACHI,SDT
CREATE VIEW NGAYMAX
AS
SELECT * FROM TG
WHERE NGAYTHUE IN (SELECT MAX(NGAYTHUE)
FROM TG)
SELECT * FROM NGAYMAX
/*6.TAO THU TUC DE DUA RA DANH SACH CAC PHONG HIEN TAI CHUA CO KHACH HANG DAT PHONG*/
CREATE PROC VD6
AS
SELECT * FROM PHONG
WHERE MAP NOT IN (SELECT MAP FROM DATPHONG
WHERE NGAYTRA>GETDATE())
EXEC VD6
/*7.TAO THU TUC DE DUA RA DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG*/
CREATE PROC VD7
AS
SELECT * FROM KHACHHANG
WHERE MAK IN(SELECT MAK FROM DATPHONG
WHERE CONVERT(CHAR(10),NGAYTRA)=CONVERT(CHAR(10),GETDATE()))
EXEC VD7
/*8.TAO TRIGGER DE KIEM TRA DU LIEU KHI CAP NHAT VAO BANG PHONG PHAI DAM BAO DU LIEU DONGIA>0*/
CREATE TRIGGER TG8
ON PHONG
FOR INSERT
AS
IF EXISTS (SELECT DONGIA FROM PHONG
WHERE DONGIA<0)
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
/*9.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG DATPHONG PHAI DAM BAO NGAYTRA PHONG LUON
LON HON HOAC BANG NGAY NHAN PHONG*/
ALTER TRIGGER TG9
ON DATPHONG
FOR INSERT
AS
IF EXISTS (SELECT NGAYTRA,NGAYNHAN FROM DATPHONG
WHERE CONVERT(INT,NGAYTRA)<CONVERT(INT,NGAYNHAN))
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO DATPHONG
VALUES('K03','P03','11/20/2010','11/19/2010')
/*10.DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG*/
--KHAI BAO--
DECLARE CS10 CURSOR FOR
SELECT KHACHHANG.* FROM KHACHHANG
WHERE MAK IN(SELECT MAK FROM DATPHONG
WHERE CONVERT(CHAR(10),NGAYTRA)=CONVERT(CHAR(10),GETDATE()))
--MO--
OPEN CS10
--XU LY--
PRINT 'DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG LA'
PRINT 'MAKTENKDIACHISDT'
DECLARE @MAK CHAR(10),@TENK CHAR(30),@DIACHI CHAR(30),@SDT CHAR(15)
FETCH NEXT FROM CS10
INTO @MAK,@TENK,@DIACHI,@SDT
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @MAK+@TENK+@DIACHI+@SDT
FETCH NEXT FROM CS10
INTO @MAK,@TENK,@DIACHI,@SDT
END
--DONG--
CLOSE CS10
--HUY--
DEALLOCATE CS10
quan ly nhan vien
CREATE DATABASE QUANLYNHANVIEN
USE QUANLYNHANVIEN
CREATE TABLE PHONG(
MAP CHAR(10) PRIMARY KEY,
TENP CHAR(30) NOT NULL,
DIACHIP CHAR(30),
SDT CHAR(15)
)
CREATE TABLE NHANVIEN(
MANV CHAR(10) PRIMARY KEY,
TENNV CHAR(40) NOT NULL,
DIACHI CHAR(40),
MAP CHAR(10),
LUONG INT,--LUONG CUA NHAN VIEN--
CONSTRAINT KN_NHANVIEN FOREIGN KEY(MAP) REFERENCES PHONG(MAP)
)
CREATE TABLE NGOAINGU(
MANN CHAR(10) PRIMARY KEY,
TENN CHAR(30) NOT NULL
)
CREATE TABLE TDNN(--TRINH DO NGOAI NGU--
MANV CHAR(10),
MANN CHAR(10),
TRINHDO CHAR(30) NOT NULL,
CONSTRAINT KC_TDNN PRIMARY KEY(MANV,MANN),
CONSTRAINT KN_TDNN FOREIGN KEY(MANV) REFERENCES NHANVIEN(MANV),
CONSTRAINT KN1_TDNN FOREIGN KEY(MANN) REFERENCES NGOAINGU(MANN),
)
--2.TAO VIEW DE TONG HOP THONG TIN VE MOI PHONG HIEN TAI CO BAO NHIEU NHAN VIEN--
CREATE VIEW VD2
AS
SELECT PHONG.MAP,TENP,DIACHIP,SDT,COUNT(NHANVIEN.MAP) AS SONV
FROM PHONG,NHANVIEN
WHERE PHONG.MAP=NHANVIEN.MAP
GROUP BY phong.MAP,TENP,DIACHIP,SDT
--3/ Tạo View để tổng hợp thông tin về các nhân viên chưa tích luỹ được trình độ ngoại ngữ nào--
CREATE VIEW VD3
AS
SELECT * FROM NHANVIEN
WHERE MANV NOT IN (SELECT MANV FROM TDNN)
--4/ Tạo View để tổng hợp thông tin về các nhân viên có lương cao nhất trong phòng họ làm việc.--
CREATE VIEW TG
AS
SELECT MAP,MAX(LUONG) AS LUONGCAONHAT
FROM NHANVIEN
GROUP BY MAP
CREATE VIEW LUONGMAX
AS
SELECT NHANVIEN.*
FROM NHANVIEN,TG
WHERE NHANVIEN.MAP=TG.MAP AND LUONG=LUONGCAONHAT
/*5/ Tạo thủ tục có tham số vào @TenNN và @TrinhDo để đưa ra danh các nhân viên biết ngoại ngữ
và trình độ trên. */
CREATE PROC VD5
@TENN CHAR(30),@TRINHDO CHAR(30)
AS
SELECT * FROM NHANVIEN
WHERE MANV IN (SELECT MANV FROM TDNN
WHERE TRINHDO=@TRINHDO AND MANN IN (SELECT MANN FROM NGOAINGU
WHERE TENN=@TENN))
EXEC VD5 'TIENG ANH','A'
--6/ Tạo thủ tục có tham số vào @TenP để đưa ra tổng số nhân viên hiện có trong phòng này--
ALTER PROC VD6
@TENP CHAR(20)
AS
SELECT PHONG.MAP,COUNT(NHANVIEN.MAP) AS SONV
FROM PHONG,NHANVIEN
WHERE PHONG.MAP=NHANVIEN.MAP AND TENP=@TENP
GROUP BY PHONG.MAP
EXEC VD6 'ABC'
/*7/ Tạo thủ tục có tham số vào là @TenNN, @TenP và @TrinhDo để đưa ra danh các nhân viên
ở phòng này biết ngoại ngữ và trình độ ứng với các tham biến đã cho */
CREATE PROC VD7
@TENNN CHAR(30),@TENP CHAR(20),@TRINHDO CHAR(30)
AS
SELECT NHANVIEN.* FROM NHANVIEN,PHONG,TDNN,NGOAINGU
WHERE NHANVIEN.MANV=TDNN.MANV AND PHONG.MAP=NHANVIEN.MAP
AND NGOAINGU.MANN=TDNN.MANN AND TENN=@TENNN AND TENP=@TENP AND TRINHDO=@TRINHDO
EXEC VD7 'TIENG ANH','ABC','A'
/*8/ Tạo thủ tục có tham số vào @TenP để đưa ra danh các nhân viên có lương cao nhất của phòng này*/
CREATE PROC VD8
@TENP CHAR(20)
AS
SELECT NHANVIEN.*
FROM NHANVIEN,TG,PHONG
WHERE NHANVIEN.MAP=TG.MAP AND TG.MAP=PHONG.MAP AND LUONG=LUONGCAONHAT AND TENP=@TENP
EXEC VD8 'ABC'
/*9/ Tạo Trigger để kiểm tra khi nhập dữ liệu vào bảng NhanVien phải đảm bảo lương của nhân viên
phải >0 */
CREATE TRIGGER tg9
ON NHANVIEN
FOR INSERT
AS
IF EXISTS (SELECT LUONG FROM NHANVIEN
WHERE LUONG<0)
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO NHANVIEN
VALUES('NV6','HAI','HA NOI','P04','7000')
--10/ Dùng kiểu dữ liệu CURSOR để tăng lương cho mỗi nhân viên lên 10%.--
--KHAI BAO--
DECLARE CS10 CURSOR FOR
SELECT MANV,TENNV,DIACHI,MAP,(LUONG*1.1) AS LUONGMOI
FROM NHANVIEN
GROUP BY MANV,TENNV,DIACHI,MAP,LUONG
--MO--
OPEN CS10
--XU LY--
PRINT 'DANH SACH NHAN VIEN SAU KHI TANG LUONG LEN 10% LA:'
PRINT 'MANVTENNVDIACHI MAPLUONGMOI'
DECLARE @MANV CHAR(10),@TENNV CHAR(20),@DIACHI CHAR(20),@MAP CHAR(10),@LUONG INT
FETCH NEXT FROM CS10
INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @MANV+@TENNV+@DIACHI+@MAP+CONVERT(CHAR(10),@LUONG)
FETCH NEXT FROM CS10
INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG
END
--DONG--
CLOSE CS10
--HUY--
DEALLOCATE CS10
--11/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các nhân viên có ngoại ngữ tiếng anh trình độ C--
--KHAI BAO--
DECLARE CS11 CURSOR FOR
SELECT NHANVIEN.MANV,TENNV,DIACHI,MAP,LUONG
FROM NHANVIEN,TDNN,NGOAINGU
WHERE NHANVIEN.MANV=TDNN.MANV AND TDNN.MANN=NGOAINGU.MANN
AND TENN='TIENG ANH' AND TRINHDO='C'
--MO--
OPEN CS11
--XU LY--
PRINT 'DANH SACH NHAN VIEN CO NGAOI NGU TIENG ANH TRINH DO C LA:'
PRINT 'MANVTENNVDIACHI MAPLUONG'
DECLARE @MANV CHAR(10),@TENNV CHAR(20),@DIACHI CHAR(20),@MAP CHAR(10),@LUONG INT
FETCH NEXT FROM CS11
INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @MANV+@TENNV+@DIACHI+@MAP+CONVERT(CHAR(10),@LUONG)
FETCH NEXT FROM CS11
INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG
END
--DONG--
CLOSE CS11
--HUY--
DEALLOCATE CS11
quan ly trinh do hoc vien
CREATE DATABASE QUANLYTRINHDOHOCVINHANVIEN
USE QUANLYTRINHDOHOCVINHANVIEN
CREATE TABLE PHONG(
MAP CHAR(10) PRIMARY KEY,
TENP CHAR(30) NOT NULL,
DIACHIP CHAR(30),
SDT CHAR(15)
)
CREATE TABLE NHANVIEN(
MANV CHAR(10) PRIMARY KEY,
TENNV CHAR(40) NOT NULL,
DIACHI CHAR(40),
MAP CHAR(10),
LUONG INT,--LUONG CUA NHAN VIEN--
CONSTRAINT KN_NHANVIEN FOREIGN KEY(MAP) REFERENCES PHONG(MAP)
)
CREATE TABLE HOCVI(
MAHV CHAR(10) PRIMARY KEY,
TENHV CHAR(30) NOT NULL
)
CREATE TABLE TDHVNV(--TRINH DO HOC VI NHAN VIEN--
MANV CHAR(10) ,
MATDHV CHAR(10),
THOIGIANSMALLDATETIME,--NGAY THANG NAM DAT TRINH DO NAY--
CONSTRAINT KC_TDHVNV PRIMARY KEY(MANV,MATDHV),
CONSTRAINT KN_TDHVNV FOREIGN KEY(MANV) REFERENCES NHANVIEN(MANV)
)
--2/ Tạo View để tổng hợp thông tin về các nhân viên có học vị là tiến sĩ trước năm 1990.--
CREATE VIEW VD2
AS
SELECT * FROM NHANVIEN
WHERE
--3/ Tạo View để tổng hợp thông tin về các nhân viên chưa có học vị Tiến sĩ.--
/*4/ Tạo thủ tục có tham số vào là @TenHV và @ThoiGian để đưa ra danh các nhân viên có học vị
và thời gian đạt được ứng với các tham biến đã cho. */
/*5/ Tạo thủ tục có tham số vào là @TenHV và @ThoiGian để đưa ra danh các nhân viên có
trình độ học vị và thời gian đạt được học vị đó ứng với các tham biến đã cho. */
/*6/ Tạo trigger để kiểm tra dữ liệu khi nhập vào bảng NV_TDHV sao cho thời gian đạt học vị
đó phải <= thời gian hiện tại.*/
/*7/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các nhân viên có học vị Tiến sĩ.*/
quan ly diem ren luyen
CREATE DATABASE QUANLYDIEMRENLUYEN
USE QUANLYDIEMRENLUYEN
CREATE TABLE LOP(
MAL CHAR(10) PRIMARY KEY,
TENL CHAR(20) NOT NULL
)
CREATE TABLE SV(
MASV CHAR(10) PRIMARY KEY,
TENSV CHAR(30) NOT NULL,
NGAYSINH SMALLDATETIME,
MAL CHAR(10),
CONSTRAINT KN_SV FOREIGN KEY (MAL) REFERENCES LOP(MAL)
)
CREATE TABLE DIEMRL(
MASV CHAR(10),
HOCKY INT,
NAM CHAR(4),
DIEM INT--DIEM REN LUYEN--
CONSTRAINT KC_DIEMRL PRIMARY KEY(MASV,HOCKY,NAM),
CONSTRAINT KN_DIEMRL FOREIGN KEY (MASV) REFERENCES SV(MASV)
)
--2. Hãy tạo view để tổng hợp thông tin về điểm rèn luyện của những sinh viên học lớp “Cao đẳng K4A”--
CREATE VIEW VD2
AS
SELECT * FROM DIEMRL
WHERE MASV IN (SELECT MASV FROM SV
WHERE MAL IN (SELECT MAL FROM LOP
WHERE TENL='CAO DANG K4A'))
--3. Hãy tạo view để in ra thông tin về sinh viên có điểm rèn luyện cao nhất.--
ALTER VIEW VD3
AS
SELECT *
FROM SV
WHERE MASV IN (SELECT MASV FROM DIEMRL
WHERE DIEM IN (SELECT MAX(DIEM) FROM DIEMRL))
--4. Tạo view để tổng hợp thông tin về những sinh viên có điểm rèn luyện<50 trong năm học 2006-2007.--
CREATE VIEW VD4
AS
SELECT * FROM SV
WHERE MASV IN(SELECT MASV FROM DIEMRL
WHERE DIEM<50 AND NAM ='2006-2007')
--5. Tạo thủ tục có tham số vào @Masv để đưa ra thông tin về điểm rèn luyện của sinh viên trên.--
CREATE PROC VD5
@MASV CHAR(10)
AS
SELECT * FROM DIEMRL
WHERE MASV =@MASV
EXEC VD5 'SV2'
/*6. Tạo thủ tục có tham số vào @Malop, @Nam để đưa ra thông tin về điểm rèn luyện của lớp trên vào
nam học trên.*/
CREATE PROC VD6
@MAL CHAR(10),@NAM CHAR(4)
AS
SELECT * FROM DIEMRL
WHERE NAM=@NAM AND MASV IN (SELECT MASV FROM SV
WHERE MAL=@MAL)
EXEC VD6 'L01','2010'
--7. Tạo thủ tục có tham số vào @tenlop để đưa ra thông tin về điểm rèn luyện của lớp trên.--
CREATE PROC VD7
@TENL CHAR(20)
AS
SELECT * FROM DIEMRL
WHERE MASV IN (SELECT MASV FROM SV
WHERE MAL IN (SELECT MAL FROM LOP
WHERE TENL=@TENL))
EXEC VD7 'TH'
/*8. Tạo Trigger kiểm tra việc nhập dữ liệu cho bảng DIEMRL nếu điểm nhập vào <0 hoặc >100 thì đưa
ra yêu cầu nhập lại và bản ghi này không được phép nhập vào bảng, ngược lại thì in ra thông báo nhập
thành công.*/
CREATE TRIGGER TG8
ON DIEMRL
FOR INSERT
AS
IF EXISTS (SELECT DIEM FROM DIEMRL
WHERE DIEM<0 OR DIEM >100)
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO DIEMRL
VALUES('SV4','2','2007',90)
/*9. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của học kỳ 1 năm học
2007-2008.*/
DECLARE CS9 CURSOR FOR
SELECT DIEMRL.*
FROM DIEMRL
WHERE HOCKY='1' AND NAM='2007-2008'
--MO--
OPEN CS9
--XU LY--
PRINT 'DANH SACH DIEM REN LUYEN CUA HOC KY 1 NAM HOC 2007-2008 LA:'
PRINT 'MASV HOCKY NAM DIEM'
DECLARE @MASV CHAR(10),@HOCKY INT,@NAM CHAR(20),@DIEM INT
FETCH NEXT FROM CS9
INTO @MASV,@HOCKY,@NAM,@DIEM
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @MASV+CONVERT(CHAR(10),@HOCKY)+@NAM+CONVERT(CHAR(10),@DIEM)
FETCH NEXT FROM CS9
INTO @MASV,@HOCKY,@NAM,@DIEM
END
--DONG--
CLOSE CS9
--HUY--
DEALLOCATE CS9
--10. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của năm học 2006-2007.--
--KHAI BAO--
DECLARE CS10 CURSOR FOR
SELECT DIEMRL.*
FROM DIEMRL
WHERE NAM='2006-2007'
--MO--
OPEN CS10
--XU LY--
PRINT 'DANH SACH DIEM REN LUYEN CUA HOC KY 1 NAM HOC 2007-2008 LA:'
PRINT 'MASV HOCKY NAM DIEM'
DECLARE @MASV CHAR(10),@HOCKY INT,@NAM CHAR(20),@DIEM INT
FETCH NEXT FROM CS10
INTO @MASV,@HOCKY,@NAM,@DIEM
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @MASV+CONVERT(CHAR(10),@HOCKY)+@NAM+CONVERT(CHAR(10),@DIEM)
FETCH NEXT FROM CS10
INTO @MASV,@HOCKY,@NAM,@DIEM
END
--DONG--
CLOSE CS10
--HUY--
DEALLOCATE CS10
quan ly dang vien
CREATE DATABASE QUANLYDANGVIEN
USE QUANLYDANGVIEN
CREATE TABLE CHIBO(
MACB CHAR(10) PRIMARY KEY, --MA CHI BO DANG--
TENCB CHAR(30) NOT NULL --TEN CHI BO DANG--
)
CREATE TABLE TTDV( --THONG TIN DANG VIEN--
MADV CHAR(10) PRIMARY KEY, --MA DANG VIEN--
TENDV CHAR(40) NOT NULL, --TEN DANG VIEN--
NGAYSINH SMALLDATETIME,
GIOITINH CHAR(3) NOT NULL,
NGAYVD SMALLDATETIME NOT NULL, --NGAY VAO DANG--
MACB CHAR(10) NOT NULL, --MA CHI BO DANG--
CONSTRAINT KN_TTDV FOREIGN KEY (MACB) REFERENCES CHIBO(MACB)
)
CREATE TABLE KHENTHUONG(
MADV CHAR(10) NOT NULL,
MASKT CHAR(10) NOT NULL, --MA SO KHEN THUONG--
NGAYQD SMALLDATETIME, --NGAY RA QUYET DINH KHEN THUONG--
CKT CHAR(20), --CAP KHEN THUONG--
LYDO CHAR(30),
CONSTRAINT KC_KHENTHUONG PRIMARY KEY (MADV,MASKT),
CONSTRAINT KN_KHENTHUONG FOREIGN KEY (MADV) REFERENCES TTDV(MADV)
)
--1.TAO VIEW DE TONG HOP THONG TIN VE CAC DANG VIEN VAO DANG NGAY 19/5/2009--
CREATE VIEW TTDANGVIEN
AS
SELECT *
FROM TTDV
WHERE NGAYVD='5/19/2009'
SELECT * FROM TTDANGVIEN
--2.TAO THU TUC LUU TRU VOI THAM SO VAO @MASKT DE LUU THONG TIN VE NHUNG DANG VIEN DUOC KHEN THUONG--
--THEO MA SO KHEN THUONG O TREN--
CREATE PROC VD2
@MASKT CHAR(10)
AS
SELECT *
FROM TTDV
WHERE MADV IN(SELECT MADV FROM KHENTHUONG
WHERE MASKT=@MASKT)
EXEC VD2 'SKT1'
--3.TAO TRIGGER CHO PHEP THAY DOI DU LIEU CUA NHUNG BANG LIEN QUAN TRONG VIEW O Y 1 KHI CAP NHAT DU LIEU TREN VIEW--
--DUNG INSERT DE THEM DU LIEU TRONG VIEW--
ALTER TRIGGER TG3
ON TTDANGVIEN
INSTEAD OF INSERT
AS
INSERT INTO TTDV(MADV,TENDV,NGAYSINH,GIOITINH,NGAYVD,MACB)
SELECT MADV,TENDV,NGAYSINH,GIOITINH,NGAYVD,MACB FROM INSERTED
INSERT INTO TTDANGVIEN
VALUES('DV8','HAI','11/9/1983','NAM','5/19/2009','CB3')
--DUNG UPDATE DE SUA DOI DU LIEU TRONG VIEW--
CREATE TRIGGER tg4
ON TTDANGVIEN
INSTEAD OF UPDATE
AS
UPDATE TTDV
SET TENDV=(SELECT TENDV FROM INSERTED)
WHERE MADV=(SELECT MADV FROM INSERTED)
update ttdangvien
SET TENDV='NINH'
WHERE MADV='DV1'
--4.DUNG KIEU DU LIEU CURSOR DE TONG HOP THONG TIN VE CAC DANG VIEN CHUA DUOC KHEN THUONG TRONG NAM 2009--
--KHAI BAO--
DECLARE CS4 CURSOR FOR
SELECT TTDV.*
FROM TTDV
WHERE MADV NOT IN (SELECT MADV FROM KHENTHUONG
WHERE YEAR(NGAYQD)='2009')
--MO CURSOR--
OPEN CS4
--XU LY MAU TIN--
PRINT 'NHUNG DANG VIEN CHUA DUOC KHEN THUONG TRONG NAM 2009 LA:'
PRINT 'MADV TENDVNGAYSINHGIOITINHNGAYVDMACB'
DECLARE @MADV CHAR(10),@TENDV CHAR(40),@NGAYSINH SMALLDATETIME,@GIOITINH CHAR(5),
@NGAYVD SMALLDATETIME,@MACB CHAR(10)
FETCH NEXT FROM CS4
INTO @MADV,@TENDV,@NGAYSINH,@GIOITINH,@NGAYVD,@MACB
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @MADV+@TENDV+CONVERT(CHAR(25),@NGAYSINH)+@GIOITINH+CONVERT(CHAR(25),@NGAYVD)+@MACB
FETCH NEXT FROM CS4
INTO @MADV,@TENDV,@NGAYSINH,@GIOITINH,@NGAYVD,@MACB
END
--DONG CURSOR--
CLOSE CS4
--HUY CURSOR--
DEALLOCATE CS4
thue xe du lich
CREATE DATABASE THUEXEDULICH
USE THUEXEDULICH
CREATE TABLE DMX(
MAXE NVARCHAR(10) PRIMARY KEY NOT NULL,
TENXE NVARCHAR(30) NOT NULL,
LOAIXE NVARCHAR(5),
TT BIT NOT NULL,
)
CREATE TABLE DMK(
MAK NVARCHAR(15) PRIMARY KEY NOT NULL,
TENK NVARCHAR(30) NOT NULL,
DC NVARCHAR(40),
DT NVARCHAR(15),
)
CREATE TABLE HD(
SOHD NVARCHAR(10) PRIMARY KEY NOT NULL,
MAK NVARCHAR(15) NOT NULL,
MAXE NVARCHAR(10) NOT NULL,
NGAYDI SMALLDATETIME NOT NULL,
NGAYVE SMALLDATETIME NOT NULL,
GIA INT NOT NULL,
CONSTRAINT KN3 FOREIGN KEY (MAK) REFERENCES DMK(MAK),
CONSTRAINT KN4 FOREIGN KEY (MAXE) REFERENCES DMX(MAXE),
)
INSERT INTO DMX
VALUES('X01','AB','4','1')
INSERT INTO DMX
VALUES('X02','BC','6','0')
INSERT INTO DMX
VALUES('X03','BD','15','1')
INSERT INTO DMX
VALUES('X04','EB','20','0')
INSERT INTO DMX
VALUES('X05','EC','4','1')
SELECT *
FROM DMX
INSERT INTO DMK
VALUES('KH001','QUANG HAI','THAI NGUYEN','01689999999')
INSERT INTO DMK
VALUES('KH002','QUANG HIEP','THAI BINH','01689999998')
INSERT INTO DMK
VALUES('KH003','HOANG HAI','NINH BINH','01689999988')
INSERT INTO DMK
VALUES('KH004','NGUYEN VAN MO','NAM DINH','01689998888')
INSERT INTO DMK
VALUES('KH005','NGUYEN VAN HUNG','BINH DINH','01689888888')
SELECT *
FROM DMK
INSERT INTO HD
VALUES('HD01','KH001','X01','09/09/2010','09/15/2010','500000')
INSERT INTO HD
VALUES('HD02','KH002','X02','10/02/2010','10/04/2010','1000000')
INSERT INTO HD
VALUES('HD03','KH003','X03','09/30/2010','10/02/2010','2000000')
INSERT INTO HD
VALUES('HD04','KH002','X03','10/03/2010','10/12/2010','5000000')
INSERT INTO HD
VALUES('HD05','KH004','X01','08/03/2010','08/11/2010','4000000')
INSERT INTO HD
VALUES('HD06','KH005','X05','09/10/2010','09/20/2010','7000000')
INSERT INTO HD
VALUES('HD07','KH003','X05','08/30/2010','10/10/2010','6000000')
SELECT *
FROM HD
UPDATE HD
SET GIA=GIA*0.01
DELETE FROM DMK
WHERE MAK='KH003'
ALTER TABLE HD
ADD DAINHAT INT
ALTER TABLE HD
DROP COLUMN DAINHAT
/*DUA RA THONG TIN VE CAC KHACH HANG CO HOP DONG THUE XE HOM NAY LA NGAY VE*/
SELECT *
FROM DMK
WHERE MAK IN (SELECT MAK FROM HD WHERE NGAYVE=GETDATE())
/*DUA RA THONG TIN VE CAC KHACH HANG DA THUE XE VOI GIA THUE CAO NHAT*/
SELECT *
FROM DMK
WHERE MAK IN (SELECT MAK FROM HD WHERE GIA=(SELECT MAX(GIA) FROM HD))
/*DUA RA THONG TIN VE CAC XE DA DUOC KHACH HANG CO MA KHACH 'KH001' THUE*/
SELECT *
FROM DMX
WHERE MAXE IN (SELECT MAXE FROM HD WHERE MAK='KH001')
/*TONG HOP THONG TIN VE TINH HINH CHO THUE XE.THONG TIN THONG KE GOM:MAXE,TENXE,SOTIEN*/
SELECT DMX.MAXE,TENXE,GIA
FROM DMX,HD
WHERE DMX.MAXE=HD.MAXE
/*DUA RA THONG TIN VE CAC XE HIEN CHUA CO KHACH HANG THUE*/
SELECT *
FROM DMX
WHERE MAXE NOT IN(SELECT MAXE
FROM HD
WHERE GETDATE()<NGAYVE OR NGAYVE IS NULL)
/*DUA RA THONG TIN VE CAC XE DA DUOC THUE CO NGAY BAT DAU LA NGAY HOM NAY*/
SELECT *
FROM DMX
WHERE MAXE IN (SELECT MAXE FROM HD WHERE GETDATE()=NGAYDI)
/*DUA RA THONG TIN VE CAC KHACH HANG DA THUE XE DAI NGAY NHAT*/
SELECT *
FROM DMK
WHERE MAK IN (SELECT MAK FROM HD WHERE (NGAYVE-NGAYDI) IN (SELECT MAX(NGAYVE-NGAYDI) FROM HD) )
/*DUA RA THONG TIN VE CAC XE CHUA TUNG DUOC KHACH HANG NAO THUE*/
SELECT *
FROM DMX
WHERE MAXE NOT IN(SELECT MAXE FROM HD)
/*DUA RA THONG TIN THUE XE VE KHACH HANG CO TEN "NGUYEN VAN MO" */
SELECT *
FROM HD
WHERE MAK IN (SELECT MAK FROM DMK WHERE TENK='NGUYEN VAN MO')
/*DUA RA THONG TIN VE CAC KHACH HANG DA THUE XE 4 CHO TRONG THANG 8/2010 */
SELECT *
FROM DMK
WHERE MAK IN(SELECT MAK FROM HD
WHERE MONTH(NGAYDI)='08' AND YEAR(NGAYDI)='2010' AND MONTH(NGAYVE)='08' AND YEAR(NGAYVE)='2010'
AND MAXE IN(SELECT MAXE FROM DMX WHERE LOAIXE='4'))