Đọc truyện Luat dat dai Boydb.wap.sh 2011 - Bài tập và lời giải Hệ quản trị cơ sở dữ liệu DT01692530982boydb.wap.sh

Bài tập và lời giải Hệ quản trị cơ sở dữ liệu DT01692530982boydb.wap.sh

Trước Sau

Màu nền

Font chữ

Font size

Chiều cao dòng

--1. Hãy tạo cấu trúc cơ sở dữ liệu trên(Dùng câu lệnh SQL ; Với mỗi bảng nếu có ràng buộc

--khoá chính hoặc ràng buộc khoá ngoài yêu cầu định nghĩa đầy đủ)

CREATE DATABASE DANGVIEN

USE DANGVIEN

CREATE TABLE TTDV(

MADV CHAR(10) PRIMARY KEY,

TENDV NVARCHAR(30),

NGAYS SMALLDATETIME,

GT CHAR(4),

NGAYVD SMALLDATETIME,

MACB CHAR(10),

CONSTRAINT KN FOREIGN KEY(MACB)REFERENCES CB(MACB))

CREATE TABLE CB(

MACB CHAR(10) PRIMARY KEY,

TENCB NVARCHAR(30))

CREATE TABLE KT(

MADV CHAR(10),

MASKT CHAR(10)PRIMARY KEY,

NGAYQD SMALLDATETIME,

CKT CHAR(20),

LYDO NVARCHAR(30),

CONSTRAINT KN1 FOREIGN KEY(MADV)REFERENCES TTDV(MADV))

INSERT INTO TTDV

VALUES('DV01','DINH XUAN TRIEU','03/07/1989','NAM','05/19/2009','CB01')

INSERT INTO TTDV

VALUES('DV02','DINH XUAN BACH','11/02/1990','NAM','05/19/2010','CB01')

INSERT INTO CB

VALUES('CB01','PHUC TIEN')

INSERT INTO CB

VALUES('CB02','CNTT')

INSERT INTO KT

VALUES('DV01','KT01','05/19/2008','CAP TINH','DV TOT')

INSERT INTO KT

VALUES('DV02','KT02','05/19/2007','CAP XA','DV GIOI')

INSERT INTO KT

VALUES('DV01','KT03','05/19/2008','CAP TP','DV TOT')

SELECT*FROM KT

--2. Tạo view để tổng hợp thông tin về các Đảng viên vào Đảng ngày 19/5/2009

CREATE VIEW CAU2

SELECT*FROM TTDV

WHERE NGAYVD='05/19/2009'

SELECT*FROM CAU2

--3. Tạo thủ tục lưu trữ với tham số vào @Maskt để lưu thông tin về những Đảng viên được khen thưởng theo mã số khen thưởng ở trên

CREATE PROC CAU3

@MASKT CHAR(30)

SELECT TTDV.* FROM TTDV,KT

WHERE TTDV.MADV=KT.MADV AND MASKT=@MASKT

CAU3 'KT03'

--4. Tạo trigger cho phép thay đổi dữ liệu của những bảng liên quan trong view ở ý 2 khi cập nhật dữ liệu trên view.

--5. Dùng kiểu dữ liệu Cursor để tổng hợp thông tin về các Đảng viên chưa được khen thưởng trong năm 2009

--KHAI BAO CURSOR

DECLARE CAU5 CURSOR FOR

SELECT*FROM TTDV

WHERE MADV NOT IN(SELECT MADV FROM KT)

--MO CURSOR

OPEN CAU5

--XU LY CURSOR

DECLARE @MADV CHAR(10),@TENDV CHAR(30),@NGAYS SMALLDATETIME,@GT CHAR(5),@NGAYVD SMALLDATETIME,@MACB CHAR(10)

FETCH NEXT FROM CAU5

INTO @MADV,@TENDV,@NGAYS,@GT,@NGAYVD,@MACB

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MADV+@TENDV+CONVERT(CHAR(30),@NGAYS)+ @GT+CONVERT(CHAR(30),@NGAYVD)+@MACB

FETCH NEXT FROM CAU5

INTO @MADV,@TENDV,@NGAYS,@GT,@NGAYVD,@MACB

END

--DONG

CLOSE CAU5

--HUY

DEALLOCATE CAU5

BÀI TẬP HỆ QUẢN TRỊ CSDL SQL SERVER 2000

Bài 1.

Cho cơ sở dữ liệu quản lý bán hàng gồm các bảng dữ liệu sau:

+ Bảng DMKhach để lưu các danh mục các khách hàng gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaKhach

Mã khách hàng

TenKhach

Tên khách hàng

DiaChi

Địa khách hàng

DienThoai

Điện thoại

+ Bảng DMHang để lưu danh mục hàng hoá gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaHang

Mã hàng

TenHang

Tên hàng

DVT

Đơn vị tính

+ Bảng HoaDonBan để lưu danh sách các đơn hàng gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

SoHD

Số hoá đơn bán

MaKhach

Mã khách hàng

NgayHD

Ngày hoá đơn

DienGiai

Diễn giải

+ Bảng ChiTietHoaDon để lưu chi tiết các hoá đơn bán hàng gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

SoHD

Số hoá đơn bán

MaHang

Mã hàng

SoLuong

Số lượng bán

DonGia

Đơn giá

Yêu cầu

1/ Hãy tạo cấu trúc cơ sở dữ liệu trên(Dùng trình soạn thảo Query Analyzer; Với mỗi bảng nếu có ràng buộc khoá chính hoặc ràng buộc khoá ngoài yêu cầu định nghĩa đầy đủ)

Thực hiện các câu lệnh truy vấn sau:

1. Đưa ra danh sách mặt hàng chưa được bán trong tháng 9 năm 2009

2. Đưa ra danh sách khách hàng có địa chỉ ở Thái Nguyên và từng mua hàng trong tháng 9 năm 2009

3. Đưa ra số lượng đã bán tương ứng của từng mặt hàng trong tháng 9 năm 2009

4. Đưa ra tổng số lượng hàng đã bán cho mục đích mua hàng ‘Tiếp khách’

5. Hiển thị thông tin khách hàng đã từng mua hàng tại cửa hàng

2/ Tạo View để tổng hợp dữ liệu về từng mặt hàng đã được bán (Cấu trúc View gồm các thuộc tính: MaHang, TenHang, DVT, SoLuongBan)

3. Tạo View để tổng hợp dữ liệu về các mặt hàng đã được bán với số lượng lớn nhất.

4/ Tạo View để tổng hợp dữ liệu về các khách hàng đã mua hàng trong ngày 20/10/2005.

5/ Tạo thủ tục có tham số vào là @SoHD để đưa ra danh mục các mặt hàng có trong hoá đơn trên.

6/ Tạo thủ tục có tham số vào là @Ngay để đưa ra danh mục các mặt hàng đã được bán vào ngày trên (Danh sách đưa ra gồm các thuộc tính sau: MaHang, TenHang, DVT, SoLuong).

7/ Tạo TRIGGER để kiểm tra khi nhập dữ liệu vào bảng ChiTietHoaDon nếu số lượng hoặc đơn giá nhập vào nhỏ hơn 0 thì in ra màn hình thông báo lỗi “Dữ liệu nhập vào không hợp lệ” và bản ghi này không được phép nhập vào bảng; Ngược lại in ra màn hình thông báo “Dữ liệu đã được nhập thành công”.

8/ Tạo kiểu dữ liệu CURSOR để lưu trữ thông tin về các mặt hàng đã được bán trong ngày 20/11/2006. Sau đó đưa ra màn hình danh sách dữ liệu trên.

9/ Thêm mới một trường TongTien vào bảng HoaDonBan

10/ Dùng kiểu dữ liệu CURSOR để cập nhập dữ liệu cho trường TongTien

Bài 2.

Cho cơ sở dữ liệu quản lý nhà cho thuê bao gồm các bảng dữ liệu sau:

+ Bảng KHACH để lưu danh sách các khách hàng gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaK

Mã khách hàng

TenK

Tên khách hàng

ĐiaChi

Địa chỉ khách

SoDT

Số điện thoại

+ Bảng NHA để lưu trữ thông tin về các ngôi nhà cho thuê, gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaN

Mã nhà

TenChuNha

Tên chủ nhà

GiaThue

Giá thuê

DiaChiNha

Địa chỉ nhà

+Bảng HOPDONG để lưu trữ thông tin về các hợp đồng thuê nhà của khách, gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

SoHĐ

Số hợp đồng

MaN

Mã nhà

MaK

Mã khách

NgayBĐ

Ngày bắt đầu hợp đồng

NgayKT

Ngày kết thúc hợp đồng

Yêu cầu:

1/ Hãy tạo cấu trúc cơ sở dữ liệu trên (Dùng trình soạn thảo Query Analyzer; Với mỗi bảng nếu có ràng buộc khoá chính hoặc ràng buộc khoá ngoài yêu cầu sv phải định nghĩa đầy đủ).

Thực hiện các câu lệnh truy vấn sau:

1. Đưa ra danh sách các ngôi nhà đang được thuê

2. Đưa ra thông tin về khách hàng đã thuê ngôi nhà có mã là ‘NHA09’

3. Đưa ra thông tin về những ngôi nhà hiện chưa có người nào thuê

2/ Tạo View để tổng hợp thông tin về những ngôi nhà có giá thuê cao nhất.

3/ Tạo View để tổng hợp thông tin về các khách hàng ngày hôm nay đã hết hạn hợp đồng.

4/ Tạo View để tổng hợp thông tin về các ngôi nhà chưa từng được ai thuê.

5/ Tạo thủ tục có một tham số vào là @ngay để đưa ra danh sách các khách hàng đã hết hạn hợp đồng vào ngày trên.

6/ Tạo thủ tục để đưa ra màn hình danh sách các ngôi nhà hiện tại chưa có khách hàng thuê.

7/ Tạo thủ tục có tham số là @Ngay để đưa ra danh sách các khách hàng bắt đầu thuê nhà vào ngày trên.

8/ Tạo Trigger để kiểm tra khi nhập dữ liệu vào bảng NHA nếu giá thuê nhập vào <0 thì đưa ra màn hình thông báo lỗi “Dữ liệu nhập vào không hợp lệ” và bản ghi này không được phép nhập vào bảng; Ngược lại in ra màn hình thông báo “Dữ liệu đã được nhập thành công”.

9/ Sử dụng kiểu dữ liệu CURSOR để đưa ra màn hình thông tin về các khách hàng đang thuê các ngôi nhà có địa chỉ ở “Tổ 10, phường Phan Đình Phùng”.

10/ Sử dụng kiểu dữ liệu CURSOR để đưa ra danh sách các ngôi nhà hiện tại chưa có khách hàng thuê.

Bài 3.

Cho cơ sở dữ liệu quản lý sinh viên bao gồm các bảng dữ liệu sau:

+ Bảng LOP để lưu thông tin về các lớp học gồm có các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaLop

Mã lớp học

TenLop

Tên lớp học

+ Bảng TINH để lưu trữ thông tin về các tỉnh thành phố, gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaTinh

Mã tỉnh, thành phố

TenTinh

Tên tỉnh, thành phố

+Bảng SINHVIEN để lưu trữ thông tin về các sinh viên, gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaSV

Mã sinh viên

HoTen

Họ tên sinh viên

NgaySinh

Ngày sinh

GioiTinh

Giới tính

MaLop

Mã lớp

MaTinh

Mã tỉnh

DTB

Điểm trung bình

Yêu cầu:

Thực hiện các câu lệnh truy vấn sau:

1. Đưa ra thông tin về những sinh viên có điểm trung bình dưới 5

2. Đưa ra thông tin về sinh viên có địa chỉ ở Thái Nguyên

3. Đưa ra thông tin về các lớp học không có sinh viên nào ở Hà Nội

2/ Tạo View để tổng hợp thông tin về các sinh viên có điểm trung bình cao nhất.

3/ Tạo View để tổng hợp thông tin về các sinh viên có điểm trung bình cao nhất theo từng lớp học.

4/ Tạo thủ tục để đưa ra sĩ số sinh viên cho từng lớp học (Danh sách đưa ra phải có các thuộc tính sau: MaLop, TenLop, SiSo).

5/ Tạo thủ tục có tham số là @Tenlop để xoá thông tin về những sinh viên học ở lớp trên.

6/ Tạo TRIGGER để kiểm tra dữ liệu khi nhập vào bảng sinh viên: nếu điểm trung bình nhập vào <0 thì đưa ra màn hình thông báo lỗi và bản nghi này không được phép nhập vào bảng dữ liệu, ngược lại đưa ra thông báo bản ghi đã nhập thành công.

7/ Sử dụng kiểu dữ liệu CURSOR để đưa ra màn hình danh sách sinh viên học ở lớp có tên là ‘Cao đẳng 2A’.

Bài 4:

Cho cơ sở dữ liệu quản lý Thực tập gồm 3 bảng dữ liệu sau:

+ Bảng SINHVIEN chứa danh sách sinh viên bao gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

Masv

Mã số sinh viên

HotenSV

Họ tên sinh viên

Ngày sinh

Diachi

Địa chỉ

+ Bảng DETAI chứa danh sách các đề tài thực tập gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaDT

Mã số đề tài

TenDT

Tên đề tài

GVHD

Họ và tên giáo viên hướng dẫn đề tài

+ Bảng SV_DETAI chứa thông tin về tình hình thực tập của sinh viên theo các đề tài, gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaDT

Mã số đề tài

MaSV

Mã số sinh viên

NTT

Nơi sinh viên đến thực tập

Kết quả thực tập của sv theo đề tài đã chọn

Yêu cầu:

2/ Tạo View để tổng hợp thông tin về các sinh viên tham gia thực tập tại quê.

3/ Tạo View để tổng hợp thông tin về các sinh viên có kết quả thực tập cao nhất.

4/ Tạo View để tổng hợp thông tin về mỗi giáo viên đã và đang hướng dẫn bao nhiêu sinh viên thực tập.

5/ Tạo thủ tục có tham số vào là @gvhd để đưa ra danh sách các sinh viên do giáo viên trên hướng dẫn.

6/ Tạo thủ tục có tham số vào là @NTT để đưa ra danh sách các sinh viên thực tập tại địa điểm trên.

7/ Tạo thủ tục để đưa ra danh sách các đề tài chưa từng có sinh viên nào tham gia đăng ký thực tập.

8/ Tạo TRIGGER để kiểm tra khi nhập dữ liệu vào bảng SV_DETAI nếu kết quả thực tập nhỏ hơn 0 thì đưa ra màn hình thông báo lỗi và bản nghi này không được phép nhập vào bảng dữ liệu, ngược lại đưa ra thông báo bản ghi đã nhập thành công.

9/ Dùng kiểu dữ liệu CURSOR để đưa ra màn hình danh sách các sinh viên phải thực tập lại (những sinh viên có kết quả thực tập < 5 yêu cầu phải thực tập lại)

10/ Dùng kiểu dữ liệu CURSOR để đưa ra màn hình danh sách các đề tài có kết quả thực tập cao nhất

Bài 5:

Cho cơ sở dữ liệu quản lý thư viện gồm các bảng dữ liệu sau:

+Bảng SACH chứa danh mục sách gồm các thông tin thuộc tính sau:

Tên Thuộc tính

Giải thích

MaSach

Mã sách

TenSach

Tên sách

NXB

Tên nhà xuất bản

NamXB

Năm xuất bản

TenTG

Tên tác giả

+Bảng DOCGIA chứa danh sách độc giả gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaDG

Mã độc giả

TenDG

Tên độc giả

DiaChiDG

Địa chỉ độc giả

+Bảng MUON_TRA chứa thông tin về sự mượn và trả sách của độc giả gồm các thuộc tính:

Tên Thuộc tính

Giải thích

MaDG

Mã độc giả

MaSach

Mã sách

NgayMuon

Ngày mượn

Số lượng mượn

NgayHenTra

Ngày hẹn trả

NgayTra

Ngày trả

Yêu cầu:

2/ Tạo View để tổng hợp thông tin về các độc giả đã hết hạn trả sách nhưng chưa trả.

3/ Tạo View để tổng hợp thông tin về danh mục sách chưa từng được độc giả nào mượn.

4/ Tạo View để tổng hợp thông tin về từng loại sách hiện tại có độc giả đang mượn ( Cấu trúc View gồm các thuộc tính sau: MaSach, TenSach, TenTG, SoLuong).

5/ Tạo thủ tục có tham số là @MaDG để đưa ra danh mục sách mà độc giả trên đang mượn.

6/ Tạo thủ tục có tham số là @NgayMuon để đưa ra danh sách các độc giả đã mượn sách vào ngày trên.

7/ Tạo thủ tục để đưa ra danh sách các độc giả ngày hôm nay là hạn cuối cùng phải trả sách cho thư viện.

8/ Tạo TRIGGER để kiểm tra dữ liệu khi nhập vào bảng MUON_TRA nếu số lượng mượn nhỏ hơn 0 thì thì đưa ra màn hình thông báo lỗi và bản nghi này không được phép nhập vào bảng dữ liệu, ngược lại đưa ra thông báo bản ghi đã nhập thành công.

9/ Dùng kiểu dữ liệu CURSOR để đưa ra màn hình danh sách các độc giả đã hiện tại vẫn đang mượn sách của thư viện.

Bài 6

Cho cơ sở dữ liệu quản lý dự án gồm các bảng dữ liệu sau:

+ Bảng NHANVIEN chứa danh sách các nhân viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaNV

Mã nhân viên

Hoten

Họ tên nhân viên

Ngaysinh

Ngày sinh

Giới tính

+ Bảng DUAN chứa thông tin về các dự án gồm có các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaDA

Mã dự án

TenDA

Tên dự án

NganSach

Ngân sách

+ Bảng THAMGIA ghi danh sách sinh viên đăng ký tham gia dự án

Tên Thuộc tính

Giải thích

MaDA

Mã dự án

MaNV

Mã nhân viên

TGBD

Thời gian bắt đầu

TGKT

Thời gian kết thúc

Yêu cầu:

2/ Tạo View để tổng hợp thông tin về các nhân viên chưa tham gia bất kỳ dự án nào.

3/ Tạo View để tổng hợp thông tin về các dự án có ngân sách lớn nhất.

Tạo View để tổng hợp thông tin về mỗi nhân viên đã tham gia bao nhiêu dự án.

4/ Tạo thủ tục có tham số là @TenDA để đưa ra danh sách các nhân viên tham gia dự án trên.

5/ Tạo thủ tục có tham số là @TGKT và @MaDA để đưa ra danh sách các nhân viên phải hoàn thành dự án đó vào ngày trên.

6/ Tạo TRIGGER để kiểm tra dữ liệu khi nhập vào bảng DUAN nếu ngân sách nhỏ hơn 0 thì đưa ra màn hình thông báo lỗi và bản nghi này không được phép nhập vào bảng dữ liệu, ngược lại đưa ra thông báo bản ghi đã nhập thành công.

7/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách nhân viên bắt đầu thực hiện dự án “Phòng chống bệnh mắt hột” từ ngày 20/11/2005.

8/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các dự án chưa từng có nhân viên nào tham gia đăng ký.

Bài 7

Cho cơ sở dữ liệu quản lý Tài sản trong một cơ quan gồm các bảng quan hệ sau:

+Bảng DMPhong để lưu danh sách các phòng gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaP

Mã phòng

TenP

Tên phòng

+ Bảng DMTaiSan để lưu trữ thông tin về các tài sản gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaTS

Mã tài sản

TenTS

Tên tài sản

DonVi

Đơn vị tính

Gia

Đơn giá

+ Bảng PhanPhoi để lưu trữ thông tin về các loại tài sản có trong từng phòng gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaTS

Mã tài sản

MaP

Mã phòng

SoLuong

Số lượng

NgayPP

Ngày phân phối tài sản

GhiChu

Ghi chú

Yêu cầu:

2/ Tạo View để tổng hợp thông tin về các tài sản có đơn giá lớn nhất.

3/ Tạo View để tổng hợp thông tin về những tài sản chưa từng được phân phối cho các phòng để sử dụng.

4/ Tạo thủ tục có tham số là @TenP để đưa ra danh sách các tài sản được phân phối vào phòng trên.

5/ Tạo thủ tục có tham số là @TenTS để đưa ra danh sách các phòng được phân phối tài sản trên.

6/ Tạo TRIGGER để kiểm tra dữ liệu khi nhập vào bảng DMTaiSan nếu đơn giá nhập vào nhỏ hơn 0 thì đưa ra màn hình thông báo lỗi và bản nghi này không được phép nhập vào bảng dữ liệu, ngược lại đưa ra thông báo bản ghi đã nhập thành công.

Bài 8

Cho cơ sở dữ liệu quản lý điểm thi gồm các bảng dữ liệu sau:

+ Bảng SinhVien để lưu danh mục thông tin về sinh viên gồm các trường sau:

Tên Thuộc tính

Giải thích

MaSV

Mã sinh viên

TenSV

Tên sinh viên

NgaySinh

Ngày sinh

GioiTinh

Giới tính

QueQuan

Địa chỉ

+ Bảng MonHoc chứa danh mục các môn học gồm các trường sau:

Tên Thuộc tính

Giải thích

MaM

Mã môn

TenM

Tên môn

DVHT

Đơn vị học trình

+ Bảng Diem để lưu trữ thông tin về điểm thi theo từng môn học của mỗi sinh viên bao gồm các trường sau:

Tên Thuộc tính

Giải thích

MaM

Mã môn

MaSV

Mã sinh viên

DiemThi

Điểm thi

Yêu cầu

2/ Tạo View để tổng hợp thông tin về các sinh viên có điểm thi cao nhất của môn học có tên là “Cơ sở dữ liệu”.

3/ Tạo thủ tục có tham số là @TenM để đưa ra danh sách sinh viên phải thi lại môn học trên.

4/ Dùng kiểu dữ liệu CURSOR để đưa ra trung bình điểm thi của từng môn học.

Bài 9

Cho cơ sở dữ liệu quản lý gồm các bảng dữ liệu sau:

+ Bảng GIAOVIEN để lưu các thông tin về giáo viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaGV

Mã giáo viên

TenGV

Tên giáo viên

DiaChi

Địa chỉ giáo viên

DienThoai

Điện thoại giáo viên

+ Bảng HOCVI để lưu thông tin về học vị gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaHV

Mã học vị

TenHV

Tên học vị

+ Bảng CHUYENNGANH để lưu danh sách các chuyên ngành gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaCN

Mã chuyên ngành

TenCN

Tên chuyên ngành

+ Bảng GV_HV_CN để lưu thông tin về học vị của giáo viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaGV

Mã giáo viên

MaHV

Mã học vị

MaCN

Mã chuyên ngành

Nam

Năm đạt học vị

Yêu cầu:

2/ Tạo View để cho biết thông tin về các giáo viên có học vị là “Tiến sỹ”

3/ Tạo View để cho biết thông tin về các giáo viên có chuyên ngành “Kinh tế”

4/ Tạo View để cho biết thông tin về các giáo viên có địa chỉ ở “Thái Nguyên”

5/ Tạo thủ tục có tham số vào là @Nam để đưa ra tên của các giáo viên nhận học vị “Tiến sỹ vào năm trên.

6/ Tạo thủ tục nhập dữ liệu cho bảng GIAOVIEN

7/ Tạo thủ tục có tham số vào là @TenHV để đưa ra thông tin về các giáo viên có tên học vị trên

8/ Tạo TRIGGER để kiểm tra khi nhập dữ liệu vào bảng GV_HV_CN nếu năm nhập vào nhỏ hơn 0 thì in ra màn hình thông báo lỗi “Dữ liệu nhập vào không hợp lệ” và bản ghi này không được phép nhập vào bảng; Ngược lại in ra màn hình thông báo “Dữ liệu đã được nhập thành công”.

9/ Dùng kiểu dữ liệu CURSOR để đưa ra thông tin về từng giáo viên của từng chuyên ngành.

Bài 10

Cho cơ sở dữ liệu quản lý gồm các bảng dữ liệu sau:

+ Bảng TOCONGDOAN để lưu thông tin về các chi đoàn gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaTCD

Mã tổ công đoàn

TenTCD

Tên tổ công đoàn

+ Bảng CONGDOAN để lưu thông tin về các đoàn viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaCDV

Mã công đoàn viên

TenCDV

Tên công đoàn viên

NgayS

Ngày sinh

NgayV

Ngày ra nhập công đoàn

MaTCD

Mã tổ công đoàn

+ Bảng KHENTHUONG để lưu thông tin về tình hình khen thưởng của các công đoàn viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaCDV

Mã đoàn viên

MSKT

Mã số khen thưởng

Ldo

Lý do khen thưởng

Nam

Năm khen thưởng

Yêu cầu:

1.Hãy tạo cấu trúc cơ sở dữ liệu trên (Dùng trình soạn thảo Query Analyzer; Với mỗi bảng nếu có ràng buộc khoá chính hoặc ràng buộc khoá ngoài yêu cầu sv phải định nghĩa đầy đủ).

2. Hãy tạo view để tổng hợp thông tin về các công đoàn viên đã được khen thưởng của tổ công đoàn “Hệ thống thông tin”.

3. Hãy tạo view cho biết thông tin về các công đoàn viên chưa từng được khen thưởng.

4. Hãy tạo view để thông tin về các công đoàn viên được khen thưởng về việc hoàn thành đề tài câp bộ.

5. Tạo thủ tục có tham số vào @TenTCD để đưa ra thông tin về những đoàn viên của chi đoàn trên.

6. Tạo thủ tục có tham số vào @TenTCD, @Nam để đưa ra thông tin về những công đoàn viên của tổ công đoàn đã được khen thưởng vào năm trên.

7. Tạo thủ tục có tham số vào @Tencd để xoá thông tin về những công đoàn viên thuộc chi đoàn trên.

8. Tạo Trigger để kiểm tra việc nhập dữ liệu cho bảng KHENTHUONG. Nếu năm khen thưởng nhỏ hơn 0 thì in ra thông báo dữ liệu không hợp lệ và bản ghi không được phép nhập vào bảng. Ngược lại thì in ra thông báo dữ liệu nhập thành công.

9. Sử dụng kiểu dữ liệu Cursor để tổng hợp thông tin về những công đoàn viên chưa được khen thưởng trong năm 2007.

Bài 11

Cho cơ sở dữ liệu quản lý điểm gồm 3 bảng dữ liệu sau:

+ Bảng SINHVIEN chứa danh sách sinh viên bao gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaSV

Mã số sinh viên

HotenSV

Họ tên sinh viên

Ngày sinh

Diachi

Địa chỉ

DVHT

Đơn vị học trình

+ Bảng MON chứa danh sách các môn học gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaM

Mã môn

TenM

Tên môn

+ Bảng DIEM chứa thông tin về điểm của sinh viên theo các môn học gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaM

Mã môn

MaSV

Mã số sinh viên

Phach

Số phách

DIEM

Điểm thi

Yêu cầu:

2/ Tạo View để tổng hợp thông tin về sinh viên có điểm thi cao nhất của môn “CSDL”.

3/ Tạo View để tổng hợp thông tin về sinh viên có địa chỉ ở “Thái Nguyên”

4/ Tạo View cho biết thông tin về các sinh viên sinh trước năm 1980

5/ Tạo thủ tục có tham số đầo vào @TenM để đưa ra danh sách các sinh viên có điểm thi chưa đạt của môn học trên.

6/ Tạo thủ tục có tham số đầo vào @Diem để đưa ra danh sách các sinh viên có điểm thi thấp hơn điểm trên của môn học “CSDL”.

7/ Tạo Trigger để kiểm tra việc nhập dữ liệu cho bảng DIEM. Nếu điêm nhỏ hơn 0 hoặc điểm lớn hơn 10 thì in ra thông báo dữ liệu không hợp lệ và bản ghi không được phép nhập vào bảng. Ngược lại thì in ra thông báo dữ liệu nhập thành công.

8/ Dùng kiểu dữ liệu CURSOR để đưa ra màn hình danh sách các sinh viên có điểm thi cao nhất của các môn học?

Bài 12

Cho cơ sở dữ liệu quản lý khách sạn gồm các bảng dữ liệu sau:

+ Bảng KhachHang để lưu danh mục thông tin về khách hàng tại khách sạn gồm các trường sau:

Tên Thuộc tính

Giải thích

MaKhach

Mã khách

TenKhach

Tên khách hàng

ĐiaChi

Địa chỉ

SDT

Số điện thoại

+ Bảng Phong để lưu thông tin về các phòng có trong khách sạn gồm các trường sau:

Tên Thuộc tính

Giải thích

MaP

Mã phòng

TenP

Tên phòng

LoaiP

Loại phòng

Gia

Đơn giá

+ Bảng DatPhong (Đặt phòng) để lưu trữ thông tin việc đặt phòng của khách:

Tên Thuộc tính

Giải thích

MaKhach

Mã khách

MaP

Mã phòng

NgayNhan

Ngày nhận phòng

NgayTra

Ngày trả phòng

Yêu cầu:

2/ Tạo thủ tục để tăng giá của tất cả các phòng trong khách sạn lên 5%.

3/ Tạo View để tổng hợp thông tin về các khách hàng hiện tại đang thuê phòng tại khách sạn.

4/ Tạo View để tổng hợp thông tin hoá đơn thanh toán của khách hàng (hoá đơn gồm các thuộc tính sau: (Mã khách, Tên khách, Mã phòng, Đơn Giá, Ngày nhận, Ngày Trả, Thành Tiền).

5/ Tạo View để tổng hợp thông tin về các khách hàng có số ngày đặt phòng lớn nhất.

6/ Tạo thủ tục để đưa ra danh các phòng hiện tại chưa có khách hàng đặt phòng .

7/ Tạo thủ tục để đưa ra danh sách khách hàng ngày hôm nay phải trả phòng.

8/ Tạo trigger để kiểm tra dữ liệu khi cập nhật vào bảng Phòng phải đảm bảo dữ liệu đơn giá >0.

9/ Tạo trigger để kiểm tra dữ liệu khi nhập vào bảng DatPhong phải đảm ngày trả phòng luôn lớn hơn hoặc bằng ngày nhận phòng.

10/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách khách hàng ngày hôm nay phải trả phòng.

Bài 13

Cho cơ sở dữ liệu gồm các bảng dữ liệu sau:

+ Bảng NhanVien để lưu thông tin về nhân viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaNV

Mã nhân viên

TenNV

Tên nhân viên

DiaChi

Địa chỉ

Map

Mã phòng

Luong

Lương của nhân viên

+ Bảng Phong để lưu thông tin về các phòng ban trong cơ quan gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaP

Mã phòng

TenP

Tên phòng

DiaChiP

Địa chỉ phòng

SDT

Số điện thoại

+ Bảng NgoaiNgu (ngoại ngữ) gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaNN

Mã ngoại ngữ

TenNN

Tên ngoại ngữ

+ Bảng TrinhDo_NN (trình độ ngoại ngữ) để lưu trữ thông tin về trình độ ngoại ngữ của nhân viên:

Tên Thuộc tính

Giải thích

MaNV

Mã nhân viên

MaNN

Mã ngoại ngữ

TrinhDo

Trình độ ngoại ngữ

Yêu cầu:

2/ Tạo View để tổng hợp thông tin về mỗi phòng hiện tại có bao nhiêu nhân viên.

3/ Tạo View để tổng hợp thông tin về các nhân viên chưa tích luỹ được trình độ ngoại ngữ nào.

4/ Tạo View để tổng hợp thông tin về các nhân viên có lương cao nhất trong phòng họ làm việc.

5/ Tạo thủ tục có tham số vào @TenNN và @TrinhDo để đưa ra danh các nhân viên biết ngoại ngữ và trình độ trên.

6/ Tạo thủ tục có tham số vào @TenP để đưa ra tổng số nhân viên hiện có trong phòng này.

7/ Tạo thủ tục có tham số vào là @TenNN, @TenP và @TrinhDo để đưa ra danh các nhân viên ở phòng này biết ngoại ngữ và trình độ ứng với các tham biến đã cho

8/ Tạo thủ tục có tham số vào @TenP để đưa ra danh các nhân viên có lương cao nhất của phòng này.

9/ Tạo Trigger để kiểm tra khi nhập dữ liệu vào bảng NhanVien phải đảm bảo lương của nhân viên phải >0.

10/ Dùng kiểu dữ liệu CURSOR để tăng lương cho mỗi nhân viên lên 10%.

11/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các nhân viên có ngoại ngữ tiếng anh trình độ C..

Bài 14.

Cho cơ sở dữ liệu gồm các bảng dữ liệu sau:

+ Bảng NhanVien để lưu thông tin về nhân viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaNV

Mã nhân viên

TenNV

Tên nhân viên

DiaChi

Địa chỉ

Map

Mã phòng

Luong

Lương của nhân viên

+ Bảng Phong để lưu thông tin về các phòng ban trong cơ quan gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaP

Mã phòng

TenP

Tên phòng

DiaChiP

Địa chỉ phòng

SDT

Số điện thoại

+ Bảng HocVi (học vị) gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

MaHV

Mã học vị

TenHV

Tên học vị

+ Bảng NV_TDHV (trình độ học vị của nhân viên) :

Tên Thuộc tính

Giải thích

MaNV

Mã nhân viên

MaTD

Mã trình độ học vị

ThoiGian

Ngày tháng năm đạt trình độ này

Yêu cầu:

2/ Tạo View để tổng hợp thông tin về các nhân viên có học vị là tiến sĩ trước năm 1990.

3/ Tạo View để tổng hợp thông tin về các nhân viên chưa có học vị Tiến sĩ.

4/ Tạo thủ tục có tham số vào là @TenHV và @ThoiGian để đưa ra danh các nhân viên có học vị và thời gian đạt được ứng với các tham biến đã cho.

5/ Tạo thủ tục có tham số vào là @TenHV và @ThoiGian để đưa ra danh các nhân viên có trình độ học vị và thời gian đạt được học vị đó ứng với các tham biến đã cho.

6/ Tạo trigger để kiểm tra dữ liệu khi nhập vào bảng NV_TDHV sao cho thời gian đạt học vị đó phải <= thời gian hiện tại.

7/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các nhân viên có học vị Tiến sĩ.

Bài 15.

Cho cơ sở dữ liệu quản lý điểm rèn luyện gồm các bảng sau:

+ Bảng LOP để lưu thông tin về các lớp học gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

Malop

Mã lớp

Tenlop

Tên lớp

+ Bảng SINHVIEN để lưu thông tin về các sinh viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

Masv

Mã sinh viên

Tensv

Tên sinh viên

Ngaysinh

Ngày sinh

Malop

Mã lớp

+ Bảng DIEMRL để lưu thông tin về tình hình điểm rèn luyện của các sinh viên gồm các thuộc tính sau:

Tên Thuộc tính

Giải thích

Masv

Mã sinh viên

Hocky

Học kỳ

Nam

Năm học

Diem

Điểm rèn luyện

Yêu cầu:

1. Hãy tạo cấu trúc cơ sở dữ liệu trên (Dùng trình soạn thảo Query Analyzer; Với mỗi bảng nếu có ràng buộc khoá chính hoặc ràng buộc khoá ngoài yêu cầu sv phải định nghĩa đầy đủ).

2. Hãy tạo view để tổng hợp thông tin về điểm rèn luyện của những sinh viên học lớp “Cao đẳng K4A”

3. Hãy tạo view để in ra thông tin về sinh viên có điểm rèn luyện cao nhất.

4. Tạo view để tổng hợp thông tin về những sinh viên có điểm rèn luyện<50 trong năm học 2006-2007.

5. Tạo thủ tục có tham số vào @Masv để đưa ra thông tin về điểm rèn luyện của sinh viên trên.

6. Tạo thủ tục có tham số vào @Malop, @Nam để đưa ra thông tin về điểm rèn luyện của lớp trên vào nam học trên.

7. Tạo thủ tục có tham số vào @tenlop để đưa ra thông tin về điểm rèn luyện của lớp trên.

8. Tạo Trigger kiểm tra việc nhập dữ liệu cho bảng DIEMRL nếu điểm nhập vào <0 hoặc >100 thì đưa ra yêu cầu nhập lại và bản ghi này không được phép nhập vào bảng, ngược lại thì in ra thông báo nhập thành công.

9. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của học kỳ 1 năm học 2007-2008.

10. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của năm học 2006-2007.

Giải

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

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

SELECT MAH,SUM(SL) AS TSL

FROM CHITIETHOADON

GROUP BY MAH

CREATE VIEW VD3

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

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)

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

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

IF EXISTS (SELECT SL,DONGIA FROM CHITIETHOADON

WHERE SL<0 OR DONGIA<0)

BEGIN

PRINT 'DU LIEU NHAP VAO KHONG HOP LE'

ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO CHITIETHOADON

VALUES('HD8','H03','-10','8000')

--8.TAO KIEU DU LIEU CURSOR DE LUU TRU THONG TIN VE CAC MAT HANG DA DUOC BAN TRONG NGAY 20/11/2006

--SAU DO DUA RA MAN HINH DANH SACH DU LIEU TREN--

--KHAI BAO--

DECLARE CS8 CURSOR FOR

SELECT * FROM DMH

WHERE MAH IN (SELECT MAH FROM CHITIETHOADON

WHERE SOHD IN (SELECT SOHD FROM HOADONBAN

WHERE NGAYHD='11/20/2006'))

--OPEN CURSOR--

OPEN CS8

--XU LY MAU TIN--

FETCH NEXT FROM CS8

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM CS8

END

--DONG CURSOR--

CLOSE CS8

--HUY--

DEALLOCATE CS8

--9.THEM MOI MOT TRUONG TONGTIEN VAO BANG HOADONBAN--

ALTER TABLE HOADONBAN

ADD TONGTIEN INT

--10.DUNG KIEU DU LIEU CURSOR DE CAP NHAP DU LIEU CHO TRUONG TONGTIEN--

--KHAI BAO--

DECLARE CS10 CURSOR FOR

SELECT SOHD,SUM(SL*DONGIA)

FROM CHITIETHOADON

GROUP BY SOHD

--MO--

OPEN CS10

--XU LY MAU TIN--

DECLARE @SOHD CHAR(10),@TONGTIEN FLOAT

FETCH NEXT FROM CS10

INTO @SOHD,@TONGTIEN

WHILE @@FETCH_STATUS=0

BEGIN

UPDATE HOADONBAN

SET TONGTIEN=@TONGTIEN

WHERE SOHD=@SOHD

END

--DONG--

CLOSE CS10

--HUY--

DEALLOCATE CS10

Bài3

CREATE DATABASE QLSV

USE QLSV

CREATE TABLE LOP(

MAL CHAR(10) PRIMARY KEY,

TENL CHAR(20) NOT NULL,

CREATE TABLE TINH(

MAT CHAR(10) PRIMARY KEY,

TENT CHAR(40),

CREATE TABLE SV(

MASV CHAR(15) PRIMARY KEY,

HOTEN CHAR(50) NOT NULL,

NGAYS SMALLDATETIME,

GT BIT NOT NULL,

MAL CHAR(10) NOT NULL,

MAT CHAR(10) NOT NULL,

DTB REAL,

CONSTRAINT KN1 FOREIGN KEY (MAL) REFERENCES LOP,

CONSTRAINT KN2 FOREIGN KEY (MAT) REFERENCES TINH

/*1. DUA RA THONG TIN VE NHUNG SINH VIEN CO DIEM TRUNG BINH DUOI 5*/

SELECT *

FROM SV

WHERE DTB<5

/*2.DUA RA THONG TIN VE SINH VIEN CO DIA CHI O THAI NGUYEN*/

SELECT *

FROM SV

WHERE MAT IN(SELECT MAT FROM TINH

WHERE TENT='THAI NGUYEN')

/*3.DUA RA THONG TIN VE CAC LOP HOC KHONG CO SINH VIEN NAO O HA NOI*/

SELECT *

FROM LOP

WHERE MAL NOT IN(SELECT MAL FROM SV

WHERE MAT IN(SELECT MAT FROM TINH

WHERE TENT='HA NOI' ))

/*4.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN CO DIEM TRUNG BINH CAO NHAT*/

CREATE VIEW DTBMAX

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

SELECT MAL,MAX(DTB) AS DCNL

FROM SV

GROUP BY MAL

CREATE VIEW DTBMAXLOP

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

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)

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

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 THUCTAP

USE THUCTAP

CREATE TABLE SV(

MASV CHAR(15) PRIMARY KEY,

HTSV CHAR(40) NOT NULL,

NGAYS SMALLDATETIME,

DC CHAR(50),

INSERT INTO SV

VALUES('SV01','NGOC LAN','10/30/1990','HA NOI')

INSERT INTO SV

VALUES('SV02','NGOC HUONG','11/28/1991','THAI NGUYEN')

INSERT INTO SV

VALUES('SV03','HUONG NHUNG','11/29/1991','NAM DINH')

INSERT INTO SV

VALUES('SV04','HONG NHUNG','11/11/1991','BINH DINH')

INSERT INTO SV

VALUES('SV05','HONG NHI','09/08/1989','NAM DINH')

CREATE TABLE DETAI(

MADT CHAR(10) PRIMARY KEY,

TENDT CHAR(50) NOT NULL,

GVHD CHAR(45),

INSERT INTO DETAI

VALUES('DT01','CSDL','NGUYEN HUONG')

INSERT INTO DETAI

VALUES('DT02','TIN','NGUYEN NGAN')

INSERT INTO DETAI

VALUES('DT03','TOAN','HONG NGAN')

INSERT INTO DETAI

VALUES('DT04','TOAN LY','HONG NAM')

INSERT INTO DETAI

VALUES('DT05','TOAN TIN','HONG NAM')

INSERT INTO DETAI

VALUES('DT06','TIN HOC','HONG NGAN')

CREATE TABLE SV_DETAI(

MADT CHAR(10),

MASV CHAR(15),

NTT CHAR(40) NOT NULL,

KQ INT,

CONSTRAINT KC PRIMARY KEY(MADT,MASV),

CONSTRAINT KN3 FOREIGN KEY (MADT) REFERENCES DETAI(MADT),

CONSTRAINT KN4 FOREIGN KEY (MASV) REFERENCES SV(MASV),

INSERT INTO SV_DETAI

VALUES('DT01','SV01','HA NOI','7')

INSERT INTO SV_DETAI

VALUES('DT02','SV02','NINH BINH','6')

INSERT INTO SV_DETAI

VALUES('DT03','SV03','NINH THUAN','8')

INSERT INTO SV_DETAI

VALUES('DT04','SV04','BINH THUAN','9')

INSERT INTO SV_DETAI

VALUES('DT05','SV05','BINH MINH','5')

--1.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN THAM GIA THUC TAP TAI QUE--

CREATE VIEW SVTT

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

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

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)

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)

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

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

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

Bai 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

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

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

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)

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)

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

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

Bai 7

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

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

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)

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)

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

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'

Bai8

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

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)

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

Bai 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

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

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

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

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)

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)

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

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

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

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

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)

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

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)

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

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Ài 11

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

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

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

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)

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

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

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

SELECT MAM,MAX(DIEM) AS DIEMCAONHAT

FROM DIEM

GROUP BY MAM

--KHAI BAO--

DECLARE CS8 CURSOR FOR

SELECT DIEM.MAM,SINHVIEN.MASV,HOTENSV,NGAYSINH,DIACHI,DVHT,DIEM

FROM SINHVIEN,MAXDIEM,DIEM

WHERE SINHVIEN.MASV=DIEM.MASV AND DIEM.MAM=MAXDIEM.MAM AND DIEM=DIEMCAONHAT

GROUP BY DIEM.MAM,SINHVIEN.MASV,HOTENSV,NGAYSINH,DIACHI,DVHT,DIEM

--MO--

OPEN CS8

--XU LY MAU TIN--

PRINT 'DANH SACH SINH VIEN CO DIEM THI CAO NHAT CUA CAC MON HOC LA'

PRINT 'MAM MASV TENSVNGAYSINHDVHTDIEM'

DECLARE @B1 CHAR(10),@B2 CHAR(10),@B3 CHAR(20),@B4 SMALLDATETIME,@B5 CHAR(20),@B6 INT,

@B7 FLOAT

FETCH NEXT FROM CS8

INTO @B1,@B2,@B3,@B4,@B5,@B6,@B7

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @B1+@B2+@B3+CONVERT(CHAR(15),@B4)+@B5+CONVERT(CHAR(10),@B6)+CONVERT(CHAR(5),@B7)

FETCH NEXT FROM CS8

INTO @B1,@B2,@B3,@B4,@B5,@B6,@B7

END

--DONG--

CLOSE CS8

--HUY--

DEALLOCATE CS8

Bai 12

CREATE DATABASE QUANLYKHACHSAN

USE QUANLYKHACHSAN

CREATE TABLE KHACHHANG(

MAK CHAR(10) PRIMARY KEY,

TENK CHAR(40) NOT NULL,

DIACHI CHAR(40),

SDT CHAR(15)

DROP TABLE PHONG (

MAP CHAR(10) PRIMARY KEY,

TENP CHAR(30) NOT NULL,

LOAIP CHAR(20),--LOAI PHONG--

DONGIA FLOAT

CREATE TABLE DATPHONG(

MAK CHAR(10),

MAP CHAR(10),

NGAYNHANSMALLDATETIME,--NGAY NHAN PHONG--

NGAYTRASMALLDATETIME,--NGAY TRA PHONG--

CONSTRAINT KC_DATPHONG PRIMARY KEY(MAK,MAP,NGAYNHAN),

CONSTRAINT KN_DATPHONG FOREIGN KEY (MAK) REFERENCES KHACHHANG(MAK),

CONSTRAINT KN1_DATPHONG FOREIGN KEY (MAP) REFERENCES PHONG(MAP),

/*2.TAO THU TUC DE TANG GIA CUA TAT CA CAC PHONG TRONG KHACH SAN LEN 5%*/

CREATE PROC VD2

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

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

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

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

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

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

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

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

IF EXISTS (SELECT NGAYTRA,NGAYNHAN FROM DATPHONG

WHERE CONVERT(INT,NGAYTRA)<CONVERT(INT,NGAYNHAN))

BEGIN

PRINT 'DU LIEU NHAP KHONG HOP LE'

ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO DATPHONG

VALUES('K03','P03','11/20/2010','11/19/2010')

/*10.DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG*/

--KHAI BAO--

DECLARE CS10 CURSOR FOR

SELECT KHACHHANG.* FROM KHACHHANG

WHERE MAK IN(SELECT MAK FROM DATPHONG

WHERE CONVERT(CHAR(10),NGAYTRA)=CONVERT(CHAR(10),GETDATE()))

--MO--

OPEN CS10

--XU LY--

PRINT 'DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG LA'

PRINT 'MAKTENKDIACHISDT'

DECLARE @MAK CHAR(10),@TENK CHAR(30),@DIACHI CHAR(30),@SDT CHAR(15)

FETCH NEXT FROM CS10

INTO @MAK,@TENK,@DIACHI,@SDT

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MAK+@TENK+@DIACHI+@SDT

FETCH NEXT FROM CS10

INTO @MAK,@TENK,@DIACHI,@SDT

END

--DONG--

CLOSE CS10

--HUY--

DEALLOCATE CS10

Bai 13

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

SELECT PHONG.MAP,TENP,DIACHIP,SDT,COUNT(NHANVIEN.MAP) AS SONV

FROM PHONG,NHANVIEN

WHERE PHONG.MAP=NHANVIEN.MAP

GROUP BY phong.MAP,TENP,DIACHIP,SDT

--3/ Tạo View để tổng hợp thông tin về các nhân viên chưa tích luỹ được trình độ ngoại ngữ nào--

CREATE VIEW VD3

SELECT * FROM NHANVIEN

WHERE MANV NOT IN (SELECT MANV FROM TDNN)

--4/ Tạo View để tổng hợp thông tin về các nhân viên có lương cao nhất trong phòng họ làm việc.--

CREATE VIEW TG

SELECT MAP,MAX(LUONG) AS LUONGCAONHAT

FROM NHANVIEN

GROUP BY MAP

CREATE VIEW LUONGMAX

SELECT NHANVIEN.*

FROM NHANVIEN,TG

WHERE NHANVIEN.MAP=TG.MAP AND LUONG=LUONGCAONHAT

/*5/ Tạo thủ tục có tham số vào @TenNN và @TrinhDo để đưa ra danh các nhân viên biết ngoại ngữ

và trình độ trên. */

CREATE PROC VD5

@TENN CHAR(30),@TRINHDO CHAR(30)

SELECT * FROM NHANVIEN

WHERE MANV IN (SELECT MANV FROM TDNN

WHERE TRINHDO=@TRINHDO AND MANN IN (SELECT MANN FROM NGOAINGU

WHERE TENN=@TENN))

EXEC VD5 'TIENG ANH','A'

--6/ Tạo thủ tục có tham số vào @TenP để đưa ra tổng số nhân viên hiện có trong phòng này--

ALTER PROC VD6

@TENP CHAR(20)

SELECT PHONG.MAP,COUNT(NHANVIEN.MAP) AS SONV

FROM PHONG,NHANVIEN

WHERE PHONG.MAP=NHANVIEN.MAP AND TENP=@TENP

GROUP BY PHONG.MAP

EXEC VD6 'ABC'

/*7/ Tạo thủ tục có tham số vào là @TenNN, @TenP và @TrinhDo để đưa ra danh các nhân viên

ở phòng này biết ngoại ngữ và trình độ ứng với các tham biến đã cho */

CREATE PROC VD7

@TENNN CHAR(30),@TENP CHAR(20),@TRINHDO CHAR(30)

SELECT NHANVIEN.* FROM NHANVIEN,PHONG,TDNN,NGOAINGU

WHERE NHANVIEN.MANV=TDNN.MANV AND PHONG.MAP=NHANVIEN.MAP

AND NGOAINGU.MANN=TDNN.MANN AND TENN=@TENNN AND TENP=@TENP AND TRINHDO=@TRINHDO

EXEC VD7 'TIENG ANH','ABC','A'

/*8/ Tạo thủ tục có tham số vào @TenP để đưa ra danh các nhân viên có lương cao nhất của phòng này*/

CREATE PROC VD8

@TENP CHAR(20)

SELECT NHANVIEN.*

FROM NHANVIEN,TG,PHONG

WHERE NHANVIEN.MAP=TG.MAP AND TG.MAP=PHONG.MAP AND LUONG=LUONGCAONHAT AND TENP=@TENP

EXEC VD8 'ABC'

/*9/ Tạo Trigger để kiểm tra khi nhập dữ liệu vào bảng NhanVien phải đảm bảo lương của nhân viên

phải >0 */

CREATE TRIGGER tg9

ON NHANVIEN

FOR INSERT

IF EXISTS (SELECT LUONG FROM NHANVIEN

WHERE LUONG<0)

BEGIN

PRINT 'DU LIEU NHAP KHONG HOP LE'

ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO NHANVIEN

VALUES('NV6','HAI','HA NOI','P04','7000')

--10/ Dùng kiểu dữ liệu CURSOR để tăng lương cho mỗi nhân viên lên 10%.--

--KHAI BAO--

DECLARE CS10 CURSOR FOR

SELECT MANV,TENNV,DIACHI,MAP,(LUONG*1.1) AS LUONGMOI

FROM NHANVIEN

GROUP BY MANV,TENNV,DIACHI,MAP,LUONG

--MO--

OPEN CS10

--XU LY--

PRINT 'DANH SACH NHAN VIEN SAU KHI TANG LUONG LEN 10% LA:'

PRINT 'MANVTENNVDIACHI MAPLUONGMOI'

DECLARE @MANV CHAR(10),@TENNV CHAR(20),@DIACHI CHAR(20),@MAP CHAR(10),@LUONG INT

FETCH NEXT FROM CS10

INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MANV+@TENNV+@DIACHI+@MAP+CONVERT(CHAR(10),@LUONG)

FETCH NEXT FROM CS10

INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

END

--DONG--

CLOSE CS10

--HUY--

DEALLOCATE CS10

--11/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các nhân viên có ngoại ngữ tiếng anh trình độ C--

--KHAI BAO--

DECLARE CS11 CURSOR FOR

SELECT NHANVIEN.MANV,TENNV,DIACHI,MAP,LUONG

FROM NHANVIEN,TDNN,NGOAINGU

WHERE NHANVIEN.MANV=TDNN.MANV AND TDNN.MANN=NGOAINGU.MANN

AND TENN='TIENG ANH' AND TRINHDO='C'

--MO--

OPEN CS11

--XU LY--

PRINT 'DANH SACH NHAN VIEN CO NGAOI NGU TIENG ANH TRINH DO C LA:'

PRINT 'MANVTENNVDIACHI MAPLUONG'

DECLARE @MANV CHAR(10),@TENNV CHAR(20),@DIACHI CHAR(20),@MAP CHAR(10),@LUONG INT

FETCH NEXT FROM CS11

INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MANV+@TENNV+@DIACHI+@MAP+CONVERT(CHAR(10),@LUONG)

FETCH NEXT FROM CS11

INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

END

--DONG--

CLOSE CS11

--HUY--

DEALLOCATE CS11

Bai 14

CREATE DATABASE QUANLYTRINHDOHOCVINHANVIEN

USE QUANLYTRINHDOHOCVINHANVIEN

CREATE TABLE PHONG(

MAP CHAR(10) PRIMARY KEY,

TENP CHAR(30) NOT NULL,

DIACHIP CHAR(30),

SDT CHAR(15)

CREATE TABLE NHANVIEN(

MANV CHAR(10) PRIMARY KEY,

TENNV CHAR(40) NOT NULL,

DIACHI CHAR(40),

MAP CHAR(10),

LUONG INT,--LUONG CUA NHAN VIEN--

CONSTRAINT KN_NHANVIEN FOREIGN KEY(MAP) REFERENCES PHONG(MAP)

CREATE TABLE HOCVI(

MAHV CHAR(10) PRIMARY KEY,

TENHV CHAR(30) NOT NULL

CREATE TABLE TDHVNV(--TRINH DO HOC VI NHAN VIEN--

MANV CHAR(10) ,

MATDHV CHAR(10),

THOIGIANSMALLDATETIME,--NGAY THANG NAM DAT TRINH DO NAY--

CONSTRAINT KC_TDHVNV PRIMARY KEY(MANV,MATDHV),

CONSTRAINT KN_TDHVNV FOREIGN KEY(MANV) REFERENCES NHANVIEN(MANV)

--2/ Tạo View để tổng hợp thông tin về các nhân viên có học vị là tiến sĩ trước năm 1990.--

CREATE VIEW VD2

SELECT * FROM NHANVIEN

WHERE

--3/ Tạo View để tổng hợp thông tin về các nhân viên chưa có học vị Tiến sĩ.--

/*4/ Tạo thủ tục có tham số vào là @TenHV và @ThoiGian để đưa ra danh các nhân viên có học vị

và thời gian đạt được ứng với các tham biến đã cho. */

/*5/ Tạo thủ tục có tham số vào là @TenHV và @ThoiGian để đưa ra danh các nhân viên có

trình độ học vị và thời gian đạt được học vị đó ứng với các tham biến đã cho. */

/*6/ Tạo trigger để kiểm tra dữ liệu khi nhập vào bảng NV_TDHV sao cho thời gian đạt học vị

đó phải <= thời gian hiện tại.*/

/*7/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các nhân viên có học vị Tiến sĩ.*/

Bài 15

CREATE DATABASE QUANLYDIEMRENLUYEN

USE QUANLYDIEMRENLUYEN

CREATE TABLE LOP(

MAL CHAR(10) PRIMARY KEY,

TENL CHAR(20) NOT NULL

CREATE TABLE SV(

MASV CHAR(10) PRIMARY KEY,

TENSV CHAR(30) NOT NULL,

NGAYSINH SMALLDATETIME,

MAL CHAR(10),

CONSTRAINT KN_SV FOREIGN KEY (MAL) REFERENCES LOP(MAL)

CREATE TABLE DIEMRL(

MASV CHAR(10),

HOCKY INT,

NAM CHAR(4),

DIEM INT--DIEM REN LUYEN--

CONSTRAINT KC_DIEMRL PRIMARY KEY(MASV,HOCKY,NAM),

CONSTRAINT KN_DIEMRL FOREIGN KEY (MASV) REFERENCES SV(MASV)

--2. Hãy tạo view để tổng hợp thông tin về điểm rèn luyện của những sinh viên học lớp “Cao đẳng K4A”--

CREATE VIEW VD2

SELECT * FROM DIEMRL

WHERE MASV IN (SELECT MASV FROM SV

WHERE MAL IN (SELECT MAL FROM LOP

WHERE TENL='CAO DANG K4A'))

--3. Hãy tạo view để in ra thông tin về sinh viên có điểm rèn luyện cao nhất.--

ALTER VIEW VD3

SELECT *

FROM SV

WHERE MASV IN (SELECT MASV FROM DIEMRL

WHERE DIEM IN (SELECT MAX(DIEM) FROM DIEMRL))

--4. Tạo view để tổng hợp thông tin về những sinh viên có điểm rèn luyện<50 trong năm học 2006-2007.--

CREATE VIEW VD4

SELECT * FROM SV

WHERE MASV IN(SELECT MASV FROM DIEMRL

WHERE DIEM<50 AND NAM ='2006-2007')

--5. Tạo thủ tục có tham số vào @Masv để đưa ra thông tin về điểm rèn luyện của sinh viên trên.--

CREATE PROC VD5

@MASV CHAR(10)

SELECT * FROM DIEMRL

WHERE MASV =@MASV

EXEC VD5 'SV2'

/*6. Tạo thủ tục có tham số vào @Malop, @Nam để đưa ra thông tin về điểm rèn luyện của lớp trên vào

nam học trên.*/

CREATE PROC VD6

@MAL CHAR(10),@NAM CHAR(4)

SELECT * FROM DIEMRL

WHERE NAM=@NAM AND MASV IN (SELECT MASV FROM SV

WHERE MAL=@MAL)

EXEC VD6 'L01','2010'

--7. Tạo thủ tục có tham số vào @tenlop để đưa ra thông tin về điểm rèn luyện của lớp trên.--

CREATE PROC VD7

@TENL CHAR(20)

SELECT * FROM DIEMRL

WHERE MASV IN (SELECT MASV FROM SV

WHERE MAL IN (SELECT MAL FROM LOP

WHERE TENL=@TENL))

EXEC VD7 'TH'

/*8. Tạo Trigger kiểm tra việc nhập dữ liệu cho bảng DIEMRL nếu điểm nhập vào <0 hoặc >100 thì đưa

ra yêu cầu nhập lại và bản ghi này không được phép nhập vào bảng, ngược lại thì in ra thông báo nhập

thành công.*/

CREATE TRIGGER TG8

ON DIEMRL

FOR INSERT

IF EXISTS (SELECT DIEM FROM DIEMRL

WHERE DIEM<0 OR DIEM >100)

BEGIN

PRINT 'DU LIEU NHAP KHONG HOP LE'

ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO DIEMRL

VALUES('SV4','2','2007',90)

/*9. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của học kỳ 1 năm học

2007-2008.*/

DECLARE CS9 CURSOR FOR

SELECT DIEMRL.*

FROM DIEMRL

WHERE HOCKY='1' AND NAM='2007-2008'

--MO--

OPEN CS9

--XU LY--

PRINT 'DANH SACH DIEM REN LUYEN CUA HOC KY 1 NAM HOC 2007-2008 LA:'

PRINT 'MASV HOCKY NAM DIEM'

DECLARE @MASV CHAR(10),@HOCKY INT,@NAM CHAR(20),@DIEM INT

FETCH NEXT FROM CS9

INTO @MASV,@HOCKY,@NAM,@DIEM

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MASV+CONVERT(CHAR(10),@HOCKY)+@NAM+CONVERT(CHAR(10),@DIEM)

FETCH NEXT FROM CS9

INTO @MASV,@HOCKY,@NAM,@DIEM

END

--DONG--

CLOSE CS9

--HUY--

DEALLOCATE CS9

--10. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của năm học 2006-2007.--

--KHAI BAO--

DECLARE CS10 CURSOR FOR

SELECT DIEMRL.*

FROM DIEMRL

WHERE NAM='2006-2007'

--MO--

OPEN CS10

--XU LY--

PRINT 'DANH SACH DIEM REN LUYEN CUA HOC KY 1 NAM HOC 2007-2008 LA:'

PRINT 'MASV HOCKY NAM DIEM'

DECLARE @MASV CHAR(10),@HOCKY INT,@NAM CHAR(20),@DIEM INT

FETCH NEXT FROM CS10

INTO @MASV,@HOCKY,@NAM,@DIEM

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MASV+CONVERT(CHAR(10),@HOCKY)+@NAM+CONVERT(CHAR(10),@DIEM)

FETCH NEXT FROM CS10

INTO @MASV,@HOCKY,@NAM,@DIEM

END

--DONG--

CLOSE CS10

--HUY--

DEALLOCATE CS10

Quản lí thư viện

THAY MẶT NHÓM EM GỬI CHO CÔ BÀI TẬP THẢO LUẬN 3+4

NHÓM GỒM: NGUYỄN VĂN SÁU

TRẦN VĂN HAI

VŨ HỒNG HẢI

--------------------------=========================---------------------------------------

CREATE DATABASE QLTHUVIEN

USE QLTHUVIEN

CREATE TABLE SACH

MAS CHAR (5) PRIMARY KEY,

TENS NVARCHAR(20) NOT NULL,

NAMXB CHAR(5),

NXB NVARCHAR(50),

TENTG NVARCHAR(30)

CREATE TABLE DG

MADG CHAR(5) PRIMARY KEY,

TENDG NVARCHAR(30) NOT NULL,

DCDG NVARCHAR(20),

CREATE TABLE MT

MADG CHAR(5)NOT NULL,

MAS CHAR(5) NOT NULL,

NGAYM SMALLDATETIME NOT NULL,

SL INT NOT NULL,

NGAYHT SMALLDATETIME,

NGAYT SMALLDATETIME,

CONSTRAINT KC1 PRIMARY KEY (MADG,MAS,NGAYM),

CONSTRAINT KN3 FOREIGN KEY (MADG) REFERENCES DG(MADG),

CONSTRAINT KN4 FOREIGN KEY (MAS) REFERENCES SACH (MAS),

INSERT INTO SACH VALUES ('S06','TIN HOC DAI CUONG','2001','ĐẠI HỌC THÁI NGUYÊN','NGUYỄN THỊ OANH')

INSERT INTO DG VALUES('DG06','NGUYỄN VĂN SÁU','LỚP THKTK7A')

INSERT INTO MT VALUES ('DG06','S02','10/08/2010','1','10/31/2010',NULL)

DELETE MT

--1.1 TẠO VIEW TỔNG HỢP THÔNG TIN VỀ CÁC ĐỘC GIẢ ĐÃ HẾT HẠN TRẢ SÁCH NHƯNG CHƯA TRẢ--

CREATE VIEW TTDG

SELECT *

FROM DG

WHERE MADG IN (SELECT MADG

FROM MT

WHERE DATEDIFF(DAY,GETDATE(),NGAYHT)<=0

AND NGAYT IS NULL

SELECT * FROM TTDG

------------------------------------------------------------------------------------

--1.2 TẠO VIEW ĐỂ TỔNG TỢP THÔNG TIN VỀ DANH MỤC SÁCH CHƯA TỪNG ĐƯỢC ĐỘC GIẢ NÀO MƯỢN--

CREATE VIEW TT1

SELECT *

FROM SACH

WHERE MAS NOT IN (SELECT MAS

FROM MT)

SELECT * FROM TT1

--------------------------------------------------------------------------------------

-- 1.3. TẠO VIEW TỔNG HỢP THÔNG TIN VỀ TỪNG LOẠI SÁCH HIỆN CÓ ĐỘC GIẢ ĐANG MƯỢN

--(CẤU TRÚC VIEW GỒM CÁC THUỘC TÍNH : MASACH,TENSACH,TENDG,SL)

CREATE VIEW TTS

SELECT SACH.TENS, SACH.MAS,DG.TENDG,MT.SL

FROM SACH,DG,MT

WHERE SACH.MAS=MT.MAS

AND DG.MADG=MT.MADG

AND MT.MAS IN(SELECT MAS

FROM MT

WHERE NGAYT IS NULL)

-------------------------------------------------------------------------------------------

--1.4.TAO THU TUC CO THAM SO LA @MADG DE DUA RA DANH MUC SACH MA DOC GIA TREN DANG MUON

CREATE PROC DMS

@MADG CHAR(5)

SELECT *

FROM SACH

WHERE MAS IN(SELECT MAS

FROM MT

WHERE MADG=@MADG)

DMS @MADG='DG03'

---------------------------------------------------------------------------------------------------------

--1.5. TAO THU TUC CO THAM SO LA @MGAYMUON DE DUA RA DANH SACH CAC DOC GIA DA MUON SACH VAO NGAY TREN

CREATE PROC DSDG

@NGAYMUON SMALLDATETIME

SELECT *

FROM DG

WHERE MADG IN (SELECT MADG

FROM MT

WHERE NGAYM = @NGAYMUON)

DSDG '10/8/2010'

----------------------------------------------------------------------------------------------------------------

--1.6. TAO THU TUC DE DUA RA DANH SACH CAC DOC GIA NGAY HOM NAY LA HAN CUOI CUNG PHAI TRA SACH CHO THU VIEN

CREATE PROC HANTRASACH

SELECT *

FROM DG

WHERE MADG IN(SELECT MADG

FROM MT

WHERE DATEDIFF(DAY,GETDATE(),NGAYHT)=0)

HANTRASACH

SELECT * FROM SACH

SELECT * FROM DG

SELECT * FROM MT

Bạn đang đọc truyện trên: Truyen2U.Pro

Trước Sau

#01692530982 #bai #biến #boydb #cơ #dbp #diễn #dự #giai #hệ #liêu #loi #phụ #quan #số #teendb #tri #tập #và #wap #đbp #điên