kha_db.sql

-- 1
SELECT mamh, tenmh, tclt, tcth
FROM Khoa JOIN Monhoc
  ON (Khoa.makhoa = Monhoc.makhoa)
WHERE tenkhoa = N'Ngoại ngữ';

-- 2
SELECT *
FROM Monhoc
WHERE tclt > tcth;

-- 3
SELECT *
FROM Giaovien JOIN Khoa
  ON (Giaovien.makhoa = Khoa.makhoa)
WHERE gioitinh = 'Nam'
  AND tenkhoa = 'CNTT'
  AND ngvl >= '2010-01-01';
  
-- 4
SELECT *
FROM Giaovien
WHERE hoten LIKE N'Nguyễn%'
  AND MONTH(ngsinh) = 10; 

-- 5
SELECT *
FROM Lop
WHERE siso = (SELECT MAX(siso)
               FROM Lop);

-- 6
  SELECT Hocvien.mahv, ho + ' ' + ten
  FROM Hocvien JOIN Ketquathi
        ON (Hocvien.mahv = Ketquathi.mahv)
      JOIN Monhoc
        ON (Ketquathi.mamh = Monhoc.mamh)
  WHERE tenmh = N'Tin học căn bản 1';
INTERSECT
  SELECT Hocvien.mahv, ho + ' ' + ten
  FROM Hocvien JOIN Ketquathi
        ON (Hocvien.mahv = Ketquathi.mahv)
      JOIN Monhoc
        ON (Ketquathi.mamh = Monhoc.mamh)
  WHERE tenmh = N'Lập trình căn bản';

-- 7
SELECT *
FROM Giaovien
WHERE mucluong * heso = (SELECT MIN(mucluong * heso)
                          FROM Giaovien);

-- 8
SELECT Giaovien.magv, hoten,
       hocky,
       COUNT(*) AS solopdagiangday
FROM Giaovien JOIN Giangday
  ON (Giaovien.magv = Giangday.magv)
WHERE YEAR(nam) = 2020
GROUP BY Giaovien.magv, hoten, hocky;

-- 9
SELECT Hocvien.mahv, ho + ' ' + ten AS [ho va ten],
       ngaysinh, COUNT(mamh) AS [so mon hoc da tham gia]
FROM Hocvien JOIN Ketquathi
     ON (Hocvien.mahv = Ketquathi.mahv)
WHERE lanthi = 1
GROUP BY Hocvien.mahv, ho + ' ' + ten, ngaysinh;

-- 10
SELECT Hocvien.mahv, ho + ' ' + ten AS [ho va ten],
       ngaysinh, COUNT(mamh) AS [so mon hoc can tra no]
FROM Hocvien JOIN Ketquathi
     ON (Hocvien.mahv = Ketquathi.mahv)
WHERE lanthi = 2
      AND diem < 5.0
GROUP BY Hocvien.mahv, ho + ' ' + ten;

-- 11
SELECT Monhoc.mamh, tenmh,
      COUNT(mahv) AS sohocvienthamgia
FROM Monhoc JOIN Ketquathi
    ON (Monhoc.mamh = Ketquathi.mamh)
WHERE lanthi = 1
GROUP BY Monhoc.mamh, tenmh;

-- 12
SELECT *
FROM Monhoc
WHERE mamh NOT IN (SELECT mamh
                          FROM Giangday);