Mục Lục
Chương I : khảo sát hiện trạng 3.
1. Giới thiệu công ty Phong Vũ . . .3.
2. Các hoạt động nghiệp vụ của công ty .3.
3. Các yêu cầu đặt ra .5.
Chương II: Xây Dựng Cơ Sở Dữ Liệu .10.
1. Các đối tượng cần quản lý .10.
2. Mối liên hệ giữa các đối tượng .10.
3 Mô hình thực thể và mối kết hợp .12.
4. Mô hình Codd .14.
5. Mô tả chi tiết các đối tượng và các mối kết hợp 14.
Chương III: Ràng Buộc Toàn Vẹn 19.
1. Ràng buộc trên một quan hệ .19.
2. Ràng buộc trên nhiều quan hệ .20.
Chương IV: Giải Pháp Bằng SQL 25.
4.1 Yêu cầu về quản lý nhân sự 25.
4.2 Yêu cầu quản lý khách hàng, nhà cung cấp .27.
4.3 Yều cầu từ các nghiệp vụ bán hàng 30.
4.4 Yêu cầu từ các nghiệp vụ nhập hàng 35.
4.5 Yêu cầu từ nghiệp vụ quản lý kho hàng 39.
4.6 Yêu cầu từ nghiệp vụ quản lý tài chính . 40.
4.7 Yêu cầu từ nghiệp vụ quản lý công nợ .42.
4.8 Yêu cầu đến từ nghiệp vụ bảo hành .43.
4.9 Yêu cầu ra quyết định .45.
Chương V: Giao Diện Hóa Cho Chương IV 49.
Chương VI: Chuẩn Hóa Cơ Sở Dữ Liệu .61.
65 trang |
Chia sẻ: lethao | Lượt xem: 1775 | Lượt tải: 3
Bạn đang xem trước 20 trang tài liệu Đồ án Quản lý hoạt động kinh doanh của công ty Phong Vũ, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
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ó
Các file đính kèm theo tài liệu này:
- 2727891 n QUA777N LY769 HOA803T 272803NG KINH DOANH CU777A C.doc
- 2727891 n QUA777N LY769 HOA803T 272803NG KINH DOANH CU777A C.pdf