Tài liệu đang xem: Bài 1

use KHACHHANG;
--yc1--
select HoaDon.maHoaDon, HoaDonChiTiet.maHoaDon, HoaDon.maKhachHang,
HoaDon.trangThai, HoaDonChiTiet.maSanPham, HoaDonChiTiet.soLuong, HoaDon.ngayMuaHang 
FROM HoaDon,HoaDonChiTiet 
WHERE HoaDon.maHoaDon=HoaDonChiTiet .maHoaDon;
--yc2--
select HoaDon.maHoaDon, HoaDonChiTiet.maHoaDon, HoaDon.maKhachHang,
HoaDon.trangThai, HoaDonChiTiet.maSanPham, HoaDonChiTiet.soLuong,
HoaDon.ngayMuaHang 
FROM HoaDon,HoaDonChiTiet 
WHERE HoaDon.maHoaDon=HoaDonChiTiet .maHoaDon and maKhachHang='KH001';
--yc3--
select HoaDon.maHoaDon, HoaDon.ngayMuaHang, SanPham.tenSP,SanPham.donGia, 
SanPham.soLuong, HoaDonChiTiet.soLuong, 
SanPham.soLuong* SanPham.donGia as ThanhTienSanPham, HoaDonChiTiet.soLuong * SanPham.soLuong as ThanhTienHoaDonChiTiet
FROM HoaDon 
JOIN HoaDonChiTiet ON HoaDon.maHoaDon = HoaDonChiTiet.maHoaDon
JOIN SanPham ON HoaDonChiTiet.maSanPham = SanPham.maSanPham
--yc4--
select KhachHang.hoVaTenLot + KhachHang.Ten as HoVaTen, KhachHang.Email, KhachHang.dienThoai, 
HoaDon.maHoaDon, HoaDonChiTiet.maHoaDon, HoaDon.trangThai, 
SUM(SanPham.donGia * HoaDonChiTiet.soLuong) AS TongTien 
FROM KhachHang 
JOIN HoaDon ON KhachHang.maKhachHang = HoaDon.maKhachHang
JOIN HoaDonChiTiet ON HoaDon.maHoaDon = HoaDonChiTiet.maHoaDon
JOIN SanPham ON HoaDonChiTiet.maSanPham = SanPham.maSanPham
WHERE trangThai='Chưa thanh toán'
GROUP BY KhachHang.hoVaTenLot, KhachHang.Ten, KhachHang.email, KhachHang.dienThoai,
HoaDon.maHoaDon, HoaDonChiTiet.maHoaDon, HoaDon.trangThai;
--yc5--
SELECT HoaDon.maHoaDon, HoaDon.ngayMuaHang, 
SUM(SanPham.soLuong * SanPham.donGia) AS ThanhTienSanPham, 
SUM(HoaDonChiTiet.soLuong * SanPham.donGia) AS ThanhTienHoaDonChiTiet
FROM HoaDon
JOIN HoaDonChiTiet ON HoaDon.maHoaDon = HoaDonChiTiet.maHoaDon
JOIN SanPham ON HoaDonChiTiet.maSanPham = SanPham.maSanPham
GROUP BY HoaDon.maHoaDon, HoaDon.ngayMuaHang
HAVING 
SUM(HoaDonChiTiet.soLuong * SanPham.donGia) >= 500.000 
and
SUM(SanPham.soLuong * SanPham.donGia) >= 500.000
ORDER BY 
ThanhTienHoaDonChiTiet DESC,
ThanhTienSanPham DESC;
--yc5--
SELECT HoaDon.maHoaDon, HoaDon.ngayMuaHang, 
SUM(HoaDonChiTiet.soLuong * SanPham.donGia) AS ThanhTienHoaDonChiTiet
FROM HoaDon
JOIN HoaDonChiTiet ON HoaDon.maHoaDon = HoaDonChiTiet.maHoaDon
JOIN SanPham ON HoaDonChiTiet.maSanPham = SanPham.maSanPham
GROUP BY HoaDon.maHoaDon, HoaDon.ngayMuaHang
HAVING 
SUM(HoaDonChiTiet.soLuong * SanPham.donGia) >= 500.000
ORDER BY 
ThanhTienHoaDonChiTiet DESC;