Đồ án Quản lý hoạt động kinh doanh của công ty Phong Vũ

a Sửa CongNoBan + - +(SoTien) R4 : trên quan hệ CongNoNhap thuộc tính SoTien phải là số nguyên lớn hơn 0. " t Î CongNoNhap, t.SoTien Î N* . Bảng tầm ảnh hưởng. R4 Thêm Xóa Sửa CongNoNhap + - +(SoTien) R5 : trên quan hệ HangHoa thuộc tính SoLuong va DonGia phải là những số tự nhiên lớn hơn 0. " t Î HangHoa , t.SoLuong Î N* ^ t.DonGia Î N*. Bảng tầm ảnh huởng. R5 Thêm Xóa Sửa HangHoa + - +(SoLuong,DonGia) R6 :trên quan hệ HoaDon thuộc tính TongGiaTri phải là số nguyên lớn hơn 0.(đơn vị của TonhGiaTri là đồng) " t Î HoaDon, t.TongGiaTri Î N* . Bảng tầm ảnh hưởng : R6 Thêm Xóa Sửa HoaDon + - +(TongGiaTri) R7 : trên quan hệ PhieuChi thuộc tính SoTien phài là số nguyên lớn hơn 0. " t Î PhieuChi, t.SoTien Î N* . Bảng tầm ảnh hưởng. R7 Thêm Xóa Sửa PhieuChi + - +(SoTien) R8 : trên quan hệ PhieuNhapHang thuộc tính TongGiaTri phải là số nguyên lớn hơn 0. " t Î PhieuNhapHang, t.TongGiaTri Î N* . Bảng tầm ảnh hưởng. R8 Thêm Xóa Sửa PhieuNhapHang + - +(TongGiaTri) R9 : trên quan hệ PhieuThu thuộc tính SoTien phải là số nguyên lớn hơn 0. " t Î PhieuThu, t.SoTien Î N* . Bảng tầm ảnh hưởng. R9 Thêm Xóa Sửa PhieuThu + - +(SoTien) Ràng buộc trên nhiều quan hệ. 2.1 Ràng buộc phụ thuộc tồn tại: R10: Trong mô hình thực thề và mối kết hợp ở trên nếu 2 đối tượng có liên hệ 1---∞ thì luôn tồn tại 1 ràng buộc phụ thuộc tồn tại là ràng buộc khóa ngoại. Ràng buộc này được mô tả là: Gọi q1 là quan hệ của lược đồ quan hệ Q mô tả thông tin về đối tượng ở nhánh 1 , q2 là quan hệ của lược đồ quan hệ S mô tả thông tin về đối tượng ở nhánh ∞, K là khóa chính của lược đồ quan hệ Q, thì trong S sẽ tồn tại khóa ngoại là K. Khi đó với giá trị k của mỗi dòng trong quan hệ q2 phải tương ứng tồn tại trong quan hệ q1. Đặc tả bằng ngôn ngữ hình thức: " t Î q2 , $t’Î q1 : t.K = t’.K. Bảng tầm ảnh hưởng. R10 Thêm Xóa Sửa q1 - + - q2 + - +(K) R11 : Khi 2 đối tượng trong mô hình thực thể và mối kết hợp có liên hệ ∞----∞ với nhau thông qua mối kết hợp thì cũng tồn tại ràng buộc phụ thuộc tồn tại giữa mối kết hợp với 2 đối tượng. Ràng buộc này được mô tả. Gọi q1 là quan hệ của lược đồ quan hệ Q mô tả thông tin về đối tượng 1, q2 là quan hệ của lược đồ quan hệ S mô tả thông tin về đối tượng 2, q3 là quan hệ của lược đồ quan hệ P mô tả thông tin về mối kết hợp, K là khóa chính của lược đồ quan hệ Q, K’ là khóa chính trong lược đồ quan hệ S thì trong P sẽ tồn tại khóa ngoại là K và K’. Khi đó với giá trị K và K’ của mỗi dòng trong quan hệ q2 phải tương ứng tồn tại trong quan hệ q1 và q2. Đặc tả bằng ngôn ngữ hình thức: " tÎ q3, $ t1’Î q1, t2’Î q2 : t.K=t1’.K ^ t.K’=t2’.K’. Bảng tầm ảnh hưởng R11 Thêm Xóa Sửa q1 - + - q2 - + - q3 + - +(K) +(K’) R12 : Trên quan hệ NhanVien và LyLich, với mỗi giá trị MaNV trong quan hệ NhanVien phải tồn tại duy nhất 1 giá trị MaNV trong quan hệ LyLich và ngược lại. Đặc tả bằng ngôn nhữ hình thức. " tÎ NhanVien, $! t’ÎLyLich: t.MaNV=t’.MaNV. " tÎ LyLich, $! t’ÎNhanVien: t.MaNV=t’.MaNV. Bảng tầm ảnh hưởng R12 Thêm Xóa Sửa NhanVien + + - LyLich + + - Ràng buộc liên thuộc tính – liên quan hệ. R13 : Trên quan hệ HoaDon và CongNoBan thuộc tính NgayLap trong quan hệ HoaDon phải có giá trị nhỏ hơn thuộc tính NgayHen trong quan hệ CongNoBan và thuộc tính TongGiaTri trong quan hệ HoaDon phải có giá trị lơn hơn hoặc bằng thuộc tính SoTien trong quan hệ CongNoBan trên cùng một giá tri MaHD. "tÎHoaDon,t’Î CongNoBan t.MaHD = t’.MaHD ® t.NgayLap = t’.SoTien. Bảng tầm ảnh hưởng. R13 Thêm Xóa Sửa HoaDon + - +(NgayLap, TongGiaTri) ConNoBan + - +(NgayHen,SoTien) R14 : Trên quan hệ HoaDon và PhieuThu thuộc tính NgayLap trong quan hệ HoaDon phải có gái trị nhỏ hơn hoặc bằng thuộc tính NgayThu trong quan hệ PhieuThu và thuộc tính TongGiaTri trong quan hệ HoaDon phải có giá trị lơn hơn hoặc bằng thuộc tính SoTien trong quan hệ PhieuThu trên cùng một giá tri MaHD. "tÎHoaDon,t’Î PhieuThu t.MaHD = t’.MaHD ® t.NgayLap = t’.SoTien. Bảng tầm ảnh hưởng. R14 Thêm Xóa Sửa HoaDon + - +(NgayLap. TongGiaTri) PhieuThu + - +(NgayThu,SoTien) R15 : Trên quan hệ PhieuNhapHang và CongNoNhap thuộc tính NgayLap trong quan hệ PhieuNhapHang phải có giá trị nhỏ thuộc tính NgayHen trong quan hệ CongNoNhap và thuộc tính TongGiaTri trong quan hệ PhieuNhapHang phải có giá trị lơn hơn hoặc bằng thuộc tính SoTien trong quan hệ CongNoNhap trên cùng một giá tri MaPN. "tÎPhieuNhapHang,t’Î CongNoNhap t.MaPN= t’.MaPN ® t.NgayLap = t’.SoTien. Bảng tầm ảnh hưởng. R15 Thêm Xóa Sửa PhieuNhapHang + - +(NgayLap, TongGiaTri) CongNoNhap + - +(NgayHen,SoTien) R16 : Trên quan hệ PhieuNhapHang và PhieuChi thuộc tính NgayLap trong quan hệ PhieuNhapHang phải có giá trị nhỏ hơn hoặc bằng thuộc tính NgayChi trong quan hệ PhieuChi và thuộc tính TongGiaTri trong quan hệ PhieuNhapHang phải có giá trị lơn hơn hoặc bằng thuộc tính SoTien trong quan hệ PhieuChi trên cùng một giá tri MaPN. "tÎPhieuNhapHang, t’Î PhieuChi t.MaPN = t’.MaPN ® t.NgayLap = t’.SoTien. Bảng tầm ảnh hưởng. R16 Thêm Xóa Sửa PhieuNhaphang + - +(NgayLap, TongGiaTri) PhieuChi + - +(NgayChi.SoTien) R17 : Trên quan hệ HoaDon và BaoHanh thuộc tính NgayLap trong quan hệ HoaDon phải nhỏ thuộc tính NgayBH trong quan hệ BaoHanh trên cùng một giá tri MaHD. "tÎHoaDon, t’Î BaoHanh t.MaHD= t’.MaHD ® t.NgayLap < t’.NgayBH. Bảng tầm ảnh hưởng. R17 Thêm Xóa Sửa HoaDon + - +(NgayLap) BaoHanh + - +(NgayBH) Ràng buộc liên bộ – liên quan hệ. R18 : Trên quan hệ HoaDon, PhieuThu, CongNoBan nếu giá trị MaHD ở bảng HoaDon. Không tồn tại giá trị tương ứng ở quan hệ CongNoBan thì sẽ tốn tại giá trị tương ứng ở quan hệ PhieuThu sao cho tổng giá trị ở thuộc tính SoTien trong quan hệ PhieuThu có MaHD tương ứng với MaHD ở quan hệ HoaDon sẽ bằng thuộc tính TongGiaTri ở quan hệ HoaDon. Không tồn tại giá trị tương ứng trong quan hệ PhieuThu thì sẻ tồn tại giá trị tương ứng ở quan hệ CongNoBan sao cho thuộc tính SoTien trong quan hệ CongNoBan có giá trị bằng thuộc tính TongGiaTri ở quan hệ HoaDon trên cùng giá trị MaHD. Tồn tại giá trị tương ứng ở quan hệ PhieuThu và quan hệ CongNoBan thì tổng giá trị ở thuộc tính SoTien trong quan hệ PhieuThu cộng với giá trị SoTien trong quan hệ CongNoBan phải bằng giá trị thuộc tính TongGiaTri trong quan hệ HoaDon. Đặc tả bằng ngôn ngữ hình thức. " tÎ HoaDon ∄t1Î CongNoBan ® $ t2Î PhieuThu : t.TongGiaTri = S t2.SoTien ^ t.MaHD = t2.MaHD. ∄t2 Î PhieuThu ® $ t1Î CongNoBan: t.TongGiaTri = t1.SoTien ^ t.MaHD = t1.MaHD. $t1Î CongNoBan,t2 Î PhieuThu: t.MaHD = t1.MaHD = t2.MaHD ^ t.TongGiaTri = S t2.SoTien + t1.SoTien . Bảng tầm ảnh hưởng. R18 Thêm Xóa Sửa HoaDon + - +(TongGiaTri) CongNoBan + + +(SoTien) PhieuThu + + +(SoTien) R19 : Trên quan hệ PhieuNhapHang, PhieuChi, CongNoNhap nếu giá trị MaPN ở bảng PhieuNhapHang. Không tồn tại giá trị tương ứng ở quan hệ CongNoNhap thì sẽ tốn tại giá trị tương ứng ở quan hệ PhieuChi sao cho tổng giá trị ở thuộc tính SoTien trong quan hệ PhieuChi có MaPN tương ứng với MaPN ở quan hệ PhieuNhapHang sẽ bằng thuộc tính TongGiaTri ở quan hệ PhieuNhapHang Không tồn tại giá trị tương ứng trong quan hệ PhieuChi thì sẻ tồn tại giá trị tương ứng ở quan hệ CongNoNhap sao cho thuộc tính SoTien trong quan hệ CongNoNhap có giá trị bằng thuộc tính TongGiaTri ở quan hệ PhieuNhapHang trên cùng giá trị MaPN. Tồn tại giá trị tương ứng ở quan hệ PhieuChi và quan hệ CongNoNhap thì tổng giá trị ở thuộc tính SoTien trong quan hệ PhieuChi cộng với giá trị SoTien trong quan hệ CongNoNhap phải bằng giá trị thuộc tính TongGiaTri trong quan hệ PhieuNhapHang. Đặc tả bằng ngôn ngữ hình thức. " tÎ PhieuNhapHang ∄t1Î CongNoNhap ® $ t2Î PhieuChi : t.TongGiaTri = S t2.SoTien ^ t.MaHD = t2.MaHD. ∄t2 Î PhieuChi ® $ t1Î CongNoNhap: t.TongGiaTri = t1.SoTien ^ t.MaHD = t1.MaHD. $t1Î CongNoNhap,t2 Î PhieuChi: t.MaHD = t1.MaHD = t2.MaHD ^ t.TongGiaTri = S t2.SoTien + t1.SoTien . Bảng tầm ảnh hưởng. R19 Thêm Xóa Sửa PhieuNhapHang + - +(TongGiaTri) CongNoNhap + + +(SoTien) PhieuChi + + +(SoTien) R20 : Trên quan hệ HoaDon và quan hệ ChiTietHoaDon giá trị thuộc tính TongGiaTri trong quan hệ HoaDon phải bằng tổng giá trị thuộc tính SoLuong nhân thuộc tính DonGia trong quan hệ ChiTietHoaDon trên cùng một giá trị MaHD. Đặc tả bằng ngôn ngữ hình thức. " t Î HoaDon $ t’ÎChiTietHoaDon: t.TongGiaTri =S(t’.SoLuong * t’.DonGia) ^ t.MaHD = t’.MaHD. Bảng tấm ảnh hưởng. R20 Thêm Xóa Sửa HoaDon + - +(TongGiaTri) ChiTietHoaDon + + +(SoLuong,DonGia) R21 : Trên quan hệ PhieuNhapHang và quan hệ ChiTietPhieuNhapHang giá trị thuộc tính TongGiaTri trong quan hệ PhieuNhapHang phải bằng tổng giá trị thuộc tính SoLuong nhân thuộc tính DonGia trong quan hệ ChiTietPhieuNhapHang trên cùng một giá trị MaPN. Đặc tả bằng ngôn ngữ hình thức. " t Î PhieuNhapHang$ t’ÎChiTietPhieuNhapHang: t.TongGiaTri =S(t’.SoLuong * t’.DonGia) ^ t.MaPN = t’.MaPN. Bảng tấm ảnh hưởng. R21 Thêm Xóa Sửa PhieuNhapHang + - +(TongGiaTri) ChiTietPhieuNhapHang + + +(SoLuong,DonGia) Chương IV: Giải Pháp Bằng SQL Yêu cầu về quản lý nhân sự: b.1.3 Lập danh sách tính tổng số nhân viên của một bộ phận, danh sách gồm mã bộ phận, tên bộ phận, tổng số nhân viên. SELECT BP.MaBP, BP.TenBP, COUNT(NV.MaNV) AS TongSoNhanVien FROM NhanVien AS NV INNER JOIN BoPhan AS BP ON NV.MaBP = BP.MaBP GROUP BY BP.MaBP, BP.TenBP Kết quả: b.1.4 Lập danh sách về số lượng hàng, tổng giá trị hàng hóa mà mỗi nhân viên ở bộ phận bán hàng bán được trong năm 2008. Danh sách gồm có mã nhân viên, họ tên, ngày sinh, số lượng hàng bán được, tổng giá trị bán được. Sắp xếp theo tổng giá trị giảm dần. SELECT NV.MaNV, NV.HoTen, SUM(CTHD.SoLuong) AS TongSoLuongBan, SUM(CTHD.SoLuong*CTHD.DonGia) AS TongGiaTri FROM NhanVien AS NV INNER JOIN HoaDon AS HD ON NV.MaNV = HD.MaNV INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD WHERE (NV.MaBP = 'BP1' AND YEAR(HD.NgayLap)= '2008') GROUP BY NV.MaNV, NV.HoTen ORDER BY TongGiaTri DESC b.1.6 Tính độ tuổi trung bình của nhân viên trong công ty. SELECT TongTuoi / TongNhanVien AS TuoiTrungBinh FROM (SELECT SUM(DATEDIFF(Year, NgaySinh, { fn NOW() })) AS TongTuoi, COUNT(MaNV) AS TongNhanVien FROM NhanVien AS NV) Kết quả: b.1.7 Lập danh sách nhân viên bán hàng có doanh thu nhiều nhất. Danh sách gồm MaNV, HoTen, TongSoLuong, TongGiaTri SELECT MaNV, HoTen, TongSoLuong, TongGiaTri FROM (SELECT NV.MaNV, NV.HoTen, SUM(CTHD.SoLuong) AS TongSoLuong, SUM(CTHD.SoLuong * CTHD.DonGia) AS TongGiaTri FROM NhanVien AS NV INNER JOIN HoaDon AS HD ON NV.MaNV = HD.MaNV INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD WHERE (NV.MaBP = 'BP1') GROUP BY NV.MaNV, NV.HoTen) WHERE (TongGiaTri >= ALL (SELECT SUM(CTHD.SoLuong * CTHD.DonGia) AS TongGiaTri FROM NhanVien AS NV INNER JOIN HoaDon AS HD ON NV.MaNV = HD.MaNV INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD WHERE (NV.MaBP = 'BP1') GROUP BY NV.MaNV, NV.HoTen)) b.1.8 Thống kê về tỷ lệ nam, nữ của công ty. SELECT (SELECT COUNT(MaNV) FROM NhanVien WHERE (GioiTinh = 0)) * 100 / (SELECT COUNT(MaNV) FROM dbo.NhanVien AS NhanVien_3) AS TyLeNam, (SELECT COUNT(MaNV) AS Expr1 FROM dbo.NhanVien AS NhanVien_2 WHERE (GioiTinh = 1)) * 100 / (SELECT COUNT(MaNV) FROM dbo.NhanVien AS NhanVien_1) AS TyLeNu Kếtquả: b.1.9 Lập danh sách top 5 nhân viên có mang về nhiều doanh thu nhất cho công ty. SELECT TOP (5) MaNV, HoTen, TongSoLuongBan, TongGiaTriBan FROM (SELECT NV.MaNV, NV.HoTen, SUM(CTHD.SoLuong) AS TongSoLuongBan, SUM(CTHD.SoLuong * CTHD.DonGia) AS TongGiaTriBan FROM NhanVien AS NV INNER JOIN HoaDon AS HD ON NV.MaNV = HD.MaNV INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD WHERE (NV.MaBP = 'BP1') AND (YEAR(HD.NgayLap) = '2008') GROUP BY NV.MaNV, NV.HoTen) ORDER BY TongGiaTriBan DESC Kết quả: Yêu cầu quản lý khách hàng, nhà cung cấp b.2.3 Lập bảng thống kê khối lượng giao dịch của khách hàng năm 2008. Danh sách gồm mã khách hàng, tên khách hàng, ngày sinh, địa chỉ, điện thoại, tổng khối luợng giao dịch, tổng lợi nhuận của doanh nghiệp do khách hàng mang lại. Sắp xếp theo tổng khối luợng giao dịch giảm. SELECT KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi, KH.DienThoai, SUM(CTHD.SoLuong * CTHD.DonGia) AS TongKhoiLuongGiaoDich, SUM((CTHD.DonGia - HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM KhachHang AS KH INNER JOIN HoaDon AS HD ON KH.MaKH = HD.MaKH INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD INNER JOIN HangHoa AS HH ON CTHD.MaHang = HH.MaHang GROUP BY KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi, KH.DienThoai ORDER BY TongKhoiLuongGiaoDich DESC b.2.4 Lập danh sách khách hàng có khối luợng giao dịch lớn nhất trong năm 2008. Danh sách gồm mã khách hàng, tên khách hàng,ngày sinh địa chỉ, điện thoại, tổng khối luợng giao dịch, tổng lợi nhuận của doanh nghiệp do khách hàng mang lại. SELECT MaKH, Ten, NgaySinh, DiaChi, DienThoai, TongKhoiLuongGiaoDich, LoiNhuan FROM (SELECT KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi,KH.DienThoai, SUM(CTHD.SoLuong * CTHD.DonGia) AS TongKhoiLuongGiaoDich, SUM((CTHD.DonGia - HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM KhachHang AS KH INNER JOIN HoaDon AS HD ON KH.MaKH = HD.MaKH INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD INNER JOIN HangHoa AS HH ON CTHD.MaHang = HH.MaHang GROUP BY KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi, KH.DienThoai) WHERE (TongKhoiLuongGiaoDich >= ALL (SELECT TongKhoiLuongGiaoDich FROM (SELECT MaKH, SUM(TongGiaTri) AS TongKhoiLuongGiaoDich FROM HoaDon GROUP BY MaKH))) Kết quả: b.2.5 Lập danh sách thống kê khối lượng giao dịch của công ty với các tác trong trong năm 2008. Danh sách gồm có mã nhà cung cấp, tên nhà cung cấp, địa chỉ, điện thoại, emal, tổng số tiền mà doanh nghiệp đã bỏ ra để nhập hàng từ nhà cung cấp. SELECT NCC.MaNCC, NCC.Ten, NCC.DiaChi, NCC.DienThoai, NCC.Email, SUM(PNH.TongGiaTri) AS TongGiaTriGiaoDich FROM PhieuNhapHang AS PNH INNER JOIN NhaCungCap AS NCC ON PNH.MaNCC = NCC.MaNCC WHERE (YEAR(PNH.NgayLap) = 2008) GROUP BY NCC.MaNCC, NCC.Ten, NCC.DiaChi, NCC.DienThoai, NCC.Email Kết quả: b.2.6 Lập danh sách nhà cung cấp có khối lượng giao dịch nhiều nhất với công ty trong năm 2008. SELECT MaNCC, Ten, DiaChi, DienThoai, Email, TongGiaTriGiaoDich FROM (SELECT NCC.MaNCC, NCC.Ten, NCC.DiaChi, NCC.DienThoai, NCC.Email, SUM(PNH.TongGiaTri) AS TongGiaTriGiaoDich FROM PhieuNhapHang AS PNH INNER JOIN NhaCungCap AS NCC ON PNH.MaNCC = NCC.MaNCC GROUP BY NCC.MaNCC, NCC.Ten, NCC.DiaChi, NCC.DienThoai, NCC.Email) WHERE (TongGiaTriGiaoDich >= ALL (SELECT TongGiaTriGiaoDich FROM (SELECT NCC.MaNCC, SUM(PNH.TongGiaTri) AS TongGiaTriGiaoDich FROM PhieuNhapHang AS PNH INNER JOIN NhaCungCap AS NCC ON PNH.MaNCC = NCC.MaNCC WHERE (YEAR(PNH.NgayLap) = 2008) GROUP BY NCC.MaNCC))) Kết quả: b.2.8 Lập bảng thống kê số lần đến mua hàng của các khách hàng trong năm 2008. Danh sách gồm có mã khách hàng, họ tên, địa chỉ, điện thoại, số lần mua hàng theo thứ thự số lần mua giảm. SELECT MaKH, Ten, NgaySinh, DiaChi, DienThoai, SoLanDenMua FROM (SELECT KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi, KH.DienThoai, COUNT(HD.MaHD) AS SoLanDenMua FROM KhachHang AS KH KH LEFT OUTER JOIN HoaDon AS HD ON KH.MaKH = HD.MaKH WHERE (YEAR(HD.NgayLap) = 2008) GROUP BY KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi, KH.DienThoai) ORDER BY SoLanDenMua DESC b.2.9 Lập danh sách top 5 khách hàng có khối lượng giao dich với công ty lớn nhất. Danh sách gồm mã khách hàng, tên khách hàng, ngày sinh, địa chỉ,điện thoại, tổng khối luợng giao dịch. SELECT TOP (5) MaKH, Ten, NgaySinh, DiaChi, DienThoai,TongGiaTriGiaoDich FROM (SELECT KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi, KH.DienThoai, SUM(HD.TongGiaTri) AS TongGiaTriGiaoDich FROM KhachHang AS KH INNER JOIN HoaDon AS HD ON KH.MaKH = HD.MaKH GROUP BY KH.MaKH, KH.Ten, KH.NgaySinh, KH.DiaChi, KH.DienThoai) ORDER BY TongGiaTriGiaoDich DESC Yều cầu từ các nghiệp vụ bán hàng b.3.4 Lâp danh sách khối lượng giao dịch của khách hàng có mã là x. Danh sách gồm mã khách hàng, họ tên, tổng khối lượng giao dịch SELECT KH.MaKH, KH.Ten, SUM(HD.TongGiaTri) AS TongKhoiLuongGiaoDich FROM KhachHang AS KH INNER JOIN HoaDon AS HD ON KH.MaKH = HD.MaKH WHERE (HD.MaKH = 'KH10') GROUP BY KH.MaKH, KH.Ten b.3.5 Lập danh sách những hóa đơn đã thanh toán . Danh sách gồm có mã hoá đơn, mã nhân viên, mã khách hàng ,ngày lập, tổng giá trị SELECT MaHD, MaNV, MaKH, NgayLap, TongGiaTri FROM (SELECT MaHD, MaNV, MaKH, NgayLap, ISNULL(SoTien, 0) AS SoTienNo, TongGiaTri, SoTien, NgayHen FROM (SELECT HD.MaHD, HD.MaNV, HD.MaKH, HD.NgayLap, HD.TongGiaTri, CNB.MaCNB, CNB.SoTien, CNB.NgayHen FROM HoaDon AS HD LEFT OUTER JOIN CongNoBan AS CNB ON HD.MaHD = CNB.MaHD) ) WHERE (SoTienNo = '0') b.3.7 Lập danh sách mặt hàng có số lượng bán nhiều nhất trong năm 2008. Danh sách gồm mã hàng, tên hàng, số lượng bán. SELECT MaHang, TenHang, SoLuongBan FROM (SELECT HH.MaHang, HH.TenHang, SUM(CTHD.SoLuong) AS SoLuongBan FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang GROUP BY HH.MaHang, HH.TenHang WHERE (SoLuongBan >= ALL (SELECT SoLuongBan FROM (SELECT HH.MaHang, HH.TenHang, SUM(CTHD.SoLuong) AS SoLuongBan FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang GROUP BY HH.MaHang, HH.TenHang))) b.3.8 Lập bảng thống kê các mặt hàng đuợc bán ra năm 2008. Danh sách gồm có mã hàng, tên hàng, tổng số lượng bán, doanh thu, lợi nhuận từ các mặt hàng đó. SELECT HH.MaHang, HH.TenHang, SUM(CTHD.SoLuong) AS TongSoLuong, SUM(CTHD.SoLuong * CTHD.DonGia) AS DoanhThu, SUM((CTHD.DonGia - HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang INNER JOIN HoaDon AS HD ON CTHD.MaHD = HD.MaHD WHERE (YEAR(HD.NgayLap) = '2008') GROUP BY HH.MaHang, HH.TenHang b.3.9 Lập danh sách mặt hàng mang lại nhiều lợi nhuận nhất cho công ty. Danh sách gồm mã hàng, tên hàng, lợi nhuận SELECT MaHang, TenHang, LoiNhuan FROM (SELECT HH.MaHang, HH.TenHang, SUM((CTHD.DonGia – HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang GROUP BY HH.MaHang, HH.TenHang) WHERE (LoiNhuan >= ALL (SELECT LoiNhuan FROM (SELECT HH.MaHang, HH.TenHang, SUM((CTHD.DonGia - HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang GROUP BY HH.MaHang, HH.TenHang))) b.3.10 Tính doanh thu và lợi nhuân của công ty trong năm 2008. SELECT SUM(CTHD.DonGia * CTHD.SoLuong) AS DoanhThu, SUM((CTHD.DonGia - HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang INNER JOIN HoaDon AS HD ON CTHD.MaHD = HD.MaHD WHERE (YEAR(HD.NgayLap) = '2008') b.3.11 Lập bảng thống kê doanh, thu lợi nhuận của từng tháng của năm 2008. Danh sách gồm có tháng, doanh thu, lợi nhuận. SELECT Thang, SUM(DonGiaBan * SoLuong) AS DoanhThu, SUM((DonGiaBan - DonGia) * SoLuong) AS LoiNhuan FROM (SELECT HH.DonGia, CTHD.DonGia AS DonGiaBan, CTHD.SoLuong, MONTH(HD.NgayLap) AS Thang FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang INNER JOIN HoaDon AS HD ON CTHD.MaHD = HD.MaHD WHERE (YEAR(HD.NgayLap) = '2008')) AS derivedtbl_1 GROUP BY Thang b.3.12 Lập danh sách các mặt hàng mà khách hàng có mãkh là x đã mua Danh sách gồm có mã hàng, tên hàng, số lượng, số tiền bỏ ra để mua mặt hàng đó SELECT HH.MaHang, HH.TenHang, SUM(CTHD.SoLuong) AS TongSoLuong, SUM(CTHD.SoLuong * CTHD.DonGia) AS TongGiaTri FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang INNER JOIN HoaDon AS HD ON CTHD.MaHD = HD.MaHD WHERE (HD.MaKH = 'KH10') GROUP BY HH.MaHang, HH.TenHang b.3.13 Tìm ra tháng trong năm 2008 mà công ty có lợi nhuận lớn nhất SELECT Thang, LoiNhuan FROM (SELECT Thang, SUM((DonGiaBan - DonGia) * SoLuong) AS LoiNhuan FROM (SELECT HH.DonGia, CTHD.DonGia AS DonGiaBan, CTHD.SoLuong, MONTH(HD.NgayLap) AS Thang FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang INNER JOIN HoaDon AS HD ON CTHD.MaHD = HD.MaHD WHERE (YEAR(HD.NgayLap) = '2008')) GROUP BY Thang) WHERE (LoiNhuan >= ALL (SELECT LoiNhuan FROM (SELECT Thang, SUM((DonGiaBan - DonGia) * SoLuong) AS LoiNhuan FROM (SELECT HH.DonGia, CTHD.DonGia AS DonGiaBan, CTHD.SoLuong, MONTH(HD.NgayLap) AS Thang FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang INNER JOIN HoaDon AS HD ON CTHD.MaHD = HD.MaHD WHERE (YEAR(HD.NgayLap) = '2008')) GROUP BY Thang))) b.3.14 Tính tháng có nhiều khách hàng đến mua hàng nhất. SELECT Thang, SoKhachHang FROM (SELECT Thang, COUNT(MaKH) AS SoKhachHang FROM (SELECT DISTINCT MONTH(NgayLap) AS Thang, MaKH FROM HoaDon) GROUP BY Thang) WHERE (SoKhachHang >= ALL (SELECT SoKhachHang FROM (SELECT Thang, COUNT(MaKH) AS SoKhachHang FROM (SELECT DISTINCT MONTH(NgayLap) AS Thang, MaKH FROM HoaDon) GROUP BY Thang))) b.3.15 Lập danh sách top 5 mặt hàng thu về nhiều lợi nhuận cho công ty nhất. danh sách gồm mã hàng, tên hàng, lợi nhuận. SELECT TOP (5) MaHang, TenHang, LoiNhuan FROM (SELECT HH.MaHang, HH.TenHang, SUM((CTHD.DonGia – HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM HangHoa AS HH INNER JOIN ChiTietHoaDon AS CTHD ON HH.MaHang = CTHD.MaHang GROUP BY HH.MaHang, HH.TenHang) ORDER BY LoiNhuan DESC b.3.16 Lập bảng thống kê doanh thu, lợi nhuận của công ty theo khu vực địa lý(khu vưc địa lý theo địa chỉ của khách hàng) SELECT KH.DiaChi AS KhuVuc, SUM(CTHD.SoLuong * CTHD.DonGia) AS DoanhThu, SUM((CTHD.DonGia - HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM KhachHang AS KH INNER JOIN HoaDon AS HD ON KH.MaKH = HD.MaKH INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD INNER JOIN HangHoa AS HH ON CTHD.MaHang = HH.MaHang GROUP BY KH.DiaChi b.3.17 Lập danh sách những hóa đơn đã thanh toán. Danh sách gồm mã hóa đơn, mã nhân viên, mã khách hàng,ngày lập,tổng giá trị, lợi nhuận. SELECT MaHD, MaNV, MaKH, NgayLap, TongGiaTri, LoiNhuan FROM (SELECT HTH.MaHD, HTH.MaNV, HTH.MaKH, HTH.NgayLap, HTH.TongGiaTri, HTH.LoiNhuan, ISNULL(CNB.SoTien, 0) AS SoTienNo FROM (SELECT HD.MaHD, HD.MaNV, HD.MaKH, HD.NgayLap, SUM(CTHD.DonGia * CTHD.SoLuong) AS TongGiaTri, SUM((CTHD.DonGia - HH.DonGia) * CTHD.SoLuong) AS LoiNhuan FROM HoaDon AS HD INNER JOIN ChiTietHoaDon AS CTHD ON HD.MaHD = CTHD.MaHD INNER JOINHangHoa AS HH ON CTHD.MaHang = HH.MaHang GROUP BY HD.MaHD, HD.MaNV, HD.MaKH, HD.NgayLap) AS HTH LEFT OUTER JOIN CongNoBan AS CNB ON HTH.MaHD = CNB.MaHD) WHERE (SoTienNo = '0') Yêu cầu từ các nghiệp vụ nhập hàng b.4.6 Lập bảng thống kê các mặt hàng được nhập bởi công ty trong năm 2008. Danh sách gốm có mã hàng, tên hàng, số lượng, số tiền bỏ ra để nhập mặt hàng đó. SELECT HH.MaHang, HH.TenHang, SUM(CTPNH.SoLuong) AS SoLuongNhap, SUM(CTPNH.SoLuong * CTPNH.DonGia) AS TongGiaTri FROM HangHoa AS HH INNER JOIN ChiTietPhieuNhapHang AS CTPNH ON HH.MaHang = CTPNH.MaHang INNER JOIN PhieuNhapHang AS PNH ON CTPNH.MaPN = PNH.MaPN WHERE (YEAR(PNH.NgayLap) = '2008') GROUP BY HH.MaHang, HH.TenHang b.4.7 Lập danh sách mặt hàng được công ty nhập nhiều nhất trong năm 2008. Danh sách gồm có