15 trang 30 lượt tải

Thực hành cơ sở dữ liệu: Hướng dẫn chi tiết các câu lệnh SQL môn An toàn ứng dụng web và cơ sở dữ liệu | Học viện Công Nghệ Bưu Chính Viễn Thông

59

Thực hành cơ sở dữ liệu: Hướng dẫn chi tiết các câu lệnh SQL môn An toàn ứng dụng web và cơ sở dữ liệu. Tài liệu được sưu tầm gồm 15 trang, giúp các bạn nắm vững kiến thức, rèn luyện kỹ năng và đạt được kết quả tốt trong học tập. Mời các bạn đón xem!

Môn: An toàn ứng dụng web và cơ sở dữ liệu 10 tài liệu

Trường: Học viện Công Nghệ Bưu Chính Viễn Thông 1.2 K tài liệu

Tác giả:

Profile

My Lữ

2 tháng trước
Danh sách Quiz
/15
lOMoARcPSD| 58647650
THỰC HÀNH CƠ SỞ DỮ LIỆU
A.Svtech:
1.Môi trường:
1.1.Hiển thị phiên bản của database:
Select version();
1.2.Hiển thị danh sách database:
Show databases;
1.3.Hiển thị danh sách các table của một database:
Show table from dblab;
1.4. Hiển thị cấu trúc của một table.
Show columns from sinhvien;
(sinhvien là tên của 1 table có trong database dblab)
1.5. Hiển thị user đang kết nối:
Select user();
lOMoARcPSD| 58647650
1.6. Hiển thị database(schema) đang sử dụng.Select
database();
1.7. Hiển thị ngày giờ hiện tại của database.Select now();
2.DDL
2.1 Tạo bảng khoa: create table khoa(
ma_khoa varchar(4) primary key,
ten_khoa varchar(100) not null
);
2. 2.Tạo bảng sinh viên:
CREATE TABLE sinhvien(
masinhvien INT (10) NOT
NULL, ten VARCHAR(100) NOT
NULL, ngaysinh DATE NOT
NULL, makhoa VARCHAR(4),
diachi VARCHAR(200),
PRIMARY KEY(masinhvien),
lOMoARcPSD| 58647650
FOREIGN KEY(makhoa) REFERENCES khoa(ma_khoa)
);
2.3. Bổ sung thêm cột “dienthoai” cho bảng sinh viên.
ALTER TABLE sinhvien
ADD COLUMN dienthoai VARCHAR(20);
2.4. Mở độ rộng cho cột dienthoai để có lưu số điện thoại có cả mã vùng và
số máynội bộ.
ALTER TABLE sinhvien
MODIFY COLUMN dienthoai VARCHAR(30);
2.5. Bổ sung constraint cho bảng sinhvien để (tên, ngày sinh) không trùng
nhau.
ALTER TABLE sinhvien
ADD CONSTRAINT unique_ten_ngaysinh UNIQUE (ten, ngay_sinh);
2.6. Tạo một view chỉ cho hiển thị sinh viên của một khoa có mã nhất định
CREATE VIEW view_sinhvien_theo_khoa AS
SELECT ma, ten, ngaysinh, dienthoai, diachi
FROM sinhvien
WHERE makhoa = 'KHOA_ID'; -- Thay 'KHOA_ID' bằng mã khoa cụ thể
2.7. Gán quyền cho người dùng dblab.user02 có quyền them bản ghi vào
bảngsinhvien
lOMoARcPSD| 58647650
GRANT INSERT ON sinhvien TO 'dblab.user02'@'localhost';
2.8. Tạo thêm index cho cột dienthoai của bảng sinhvien.
CREATE INDEX idx_dienthoai ON sinhvien(dienthoai);
3.DML
3.1. Thêm 3 bản ghi mới vào table khoa:
SELECT *FROM dblab.khoa;
INSERT INTO khoa(ma_khoa,ten_khoa)
VALUES ('VT',
'Vien_thong'), ('CNTT', 'Cong_nghe_thong_tin'), ('DT', 'Dien_tu');
3.2. Thêm 3 bản ghi mới vào bảng sinhvien:
SELECT * FROM dblab.sinhvien;
INSERT INTO sinhvien (masinhvien, ten, ngaysinh, makhoa, diachi, dienthoai)
VALUES (1, 'Nguyen Van A', '2003-06-05', 'VT','Ha_noi ','0383123878'),
(1234, 'Tran Van B', '2003-05-25', 'CNTT','Bac_Ninh','0584262343'),
(235, 'Nguyen Thi C', '2003-08-25', 'DT','Hai_Phong','029347869');
lOMoARcPSD| 58647650
3.3, 3.4
-Cập nhật số điện thoại thêm vào đầu mỗi số chuỗi '+84'. -
Cập nhật số điện thoại thêm vào đầu mỗi số chuỗi '+84'. Nếu
số điện thoại đã có mã quốc tế thì không được thêm vào nữa.
SELECT * FROM dblab.sinhvien;
SET SQL_SAFE_UPDATES = 0;
UPDATE sinhvien
SET dienthoai = CONCAT('+84', dienthoai)
WHERE dienthoai NOT LIKE '+%';
4. DQL:
4.1 Tìm nhân viên trong tên có chữ “A”:
SELECT * FROM hr.employees
WHERE first_name LIKE '%A%';
4.2 Tìm nhân viên trong họ tên có chữ “V”:
SELECT * FROM hr.employees
WHERE first_name LIKE '%V%' OR last_name LIKE '%V%';
4.3 Tìm nhân viên chưa có phân công vào phòng nào:
SELECT * FROM hr.employees
WHERE department_id IS NULL;
4.4 Tìm nhân viên vào công ty trong năm 2000:
lOMoARcPSD| 58647650
SELECT * FROM hr.employees
WHERE YEAR(hire_date) = 2000;
4.5 Tìm nhân viên vào công ty trong các tháng chẵn:
SELECT * FROM hr.employees
WHERE MONTH(hire_date) IN (2, 4, 6, 8, 10, 12);
4.6 Hiển thị danh sách phòng cùng mã nhân viên, họ, tên, ngày vào công ty,
thâmniên của nhân viên có thâm niên trên 30 năm:
SELECT d.department_name, e.employee_id, e.first_name, e.last_name,
e.hire_date,
TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS seniority_years
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
WHERE TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) > 30;
4.7 Hiển thị tổng số nhân viên của công ty: SELECT COUNT(*) AS
total_employees
FROM hr.employees;
4.8 Hiển thị tên phòng và tổng số nhân viên của phòng đó
SELECT d.department_name, COUNT(e.employee_id) AS total_employees
FROM hr.departments d
LEFT JOIN hr.employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
4.9 Hiển thị tên phòng và tổng số nhân viên của phòng đó, nếu không có
nhân viênthì hiển thị là 0:
SELECT d.department_name, COUNT(e.employee_id) AS total_employees
FROM hr.departments d
lOMoARcPSD| 58647650
LEFT JOIN hr.employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
4.10 Hiển thị danh sách phòng cùng mã nhân viên, họ, tên, ngày vào công
ty, lươngcủa nhân viên có lương cao nhất phòng:
SELECT d.department_name, e.employee_id, e.first_name, e.last_name,
e.hire_date, e.salary
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
WHERE e.salary = (
SELECT MAX(salary)
FROM hr.employees e2
WHERE e2.department_id = e.department_id
);
B.KIỂM TRA THỰC HÀNH:
CREATE schema thsql;
USE thsql;
SET SQL_SAFE_UPDATES = 0;
*Tạo bảng phong:
CREATE TABLE PHONG (
MaPhong VARCHAR(10) PRIMARY KEY,
LoaiPhong VARCHAR(50),
SoKhachToiDa INT,
GiaPhong DECIMAL(10, 2),
MoTa TEXT
);
lOMoARcPSD| 58647650
* Bảng KHACH_HANG
CREATE TABLE KHACH_HANG (
MaKH VARCHAR(10) PRIMARY KEY,
TenKH VARCHAR(100),
DiaChi VARCHAR(100),
SoDT VARCHAR(15)
);
*Bảng DICH_VU_DI_KEM
CREATE TABLE DICH_VU_DI_KEM (
MaDV VARCHAR(10) PRIMARY KEY,
TenDV VARCHAR(100),
DonViTinh VARCHAR(50),
DonGia DECIMAL(10, 2)
);
lOMoARcPSD| 58647650
*Bảng DAT_PHONG
CREATE TABLE DAT_PHONG (
MaDatPhong VARCHAR(10) PRIMARY KEY,
MaPhong VARCHAR(10),
MaKH VARCHAR(10),
NgayDat DATE,
GioBatDau TIME,
GioKetThuc TIME,
TienDatCoc DECIMAL(10, 2),
GhiChu VARCHAR(100),
TrangThaiDat VARCHAR(20),
FOREIGN KEY (MaPhong) REFERENCES PHONG(MaPhong),
FOREIGN KEY (MaKH) REFERENCES KHACH_HANG(MaKH)
);
*Bảng CHI_TIET_SU_DUNG_DV
CREATE TABLE CHI_TIET_SU_DUNG_DV (
MaDatPhong VARCHAR(10),
MaDV VARCHAR(10),
SoLuong INT,
PRIMARY KEY (MaDatPhong, MaDV),
FOREIGN KEY (MaDatPhong) REFERENCES DAT_PHONG(MaDatPhong),
FOREIGN KEY (MaDV) REFERENCES DICH_VU_DI_KEM(MaDV)
lOMoARcPSD| 58647650
);
*Dữ liệu cho bảng PHONG
INSERT INTO PHONG (MaPhong, LoaiPhong, SoKhachToiDa, GiaPhong, MoTa)
VALUES
('P0001', 'Loai 1', 20, 60000, NULL),
('P0002', 'Loai 1', 25, 80000, NULL),
('P0003', 'Loai 2', 15, 50000, NULL),
('P0004', 'Loai 3', 20, 50000, NULL);
*Dữ liệu cho bảng KHACH_HANG
INSERT INTO KHACH_HANG (MaKH, TenKH, DiaChi, SoDT)
VALUES
('KH0001', 'Nguyen Van A', 'Hoa xuan', '1111111111'),
('KH0002', 'Nguyen Van B', 'Hoa hai', '1111111112'),
('KH0003', 'Phan Van A', 'Cam le', '1111111113'),
('KH0004', 'Phan Van B', 'Hoa xuan', '1111111114');
lOMoARcPSD| 58647650
*Dữ liệu cho bảng DICH_VU_DI_KEM
INSERT INTO DICH_VU_DI_KEM (MaDV, TenDV, DonViTinh, DonGia)
VALUES
('DV001', 'Beer', 'lon', 10000),
('DV002', 'Nuoc ngot', 'lon', 8000),
('DV003', 'Trai cay', 'dia', 35000),
('DV004', 'Khan uot', 'cai', 2000);
*Dữ liệu cho bảng DAT_PHONG
INSERT INTO DAT_PHONG (MaDatPhong, MaPhong, MaKH, NgayDat,
GioBatDau, GioKetThuc, TienDatCoc, GhiChu, TrangThaiDat)
VALUES
('DP0001', 'P0001', 'KH0002', '2018/03/26', '11:00', '13:30', 100000, 'Da dat', 'Da
dat'),
('DP0002', 'P0003', 'KH0003', '2018/03/27', '17:15', '19:15', 500000, 'Da huy', 'Da
huy'),
('DP0003', 'P0002', 'KH0002', '2018/03/30', '20:30', '22:00', 300000, 'Da dat', 'Da
dat'),
lOMoARcPSD| 58647650
('DP0004', 'P0001', 'KH0001', '2018/04/01', '19:30', '21:15', 200000, 'Da dat', 'Da
dat');
*Dữ liệu cho bảng CHI_TIET_SU_DUNG_DV
INSERT INTO CHI_TIET_SU_DUNG_DV (MaDatPhong, MaDV, SoLuong)
VALUES
('DP0001', 'DV001', 20),
('DP0001', 'DV002', 10),
('DP0001', 'DV003', 5),
('DP0001', 'DV004', 10),
('DP0002', 'DV001', 10),
('DP0002', 'DV004', 5),
('DP0003', 'DV001', 10),
('DP0003', 'DV002', 10),
('DP0004', 'DV001', 20),
('DP0004', 'DV002', 10);
lOMoARcPSD| 58647650
Câu 1:
SELECT MaDatPhong, MaDV, SoLuong
FROM CHI_TIET_SU_DUNG_DV
WHERE SoLuong > 10 AND SoLuong < 20;
Câu 2:
UPDATE PHONG
SET GiaPhong = GiaPhong + 15000
WHERE SoKhachToiDa > 20;
Câu 3:
DELETE FROM DAT_PHONG
WHERE TrangThaiDat = 'Da huy';
Câu 4:
SELECT TenKH
FROM KHACH_HANG
WHERE LEFT(TenKH, 1) IN ('H', 'V', 'M') AND LENGTH(TenKH) <= 20;
Câu 5:
SELECT DISTINCT TenKH
lOMoARcPSD| 58647650
FROM KHACH_HANG;
Câu 6:
SELECT MaDV, TenDV, DonViTinh, DonGia
FROM DICH_VU_DI_KEM
WHERE (DonViTinh = 'dia' AND DonGia > 35000)
OR (DonViTinh = 'Cai' AND DonGia < 5000);
Câu 7:
SELECT dp.MaDatPhong, dp.MaPhong, p.LoaiPhong, p.SoKhachToiDa,
p.GiaPhong, kh.MaKH, kh.TenKH, kh.SoDT, dp.NgayDat, dp.GioBatDau,
dp.GioKetThuc, dv.MaDV, dvct.SoLuong, dv.DonGia
FROM DAT_PHONG dp
JOIN PHONG p ON dp.MaPhong = p.MaPhong
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH
LEFT JOIN CHI_TIET_SU_DUNG_DV dvct ON dp.MaDatPhong =
dvct.MaDatPhong
LEFT JOIN DICH_VU_DI_KEM dv ON dvct.MaDV = dv.MaDV
WHERE YEAR(dp.NgayDat) = 2018
AND p.GiaPhong < 80000;
Câu 8:
SELECT dp.MaDatPhong, p.MaPhong, p.LoaiPhong, p.GiaPhong, kh.TenKH,
dp.NgayDat,
(p.GiaPhong * (TIME_TO_SEC(dp.GioKetThuc) -
TIME_TO_SEC(dp.GioBatDau)) / 3600) AS TongTienHat,
COALESCE(SUM(dvct.SoLuong * dv.DonGia), 0) AS
TongTienSuDungDichVu,
(p.GiaPhong * (TIME_TO_SEC(dp.GioKetThuc) -
TIME_TO_SEC(dp.GioBatDau)) / 3600)
lOMoARcPSD| 58647650
+ COALESCE(SUM(dvct.SoLuong * dv.DonGia), 0) AS TongTienThanhToan
FROM DAT_PHONG dp
JOIN PHONG p ON dp.MaPhong = p.MaPhong
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH
LEFT JOIN CHI_TIET_SU_DUNG_DV dvct ON dp.MaDatPhong =
dvct.MaDatPhong
LEFT JOIN DICH_VU_DI_KEM dv ON dvct.MaDV = dv.MaDV
GROUP BY dp.MaDatPhong, p.MaPhong, p.LoaiPhong, p.GiaPhong, kh.TenKH,
dp.NgayDat, dp.GioBatDau, dp.GioKetThuc; Câu 9:
SELECT kh.MaKH, kh.TenKH, kh.DiaChi, kh.SoDT
FROM DAT_PHONG dp
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH
WHERE kh.DiaChi = 'Hoa xuan';
Câu 10:
SELECT kh.MaKH, kh.TenKH, kh.DiaChi, kh.SoDT
FROM DAT_PHONG dp
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH
WHERE kh.DiaChi = 'Hoa xuan';

Tài liệu khác của Học viện Công Nghệ Bưu Chính Viễn Thông

Preview text:

lOMoAR cPSD| 58647650
THỰC HÀNH CƠ SỞ DỮ LIỆU A.Svtech: 1.Môi trường:
1.1.Hiển thị phiên bản của database: Select version();
1.2.Hiển thị danh sách database: Show databases;
1.3.Hiển thị danh sách các table của một database: Show table from dblab;
1.4. Hiển thị cấu trúc của một table. Show columns from sinhvien;
(sinhvien là tên của 1 table có trong database dblab)
1.5. Hiển thị user đang kết nối: Select user(); lOMoAR cPSD| 58647650
1.6. Hiển thị database(schema) đang sử dụng.Select database();
1.7. Hiển thị ngày giờ hiện tại của database.Select now(); 2.DDL
2.1 Tạo bảng khoa: create table khoa(
ma_khoa varchar(4) primary key,
ten_khoa varchar(100) not null ); 2. 2.Tạo bảng sinh viên: CREATE TABLE sinhvien( masinhvien INT (10) NOT NULL, ten VARCHAR(100) NOT NULL, ngaysinh DATE NOT NULL, makhoa VARCHAR(4), diachi VARCHAR(200), PRIMARY KEY(masinhvien), lOMoAR cPSD| 58647650
FOREIGN KEY(makhoa) REFERENCES khoa(ma_khoa) );
2.3. Bổ sung thêm cột “dienthoai” cho bảng sinh viên. ALTER TABLE sinhvien
ADD COLUMN dienthoai VARCHAR(20);
2.4. Mở độ rộng cho cột dienthoai để có lưu số điện thoại có cả mã vùng và số máynội bộ. ALTER TABLE sinhvien
MODIFY COLUMN dienthoai VARCHAR(30);
2.5. Bổ sung constraint cho bảng sinhvien để (tên, ngày sinh) không trùng nhau. ALTER TABLE sinhvien
ADD CONSTRAINT unique_ten_ngaysinh UNIQUE (ten, ngay_sinh);
2.6. Tạo một view chỉ cho hiển thị sinh viên của một khoa có mã nhất định
CREATE VIEW view_sinhvien_theo_khoa AS
SELECT ma, ten, ngaysinh, dienthoai, diachi FROM sinhvien
WHERE makhoa = 'KHOA_ID'; -- Thay 'KHOA_ID' bằng mã khoa cụ thể
2.7. Gán quyền cho người dùng dblab.user02 có quyền them bản ghi vào bảngsinhvien lOMoAR cPSD| 58647650
GRANT INSERT ON sinhvien TO 'dblab.user02'@'localhost';
2.8. Tạo thêm index cho cột dienthoai của bảng sinhvien.
CREATE INDEX idx_dienthoai ON sinhvien(dienthoai); 3.DML
3.1. Thêm 3 bản ghi mới vào table khoa: SELECT *FROM dblab.khoa;
INSERT INTO khoa(ma_khoa,ten_khoa) VALUES ('VT',
'Vien_thong'), ('CNTT', 'Cong_nghe_thong_tin'), ('DT', 'Dien_tu');
3.2. Thêm 3 bản ghi mới vào bảng sinhvien: SELECT * FROM dblab.sinhvien;
INSERT INTO sinhvien (masinhvien, ten, ngaysinh, makhoa, diachi, dienthoai)
VALUES (1, 'Nguyen Van A', '2003-06-05', 'VT','Ha_noi ','0383123878'),
(1234, 'Tran Van B', '2003-05-25', 'CNTT','Bac_Ninh','0584262343'),
(235, 'Nguyen Thi C', '2003-08-25', 'DT','Hai_Phong','029347869'); lOMoAR cPSD| 58647650 3.3, 3.4
-Cập nhật số điện thoại thêm vào đầu mỗi số chuỗi '+84'. -
Cập nhật số điện thoại thêm vào đầu mỗi số chuỗi '+84'. Nếu
số điện thoại đã có mã quốc tế thì không được thêm vào nữa. SELECT * FROM dblab.sinhvien; SET SQL_SAFE_UPDATES = 0; UPDATE sinhvien
SET dienthoai = CONCAT('+84', dienthoai)
WHERE dienthoai NOT LIKE '+%'; 4. DQL:
4.1 Tìm nhân viên trong tên có chữ “A”: SELECT * FROM hr.employees WHERE first_name LIKE '%A%';
4.2 Tìm nhân viên trong họ tên có chữ “V”: SELECT * FROM hr.employees
WHERE first_name LIKE '%V%' OR last_name LIKE '%V%';
4.3 Tìm nhân viên chưa có phân công vào phòng nào: SELECT * FROM hr.employees WHERE department_id IS NULL;
4.4 Tìm nhân viên vào công ty trong năm 2000: lOMoAR cPSD| 58647650 SELECT * FROM hr.employees WHERE YEAR(hire_date) = 2000;
4.5 Tìm nhân viên vào công ty trong các tháng chẵn: SELECT * FROM hr.employees
WHERE MONTH(hire_date) IN (2, 4, 6, 8, 10, 12);
4.6 Hiển thị danh sách phòng cùng mã nhân viên, họ, tên, ngày vào công ty,
thâmniên của nhân viên có thâm niên trên 30 năm:
SELECT d.department_name, e.employee_id, e.first_name, e.last_name, e.hire_date,
TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS seniority_years FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
WHERE TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) > 30;
4.7 Hiển thị tổng số nhân viên của công ty: SELECT COUNT(*) AS total_employees FROM hr.employees;
4.8 Hiển thị tên phòng và tổng số nhân viên của phòng đó
SELECT d.department_name, COUNT(e.employee_id) AS total_employees FROM hr.departments d
LEFT JOIN hr.employees e ON d.department_id = e.department_id GROUP BY d.department_name;
4.9 Hiển thị tên phòng và tổng số nhân viên của phòng đó, nếu không có
nhân viênthì hiển thị là 0:
SELECT d.department_name, COUNT(e.employee_id) AS total_employees FROM hr.departments d lOMoAR cPSD| 58647650
LEFT JOIN hr.employees e ON d.department_id = e.department_id GROUP BY d.department_name;
4.10 Hiển thị danh sách phòng cùng mã nhân viên, họ, tên, ngày vào công
ty, lươngcủa nhân viên có lương cao nhất phòng:
SELECT d.department_name, e.employee_id, e.first_name, e.last_name, e.hire_date, e.salary FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id WHERE e.salary = ( SELECT MAX(salary) FROM hr.employees e2
WHERE e2.department_id = e.department_id );
B.KIỂM TRA THỰC HÀNH: CREATE schema thsql; USE thsql; SET SQL_SAFE_UPDATES = 0; *Tạo bảng phong: CREATE TABLE PHONG (
MaPhong VARCHAR(10) PRIMARY KEY, LoaiPhong VARCHAR(50), SoKhachToiDa INT, GiaPhong DECIMAL(10, 2), MoTa TEXT ); lOMoAR cPSD| 58647650 * Bảng KHACH_HANG CREATE TABLE KHACH_HANG (
MaKH VARCHAR(10) PRIMARY KEY, TenKH VARCHAR(100), DiaChi VARCHAR(100), SoDT VARCHAR(15) ); *Bảng DICH_VU_DI_KEM CREATE TABLE DICH_VU_DI_KEM (
MaDV VARCHAR(10) PRIMARY KEY, TenDV VARCHAR(100), DonViTinh VARCHAR(50), DonGia DECIMAL(10, 2) ); lOMoAR cPSD| 58647650 *Bảng DAT_PHONG CREATE TABLE DAT_PHONG (
MaDatPhong VARCHAR(10) PRIMARY KEY, MaPhong VARCHAR(10), MaKH VARCHAR(10), NgayDat DATE, GioBatDau TIME, GioKetThuc TIME, TienDatCoc DECIMAL(10, 2), GhiChu VARCHAR(100), TrangThaiDat VARCHAR(20),
FOREIGN KEY (MaPhong) REFERENCES PHONG(MaPhong),
FOREIGN KEY (MaKH) REFERENCES KHACH_HANG(MaKH) );
*Bảng CHI_TIET_SU_DUNG_DV
CREATE TABLE CHI_TIET_SU_DUNG_DV ( MaDatPhong VARCHAR(10), MaDV VARCHAR(10), SoLuong INT,
PRIMARY KEY (MaDatPhong, MaDV),
FOREIGN KEY (MaDatPhong) REFERENCES DAT_PHONG(MaDatPhong),
FOREIGN KEY (MaDV) REFERENCES DICH_VU_DI_KEM(MaDV) lOMoAR cPSD| 58647650 );
*Dữ liệu cho bảng PHONG
INSERT INTO PHONG (MaPhong, LoaiPhong, SoKhachToiDa, GiaPhong, MoTa) VALUES
('P0001', 'Loai 1', 20, 60000, NULL),
('P0002', 'Loai 1', 25, 80000, NULL),
('P0003', 'Loai 2', 15, 50000, NULL),
('P0004', 'Loai 3', 20, 50000, NULL);
*Dữ liệu cho bảng KHACH_HANG
INSERT INTO KHACH_HANG (MaKH, TenKH, DiaChi, SoDT) VALUES
('KH0001', 'Nguyen Van A', 'Hoa xuan', '1111111111'),
('KH0002', 'Nguyen Van B', 'Hoa hai', '1111111112'),
('KH0003', 'Phan Van A', 'Cam le', '1111111113'),
('KH0004', 'Phan Van B', 'Hoa xuan', '1111111114'); lOMoAR cPSD| 58647650
*Dữ liệu cho bảng DICH_VU_DI_KEM
INSERT INTO DICH_VU_DI_KEM (MaDV, TenDV, DonViTinh, DonGia) VALUES
('DV001', 'Beer', 'lon', 10000),
('DV002', 'Nuoc ngot', 'lon', 8000),
('DV003', 'Trai cay', 'dia', 35000),
('DV004', 'Khan uot', 'cai', 2000);
*Dữ liệu cho bảng DAT_PHONG
INSERT INTO DAT_PHONG (MaDatPhong, MaPhong, MaKH, NgayDat,
GioBatDau, GioKetThuc, TienDatCoc, GhiChu, TrangThaiDat) VALUES
('DP0001', 'P0001', 'KH0002', '2018/03/26', '11:00', '13:30', 100000, 'Da dat', 'Da dat'),
('DP0002', 'P0003', 'KH0003', '2018/03/27', '17:15', '19:15', 500000, 'Da huy', 'Da huy'),
('DP0003', 'P0002', 'KH0002', '2018/03/30', '20:30', '22:00', 300000, 'Da dat', 'Da dat'), lOMoAR cPSD| 58647650
('DP0004', 'P0001', 'KH0001', '2018/04/01', '19:30', '21:15', 200000, 'Da dat', 'Da dat');
*Dữ liệu cho bảng CHI_TIET_SU_DUNG_DV
INSERT INTO CHI_TIET_SU_DUNG_DV (MaDatPhong, MaDV, SoLuong) VALUES ('DP0001', 'DV001', 20), ('DP0001', 'DV002', 10), ('DP0001', 'DV003', 5), ('DP0001', 'DV004', 10), ('DP0002', 'DV001', 10), ('DP0002', 'DV004', 5), ('DP0003', 'DV001', 10), ('DP0003', 'DV002', 10), ('DP0004', 'DV001', 20), ('DP0004', 'DV002', 10); lOMoAR cPSD| 58647650 Câu 1:
SELECT MaDatPhong, MaDV, SoLuong FROM CHI_TIET_SU_DUNG_DV
WHERE SoLuong > 10 AND SoLuong < 20; Câu 2: UPDATE PHONG
SET GiaPhong = GiaPhong + 15000 WHERE SoKhachToiDa > 20; Câu 3: DELETE FROM DAT_PHONG
WHERE TrangThaiDat = 'Da huy'; Câu 4: SELECT TenKH FROM KHACH_HANG
WHERE LEFT(TenKH, 1) IN ('H', 'V', 'M') AND LENGTH(TenKH) <= 20; Câu 5: SELECT DISTINCT TenKH lOMoAR cPSD| 58647650 FROM KHACH_HANG; Câu 6:
SELECT MaDV, TenDV, DonViTinh, DonGia FROM DICH_VU_DI_KEM
WHERE (DonViTinh = 'dia' AND DonGia > 35000)
OR (DonViTinh = 'Cai' AND DonGia < 5000); Câu 7:
SELECT dp.MaDatPhong, dp.MaPhong, p.LoaiPhong, p.SoKhachToiDa,
p.GiaPhong, kh.MaKH, kh.TenKH, kh.SoDT, dp.NgayDat, dp.GioBatDau,
dp.GioKetThuc, dv.MaDV, dvct.SoLuong, dv.DonGia FROM DAT_PHONG dp
JOIN PHONG p ON dp.MaPhong = p.MaPhong
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH
LEFT JOIN CHI_TIET_SU_DUNG_DV dvct ON dp.MaDatPhong = dvct.MaDatPhong
LEFT JOIN DICH_VU_DI_KEM dv ON dvct.MaDV = dv.MaDV WHERE YEAR(dp.NgayDat) = 2018 AND p.GiaPhong < 80000; Câu 8:
SELECT dp.MaDatPhong, p.MaPhong, p.LoaiPhong, p.GiaPhong, kh.TenKH, dp.NgayDat,
(p.GiaPhong * (TIME_TO_SEC(dp.GioKetThuc) -
TIME_TO_SEC(dp.GioBatDau)) / 3600) AS TongTienHat,
COALESCE(SUM(dvct.SoLuong * dv.DonGia), 0) AS TongTienSuDungDichVu,
(p.GiaPhong * (TIME_TO_SEC(dp.GioKetThuc) -
TIME_TO_SEC(dp.GioBatDau)) / 3600) lOMoAR cPSD| 58647650
+ COALESCE(SUM(dvct.SoLuong * dv.DonGia), 0) AS TongTienThanhToan FROM DAT_PHONG dp
JOIN PHONG p ON dp.MaPhong = p.MaPhong
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH
LEFT JOIN CHI_TIET_SU_DUNG_DV dvct ON dp.MaDatPhong = dvct.MaDatPhong
LEFT JOIN DICH_VU_DI_KEM dv ON dvct.MaDV = dv.MaDV
GROUP BY dp.MaDatPhong, p.MaPhong, p.LoaiPhong, p.GiaPhong, kh.TenKH,
dp.NgayDat, dp.GioBatDau, dp.GioKetThuc; Câu 9:
SELECT kh.MaKH, kh.TenKH, kh.DiaChi, kh.SoDT FROM DAT_PHONG dp
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH WHERE kh.DiaChi = 'Hoa xuan'; Câu 10:
SELECT kh.MaKH, kh.TenKH, kh.DiaChi, kh.SoDT FROM DAT_PHONG dp
JOIN KHACH_HANG kh ON dp.MaKH = kh.MaKH WHERE kh.DiaChi = 'Hoa xuan';

Tài liệu liên quan: