baitapk8sql
H? và tên :luong nhu thi?n
Ngày sinh:11/09/1990
L?p n03-th2
Môn h? qu?n tr?
-------------------------
Bài 1
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
Bài2
Create database QUANLYNHA1
use QUANLYNHA1
Create table Nha(
MaN nvarchar(10) not null Primary key,
TenChuNha nvarchar(30) not null,
GiaThue int not null,
DiaChiNha nvarchar(30) not null
Create table Khach(
MaK nvarchar(10) not null Primary key,
TenK nvarchar(30) not null,
DiaChi nvarchar(30) not null,
SoDT nvarchar(15)
Create table HopDong(
SoHD nvarchar(10) not null Primary key,
MaN nvarchar(10) not null,
MaK nvarchar(10) not null,
NgayBD smalldatetime not null,
NgayKT smalldatetime 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
Bài 3
create database qlsv
use qlsv
-----------------------
create table lop
( mal char(10) primary key,
tenl varchar(30))
------------------
create table tinh
( mat char(10) primary key,
tent varchar(30))
--------------------
create table sinhvien
( masv char(10) ,
hoten varchar(50),
ngaysinh datetime,
gioitinh char(10),
mal char(10),
mat char(10),
dtb float,
constraint kc primary key (masv),
constraint kn foreign key (mal) references lop(mal),
constraint kn1 foreign key (mat) references tinh(mat))
/*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 in (select mal
from sinhvien
where mat in (select mat
from tinh
where mat not in (select mat
from tinh
where tinh.tent='thainguyen')))
/*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
Bài 4
create database qltt
use qltt
create table sinhvien
( masv char(10) primary key,
hotensv varchar(50),
ns datetime,
diachi varchar(30))
-----------------
create table detai
( madt char(10) primary key,
tendt varchar(30),
gvhd varchar(50))
------------
create table sv_detai
(madt char(10),
masv char(10),
ntt varchar(30),
kq char(10),
constraint kc primary key (ntt),
constraint kn1 foreign key (madt) references detai(madt),
constraint kn2 foreign key (masv) references sinhvien(masv))
--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
Bài 6
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
Bài7
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'
Bài8
CREATE DATABASE QUANLYDIEMTHI
USE QUANLYDIEMTHI
create table sinhvien(
masv nvarchar(10) primary key,
tensv nvarchar(30) not null,
ngaysinh datetime not null,
gioitinh nvarchar(10) not null,
quequan nvarchar(30), )
create table monhoc(
mam nvarchar(10) primary key,
tenm nvarchar(20) not null,
dvht int )
create table diem(
mam nvarchar(10) not null,
masv nvarchar(10) not null,
diemthi float not null,
constraint kc primary key(mam,masv),
constraint kn1 foreign key(mam) references monhoc(mam),
constraint kn2 foreign key(masv) references sinhvien(masv) )
/*2.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN CO DIEM THI CAO NHAT CUA MON HOC CO TEN LA
"CO SO DU LIEU"*/
CREATE VIEW VD2
AS
SELECT * FROM SINHVIEN
WHERE MASV IN (SELECT MASV FROM DIEM
WHERE DIEMTHI IN (SELECT MAX(DIEMTHI) FROM DIEM)
AND MAM IN (SELECT MAM FROM MONHOC
WHERE TENM='CO SO DU LIEU'))
--3.TAO THU TUC CO THAM SO LA @TENM DE DUA RA DANH SACH SINH VIEN PHAI THI LAI MON HOC TREN--
CREATE PROC VD3
@TENM CHAR(20)
AS
SELECT * FROM SINHVIEN
WHERE MASV IN (SELECT MASV FROM DIEM
WHERE DIEMTHI<5 AND MAM IN (SELECT MAM FROM MONHOC
WHERE TENM=@TENM))
EXEC VD3 'SQL'
--4.DUNG KIEU DU LIEU CURSOR DE DUA RA TRUNG BINH DIEM THI CUA TUNG MON HOC--
--KHAI BAO--
DECLARE CS4 CURSOR FOR
SELECT MAM,AVG(DIEMTHI) AS DTB
FROM DIEM
GROUP BY MAM
--MO--
OPEN CS4
--XU LY MAU TIN--
FETCH NEXT FROM CS4
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CS4
END
--DONG--
CLOSE CS4
--HUY--
DEALLOCATE CS4
Bài 9
CREATE DATABASE QUANLYGIAOVIEN
USE QUANLYGIAOVIEN
CREATE TABLE GIAOVIEN(
MAGV CHAR(10) PRIMARY KEY,
TENGV CHAR(40) NOT NULL,
DC CHAR(40),
DT CHAR(15)
CREATE TABLE HOCVI(
MAHV CHAR(10) PRIMARY KEY,
TENHV CHAR(30) NOT NULL
CREATE TABLE CHUYENNGANH(
MACN CHAR(10) PRIMARY KEY,
TENCN CHAR(30) NOT NULL
CREATE TABLE GV_HV_CN(
MAGV CHAR(10),
MAHV CHAR(10),
MACN CHAR(10),
NAM INT,--NAM DAT HOC VI--
CONSTRAINT KC_GV_HV_CN PRIMARY KEY (MAGV,MAHV,MACN),
CONSTRAINT KN_GV_HV_CN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV),
CONSTRAINT KN1_GV_HV_CN FOREIGN KEY (MAHV) REFERENCES HOCVI(MAHV),
CONSTRAINT KN2_GV_HV_CN FOREIGN KEY (MACN) REFERENCES CHUYENNGANH(MACN)
--2.TAO VIEW DE CHO BIET THONG TIN VE CAC GIAO VIEN CO HOC VI LA "TIEN SY"--
CREATE VIEW VD2
AS
SELECT * FROM GIAOVIEN
WHERE MAGV IN (SELECT MAGV FROM GV_HV_CN
WHERE MAHV IN (SELECT MAHV FROM HOCVI
WHERE TENHV='TIEN SY'))
SELECT * FROM VD2
--3.TAO VIEW DE CHO BIET THONG TIN VE CAC GIAO VIEN CO CHUYEN NGANH "KINH TE"--
CREATE VIEW VD3
AS
SELECT * FROM GIAOVIEN
WHERE MAGV IN (SELECT MAGV FROM GV_HV_CN
WHERE MACN IN (SELECT MACN FROM CHUYENNGANH
WHERE TENCN='KINH TE'))
SELECT * FROM VD3
--4.TAO VIEW DE CHO BIET THONG TIN VE CAC GIAO VIEN CO DIA CHI O "THAI NGUYEN"--
CREATE VIEW VD4
AS
SELECT * FROM GIAOVIEN
WHERE DC='THAI NGUYEN'
/*5.TAO THU TUC CO THAM SO VAO LA @NAM DE DUA RA TEN CUA CAC GIAO VIEN NHAN HOC VI "TIEN
SY" VAO NAM TREN*/
ALTER PROC VD5
@NAM INT
AS
SELECT * FROM GIAOVIEN
WHERE MAGV IN (SELECT MAGV FROM GV_HV_CN
WHERE NAM=@NAM AND MAHV IN (SELECT MAHV FROM HOCVI
WHERE TENHV='TIEN SY'))
EXEC VD5 2009
--6.TAO THU TUC NHAP DU LIEU CHO BANG GIAOVIEN--
CREATE PROC VD6
@MAGV CHAR(10),@TENGV CHAR(40),@DC CHAR(40),@DT CHAR(15)
AS
INSERT INTO GIAOVIEN
VALUES(@MAGV,@TENGV,@DC,@DT)
VD6 'GV5','HANG','VINH PHUC','157148'
/*7.TAO THU TUC CO THAM SO VAO LA @TENHV DE DUA RA THONG TIN VE CAC GIAO VIEN CO TEN HOC VI TREN*/
CREATE PROC VD7
@TENHV CHAR(40)
AS
SELECT * FROM GIAOVIEN
WHERE MAGV IN (SELECT MAGV FROM GV_HV_CN
WHERE MAHV IN (SELECT MAHV FROM HOCVI
WHERE TENHV=@TENHV))
EXEC VD7 'THAC SY'
/*8.TAO TRIGGER DE KIEM TRA KHI NHAP DU LIEU VAO BANG GV_HV_CN.NEU NAM 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 GV_HV_CN
FOR INSERT
AS
IF EXISTS(SELECT NAM FROM GV_HV_CN
WHERE NAM<0)
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO GV_HV_CN
VALUES('GV5','HV2','CN2',-2010)
/*9.DUNG KIEU DU LIEU CURSOR DE DUA RA THONG TIN VE TUNG GIAO VIEN CUA TUNG CHUYEN NGANH*/
--KHAI BAO--
DECLARE CS9 CURSOR FOR
SELECT MACN,GIAOVIEN.MAGV,TENGV,DC,DT
FROM GIAOVIEN,GV_HV_CN
WHERE GIAOVIEN.MAGV=GV_HV_CN.MAGV
GROUP BY MACN,GIAOVIEN.MAGV,TENGV,DC,DT
--MO--
OPEN CS9
--XU LY MAU TIN--
DECLARE @B1 CHAR(10),@B2 CHAR(10),@B3 CHAR(30),@B4 CHAR(30),@B5 CHAR(15)
FETCH NEXT FROM CS9
INTO @B1,@B2,@B3,@B4,@B5
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @B1+@B2+@B3+@B4+@B5
FETCH NEXT FROM CS9
INTO @B1,@B2,@B3,@B4,@B5
END
--DONG--
CLOSE CS9
--HUY--
DEALLOCATE CS9
Bài 10
CREATE DATABASE QUANLYDOANVIEN
USE QUANLYDOANVIEN
CREATE TABLE TOCONGDOAN(
MATCD CHAR(10) PRIMARY KEY,
TENTCD CHAR(30) NOT NULL
CREATE TABLE CONGDOAN(
MACDV CHAR(10) PRIMARY KEY, --MA CONG DOAN VIEN--
TENCDV CHAR(30) NOT NULL, --TEN CONG DOAN VIEN--
NGAYSINH SMALLDATETIME,
NGAYV SMALLDATETIME NOT NULL, --NGAY RA NHAP CONG DOAN--
MATCD CHAR(10) NOT NULL,
CONSTRAINT KN_CONGDOAN FOREIGN KEY (MATCD) REFERENCES TOCONGDOAN(MATCD)
CREATE TABLE KHENTHUONG(
MACDV CHAR(10),
MSKT CHAR(10), --MA SO KHEN THUONG--
LYDO CHAR(40), --LY DO KHEN THUONG--
NAM INT, --NAM KHEN THUONG--
CONSTRAINT KC_KHENTHUONG PRIMARY KEY (MACDV,MSKT),
CONSTRAINT KN_KHENTHUONG FOREIGN KEY (MACDV) REFERENCES CONGDOAN(MACDV)
/*2.HAY TAO VIEW DE TONG HOP THONG TIN VE CAC CONG DOAN VIEN DA DUOC KHEN THUONG CUA TO
CONG DOAN "HE THONG THONG TIN"*/
CREATE VIEW VD2
AS
SELECT CONGDOAN.* FROM CONGDOAN,KHENTHUONG,TOCONGDOAN
WHERE CONGDOAN.MACDV=KHENTHUONG.MACDV AND CONGDOAN.MATCD=TOCONGDOAN.MATCD
AND TENTCD='HE THONG THONG TIN'
/*3.HAY TAO VIEW CHO BIET THONG TIN VE CAC CONG DOAN VIEN CHUA TUNG DUOC KHEN THUONG*/
CREATE VIEW VD3
AS
SELECT * FROM CONGDOAN
WHERE MACDV NOT IN (SELECT MACDV FROM KHENTHUONG)
/*4.HAY TAO VIEW DE CHO BIET THONG TIN VE CAC CONG DOAN VIEN DUOC KHEN THUONG VE VIEC HOAN
THANH DE TAI CAP BO*/
CREATE VIEW VD4
AS
SELECT * FROM CONGDOAN
WHERE MACDV IN (SELECT MACDV FROM KHENTHUONG
WHERE LYDO='HOAN THANH DE TAI CAP BO')
/*5.TAO THU TUC CO THAM SO VAO LA @TENTCD DE DUA RA THONG TIN VE NHUNG DOAN VIEN CUA CHI DOAN TREN*/
CREATE PROC VD5
@TENTCD CHAR(30)
AS
SELECT * FROM CONGDOAN
WHERE MATCD IN(SELECT MATCD FROM TOCONGDOAN
WHERE TENTCD=@TENTCD)
EXEC VD5 'HOC SINH'
/*6.TAO THU TUC CO THAM SO VAO LA@TENTCD,@NAM DE DUA RA THONG TIN VE NHUNG CONG DOAN VIEN
CUA TO CONG DOAN DA DUOC KHEN THUONG VAO NAM TREN */
CREATE PROC VD6
@TENTCD CHAR(30), @NAM INT
AS
SELECT CONGDOAN.* FROM CONGDOAN,KHENTHUONG,TOCONGDOAN
WHERE CONGDOAN.MACDV=KHENTHUONG.MACDV AND CONGDOAN.MATCD=TOCONGDOAN.MATCD
AND TENTCD=@TENTCD AND NAM=@NAM
EXEC VD6 'HE THONG THONG TIN', 2009
/*7.TAO THU TUC CO THAM SO VAO LA @TENCD DE XOA THONG TIN VE NHUNG CONG DOAN VIEN THUOC
CHI DOAN TREN*/
CREATE PROC VD7
@TENTCD CHAR(30)
AS
DELETE FROM CONGDOAN
WHERE MATCD IN(SELECT MATCD FROM TOCONGDOAN
WHERE TENTCD=@TENTCD)
EXEC VD7 'THONG TIN KINH TE'
/*8.TAO TRIGGER DE KIEM TRA VIEC NHAP DU LIEU CHO BANG KHENTHUONG.NEU NAM KHEN THUONG 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 KHENTHUONG
FOR INSERT
AS
IF EXISTS (SELECT NAM FROM KHENTHUONG
WHERE NAM<0)
BEGIN
PRINT 'DU LIEU NHAP KHONG HOP LE'
ROLLBACK TRAN
END
ELSE PRINT 'DU LIEU NHAP THANH CONG'
INSERT INTO KHENTHUONG
VALUES ('CDV3','04','OH MY GOOD',-2010)
/*9.SU DUNG KIEU DU LIEU CURSOR DE TONG HOP THONG TIN VE NHUNG CONG DOAN VIEN CHUA TUNG DUOC
KHEN THUONG TRONG NAM 2007*/
--KHAI BAO--
DECLARE CS9 CURSOR FOR
SELECT * FROM CONGDOAN
WHERE MACDV NOT IN (SELECT MACDV FROM KHENTHUONG
WHERE NAM=2007)
--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
Bài11
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 TENSV NGAYSINH DVHT DIEM'
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
Bài12
CREATE DATABASE QUANLYKHACHSAN
USE QUANLYKHACHSAN
CREATE TABLE KHACHHANG(
MAK CHAR(10) PRIMARY KEY,
TENK CHAR(40) NOT NULL,
DIACHI CHAR(40),
SDT CHAR(15)
create 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),
NGAYNHAN SMALLDATETIME, --NGAY NHAN PHONG--
NGAYTRA SMALLDATETIME, --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 'MAK TENK DIACHI SDT'
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
Bài13
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 d? t?ng h?p thông tin v? các nhân viên chua tích lu? du?c trình d? ngo?i ng? nào--
CREATE VIEW VD3
AS
SELECT * FROM NHANVIEN
WHERE MANV NOT IN (SELECT MANV FROM TDNN)
--4/ T?o View d? t?ng h?p thông tin v? các nhân viên có luong 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 d? dua ra danh các nhân viên bi?t ngo?i ng?
và trình d? 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 d? dua 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 d? dua ra danh các nhân viên
? phòng này bi?t ngo?i ng? và trình d? ?ng v?i các tham bi?n dã 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 d? dua ra danh các nhân viên có luong 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 d? ki?m tra khi nh?p d? li?u vào b?ng NhanVien ph?i d?m b?o luong 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 d? tang luong 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 'MANV TENNV DIACHI MAP LUONGMOI'
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 d? dua ra danh sách các nhân viên có ngo?i ng? ti?ng anh trình d? 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 'MANV TENNV DIACHI MAP LUONG'
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
Bài15
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 d? t?ng h?p thông tin v? di?m rèn luy?n c?a nh?ng sinh viên h?c l?p "Cao d?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 d? in ra thông tin v? sinh viên có di?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 d? t?ng h?p thông tin v? nh?ng sinh viên có di?m rèn luy?n<50 trong nam 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 d? dua ra thông tin v? di?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 d? dua ra thông tin v? di?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 d? dua ra thông tin v? di?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 di?m nh?p vào <0 ho?c >100 thì dua
ra yêu c?u nh?p l?i và b?n ghi này không du?c phép nh?p vào b?ng, ngu?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 d? in ra màn hình danh sách di?m rèn luy?n c?a h?c k? 1 nam 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 d? in ra màn hình danh sách di?m rèn luy?n c?a nam 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
--------------------------
Bạn đang đọc truyện trên: Truyen247.Pro