CREATE DATABASE DEMODB
GO
USE DEMODB
GO

CREATE TABLE HOADON (
    MAHD INT,
    KH NVARCHAR(10),
    TONGTIEN INT,
    PRIMARY KEY (MAHD)
)

CREATE TABLE SANPHAM (
    MASP CHAR(1),
    TENSP NVARCHAR(20),
    GIABAN INT,
    PRIMARY KEY (MASP)
)

CREATE TABLE CHITIET (
    HOADON INT,
    SP CHAR(1),
    SL INT,
    PRIMARY KEY (HOADON, SP)
)

INSERT INTO HOADON VALUES
    (1, N'AN', 6000),
    (2, N'HIẾU', 7000),
    (3, N'AN', 7500)

INSERT INTO SANPHAM VALUES
    ('A', N'CÁ HỘP', 3000),
    ('B', N'KEM TƯƠI', 2500),
    ('C', N'DẦU ĂN', 1500)

INSERT INTO CHITIET VALUES
    (1, 'A', 12),
    (1, 'B', 8),
    (1, 'C', 6),
    (2, 'A', 1),
    (2, 'B', 1),
    (3, 'A', 2),
    (3, 'C', 4)

--1
SELECT COUNT(SP)
FROM CHITIET JOIN HOADON ON (HOADON=MAHD AND KH='AN')

--2
SELECT SUM(SL)
FROM CHITIET JOIN SANPHAM ON (SP=MASP AND TENSP=N'DẦU ĂN')

--3
SELECT KH, COUNT(SP) AS SLSANPHAM
FROM HOADON JOIN CHITIET ON (MAHD=HOADON)
GROUP BY MAHD, KH

--4
SELECT HOADON
FROM CHITIET
GROUP BY HOADON
HAVING COUNT(SP) = (SELECT COUNT(MASP) FROM SANPHAM)