BAI 2: HOA DON BAN LE.

--- QUẢN LÝ HÓA DƠN BÁN LẺ

IF DB_ID('HDBL') IS NOT NULL

DROP DATABASE HDBL

CREATE DATABASE HDBL

USE HDBL

-- TAO TABLE HANG

IF OBJECT_ID('HANG') IS NOT NULL

DROP TABLE HANG

CREATE TABLE HANG

MAHG VARCHAR(10) NOT NULL PRIMARY KEY,

TENHG NVARCHAR(30) NOT NULL,

DONGIA INT

-- TAO BANG HOA DON

IF OBJECT_ID('HOADON') IS NOT NULL

DROP TABLE HOADON

CREATE TABLE HOADON

MAHD VARCHAR(10) NOT NULL PRIMARY KEY,

NGAYBAN SMALLDATETIME NULL,

NOIDUNG NVARCHAR(30) NOT NULL

--- TAO BANG CHI TIET HOA DON

IF OBJECT_ID('CTHD') IS NOT NULL

DROP TABLE CTHD

CREATE TABLE CTHD

MAHD VARCHAR(10) NOT NULL ,

MAHG VARCHAR(10) NOT NULL,

SL INT NOT NULL,

PRIMARY KEY(MAHD, MAHG)

-------- NHAP LIEU

--- NHAP LIEU BANG HANG

INSERT INTO HANG VALUES('H01', N'HÀNG A', 150)

INSERT INTO HANG VALUES('H02', N'HÀNG B', 70)

INSERT INTO HANG VALUES('H03', N'HÀNG C', 200)

SELECT * FROM HANG

--- NHAP LIEU BANG HOA DON

INSERT INTO HOADON VALUES('HD01','09/12/2009', N'Giao Hàng Liền')

INSERT INTO HOADON VALUES('HD02','09/15/2009', N'Khách vãng lai')

INSERT INTO HOADON VALUES('HD03','09/15/2009', N'xxxxxxxxxxxxx')

SELECT * FROM HOADON

--- NHAP LIEU BANG CHI TIET HOA DON

INSERT INTO CTHD VALUES('HD01','H01', 10)

INSERT INTO CTHD VALUES('HD01','H03', 12)

INSERT INTO CTHD VALUES('HD02','H01', 34)

INSERT INTO CTHD VALUES('HD02','H02', 15)

INSERT INTO CTHD VALUES('HD02','H03', 3)

INSERT INTO CTHD VALUES('HD03','H02', 2)

INSERT INTO CTHD VALUES('HD03','H03', 16)

SELECT * FROM CTHD

--- CÂU 3

SELECT MAHD AS 'MÃ HÓA ĐƠN', SUM(SL) AS 'TỔNG SỐ LƯỢNG'

FROM CTHD

GROUP BY MAHD

--- CÂU 4

SELECT SUM(SL*DONGIA) AS 'TỔNG TRỊ GIÁ'

FROM HANG A, CTHD B

WHERE B.MAHD IN (

SELECT MAHD

FROM HOADON

WHERE NGAYBAN BETWEEN '09/14/2009' AND '09/15/2009')

--- CÂU 5

SELECT A.MAHD AS 'MÃ HÓA ĐƠN' , SUM(SL*DONGIA) AS 'TRỊ GIÁ BÁN'

FROM HOADON A, CTHD B, HANG C

WHERE A.MAHD = B.MAHD AND C.MAHG = B.MAHG AND NGAYBAN = '09/15/2009'

GROUP BY A.MAHD

--- CÂU 6

SELECT A.MAHD AS 'MÃ HÓA ĐƠN' , SUM(SL*DONGIA) AS 'TRỊ GIÁ BÁN'

FROM HOADON A, CTHD B, HANG C

WHERE A.MAHD = B.MAHD AND C.MAHG = B.MAHG AND NGAYBAN BETWEEN '09/12/2009' AND '09/15/2009'

GROUP BY A.MAHD

--- CÂU 7

SELECT A.MAHD AS 'MÃ HÓA ĐƠN CÓ TRỊ GIÁ LỚN NHẤT' , SUM(SL*DONGIA) AS 'TRỊ GIÁ BÁN'

FROM HOADON A, CTHD B, HANG C

WHERE A.MAHD = B.MAHD AND C.MAHG = B.MAHG AND NGAYBAN BETWEEN '09/12/2009' AND '09/15/2009'

GROUP BY A.MAHD

HAVING SUM(SL*DONGIA) >= ALL (SELECT SUM(SL*DONGIA)

FROM HOADON A, CTHD B, HANG C

WHERE A.MAHD = B.MAHD AND C.MAHG = B.MAHG AND NGAYBAN BETWEEN '09/12/2009' AND '09/15/2009'

GROUP BY A.MAHD)