BAI 5: QUAN LY DON DAT HANG.

--- QUẢN LÝ DƠN ĐẶT HÀNG

IF DB_ID('QLDDH') IS NOT NULL

DROP DATABASE QLDDH

CREATE DATABASE QLDDH

USE QLDDH

--- TAO BANG NHACC

IF OBJECT_ID('NHACC') IS NOT NULL

DROP TABLE NHACC

CREATE TABLE NHACC

MANCC VARCHAR(10) NOT NULL PRIMARY KEY,

TENNCC NVARCHAR(30) NOT NULL,

DCNCC NVARCHAR(30),

MALH VARCHAR(10)

--- TAO BANG LOAI HANG

IF OBJECT_ID('LOAIHANG') IS NOT NULL

DROP TABLE LOAIHANG

CREATE TABLE LOAIHANG

MALH VARCHAR(10) NOT NULL PRIMARY KEY,

TENLH NVARCHAR(30) NOT NULL,

MOTALH NVARCHAR(30)

--- TAO BANG 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 NOT NULL,

MALH VARCHAR(10) NOT NULL,

MANCC VARCHAR(10) NOT NULL

--- TAO BANG KHACH

IF OBJECT_ID('KHACH') IS NOT NULL

DROP TABLE KHACH

CREATE TABLE KHACH

MAKH VARCHAR(10) NOT NULL PRIMARY KEY,

TENKH NVARCHAR(30) NOT NULL,

DCKH NVARCHAR(30)

--- TAO BANG DDH

IF OBJECT_ID('DDH') IS NOT NULL

DROP TABLE DDH

CREATE TABLE DDH

MADDH VARCHAR(10) NOT NULL PRIMARY KEY,

NGAYDH SMALLDATETIME NOT NULL,

NGAYHL SMALLDATETIME NOT NULL,

MAKH VARCHAR(10) NOT NULL

--- TAO BANG CTDDH

IF OBJECT_ID('CTDDH') IS NOT NULL

DROP TABLE CTDDH

CREATE TABLE CTDDH

MADDH VARCHAR(10) NOT NULL,

MAHG VARCHAR(10) NOT NULL,

SLDAT INT NOT NULL,

PRIMARY KEY(MADDH,MAHG)

--- TAO BANG DOT GIAO

IF OBJECT_ID('DOTGIAO') IS NOT NULL

DROP TABLE DOTGIAO

CREATE TABLE DOTGIAO

MADGH VARCHAR(10) NOT NULL PRIMARY KEY,

NGAYDGH SMALLDATETIME NOT NULL,

MADDH VARCHAR(10) NOT NULL

--- TAO BANG CTDGH

IF OBJECT_ID('CTDGH') IS NOT NULL

DROP TABLE CTDGH

CREATE TABLE CTDGH

MADGH VARCHAR(10) NOT NULL,

MAHG VARCHAR(10) NOT NULL,

SKGH INT NOT NULL,

PRIMARY KEY(MADGH,MAHG)

---- NHAP LIEU

---- NHAP BANG NCC

INSERT INTO NHACC VALUES('C01', N'NHÀ CUNG CẤP 1', N'BÌNH DƯƠNG', 'L01')

INSERT INTO NHACC VALUES('C02', N'NHÀ CUNG CẤP 2', N'BÌNH DƯƠNG', 'L02')

SELECT * FROM NHACC

---- NHAP BANG LOAI HANG

INSERT INTO LOAIHANG VALUES('L01', N'PHÂN BÓN', N'PHÂN HÓA HỌC')

INSERT INTO LOAIHANG VALUES('L02', N'THUỐC TRỪ SÂU', N'DÙNG CHO CÀ PHÊ')

SELECT * FROM LOAIHANG

--- NHAP BANG HANG

INSERT INTO HANG VALUES('H01', N'NPK',150,'L01','C01')

INSERT INTO HANG VALUES('H02', N'URÊ',130,'L01','C01')

INSERT INTO HANG VALUES('H03', N'VI SINH',90,'L01','C01')

INSERT INTO HANG VALUES('H04', N'VI TỐC 636',150,'L02','C02')

INSERT INTO HANG VALUES('H05', N'ZEN 185',150,'L02','C02')

INSERT INTO HANG VALUES('H06', N'RẦY NÂU',150,'L02','C02')

SELECT * FROM HANG

--- NHAP BANG KHACH

INSERT INTO KHACH VALUES('K01', N'NGUYỄN VĂN A', N'QUẬN 3')

INSERT INTO KHACH VALUES('K02', N'NGUYỄN VĂN B', N'QUẬN 8')

INSERT INTO KHACH VALUES('K03', N'NGUYỄN VĂN C', N'QUẬN 12')

INSERT INTO KHACH VALUES('K04', N'NGUYỄN VĂN D', N'BÌNH DƯƠNG')

INSERT INTO KHACH VALUES('K05', N'NGUYỄN VĂN E', N'QUẬN 2')

SELECT * FROM KHACH

--- NHAP BANG DDH

INSERT INTO DDH VALUES('D01','5/12/2009','5/15/2009', 'K01')

INSERT INTO DDH VALUES('D02','5/13/2009','5/15/2009', 'K01')

INSERT INTO DDH VALUES('D03','5/12/2009','5/15/2009', 'K02')

INSERT INTO DDH VALUES('D04','5/12/2009','5/14/2009', 'K02')

INSERT INTO DDH VALUES('D05','5/11/2009','5/15/2009', 'K03')

INSERT INTO DDH VALUES('D06','5/12/2009','5/19/2009', 'K04')

INSERT INTO DDH VALUES('D07','5/9/2009','5/21/2009', 'K05')

SELECT * FROM DDH

--- NHAP BANG CTDDH

INSERT INTO CTDDH VALUES('D01','H01',60)

INSERT INTO CTDDH VALUES('D02','H03',10)

INSERT INTO CTDDH VALUES('D02','H01',10)

INSERT INTO CTDDH VALUES('D03','H04',10)

INSERT INTO CTDDH VALUES('D04','H05',60)

INSERT INTO CTDDH VALUES('D05','H03',10)

INSERT INTO CTDDH VALUES('D06','H04',10)

INSERT INTO CTDDH VALUES('D07','H05',60)

SELECT * FROM CTDDH

--- NHAP BANG DOT GIAO

INSERT INTO DOTGIAO VALUES('G01','5/15/2009','D01')

INSERT INTO DOTGIAO VALUES('G02','5/14/2009','D04')

INSERT INTO DOTGIAO VALUES('G03','5/15/2009','D02')

INSERT INTO DOTGIAO VALUES('G04','5/15/2009','D03')

INSERT INTO DOTGIAO VALUES('G05','5/15/2009','D05')

SELECT * FROM DOTGIAO

--- NHAP BANG CTDGH

INSERT INTO CTDGH VALUES('G01','H01',60)

INSERT INTO CTDGH VALUES('G02','H05',10)

INSERT INTO CTDGH VALUES('G03','H01',10)

INSERT INTO CTDGH VALUES('G03','H03',10)

INSERT INTO CTDGH VALUES('G04','H04',10)

SELECT * FROM CTDGH

--- TRA LOI SQL

--- CÂU 1

SELECT MAHG AS MÃ_HÀNG, TENHG AS TÊN_HANG

FROM NHACC A, HANG B

WHERE A.MALH = B.MALH AND A.MANCC = 'C01'

--- CÂU 2

SELECT A.MAHG AS MÃ_HÀNG_CHƯA_CUNG_CẤP, TENHG AS TÊN_HÀNG

FROM HANG A

WHERE MANCC = 'C01' AND A.MAHG NOT IN (

SELECT A.MAHG

FROM CTDDH A, HANG B

WHERE B.MANCC = 'C01' AND A.MAHG = B.MAHG )

--- CÂU 3

SELECT A.MAKH AS MÃ_KHÁCH_HÀNG, TENKH AS TÊN_KHÁCH_HÀNG

FROM KHACH A, DDH B

WHERE A.MAKH = B.MAKH AND NGAYDH BETWEEN '5/11/2009' AND '5/13/2009'

--- CÂU 4

SELECT A.MAHG AS mÃ_HÀNG, TENHG AS TÊN_HÀNG

FROM HANG A, CTDGH B

WHERE B.MAHG = A.MAHG AND MADGH = 'G01'

--- CÂU 5

SELECT MAHG AS MÃ_HÀNG, TENHG AS TÊN_HÀNG

FROM HANG

WHERE MAHG NOT IN (

SELECT MAHG

FROM CTDDH

--- CÂU 6

SELECT B.MAHG AS MÃ_HÀNG, TENHG AS TÊN_HÀNG

FROM DDH A, CTDDH B, HANG C

WHERE A.NGAYDH BETWEEN '5/11/2009' AND '5/13/2009' AND A.MADDH = B.MADDH AND B.MAHG = C.MAHG

AND B.MAHG NOT IN (

SELECT MAHG

FROM DDH A, DOTGIAO B, CTDGH C

WHERE A.NGAYDH BETWEEN '5/11/2009' AND '5/13/2009'AND A.MADDH = B.MADDH

AND C.MADGH = B.MADGH

--- GHI CHU LOI

--- CÂU 8

SELECT MAKH AS MÃ_KHÁCH_HÀNG, COUNT(*) AS SỐ_LƯỢNG_ĐƠN

FROM DDH

WHERE NGAYDH BETWEEN '5/11/2009' AND '5/16/2009'

GROUP BY MAKH

--- CÂU 9

SELECT MAKH AS MÃ_KHÁCH_HÀNG_ĐẶT_NHÌU_ĐƠN_NHẤT, COUNT(*) AS SỐ_LƯỢNG_ĐƠN

FROM DDH

WHERE NGAYDH BETWEEN '5/11/2009' AND '5/16/2009'

GROUP BY MAKH

HAVING COUNT(*)>= ALL (

SELECT COUNT(*)

FROM DDH

WHERE NGAYDH BETWEEN '5/11/2009' AND '5/16/2009'

GROUP BY MAKH)

--- CÂU 10

SELECT B.MADDH AS MÃ_ĐƠN, SUM(SLDAT*DONGIA) AS TRỊ_GIÁ

FROM DDH A, CTDDH B, HANG C

WHERE A.NGAYDH BETWEEN '5/11/2009' AND '5/16/2009' AND B.MADDH = A.MADDH

AND B.MAHG = C.MAHG

GROUP BY A.MADDH, B.MADDH

---CÂU 11

SELECT B.MADDH AS MÃ_ĐƠN_TRỊ_GIÁ_LỚN_NHẤT, SUM(SLDAT*DONGIA) AS TRỊ_GIÁ

FROM DDH A, CTDDH B, HANG C

WHERE A.NGAYDH BETWEEN '5/11/2009' AND '5/16/2009' AND B.MADDH = A.MADDH

AND B.MAHG = C.MAHG

GROUP BY A.MADDH, B.MADDH

HAVING SUM(SLDAT*DONGIA)>= ALL (

SELECT SUM(SLDAT*DONGIA)

FROM DDH A, CTDDH B, HANG C

WHERE A.NGAYDH BETWEEN '5/11/2009' AND '5/16/2009' AND B.MADDH = A.MADDH

AND B.MAHG = C.MAHG

GROUP BY A.MADDH, B.MADDH)