Tiếp tục là một bài test SQL cơ bản của FPT FSOFT năm 2018 vừa rồi. Lập trình không khó bọn mình sẽ chia sẻ và hướng dẫn mọi người tìm ra đáp án của bài test này. Theo mình bài test này hay đấy vì thế nên mình mới chia sẻ cho các bạn :D.
SQL cơ bản FPT FSOFT 2018
Lưu ý:
- KHACH_HANG: lưu trữ thông tin của khách hàng
- PHONG: lưu trữ thông tin phòng karaoke
- DICH_VU_DI_KEM: lưu trữ thông tin các dịch vụ đi kèm được cung cấp tại quán Karaoke.
- DAT_PHONG: lưu trữ thông tin đặt phòng karaoke của khách hàng
- CHI_TIET_SU_DUNG_DV: lưu trữ thông tin chi tiết khi khách hàng sử dụng các dịch vụ đi kèm.
- PK: là các trường tham gia vào khóa chính của bảng
Viết câu lệnh SQL để thực hiện các yêu cầu sau (mỗi yêu cầu chỉ được viết tối đa 1 câu lệnh SQL):
I.
Câu 1:Liệt kê MaDatPhong, MaDV, SoLuong của tất cả các dịch vụ có số lượng lớn hơn 3 và nhỏ hơn 10. (1 điểm)
Câu 2: Cập nhật dữ liệu trên trường GiaPhong thuộc bảng PHONG tăng lên 10,000 VNĐ so với giá phòng hiện tại, chỉ cập nhật giá phòng của những phòng có số khách tối đa lớn hơn 10. (1 điểm)
Câu 3: Xóa tất cả những đơn đặt phòng (từ bảng DAT_PHONG) có trạng thái đặt (TrangThaiDat) là “Da huy”. (1 điểm)
II.
Câu 4: Hiển thị TenKH của những khách hàng có tên bắt đầu là một trong các ký tự “H”, “N”, “M” và có độ dài tối đa là 20 ký tự. (1 điểm)
Câu 5: Hiển thị TenKH của tất cả các khách hàng có trong hệ thống, TenKH nào trùng nhau thì chỉ hiển thị một lần. Sinh viên sử dụng hai cách khác nhau để thực hiện yêu cầu trên, mỗi cách sẽ được 0,5 điểm. (1 điểm)
Câu 6: Hiển thị MaDV, TenDV, DonViTinh, DonGia của những dịch vụ đi kèm có DonViTinh là “lon” và có DonGia lớn hơn 10,000 VNĐ hoặc những dịch vụ đi kèm có DonViTinh là “Cai” và có DonGia nhỏ hơn 5,000 VNĐ. (1 điểm)
Câu 7: Hiển thị MaDatPhong, MaPhong, LoaiPhong, SoKhachToiDa, GiaPhong, MaKH, TenKH, SoDT, NgayDat, GioBatDau, GioKetThuc, MaDichVu, SoLuong, DonGia của những đơn đặt phòng có năm đặt phòng là “2016”, “2017” và đặt những phòng có giá phòng > 50,000 VNĐ/ 1 giờ. (1 điểm)
III.
Câu 8: Hiển thị MaDatPhong, MaPhong, LoaiPhong, GiaPhong, TenKH, NgayDat, TongTienHat, TongTienSuDungDichVu, TongTienThanhToan tương ứng với từng mã đặt phòng có trong bảng DAT_PHONG. Những đơn đặt phòng nào không sử dụng dịch vụ đi kèm thì cũng liệt kê thông tin của đơn đặt phòng đó ra. (1 điểm)
TongTienHat = GiaPhong * (GioKetThuc – GioBatDau)
TongTienSuDungDichVu = SoLuong * DonGia
TongTienThanhToan = TongTienHat + sum (TongTienSuDungDichVu)
Câu 9: Hiển thị MaKH, TenKH, DiaChi, SoDT của những khách hàng đã từng đặt phòng karaoke có địa chỉ ở “Hoa xuan”. (1 điểm)
Câu 10: Hiển thị MaPhong, LoaiPhong, SoKhachToiDa, GiaPhong, SoLanDat của những phòng được khách hàng đặt có số lần đặt lớn hơn 2 lần và trạng thái đặt là “Da dat”. (1 điểm)
– HẾT –
Các bạn nên tự code trước khi tham khảo đáp án của mình từ việc tạo database. Không cần phải làm được ngay chỉ cần não over load có ý tưởng rồi hỏi google. Làm như vậy sẽ nâng cao kĩ năng search google :))
Hướng dẫn:
I. Xây dựng database và insert dữ liệu
CREATE DATABASE LAYLOIHOI_DB
CREATE TABLE KHACH_HANG(
MAKH INT PRIMARY KEY,
TENKH VARCHAR(30),
DIACHI VARCHAR(50),
SODT CHAR(11)
)
CREATE TABLE PHONG(
MAPHONG INT PRIMARY KEY,
LOAIPHONG VARCHAR(20),
SOKHACHTOIDA INT,
GIAPHONG DECIMAL(6,3),
MOTA VARCHAR(255)
)
CREATE TABLE DAT_PHONG(
MADATPHONG INT PRIMARY KEY,
MAPHONG INT,
MAKH INT,
NGAYDAT DATE,
GIOBATDAU TIME,
GIOKETTHUC TIME,
TIENDATCOC DECIMAL(6,3),
GHICHU VARCHAR(255),
TRANGTHAIDAT VARCHAR(30),
FOREIGN KEY (MAPHONG) REFERENCES PHONG(MAPHONG),
FOREIGN KEY (MAKH) REFERENCES khach_hang(MAKH)
)
CREATE TABLE DICH_VU_DI_KEM(
MADV INT PRIMARY KEY,
TENDV VARCHAR(255),
DONVITINH VARCHAR(30),
DONGIA DECIMAL(6,3)
)
CREATE TABLE CHI_TIET_SU_DUNG_DV(
MADATPHONG INT,
MADV INT,
SOLUONG INT,
PRIMARY KEY (MADATPHONG, MADV),
FOREIGN KEY (MADATPHONG) REFERENCES dat_phong(MADATPHONG)
)
-- INSERT DATA
INSERT INTO `phong` (`MAPHONG`, `LOAIPHONG`, `SOKHACHTOIDA`, `GIAPHONG`, `MOTA`) VALUES
(1, 'LOAI 1', 20, '60.000', ''),
(2, 'LOAI 1', 25, '80.000', ''),
(3, 'LOAI 2', 15, '50.000', ''),
(4, 'LOAI 3', 20, '50.000', '');
INSERT INTO `khach_hang` (`MAKH`, `TENKH`, `DIACHI`, `SODT`) VALUES
(1, 'MARIA OZAWA', 'HOA XUAN', '11111111111'),
(2, 'TOKUDA', 'HOA XUAN', '11111111112'),
(3, 'MIKAMI YUA', 'HOA XUAN', '11111111113'),
(4, 'NGUYEN VAN D', 'HOA XUAN', '11111111114');
INSERT INTO `dich_vu_di_kem` (`MADV`, `TENDV`, `DONVITINH`, `DONGIA`) VALUES
(1, 'HOT GIRL', 'EM', '10.000'),
(2, 'HOA HAU', 'EM', '20.000'),
(3, 'BEER', 'LON', '10.000'),
(4, 'TRAI CAY', 'DIA', '35.000');
INSERT INTO `dat_phong` (`MADATPHONG`, `MAPHONG`, `MAKH`, `NGAYDAT`, `GIOBATDAU`, `GIOKETTHUC`, `TIENDATCOC`, `GHICHU`, `TRANGTHAIDAT`) VALUES
(1, 1, 2, '2018-03-26', '11:00:00', '13:00:00', '100.000', '', 'DA DAT'),
(2, 1, 3, '2018-03-27', '17:15:00', '19:15:00', '50.000', '', 'DA HUY'),
(3, 2, 2, '2018-03-26', '20:30:00', '22:15:00', '100.000', '', 'DA DAT'),
(4, 3, 1, '2018-04-01', '19:30:00', '21:15:00', '200.000', '', 'DA DAT');
INSERT INTO `chi_tiet_su_dung_dv` (`MADATPHONG`, `MADV`, `SOLUONG`) VALUES
(1, 1, 20),
(1, 2, 10),
(1, 3, 3),
(2, 2, 10),
(2, 3, 1),
(3, 3, 2),
(3, 4, 10);II. Đáp án Bài test SQL cơ bản FPT FSOFT 2018
Câu 1:
SELECT MADATPHONG, MADV, SOLUONG
FROM chi_tiet_su_dung_dv
WHERE SOLUONG BETWEEN 4 AND 9
Câu 2:
UPDATE phong
SET GIAPHONG = GIAPHONG + 10
WHERE SOKHACHTOIDA > 10
Câu 3:
DELETE
FROM dat_phong
WHERE TRANGTHAIDAT = 'DA HUY'
Câu 4:
SELECT TENKH
FROM khach_hang
WHERE TENKH LIKE '[HNM]%' and LEN(TENKH) <= 20
Câu 5:
SELECT DISTINCT TENKH
FROM khach_hang
SELECT TENKH
FROM khach_hang
GROUP BY TENKH
Câu 6:
SELECT MADV, TENDV, DONVITINH, DONGIA
FROM dich_vu_di_kem
WHERE ( DONVITINH LIKE 'lon' AND DONGIA > 10 ) OR ( DONVITINH LIKE 'CAI' AND DONGIA < 5)
Câu 7: // Sử dụng JOIN kết nối các bảng và dùng hàm hỗ trợ của SQL thôi ^^!
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, ct.SOLUONG, dv.DONGIA
FROM dat_phong dp
JOIN khach_hang kh
on dp.MAKH = kh.MAKH
JOIN phong p
on dp.MAPHONG = p.MAPHONG
JOIN chi_tiet_su_dung_dv ct
ON dp.MADATPHONG = ct.MADATPHONG
JOIN dich_vu_di_kem dv
ON ct.MADV = dv.MADV
WHERE (YEAR(NGAYDAT) = 2016 OR YEAR(NGAYDAT) = 2018) AND P.GIAPHONG > 50
Câu 8: // trong thực tế k tính thời gian bằng GIOKETHUC - GIOBATDAU đâu đây là người ta cho sẵn biểu thức thế thôi, mà chúng ta phải dùng hàm TIMEDIFF(time_end, time_start ) để tính chính xác thời gian và lấy ra Giờ phút giây của nó bằng hàm DATEPART(), tiếp đó tính tổng số giây. Nó cho giá phòng trong 1 giờ thì chúng ta phải đổi ra 1 giây bao nhiêu tiền, Tổng tiền phòng bằng tổng giây * tiền phòng 1 giây ^^!
SELECT dp.MADATPHONG, p.MAPHONG, p.LOAIPHONG, p.GIAPHONG, kh.TENKH, dp.NGAYDAT, p.GIAPHONG * (GIOKETTHUC - GIOBATDAU) AS TONGTIENHAT, SUM(ct.SOLUONG * dv.DONGIA) AS TONGTIENSUDUNGDV, ( p.GIAPHONG * (GIOKETTHUC - GIOBATDAU) + SUM(CT.SOLUONG * dv.DONGIA ))
FROM dat_phong dp
JOIN khach_hang kh
on dp.MAKH = kh.MAKH
JOIN phong p
on dp.MAPHONG = p.MAPHONG
JOIN chi_tiet_su_dung_dv ct
ON dp.MADATPHONG = ct.MADATPHONG
JOIN dich_vu_di_kem dv
ON ct.MADV = dv.MADV
GROUP BY dp.MADATPHONG
Câu 9: // Câu này có thể dùng IN hoặc EXIST khuyến khích dùng EXISTS vì SQL SERVER sẽ đưa ra kết quả nhanh hơn
SELECT kh.MAKH, kh.TENKH, kh.DIACHI, kh.SODT
FROM khach_hang kh
WHERE kh.DIA CHI = 'HOA XUAN' AND EXISTS (
SELECT *
FROM dat_phong dp
WHERE kh.MAKH = dp.MAKH )
Câu 10: // Câu 10 điểm này theo mình thì là câu cho điểm 10 :D
SELECT p.MAPHONG,p.LOAIPHONG,p.SOKHACHTOIDA,p.GIAPHONG, COUNT(dp.MAPHONG) AS SOLUONGDAT
FROM phong p
JOIN dat_phong dp
ON p.MAPHONG = dp.MAPHONG
WHERE dp.TRANGTHAIDAT = 'DA DAT'
GROUP BY p.MAPHONG
HAVING COUNT(dp.MAPHONG) > 2
Ok vậy là mình đã xử lý xong bài test SQL cơ bản FPT FSOFT 2018. Tiếp theo sẽ là bài test của năm 2019 :D. Tiếp sau đó mình sẽ chia sẻ những bài test SQL nâng cao, Java cơ bản và Java nâng cao cũng của FPT luôn. Mình hi vọng bài chia sẻ này sẽ giúp cho công việc học tập của mọi người. Mọi thắc mắc cứ để lại comment ở dưới mình sẽ giải quyết !
Xem bài viết tiếp theo tại đây.
Để lại một bình luận