BTC SQL
--Chuong I:Thiet ke bang trong CSDL va rang buoc toan ven
CREATE DATABASE QLSV
USE QLSV
--Tao bang SV
CREATE TABLE Sinhvien(
masv VARCHAR(10) NOT NULL,
hoten NVARCHAR(30),
diachi NVARCHAR(30),
namsinh DATETIME,
makhoa VARCHAR(10))
--Tao bang Khoa
CREATE TABLE Khoa(
makhoa VARCHAR(10) NOT NULL,
tenkhoa NVARCHAR(30))
--Tao bang Mon hoc
CREATE TABLE Monhoc(
mamon VARCHAR(10) PRIMARY KEY,
tenmon NVARCHAR(30))
--Tao bang Diem thi
CREATE TABLE Diemthi(
mamon VARCHAR(10) NOT NULL,
masv VARCHAR(10) NOT NULL,
diemthi FLOAT)
--Cau 1.1:Tao rang buoc khoa tren cot masv cua bang Sinhvien
ALTER TABLE Sinhvien ADD CONSTRAINT p_masv PRIMARY KEY(MaSV)
--Cau 1.2:Tao rang buoc Khoa tren cot makhoa
ALTER TABLE Khoa ADD CONSTRAINT p_makhoa PRIMARY KEY (makhoa)
--Cau 1.3:Tao rang buoc khoa tren cot Masv va mamon cua bang Diemthi
ALTER TABLE Diemthi ADD CONSTRAINT p_mamon_masv PRIMARY KEY (MaSV,mamon)
--Cau 1.4:Tao chi muc cho cot Hoten trong bang sinhvien
CREATE INDEX i_hoten ON SInhvien(hoten)
--Cau 1.5:Them cot Khoahoc vao bang Sinhvien
ALTER TABLE Sinhvien ADD khoahoc VARCHAR(10)
--Cau 1.6:Tao rang buoc Not null tren cot Tenkhoa trong bang Khoa
ALTER TABLE Khoa ALTER COLUMN tenkhoa VARCHAR(30) NOT NULL
--Cau 1.7:Tao rang buoc Not null tren cot diemthi
ALTER TABLE Diemthi ALTER COLUMN Diemthi INT NOT NULL
--Cau 1.8:Tao rang buoc de cot Diem thi chi co gia tri tu 0-10
ALTER TABLE Diemthi ADD CONSTRAINT C_diemthi CHECK(diemthi BETWEEN 0 AND 10)
--Cau 1.9:Tao rang buoc de cot nam sinh trong bang Sinhvien chi co the nhan gia tri tu 1/1/1999 den 30/12/2005
ALTER TABLE Sinhvien ADD CONSTRAINT C_namsinh CHECK(namsinh BETWEEN '1/1/1999' AND '30/12/2005')
--Cau 1.10:Them cot ghi chu vao bang KHoa
ALTER TABLE Khoa ADD ghichu NVARCHAR(30)
--cau 1.11:Tao quan he 1-n tu bang Khoa sang bang Sinhvien
ALTER TABLE Sinhvien ADD CONSTRAINT f_makhoa FOREIGN KEY(makhoa) REFERENCES khoa(makhoa) ON DELETE CASCADE ON UPDATE CASCADE
--Cau 1.12:Tao quan he 1-n tu bang sinhvien sang bang Diemthi
ALTER TABLE Diemthi ADD CONSTRAINT f_masv FOREIGN KEY(MaSV) REFERENCES Sinhvien(MaSV) ON DELETE CASCADE ON UPDATE CASCADE
--Cau 1.13:Tao quan he 1-n tu bang mon hoc snag bang Diemthi
ALTER TABLE Diemthi ADD CONSTRAINT f_mamon FOREIGN KEY(mamon) REFERENCES monhoc(mamon) ON DELETE CASCADE ON UPDATE CASCADE
--Cau 1.14:Chen mot so ban ghi vao cac bang tren
--Chen vao bang Khoa
INSERT INTO Khoa VALUES ('KH001','CNTT',N'Công nghệ thông tin')
INSERT INTO Khoa VALUES ('KH002','Khoa May',N'Khoa may')
INSERT INTO Khoa VALUES ('KH003','Khoa DT',N'Khoa điện tử')
--Chen vao bang SInhvien
INSERT INTO sinhvien VALUES('SV001',N'Vũ Tùng Lâm','Hưng Yên','23/06/1999','KH001','2006-2009')
INSERT INTO sinhvien VALUES('SV002',N'Hà Ngọc Hùng','Hải Dương','09/06/2000','KH003','2007-2010')
INSERT INTO sinhvien VALUES('SV003',N'Nguyễn Thị Thanh Hoài','Hải Phòng','12/08/2001','KH002','2008-2011')
--Chen vao bang Monhoc
INSERT INTO Monhoc VALUES('MH001',N'Toán cao cấp')
INSERT INTO Monhoc VALUES('MH002','WindowsForm')
INSERT INTO Monhoc VALUES('MH003',N'Công nghệ.Net')
INSERT INTO Monhoc VALUES('MH005','Java')
SELECT * FROM sinhvien
--Chen vao bang Diem thi
INSERT INTO Diemthi VALUES('Mh001','SV001',9)
INSERT INTO Diemthi VALUES('Mh001','SV002',10)
INSERT INTO Diemthi VALUES('Mh002','SV003',9.5)
INSERT INTO Diemthi VALUES('Mh003','SV003',8)
INSERT INTO Diemthi VALUES('Mh005','SV002',4.5)
--Cau 1.15:Cho biet SV bi thi lai mon Java cho biet ma mon Java la 5
SELECT masv FROM diemthi WHERE mamon='MH005' AND diemthi <5
--Cau 1.16:Cho biet diem mon Java cua SV Ha Ngoc Hung
SELECT masv,diemthi FROM Diemthi
WHERE mamon=( SELECT mamon FROM monhoc WHERE tenmon='Java')
AND masv IN(SELECT masv FROM sinhvien WHERE hoten=N'Hà Ngọc Hùng')
--Cau 1.17:Cho biet so luong sinh vien khoa CNTT biet rang khoa nay co ma la KH001
SELECT makhoa,COUNT(*) [Số lượng sinh viên]
FROM sinhvien WHERE makhoa='KH001'
GROUP BY makhoa
--Cau 1.18:Huy bo diem thi mon Toán cao cấp cua sv co ma la SV001
DELETE FROM diemthi WHERE masv='SV001' AND mamon=(SELECT mamon FROM monhoc WHERE tenmon=N'Toán cao cấp')
--Cau 1.19:Sua lai diem Mon Java cua sinh vien co ma SV002 thanh 8
UPDATE diemthi SET diemthi=8 WHERE masv='SV002' AND mamon=(SELECT mamon FROM monhoc WHERE tenmon=N'Java')
--Cau 1.20:Xoa rang buoc chi muc tren cot Hoten
DROP INDEX i_hoten ON Sinhvien
--Cau 1.21:Xoa rang buoc tu bang Khoa sang bang Sinhvien
ALTER TABLE sinhvien DROP CONSTRAINT f_makhoa
========================
--Chuong 2:Truy van Nang cao
-- Phan I:
--Tao CSDL QLD
CREATE DATABASE QLD
USE QLD
--Tao cau truc bang Sinhvien
CREATE TABLE Sinhvien(
masv VARCHAR(10) PRIMARY KEY,
hoten NVARCHAR(30),
diachi NVARCHAR(30))
--Tao bang Monhoc
CREATE TABLE monhoc(
mamon VARCHAR(10) PRIMARY KEY,
tenmon NVARCHAR(30))
--Tao bang Diem
CREATE TABLE Diem(
mamon VARCHAR(10) CONSTRAINT f_mamon FOREIGN KEY REFERENCES monhoc(mamon) ON DELETE CASCADE ON UPDATE CASCADE,
masv VARCHAR(10)CONSTRAINT f_masv FOREIGN KEY REFERENCES Sinhvien(masv) ON DELETE CASCADE ON UPDATE CASCADE,
diem FLOAT,
CONSTRAINT p_mamon_masv PRIMARY KEY(mamon,masv))
--Cau 2.1:Cho biet danh sach sinh vien co mot trong cac dia chi sau:'Hưng Yên','Hải Dương','Nghệ an'
SELECT * FROM sinhvien WHERE diachi IN(N'Hưng Yên',N'hải Dương',N'Nghệ an')
--Cau 2.2:Cho biet danh sach SV trong nhom top 10 co diem toancaocap(ma 001) cao nhat
SELECT TOP(10) * FROM sinhvien WHERE masv IN(SELECT masv FROM diem where mamon='Mh001')
--Cau 2.3:Cho biet danh sach SV bi truot mon tinhoc(ma=002)
SELECT * FROM Sinhvien WHERE masv IN(SELECT masv FROM diem WHERE mamon='Mh002' AND diem<5)
--Cau 2.4:Cho biet danh sach sinh vien bi truot it nhat mot mon
SELECT * FROM sinhvien WHERE masv IN(SELECT masv FROM diem WHERE diem<5)
--Cau 2.5:Cho biet danh sach sv bi truot tat ca cac mon
--Co nghia la tat ca cac mon deu co diem <5
SELECT masv,COUNT(*)[So mon truot] FROM Diem d
WHERE diem<5 GROUP BY masv
HAVING COUNT(*)=(SELECT COUNT(*) FROM dbo.monhoc)
--Cau 2.6:Cho biet danh sach sv da thi lai tat ca cac mon
--Co nghia la tat ca cac mon deu co diem >5
SELECT masv FROM diem d
WHERE diem>5 GROUP BY masv
HAVING COUNT(*)=(SELECT COUNT(*) FROM dbo.monhoc)
--Cau 2.7:Cho biet danh sach sv chua thi tat ca cac mon
--co nghia la chua co diem cua bat ki mon nao
SELECT masv FROM sinhvien WHERE masv NOT IN(SELECT masv FROM dbo.Diem)
--Cau 2.8:Cho biet danh sach sv da co diem cua tat ca cac mon
SELECT d.masv,COUNT(*)[Số lượng môn học] FROM diem d
GROUP BY d.masv
HAVING COUNT(*) =(SELECT COUNT(*) FROM dbo.monhoc)
SELECT * FROM diem
SELECT * FROM monhoc
SELECT * FROM sinhvien
--Phan II:QUAN LY BAN HANG
--Tao CSDL QLBH
CREATE DATABASE QLBH
USE QLBH
--Tao bang DM_Nhanvien
CREATE TABLE DM_nhanvien(
manv CHAR(4) PRIMARY KEY,
tennv NVARCHAR(30),
chucvu NVARCHAR(20))
--Tao bang DM_LoaiphieuXuatNhap
CREATE TABLE DM_LoaiphieuXuatNhap(
maloai CHAR(4) PRIMARY KEY,
tenloai NVARCHAR(20),
ghichu NVARCHAR(20))
--Tao bang DM_hangHoa
CREATE TABLE DM_hanghoa(
mahang CHAR(10) PRIMARY KEY,
tenhang NVARCHAR(50),
donvitinh NVARCHAR(50),
dactinh NVARCHAR(200))
--Tao bang DM_nhacungcap
CREATE TABLE DM_nhacungcap(
makh CHAR(4) PRIMARY KEY,
tenKH NVARCHAR(150),
Diachi NVARCHAR(150),
Masothue NVARCHAR(50),
Taikhoan NVARCHAR(100),
Tennganhang NVARCHAR(200),
Ghichu NVARCHAR(200))
--Tao bang DM_hoadon
CREATE TABLE DM_Hoadon(
mahd CHAR(10) PRIMARY KEY,
ngaylaphd DATETIME,
makh CHAR(4)CONSTRAINT f_makh FOREIGN KEY
REFERENCES DM_nhacungcap(makh) ON DELETE CASCADE ON UPDATE CASCADE,
manv CHAR(4)CONSTRAINT f_manv FOREIGN KEY
REFERENCES DM_Nhanvien(manv) ON DELETE CASCADE ON UPDATE CASCADE,
maloai CHAR(4)CONSTRAINT f_maloai FOREIGN KEY
REFERENCES DM_Loaiphieuxuatnhap(maloai) ON DELETE CASCADE ON UPDATE CASCADE,
dongia NVARCHAR(200))
--Tao bang CHitiethoadon
CREATE TABLE Chitiethoadon(
maHD CHAR(10)CONSTRAINT f_mahd FOREIGN KEY
REFERENCES DM_hoadon(mahd) ON DELETE CASCADE ON UPDATE CASCADE,
mahang CHAR(10)CONSTRAINT f_mahang FOREIGN KEY
REFERENCES DM_hanghoa(mahang) ON DELETE CASCADE ON UPDATE CASCADE,
soluong FLOAT,
dongia FLOAT,
Chietkhau FLOAT,
CONSTRAINT p_mahd_mahang PRIMARY KEY(mahd,mahang))
--Cau 1:Liet ke KH_NCC co dia chi o HN
SELECT * FROM DM_nhacungcap WHERE diachi=N'Hà nội'
--Cau 2:Liet ke hoa don ban trong thang 4/2008
SET DATEFORMAT dmy
SELECT * FROM DM_Hoadon
WHERE ngaylaphd BETWEEN '1/4/2008' AND '30/4/2008'
--Cau 3:Liet ke hoa don do nhan vien Nguyen Thi A phu trach lap trong 4/2008
SELECT * FROM DM_hoadon
WHERE manv=(SELECT manv FROM DM_nhanvien WHERE tennv=N'Nguyen Thi A')
AND ngaylaphd BETWEEN '1/4/2008' AND '30/4/2008'
--Cau 4:Lap bao cao tong hop mat hang xuat tu dau nam toi nay
SELECT c.mahang,SUM(soluong)[Số lượng] FROM chitiethoadon c,DM_hoadon h
WHERE c.mahd=h.mahd AND YEAR(ngaylaphd)=YEAR(GETDATE())
GROUP BY c.mahang
--Cau 5:Liet ke 10 mat hang ban chay nhat trong 4/2008
SELECT TOP(10) h.mahang,SUM(soluong)[Số lượng] FROM DM_hanghoa h,chitiethoadon c
WHERE c.mahang=h.mahang
GROUP BY h.mahang
ORDER BY [Số lượng] DESC
--Cau 6:Thong ke doanh thu ban hang cua tung nhan vien trong 4/2008
--Doanh thu= SL*DG-SL*DG*CK
SELECT manv,c.soluong*c.dongia-c.soluong*c.dongia*chietkhau AS [Doanh thu] FROM chitiethoadon c,DM_Hoadon h
WHERE c.mahd=h.mahd AND ngaylaphd BETWEEN '1/4/2008' AND '30/4/2008'
GROUP BY manv
--Cau 7:Bao cao tong hop nhat xuat ton trong 4/2008
--Cau 8:Liet ke mat hang khong co nguoi mua trong thang 4/2008
SELECT * FROM DM_hanghoa
WHERE mahang NOT IN (SELECT mahang FROM chitiethoadon
WHERE mahd IN(SELECT mahd FROM DM_Hoadon
WHERE ngaylaphd BETWEEN '1/4/2008' AND '30/4/2008'))
--Cau 9:Bao cao tong hop ket qua kinh doanh trong 4/2008
--Cau 10:Liet ke mat hang ban chay nhat
SELECT TOP 1 WITH ties c.mahang,SUM(soluong)[Số lượng hàng bán]
FROM chitiethoadon c
GROUP BY c.mahang
ORDER BY 2
--PHAN III:QUAN LY NHAN SU
--Tao CSDL QLNS
CREATE DATABASE QLNS
USE QLNS
--Tao bang DMDonvi
CREATE TABLE DMDonvi(
madonvi CHAR(4) PRIMARY KEY,
tendonvi NVARCHAR(100))
--Tao bang HSCB
CREATE TABLE HSCB(
Macb CHAR(4) PRIMARY KEY,
hodem NVARCHAR(50),
ten NVARCHAR(20),
ngaysinh SMALLDATETIME,
gioitinh BIT,
Maque CHAR(4),
Ngayvaodang SMALLDATETIME,
Ngaynhapngu SMALLDATETIME,
Ngayxuatngu SMALLDATETIME)
--Tao bang DMChucvu
CREATE TABLE DMChucvu(
machucvu CHAR(4) PRIMARY KEY,
tenchucvu NVARCHAR(100))
--Tao bang QTCongtac
CREATE TABLE QTCongtac(
macb CHAR(4) CONSTRAINT f_macb FOREIGN KEY REFERENCES HSCB(macb) ON DELETE CASCADE ON UPDATE CASCADE,
madonvi CHAR(4) CONSTRAINT f_madvi FOREIGN KEY REFERENCES DMDonvi(madonvi) ON DELETE CASCADE ON UPDATE CASCADE,
Machucvu CHAR(4)CONSTRAINT f_macv FOREIGN KEY REFERENCES DMChucvu(machucvu) ON DELETE CASCADE ON UPDATE CASCADE,
Tungay SMALLDATETIME,
toingay SMALLDATETIME)
--Cau 1:Tim nhung CB nhap ngu tu nam 1960-1965 que O Ha Tay
SELECT * FROM HSCB
WHERE maque=N'Ha Tay' AND YEAR(ngaynhapngu) BETWEEN '1960' AND '1965'
--Cau 2:Liet ke danh sach can bo den tuoi cho nghi huu(nam>=55,nu >=50)
SELECT *,DATEDIFF(yy,ngaysinh,GETDATE()) [Tuổi] FROM HSCB
WHERE gioitinh=0 OR gioitinh=1
--Cau 3:Thong ke sy so cua tung don vi tinh den thoi diem hien tai
SELECT madonvi,COUNT(*)[Số lượng cán bộ] FROM QTcongtac
WHERE tungay<GETDATE() AND toingay<GETDATE()
GROUP BY madonvi
--Cau 4:Liet ke qua trinh cong tac cua CB Nguyen Van A, sap xep thoi thoi gian tang dan
SELECT * FROM QTCongtac
WHERE macb=(SELECT macb FROM HSCB
WHERE hodem=N'Nguyen Van' AND ten='A'
ORDER BY tungay ASC
--Cau 5:Liet ke Cb da hoac dang dam nhiem chuc Pho Phong
SELECT Macb FROM QTCongtac
WHERE YEAR(toingay)<=YEAR(GETDATE()) AND machucvu=(SELECT machucvu FROM DMchucvu
WHERE tenchucvu=N'Phó Phòng')
--Cau 6:Liet ke CB da dam nhiem chuc truong phong
SELECT Macb FROM QTCongtac
WHERE YEAR(denngay)<YEAR(GETDATE()) AND machucvu=(SELECT machucvu FROM DMchucvu
WHERE tenchucvu=N'Trưởng Phòng')
--Cau 7:Liet ke CB co so lan luan chuyen nhieu nhat
--Cau 8:Liet ke trong khoang thoi gian tu nam 2005 toi nay
--tai phong bao dao co bao nhieu CB duoc chuyen den va chuyen di
--Cau 9:Hien thi thong tin ve don vi,Cb tuong ung cua DV do
--va thong ke so luong nhan vien tai moi don vi
SELECT q.madonvi,q.macb,COUNT(*) [Số lượng nhân viên]FROM DMDOnvi d,HSCB h,QTCongtac q
WHERE d.madonvi=q.madonvi AND h.macb=q.macb
GROUP BY q.madonvi,q.maCb
--Cau 10:Xep hang can bo theo do tuoi
SELECT MACB,TEN,NGAYSINH,RANK() OVER(PARTITION BY macb ORDER BY DATEDIFF(yy,ngaysinh,GETDATE())) [Phân hạng]FROM dbo.HSCB
======================
--Chuong III:VIEW
--Tao CSDL QLDT
CREATE DATABASE QLDT
USE QLDT
--Tao bang Sinhvien
CREATE TABLE Sinhvien(
masv CHAR(10) PRIMARY KEY,
hoten NVARCHAR(30),
diachi NVARCHAR(30),
lop CHAR(10))
--Tao bang Detai
CREATE TABLE detai(
madt CHAR(10) PRIMARY KEY,
tendt NVARCHAR(30))
--Tao bang Sinhvien_detai
CREATE TABLE Sinhvien_detai(
masv CHAR(10) CONSTRAINT f_masv FOREIGN KEY REFERENCES sinhvien(MaSV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10)CONSTRAINT f_madt FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT p_masv_madt PRIMARY KEY(masv,madt))
--Tao bang GiaoVien
CREATE TABLE Giaovien(
magv CHAR(10) PRIMARY KEY,
hoten NVARCHAR(30),
diachi NVARCHAR(30))
--Tao bang GiaoVien_Detai
CREATE TABLE GiaoVien_Detai(
magv CHAR(10) CONSTRAINT f_magv FOREIGN KEY REFERENCES giaovien(MagV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10) CONSTRAINT f_madt2 FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT p_magv_madt PRIMARY KEY(magv,madt))
--Tao bang Ketqua
CREATE TABLE Ketqua(
masv CHAR(10) CONSTRAINT f_masv2 FOREIGN KEY REFERENCES sinhvien(MaSV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10) CONSTRAINT f_madt3 FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
diem FLOAT)
--Cau 3.1:Ung voi moi giao vien cho biet ten cac dt huong dan
CREATE VIEW VIEW_1
AS SELECT * FROM Giaovien_detai
--Cau 3.2:Cho biet cac SV co ket qua bao ve thuoc loai Kha(diem>7)
CREATE VIEW view_2
AS SELECT * FROM ketqua WHERE diem>7
--Cau 3.3:Cho biet ten GVHD cac de tai co kq thuoc loai KHA'
CREATE VIEW view_3
AS SELECT g.Hoten,gd.madt,diem FROM ketqua k,giaovien g,giaovien_detai gd,sinhvien_detai sd
WHERE k.masv=sd.masv AND sd.madt=gd.madt AND g.magv=gd.magv AND diem>7
--Cau 3.4:Ung voi moi giao vien cho biet so luong dt huong dan
CREATE VIEW view_4
AS SELECT magv,COUNT(*)[Số lượng đề tài] FROM Giaovien_detai GROUP BY magv
--Cau 3.5:Ung voi moi de tai cho biet so luong SV tham gia
CREATE VIEW view_5
AS SELECT madt,COUNT(*)[Số lượng SV tham gia]FROM sinhvien_detai GROUP BY madt
--Cau 3.6:Ung voi moi SV cho biet cac dt ma SV do tham gia
CREATE VIEW view_6
AS SELECT tendt FROM detai d,sinhvien_detai sd
WHERE d.madt=sd.madt
GROUP BY sd.masv,tendt
--Cau 3.7:Cho biet ten GV khong tham gia HD de tai nao
CREATE VIEW view_7
AS SELECT hoten FROM giaovien WHERE magv NOT IN (SELECT magv FROM giaovien_detai)
--Cau 3.8:Cho biet ten dt ma khong co sv nao tham gia
CREATE VIEW view_8
AS SELECT tendt FROM detai WHERE madt NOT IN(SELECT madt FROM sinhvien_detai)
--Cau 3.9:Cho biet danh sach de tai cung voi SL SV tham gia lam de tai
CREATE VIEW view_9
AS SELECT madt,COUNT(*)[Số lượng SV tham gia]FROM sinhvien_detai GROUP BY madt
--Cau 3.10:Cho biet danh sach GV voi so luong SV ma GV do HD
CREATE VIEW view_10
AS SELECT magv,COUNT(*) [Số lượng SV HD]FROM sinhvien_detai sd,giaovien_detai gd
WHERE sd.madt=gd.madt
GROUP BY magv
==========================
--Chuong IV:CHI MUC
--PHAN I:Tao CSDL SINHVIEN
CREATE DATABASE Sinhvien
USE Sinhvien
--Tao bang SV:
CREATE TABLE sinhvien(
masv VARCHAR(15) NOT NULL,
hoten NVARCHAR(50),
que NVARCHAR(100),
Ngaysinh DATETIME,
soCMT VARCHAR(15))
--Cau 4.1:Tao khoa chinh tren cot Masv
ALTER TABLE Sinhvien ADD CONSTRAINT p_masv PRIMARY KEY(masv)
--Cau 4.2:tao chi muc Nonclustered tren cot Hoten
CREATE NONCLUSTERED INDEX ht_clus ON sinhvien(hoten)
--Cau 4.3:tao chi muc Nonclustered tren cot Que
CREATE NONCLUSTERED INDEX que_clus ON sinhvien(que)
--Cau 4.4:Tao chi muc NONCLUSTERED phuc hop tren 2 cot Hoten va Que
CREATE NONCLUSTERED INDEX ht_que_clus ON sinhvien(hoten,que)
--Cau 4.5:Tao chi muc duy nhat Unique tren cot SoCMT
CREATE UNIQUE INDEX Socmt_clus ON sinhvien(soCMT)
--Cau 4.6:Xem lai cac chi muc dang co trong bang Sinhvien
EXEC sys.sp_helpindex sinhvien
--Cau 4.7:Tim tat ca cac sinh vien co ten Nguyen van A = cach sd chi muc o cau 2
SELECT * FROM sinhvien WITH (INDEX=ht_clus)
WHERE hoten=N'Nguyen Van A'
--Cau 4.8:Tim tat ca sinh vien co que o HY = cach sd chi muc da tao o cau 3
SELECT * FROM sinhvien WITH (INDEX=que_clus)
WHERE que=N'Hưng Yên'
--Cau 4.9:Tim tat ca SV co ten bat dau la 'Nguyen Van' va co que O HY = cach sd chi muc o cau 4
SELECT * FROM sinhvien WITH (INDEX=ht_clus,INDEX=que_clus)
WHERE hoten LIKE N'Nguyen Van %' AND que=N'Hưng Yên'
--Cau 4.10:Xoa cac chi muc da tao tu cau 2-4
DROP INDEX sinhvien.ht_clus
DROP INDEX sinhvien.que_clus
DROP INDEX sinhvien.ht_que_clus
--Cau 4.11:Xóa ràng buộc khóa chính khỏi bảng Sinhvien
ALTER TABLE sinhvien DROP CONSTRAINT P_masv
--Cau 4.12:Tao lai khoa chinh trong bang SV sd Nonclustered
--de SQL khong tao chi muc clustered tren chi muc khoa chinh
ALTER TABLE sinhvien ADD CONSTRAINT p_masv PRIMARY KEY(masv) NONCLUSTERED
--Cau 4.13:Tao chi muc Clustered va Unique tren cot SoCMT voi he so dien day = 60
CREATE UNIQUE CLUSTERED INDEX clus_socmt ON sinhvien(soCmt) WITH (FILLFACTOR =60)
--Cau 4.14:Tao chi muc FullText tren cot que va ung dung tim kiem tren cot nay=fulltext
CREATE FULLTEXT CATALOG que_cat WITH
--PHAN II:CSDL QLTV
--Tao CSDL moi QLTV
CREATE DATABASE QLTV
USE QLTV
--Tao bang Tac gia
CREATE TABLE Tacgia(
matg VARCHAR(10)PRIMARY KEY,
tentg NVARCHAR(50),
DClienhe NVARCHAR(50))
--Tao bang SachTG
CREATE TABLE sachTG(
masach VARCHAR(10)NOT NULL,
matg VARCHAR(10) NOT NULL,
CONSTRAINT p_masach_matg PRIMARY KEY(masach,matg))
--Tao bang Sach
CREATE TABLE sach(
masach VARCHAR(10) PRIMARY key,
tensach nvarchar(30),
Sotrang smallint,
matg varchar(10)CONSTRAINT f_matg FOREIGN KEY REFERENCES tacgia(matg) ON DELETE CASCADE ON UPDATE CASCADE,
namxb int,
tukhoa varchar(10),
NXB varchar(10))
--Tao bang Chi tiet sach
CREATE TABLE chitietsach(
masach VARCHAR(10)NOT NULL,
maCB VARCHAR(10) PRIMARY KEY,
tinhtrangsach TINYINT,
tinhtrangPV TINYINT)
--Tao bang Docgia
CREATE TABLE Docgia(
sothe VARCHAR(10) PRIMARY KEY NOT NULL,
Hoten NVARCHAR(30),
DVCT NVARCHAR(30))
--Tao bang Sachmuon
CREATE TABLE sachmuon(
sothe VARCHAR(10)NOT NULL,
MACB VARCHAR(10)CONSTRAINT f_macb FOREIGN KEY REFERENCES chitietsach(macb) ON DELETE CASCADE ON UPDATE CASCADE,
ngaymuon DATETIME,
ngayphaitra DATETIME,
ngaytra DATETIME,
CONSTRAINT p_sothe_macb PRIMARY KEY(sothe,macb))
--Cau 4.15:Hay tao chi muc sao cho cac thao tac tim kiem,cap nhat
--toi uu biet rang ra thuong xuyen tim kiem sach theo:
--Tua de sach,tentg,tu khoa,nxb hoac ket hop tat ca
--Ta tim kiem doc gia theo so the,hoten
//Chua lam dc
--Cau 4.16:Tao rang buoc cho cac bang :SachTG,chitietsach va sachmuon
ALTER TABLE sachTG ADD CONSTRAINT f_matg_1 FOREIGN KEY
REFERENCES tacgia(matg) ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE SachTG ADD CONSTRAINT f_masach FOREIGN KEY
REFERENCES sach(masach) ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE Chitietsach ADD CONSTRAINT f_masach_1 FOREIGN KEY
REFERENCES sach(masach) ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE Sachmuon ADD CONSTRAINT f_sothe FOREIGN KEY
REFERENCES docgia(sothe) ON DELETE CASCADE ON UPDATE CASCADE
--Cau 4.17:Tao cac rang buoc cho bang SACH nhu sau:
--Truong sotrang >0
ALTER TABLE sach ADD CONSTRAINT c_sotrang CHECK(sotrang>0)
--Truong NXB phai nho hon nam hien tai
ALTER TABLE sach ADD CONSTRAINT c_nxb CHECK(nxb <GETDATE())
--Tao rang buoc rong bang SachMuon
--Truong ngayPhaiTra>NgayMuon
ALTER TABLE sachmuon ADD CONSTRAINT c_ngatra_muon CHECK(ngayphaitra>=ngaymuon)
--Trong truong NgayTra>=NgayMuon
ALTER TABLE sachmuon ADD CONSTRAINT c_ngatra_ngaymuon CHECK(ngaytra>=ngaymuon)
======================
--Chuong V:STORE PROCEDURE
--Tao CSDL QuanLyDiem
CREATE DATABASE QuanLyDiem
USE QuanLyDiem
--Tao bang Sinhvien
CREATE TABLE Sinhvien(
masv CHAR(10) PRIMARY KEY,
hoten NVARCHAR(30),
diachi NVARCHAR(30),
lop CHAR(10))
--Tao bang Detai
CREATE TABLE detai(
madt CHAR(10) PRIMARY KEY,
tendt NVARCHAR(30))
--Tao bang Sinhvien_detai
CREATE TABLE Sinhvien_detai(
masv CHAR(10) CONSTRAINT f_masv FOREIGN KEY REFERENCES sinhvien(MaSV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10)CONSTRAINT f_madt FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT p_masv_madt PRIMARY KEY(masv,madt))
--Tao bang GiaoVien
CREATE TABLE Giaovien(
magv CHAR(10) PRIMARY KEY,
hoten NVARCHAR(30),
diachi NVARCHAR(30))
--Tao bang GiaoVien_Detai
CREATE TABLE GiaoVien_Detai(
magv CHAR(10) CONSTRAINT f_magv FOREIGN KEY REFERENCES giaovien(MagV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10) CONSTRAINT f_madt2 FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT p_magv_madt PRIMARY KEY(magv,madt))
--Tao bang Ketqua
CREATE TABLE Ketqua(
masv CHAR(10) CONSTRAINT f_masv2 FOREIGN KEY REFERENCES sinhvien(MaSV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10) CONSTRAINT f_madt3 FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
diem FLOAT)
--PHAN I:THU TUC LUU TRU VOI THAM SO TRUYEN VAO
--Cau 5.1:Viet thu tuc luu tru co thu tuc la xau ki tu,thu tuc nay se tra ve
--de tai co ma tuong ung voi tham so cua de tai do
CREATE PROC XemTTDT
@madt varchar(10)
as SELECT * FROM sinhvien_detai sd,giaovien_detai gd WHERE sd.madt=gd.madt AND sd.madt=@madt
EXEC XemTTDT 'DT001'
--Cau 5.2:Viet TTLT co tham so la 1 so nguyen,thu tuc nay tra ve cac Sinhvien co ket qua
--bao ve da tai nho hon gia tri tham so truyen vao
ALTER PROC XemDiem
@diem float
AS SELECT masv,diem FROM ketqua WHERE diem<@diem
EXEC xemdiem 7
--Cau 5.3:Viet TTLT co tham so la 1 xau ki tu,TT nay se tra ve so luong cac de tai
--huong dan cua giao vien co ma = tham so truyen vao
CREATE PROC Sodetai
@magv varchar(10)
AS SELECT magv,COUNT(*)[Số lượng ĐT hướng dẫn] FROM Giaovien_detai GROUP BY magv
EXEC sodetai 'GV001'
--Cau 5.4:Viet TTLT co tham so la 1 xau ki tu,TT nay se tra ve so luong sinhvien tham gia
--de tai co ma de tai = tham so truyen vao
CREATE PROC SoLuongSV
@madt varchar(10)
AS SELECT madt,COUNT(*)[Số lượng SV Tham gia]FROM sinhvien_detai WHERE madt=@madt GROUP BY madt
EXEC SoluongSV 'DT001'
--Cau 5.5:Hay them cot Xeploai vao bang Ketqua, viet TTLT de cap nhat xep loai
--theo cong thuc de tai da cho:
ALTER TABLE ketqua ADD Xeploai nvarchar(30)
CREATE PROC Xeploai
AS
UPDATE Ketqua SET Xeploai=CASE
WHEN diem< 5 THEN N'Yếu'
WHEN diem BETWEEN 5 AND 6 THEN N'Trung Bình'
WHEN diem >=7 AND diem <8 THEN N'Khá'
WHEN diem >8 THEN N'Giỏi'
END
--Cau 5.6:Hay bo xung them cot so luong vao bang Detai ,viet TTLT de cap nhat cot
--so luong cua bang DT=SL SV tham gia dt do
ALTER TABLE detai ADD Soluong INT
CREATE proc SetSL
AS
DECLARE @Sl INT,
--DECLARE @madt VARCHAR(10)
SELECT madt,@sl=COUNT(*) FROM Sinhvien_detai GROUP BY madt
UPDATE detai SET soluog=@sl WHERE madt=
END
--PHAN II:THU TUC LUU TRU VOI THAM SO TRUYEN RA
--Cau 5.7:Dua vao ten giao vien va tra ra so GV co cung ten,neu ko co thi tra ve 0
ALTER PROC SLGV
@sl int OUTPUT,
@tengv nvarchar(30)
AS
IF EXISTS(SELECT hoten FROM giaovien WHERE hoten=@tengv)
BEGIN
SELECT @sl=COUNT(*)FROM giaovien WHERE hoten=N'@tengv' GROUP BY hoten
END
ELSE
PRINT '0'
END
DECLARE @sluong INT
SET @sluong=0
EXEC SLGV 'ANh',@Sluong OUTPUT SELECT @sluong
--Cau 5.8:Dua vao ma dt cho biet diem cua Ma dt do,neu khong tim thay dt tuong ung thi tra ve -1
CREATE PROC DiemDT
@madt varchar(10),
@diem float output
AS
IF EXISTS (SELECT madt FROM ketqua WHERE madt=@madt)
BEGIN
DECLARE @luudiem FLOAT
SELECT @luudiem=diem FROM ketqua WHERE madt=@madt
END
ELSE
PRINT '-1'
END
DECLARE @a FLOAT
SET @a=0
EXEC DiemDT 'DT001',@a float OUTPUT SELECT @a
==========================
--Chuong VI:Trigger
--PHAN I:Tao CSDL QLHV
CREATE DATABASE QLHV
USE QLHV
--Tao bang Sinhvien
CREATE TABLE Sinhvien(
masv CHAR(10) PRIMARY KEY,
hoten NVARCHAR(30),
diachi NVARCHAR(30),
lop CHAR(10))
--Tao bang HocVi
CREATE TABLE Hocvi(
mahv CHAR(10) PRIMARY KEY,
tenhv NVARCHAR(30))
--Tao bang Detai
CREATE TABLE detai(
madt CHAR(10) PRIMARY KEY,
tendt NVARCHAR(30))
--Tao bang Sinhvien_detai
CREATE TABLE Sinhvien_detai(
masv CHAR(10) CONSTRAINT f_masv FOREIGN KEY REFERENCES sinhvien(MaSV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10)CONSTRAINT f_madt FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT p_masv_madt PRIMARY KEY(masv,madt))
--Tao bang GiaoVien
CREATE TABLE Giaovien(
magv CHAR(10) PRIMARY KEY,
hoten NVARCHAR(30),
diachi NVARCHAR(30),
mahv CHAR(10) CONSTRAINT f_hv FOREIGN KEY REFERENCES Hocvi(mahv) ON DELETE CASCADE ON UPDATE CASCADE)
--Tao bang GiaoVien_Detai
CREATE TABLE GiaoVien_Detai(
magv CHAR(10) CONSTRAINT f_magv FOREIGN KEY REFERENCES giaovien(MagV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10) CONSTRAINT f_madt2 FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT p_magv_madt PRIMARY KEY(magv,madt))
--Tao bang Ketqua
CREATE TABLE Ketqua(
masv CHAR(10) CONSTRAINT f_masv2 FOREIGN KEY REFERENCES sinhvien(MaSV)ON UPDATE CASCADE ON DELETE CASCADE,
madt CHAR(10) CONSTRAINT f_madt3 FOREIGN KEY REFERENCES detai(Madt)ON UPDATE CASCADE ON DELETE CASCADE,
diem FLOAT)
--Cau 6.1:Viet Trigger khong cho phep xoa hoc vi khi van dang co GV co HV do
CREATE TRIGGER X_HV ON Hocvi FOR DELETE AS
IF EXISTS(SELECT d.mahv FROM Giaovien g,DELETED d
WHERE g.mahv=d.mahv)
BEGIN
ROLLBACK TRANSACTION
PRINT 'Ma HV dang bi tham chieu'
END
ELSE
PRINT 'Xoa Thanh Cong'
--Cau 6.2:Viet trigger de khi xoa 1 Sv thi xoa tat ca cac de tai ma SV do tham gia
CREATE TRIGGER X_SV ON SInhvien FOR DELETE AS
DECLARE @masv CHAR(10)
IF EXISTS(SELECT masv FROM DELETED WHERE masv IN (SELECT masv FROM dbo.Sinhvien_detai))
DELETE FROM dbo.Sinhvien_detai WHERE masv IN (SELECT masv FROM DELETED)
END
/*Cau 6.3:Viet trigger de thiet lap rang buoc:
neu gv co hv la Tiensi thi co the HD toi da 4 dt
neu gv co hv la Thac si thi co the HD toi da 3 dt
neu gv co hv la Ky su thi co the HD toi da 2 dt
neu Gv co hv khac thi ko dc hd*/
CREATE TRIGGER Ktra ON giaovien_detai FOR INSERT AS
DECLARE @magv CHAR(10)
DECLARE @tenhv NVARCHAR(30)
DECLARE @SL INT
SET @magv=(SELECT magv FROM Inserted)
SET @tenhv=(SELECT tenhv FROM dbo.Hocvi WHERE mahv IN(SELECT mahv FROM dbo.Giaovien WHERE magv=@magv))
SET @sl=(SELECT COUNT(*) FROM dbo.GiaoVien_Detai WHERE magv=@magv)
IF(@tenhv !=N'Tien si' AND @tenhv !=N'Thac si' AND @tenhv != N'Ky su')
BEGIN
ROLLBACK TRANSACTION
PRINT 'Gv nay khong duoc HD detai nao'
END
ELSE
IF(@tenhv=N'Tien si' AND @SL >4)
BEGIN
ROLLBACK TRANSACTION
PRINT 'GV nay chi duoc HD toi da 4 DT'
END
ELSE
IF(@tenhv=N'Tien si' AND @SL >3)
BEGIN
ROLLBACK TRANSACTION
PRINT 'GV nay chi duoc HD toi da 3 DT'
END
ELSE
IF(@tenhv=N'Tien si' AND @SL >2)
BEGIN
ROLLBACK TRANSACTION
PRINT 'GV nay chi duoc HD toi da 2 DT'
END
--Cau 6.4:Viet trigger de khi thay doi Ma GV thi thay doi magv trong cac bang lien quan
CREATE TRIGGER Update_gv ON giaovien FOR UPDATE AS
UPDATE dbo.GiaoVien_Detai
SET magv=(SELECT magv FROM INSERTED
WHERE magv =(SELECT magv FROM deleted))
--Cau 6.5:Tao trigger de 1 de tai ko co qua 3 SV tham gia nghien cuu
CREATE TRIGGER SLSV ON sinhvien_detai FOR INSERT AS
IF(SELECT COUNT(*) FROM dbo.Sinhvien_detai sd,INSERTED i
WHERE sd.madt=i.madt)<=3
BEGIN
PRINT 'Chen thanh cong'
END
ELSE
BEGIN
PRINT 'Khong the co qua SV tham gia de tai nay'
ROLLBACK TRANSACTION
END
--PHAN II:CSDL SACH
CREATE DATABASE sach
USE sach
CREATE TABLE khosach(
makho VARCHAR(10) PRIMARY KEY,
tenkho NVARCHAR(30))
CREATE TABLE sach(
masach VARCHAR(10) PRIMARY KEY,
tensach NVARCHAR(30),
NhaXB NVARCHAR(30),
Sotrang INT,
NamXB SMALLDATETIME)
CREATE TABLE sach_cabiet(
masach varchar(10)CONSTRAINT f_masach FOREIGN KEY REFERENCES sach(masach) ON DELETE CASCADE ON UPDATE cascade,
maCB varchar(10),
makho varchar(10)CONSTRAINT f_makho FOREIGN KEY REFERENCES Khosach(makho) ON DELETE CASCADE ON UPDATE cascade,
CONSTRAINT p_ms_mk_mcb PRIMARY KEY(masach,makho,macb))
--cau 6.6:Tao trigger khong cho phep xoa kho khi van con sach trong kho
alter TRIGGER Xoa_kho ON khosach FOR DELETE AS
IF(SELECT COUNT(*) FROM Sach_cabiet s,DELETED d
WHERE s.makho=d.makho)>=1
BEGIN
PRINT 'Khong the xoa do van con sach'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT 'Xoa Thanh cong'
END
--Cau 6.7:Khong cho phep them mot sach moi hay sua 1 sach ma co nam xuat ban nho hon nam hien tai
CREATE TRIGGER insert_update ON sach FOR UPDATE,INSERT AS
IF UPDATE(namxb)
--Cau 6.8:Hai kho co ma khac nhau khong the co ten kho giong nhau
CREATE TRIGGER update_insert ON khosach FOR UPDATE,INSERT AS
DECLARE @makho VARCHAR(10)
DECLARE @tenkho NVARCHAR(30)
SET @makho=(SELECT makho FROM khosach)
SET @tenkho=(SELECT tenkho FROM khosach WHERE makho=@makho)
=====================
--Chuong VII:Quan ly User va Security
--Cau 7.2:Tao 2 CSDL Test Va SACH2 voi cac bang tuy y
CREATE DATABASE Test
USE Test
CREATE TABLE Nhanvien(
manv VARCHAR(10) PRIMARY KEY,
tennv NVARCHAR(30),
hoten NVARCHAR(30),
diachi NVARCHAR(30))
CREATE TABLE PhongBan(
maPB VARCHAR(10) PRIMARY KEY,
tenPB NVARCHAR(30))
CREATE DATABASE sach2
USE sach2
CREATE TABLE Nhanvien(
manv VARCHAR(10) PRIMARY KEY,
tennv NVARCHAR(30),
hoten NVARCHAR(30),
diachi NVARCHAR(30),
maPB VARCHAR(10) CONSTRAINT f_mapb FOREIGN KEY REFERENCES Phongban(mapb))
CREATE TABLE PhongBan(
maPB VARCHAR(10) PRIMARY KEY,
tenPB NVARCHAR(30))
--Cau 7.3:Tao ra cac Tai khoan dang nhap:SV1,Sv2,sv3,sv4,gv1,gv2,gv3,admin1,admin2 voi mat khau tuy y
CREATE LOGIN sv1 WITH PASSWORD ='123'
CREATE LOGIN sv2 WITH PASSWORD ='123'
CREATE LOGIN sv3 WITH PASSWORD ='123'
CREATE LOGIN sv4 WITH PASSWORD ='123'
CREATE LOGIN gv1 WITH PASSWORD ='123'
CREATE LOGIN gv2 WITH PASSWORD ='123'
CREATE LOGIN gv3 WITH PASSWORD ='123'
CREATE LOGIN admin1 WITH PASSWORD ='123'
CREATE LOGIN admin2 WITH PASSWORD ='123'
EXEC sp_addlogin 'admin3','123'
--Cau 7.4:Thiet lap cho tai khoan admin1 va admin2 co tat ca cac quyen
GRANT ALL TO admin1 WITH GRANT OPTION
GRANT ALL TO admin2 WITH GRANT OPTION
CREATE USER admin1 FOR LOGIN admin1;
CREATE USER admin2 FOR LOGIN admin2;
CREATE USER sach2 FOR LOGIN sv1;
Bạn đang đọc truyện trên: AzTruyen.Top