SQL

--7/
DECLARE @maSpTiviBanCuoiNam VARCHAR(255);
DECLARE @maSpTiviBanDauNam VARCHAR(255);

SELECT @maSpTiviBanCuoiNam = STRING_AGG(CHITIETHOADON.masp, ', ')
   FROM CHITIETHOADON
   INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
   INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
   INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.maloaisp
   WHERE LOAISANPHAM.tenloaisanpham = 'TIVI'
     AND ngaythanhtoan >= '2021-07-01'
     AND ngaythanhtoan <= '2021-12-31'

SELECT @maSpTiviBanDauNam = STRING_AGG(CHITIETHOADON.masp, ', ')
   FROM CHITIETHOADON
   INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
   INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
   INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
   WHERE LOAISANPHAM.tenloaisanpham = 'TIVI'
     AND ngaythanhtoan >= '2021-01-01'
     AND ngaythanhtoan <= '2021-06-30'

-- SELECT @maSpTiviBanCuoiNam
-- SELECT @maSpTiviBanDauNam

SELECT *
FROM SANPHAM
WHERE @maSpTiviBanCuoiNam LIKE CONCAT('%', masp, '%')
  AND @maSpTiviBanDauNam NOT LIKE CONCAT('%', masp, '%')

--8/
SELECT TOP 5 SANPHAM.MaSP,
           SANPHAM.tensp,
           SUM(CHITIETHOADON.soluongban) AS SL
FROM CHITIETHOADON
INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
GROUP BY SANPHAM.MaSP,
         SANPHAM.tensp
ORDER BY SL DESC

--9/
SELECT HOADON.mahd,
       HOADON.manv,
       HOADON.makh,
       HOADON.ngaythanhtoan,
       HOADON.phuongthucthanhtoan,
       CHITIETHOADON.SoLuongBan * SANPHAM.GiaBan AS TongTien
FROM CHITIETHOADON
INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp

-- 10/
DECLARE @spBanQ32020 VARCHAR(255);

SET @spBanQ32020 =
  (SELECT STRING_AGG(CHITIETHOADON.masp, ', ')
   FROM CHITIETHOADON
   INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
   INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
   INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
   WHERE ngaythanhtoan >= '2020-10-01'
     AND ngaythanhtoan <= '2020-12-31' );

DECLARE @spBan4Lan2021 VARCHAR(255);

WITH temp AS (
   SELECT CHITIETHOADON.masp,SUM( CHITIETHOADON.soluongban) AS SoLuong
   FROM CHITIETHOADON
   INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
   INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
   INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
   WHERE ngaythanhtoan >= '2021-01-01'
   GROUP BY CHITIETHOADON.masp
)

SELECT @spBan4Lan2021 = STRING_AGG(masp, ', ') FROM temp WHERE SoLuong >= 4

SELECT *
FROM SANPHAM
WHERE @spBanQ32020 NOT LIKE CONCAT('%', masp, '%')
  AND @spBan4Lan2021 NOT LIKE CONCAT('%', masp, '%')

-- 11/
WITH temp AS (
  SELECT LOAISANPHAM.MaLoaiSP, LOAISANPHAM.TenLoaiSanPham, CHITIETHOADON.SoLuongBan * SANPHAM.GiaBan AS TongTien
   FROM CHITIETHOADON
   INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
   INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
   INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
)

SELECT MaLoaiSP, TenLoaiSanPham, SUM(TongTien) AS DoanhThu
  FROM temp
    GROUP BY MaLoaiSP, TenLoaiSanPham
    ORDER BY DoanhThu

-- 12/
DECLARE @nvBanNhieuSpNhat VARCHAR(255);
DECLARE @nvBanDoanhThuCaoNhat VARCHAR(255);

WITH temp1 AS (
  SELECT TOP 1 HOADON.manv, SUM(CHITIETHOADON.SoLuongBan) AS SL
  FROM CHITIETHOADON
  INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
  INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
  INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
  GROUP BY HOADON.manv
  ORDER BY SL DESC
), temp2 AS (
  SELECT HOADON.manv, CHITIETHOADON.SoLuongBan * SANPHAM.GiaBan AS TongTien
  FROM CHITIETHOADON
  INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
  INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
  INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
), temp3 AS (
  SELECT TOP 1 manv, SUM(TongTien) AS DoanhThu
  FROM temp2
  GROUP BY manv
  ORDER BY DoanhThu DESC
)

SELECT @nvBanNhieuSpNhat = manv FROM temp1
SELECT @nvBanDoanhThuCaoNhat = manv FROM temp3

SELECT * FROM NHANVIEN WHERE MaNV = @nvBanNhieuSpNhat OR MaNV = @nvBanDoanhThuCaoNhat

-- 13/
WITH temp AS (
   SELECT CHITIETHOADON.masp,SUM( CHITIETHOADON.soluongban) AS SoLuong
   FROM CHITIETHOADON
   INNER JOIN HOADON ON CHITIETHOADON.MaHD = HOADON.mahd
   INNER JOIN SANPHAM ON CHITIETHOADON.masp = SANPHAM.masp
   INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP
   GROUP BY CHITIETHOADON.masp
)

SELECT SANPHAM.masp, SANPHAM.tensp, LOAISANPHAM.tenloaisanpham, SANPHAM.soluongtrongkho - temp.SoLuong AS SoLuongConLai
  FROM SANPHAM
  INNER JOIN temp ON SANPHAM.masp = temp.masp
  INNER JOIN LOAISANPHAM ON LOAISANPHAM.MaLoaiSP = SANPHAM.MaLoaiSP

-- 14/
UPDATE KHACHHANG
SET DiaChi = 'Da Nang'
WHERE DiaChi = 'Quang Nam' AND makh IN (SELECT MaKH FROM HOADON GROUP BY MaKH HAVING COUNT(*) >= 3)