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

use KHACHHANG;
--yc1--
UPDATE KhachHang
SET dienThoai='16267788989'
WHERE maKhachHang='KH002';
--yc2--
UPDATE SanPham
SET soLuong= soLuong +'200'
WHERE maSanPham='3';
--yc3--
UPDATE SanPham
SET donGia= donGia * 0.95;
--yc4--
UPDATE SanPham 
SET soluong = soluong + 100 
WHERE maSanPham = (
SELECT TOP 1 maSanPham
FROM HoaDonChiTiet inner join HoaDon ON HoaDon.maHoaDon=HoaDonChiTiet.maHoaDon
WHERE YEAR(ngaymuahang) = 2016 and MONTH(ngaymuahang) = 12
GROUP BY maSanPham
ORDER BY SUM(HoaDonChiTiet.soluong) DESC); 
--yc5--
UPDATE SanPham 
SET donGia = donGia - donGia * 0.1 
WHERE maSanPham IN (
SELECT TOP 2 maSanPham
FROM HoaDonChiTiet inner join HoaDon ON HoaDon.maHoaDon=HoaDonChiTiet.maHoaDon
WHERE YEAR(ngaymuahang) = 2016 and MONTH(ngaymuahang) = 12
GROUP BY maSanPham
ORDER BY SUM(HoaDonChiTiet.soluong));
--yc6--
UPDATE HoaDon
SET trangThai='Đã thanh toán'
WHERE maHoaDon='120956';
--yc7--
DELETE FROM HoaDonChiTiet
WHERE maSanPham='2'and maHoaDon='120956'
UPDATE HoaDon
SET trangThai='chưa thanh toán'
WHERE maHoaDon='120956';
--yc8--
DELETE FROM KhachHang
WHERE maKhachHang NOT IN(
SELECT DISTINCT HoaDon.maKhachHang FROM HoaDon
WHERE HoaDon.ngayMuaHang >= '01-01-2016');