Bài tập QLBH (Query Analyzer)

CREATE DATABASE QLBH

CREATE TABLE KHACHHANG
(
MAKH char(4) PRIMARY KEY,
HOTEN varchar(40) ,
DCHI varchar(50) ,
SODT varchar(20) ,
NGSINH smalldatetime ,
DOANHSO money ,
NGDK smalldatetime
)

CREATE TABLE NHANVIEN
(
MANV char(4) PRIMARY KEY,
HOTEN varchar(40) ,
SODT varchar(20) ,
NGVL smalldatetime
)

CREATE TABLE SANPHAM
(
MASP char(4) PRIMARY KEY,
TENSP varchar(40) ,
DVT varchar(20) ,
NUOCSX varchar(40) ,
GIA money
)

CREATE TABLE HOADON
(
SOHD int PRIMARY KEY,
NGHD smalldatetime ,
MAKH char(4),
MANV char(4),
TRIGIA money
)

CREATE TABLE CTHD
(
SOHD int ,
MASP char(4) ,
SL int
CONSTRAINT PK_CTHD PRIMARY KEY(SOHD,MASP)
)

ALTER TABLE HOADON ADD CONSTRAINT FK_HD_KH FOREIGN KEY (MAKH) REFERENCES KHACHHANG(MAKH)
ALTER TABLE HOADON ADD CONSTRAINT FK_HD_NV FOREIGN KEY (MANV) REFERENCES NHANVIEN(MANV)
ALTER TABLE CTHD ADD CONSTRAINT FK_CT_HD FOREIGN KEY (SOHD) REFERENCES HOADON(SOHD)
ALTER TABLE CTHD ADD CONSTRAINT FK_CT_SP FOREIGN KEY (MASP) REFERENCES SANPHAM(MASP)

SET DATEFORMAT DMY

INSERT INTO NHANVIEN (MANV, HOTEN, SODT, NGVL) VALUES ('NV01', 'Nguyen Nhu Nhut', 0927345678, '13/4/2006')
INSERT INTO NHANVIEN (MANV, HOTEN, SODT, NGVL) VALUES ('NV02', 'Le Thi Phi Yen', 0987567390, '21/4/2006')
INSERT INTO NHANVIEN (MANV, HOTEN, SODT, NGVL) VALUES ('NV03', 'Nguyen Van B', 0997047382, '27/4/2006')
INSERT INTO NHANVIEN (MANV, HOTEN, SODT, NGVL) VALUES ('NV04', 'Ngo Thanh Tuan', 0913758498, '24/6/2006')
INSERT INTO NHANVIEN (MANV, HOTEN, SODT, NGVL) VALUES ('NV05', 'Nguyen Thi Truc Thanh', 0918590387, '20/7/2006')

INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH01', 'Nguyen Van A', '731 Tran Hung Dao, Q5, TpHCM', 08823451, '20/10/1960', 13060000, '22/07/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH02', 'Tran Ngoc Han', '23/5 Nguyen Trai, Q5, TpHCM', 0908256478, '3/4/1974', 280000, '30/07/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH03', 'Tran Ngoc Linh', '45 Nguyen Canh Chan, Q1, TpHCM', 0938776266, '12/6/1980', 3860000, '05/08/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH04', 'Tran Minh Long', '50/34 Le Dai Hanh, Q10, TpHCM', 0917325476, '9/3/1965', 250000, '02/10/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH05', 'Le Nhat Minh', '34 Truong Dinh, Q3, TpHCM', 08246108, '10/3/1950', 21000, '28/10/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH06', 'Le Hoai Thuong', '227 Nguyen Van Cu, Q5, TpHCM', 08631738, '31/12/1981', 915000, '24/11/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH07', 'Nguyen Van Tam', '32/3 Tran Binh Trong, Q5, TpHCM', 0916783565, '6/4/1971', 12500, '01/12/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH08', 'Phan Thi Thanh', '45/2 An Duong Vuong, Q5, TpHCM', 093435756, '10/1/1971', 365000, '13/12/2006')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH09', 'Le Ha Vinh', '873 Le Hong Phong, Q5, TpHCM', 08654763, '3/9/1979', 70000, '14/01/2007')
INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) VALUES ('KH10', 'Ha Duy Lap', '34/34B Nguyen Trai, Q1, TpHCM', 08768904, '2/5/1983', 67500, '16/01/2007')

INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1001, '23/07/2006', 'KH01', 'NV01', 320000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1002, '12/08/2006', 'KH01', 'NV02', 840000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1003, '23/08/2006', 'KH02', 'NV01', 100000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1004, '01/09/2006', 'KH02', 'NV01', 180000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1005, '20/10/2006', 'KH01', 'NV02', 3800000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1006, '16/10/2006', 'KH01', 'NV03', 2430000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1007, '28/10/2006', 'KH03', 'NV03', 510000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1008, '28/10/2006', 'KH01', 'NV03', 440000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1009, '28/10/2006', 'KH03', 'NV04', 200000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1010, '01/11/2006', 'KH01', 'NV01', 5200000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1011, '04/11/2006', 'KH04', 'NV03', 250000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1012, '30/11/2006', 'KH05', 'NV03', 21000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1013, '12/12/2006', 'KH06', 'NV01', 5000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1014, '31/12/2006', 'KH03', 'NV02', 3150000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1015, '01/01/2007', 'KH06', 'NV01', 910000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1016, '01/01/2007', 'KH07', 'NV02', 12500)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1017, '02/01/2007', 'KH08', 'NV03', 35000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1018, '13/01/2007', 'KH08', 'NV03', 330000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1019, '13/01/2007', 'KH01', 'NV03', 30000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1020, '14/01/2007', 'KH09', 'NV04', 70000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1021, '16/01/2007', 'KH10', 'NV03', 67500)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1022, '16/01/2007', 'Null', 'NV03', 7000)
INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) VALUES (1023, '17/01/2007', 'Null', 'NV01', 330000)

INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('BC01', 'But chi', 'cay', 'Singapore', 3000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('BC02', 'But chi', 'cay', 'Singapore', 5000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('BC03', 'But chi', 'cay', 'Viet Nam', 3500)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('BC04', 'But chi', 'hop', 'Viet Nam', 30000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('BB01', 'But bi', 'cay', 'Viet Nam', 5000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('BB02', 'But bi', 'cay', 'Trung Quoc', 7000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('BB03', 'But bi', 'hop', 'Thai Lan', 100000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('TV01', 'Tap 100 giay mong', 'quyen', 'Trung Quoc', 2500)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('TV02', 'Tap 200 giay mong', 'quyen', 'Trung Quoc', 4500)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('TV03', 'Tap 100 giay tot', 'quyen', 'Viet Nam', 3000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('TV04', 'Tap 200 giay tot', 'quyen', 'Viet Nam', 5500)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('TV05', 'Tap 100 trang', 'chuc', 'Viet Nam', 23000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('TV06', 'Tap 200 trang', 'chuc', 'Viet Nam', 53000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('TV07', 'Tap 100 trang', 'chuc', 'Trung Quoc', 34000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST01', 'So tay 500 trang', 'quyen', 'Trung Quoc', 40000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST02', 'So tay loai 1', 'quyen', 'Viet Nam', 55000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST03', 'So tay loai 2', 'quyen', 'Viet Nam', 51000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST04', 'So tay', 'quyen', 'Thai Lan', 55000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST05', 'So tay mong', 'quyen', 'Thai Lan', 20000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST06', 'Phan viet bang', 'hop', 'Viet Nam', 5000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST07', 'Phan khong bui', 'hop', 'Viet Nam', 7000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST08', 'Bong bang', 'cai', 'Viet Nam', 1000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST09', 'But long', 'cay', 'Viet Nam', 5000)
INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) VALUES ('ST10', 'But long', 'cay', 'Trung Quoc', 7000)

INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1001, 'TV02', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1001, 'ST01', 5)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1001, 'BC01', 5)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1001, 'BC02', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1001, 'ST08', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1002, 'BC04', 20)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1002, 'BB01', 20)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1002, 'BB02', 20)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1003, 'BB03', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1004, 'TV01', 20)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1004, 'TV02', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1004, 'TV03', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1004, 'TV04', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1005, 'TV05', 50)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1005, 'TV06', 50)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1006, 'TV07', 20)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1006, 'ST01', 30)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1006, 'ST02', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1007, 'ST03', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1008, 'ST04',
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1009, 'ST05', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1010, 'TV07', 50)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1010, 'ST07', 50)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1010, 'ST08', 100)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1010, 'ST04', 50)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1010, 'TV03', 100)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1011, 'ST06', 50)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1012, 'ST07', 3)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1013, 'ST08', 5)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1014, 'BC02', 80)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1014, 'BB02', 100)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1014, 'BC04', 60)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1014, 'BB01', 50)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1015, 'BB01', 30)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1015, 'BB01', 7)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1016, 'TV01', 5)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1017, 'TV02', 1)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1017, 'TV03', 1)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1017, 'TV04', 5)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1018, 'ST04', 6)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1019, 'ST05', 1)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1019, 'ST06', 2)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1020, 'ST07', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1021, 'ST08', 5)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1021, 'TV01', 7)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1021, 'TV02', 10)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1022, 'ST07', 1)
INSERT INTO CTHD (SOHD, MASP, SL) VALUES (1023, 'ST04', 6)

/* Phan I - Ngon ngu dinh nghia du lieu - DDL*/
/* Cau 2 */
ALTER TABLE SANPHAM ADD GHICHU varchar(20)
/* Cau 3 */
ALTER TABLE KHACHHANG ADD LOAIKH tinyint
/* Cau 4 */
ALTER TABLE SANPHAM ALTER COLUMN GHICHU varchar(100)
/* Cau 5 */
ALTER TABLE SANPHAM DROP COLUMN GHICHU
/* Cau 6 */
ALTER TABLE KHACHHANG ALTER COLUMN LOAIKH varchar(20)
/* Cau 7 */
ALTER TABLE SANPHAM ADD CONSTRAINT CK_DVT CHECK (DVT='cay' OR DVT='hop' OR DVT='cai' OR DVT='quyen' OR DVT='chuc')
/* Cau 8 */
ALTER TABLE SANPHAM ADD CONSTRAINT CK_GIA CHECK (GIA >= 500)

/* Phan II - Ngon ngu thao tac du lieu - DML */
/* Cau 2: tao quan he SANPHAM1 chua toan bo du lieu cua quan he SANPHAM */
SELECT * INTO SANPHAM1 FROM SANPHAM
SELECT * INTO KHACHHANG1 FROM KHACHHANG
/* Cau 3 */
UPDATE SANPHAM1
SET GIA=GIA*0.05
WHERE NUOCSX='Thai Lan'
/* Cau 4 */
UPDATE SANPHAM1
SET GIA=GIA/0.05
WHERE NUOCSX='Trung Quoc'
and GIA <=10000
/* Cau 5 */
UPDATE KHACHHANG1
SET LOAIKH='Vip'
WHERE NGDK<1/1/2007 AND DOANHSO>=10000000 OR NGDK>=1/1/2007 AND DOANHSO>=2000000

/*Phan III - Ngon ngu truy van du lieu - SQL*/
/*Cau 1*/
SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX ='TRUNG QUOC'
/*Cau 2*/
SELECT MASP, TENSP
FROM SANPHAM
WHERE DVT='CAY' OR DVT='QUYEN'
/*Cau 3*/
SELECT MASP, TENSP
FROM SANPHAM
WHERE MASP LIKE 'B%01'
/*CAU 4*/
SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX='TRUNG QUOC' AND GIA BETWEEN 30000 AND 40000
/*CAU 5*/
SELECT MASP, TENSP
FROM SANPHAM
WHERE (NUOCSX = 'TRUNG QUOC' OR NUOCSX ='THAI LAN') AND ( GIA BETWEEN 30000 AND 40000)
/*CAU 6*/
SELECT SOHD, TRIGIA
FROM HOADON
WHERE NGHD= '1/1/2007' OR NGHD='2/1/2007'
/*CAUB 7*/
SELECT SOHD, TRIGIA
FROM HOADON
WHERE MONTH(NGHD)=1 AND YEAR(NGHD)=2007 ORDER BY NGHD ASC, TRIGIA DESC
/*CAU 8*/
SELECT KHACHHANG.MAKH,HOTEN
FROM KHACHHANG INNER JOIN HOADON ON KHACHHANG.MAKH=HOADON.MAKH
WHERE NGHD='1/1/2007'
/*CAU 8 cach 2*/
SELECT HOADON.MAKH, HOTEN
FROM KHACHHANG, HOADON
WHERE KHACHHANG.MAKH=HOADON.MAKH AND NGHD='1/1/2007'
/*CAU 9*/
SELECT HOADON.SOHD, TRIGIA
FROM NHANVIEN INNER JOIN HOADON ON NHANVIEN.MANV=HOADON.MANV
WHERE NGHD =' 28/10/2006' AND HOTEN='NGUYEN VAN B'
/*CAU 10*/
SELECT SANPHAM.MASP, TENSP
FROM ((KHACHHANG INNER JOIN HOADON ON KHACHHANG.MAKH=HOADON.MAKH)
INNER JOIN CTHD ON HOADON.SOHD=CTHD.SOHD)
INNER JOIN SANPHAM ON SANPHAM.MASP=CTHD.MASP
WHERE HOTEN='NGUYEN VAN A' AND MONTH(NGHD)=10 AND YEAR(NGHD)=2006
/*CAU11*/
SELECT DISTINCT SOHD
FROM CTHD
WHERE MASP='BB01'
OR SOHD IN
(SELECT DISTINCT SOHD
FROM CTHD
WHERE MASP='BB02')
/*CAU 12*/
SELECT DISTINCT SOHD
FROM CTHD
WHERE MASP='BB01' AND SL BETWEEN 10 AND 20
OR SOHD IN
(SELECT DISTINCT SOHD
FROM CTHD
WHERE MASP='BB02' AND SL BETWEEN 10 AND 20)

/*CAU13*/
SELECT DISTINCT SOHD
FROM CTHD
WHERE MASP='BB01' AND SL BETWEEN 10 AND 20
AND SOHD IN
(SELECT DISTINCT SOHD
FROM CTHD
WHERE MASP='BB02' AND SL BETWEEN 10 AND 20)
/*CAU14*/
SELECT SANPHAM.MASP, TENSP
FROM SANPHAM, HOADON
WHERE NUOCSX='TRUNGQUOC' OR NGHD='1/1/2007'
/*CAU 15*/
SELECT MASP, TENSP
FROM SANPHAM
WHERE MASP NOT IN
(SELECT MASP
FROM CTHD)
/*CAU 16*/
SELECT MASP, TENSP
FROM SANPHAM, HOADON
WHERE MASP NOT IN
(SELECT MASP
FROM CTHD) AND YEAR(NGHD)='2006'
/*CAU 17*/
SELECT MASP, TENSP
FROM SANPHAM, HOADON
WHERE MASP NOT IN
(SELECT MASP
FROM CTHD) AND YEAR(NGHD)='2006' AND NUOCSX='TRUNGQUOC'
/* CAU 22 KHONG GOM NHOM*/
SELECT MAX(TRIGIA) AS TGCAONHAT, MIN(TRIGIA) AS TGTHAPNHAT
FROM HOADON
/* CAU 23*/
SELECT AVG(TRIGIA) AS TGTRUNGBINHCUATATCAHDBANRA2006
FROM HOADON
WHERE YEAR(NGHD)='2006'
/* CAU 24*/
SELECT SUM(TRIGIA) AS DOANHTHUBANHANGTRONGNAM2006
FROM HOADON
WHERE YEAR(NGHD)='2006'
/* CAU 25*/
SELECT MAX(TRIGIA) AS SOHOADONCOGIATRICAONHAT2006
FROM HOADON
WHERE YEAR(NGHD)='2006'
/* CAU 33 GOM NHOM*/
SELECT NUOCSX, COUNT(MASP) AS TONGSANPHAMCUATUNGNUOCSX
FROM SANPHAM
GROUP BY NUOCSX
/* CAU 34*/
SELECT NUOCSX, MAX(GIA)AS GIACAONHAT, MIN(GIA) AS GIATHAPNHAT, AVG(GIA) GIATB
FROM SANPHAM
GROUP BY NUOCSX
/* CAU 35*/
SELECT DAY(NGHD) AS NGAY, SUM(TRIGIA) AS DOANHTHUBANHANGCAONHAT
FROM HOADON
GROUP BY DAY(NGHD)
/*CAU 36*/
SELECT SUM (SL) AS TONGSL
FROM CTHD INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD
WHERE MONTH(NGHD)='10' AND YEAR(NGHD)='2006'
/*CAU 37*/
SELECT MONTH(NGHD) AS THANG, SUM(TRIGIA) AS DOANHTHUBANHANG
FROM HOADON
WHERE YEAR(NGHD)='2006'
GROUP BY MONTH(NGHD)