31 trang 6 lượt tải

Bài Tập Cơ Sở Dữ Liệu MS SQL Server - Các Bài Tập Thực Hành

12

Bài Tập Cơ Sở Dữ Liệu MS SQL Server - Các Bài Tập Thực Hành  

Môn: Marketing quốc tế 66 tài liệu

Trường: Trường Đại học Kinh Tế Quốc Dân 5.3 K tài liệu

Tác giả:

Profile

Linh Đan

2 tuần trước
Tải xuống Chia sẻ Báo cáo
Danh sách Quiz
Tải xuống
/31
DATABASE EXERCISES – MS SQL SERVER 2008
Bài 1: CSDL Quản lý học viên.
I. Tạo DB:
IF DB_ID('QuanLyHocVien') IS NULL
CREATE DATABASE QuanLyHocVien;
II. Tạo bảng:
-- Tao QH MONHOC --
USE QuanLyHocVien;
IF OBJECT_ID('MONHOC') IS NOT NULL
DROP TABLE MONHOC
CREATE TABLE MONHOC
(
MAMH NVARCHAR(30) NOT NULL PRIMARY KEY,
TENMH NVARCHAR(30) NOT NULL UNIQUE,
SOTC INT NOT NULL
);
INSERT INTO MONHOC(MAMH, TENMH, SOTC)
VALUES ('M01', N'Cơ sở dữ liệu', 3),
('M02', N'Thiết kế web', 3),
('M03', N'Xử lý ảnh', 4);
-- Tao QH LOP --
USE QuanLyHocVien;
IF OBJECT_ID('LOP') IS NOT NULL
DROP TABLE LOP
CREATE TABLE LOP
(
MALOP NVARCHAR(30) NOT NULL PRIMARY KEY,
TENLOP NVARCHAR(30) NOT NULL UNIQUE
);
INSERT INTO LOP(MALOP, TENLOP)
VALUES ('L01', N'Công nghệ TT 2009'),
('L02', N'Quản trị 2009');
-- Tao QH HV --
USE QuanLyHocVien;
IF OBJECT_ID('HV') IS NOT NULL
DROP TABLE HV
CREATE TABLE HV
(
MAHV NVARCHAR(30) NOT NULL PRIMARY KEY,
1
TENHV NVARCHAR(30) NOT NULL,
DCHV NVARCHAR(30),
MALOP NVARCHAR(30) REFERENCES LOP
);
INSERT INTO HV(MAHV, TENHV, DCHV, MALOP)
VALUES ('H01', N'Nguyễn Văn A', 'Q1', 'L01'),
('H02', N'Nguyễn Văn B', 'Q2', 'L01'),
('H03', N'Nguyễn Văn C', 'Q1', 'L01'),
('H04', N'Nguyễn Văn B', 'Q3', 'L02'),
('H05', N'Nguyễn Văn A', 'Q3', 'L02'),
('H06', N'Nguyễn Văn A', 'Q2', 'L03');
-- Tao QH DIEMHV --
USE QuanLyHocVien;
IF OBJECT_ID('DIEMHV') IS NOT NULL
DROP TABLE DIEMHV
CREATE TABLE DIEMHV
(
MAHV NVARCHAR(30) REFERENCES HV,
MAMH NVARCHAR(30) REFERENCES MONHOC,
DIEM INT,
CONSTRAINT Diem PRIMARY KEY (MAHV, MAMH)
);
INSERT INTO DIEMHV(MAHV, MAMH, DIEM)
VALUES ('H01', 'M01', 5),
('H01', 'M02', 4),
('H02', 'M01', 7),
('H02', 'M02', 5),
('H02', 'M03', 6),
('H03', 'M01', 7);
III. Trả lời bằng T-SQL.
III.1
Cho biết tên môn học và số tín chỉ của môn học mã số “M01”.
USE QuanLyHocVien;
SELECT TENMH, SOTC
FROM MONHOC
WHERE MAMH = 'M03';
III.2
Liệt kê các học viên (mã số và tên) của lớp có tên “Quản trị 2009”.
USE QuanLyHocVien;
SELECT hv.MAHV, hv.TENHV
FROM LOP AS L , HV AS hv
WHERE L.MALOP = hv.MALP
AND TENLOP = N'Quản trị 2009';
III.3
Liệt kê điểm và tên môn học của học viên mã số “H01”.
USE QuanLyHocVien;
2
select mh.TENMH, d.DIEM
FROM MONHOC AS mh , DIEMHV AS d
WHERE mh.MAMH = d.MAMH
AND d.MAHV = 'H01';
III.4
Cho biết mã số và tên học viên chưa đạt điểm môn “Cơ sở dữ liệu”.
USE QuanLyHocVien;
select hv.MAHV, hv.TENHV
FROM DIEMHV AS d, HV AS hv
WHERE d.DIEM < 5
AND d.MAMH = 'M01'
AND hv.MAHV = d.MAHV;
III.5
Cho biết các môn học mà học viên mã số “H01” chưa thi.
USE QuanLyHocVien;
SELECT mh.MAMH, mh.TENMH
FROM monhoc AS mh
WHERE MAMH NOT IN (SELECT MAMH FROM DIEMHV WHERE MAHV = 'H01');
III.6
Liêt kê những học viên có điểm “M01” lơn nhất.
USE QuanLyHocVien;
SELECT hv.MAHV, hv.TENHV, d.DIEM
FROM HV AS hv,DIEMHV AS d
WHERE d.MAMH = 'M01'
AND DIEM = (SELECT MAX(DIEM) FROM DIEMHV)
AND hv.MAHV = d. MAHV;
III.7
Liêt kê những học viên có điểm môn “M01” lớn nhất.
USE QuanLyHocVien;
SELECT hv.MAHV, hv.TENHV, d.DIEM
FROM HV AS hv, DIEMHV AS d
WHERE d.DIEM = (SELECT MAX(DIEM) FROM DIEMHV WHERE MAMH = 'M01')
AND hv.MAHV = d.MAHV;
3
III.8
Cho biết sĩ số từng lớp.
USE QuanLyHocVien;
SELECT L.TENLOP, COUNT(*) AS 'Sĩ số'
FROM HV AS hv, LOP AS L
WHERE hv.MALOP = L.MALOP
GROUP BY L.TENLOP;
Bài 2: CSDL Hóa Đơn Bán Lẻ
I. Tạo DB
--- Bai 2 Tao DB Hoa Don Ban Le ---
IF DB_ID('HoaDonBanLe') IS NULL
CREATE DATABASE HoaDonBanLe;
II. Tạo Table
--- Tao QH HANG ---
USE HoaDonBanLe;
IF OBJECT_ID('HANG') IS NOT NULL
DROP TABLE HANG
CREATE TABLE HANG
(
MAHG NVARCHAR(30) NOT NULL PRIMARY KEY,
TENHG NVARCHAR(30) NOT NULL,
DONGIA INT NOT NULL
);
INSERT INTO HANG(MAHG, TENHG, DONGIA)
VALUES ('H01', N'Hàng A', 150),
('H02', N'Hàng B', 70),
('H03', N'Hàng C', 200);
--- Tao QH Hoa Don ---
USE HoaDonBanLe;
IF OBJECT_ID('HOADON') IS NOT NULL
DROP TABLE HOADON
CREATE TABLE HOADON
(
MAHD NVARCHAR(30) NOT NULL PRIMARY KEY,
NGAYBAN DATE NOT NULL,
NOIDUNG NVARCHAR(30)
);
INSERT INTO HOADON(MAHD, NGAYBAN, NOIDUNG)
VALUES ('HD01', '20090912', N'Giao hàng liền'),
('HD02', '20090915', N'Khách vãng lai'),
('HD03', '20090915', N'xxxxxxxx');
4
--- Tao QH CTHD ---
USE HoaDonBanLe;
IF OBJECT_ID('CTHD') IS NOT NULL
DROP TABLE CTHD
CREATE TABLE CTHD
(
MAHD NVARCHAR(30) REFERENCES HOADON NOT NULL,
MAHG NVARCHAR(30) NOT NULL REFERENCES HANG,
SL INT,
CONSTRAINT HD_detail PRIMARY KEY (MAHD, MAHG)
);
INSERT INTO CTHD(MAHD, MAHG, SL)
VALUES ('HD01', 'H01', 10),
('HD01', 'H03', 12),
('HD02', 'H01', 34),
('HD02', 'H02', 15),
('HD02', 'H03', 3),
('HD03', 'H02', 2),
('HD03', 'H03', 16);
III. Trả lời bằng T-SQL
III.1
Liệt kê các mặt hàng có đơn giá từ 100 đến 200.
USE HoaDonBanLe;
SELECT MAHG, TENHG, DONGIA
FROM HANG
WHERE DONGIA >= 100 AND DONGIA <= 200;
III.2
Các mặt hàng ( Mã, tên, đơn giá ) được bán trong ngày “15/09/2009”.
SELECT h.MAHG, h.TENHG, h.DONGIA
FROM HANG AS h
WHERE h.MAHG IN
(SELECT chitiet.MAHG
FROM CTHD AS chitiet
JOIN HOADON AS hd
ON chitiet.MAHD = hd.MAHD and hd.NGAYBAN = '20090915');
III.3
Tổng số lượng bán của từng hóa đơn.
SELECT DISTINCT ct.MAHD, SUM(ct.SL) AS 'Tong SL ban'
FROM CTHD AS ct
GROUP BY ct.MAHD;
III.4
Trị giá bán từ ngày d1 đến ngày d2.
Giả sử có 2 biến kiểu ngày;
d1 = 13/09/2009
d2 = 16/09/2009
5
SELECT SUM(ct.SL*h.DONGIA) AS 'Tong gia tri'
FROM CTHD AS ct, HANG AS h
WHERE ct.MAHD IN
(SELECT MAHD
FROM HOADON
WHERE NGAYBAN >= 'd1' AND NGAYBAN <= 'd2');
III.5
Tổng số lượng bán của từng hóa đơn trong ngày “15/09/2009”.
SELECT DISTINCT ct.MAHD, SUM(ct.SL) AS 'Tong'
FROM CTHD AS ct
WHERE ct.MAHD IN (SELECT MAHD
FROM HOADON
WHERE NGAYBAN = '20090915')
GROUP BY ct.MAHD;
III.6
Trị giá của từng hóa đơn bán từ ngày “12/09/2009” đến “15/09/2009.
SELECT DISTINCT ct.MAHD, SUM(ct.SL * h.DONGIA) AS 'Tong'
FROM CTHD AS ct, HANG AS h
WHERE ct.MAHD IN
(SELECT MAHD
FROM HOADON
WHERE NGAYBAN >= '20090912' AND NGAYBAN <= '20090915')
AND ct.MAHG = h.MAHG
GROUP BY ct.MAHD;
III.7
Từ ngày “12/09/2009” đến “15/09/2009” hóa đơn nào có giá trị lớn nhất.
USE HoaDonBanLe;
SELECT CT.MAHD, SUM(CT.SL * H.DONGIA) AS 'Tổng'
FROM CTHD AS CT, HANG AS H
WHERE CT.MAHG = H.MAHG
AND CT.MAHD IN
(SELECT MAHD FROM HOADON
WHERE NGAYBAN >= '20090912' AND NGAYBAN <= '20090915')
GROUP BY CT.MAHD
HAVING SUM(CT.SL * H.DONGIA) >=
ALL (SELECT SUM(CT.SL * H.DONGIA)
FROM CTHD AS CT, HANG AS H
WHERE CT.MAHG = H.MAHG
AND CT.MAHD IN
(SELECT MAHD FROM HOADON
WHERE NGAYBAN >= '20090912'
AND NGAYBAN <= '20090915')
GROUP BY CT.MAHD);
Bài 3: CSDL Kho Hàng
I. Tạo DB
--- Bai 3 CSDL KHOHANG ---
6
IF DB_ID('KhoHang') IS NULL
CREATE DATABASE KhoHang;
II. Tạo QH.
--- Tao QH LOAIHANG
--- Tan tu: Danh muc cac loai hang
USE KhoHang;
IF OBJECT_ID('LOAIHANG') IS NOT NULL
DROP TABLE LOAIHANG
CREATE TABLE LOAIHANG
(
MALH NVARCHAR(30) NOT NULL,
TENLH NVARCHAR(30) NOT NULL,
CONSTRAINT PK_LOAIHANG PRIMARY KEY (MALH),
CONSTRAINT Uni_MaHang UNIQUE (TENLH)
);
INSERT INTO LOAIHANG (MALH,TENLH)
VALUES ('L01', N'Nước giải khát'),
('L02', N'Trà khô');
--- Tao QH HANG
--- Tan tu: Danh muc hang
USE KhoHang;
IF OBJECT_ID('HANG') IS NOT NULL
DROP TABLE HANG
CREATE TABLE HANG
(
MAHG NVARCHAR(30) NOT NULL,
TENHG NVARCHAR(30) NOT NULL,
MALH NVARCHAR(30) NOT NULL REFERENCES LOAIHANG,
CONSTRAINT PK_HANG PRIMARY KEY (MAHG),
CONSTRAINT Uni_TenHang UNIQUE (TENHG)
);
INSERT INTO HANG (MAHG, TENHG, MALH)
VALUES ('H01', N'Trà xanh', 'L01'),
('H02', N'Pepsi', 'L01'),
('H03', N'7 Up', 'L01'),
('H04', N'Trà Tân cương', 'L01'),
('H05', N'Trà Bảo Lộc', 'L01');
select * from HANG
-- Tao QH KHOHG
-- Tan tu: Danh muc cac kho hang, moi kho hang chi duoc chua mot loai
hang
USE KhoHang;
IF OBJECT_ID('KHOHANG') IS NOT NULL
DROP TABLE KHOHANG
CREATE TABLE KHOHANG
(
MAKH NVARCHAR(30) NOT NULL,
TENKH NVARCHAR(30) NOT NULL,
DCKH NVARCHAR(30),
MALH NVARCHAR(30) NOT NULL,
CONSTRAINT PK_KHOHANG PRIMARY KEY (MAKH),
CONSTRAINT UNI_TENKH UNIQUE (TENKH)
7
);
INSERT INTO KHOHANG (MAKH, TENKH, DCKH, MALH)
VALUES ('K01', 'Kho A', 'Q1', 'L01'),
('K02', 'Kho B', 'Q1', 'L01'),
('K03', 'Kho C', 'Q2', 'L02');
-- Tao QH KHOCHUA
-- Tan tu: Hiện trạng chứa hàng của các kho hàng.
USE KhoHang;
IF OBJECT_ID('KHOCHUA') IS NOT NULL
DROP TABLE KHOCHUA
CREATE TABLE KHOCHUA
(
MAKH NVARCHAR(30) NOT NULL REFERENCES KHOHANG,
MAHG NVARCHAR(30) NOT NULL REFERENCES HANG,
SL INT,
CONSTRAINT PK_KHOCHUA PRIMARY KEY (MAKH, MAHG)
);
INSERT INTO KHOCHUA (MAKH, MAHG, SL)
VALUES ('K01', 'H01', '50'),
('K01', 'H02', '30'),
('K02', 'H03', '100'),
('K03', 'H04', '40'),
('K03', 'H05', '50');
III. Trả lời bằng T-SQL.
III.1
Các mặt hàng của loại hàng mã số “L01”
USE KhoHang;
SELECT MAHG, TENHG
FROM HANG
WHERE MALH = 'L01';
III.2
Các mặt hàng (mã số và tên) có thể chứa được trong kho mã số “K01”
USE KhoHang;
SELECT MAHG, TENHG
FROM HANG
WHERE MALH IN (SELECT MALH FROM KHOHANG WHERE MAKH = 'K01');
III.3
Các mặt hàng (mã số và tên) hiên chứa trong kho mã số "K01"
USE KhoHang;
SELECT MAHG, TENHG
FROM HANG
WHERE MAHG IN (SELECT MAHG FROM KHOCHUA WHERE MAKH = 'K01');
III.4
Tổng số lượng chứa của từng kho
USE KhoHang;
SELECT kh.MAKH, kh.TENKH, SUM(kc.SL) AS 'Tổng Số lượng'
8
FROM KHOHANG AS kh, KHOCHUA AS kc
WHERE kh.MAKH = kc.MAKH
GROUP BY kh.MAKH, kh.TENKH;
III.5
Kho hàng nào chứa nhiều mặt hàng nhất
USE KhoHang;
SELECT kh.MAKH, kh.TENKH, COUNT(*) AS 'Số mặt hàng'
FROM KHOHANG AS kh, KHOCHUA AS kc
WHERE kh.MAKH = kc.MAKH
GROUP BY kh.MAKH, kh.TENKH
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM KHOHANG AS kh, KHOCHUA AS kc
WHERE kh.MAKH = kc.MAKH
GROUP BY kh.MAKH, kh.TENKH);
III.6
Kho hàng nào có tổng số lượng chứa nhiều nhất
USE KhoHang;
SELECT kh.MAKH, kh.TENKH, SUM(kc.SL) AS 'Tổng'
FROM KHOHANG AS kh, KHOCHUA AS kc
WHERE kh.MAKH = kc.MAKH
GROUP BY kh.MAKH, kh.TENKH
HAVING SUM(kc.SL) >= ALL (SELECT SUM(kc.SL)
FROM KHOHANG AS kh, KHOCHUA AS kc
WHERE kh.MAKH = kc.MAKH
GROUP BY kh.MAKH, kc.MAKH);
III.7
Tổng số lượng tồn kho của từng mặt hàng
USE KhoHang;
SELECT h.MAHG, h.TENHG, kc.SL
FROM HANG AS h, KHOCHUA AS kc
WHERE h.MAHG = kc.MAHG;
III.8
Loại hàng nào tồn kho nhiều nhất
USE KhoHang;
SELECT lh.MALH, TENLH, SUM(kc.SL) AS 'Số lượng tồn kho'
FROM LOAIHANG AS lh, HANG AS h, KHOCHUA AS kc
WHERE h.MAHG = kc.MAHG
AND lh.MALH = h.MALH
GROUP BY lh.MALH, TENLH
HAVING SUM(kc.SL) >= ALL (SELECT SUM(kc.SL)
FROM LOAIHANG AS lh, HANG AS h, KHOCHUA AS kc
WHERE h.MAHG = kc.MAHG
AND lh.MALH = h.MALH
GROUP BY lh.MALH, lh.TENLH);
9
Bài 4: CSDL Quản Lý Dự Án
I. Tạo DB
-- Bai 4 CSDL Quản Lý Dự Án
IF DB_ID('QuanLyDuAn') IS NULL
CREATE DATABASE QuanLyDuAn;
II. Tạo QH
-- Tao QH PHONGBAN
-- Tân từ: Danh sách các phòng ban
USE QuanLyDuAn;
IF OBJECT_ID('PHONGBAN') IS NOT NULL
DROP TABLE PHONGBAN
CREATE TABLE PHONGBAN
(
MAPB NVARCHAR(30) NOT NULL,
TENPB NVARCHAR(30) NOT NULL,
CONSTRAINT PK_PHONGBAN PRIMARY KEY (MAPB)
);
INSERT INTO PHONGBAN (MAPB,TENPB)
VALUES ('P01', N'Phòng Marketing'),
('P02', N'Phòng Nhân sự');
--- Tao QH NHANVIEN
--- Tân từ: Danh sách nhân viên
USE QuanLyDuAn;
IF OBJECT_ID('NHANVIEN') IS NOT NULL
DROP TABLE NHANVIEN
CREATE TABLE NHANVIEN
(
MANV NVARCHAR(30) NOT NULL,
TENNV NVARCHAR(30) NOT NULL,
DCNV NVARCHAR(30),
MAPB NVARCHAR(30) NOT NULL REFERENCES PHONGBAN,
CONSTRAINT PK_NHANVIEN PRIMARY KEY (MANV)
);
INSERT INTO NHANVIEN (MANV, TENNV, DCNV, MAPB)
VALUES ('N01', N'VƯƠNG NGỌC A', N'QUẢNG NAM', 'P01'),
('N02', N'VƯƠNG NGỌC B', N'QUẢNG NGÃI', 'P01'),
('N03', N'VƯƠNG NGỌC C', N'BÌNH ĐỊNH', 'P02'),
('N04', N'VƯƠNG NGỌC D', N'PHÚ YÊN', 'P02');
-- Tao QH DUAN
-- Tân từ: Danh sách dự án
USE QuanLyDuAn;
IF OBJECT_ID('DUAN') IS NOT NULL
DROP TABLE DUAN
CREATE TABLE DUAN
(
10
MADA NVARCHAR(30) NOT NULL,
TENDA NVARCHAR(30) NOT NULL,
TGDA NVARCHAR(30),
MAPB NVARCHAR(30) NOT NULL REFERENCES PHONGBAN,
CONSTRAINT PK_DUAN PRIMARY KEY (MADA)
);
INSERT INTO DUAN (MADA, TENDA, TGDA, MAPB)
VALUES ('D01', N'Dự Án A', '', 'P01'),
('D02', N'Dự Án B', '', 'P01'),
('D03', N'Dự Án C', '', 'P02');
-- Tao QH PHANCONG
-- Tân từ: Nhân viên chỉ được phân công vào các dư án thuộc phòng ban
của nhân viên đó
USE QuanLyDuAn;
IF OBJECT_ID('PHANCONG') IS NOT NULL
DROP TABLE PHANCONG
CREATE TABLE PHANCONG
(
MADA NVARCHAR(30) NOT NULL REFERENCES DUAN,
MANV NVARCHAR(30) NOT NULL REFERENCES NHANVIEN,
CONSTRAINT PK_PHANCONG PRIMARY KEY (MADA, MANV)
);
INSERT INTO PHANCONG (MADA, MANV)
VALUES ('D01', 'N01'),
('D01', 'N02'),
('D02', 'N03'),
('D03', 'N04');
III. Trả lời bằng T-SQL
III.1
Liệt kê danh sách nhân viên (mã số và tên) của phòng ban "P01"
USE QuanLyDuAn;
SELECT MANV, TENNV
FROM NHANVIEN
WHERE MAPB = 'P01';
III.2
Liệt kê DS nhân viên (mã số và tên) có thể được phân công vào dự án "D01"
USE QuanLyDuAn;
SELECT MANV, TENNV
FROM NHANVIEN AS nv, DUAN AS da
WHERE nv.MAPB = da.MAPB
AND da.MADA = 'D01';
III.3
Nhân viên mã số "N01" có thể được phân công vào dự án nào
USE QuanLyDuAn;
SELECT nv.MANV, nv.TENNV, da.MADA, da.TENDA
FROM NHANVIEN AS nv, DUAN AS da
WHERE nv.MANV = 'N01'
AND nv.MAPB = da.MAPB;
11
III.4
Cho biết nhân viên (mã số và tên) có thể được phân công vào dự án "D02" nhưng
chưa được phân công
USE QuanLyDuAn;
SELECT nv.MANV, nv.TENNV
FROM NHANVIEN AS nv, DUAN AS da
WHERE nv.MAPB = da.MAPB
AND da.MADA = 'D02'
AND da.MADA NOT IN (SELECT MADA FROM PHANCONG);
III.5
Số lượng nhân viên được phân công vào từng dự án
USE QuanLyDuAn;
SELECT da.MADA, da.TENDA, COUNT(*) AS 'Số nhân viên'
FROM PHANCONG AS pc, DUAN AS da
WHERE da.MADA = pc.MADA
GROUP BY da.MADA, da.TENDA;
III.6
Dự án nào phân công nhiều nhân viên nhất
USE QuanLyDuAn;
SELECT da.MADA, da.TENDA, COUNT(*) AS 'Số nhân viên'
FROM PHANCONG AS pc, DUAN AS da
WHERE da.MADA = pc.MADA
GROUP BY da.MADA, da.TENDA
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM PHANCONG AS pc, DUAN AS da
WHERE da.MADA = pc.MADA
GROUP BY da.MADA, da.TENDA);
III.7
Phong ban nào có nhiều dự án nhất
USE QuanLyDuAn;
SELECT pb.MAPB, pb.TENPB, COUNT(*) AS 'Số dự án'
FROM PHONGBAN AS pb, DUAN AS da
WHERE pb.MAPB = da.MAPB
GROUP BY pb.MAPB, pb.TENPB
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM PHONGBAN AS pb, DUAN AS da
WHERE pb.MAPB = da.MAPB
GROUP BY pb.MAPB);
Bài 5: CSDL Quản Lý Đơn Đặt Hàng
I. Tạo DB
-- Bai 5 CSDL Quản Lý Đơn Đăt Hàng
IF DB_ID('QuanLyDonDatHang') IS NULL
CREATE DATABASE QuanLyDonDatHang;
12
II. Tạo QH
-- Tạo QH NHACC
-- Tân từ: Danh mục các nhà cung cấp
USE QuanLyDonDatHang;
IF OBJECT_ID('NHACC') IS NOT NULL
DROP TABLE NHACC
CREATE TABLE NHACC
(
MANCC NVARCHAR(50) NOT NULL,
TENNCC NVARCHAR(50) NOT NULL,
DCNCC NVARCHAR(50),
MALH NVARCHAR(50) REFERENCES LOAIHG,
CONSTRAINT PK_NHACC PRIMARY KEY (MANCC)
);
INSERT INTO NHACC (MANCC, TENNCC, DCNCC, MALH)
VALUES ('C01', N'SamSung', N'Korea', 'L01'),
('C02', N'Apple', N'USA', 'L01'),
('C03', N'Hon da', N'Japan', 'L02'),
('C04', N'Adidas', N'USA', 'L03');
--- Tạo QH LOAIHG
--- Tân từ: Danh mục các loại hàng
USE QuanLyDonDatHang;
IF OBJECT_ID('LOAIHG') IS NOT NULL
DROP TABLE LOAIHG
CREATE TABLE LOAIHG
(
MALH NVARCHAR(50) NOT NULL,
TENLH NVARCHAR(50) NOT NULL,
MOTALH NVARCHAR(50),
CONSTRAINT PK_LOAIHG PRIMARY KEY (MALH)
);
INSERT INTO LOAIHG (MALH, TENLH, MOTALH)
VALUES ('L01', N'Điện máy', ''),
('L02', N'Xe cộ', ''),
('L03', N'Thời Trang', '');
-- Tạo QH HANG
-- Tân từ: Danh mục các mặt hàng
USE QuanLyDonDatHang;
IF OBJECT_ID('HANG') IS NOT NULL
DROP TABLE HANG
CREATE TABLE HANG
(
MAHG NVARCHAR(50) NOT NULL,
TENHG NVARCHAR(50) NOT NULL,
DONGIA INT NOT NULL,
MALH NVARCHAR(50) NOT NULL REFERENCES LOAIHG,
MANCC NVARCHAR(50) NOT NULL REFERENCES NHACC,
CONSTRAINT PK_HANG PRIMARY KEY (MAHG)
);
INSERT INTO HANG (MAHG, TENHG, DONGIA, MALH, MANCC)
VALUES ('H01', N'Áo sơ mi', 100000, 'L03', 'C04'),
13
('H02', N'Honda SH', 130000000, 'L02', 'C03'),
('H03', N'LCD 943-SNX', 2500000, 'L01', 'C01'),
('H04', N'Martin 2000', 2000000, 'L02', 'C03');
-- Tạo QH KHACH
-- Tân từ: Danh mục các khách hàng
USE QuanLyDonDatHang;
IF OBJECT_ID('KHACH') IS NOT NULL
DROP TABLE KHACH
CREATE TABLE KHACH
(
MAKH NVARCHAR(50) NOT NULL,
TENKH NVARCHAR(50) NOT NULL,
DCKH NVARCHAR(50),
CONSTRAINT PK_KHACH PRIMARY KEY (MAKH)
);
INSERT INTO KHACH (MAKH, TENKH, DCKH)
VALUES ('K01', N'Vương Ngọc A', ''),
('K02', N'Vương Ngọc B', ''),
('K03', N'Vương Ngọc C', ''),
('K04', N'Vương Ngọc D', '');
-- Tạo QH DDH
-- Tân từ: Danh mục các đơn đặt hàng
USE QuanLyDonDatHang;
IF OBJECT_ID('DDH') IS NOT NULL
DROP TABLE DDH
CREATE TABLE DDH
(
MADDH NVARCHAR(50) NOT NULL,
NGAYDH DATE NOT NULL,
NGAYHL DATE NOT NULL,
MAKH NVARCHAR(50) NOT NULL REFERENCES KHACH,
CONSTRAINT PK_DDH PRIMARY KEY (MADDH)
);
INSERT INTO DDH (MADDH, NGAYDH, NGAYHL, MAKH)
VALUES ('D01', '20100510', '20100520', 'K01'),
('D02', '20100505', '20100530', 'K02'),
('D03', '20100430', '20100510', 'K02');
-- Tạo QH CTDDH
-- Tân từ: Các mặt hàng trong một đơn đặt hàng cùng với số lượng của nó
USE QuanLyDonDatHang;
IF OBJECT_ID('CTDDH') IS NOT NULL
DROP TABLE CTDDH
CREATE TABLE CTDDH
(
MADDH NVARCHAR(50) NOT NULL REFERENCES DDH,
MAHG NVARCHAR(50) NOT NULL REFERENCES HANG,
SLDAT INT NOT NULL,
CONSTRAINT PK_CTDDH PRIMARY KEY (MADDH, MAHG)
);
INSERT INTO CTDDH (MADDH, MAHG, SLDAT)
14
VALUES ('D01', 'H01', 10),
('D01', 'H02', 1),
('D02', 'H04', 5),
('D03', 'H03', 2);
-- Tạo QH DOTGIAO
-- Tân từ: Danh mục các đợt giao hàng cho đơn đặt hàng
USE QuanLyDonDatHang;
IF OBJECT_ID('DOTGIAO') IS NOT NULL
DROP TABLE DOTGIAO
CREATE TABLE DOTGIAO
(
MADGH NVARCHAR(50) NOT NULL,
NGAYDGH DATE NOT NULL,
MADDH NVARCHAR(50) NOT NULL REFERENCES DDH,
CONSTRAINT PK_DOTGIAO PRIMARY KEY (MADGH)
);
INSERT INTO DOTGIAO (MADGH, NGAYDGH, MADDH)
VALUES ('G01', '20100515', 'D01'),
('G02', '20100520', 'D02'),
('G03', '20100525', 'D03');
-- Tạo QH CTDGH
-- Tân từ: Các mặt hàng trong cùng đợt giao hàng cùng với số lượng của
nó
USE QuanLyDonDatHang;
IF OBJECT_ID('CTDGH') IS NOT NULL
DROP TABLE CTDGH
CREATE TABLE CTDGH
(
MADGH NVARCHAR(50) NOT NULL REFERENCES DOTGIAO,
MAHG NVARCHAR(50) NOT NULL REFERENCES HANG,
SLGH INT NOT NULL,
CONSTRAINT PK_CTDGH PRIMARY KEY (MADGH, MAHG)
);
INSERT INTO CTDGH (MADGH, MAHG, SLGH)
VALUES ('G01', 'H02', 40),
('G02', 'H04', 20),
('G03', 'H04', 10);
III. Trả lời bằng T-SQL
III.1 Liêt kê các mặt hàng được cung cấp bởi nhà công cấp mã số "C01"
USE QuanLyDonDatHang;
SELECT h.MAHG, h.TENHG
FROM HANG AS h
WHERE h.MANCC = 'C01';
III.2 Các mặt hàng có thể được cung cấp bởi nhà CC "C01" nhưng chưa được cấp
15
USE QuanLyDonDatHang;
SELECT h.MAHG, h.TENHG
FROM HANG AS h
WHERE h.MANCC = 'C01'
AND h.MAHG NOT IN (SELECT MAHG FROM CTDGH);
III.3 Danh sách khách hàng (mã số và tên) có đặt hàng từ ngày d1 đến ngày d2
-- Giả sử có 2 biến kiểu ngày:
-- d1 = 01/05/2010
-- d2 = 09/05/2010
USE QuanLyDonDatHang;
SELECT k.MAKH, k.TENKH
FROM DDH AS ddh, KHACH AS k
WHERE ddh.NGAYDH BETWEEN '20100501' AND '20100509'
AND ddh.MAKH = k.MAKH;
III.4 Liệt kê các mặt hàng (mã số và tên) được giao trong đợt giao "G01"
USE QuanLyDonDatHang;
SELECT h.MAHG, h.TENHG
FROM CTDGH AS ct_dg, HANG AS h
WHERE ct_dg.MAHG = h.MAHG
AND ct_dg.MADGH = 'G01';
III.5 Liệt kê các mặt hàng (mã số và tên) chưa được đặt hàng bào giờ
USE QuanLyDonDatHang;
SELECT h.MAHG, h.TENHG
FROM HANG AS h, CTDDH AS ct_ddh
WHERE h.MAHG NOT IN (SELECT MAHG FROM CTDDH);
III.6 Liệt kê các mặt hàng (mã số và tên) được đặt từ ngày d1 đến d2 nhưng chưa giao
-- Giả sử có hai biến kiểu ngày:
-- d1 = 02/05/2010
-- d2 = 15/05/2010
USE QuanLyDonDatHang;
SELECT h.MAHG, h.TENHG
FROM HANG AS h, DDH AS d, CTDDH AS ct
WHERE h.MAHG = ct.MAHG
AND ct.MADDH = d.MADDH
AND d.NGAYDH BETWEEN '20100502' AND '20100515'
AND h.MAHG NOT IN (SELECT MAHG FROM CTDGH);
III.7 Số lượng đơn đặt hàng của từng khách hàng từ ngày d1 đến d2
-- Giả sử có hai biến kiểu ngày:
-- d1 = 02/04/2010
-- d2 = 15/05/2010
USE QuanLyDonDatHang;
SELECT k.MAKH, k.TENKH, COUNT(*) AS 'Số đơn đặt hàng'
FROM DDH AS d, KHACH AS k
16
WHERE k.MAKH = d.MAKH
AND d.NGAYDH BETWEEN '20100402' AND '20100515'
GROUP BY k.MAKH, k.TENKH;
III.8 Từ ngày d1 đến d2 khách hàng nào đặt nhiều đơn đặt hàng nhất
-- Giả sử có hai biến kiểu ngày:
-- d1 = 02/04/2010
-- d2 = 15/05/2010
USE QuanLyDonDatHang;
SELECT k.MAKH, k.TENKH, COUNT(*) AS 'Số đơn đặt hàng'
FROM DDH AS d, KHACH AS k
WHERE k.MAKH = d.MAKH
AND d.NGAYDH BETWEEN '20100402' AND '20100615'
GROUP BY k.MAKH, k.TENKH
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM DDH AS d, KHACH AS k
WHERE k.MAKH = d.MAKH
GROUP BY k.MAKH);
III.9 Trị giá của từng đơn đặt hàng từ ngày d1 đến ngày d2
-- Giả sử có hai biến kiểu ngày:
-- d1 = 02/04/2010
-- d2 = 15/05/2010
USE QuanLyDonDatHang;
SELECT d.MADDH AS 'Đơn Dặt hàng', SUM(ct.SLDAT * h.DONGIA) AS 'Trị giá'
FROM DDH AS d, CTDDH AS ct, HANG AS h
WHERE h.MAHG = ct.MAHG
AND ct.MADDH = d.MADDH
AND d.NGAYDH BETWEEN '20100402' AND '20100515'
GROUP BY d.MADDH;
III.10 Từ ngày d1 đến d2 đơn đặt hàng nào có giá trị lớn nhất
-- Giả sử có hai biến kiểu ngày:
-- d1 = 02/04/2010
-- d2 = 15/05/2010
USE QuanLyDonDatHang;
SELECT d.MADDH AS 'Đơn Dặt hàng', SUM(ct.SLDAT*h.DONGIA) AS 'Trị giá'
FROM DDH AS d, CTDDH AS ct, HANG AS h
WHERE h.MAHG = ct.MAHG
AND ct.MADDH = d.MADDH
AND d.NGAYDH BETWEEN '20100402' AND '20100515'
GROUP BY d.MADDH
HAVING SUM(ct.SLDAT*h.DONGIA) >= ALL(SELECT SUM(ct.SLDAT*h.DONGIA)
FROM DDH AS d,CTDDH AS ct,HANG AS h
WHERE h.MAHG = ct.MAHG
AND ct.MADDH = d.MADDH
AND d.NGAYDH
BETWEEN '20100402' AND '20100515'
GROUP BY d.MADDH);
17
Bài 6: CSDL Quản Lý Chuyến Du Lịch
I. Tạo DB
-- Bai 6 CSDL Quản Lý Chuyến Dụ Lịch
IF DB_ID('QuanLyChuyenDuLich') IS NULL
CREATE DATABASE QuanLyChuyenDuLich;
II. Tạo QH
-- Tao QH XE
-- Tân từ: Danh sách xe
USE QuanLyChuyenDuLich;
IF OBJECT_ID('XE') IS NOT NULL
DROP TABLE XE
CREATE TABLE XE
(
MAXE NVARCHAR(50) NOT NULL,
BSXE NVARCHAR(50) NOT NULL,
MOTAXE NVARCHAR(50),
CONSTRAINT PK_XE PRIMARY KEY (MAXE),
CONSTRAINT UNI_XE UNIQUE (BSXE)
);
INSERT INTO XE (MAXE, BSXE, MOTAXE)
VALUES ('X01', '1111', ''),
('X02', '2222', ''),
('X03', '3333', ''),
('X04', '4444', ''),
('X05', '5555', '');
--- Tao QH DIADIEM
--- Tân từ: Danh sách các địa điểm
USE QuanLyChuyenDuLich;
IF OBJECT_ID('DIADIEM') IS NOT NULL
DROP TABLE DIADIEM
CREATE TABLE DIADIEM
(
MADD NVARCHAR(50) NOT NULL,
TENDD NVARCHAR(50) NOT NULL,
MOTADD NVARCHAR(50),
CONSTRAINT PK_DIADIEM PRIMARY KEY (MADD)
);
INSERT INTO DIADIEM (MADD, TENDD, MOTADD)
VALUES ('D01', N'Nha Trang', ''),
('D02', N'Sa pa', ''),
('D03', N'Phú Quốc', '');
-- Tao QH NHANVIEN
-- Tân từ: Danh sách nhân viên
USE QuanLyChuyenDuLich;
IF OBJECT_ID('NHANVIEN') IS NOT NULL
18
DROP TABLE NHANVIEN
CREATE TABLE NHANVIEN
(
MANV NVARCHAR(50) NOT NULL,
CMND INT NOT NULL,
TENNV NVARCHAR(50) NOT NULL,
DCNV NVARCHAR(50) NOT NULL,
CONSTRAINT PK_NHANVIEN PRIMARY KEY (MANV),
CONSTRAINT UNI_NHANVIEN UNIQUE (CMND)
);
INSERT INTO NHANVIEN (MANV, CMND, TENNV, DCNV)
VALUES ('N01', 1000001, N'Vương Ngọc A', N'Hồ chí minh'),
('N02', 1000002, N'Vương Ngọc B', N'Quảng Nam'),
('N03', 1000003, N'Vương Ngọc C', N'Đồng Nai'),
('N04', 1000004, N'Vương Ngọc D', N'Đà Nẵng'),
('N05', 1000005, N'Vương Ngọc E', N'Quảng Ngãi'),
('N06', 1000006, N'Vương Ngọc F', N'Bình Định'),
('N07', 1000007, N'Vương Ngọc G', N'Phú Yên');
-- Tao QH KHACH
-- Tân từ: Danh mục các khách hàng
USE QuanLyChuyenDuLich;
IF OBJECT_ID('KHACH') IS NOT NULL
DROP TABLE KHACH
CREATE TABLE KHACH
(
MAKH NVARCHAR(50) NOT NULL,
CMND INT NOT NULL,
TENKH NVARCHAR(50) NOT NULL,
DCKH NVARCHAR(50),
CONSTRAINT PK_KHACH PRIMARY KEY (MAKH),
CONSTRAINT UNI_KHACH UNIQUE (CMND)
);
INSERT INTO KHACH (MAKH, CMND, TENKH, DCKH)
VALUES
('K01',
5000001, N'Khách
A',
N'HCM'),
('K02',
5000002, N'Khách
B',
N'Đồng Tháp'),
('K03',
5000003, N'Khách
C',
N'Bình Dương'),
('K04',
5000004, N'Khách
D',
N'Vũng Tàu'),
('K05',
5000005, N'Khách
E',
N'Bến tre'),
('K06',
5000006, N'Khách
F',
N'Bình Dương'),
('K07',
5000007, N'Khách
G',
N'Huế'),
('K08',
5000008, N'Khách
H',
N'Vĩnh Long'),
('K09',
5000009, N'Khách
I',
N'Huế');
-- Tao QH CHUYENDI
-- Tân từ: Danh sách chuyến đi
USE QuanLyChuyenDuLich;
IF OBJECT_ID('CHUYENDI') IS NOT NULL
DROP TABLE CHUYENDI
CREATE TABLE CHUYENDI
(
MACD NVARCHAR(50) NOT NULL,
NGAYKH DATE NOT NULL,
NGAYKT DATE NOT NULL,
19
NOIDUNG NVARCHAR(50),
CONSTRAINT PK_CHUYENDI PRIMARY KEY (MACD)
);
INSERT INTO CHUYENDI(MACD, NGAYKH, NGAYKT, NOIDUNG)
VALUES ('C01', '20100510', '20100520', ''),
('C02', '20100515', '20100525', ''),
('C03', '20100520', '20100530', ''),
('C04', '20100525', '20100605', '');
-- Tao QH CD_NV
-- Tân từ: Các nhân viên tham gia vào chuyến đi
USE QuanLyChuyenDuLich;
IF OBJECT_ID('CD_NV') IS NOT NULL
DROP TABLE CD_NV
CREATE TABLE CD_NV
(
MACD NVARCHAR(50) NOT NULL REFERENCES CHUYENDI,
MANV NVARCHAR(50) NOT NULL REFERENCES NHANVIEN,
CONSTRAINT PK_CD_NV PRIMARY KEY (MACD, MANV)
);
INSERT INTO CD_NV (MACD, MANV)
VALUES ('C01', 'N01'),
('C01', 'N02'),
('C02', 'N03'),
('C03', 'N04'),
('C03', 'N05'),
('C04', 'N06');
-- Tao QH CD_XE
-- Tân từ: Danh sách xe trong các chuyến đi
USE QuanLyChuyenDuLich;
IF OBJECT_ID('CD_XE') IS NOT NULL
DROP TABLE CD_XE
CREATE TABLE CD_XE
(
MACD NVARCHAR(50) NOT NULL REFERENCES CHUYENDI,
MAXE NVARCHAR(50) NOT NULL REFERENCES XE,
CONSTRAINT PK_CD_XE PRIMARY KEY (MACD, MAXE)
);
INSERT INTO CD_XE(MACD, MAXE)
VALUES ('C01', 'X01'),
('C01', 'X02'),
('C02', 'X03'),
('C02', 'X04'),
('C03', 'X05'),
('C04', 'X05'),
('C04', 'X01');
-- Tao QH CD_DD
-- Tân từ: Các địa điểm mà chuyến đi ghé qua
USE QuanLyChuyenDuLich;
IF OBJECT_ID('CD_DD') IS NOT NULL
DROP TABLE CD_DD
20

Tài liệu khác của Trường Đại học Kinh Tế Quốc Dân

Preview text:

DATABASE EXERCISES – MS SQL SERVER 2008
Bài 1: CSDL Quản lý học viên. I. Tạo DB:
IF DB_ID('QuanLyHocVien') IS NULL CREATE DATABASE QuanLyHocVien; II. Tạo bảng: -- Tao QH MONHOC -- USE QuanLyHocVien;
IF OBJECT_ID('MONHOC') IS NOT NULL DROP TABLE MONHOC CREATE TABLE MONHOC (
MAMH NVARCHAR(30) NOT NULL PRIMARY KEY,
TENMH NVARCHAR(30) NOT NULL UNIQUE, SOTC INT NOT NULL );
INSERT INTO MONHOC(MAMH, TENMH, SOTC) VALUES
('M01', N'Cơ sở dữ liệu', 3),
('M02', N'Thiết kế web', 3), ('M03', N'Xử lý ảnh', 4); -- Tao QH LOP -- USE QuanLyHocVien;
IF OBJECT_ID('LOP') IS NOT NULL DROP TABLE LOP CREATE TABLE LOP (
MALOP NVARCHAR(30) NOT NULL PRIMARY KEY, TENLOP NVARCHAR(30) NOT NULL UNIQUE ); INSERT INTO LOP(MALOP, TENLOP) VALUES
('L01', N'Công nghệ TT 2009'), ('L02', N'Quản trị 2009'); -- Tao QH HV -- USE QuanLyHocVien; IF OBJECT_ID('HV') IS NOT NULL DROP TABLE HV CREATE TABLE HV (
MAHV NVARCHAR(30) NOT NULL PRIMARY KEY, 1 TENHV NVARCHAR(30) NOT NULL, DCHV NVARCHAR(30),
MALOP NVARCHAR(30) REFERENCES LOP );
INSERT INTO HV(MAHV, TENHV, DCHV, MALOP) VALUES
('H01', N'Nguyễn Văn A', 'Q1', 'L01'),
('H02', N'Nguyễn Văn B', 'Q2', 'L01'),
('H03', N'Nguyễn Văn C', 'Q1', 'L01'),
('H04', N'Nguyễn Văn B', 'Q3', 'L02'),
('H05', N'Nguyễn Văn A', 'Q3', 'L02'),
('H06', N'Nguyễn Văn A', 'Q2', 'L03'); -- Tao QH DIEMHV -- USE QuanLyHocVien;
IF OBJECT_ID('DIEMHV') IS NOT NULL DROP TABLE DIEMHV CREATE TABLE DIEMHV (
MAHV NVARCHAR(30) REFERENCES HV,
MAMH NVARCHAR(30) REFERENCES MONHOC, DIEM INT,
CONSTRAINT Diem PRIMARY KEY (MAHV, MAMH) );
INSERT INTO DIEMHV(MAHV, MAMH, DIEM) VALUES ('H01', 'M01', 5), ('H01', 'M02', 4), ('H02', 'M01', 7), ('H02', 'M02', 5), ('H02', 'M03', 6), ('H03', 'M01', 7); III. Trả lời bằng T-SQL.
III.1 Cho biết tên môn học và số tín chỉ của môn học mã số “M01”. USE QuanLyHocVien; SELECT TENMH, SOTC FROM MONHOC WHERE MAMH = 'M03';
III.2 Liệt kê các học viên (mã số và tên) của lớp có tên “Quản trị 2009”. USE QuanLyHocVien; SELECT hv.MAHV, hv.TENHV FROM LOP AS L , HV AS hv WHERE L.MALOP = hv.MALP
AND TENLOP = N'Quản trị 2009';
III.3 Liệt kê điểm và tên môn học của học viên mã số “H01”. USE QuanLyHocVien; 2 select mh.TENMH, d.DIEM
FROM MONHOC AS mh , DIEMHV AS d WHERE mh.MAMH = d.MAMH AND d.MAHV = 'H01';
III.4 Cho biết mã số và tên học viên chưa đạt điểm môn “Cơ sở dữ liệu”. USE QuanLyHocVien; select hv.MAHV, hv.TENHV FROM DIEMHV AS d, HV AS hv WHERE d.DIEM < 5 AND d.MAMH = 'M01' AND hv.MAHV = d.MAHV;
III.5 Cho biết các môn học mà học viên mã số “H01” chưa thi. USE QuanLyHocVien; SELECT mh.MAMH, mh.TENMH FROM monhoc AS mh
WHERE MAMH NOT IN (SELECT MAMH FROM DIEMHV WHERE MAHV = 'H01');
III.6 Liêt kê những học viên có điểm “M01” lơn nhất. USE QuanLyHocVien;
SELECT hv.MAHV, hv.TENHV, d.DIEM FROM HV AS hv,DIEMHV AS d WHERE d.MAMH = 'M01' AND
DIEM = (SELECT MAX(DIEM) FROM DIEMHV) AND hv.MAHV = d. MAHV;
III.7 Liêt kê những học viên có điểm môn “M01” lớn nhất. USE QuanLyHocVien;
SELECT hv.MAHV, hv.TENHV, d.DIEM FROM HV AS hv, DIEMHV AS d
WHERE d.DIEM = (SELECT MAX(DIEM) FROM DIEMHV WHERE MAMH = 'M01') AND hv.MAHV = d.MAHV; 3
III.8 Cho biết sĩ số từng lớp. USE QuanLyHocVien;
SELECT L.TENLOP, COUNT(*) AS 'Sĩ số' FROM HV AS hv, LOP AS L WHERE hv.MALOP = L.MALOP GROUP BY L.TENLOP;
Bài 2: CSDL Hóa Đơn Bán Lẻ I. Tạo DB
--- Bai 2 Tao DB Hoa Don Ban Le ---
IF DB_ID('HoaDonBanLe') IS NULL CREATE DATABASE HoaDonBanLe; II. Tạo Table --- Tao QH HANG --- USE HoaDonBanLe;
IF OBJECT_ID('HANG') IS NOT NULL DROP TABLE HANG CREATE TABLE HANG (
MAHG NVARCHAR(30) NOT NULL PRIMARY KEY, TENHG NVARCHAR(30) NOT NULL, DONGIA INT NOT NULL );
INSERT INTO HANG(MAHG, TENHG, DONGIA) VALUES ('H01', N'Hàng A', 150), ('H02', N'Hàng B', 70), ('H03', N'Hàng C', 200); --- Tao QH Hoa Don --- USE HoaDonBanLe;
IF OBJECT_ID('HOADON') IS NOT NULL DROP TABLE HOADON CREATE TABLE HOADON ( MAHD
NVARCHAR(30) NOT NULL PRIMARY KEY, NGAYBAN DATE NOT NULL, NOIDUNG NVARCHAR(30) );
INSERT INTO HOADON(MAHD, NGAYBAN, NOIDUNG) VALUES
('HD01', '20090912', N'Giao hàng liền'),
('HD02', '20090915', N'Khách vãng lai'),
('HD03', '20090915', N'xxxxxxxx'); 4 --- Tao QH CTHD --- USE HoaDonBanLe;
IF OBJECT_ID('CTHD') IS NOT NULL DROP TABLE CTHD CREATE TABLE CTHD (
MAHD NVARCHAR(30) REFERENCES HOADON NOT NULL,
MAHG NVARCHAR(30) NOT NULL REFERENCES HANG, SL INT,
CONSTRAINT HD_detail PRIMARY KEY (MAHD, MAHG) );
INSERT INTO CTHD(MAHD, MAHG, SL) VALUES ('HD01', 'H01', 10), ('HD01', 'H03', 12), ('HD02', 'H01', 34), ('HD02', 'H02', 15), ('HD02', 'H03', 3), ('HD03', 'H02', 2), ('HD03', 'H03', 16); III. Trả lời bằng T-SQL
III.1 Liệt kê các mặt hàng có đơn giá từ 100 đến 200. USE HoaDonBanLe; SELECT MAHG, TENHG, DONGIA FROM HANG
WHERE DONGIA >= 100 AND DONGIA <= 200;
III.2 Các mặt hàng ( Mã, tên, đơn giá ) được bán trong ngày “15/09/2009”.
SELECT h.MAHG, h.TENHG, h.DONGIA FROM HANG AS h WHERE h.MAHG IN (SELECT chitiet.MAHG FROM CTHD AS chitiet JOIN HOADON AS hd
ON chitiet.MAHD = hd.MAHD and hd.NGAYBAN = '20090915');
III.3 Tổng số lượng bán của từng hóa đơn.
SELECT DISTINCT ct.MAHD, SUM(ct.SL) AS 'Tong SL ban' FROM CTHD AS ct GROUP BY ct.MAHD;
III.4 Trị giá bán từ ngày d1 đến ngày d2.
Giả sử có 2 biến kiểu ngày; d1 = 13/09/2009 d2 = 16/09/2009 5
SELECT SUM(ct.SL*h.DONGIA) AS 'Tong gia tri' FROM CTHD AS ct, HANG AS h WHERE ct.MAHD IN (SELECT MAHD FROM HOADON
WHERE NGAYBAN >= 'd1' AND NGAYBAN <= 'd2');
III.5 Tổng số lượng bán của từng hóa đơn trong ngày “15/09/2009”.
SELECT DISTINCT ct.MAHD, SUM(ct.SL) AS 'Tong' FROM CTHD AS ct WHERE ct.MAHD IN (SELECT MAHD FROM HOADON WHERE NGAYBAN = '20090915') GROUP BY ct.MAHD;
III.6 Trị giá của từng hóa đơn bán từ ngày “12/09/2009” đến “15/09/2009.
SELECT DISTINCT ct.MAHD, SUM(ct.SL * h.DONGIA) AS 'Tong' FROM CTHD AS ct, HANG AS h WHERE ct.MAHD IN (SELECT MAHD FROM HOADON
WHERE NGAYBAN >= '20090912' AND NGAYBAN <= '20090915') AND ct.MAHG = h.MAHG GROUP BY ct.MAHD;
III.7 Từ ngày “12/09/2009” đến “15/09/2009” hóa đơn nào có giá trị lớn nhất. USE HoaDonBanLe;
SELECT CT.MAHD, SUM(CT.SL * H.DONGIA) AS 'Tổng' FROM CTHD AS CT, HANG AS H WHERE CT.MAHG = H.MAHG AND CT.MAHD IN (SELECT MAHD FROM HOADON
WHERE NGAYBAN >= '20090912' AND NGAYBAN <= '20090915') GROUP BY CT.MAHD
HAVING SUM(CT.SL * H.DONGIA) >=
ALL (SELECT SUM(CT.SL * H.DONGIA) FROM CTHD AS CT, HANG AS H WHERE CT.MAHG = H.MAHG AND CT.MAHD IN (SELECT MAHD FROM HOADON WHERE NGAYBAN >= '20090912' AND NGAYBAN <= '20090915') GROUP BY CT.MAHD); Bài 3: CSDL Kho Hàng I. Tạo DB --- Bai 3 CSDL KHOHANG --- 6 IF DB_ID('KhoHang') IS NULL CREATE DATABASE KhoHang; II. Tạo QH. --- Tao QH LOAIHANG
--- Tan tu: Danh muc cac loai hang USE KhoHang;
IF OBJECT_ID('LOAIHANG') IS NOT NULL DROP TABLE LOAIHANG CREATE TABLE LOAIHANG ( MALH NVARCHAR(30) NOT NULL, TENLH NVARCHAR(30) NOT NULL,
CONSTRAINT PK_LOAIHANG PRIMARY KEY (MALH),
CONSTRAINT Uni_MaHang UNIQUE (TENLH) );
INSERT INTO LOAIHANG (MALH,TENLH) VALUES
('L01', N'Nước giải khát'), ('L02', N'Trà khô'); --- Tao QH HANG --- Tan tu: Danh muc hang USE KhoHang;
IF OBJECT_ID('HANG') IS NOT NULL DROP TABLE HANG CREATE TABLE HANG ( MAHG NVARCHAR(30) NOT NULL, TENHG NVARCHAR(30) NOT NULL,
MALH NVARCHAR(30) NOT NULL REFERENCES LOAIHANG, CONSTRAINT PK_HANG PRIMARY KEY (MAHG),
CONSTRAINT Uni_TenHang UNIQUE (TENHG) );
INSERT INTO HANG (MAHG, TENHG, MALH) VALUES ('H01', N'Trà xanh', 'L01'), ('H02', N'Pepsi', 'L01'), ('H03', N'7 Up', 'L01'),
('H04', N'Trà Tân cương', 'L01'),
('H05', N'Trà Bảo Lộc', 'L01'); select * from HANG -- Tao QH KHOHG
-- Tan tu: Danh muc cac kho hang, moi kho hang chi duoc chua mot loai hang USE KhoHang;
IF OBJECT_ID('KHOHANG') IS NOT NULL DROP TABLE KHOHANG CREATE TABLE KHOHANG ( MAKH NVARCHAR(30) NOT NULL, TENKH NVARCHAR(30) NOT NULL, DCKH NVARCHAR(30), MALH NVARCHAR(30) NOT NULL,
CONSTRAINT PK_KHOHANG PRIMARY KEY (MAKH),
CONSTRAINT UNI_TENKH UNIQUE (TENKH) 7 );
INSERT INTO KHOHANG (MAKH, TENKH, DCKH, MALH) VALUES ('K01', 'Kho A', 'Q1', 'L01'), ('K02', 'Kho B', 'Q1', 'L01'), ('K03', 'Kho C', 'Q2', 'L02'); -- Tao QH KHOCHUA
-- Tan tu: Hiện trạng chứa hàng của các kho hàng. USE KhoHang;
IF OBJECT_ID('KHOCHUA') IS NOT NULL DROP TABLE KHOCHUA CREATE TABLE KHOCHUA (
MAKH NVARCHAR(30) NOT NULL REFERENCES KHOHANG,
MAHG NVARCHAR(30) NOT NULL REFERENCES HANG, SL INT,
CONSTRAINT PK_KHOCHUA PRIMARY KEY (MAKH, MAHG) );
INSERT INTO KHOCHUA (MAKH, MAHG, SL) VALUES ('K01', 'H01', '50'), ('K01', 'H02', '30'), ('K02', 'H03', '100'), ('K03', 'H04', '40'), ('K03', 'H05', '50'); III. Trả lời bằng T-SQL.
III.1 Các mặt hàng của loại hàng mã số “L01” USE KhoHang; SELECT MAHG, TENHG FROM HANG WHERE MALH = 'L01';
III.2 Các mặt hàng (mã số và tên) có thể chứa được trong kho mã số “K01” USE KhoHang; SELECT MAHG, TENHG FROM HANG
WHERE MALH IN (SELECT MALH FROM KHOHANG WHERE MAKH = 'K01');
III.3 Các mặt hàng (mã số và tên) hiên chứa trong kho mã số "K01" USE KhoHang; SELECT MAHG, TENHG FROM HANG
WHERE MAHG IN (SELECT MAHG FROM KHOCHUA WHERE MAKH = 'K01');
III.4 Tổng số lượng chứa của từng kho USE KhoHang;
SELECT kh.MAKH, kh.TENKH, SUM(kc.SL) AS 'Tổng Số lượng' 8
FROM KHOHANG AS kh, KHOCHUA AS kc WHERE kh.MAKH = kc.MAKH GROUP BY kh.MAKH, kh.TENKH;
III.5 Kho hàng nào chứa nhiều mặt hàng nhất USE KhoHang;
SELECT kh.MAKH, kh.TENKH, COUNT(*) AS 'Số mặt hàng'
FROM KHOHANG AS kh, KHOCHUA AS kc WHERE kh.MAKH = kc.MAKH GROUP BY kh.MAKH, kh.TENKH
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM KHOHANG AS kh, KHOCHUA AS kc WHERE kh.MAKH = kc.MAKH GROUP BY kh.MAKH, kh.TENKH);
III.6 Kho hàng nào có tổng số lượng chứa nhiều nhất USE KhoHang;
SELECT kh.MAKH, kh.TENKH, SUM(kc.SL) AS 'Tổng'
FROM KHOHANG AS kh, KHOCHUA AS kc WHERE kh.MAKH = kc.MAKH GROUP BY kh.MAKH, kh.TENKH
HAVING SUM(kc.SL) >= ALL (SELECT SUM(kc.SL)
FROM KHOHANG AS kh, KHOCHUA AS kc WHERE kh.MAKH = kc.MAKH GROUP BY kh.MAKH, kc.MAKH);
III.7 Tổng số lượng tồn kho của từng mặt hàng USE KhoHang; SELECT h.MAHG, h.TENHG, kc.SL FROM HANG AS h, KHOCHUA AS kc WHERE h.MAHG = kc.MAHG;
III.8 Loại hàng nào tồn kho nhiều nhất USE KhoHang;
SELECT lh.MALH, TENLH, SUM(kc.SL) AS 'Số lượng tồn kho'
FROM LOAIHANG AS lh, HANG AS h, KHOCHUA AS kc WHERE h.MAHG = kc.MAHG AND lh.MALH = h.MALH GROUP BY lh.MALH, TENLH
HAVING SUM(kc.SL) >= ALL (SELECT SUM(kc.SL)
FROM LOAIHANG AS lh, HANG AS h, KHOCHUA AS kc WHERE h.MAHG = kc.MAHG AND lh.MALH = h.MALH GROUP BY lh.MALH, lh.TENLH); 9
Bài 4: CSDL Quản Lý Dự Án I. Tạo DB
-- Bai 4 CSDL Quản Lý Dự Án IF DB_ID('QuanLyDuAn') IS NULL CREATE DATABASE QuanLyDuAn; II. Tạo QH -- Tao QH PHONGBAN
-- Tân từ: Danh sách các phòng ban USE QuanLyDuAn;
IF OBJECT_ID('PHONGBAN') IS NOT NULL DROP TABLE PHONGBAN CREATE TABLE PHONGBAN ( MAPB NVARCHAR(30) NOT NULL, TENPB NVARCHAR(30) NOT NULL,
CONSTRAINT PK_PHONGBAN PRIMARY KEY (MAPB) );
INSERT INTO PHONGBAN (MAPB,TENPB) VALUES ('P01', N'Phòng Marketing'), ('P02', N'Phòng Nhân sự'); --- Tao QH NHANVIEN
--- Tân từ: Danh sách nhân viên USE QuanLyDuAn;
IF OBJECT_ID('NHANVIEN') IS NOT NULL DROP TABLE NHANVIEN CREATE TABLE NHANVIEN ( MANV NVARCHAR(30) NOT NULL, TENNV NVARCHAR(30) NOT NULL, DCNV NVARCHAR(30),
MAPB NVARCHAR(30) NOT NULL REFERENCES PHONGBAN, CONSTRAINT PK_NHANVIEN PRIMARY KEY (MANV) );
INSERT INTO NHANVIEN (MANV, TENNV, DCNV, MAPB) VALUES
('N01', N'VƯƠNG NGỌC A', N'QUẢNG NAM', 'P01'),
('N02', N'VƯƠNG NGỌC B', N'QUẢNG NGÃI', 'P01'),
('N03', N'VƯƠNG NGỌC C', N'BÌNH ĐỊNH', 'P02'),
('N04', N'VƯƠNG NGỌC D', N'PHÚ YÊN', 'P02'); -- Tao QH DUAN
-- Tân từ: Danh sách dự án USE QuanLyDuAn;
IF OBJECT_ID('DUAN') IS NOT NULL DROP TABLE DUAN CREATE TABLE DUAN ( 10 MADA NVARCHAR(30) NOT NULL, TENDA NVARCHAR(30) NOT NULL, TGDA NVARCHAR(30),
MAPB NVARCHAR(30) NOT NULL REFERENCES PHONGBAN,
CONSTRAINT PK_DUAN PRIMARY KEY (MADA) );
INSERT INTO DUAN (MADA, TENDA, TGDA, MAPB) VALUES
('D01', N'Dự Án A', '', 'P01'),
('D02', N'Dự Án B', '', 'P01'),
('D03', N'Dự Án C', '', 'P02'); -- Tao QH PHANCONG
-- Tân từ: Nhân viên chỉ được phân công vào các dư án thuộc phòng ban của nhân viên đó USE QuanLyDuAn;
IF OBJECT_ID('PHANCONG') IS NOT NULL DROP TABLE PHANCONG CREATE TABLE PHANCONG (
MADA NVARCHAR(30) NOT NULL REFERENCES DUAN,
MANV NVARCHAR(30) NOT NULL REFERENCES NHANVIEN,
CONSTRAINT PK_PHANCONG PRIMARY KEY (MADA, MANV) );
INSERT INTO PHANCONG (MADA, MANV) VALUES ('D01', 'N01'), ('D01', 'N02'), ('D02', 'N03'), ('D03', 'N04'); III. Trả lời bằng T-SQL
III.1 Liệt kê danh sách nhân viên (mã số và tên) của phòng ban "P01" USE QuanLyDuAn; SELECT MANV, TENNV FROM NHANVIEN WHERE MAPB = 'P01';
III.2 Liệt kê DS nhân viên (mã số và tên) có thể được phân công vào dự án "D01" USE QuanLyDuAn; SELECT MANV, TENNV
FROM NHANVIEN AS nv, DUAN AS da WHERE nv.MAPB = da.MAPB AND da.MADA = 'D01';
III.3 Nhân viên mã số "N01" có thể được phân công vào dự án nào USE QuanLyDuAn;
SELECT nv.MANV, nv.TENNV, da.MADA, da.TENDA
FROM NHANVIEN AS nv, DUAN AS da WHERE nv.MANV = 'N01' AND nv.MAPB = da.MAPB; 11
III.4 Cho biết nhân viên (mã số và tên) có thể được phân công vào dự án "D02" nhưng chưa được phân công USE QuanLyDuAn; SELECT nv.MANV, nv.TENNV
FROM NHANVIEN AS nv, DUAN AS da WHERE nv.MAPB = da.MAPB AND da.MADA = 'D02'
AND da.MADA NOT IN (SELECT MADA FROM PHANCONG);
III.5 Số lượng nhân viên được phân công vào từng dự án USE QuanLyDuAn;
SELECT da.MADA, da.TENDA, COUNT(*) AS 'Số nhân viên'
FROM PHANCONG AS pc, DUAN AS da WHERE da.MADA = pc.MADA GROUP BY da.MADA, da.TENDA;
III.6 Dự án nào phân công nhiều nhân viên nhất USE QuanLyDuAn;
SELECT da.MADA, da.TENDA, COUNT(*) AS 'Số nhân viên'
FROM PHANCONG AS pc, DUAN AS da WHERE da.MADA = pc.MADA GROUP BY da.MADA, da.TENDA
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM PHANCONG AS pc, DUAN AS da WHERE da.MADA = pc.MADA GROUP BY da.MADA, da.TENDA);
III.7 Phong ban nào có nhiều dự án nhất USE QuanLyDuAn;
SELECT pb.MAPB, pb.TENPB, COUNT(*) AS 'Số dự án'
FROM PHONGBAN AS pb, DUAN AS da WHERE pb.MAPB = da.MAPB GROUP BY pb.MAPB, pb.TENPB
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM PHONGBAN AS pb, DUAN AS da WHERE pb.MAPB = da.MAPB GROUP BY pb.MAPB);
Bài 5: CSDL Quản Lý Đơn Đặt Hàng I. Tạo DB
-- Bai 5 CSDL Quản Lý Đơn Đăt Hàng
IF DB_ID('QuanLyDonDatHang') IS NULL
CREATE DATABASE QuanLyDonDatHang; 12 II. Tạo QH -- Tạo QH NHACC
-- Tân từ: Danh mục các nhà cung cấp USE QuanLyDonDatHang;
IF OBJECT_ID('NHACC') IS NOT NULL DROP TABLE NHACC CREATE TABLE NHACC ( MANCC NVARCHAR(50) NOT NULL, TENNCC NVARCHAR(50) NOT NULL, DCNCC NVARCHAR(50),
MALH NVARCHAR(50) REFERENCES LOAIHG,
CONSTRAINT PK_NHACC PRIMARY KEY (MANCC) );
INSERT INTO NHACC (MANCC, TENNCC, DCNCC, MALH) VALUES
('C01', N'SamSung', N'Korea', 'L01'),
('C02', N'Apple', N'USA', 'L01'),
('C03', N'Hon da', N'Japan', 'L02'),
('C04', N'Adidas', N'USA', 'L03'); --- Tạo QH LOAIHG
--- Tân từ: Danh mục các loại hàng USE QuanLyDonDatHang;
IF OBJECT_ID('LOAIHG') IS NOT NULL DROP TABLE LOAIHG CREATE TABLE LOAIHG ( MALH NVARCHAR(50) NOT NULL, TENLH NVARCHAR(50) NOT NULL, MOTALH NVARCHAR(50),
CONSTRAINT PK_LOAIHG PRIMARY KEY (MALH) );
INSERT INTO LOAIHG (MALH, TENLH, MOTALH) VALUES ('L01', N'Điện máy', ''), ('L02', N'Xe cộ', ''), ('L03', N'Thời Trang', ''); -- Tạo QH HANG
-- Tân từ: Danh mục các mặt hàng USE QuanLyDonDatHang;
IF OBJECT_ID('HANG') IS NOT NULL DROP TABLE HANG CREATE TABLE HANG ( MAHG NVARCHAR(50) NOT NULL, TENHG NVARCHAR(50) NOT NULL, DONGIA INT NOT NULL,
MALH NVARCHAR(50) NOT NULL REFERENCES LOAIHG,
MANCC NVARCHAR(50) NOT NULL REFERENCES NHACC,
CONSTRAINT PK_HANG PRIMARY KEY (MAHG) );
INSERT INTO HANG (MAHG, TENHG, DONGIA, MALH, MANCC) VALUES
('H01', N'Áo sơ mi', 100000, 'L03', 'C04'), 13
('H02', N'Honda SH', 130000000, 'L02', 'C03'),
('H03', N'LCD 943-SNX', 2500000, 'L01', 'C01'),
('H04', N'Martin 2000', 2000000, 'L02', 'C03'); -- Tạo QH KHACH
-- Tân từ: Danh mục các khách hàng USE QuanLyDonDatHang;
IF OBJECT_ID('KHACH') IS NOT NULL DROP TABLE KHACH CREATE TABLE KHACH ( MAKH NVARCHAR(50) NOT NULL, TENKH NVARCHAR(50) NOT NULL, DCKH NVARCHAR(50),
CONSTRAINT PK_KHACH PRIMARY KEY (MAKH) );
INSERT INTO KHACH (MAKH, TENKH, DCKH) VALUES
('K01', N'Vương Ngọc A', ''),
('K02', N'Vương Ngọc B', ''),
('K03', N'Vương Ngọc C', ''),
('K04', N'Vương Ngọc D', ''); -- Tạo QH DDH
-- Tân từ: Danh mục các đơn đặt hàng USE QuanLyDonDatHang;
IF OBJECT_ID('DDH') IS NOT NULL DROP TABLE DDH CREATE TABLE DDH ( MADDH NVARCHAR(50) NOT NULL, NGAYDH DATE NOT NULL, NGAYHL DATE NOT NULL,
MAKH NVARCHAR(50) NOT NULL REFERENCES KHACH,
CONSTRAINT PK_DDH PRIMARY KEY (MADDH) );
INSERT INTO DDH (MADDH, NGAYDH, NGAYHL, MAKH) VALUES
('D01', '20100510', '20100520', 'K01'),
('D02', '20100505', '20100530', 'K02'),
('D03', '20100430', '20100510', 'K02'); -- Tạo QH CTDDH
-- Tân từ: Các mặt hàng trong một đơn đặt hàng cùng với số lượng của nó USE QuanLyDonDatHang;
IF OBJECT_ID('CTDDH') IS NOT NULL DROP TABLE CTDDH CREATE TABLE CTDDH (
MADDH NVARCHAR(50) NOT NULL REFERENCES DDH,
MAHG NVARCHAR(50) NOT NULL REFERENCES HANG, SLDAT INT NOT NULL,
CONSTRAINT PK_CTDDH PRIMARY KEY (MADDH, MAHG) );
INSERT INTO CTDDH (MADDH, MAHG, SLDAT) 14 VALUES ('D01', 'H01', 10), ('D01', 'H02', 1), ('D02', 'H04', 5), ('D03', 'H03', 2); -- Tạo QH DOTGIAO
-- Tân từ: Danh mục các đợt giao hàng cho đơn đặt hàng USE QuanLyDonDatHang;
IF OBJECT_ID('DOTGIAO') IS NOT NULL DROP TABLE DOTGIAO CREATE TABLE DOTGIAO ( MADGH NVARCHAR(50) NOT NULL, NGAYDGH DATE NOT NULL,
MADDH NVARCHAR(50) NOT NULL REFERENCES DDH,
CONSTRAINT PK_DOTGIAO PRIMARY KEY (MADGH) );
INSERT INTO DOTGIAO (MADGH, NGAYDGH, MADDH) VALUES ('G01', '20100515', 'D01'), ('G02', '20100520', 'D02'), ('G03', '20100525', 'D03'); -- Tạo QH CTDGH
-- Tân từ: Các mặt hàng trong cùng đợt giao hàng cùng với số lượng của nó USE QuanLyDonDatHang;
IF OBJECT_ID('CTDGH') IS NOT NULL DROP TABLE CTDGH CREATE TABLE CTDGH (
MADGH NVARCHAR(50) NOT NULL REFERENCES DOTGIAO,
MAHG NVARCHAR(50) NOT NULL REFERENCES HANG, SLGH INT NOT NULL,
CONSTRAINT PK_CTDGH PRIMARY KEY (MADGH, MAHG) );
INSERT INTO CTDGH (MADGH, MAHG, SLGH) VALUES ('G01', 'H02', 40), ('G02', 'H04', 20), ('G03', 'H04', 10); III. Trả lời bằng T-SQL
III.1 Liêt kê các mặt hàng được cung cấp bởi nhà công cấp mã số "C01" USE QuanLyDonDatHang; SELECT h.MAHG, h.TENHG FROM HANG AS h WHERE h.MANCC = 'C01';
III.2 Các mặt hàng có thể được cung cấp bởi nhà CC "C01" nhưng chưa được cấp 15 USE QuanLyDonDatHang; SELECT h.MAHG, h.TENHG FROM HANG AS h WHERE h.MANCC = 'C01'
AND h.MAHG NOT IN (SELECT MAHG FROM CTDGH);
III.3 Danh sách khách hàng (mã số và tên) có đặt hàng từ ngày d1 đến ngày d2
-- Giả sử có 2 biến kiểu ngày: -- d1 = 01/05/2010 -- d2 = 09/05/2010 USE QuanLyDonDatHang; SELECT k.MAKH, k.TENKH FROM DDH AS ddh, KHACH AS k
WHERE ddh.NGAYDH BETWEEN '20100501' AND '20100509' AND ddh.MAKH = k.MAKH;
III.4 Liệt kê các mặt hàng (mã số và tên) được giao trong đợt giao "G01" USE QuanLyDonDatHang; SELECT h.MAHG, h.TENHG FROM CTDGH AS ct_dg, HANG AS h WHERE ct_dg.MAHG = h.MAHG AND ct_dg.MADGH = 'G01';
III.5 Liệt kê các mặt hàng (mã số và tên) chưa được đặt hàng bào giờ USE QuanLyDonDatHang; SELECT h.MAHG, h.TENHG
FROM HANG AS h, CTDDH AS ct_ddh
WHERE h.MAHG NOT IN (SELECT MAHG FROM CTDDH);
III.6 Liệt kê các mặt hàng (mã số và tên) được đặt từ ngày d1 đến d2 nhưng chưa giao
-- Giả sử có hai biến kiểu ngày: -- d1 = 02/05/2010 -- d2 = 15/05/2010 USE QuanLyDonDatHang; SELECT h.MAHG, h.TENHG
FROM HANG AS h, DDH AS d, CTDDH AS ct WHERE h.MAHG = ct.MAHG AND ct.MADDH = d.MADDH
AND d.NGAYDH BETWEEN '20100502' AND '20100515'
AND h.MAHG NOT IN (SELECT MAHG FROM CTDGH);
III.7 Số lượng đơn đặt hàng của từng khách hàng từ ngày d1 đến d2
-- Giả sử có hai biến kiểu ngày: -- d1 = 02/04/2010 -- d2 = 15/05/2010 USE QuanLyDonDatHang;
SELECT k.MAKH, k.TENKH, COUNT(*) AS 'Số đơn đặt hàng' FROM DDH AS d, KHACH AS k 16 WHERE k.MAKH = d.MAKH
AND d.NGAYDH BETWEEN '20100402' AND '20100515' GROUP BY k.MAKH, k.TENKH;
III.8 Từ ngày d1 đến d2 khách hàng nào đặt nhiều đơn đặt hàng nhất
-- Giả sử có hai biến kiểu ngày: -- d1 = 02/04/2010 -- d2 = 15/05/2010 USE QuanLyDonDatHang;
SELECT k.MAKH, k.TENKH, COUNT(*) AS 'Số đơn đặt hàng' FROM DDH AS d, KHACH AS k WHERE k.MAKH = d.MAKH
AND d.NGAYDH BETWEEN '20100402' AND '20100615' GROUP BY k.MAKH, k.TENKH
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM DDH AS d, KHACH AS k WHERE k.MAKH = d.MAKH GROUP BY k.MAKH);
III.9 Trị giá của từng đơn đặt hàng từ ngày d1 đến ngày d2
-- Giả sử có hai biến kiểu ngày: -- d1 = 02/04/2010 -- d2 = 15/05/2010 USE QuanLyDonDatHang;
SELECT d.MADDH AS 'Đơn Dặt hàng', SUM(ct.SLDAT * h.DONGIA) AS 'Trị giá'
FROM DDH AS d, CTDDH AS ct, HANG AS h WHERE h.MAHG = ct.MAHG AND ct.MADDH = d.MADDH
AND d.NGAYDH BETWEEN '20100402' AND '20100515' GROUP BY d.MADDH;
III.10 Từ ngày d1 đến d2 đơn đặt hàng nào có giá trị lớn nhất
-- Giả sử có hai biến kiểu ngày: -- d1 = 02/04/2010 -- d2 = 15/05/2010 USE QuanLyDonDatHang;
SELECT d.MADDH AS 'Đơn Dặt hàng', SUM(ct.SLDAT*h.DONGIA) AS 'Trị giá'
FROM DDH AS d, CTDDH AS ct, HANG AS h WHERE h.MAHG = ct.MAHG AND ct.MADDH = d.MADDH
AND d.NGAYDH BETWEEN '20100402' AND '20100515' GROUP BY d.MADDH
HAVING SUM(ct.SLDAT*h.DONGIA) >= ALL(SELECT SUM(ct.SLDAT*h.DONGIA)
FROM DDH AS d,CTDDH AS ct,HANG AS h WHERE h.MAHG = ct.MAHG AND ct.MADDH = d.MADDH AND d.NGAYDH
BETWEEN '20100402' AND '20100515' GROUP BY d.MADDH); 17
Bài 6: CSDL Quản Lý Chuyến Du Lịch I. Tạo DB
-- Bai 6 CSDL Quản Lý Chuyến Dụ Lịch
IF DB_ID('QuanLyChuyenDuLich') IS NULL
CREATE DATABASE QuanLyChuyenDuLich; II. Tạo QH -- Tao QH XE -- Tân từ: Danh sách xe USE QuanLyChuyenDuLich; IF OBJECT_ID('XE') IS NOT NULL DROP TABLE XE CREATE TABLE XE ( MAXE NVARCHAR(50) NOT NULL, BSXE NVARCHAR(50) NOT NULL, MOTAXE NVARCHAR(50),
CONSTRAINT PK_XE PRIMARY KEY (MAXE),
CONSTRAINT UNI_XE UNIQUE (BSXE) );
INSERT INTO XE (MAXE, BSXE, MOTAXE) VALUES ('X01', '1111', ''), ('X02', '2222', ''), ('X03', '3333', ''), ('X04', '4444', ''), ('X05', '5555', ''); --- Tao QH DIADIEM
--- Tân từ: Danh sách các địa điểm USE QuanLyChuyenDuLich;
IF OBJECT_ID('DIADIEM') IS NOT NULL DROP TABLE DIADIEM CREATE TABLE DIADIEM ( MADD NVARCHAR(50) NOT NULL, TENDD NVARCHAR(50) NOT NULL, MOTADD NVARCHAR(50),
CONSTRAINT PK_DIADIEM PRIMARY KEY (MADD) );
INSERT INTO DIADIEM (MADD, TENDD, MOTADD) VALUES ('D01', N'Nha Trang', ''), ('D02', N'Sa pa', ''), ('D03', N'Phú Quốc', ''); -- Tao QH NHANVIEN
-- Tân từ: Danh sách nhân viên USE QuanLyChuyenDuLich;
IF OBJECT_ID('NHANVIEN') IS NOT NULL 18 DROP TABLE NHANVIEN CREATE TABLE NHANVIEN ( MANV NVARCHAR(50) NOT NULL, CMND INT NOT NULL, TENNV NVARCHAR(50) NOT NULL, DCNV NVARCHAR(50) NOT NULL,
CONSTRAINT PK_NHANVIEN PRIMARY KEY (MANV),
CONSTRAINT UNI_NHANVIEN UNIQUE (CMND) );
INSERT INTO NHANVIEN (MANV, CMND, TENNV, DCNV) VALUES
('N01', 1000001, N'Vương Ngọc A', N'Hồ chí minh'),
('N02', 1000002, N'Vương Ngọc B', N'Quảng Nam'),
('N03', 1000003, N'Vương Ngọc C', N'Đồng Nai'),
('N04', 1000004, N'Vương Ngọc D', N'Đà Nẵng'),
('N05', 1000005, N'Vương Ngọc E', N'Quảng Ngãi'),
('N06', 1000006, N'Vương Ngọc F', N'Bình Định'),
('N07', 1000007, N'Vương Ngọc G', N'Phú Yên'); -- Tao QH KHACH
-- Tân từ: Danh mục các khách hàng USE QuanLyChuyenDuLich;
IF OBJECT_ID('KHACH') IS NOT NULL DROP TABLE KHACH CREATE TABLE KHACH ( MAKH NVARCHAR(50) NOT NULL, CMND INT NOT NULL, TENKH NVARCHAR(50) NOT NULL, DCKH NVARCHAR(50),
CONSTRAINT PK_KHACH PRIMARY KEY (MAKH),
CONSTRAINT UNI_KHACH UNIQUE (CMND) );
INSERT INTO KHACH (MAKH, CMND, TENKH, DCKH) VALUES
('K01', 5000001, N'Khách A', N'HCM'),
('K02', 5000002, N'Khách B', N'Đồng Tháp'),
('K03', 5000003, N'Khách C', N'Bình Dương'),
('K04', 5000004, N'Khách D', N'Vũng Tàu'),
('K05', 5000005, N'Khách E', N'Bến tre'),
('K06', 5000006, N'Khách F', N'Bình Dương'),
('K07', 5000007, N'Khách G', N'Huế'),
('K08', 5000008, N'Khách H', N'Vĩnh Long'),
('K09', 5000009, N'Khách I', N'Huế'); -- Tao QH CHUYENDI
-- Tân từ: Danh sách chuyến đi USE QuanLyChuyenDuLich;
IF OBJECT_ID('CHUYENDI') IS NOT NULL DROP TABLE CHUYENDI CREATE TABLE CHUYENDI ( MACD NVARCHAR(50) NOT NULL, NGAYKH DATE NOT NULL, NGAYKT DATE NOT NULL, 19 NOIDUNG NVARCHAR(50),
CONSTRAINT PK_CHUYENDI PRIMARY KEY (MACD) );
INSERT INTO CHUYENDI(MACD, NGAYKH, NGAYKT, NOIDUNG) VALUES
('C01', '20100510', '20100520', ''),
('C02', '20100515', '20100525', ''),
('C03', '20100520', '20100530', ''),
('C04', '20100525', '20100605', ''); -- Tao QH CD_NV
-- Tân từ: Các nhân viên tham gia vào chuyến đi USE QuanLyChuyenDuLich;
IF OBJECT_ID('CD_NV') IS NOT NULL DROP TABLE CD_NV CREATE TABLE CD_NV (
MACD NVARCHAR(50) NOT NULL REFERENCES CHUYENDI,
MANV NVARCHAR(50) NOT NULL REFERENCES NHANVIEN,
CONSTRAINT PK_CD_NV PRIMARY KEY (MACD, MANV) ); INSERT INTO CD_NV (MACD, MANV) VALUES ('C01', 'N01'), ('C01', 'N02'), ('C02', 'N03'), ('C03', 'N04'), ('C03', 'N05'), ('C04', 'N06'); -- Tao QH CD_XE
-- Tân từ: Danh sách xe trong các chuyến đi USE QuanLyChuyenDuLich;
IF OBJECT_ID('CD_XE') IS NOT NULL DROP TABLE CD_XE CREATE TABLE CD_XE (
MACD NVARCHAR(50) NOT NULL REFERENCES CHUYENDI,
MAXE NVARCHAR(50) NOT NULL REFERENCES XE,
CONSTRAINT PK_CD_XE PRIMARY KEY (MACD, MAXE) ); INSERT INTO CD_XE(MACD, MAXE) VALUES ('C01', 'X01'), ('C01', 'X02'), ('C02', 'X03'), ('C02', 'X04'), ('C03', 'X05'), ('C04', 'X05'), ('C04', 'X01'); -- Tao QH CD_DD
-- Tân từ: Các địa điểm mà chuyến đi ghé qua USE QuanLyChuyenDuLich;
IF OBJECT_ID('CD_DD') IS NOT NULL DROP TABLE CD_DD 20

Tài liệu liên quan: