-- Tạo Database
CREATE DATABASE CHMP;
USE CHMP;
-- Tạo bảng NHANVIEN
CREATE TABLE NHANVIEN (
MaNV CHAR(5) NOT NULL PRIMARY KEY,
TenNV VARCHAR(30),
DiaChiNV VARCHAR(50),
SDTNV VARCHAR(15),
ChucVu VARCHAR(30)
);
-- Tạo bảng MENU
CREATE TABLE MENU (
MaMN CHAR(5) NOT NULL PRIMARY KEY,
MaHang CHAR(8),
TenHangBan VARCHAR(30),
DonGia INT,
SoLuongBan INT
);
-- Tạo bảng KHACH
CREATE TABLE KHACH (
MaKhach CHAR(5) NOT NULL PRIMARY KEY,
TenKhach VARCHAR(30),
DiaChiKhach VARCHAR(50),
SDTKhach VARCHAR(15),
MaNV CHAR(5) NOT NULL,
MaMN CHAR(5) NOT NULL,
FOREIGN KEY (MaNV) REFERENCES NHANVIEN (MaNV),
FOREIGN KEY (MaMN) REFERENCES MENU (MaMN)
);
-- Tạo bảng LUONG
CREATE TABLE LUONG (
MaLuong CHAR(6) NOT NULL PRIMARY KEY,
MaNV CHAR(5),
ThanhToan INT,
FOREIGN KEY (MaNV) REFERENCES NHANVIEN (MaNV)
);
-- Tạo bảng HOADON
CREATE TABLE HOADON (
MaHD CHAR(5) NOT NULL PRIMARY KEY,
Ngaytao DATE,
Tongtien INT,
TienThua INT,
MaNV CHAR(5) NOT NULL,
MaKhach CHAR(5) NOT NULL,
FOREIGN KEY (MaNV) REFERENCES NHANVIEN (MaNV),
FOREIGN KEY (MaKhach) REFERENCES KHACH (MaKhach)
);
-- Tạo bảng NHACUNGCAP
CREATE TABLE NHACUNGCAP (
MaNCC CHAR(5) NOT NULL PRIMARY KEY,
TenNCC VARCHAR(30),
DiaChiNCC VARCHAR(30),
SDTNCC VARCHAR(15),
NgayHopTac DATE,
MaNV CHAR(5) NOT NULL,
FOREIGN KEY (MaNV) REFERENCES NHANVIEN (MaNV)
);
-- Tạo bảng LOAIHANG
CREATE TABLE LOAIHANG (
MaHang CHAR(8) NOT NULL PRIMARY KEY,
TenHangNhap VARCHAR(30),
SoLuongNhap INT,
NgayNhan DATE,
NgayHetHan DATE,
MaNCC CHAR(5) NOT NULL,
FOREIGN KEY (MaNCC) REFERENCES NHACUNGCAP (MaNCC)
);
-- Tạo bảng TONKHO
CREATE TABLE TONKHO (
MaHang CHAR(8) NOT NULL,
TenHangTon VARCHAR(30),
SoLuongTon INT,
FOREIGN KEY (MaHang) REFERENCES LOAIHANG (MaHang)
);
-- Tạo bảng THANHTOAN
CREATE TABLE THANHTOAN (
MaKhach CHAR(5) NOT NULL,
MaNV CHAR(5) NOT NULL,
MaHD CHAR(5) NOT NULL,
PRIMARY KEY (MaKhach, MaNV, MaHD),
FOREIGN KEY (MaKhach) REFERENCES KHACH (MaKhach),
FOREIGN KEY (MaNV) REFERENCES NHANVIEN (MaNV),
FOREIGN KEY (MaHD) REFERENCES HOADON (MaHD)
);
-- Thêm dữ liệu vào bảng NHANVIEN
INSERT INTO NHANVIEN (MaNV, TenNV, DiaChiNV, SDTNV, ChucVu)
VALUES
('NV1', 'Nguyễn Văn A', 'Hà Nội', '0123456789', 'Nhân viên bán hàng'),
('NV2', 'Trần Thị B', 'Hải Phòng', '0987654321', 'Quản lý'),
('NV3', 'Nguyễn Ngọc C', 'Đà Nẵng', '0345678901', 'Nhân viên kho'),
('NV4', 'Nguyễn Minh D', 'Khánh Hòa', '0765432189', 'Nhân viên giao hàng'),
('NV5', 'Vũ Thị E', 'Nam Định', '0234567890', 'Thu ngân');
-- Thêm dữ liệu vào bảng MENU
INSERT INTO MENU (MaMN, MaHang, TenHangBan, DonGia, SoLuongBan)
VALUES
('M023', 'H001', 'Son môi', 150000, 10),
('M012', 'H002', 'Kem dưỡng da', 200000, 20),
('M029', 'H003', 'Sữa rửa mặt', 100000, 15),
('M014', 'H004', 'Mặt nạ', 250000, 5),
('M030', 'H005', 'Phấn nền', 300000, 8);
-- Thêm dữ liệu vào bảng KHACH
INSERT INTO KHACH (MaKhach, TenKhach, DiaChiKhach, SDTKhach, MaNV, MaMN)
VALUES
('12340', 'Nguyễn Minh Trang', 'Bắc Ninh', '0968452178', 'NV1', 'M023'),
('12341', 'Vũ Minh Ngọc', 'Hải Phòng', '0912345678', 'NV2', 'M012'),
('12342', 'Trần Thị Thủy', 'Quảng Ninh', '0987564321', 'NV3', 'M029'),
('12343', 'Tạ Quỳnh Chi', 'Bắc Giang', '0909876543', 'NV4', 'M014'),
('12344', 'Vũ Phương Thảo', 'Hà Nam', '0911987654', 'NV5', 'M030');
INSERT INTO NHACUNGCAP (MaNCC, TenNCC, DiaChiNCC, SDTNCC, NgayHopTac, MaNV)
VALUES
('001', 'Nhà Cung Cấp A', 'Địa chỉ A', '0123456789', '2020-04-08', 'NV1'),
('002', 'Nhà Cung Cấp B', 'Địa chỉ B', '0987654321', '2021-01-15', 'NV2'),
('003', 'Nhà Cung Cấp C', 'Địa chỉ C', '0123456780', '2019-06-20', 'NV3'),
('004', 'Nhà Cung Cấp D', 'Địa chỉ D', '0198765432', '2022-03-30', 'NV4'),
('005', 'Nhà Cung Cấp E', 'Địa chỉ E', '0112233445', '2023-09-01', 'NV5');
-------- phan lam bai tap -------------
-- update thong tin
-- cap nhat thong tin bang nha cung cap co ma nha cung cap 001 o dia chi A thanh ha noi
UPDATE NHACUNGCAP
SET DiaChiNCC = 'Ha Noi'
WHERE MaNCC = 001;
-- cap nhat thong tin bang khach tu ma khach 12341 nguyen minh ngoc thanh nguyen van B
UPDATE KHACH
SET TenKhach = 'Nguyen Van B'
WHERE MaKhach = 12341;
-- cap nhat thong tin hoa don co ma khach la 54334 co tong tien la 4000vnd len 50000vnd
UPDATE HOADON
SET TongTien = 50000
WHERE MaKhach = '54334';
-- cap nhat lai thong tin dia chi khach co ma khach la 12340
update KHACH
set DiaChiKhach = 'Dia Chi A'
where MaKhach = '12340';
-- delete thong tin
-- xoa thong tin nha cung cap co ma nha cung cap la 001
DELETE FROM NHACUNGCAP
WHERE MaNCC = 001;
-- xoa thong tin khach hang co ma khach hang la 12344
DELETE FROM KHACH
WHERE MaKhach = '12344';
-- xoa thong tin hoa don co gia tien lon hon hoac bang 20 ngan dong
DELETE FROM HOADON
WHERE TongTien >= 20000;
-- xoa ten hang mat na trong bang menu
DELETE FROM MENU
WHERE TenHangban = 'Mặt nạ';
-- xoa dia chi khach o bac ninh
delete from KHACH
where DiaChiKhach = 'Bắc Ninh';
-- 1/ truy van tim kiem thong tin
-- lay toan bo thong tin khach hang co ten la nguyen ngoc C
SELECT *
FROM khacH
WHERE TenKhach = 'Nguyễn Ngọc C';
-- lay thong tin ma khach va ten khach tu bang khach, co ma hoa don thanh toan la 11
select MaKhach, TenKhach
from KHACH as cus join THANHTOAN as tt
on [Link] = [Link]
where MaHD = '11';
-- ??????????????
-- lay ra thong tin tu bang hoa don co ngay tao la 9/10/2002 va tong tien be hon 30 ngan dong
SELECT *
FROM HOADON
WHERE NgayTao = '9/10/2002' AND TongTien <= 30000;
-- tinh tong so luong nhan vien theo ma nhan vien
SELECT MaLuong, SUM(ThanhToan) AS TotalSalary
FROM LUONG
GROUP BY MaLuong;
-- lay ra ten nhan vien co chuc vu nhan vien ban hang va so luong dc tra
select TenNV
from NHANVIEN AS nv join LUONG AS sal
on [Link] = [Link]
where ChucVu = 'Nhân viên bán hàng';
-- 2/ them du lieu cac truong du lieu
-- them thong tin bang luong
INSERT INTO LUONG (MaLuong, MaNV, ThanhToan)
VALUES
('131235', 'Nguyen Van B', 200000),
('131235', 'Nguyen Van C', 300000),
('131235', 'Nguyen Van D', 5000000);
-- them thong tin bang ton kho
INSERT INTO TONKHO (MaHang, TenHangTon, SoLuongTon)
VALUES
('676345', 'Kem danh rang', '3'),
('676448', 'Bot Mi', '9'),
('673231', 'Sua tuoi', '5'),
('662343', 'Banh', '1'),
('612312', 'dao', '7'),
('242341', 'sung', '2'),
('676345', 'Kem danh rang', '3');
-- them thong tin bang hoa don
INSERT INTO HOADON (NgayTao, Tongtien, TienThua, MaNV, MaKhach)
VALUES
('2/4/2002', 20000, 10000, '12324', '54345'),
('9/10/2024', 100000, 50000, '87827', '12334'),
('8/4/2012', 20000, 10000, '12324', '54345'),
('1/4/2022', 300000, 60000, '12324', '54345'),
('6/3/2024', 4000, 2000, '12325', '54334'),
('5/2/2011', 120000, 100000, '12324', '54345'),
('7/3/2010', 220000, 11000, '12324', '54345');
-- them thong tin bang loai hang
INSERT INTO LOAIHANG (MaHang, TenHangNhap, SoLuongNhap, NgayNhan, NgayHetHan, MaNCC)
VALUES
('e23315','Kem danh rang', 12, '7/3/2010','6/3/2024','001'),
('d23315', 'banh ngot', 32, '18/4/2010','6/3/2024','002'),
('c23315', 'sua tuoi', 22, '4/2/2011','6/3/2024','003'),
('b4415', 'do choi', 12, '8/3/2012','6/3/2024','004'),
('a67321', 'lego', 19, '9/5/2013','6/3/2024','005');
-- them du lieu bang thanh toan
INSERT INTO THANHTOAN (MaKhach, MaNV, MaHD)
VALUES
('12340', 'NV1','11'),
('12341', 'NV2', '22'),
('12342', 'NV3', '33'),
('12343', 'NV4', '44');
-- 3/ tim kiem nhan vien duoc tra luong tren 2 trieu vnd
SELECT *
from LUONG
where ThanhToan > 2000000;
-- 4/ tinh tong so luong hang da nhap cua moi mat hang
SELECT MaHang, SUM(SoLuongTon) AS Total
FROM TONKHO
GROUP BY MaHang;
-- 5/ tinh so luong hang ton kho cua mot san pham
SELECT TenHangTon, SoLuongTon
FROM TONKHO
WHERE MaHang = '676345';
-- lay toan bo ten mat hang tu menu
select TenHangBan
from MENU;
-- 6/ dua ra thong tin khach hang co dia chi o Quang Ninh
SELECT *
FROM KHACH
WHERE DiaChiKhach = 'Quang Ninh';