Untitled

-- tao database
CREATE DATABASE BAN_HANG;

-- tao cac bang
CREATE TABLE HANG_HOA (
    MAHH VARCHAR(4),
    TENHH VARCHAR(50),
    DVT VARCHAR(50),
    DONGIA INT
);

CREATE TABLE NHAN_VIEN (
    MANV VARCHAR(4),
    HOTENNV VARCHAR(50),
    GIOITINH BOOL,
    DINHMUC INT
);

CREATE TABLE BAN_HANG (
    SOHD VARCHAR(4),
    MAHH VARCHAR(4),
    MANV VARCHAR(4),
    SLBAN INT
);

-- tao khoa chinh / khoa ngoai
ALTER TABLE HANG_HOA ADD PRIMARY KEY (MAHH);
ALTER TABLE NHAN_VIEN ADD PRIMARY KEY (MANV);
ALTER TABLE BAN_HANG ADD FOREIGN KEY (MAHH) REFERENCES HANG_HOA(MAHH);
ALTER TABLE BAN_HANG ADD FOREIGN KEY (MANV) REFERENCES NHAN_VIEN(MANV);

-- nhap du lieu
INSERT INTO HANG_HOA VALUES
    ('MH01', 'Romano', 'Chai', 45000),
    ('MH02', 'Johnson', 'Chai', 55000),
    ('MH03', 'Enchenter', 'Chai', 35000),
    ('MH04', 'Clear', 'Chai', 65000),
    ('MH05', 'Double Rich', 'Chai', 60000);

INSERT INTO NHAN_VIEN VALUES
    ('NV01', 'Nguyễn Đắc Thăng', TRUE, 70),
    ('NV02', 'Vũ Thị Hạnh', FALSE, 60),
    ('NV03', 'Phan Thanh Tâm', TRUE, 80),
    ('NV04', 'Nguyễn Kim Đức', TRUE, 100),
    ('NV05', 'Nguyễn TrongTuê', TRUE, 90);

INSERT INTO BAN_HANG VALUES
    ('X001', 'MH01', 'NV01', 120),
    ('X001', 'MH01', 'NV05', 390),
    ('X002', 'MH02', 'NV02', 150),
    ('X002', 'MH02', 'NV04', 360),
    ('X003', 'MH03', 'NV03', 80),
    ('X003', 'MH03', 'NV04', 180),
    ('X004', 'MH04', 'NV02', 300),
    ('X004', 'MH04', 'NV03', 210),
    ('X005', 'MH05', 'NV01', 70),
    ('X005', 'MH05', 'NV05', 90);

-- cau 3.1
SELECT NHAN_VIEN.MANV, NHAN_VIEN.HOTENNV, HANG_HOA.MAHH, HANG_HOA.TENHH, NHAN_VIEN.DINHMUC, BAN_HANG.SLBAN, HANG_HOA.DONGIA, (BAN_HANG.SLBAN * HANG_HOA.DONGIA) AS THANHTIEN
    FROM HANG_HOA INNER JOIN (NHAN_VIEN INNER JOIN BAN_HANG ON NHAN_VIEN.MANV = BAN_HANG.MANV) ON HANG_HOA.MAHH = BAN_HANG.MAHH
    WHERE NHAN_VIEN.HOTENNV LIKE 'Nguyễn%';

-- cau 3.2
SELECT NHAN_VIEN.MANV, NHAN_VIEN.HOTENNV, HANG_HOA.MAHH, HANG_HOA.TENHH, NHAN_VIEN.DINHMUC, BAN_HANG.SLBAN, HANG_HOA.DONGIA, (BAN_HANG.SLBAN * HANG_HOA.DONGIA) AS THANHTIEN
	FROM HANG_HOA INNER JOIN (NHAN_VIEN INNER JOIN BAN_HANG ON NHAN_VIEN.MANV = BAN_HANG.MANV) ON HANG_HOA.MAHH = BAN_HANG.MAHH
    WHERE BAN_HANG.SLBAN > NHAN_VIEN.DINHMUC
    ORDER BY BAN_HANG.SLBAN;

-- cau 3.3
SELECT HANG_HOA.MAHH, HANG_HOA.TENHH, SUM(BAN_HANG.SLBAN) AS TONGSOLUONG
	FROM HANG_HOA INNER JOIN BAN_HANG ON HANG_HOA.MAHH = BAN_HANG.MAHH
    GROUP BY HANG_HOA.MAHH
    ORDER BY HANG_HOA.MAHH;