Untitled
domcvn
pgsql
a year ago
2.6 kB
31
Indexable
-- tao database CREATE DATABASE BAN_HANG; -- tao cac bang CREATE TABLE HANG_HOA ( MAHH VARCHAR(4), TENHH VARCHAR(50), DVT VARCHAR(50), DONGIA INT ); CREATE TABLE NHAN_VIEN ( MANV VARCHAR(4), HOTENNV VARCHAR(50), GIOITINH BOOL, DINHMUC INT ); CREATE TABLE BAN_HANG ( SOHD VARCHAR(4), MAHH VARCHAR(4), MANV VARCHAR(4), SLBAN INT ); -- tao khoa chinh / khoa ngoai ALTER TABLE HANG_HOA ADD PRIMARY KEY (MAHH); ALTER TABLE NHAN_VIEN ADD PRIMARY KEY (MANV); ALTER TABLE BAN_HANG ADD FOREIGN KEY (MAHH) REFERENCES HANG_HOA(MAHH); ALTER TABLE BAN_HANG ADD FOREIGN KEY (MANV) REFERENCES NHAN_VIEN(MANV); -- nhap du lieu INSERT INTO HANG_HOA VALUES ('MH01', 'Romano', 'Chai', 45000), ('MH02', 'Johnson', 'Chai', 55000), ('MH03', 'Enchenter', 'Chai', 35000), ('MH04', 'Clear', 'Chai', 65000), ('MH05', 'Double Rich', 'Chai', 60000); INSERT INTO NHAN_VIEN VALUES ('NV01', 'Nguyễn Đắc Thăng', TRUE, 70), ('NV02', 'Vũ Thị Hạnh', FALSE, 60), ('NV03', 'Phan Thanh Tâm', TRUE, 80), ('NV04', 'Nguyễn Kim Đức', TRUE, 100), ('NV05', 'Nguyễn TrongTuê', TRUE, 90); INSERT INTO BAN_HANG VALUES ('X001', 'MH01', 'NV01', 120), ('X001', 'MH01', 'NV05', 390), ('X002', 'MH02', 'NV02', 150), ('X002', 'MH02', 'NV04', 360), ('X003', 'MH03', 'NV03', 80), ('X003', 'MH03', 'NV04', 180), ('X004', 'MH04', 'NV02', 300), ('X004', 'MH04', 'NV03', 210), ('X005', 'MH05', 'NV01', 70), ('X005', 'MH05', 'NV05', 90); -- cau 3.1 SELECT NHAN_VIEN.MANV, NHAN_VIEN.HOTENNV, HANG_HOA.MAHH, HANG_HOA.TENHH, NHAN_VIEN.DINHMUC, BAN_HANG.SLBAN, HANG_HOA.DONGIA, (BAN_HANG.SLBAN * HANG_HOA.DONGIA) AS THANHTIEN FROM HANG_HOA INNER JOIN (NHAN_VIEN INNER JOIN BAN_HANG ON NHAN_VIEN.MANV = BAN_HANG.MANV) ON HANG_HOA.MAHH = BAN_HANG.MAHH WHERE NHAN_VIEN.HOTENNV LIKE 'Nguyễn%'; -- cau 3.2 SELECT NHAN_VIEN.MANV, NHAN_VIEN.HOTENNV, HANG_HOA.MAHH, HANG_HOA.TENHH, NHAN_VIEN.DINHMUC, BAN_HANG.SLBAN, HANG_HOA.DONGIA, (BAN_HANG.SLBAN * HANG_HOA.DONGIA) AS THANHTIEN FROM HANG_HOA INNER JOIN (NHAN_VIEN INNER JOIN BAN_HANG ON NHAN_VIEN.MANV = BAN_HANG.MANV) ON HANG_HOA.MAHH = BAN_HANG.MAHH WHERE BAN_HANG.SLBAN > NHAN_VIEN.DINHMUC ORDER BY BAN_HANG.SLBAN; -- cau 3.3 SELECT HANG_HOA.MAHH, HANG_HOA.TENHH, SUM(BAN_HANG.SLBAN) AS TONGSOLUONG FROM HANG_HOA INNER JOIN BAN_HANG ON HANG_HOA.MAHH = BAN_HANG.MAHH GROUP BY HANG_HOA.MAHH ORDER BY HANG_HOA.MAHH;
Editor is loading...
Leave a Comment