tmp.sql

CREATE DATABASE BANXANG
USE BANXANG

-- Câu 1.

CREATE TABLE DNNK
(
	MADN char(5) NOT NULL,
	TENDN varchar(50),
	NGAYTL smalldatetime,
	DIACHI varchar(50),
	SODT varchar(15),
	LOAIDN varchar(20),
)

CREATE TABLE LOAIXANG
(
	MALX char(5) NOT NULL,
	TENLX varchar(25),
	MDBQ varchar(2),
)

CREATE TABLE XANGDAU
(
	MAXD char(5) NOT NULL,
	TENXD char(40),
	MADN char(5) NOT NULL,
	MALX char(5) NOT NULL,
	GIACOSO money,
	THUEPHI int,
)

CREATE TABLE CUAHANG
(
	MACH char(5) NOT NULL,
	TENCH varchar(40),
	BACCL char(2),
	RONGDD numeric,
	CAOTB numeric,
	CAOMC numeric,
)

CREATE TABLE NHAP
(
	MACH char(5) NOT NULL,
	MAXD char(5) NOT NULL,
	NGAYNHAP smalldatetime NOT NULL,
	SOLUONG numeric,
	GIANHAP money,
)

ALTER TABLE DNNK
ADD CONSTRAINT PK_DNNK
PRIMARY KEY (MADN)

ALTER TABLE LOAIXANG
ADD CONSTRAINT PK_LOAIXANG
PRIMARY KEY (MALX)

ALTER TABLE XANGDAU
ADD CONSTRAINT PK_XANGDAU
PRIMARY KEY (MAXD)

ALTER TABLE XANGDAU
ADD CONSTRAINT FK_MADN
FOREIGN KEY (MADN) REFERENCES DNNK(MADN)

ALTER TABLE XANGDAU
ADD CONSTRAINT FK_MALX
FOREIGN KEY (MALX) REFERENCES LOAIXANG(MALX)

ALTER TABLE CUAHANG
ADD CONSTRAINT PK_CUAHANG
PRIMARY KEY (MACH)

ALTER TABLE NHAP
ADD CONSTRAINT PK_NHAP
PRIMARY KEY (MACH, MAXD, NGAYNHAP)

ALTER TABLE NHAP
ADD CONSTRAINT FK_MACH
FOREIGN KEY (MACH) REFERENCES CUAHANG(MACH)

ALTER TABLE NHAP
ADD CONSTRAINT FK_MAXD
FOREIGN KEY (MAXD) REFERENCES XANGDAU(MAXD)

GO
CREATE TRIGGER CHK_NGAYNHAP
ON NHAP
FOR INSERT, UPDATE
AS
BEGIN
  IF EXISTS
  (
    SELECT *
    FROM inserted
		INNER JOIN XANGDAU xd
		ON inserted.MAXD = xd.MAXD
		INNER JOIN DNNK dnnk
		ON xd.MADN = dnnk.MADN
    WHERE (inserted.NGAYNHAP < dnnk.NGAYTL) 
  )
  BEGIN
    PRINT 'Ngay nhap xang dau phai lon hon ngay thanh lap doanh nghiep xuat khau!'
    ROLLBACK TRANSACTION
  END
END

GO
CREATE TRIGGER CHK_NGAYTL
ON DNNK
FOR INSERT, UPDATE
AS
BEGIN
  IF EXISTS
  (
    SELECT *
    FROM inserted
		INNER JOIN XANGDAU xd
		ON xd.MADN = inserted.MADN
		INNER JOIN NHAP nhap
		ON xd.MAXD = nhap.MAXD
    WHERE (inserted.NGAYTL > nhap.NGAYNHAP) 
  )
  BEGIN
    PRINT 'Ngay thanh lap doanh nghiep xuat khau phai nho hon ngay nhap xang dau!'
    ROLLBACK TRANSACTION
  END
END


SELECT cuahang.MACH, xangdau.MAXD
FROM CUAHANG cuahang
	INNER JOIN NHAP nhap
	ON cuahang.MACH = nhap.MACH
	INNER JOIN XANGDAU xangdau
	ON nhap.MAXD = xangdau.MAXD
WHERE
	YEAR(nhap.NGAYNHAP) = 2021
	AND MONTH(nhap.NGAYNHAP) = 10
	AND nhap.SOLUONG > 1000

SELECT dnnk.MADN, dnnk.TENDN
FROM XANGDAU xangdau
	INNER JOIN NHAP nhap
	ON xangdau.MAXD = nhap.MAXD
	INNER JOIN DNNK DNNK
	ON dnnk.MADN = xangdau.MADN
	INNER JOIN LOAIXANG loaixang
	ON xangdau.MALX = loaixang.MALX
WHERE
	xangdau.THUEPHI > 1
	AND loaixang.TENLX = 'Xăng không chì E5'

  -- Câu 3.c.

SELECT loaixang.MALX, ISNULL(loaixang.TENLX, '')
FROM LOAIXANG loaixang
	INNER JOIN XANGDAU xangdau
	ON loaixang.MALX = xangdau.MALX
WHERE
	loaixang.MDBQ = 'V'

  
SELECT cuahang.MACH, cuahang.TENCH
FROM XANGDAU xangdau
	INNER JOIN NHAP nhap
	ON xangdau.MAXD = nhap.MAXD
	INNER JOIN DNNK DNNK
	ON dnnk.MADN = xangdau.MADN
	INNER JOIN LOAIXANG loaixang
	ON xangdau.MALX = loaixang.MALX
	INNER JOIN CUAHANG cuahang
	ON cuahang.MACH = nhap.MACH
WHERE
	NOT (xangdau.TENXD = 'Xăng không chì R95' AND nhap.NGAYNHAP = '09/10/2022')
-- Câu 3.e.

SELECT cuahang.MACH, cuahang.TENCH, COUNT(*) AS SOLANHAP2022
FROM NHAP nhap
	INNER JOIN CUAHANG cuahang
	ON cuahang.MACH = nhap.MACH
WHERE YEAR(nhap.NGAYNHAP) = 2022
GROUP BY cuahang.MACH, cuahang.TENCH