BAI1 QUANLYBANHANG.sql
CREATE DATABASE QUANLYBANHANG
USE QUANLYBANHANG
CREATE TABLE DMKHACH(
MAK CHAR(10) PRIMARY KEY,
TENK CHAR(40) NOT NULL,
DC CHAR(50),
DT CHAR(15)
)
CREATE TABLE DMH(
MAH CHAR(10) PRIMARY KEY,
TENH CHAR(30) NOT NULL,
DVT CHAR(15)
)
CREATE TABLE HOADONBAN(
SOHD CHAR(10),
MAK CHAR(10),
NGAYHD SMALLDATETIME NOT NULL,
DIENGIAI CHAR(30)
CONSTRAINT KC_HOADONBAN PRIMARY KEY (SOHD),
CONSTRAINT KN_HOADONBAN FOREIGN KEY (MAK) REFERENCES DMKHACH(MAK)
)
CREATE TABLE CHITIETHOADON(
SOHD CHAR(10),
MAH CHAR(10),
SL INT,
DONGIA INT,
CONSTRAINT KC_CHITIETHOADON PRIMARY KEY (SOHD,MAH),
CONSTRAINT KN_CHITIETHOADON FOREIGN KEY (SOHD) REFERENCES HOADONBAN(SOHD),
CONSTRAINT KN1_CHITIETHOADON FOREIGN KEY (MAH) REFERENCES DMH(MAH)
)
--1.DUA RA DANH SACH MAT HANG CHUA DUOC BAN TRONG THANG 9 NAM 2009--
SELECT * FROM DMH
WHERE MAH NOT IN(SELECT MAH FROM CHITIETHOADON
WHERE SOHD IN(SELECT SOHD FROM HOADONBAN
WHERE MONTH(NGAYHD)='9' AND YEAR(NGAYHD)='2009'))
--DUA RA DANH SACH KHACH HANG CO DIA CHI O THAI NGUYEN VA TUNG MUA HANG TRONG THANG 9 NAM 2009--
SELECT * FROM DMKHACH
WHERE DC='THAI NGUYEN' AND MAK IN(SELECT MAK FROM HOADONBAN
WHERE MONTH(NGAYHD)='9' AND YEAR(NGAYHD)='2009')
--DUA RA SO LUONG DA BAN TUONG UNG CUA TUNG MAT HANG TRONG THANG 9 NAM 2009--
SELECT MAH,SUM(SL) AS SL
