Aggregation-Functions_Nested-Queries-Division_Operation.sql

USE QuanLyBanHang;
-------- PHAN3--------
-- Câu15--
SELECT MASP,TENSP
FROM SANPHAM
WHERE MASP NOT IN(
SELECT MASP
FROM CTHD
);
-- Câu16--
SELECT MASP,TENSP
FROM SANPHAM
WHERE MASP NOT IN(
SELECT MASP
FROM CTHD INNER JOIN HOADON ON CTHD.SOHD = HOADON.SOHD
WHERE YEAR(NGHD) = 2006
);
-- CAU17--
SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX = 'Trung Quoc' AND MASP NOT IN(
SELECT CTHD.MASP FROM CTHD INNER JOIN HOADON ON CTHD.SOHD = HOADON.SOHD
WHERE YEAR(NGHD) = 2006 AND NUOCSX ='Trung Quoc'
);
-- C2
SELECT MASP, TENSP
FROM SANPHAM AS A
WHERE NUOCSX = 'Trung Quoc' AND NOT EXISTS(
SELECT * FROM CTHD INNER JOIN HOADON ON CTHD.SOHD = HOADON.SOHD INNER JOIN SANPHAM ON SANPHAM.MASP = CTHD.MASP
WHERE YEAR(NGHD) = 2006 AND NUOCSX ='Trung Quoc' AND CTHD.MASP = A.MASP 
-- Truy vấn lồng tương quan A
-- Giá trị not exists là TRUE/False: nếu trả về giá trị thì là False, nếu không thì True.
);
-- CAU18
SELECT SOHD FROM HOADON AS A
WHERE NOT EXISTS(
	SELECT MASP FROM SANPHAM AS B
    WHERE NUOCSX = 'Singapore' AND NOT EXISTS(
    SELECT * FROM CTHD
    WHERE A.SOHD = CTHD.SOHD AND B.MASP = CTHD.MASP
    )
);
-- CAU20
SELECT COUNT(SOHD) AS SOHOADON
FROM HOADON
WHERE MAKH NOT IN (SELECT MAKH FROM KHACHHANG WHERE HOADON.MAKH=KHACHHANG.MAKH AND HOADON.NGHD >=KHACHHANG.NGDK); 

-- CAU21
SELECT  COUNT( DISTINCT MASP) AS SOSANPHAM
FROM CTHD,HOADON
WHERE YEAR(NGHD)=2006;

-- CAU 22
SELECT MAX(TRIGIA) AS TRIGIACAONHAT, MIN(TRIGIA) AS TRIGIATHAPNHAT
FROM HOADON;

-- CAU23
SELECT AVG(TRIGIA) AS TRUNGBINHTRIGIA
FROM HOADON
WHERE YEAR(NGHD)=2006;

-- CAU 24
SELECT SUM(TRIGIA) AS DOANHTHU
FROM HOADON
WHERE YEAR(NGHD)=2006
-- CAU25
SELECT SOHD
FROM HOADON
WHERE TRIGIA = (
SELECT MAX(TRIGIA)
FROM HOADON
);
-- CAU26---
SELECT HOTEN
FROM KHACHHANG INNER JOIN HOADON ON KHACHHANG.MAKH = HOADON.MAKH
WHERE YEAR(NGHD) = 2006 AND TRIGIA = (
SELECT MAX(TRIGIA)
FROM HOADON
);
-- CAU27
SELECT MAKH, HOTEN
FROM KHACHHANG
ORDER BY DOANHSO DESC
LIMIT 3;

-- CAU28
SELECT MASP,TENSP
FROM SANPHAM INNER JOIN (
SELECT DISTINCT GIA 
FROM SANPHAM 
ORDER BY GIA DESC
LIMIT 3
) AS SP3 ON SANPHAM.GIA= SP3.GIA;


-- CAU29
SELECT MASP,TENSP
FROM SANPHAM INNER JOIN (
SELECT DISTINCT GIA 
FROM SANPHAM 
ORDER BY GIA DESC
LIMIT 3
) AS SP3 ON SANPHAM.GIA= SP3.GIA
WHERE NUOCSX = 'Thai Lan'
-- CAU30
SELECT MASP,TENSP
FROM SANPHAM INNER JOIN (
SELECT DISTINCT GIA 
FROM SANPHAM 
WHERE NUOCSX = 'Trung Quoc'
ORDER BY GIA DESC
LIMIT 3
) AS SP3 ON SANPHAM.GIA= SP3.GIA

-- ---QUANLYGIAOVU-----
USE QLGV
-- PHAN 3--
-- CAU7--
-- III.7 Tìm những môn học (mã môn học, tên môn học) mà giáo viên chủ nhiệm lớp “K11” dạy trong học kỳ 1 năm 2006
SELECT MONHOC.MAMH, TENMH
FROM MONHOC INNER JOIN (SELECT * FROM GIANGDAY
WHERE YEAR(TUNGAY)= 2006 AND YEAR(DENNGAY) = 2006 AND HOCKY = 1 AND MAGV = (SELECT MAGVCN
FROM LOP
WHERE MALOP = 'K11')) AS GD1 ON MONHOC.MAMH = GD1.MAMH;
-- CAU8
SELECT HOCVIEN.HO,HOCVIEN.TEN
FROM HOCVIEN INNER JOIN (SELECT TRGLOP
FROM LOP
WHERE MALOP = 
(SELECT MALOP FROM GIANGDAY
WHERE MAGV = (SELECT MAGV
FROM GIAOVIEN
WHERE HOTEN = 'Nguyen To Lan')AND MAMH = (SELECT MAMH
FROM MONHOC
WHERE TENMH ='Co so du lieu'))
) AS SV1 ON HOCVIEN.MAHV = SV1.TRGLOP
-- CAU 9
SELECT MAMH, TENMH
FROM MONHOC
WHERE MAMH IN (SELECT MAMH_TRUOC
FROM DIEUKIEN
WHERE MAMH='CSDL'
);
-- CAU10
SELECT MAMH, TENMH
FROM MONHOC
WHERE MAMH IN (SELECT MAMH
FROM DIEUKIEN
WHERE MAMH_TRUOC=(SELECT MAMH
FROM MONHOC
WHERE TENMH='Cau truc roi rac'
));
-- CAU11
SELECT HOTEN
FROM GIANGDAY, GIAOVIEN
WHERE GIANGDAY.MAGV=GIAOVIEN.MAGV
	AND MALOP='K11' AND MAMH='CTRR' AND HOCKY=1 AND NAM=2006
	AND GIANGDAY.MAGV IN(SELECT MAGV FROM GIANGDAY WHERE MALOP='K12' AND MAMH='CTRR' AND HOCKY=1 AND NAM=2006)
-- CAU12
SELECT KETQUATHI.MAHV,HO,TEN
FROM HOCVIEN, KETQUATHI
WHERE HOCVIEN.MAHV=KETQUATHI.MAHV
	AND MAMH='CSDL' AND LANTHI=1 AND KQUA='Khong Dat'
	AND KETQUATHI.MAHV NOT IN (SELECT MAHV FROM KETQUATHI WHERE MAMH='CSDL' AND LANTHI>1)
-- CAU13
SELECT GIAOVIEN.MAGV,HOTEN
FROM GIAOVIEN
WHERE MAGV NOT IN(SELECT MAGV FROM GIANGDAY)
-- CAU14
SELECT MAGV, HOTEN
FROM GIAOVIEN
WHERE NOT EXISTS
(
	SELECT *
	FROM MONHOC
	WHERE MONHOC.MAKHOA = GIAOVIEN.MAKHOA
	AND NOT EXISTS
	(
		SELECT *
		FROM GIANGDAY
		WHERE GIANGDAY.MAMH = MONHOC.MAMH
		AND GIANGDAY.MAGV = GIAOVIEN.MAGV
	)
)

-- CAU15
SELECT DISTINCT
	(HO+' '+TEN) HoTen
FROM
	HOCVIEN, KETQUATHI
WHERE
	HOCVIEN.MAHV = KETQUATHI.MAHV
	AND MALOP = 'K11'
	AND ((LANTHI = 2 AND DIEM = 5)
	OR HOCVIEN.MAHV IN
	(
		SELECT DISTINCT MAHV
		FROM KETQUATHI
		WHERE KQUA = 'Khong Dat'
		GROUP BY MAHV, MAMH
		HAVING COUNT(*) > 3	
	))
-- CAU16
SELECT HOTEN
FROM 
	GIAOVIEN, GIANGDAY
WHERE 
	GIAOVIEN.MAGV = GIANGDAY.MAGV
	AND MAMH='CTRR'
GROUP BY
	GIAOVIEN.MAGV, HOTEN, NAM
HAVING COUNT(*)>=2