Đọc truyện BTC SQL

♥ Truyen2U.Pro - Tên miền mới của Truyen2U.Net

BTC SQL

Trước Sau

Màu nền

Font chữ

Font size

Chiều cao dòng

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

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

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)

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

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