BAI 3: QUAN LY KHO HANG.

--- QUẢN LÝ KHO HÀNG

IF DB_ID('KHOHANG') IS NOT NULL

DROP DATABASE KHOHANG

CREATE DATABASE KHOHANG

USE KHOHANG

-- TAO BANG LOAIHANG

IF OBJECT_ID('LOAIHANG') IS NOT NULL

DROP TABLE LOAIHANG

CREATE TABLE LOAIHANG

MALH VARCHAR (3) NOT NULL ,

TENLH NVARCHAR (30) NOT NULL,

PRIMARY KEY (MALH,TENLH)

-- TAO TABLE KHOHANG

IF OBJECT_ID('KHOHANG') IS NOT NULL

DROP TABLE KHOHANG

CREATE TABLE KHOHANG

MAKH VARCHAR (3)NOT NULL,

TENKH VARCHAR (30) NOT NULL,

DCKH VARCHAR (15),

MALH VARCHAR (3) NOT NULL,

PRIMARY KEY (MAKH,MALH)

-- TAO BANG HANG

IF OBJECT_ID('HANG') IS NOT NULL

DROP TABLE HANG

CREATE TABLE HANG

MAHG VARCHAR (3) NOT NULL,

TENHG NVARCHAR (30) NOT NULL,

MALH VARCHAR (3) NOT NULL,

PRIMARY KEY (MAHG, MALH)

-- TAO BANG KHO CHUA

IF OBJECT_ID('KHOCHUA') IS NOT NULL

DROP TABLE KHOCHUA

CREATE TABLE KHOCHUA

MAKH VARCHAR (3) NOT NULL,

MAHG VARCHAR (3) NOT NULL,

SL INT NOT NULL,

PRIMARY KEY (MAHG,MAKH)

INSERT INTO LOAIHANG VALUES('L01', N'Nước Giải Khát')

INSERT INTO LOAIHANG VALUES('L02', N'Trà Khô')

select * from LOAIHANG

INSERT INTO KHOHANG VALUES('K01','KHO A','Q1','L01')

INSERT INTO KHOHANG VALUES('K02','KHO B','Q1','L01')

INSERT INTO KHOHANG VALUES('K03','KHO C','Q2','L02')

SELECT * FROM KHOHANG

INSERT INTO HANG VALUES('H01', N'TRÀ XANH','L01')

INSERT INTO HANG VALUES('H02', N'PEPSI','L01')

INSERT INTO HANG VALUES('H03', N'7 UP','L01')

INSERT INTO HANG VALUES('H04', N'TRÀ TÂN CƯƠNG','L02')

INSERT INTO HANG VALUES('H05', N'TRÀ BẢO LỘC','L02')

SELECT * FROM HANG

INSERT INTO KHOCHUA VALUES('K01','H01',50)

INSERT INTO KHOCHUA VALUES('K01','H02',30)

INSERT INTO KHOCHUA VALUES('K02','H03',100)

INSERT INTO KHOCHUA VALUES('K03','H04',40)

INSERT INTO KHOCHUA VALUES('K03','H05',50)

SELECT * FROM KHOCHUA

-- CAU 1 --

SELECT * FROM HANG WHERE MALH = 'L01'

-- CAU 2 --

SELECT MAHG AS Mã_Hàng,TENHG AS Tên_Hàng

from HANG AS A, KHOHANG AS B

WHERE A.MALH = B.MALH AND MAKH = 'K01'

-- CAU 3 --

SELECT A.MAHG AS Mã_Hàng, TENHG AS Tên_Hàng, SL AS Số_Lượng

FROM HANG A, KHOCHUA B

WHERE A.MAHG = B.MAHG

-- CAU 4 --

SELECT MAKH AS 'MÃ KHO', SUM(SL) AS 'SỐ LƯỢNG'

FROM KHOCHUA

GROUP BY MAKH

-- CAU 5 --

SELECT MAKH AS 'MÃ KHO', COUNT(*) AS 'TỔNG SỐ LƯỢNG'

FROM KHOCHUA

GROUP BY MAKH

HAVING COUNT(*)>=ALL (SELECT COUNT(*)

FROM KHOCHUA

GROUP BY MAKH )

-- CAU 6 --

SELECT MAKH AS 'KHO NHIỀU MẶT HÀNG NHẤT', SUM(SL) AS 'SỐ LƯỢNG'

FROM KHOCHUA

GROUP BY MAKH

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

FROM KHOCHUA

GROUP BY MAKH

-- CAU 7 --

SELECT MAHG AS KHO_CHỨA_NHIỀU_HÀNG_NHẤT, SUM(SL) AS TỔNG_SỐ_LƯỢNG

FROM KHOCHUA

GROUP BY MAHG

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

FROM KHOCHUA

GROUP BY MAHG)

-- CAU 8 --

SELECT MAHG AS MÃ_HÀNG, SUM(SL) AS SỐ_LƯỢNG_TỒN_KHO

FROM KHOCHUA

GROUP BY MAHG

--- CÂU 9

SELECT MAHG AS MÃ_HÀNG_TỒN_KHO_NHIỀU_NHẤT, SUM(SL) AS SỐ_LƯỢNG_TỒN_KHO

FROM KHOCHUA

GROUP BY MAHG

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

FROM KHOCHUA

GROUP BY MAHG )