demoTop1.sql

CREATE DATABASE QLHANG
USE QLHANG


CREATE TABLE VATTU
(
MAVT CHAR(10) NOT NULL PRIMARY KEY,
TENVT NVARCHAR(30) NOT NULL,
DVTINH CHAR(10) NOT NULL,
SLCON INT NOT NULL
)

CREATE TABLE HDBAN
(
MAHD CHAR(10) NOT NULL PRIMARY KEY,
NGAYXUAT DATETIME NOT NULL,
HOTENKHACH NVARCHAR(30)
)

CREATE TABLE HANGXUAT
(
MAHD CHAR(10) NOT NULL,
MAVT CHAR(10) NOT NULL, 
DONGIA MONEY NOT NULL, 
SLBAN INT NOT NULL,
PRIMARY KEY(MAHD, MAVT),
CONSTRAINT FK1 FOREIGN KEY(MAHD) REFERENCES HDBAN(MAHD),
CONSTRAINT FK2 FOREIGN KEY(MAVT) REFERENCES VATTU(MAVT)
)


INSERT INTO VATTU VALUES
('VT1', N'NHÔM', 'VND', 30),
('VT2', N'ĐỒNG', 'VND', 70)


INSERT INTO HDBAN VALUES
('HD1', '3/22/2021', N'ĐỖ NGỌC ĐỨC'),
('HD2', '3/25/2021', N'ĐỖ NGỌC HOÀI')


INSERT INTO HANGXUAT VALUES
('HD1', 'VT1', 30000, 30),
('HD1', 'VT2', 55000, 33),
('HD2', 'VT2', 70000, 39),
('HD2', 'VT1', 60000, 25)

SELECT * FROM VATTU
SELECT * FROM HDBAN
SELECT * FROM HANGXUAT

SELECT TOP(1)  MAHD, SUM(SLBAN * DONGIA) AS 'TONG TIEN'
FROM HANGXUAT
GROUP BY MAHD
ORDER BY SUM(SLBAN * DONGIA) DESC