CNTTK9B

FROM CHITIETHOADON

WHERE SOHD IN(SELECT SOHD FROM HOADONBAN

WHERE MONTH(NGAYHD)='9' AND YEAR(NGAYHD)='2009')

GROUP BY MAH

--DUA RA TONG SO LUONG HANG DA BAN CHO MUC DICH MUA HANG 'TIEP KHACH'--

SELECT SUM(SL) AS SL

FROM CHITIETHOADON

WHERE SOHD IN(SELECT SOHD FROM HOADONBAN

WHERE DIENGIAI='TIEP KHACH')

--HIEN THI THONG TIN KHACH HANG DA TUNG MUA HANG TAI CUA HANG--

SELECT * FROM DMKHACH

WHERE MAK IN(SELECT MAK FROM HOADONBAN)

--2.TAO VIEW DE TONG HOP DU LIEU VE TUNG MAT HANG DA DUOC BAN (CAU TRUC VIEW GOM CAC THUOC TINH:

--MAH,TENH,DVT,SLBAN--

ALTER VIEW VD2

AS

SELECT DMH.MAH,TENH,DVT,SUM(SL) AS TSL

FROM DMH,CHITIETHOADON

WHERE DMH.MAH=CHITIETHOADON.MAH

GROUP BY DMH.MAH,TENH,DVT

SELECT * FROM VD2

--3.TAO VIEW DE TONG HOP DU LIEU VE CAC MAT HANG DA DUOC BAN VOI SO LUONG LON NHAT--

CREATE VIEW TG

AS

SELECT MAH,SUM(SL) AS TSL

FROM CHITIETHOADON

GROUP BY MAH

CREATE VIEW VD3

AS

SELECT DMH.*,TSL

FROM DMH,TG

WHERE DMH.MAH =TG.MAH AND TSL IN (SELECT MAX(TSL) FROM TG)

--4.TAO VIEW DE TONG HOP DU LIEU VE CAC KHACH HANG DA MUA HANG TRONG NGAY 20/10/2005--

CREATE VIEW VD4

AS

SELECT * FROM DMKHACH

WHERE MAK IN(SELECT MAK FROM HOADONBAN

WHERE NGAYHD='10/20/2005')

--5.TAO THU TUC CO THAM SO VAO LA @SOHD DE DUA RA DANH MUC CAC MAT HANG CO TRONG HOA DON TREN--

CREATE PROC VD5

@SOHD CHAR(10)

AS

SELECT * FROM DMH

WHERE MAH IN(SELECT MAH FROM CHITIETHOADON

WHERE SOHD=@SOHD)

EXEC VD5 'HD1'

--6.TAO THU TUC CO THAM SO VAO LA @NGAY DE DUA RA DANH MUA CAC MAT HANG DA DUOC BAN VAO NGAY TREN

--DANH SACH DUA RA GOM CAC THUOC TINH SAU:MAH,TENH,DVT,SL--

ALTER PROC VD6

@NGAY SMALLDATETIME

AS

SELECT DMH.MAH,TENH,DVT,SL