Ai giúp mình câu 45 Quản Lí Chuyến bay cái.Thank nha

--Cau 1: Cho biet ma so, ten phi Cong, dia chi, dien thoai cua cac phi cong da tung lai may bay loai B747
select NV.MANV,NV.TEN,NV.DCHI,NV.DTHOAI
from LICHBAY LB, NHANVIEN NV, PHANCONG PC
where LB.MALOAI=N'B747' and PC.MACB=LB.MACB AND NV.MANV=PC.MANV AND NV.LOAINV=1

--Cau 2:Cho Biet ma so va ngay di cua cac chuyen bay xuat phat tu san bay DCA trong khoang thoi gian tu 14h->18h
select LB.NGAYDI,CB.MACB
from LICHBAY LB,CHUYENBAY CB
where CB.SOBD=N'DCA' AND GIODI between '14:00' and '18:00' AND LB.MACB=CB.MACB

--Cau 3:Cho biet ten nhung NV dc phan cong tren chuyen bay co ma so 100 dc xuat phat tai san bay SLC.Cac dong du lieu xuat ra ko dc trung lap
select distinct NV.TEN
from CHUYENBAY CB, PHANCONG PC, NHANVIEN NV
where CB.MACB=100 and CB.SOBD=N'SLC' AND PC.MACB=CB.MACB AND NV.MANV=PC.MANV

--Cau 4:Cho biet ma loai va so hieu may bay da tung xuat phat tai san bay MIA, Cac dong du lieu xuat ra ko dc trung lap
select distinct CB.MACB,LC.SOHIEU,CB.SOBD
from CHUYENBAY CB, LICHBAY LC
where CB.SOBD=N'MIA' and LC.MACB=CB.MACB

--Cau 5:Cho biet ma Chuyen bay, ngay di, cung voi ten, dia chi,dien thoai cua tat ca cac hanh khach di tren chuyen bay do. Sap xep theo thu tu tang dan cua ma chuyen bay va theo ngay di giam dan
select DC.MACB,DC.NGAYDI,KH.TEN,KH.DCHI,KH.DTHOAI
from DATCHO DC,KHACHHANG KH,CHUYENBAY CB
where CB.MACB=DC.MACB and DC.MAKH=KH.MAKH
order by MACB,DC.NGAYDI desc

--Cau 6 Cho biet ma chuyen bay,Ngay di,cung voi ten,dia chi,dien thoai cua tat ca nhung NV dc phan cong trong chuyen bay do,Sap xep theo thu tu tang dan cua ma chuyen bay va theo nhay di giam dan
select PC.MACB,PC.NGAYDI,NV.TEN,NV.DCHI,NV.DTHOAI
from NHANVIEN NV,PHANCONG PC, CHUYENBAY CB
where PC.MACB=CB.MACB and NV.MANV=PC.MANV
order by PC.MACB,PC.NGAYDI desc

--Cau 7:Cho biet ma chuyen bay,ngay di,ma so va ten cua nhung phi cong dc phan cong vao chuyen bay ha canh xuong san bay ORD
select PC.MACB,PC.NGAYDI,NV.MANV,NV.TEN
from CHUYENBAY CB,NHANVIEN NV, PHANCONG PC
where CB.SBDEN=N'ORD' and CB.MACB=PC.MACB and NV.MANV=PC.MANV and NV.LOAINV=1

--Cau 8:Cho biet cac chuyen bay (ma so chuyen bay, ngay di,va ten cua phi cong)trong do phi cong co ma 1001 dc phan cong lai
select CB.MACB,PC.NGAYDI,NV.TEN
from NHANVIEN NV, PHANCONG PC,CHUYENBAY CB
where NV.MANV=1001 and NV.MANV=PC.MANV and PC.MACB=CB.MACB

--Cau 9:Cho biet thong tin (ma chuyen bay, san bay di,gio di, gio den,ngay di ) cua nhung chuyen bay ha canh xuong DEN.Cac chuyen bay dc liet ke theo ngay di giam dan va san bay xuat phat (SBDI) tang dan
select CB.MACB,CB.SOBD,CB.GIODI,CB.GIODEN,LB.NGAYDI,CB.SBDEN
from CHUYENBAY CB, LICHBAY LB
where CB.SBDEN=N'DEN' and CB.MACB=LB.MACB
order by LB.NGAYDI desc,CB.SOBD

--Cau 10:Voi moi phi cong,Cho biet hang san xuat, va ma loai may bay ma phi cong nay co kha nang bay dc.Xuat ra ten phi cong,hang san xuat va ma loai may bay
select NV.TEN,MB.HANGSX,MB.MALOAI
from NHANVIEN NV,KHANANG KN, LOAIMB MB
where NV.MANV=KN.MANV and MB.MALOAI=KN.MALOAI

--Cau 11: Cho biet ma phi cong,ten phi cong,da lai may bay trong chuyen bay ma so 100 vao ngay 11/01/2000
select NV.MANV,NV.TEN
from NHANVIEN NV, LICHBAY LB,PHANCONG PC
where LB.MACB=100 and LB.NGAYDI='11/01/2000' and PC.MACB=LB.MACB and NV.MANV=PC.MANV and LB.NGAYDI=PC.NGAYDI

--Cau 12:Cho biet ma chuyen bay,ma nhan vien,ten nhan vien,dc phan cong vao chuyen bay xuat phat ngay 10/31/2000 tai San bay MIA vao luc 20:30
select CB.MACB,NV.MANV,NV.TEN
from PHANCONG PC, CHUYENBAY CB, NHANVIEN NV
where PC.NGAYDI='10/31/2000' and CB.SOBD=N'MIA' and CB.GIODI='20:30' and PC.MACB=CB.MACB and NV.MANV=PC.MANV

--Cau 13:Cho biet thong tin ve chuyen bay (ma chuyen bay, so hieu,ma loai,hang sx)ma Quang da lai
select PC.MACB,LB.SOHIEU,LB.MALOAI,MB.HANGSX
from NHANVIEN NV,PHANCONG PC,LICHBAY LB,LOAIMB MB
where NV.TEN=N'QUANG' and NV.MANV=PC.MANV and PC.MACB=LB.MACB and LB.MALOAI=MB.MALOAI


--Cau 15:Cho biet ten khach hang da di chuyen bay tren may bay cua hang "Boeing"
select *
from KHACHHANG KH, LOAIMB MB,LICHBAY LB,DATCHO DC
where MB.HANGSX=N'Boeing' and MB.MALOAI=LB.MALOAI and LB.MACB=DC.MACB and DC.MAKH=KH.MAKH

--Cau 16:Cho biet ma cac chuyen bay chi bay voi may bay so hieu 10 va ma loai B747
select LB.MACB
from LICHBAY LB
where LB.SOHIEU=10 and LB.MALOAI=N'B747'

--Cau 17:Voi moi san bay (SBDEN), cho biet so luong chuyen bay ha canh xuong san bay do.Ket qua dc sap xep theo thu tu tang dan cua san bay den
select count(MACB)as SLchuyenbay,SBDEN
from CHUYENBAY
GROUP by SBDEN

--Cau 18:Voi moi san bay (SBDI)cho biet so luong chuyen bay xuat phat tu san bay do, sap xep theo thu tu tang dan cua san bay xuat phat
select COUNT (CB.MACB)as SLChuyenBay, CB.SOBD
from CHUYENBAY CB
GROUP by CB.SOBD
order by SLChuyenBay

--Cau 19:voi moi san bay (SBDI)cho biet so luong chuyen bay xuat phat theo tung ngay .Xuat ra ma san bay di,Ngay va so luong
select COUNT(CB.MACB)as SLChuyenBay,LB.NGAYDI,CB.SOBD
from CHUYENBAY CB, LICHBAY LB
where CB.MACB=LB.MACB
GROUP by LB.NGAYDI,CB.SOBD

--Cau 20:voi moi san bay (SBDEN)cho biet so luong chuyen bay ha canh theo tung ngay.Xuat ra ma san bay den,Ngay va so luong
select COUNT(CB.MACB)as SLChuyenBay,CB.SBDEN,LB.NGAYDI
from CHUYENBAY CB,LICHBAY LB
where CB.MACB=LB.MACB
GROUP by CB.SBDEN,LB.NGAYDI

--Cau 21:Voi moi lich bay, cho biet ma chuyen bay,ngay di cung voi so luong NV ko phai la phi cong cua chuyen bay do
select COUNT(NV.MANV)as SLNhanVien, LB.MACB,LB.NGAYDI
from LICHBAY LB,PHANCONG PC,NHANVIEN NV
where NV.LOAINV=0 and NV.MANV=PC.MANV and PC.MACB=LB.MACB and PC.NGAYDI=LB.NGAYDI
GROUP by LB.MACB,LB.NGAYDI

--Cau 22:So luong chuyen bay xuat phat tu san bay MIA vao ngay 11/01/2000
select COUNT(CB.MACB)as SLChuyenBay,CB.SOBD,LB.NGAYDI
from CHUYENBAY CB,LICHBAY LB
where CB.SOBD=N'MIA' and CB.MACB=LB.MACB and LB.NGAYDI='11/01/2000'
GROUP by CB.SOBD,LB.NGAYDI

--Cau 23:Voi moi chuyen bay cho biet ma chuyen bay ,ngay di,so luong nhan vien dc phan cong tren chuyen bay do,sap sep theo thu tu giam dan cua so luong
select COUNT(PC.MANV)as SLNHANVIEN,PC.MACB,PC.NGAYDI
from PHANCONG PC
GROUP by PC.MACB,PC.NGAYDI
order by SLNHANVIEN

--Cau 24:Voi moi chuyen bay, cho biet ma chuyen bay,ngay di, cung voi so luong hanh khach da dat cho cua chuyen bay do, sap xep theo thu tu giam dan cua so luong
select COUNT(MAKH)as SlKhachHang, MACB,NGAYDI
from DATCHO
group by MACB,NGAYDI
order by SlKhachHang

--Cau 25:Voi moi chuyen bay cho biet ma chuyen bay,ngay di, tong luong cua phi hanh doan,sap theo thu tu tang dan cua Tong luong
select SUM(NV.LUONG)as TongLuong,PC.MACB,PC.NGAYDI
from PHANCONG PC, NHANVIEN NV
where PC.MANV=NV.MANV
GROUP by PC.MACB,PC.NGAYDI
order by TongLuong

--Cau 26:Cho biet luong trung binh cua cac nhan vien ko phai la PHi cong
select AVG(LUONG)as LuongTB
from NHANVIEN
where LOAINV=0

--Cau 27:Cho biet luong trung binh cua cac phi cong
select AVG(LUONG)as LuongTB
from NHANVIEN
where LOAINV=1

--Cau 28:Voi moi loai may bay,cho biet so luong chuyen bay,da bay tren loai may bay do ha canh xuong san bay ORD.Xuat ra ma loai may bay,so luong chuyen bay
select COUNT(*)as SLChuyenBay,LB.MALOAI
from LICHBAY LB,CHUYENBAY CB
where LB.MACB=CB.MACB and CB.SBDEN=N'ORD'
GROUP by LB.MALOAI

--Cau 29:Cho biet san bay(SBDI) va so luong chuyen bay co nhieu hon 2 chuyen bay xuat phat trong khoang 10h->22h
select COUNT(*)as SLChuyenbay, CB.SOBD
from CHUYENBAY CB
where CB.GIODI between '10:00' and '22:00'
GROUP by CB.SOBD having COUNT(*)>2

--Cau 30:Cho biet ten phi cong dc phan cong vao it nhat 2 chuyen bay trong cung 1 ngay
select COUNT(PC.MACB)as SLChuyenBay,PC.NGAYDI,NV.TEN
from PHANCONG PC, NHANVIEN NV
where NV.MANV=PC.MANV and NV.LOAINV=1
GROUP by PC.NGAYDI,NV.TEN having COUNT(PC.MACB)>=2

--Cau 31:Cho biet ma chuyen bay va ngay di cua nhung chuyen bay co it hon 3 hanh khach dat cho
select COUNT(*)as SLKHACHHANG, MACB,NGAYDI
from DATCHO
GROUP by MACB,NGAYDI having COUNT(*)<3

--Cau 32:Cho biet so hieu may bay va loai may bay ma phi cong co ma 1001 dc phan cong lai tren 2 lan
select COUNT(*)as SLLai,LC.SOHIEU,LC.MALOAI
from PHANCONG PC, LICHBAY LC
where PC.MACB=LC.MACB and PC.MANV=1001and PC.NGAYDI=LC.NGAYDI
GROUP by LC.SOHIEU,LC.MALOAI having COUNT(*)>2

--Cau 33:Voi moi hang SX,cho biet so luong loai may bay ma hang do da SX.Xuat ra hang SX va so luong
select COUNT(*)as SLMayBay,HANGSX
from LOAIMB
GROUP by HANGSX

--Cau 34:Cho biet hang SX, ma loai va so hieu cua may bay da dc su dung nhieu nhat
select LMB.HANGSX,LMB.MALOAI,LB.SOHIEU, COUNT(*) as SLsudung
from LOAIMB LMB ,LICHBAY LB, CHUYENBAY CB
where LMB.MALOAI=LB.MALOAI and CB.MACB=LB.MACB
group by LMB.HANGSX,LMB.MALOAI,LB.SOHIEU
having COUNT (*)>=all (select COUNT(*)
from LOAIMB LMB1 ,LICHBAY LB1, CHUYENBAY CB1
where LMB1.MALOAI=LB1.MALOAI and CB1.MACB=LB1.MACB
group by LMB1.HANGSX,LMB1.MALOAI,LB1.SOHIEU)

--Cau 35:Cho biet ten Nhan Vien dc phan cong di nhieu chyen bay nhat
select NV.TEN ,count (*)as SLDi
from NHANVIEN NV, PHANCONG PC
where NV.MANV= PC.MANV
GROUP by NV.TEN
having COUNT (*)>=all(select count (*)
from NHANVIEN NV1, PHANCONG PC1
where NV1.MANV= PC1.MANV
GROUP by NV1.TEN)

--Cau 36:Cho biet thong tin cua phi cong (ten, dia chi, dien thoai)lai nhieu chuyen bay nhat
select NV.TEN,NV.DCHI,NV.DTHOAI, COUNT (*)
from NHANVIEN NV,PHANCONG PC, CHUYENBAY CB
where NV.LOAINV=1 and PC.MACB=CB.MACB and PC.MANV=NV.MANV
GROUP by NV.TEN,NV.DCHI,NV.DTHOAI
having COUNT (*)>=all(select COUNT (*)
from NHANVIEN NV1,PHANCONG PC1, CHUYENBAY CB1
where NV1.LOAINV=1and PC1.MACB=CB1.MACB and PC1.MANV=NV1.MANV
GROUP by NV1.TEN,NV1.DCHI,NV1.DTHOAI)

--Cau 37:Cho biet san bay SBDEN va so luong chuyen bay cua san bay co it chuyen bay dap xuong nhat
select CB.SBDEN, count (*)as SLDapXuong
from CHUYENBAY CB
GROUP by CB.SBDEN
having COUNT (*)<=all(select count (*)
from CHUYENBAY CB1
GROUP by CB1.SBDEN)

--Cau 38:Cho biet san bay SBDI va so luong chuyen bay cua san bay co nhieu chuyen bay xuat phat nhat
select CB.SOBD, count (*)as SLXuatPhat
from CHUYENBAY CB
GROUP by CB.SOBD
having COUNT (*)>=all(select count (*)
from CHUYENBAY CB1
GROUP by CB1.SOBD)

--Cau 39:Cho biet ten ,dia chi,dien thoai cua khach hang da di tren nhieu chuyen bay nhat
select KH.TEN,KH.DCHI,KH.DTHOAI, COUNT (*)
from KHACHHANG KH,DATCHO DC,CHUYENBAY CB
where KH.MAKH=DC.MAKH and DC.MACB=CB.MACB
group by KH.TEN,KH.DCHI,KH.DTHOAI
having COUNT (*)>=all(select COUNT (*)
from KHACHHANG KH1,DATCHO DC1,CHUYENBAY CB1
where KH1.MAKH=DC1.MAKH and DC1.MACB=CB1.MACB
group by KH1.TEN,KH1.DCHI,KH1.DTHOAI)

--Cau 40:Cho biet ma so,ten va luong cua cac phi cong co kha nang lai nhieu loai may bay nhat
select NV.TEN,NV.MANV,NV.LUONG,COUNT (*) as SLMaybayCoTheLai
from NHANVIEN NV,KHANANG KN
where NV.MANV=KN.MANV
GROUP by NV.TEN,NV.MANV,NV.LUONG
having COUNT (*)>=all(select COUNT (*)
from NHANVIEN NV1,KHANANG KN1
where NV1.MANV=KN1.MANV
GROUP by NV1.TEN,NV1.MANV,NV1.LUONG)

--Cau 41:Cho biet ma NV, luong,ten,cua nhan vien co luong cao nhat
select NV.TEN,NV.MANV,LUONG
from NHANVIEN NV
where NV.LUONG>=all(select NV1.LUONG
from NHANVIEN NV1)

--Cau 42:Cho biet Ten, Dia chi cua cac nhan vien co luong cao nhat trong phi hanh doan (cac nhan vien dc phan cong trong 1 chuyen bay)ma nguoi do tham gia
--Cau 43:Cho biet ma chuyen bay,gio di,gio den cua chuyen bay bay som nhat trong ngay
select CB.MACB,CB.GIODI,CB.GIODEN
from CHUYENBAY CB
where CB.GIODEN <=all(select CB1.GIODEN
from CHUYENBAY CB1)

--Cau 44:Cho biet ma chuyen bay co thoi gian bay dai nhat.Xuat ra ma chuyen bay va thoi gian bay.
select CB.MACB, (CB.GIODEN -CB.GIODI ) AS THOIGIANDI
from CHUYENBAY CB
where CB.GIODEN -CB.GIODI >= ALL ( select CB1.GIODEN - CB1.GIODI
from CHUYENBAY CB1)

--Cau 45:Cho biet ma chuyen bay co thoi gian bay it nhat .Xuat ra ma chuyen bay va thoi gian bay.
select CB.MACB, (CB.GIODEN -CB.GIODI ) AS THOIGIANDI
from CHUYENBAY CB
where CB.GIODEN -CB.GIODI <= ALL ( select CB1.GIODEN - CB1.GIODI
from CHUYENBAY CB1)
--Cau 46:Cho biet ma chuyen bay va ngay di cua nhung chuyen bay bay tren loai may bay B747 nhieu nhat
select CB.MACB,LB.NGAYDI ,count (*)
from CHUYENBAY CB, LICHBAY LB
where CB.MACB=LB.MACB and LB.MALOAI='B747'
group by CB.MACB,LB.NGAYDI
having COUNT (*)>=all(select count (*)
from CHUYENBAY CB1, LICHBAY LB1
where CB1.MACB=LB1.MACB and LB1.MALOAI='B747'
group by CB1.MACB,LB1.NGAYDI)

--Cau 47:Voi moi chuyen bay co tren 3 hanh khach, cho biet ma chuyen bay va so luong nhan vien tren chuyen bay do.
select PC.MACB,COUNT(*)as SLNhanVien
from PHANCONG PC
where exists(select COUNT (*)
from DATCHO DC
where PC.MACB=DC.MACB and PC.NGAYDI=DC.NGAYDI
GROUP by DC.MACB
having COUNT(*)>=3)
group by PC.MACB

--Cau 48:voi moi loai nhan vien co tong luong >60.000 cho biet so luong nhan vien trong tung loai NV do.Xuat ra loai nhan vien, va so luong nhan vien tuong ung
select NV.LOAINV,sum(NV.LUONG)as Luong,count (*)as SLNhanVien
from NHANVIEN NV
GROUP by NV.LOAINV
having sum(NV.LUONG)>60000

--Cau 49:Voi moi chuyen bay co tren 3 nhan vien cho biet ma chuyen bay va so luong khach hang da dat cho tren chuyen bay do
select COUNT (*)as SLKhachHang,DC.MACB
from DATCHO DC
where exists(select COUNT (*)
from PHANCONG PC
where PC.MACB=DC.MACB and DC.NGAYDI=PC.NGAYDI
GROUP by PC.MACB
having COUNT (*)>=3)
group by DC.MACB

--Cau 50:Voi moi loai may bay co nhieu hon 1 chiec,cho biet so luong chuyen bay da dc bo tri bay bang loai may bay do.Xuat ra ma loai va so luong
select COUNT (*) as SLchuyenBay ,LB.MALOAI
from LICHBAY LB
where exists (select COUNT (*)
from LICHBAY LB1
where LB1.MALOAI=LB.MALOAI
GROUP by LB1.MALOAI
having count (*)>1)
GROUP by LB.MALOAI

--Câu 51:Cho biết mã những chuyến bay đã bay tất cả các máy bay của hãng "Boeing"
select LB.MACB
from LICHBAY LB
where not exists (select *
from LOAIMB LMB
where LMB.HANGSX='BOEING' and not exists (select *
from LICHBAY LB1
where LB1.MALOAI=LMB.MALOAI and LB1.MACB=LB.MACB))

--Câu 52:Cho biết mã và tên phi công có khả năng lái tất cả các máy bay của hãng "Airbus"
select NV.MANV,NV.TEN
from NHANVIEN NV
where not exists (select *
from LOAIMB LMB, KHANANG KN
where LMB.HANGSX='AIRBUS'and LMB.MALOAI=KN.MALOAI and not exists (select *
from KHANANG KN1
where KN1.MANV=NV.MANV and KN1.MALOAI=KN.MALOAI))

--Câu 53:Cho biết tên nhân viên (không phải là phi công)được phân công bay vào tất cả các chuyến bay có mã 100
select NV.TEN
from NHANVIEN NV
where NV.LOAINV <>1 and not exists (select *
from PHANCONG PC
where PC.MACB=100 and not exists (select *
from PHANCONG PC1
where PC1.MANV=NV.MANV and PC1.MACB=PC.MACB and PC1.NGAYDI=PC.NGAYDI))

--Câu 54--Cho biết ngày đi nào mà tất cả các loại máy bay của hãng "Boeing" tham gia
select LB.NGAYDI
from LICHBAY LB
where not exists (select *
from LOAIMB LMB
where LMB.HANGSX ='BOEING' and not exists (select *
from LICHBAY LB1
where LB1.MALOAI=LMB.MALOAI and LB1.NGAYDI=LB.NGAYDI))

-- Câu 55:Cho biết loại máy bay của hãng "Boeing" nào có tham gia vào tất cả các ngày đi
select LMB.MALOAI
from LOAIMB LMB
where LMB.HANGSX='BOEING' and not exists (select *
from LICHBAY LB
where not exists (select *
from LICHBAY LB2
where LB2.MALOAI=LMB.MALOAI and LB2.NGAYDI=LB.NGAYDI))

--Câu 56:Cho biết mã và tên các khách hàng có đặt chổ trong tất cả các ngày từ 31/10/2000 và 11/1/2000
select KH.MAKH, KH.TEN
from KHACHHANG KH
where not exists (select *
from DATCHO DC
where DC.NGAYDI between '10/31/2000' and '11/1/2000' and not exists (select *
from DATCHO DC1
where DC1.MAKH=KH.MAKH and DC1.NGAYDI=DC.NGAYDI))

--Câu 57:Cho biết mã và tên phi công không có khả năng lái được tất cả các máy bay của hãng "Airbus"
select NV.TEN,NV.MANV
from NHANVIEN NV
where NV.LOAINV=1 and exists (select *
from LOAIMB LMB
where LMB.HANGSX='AIRBUS' and not exists (select *
from KHANANG KN
where KN.MALOAI=LMB.MALOAI and KN.MANV=NV.MANV))

--Câu 58:Cho biết sân bay nào đã có tất cả các loại máy bay của hãng "Boeing" xuất phát
select CB.SOBD
from CHUYENBAY CB
where not exists (select *
from LOAIMB LMB, LICHBAY LB
where LB.MALOAI=LMB.MALOAI and LMB.HANGSX='BOEING' and not exists (select *
from CHUYENBAY CB1
where CB1.SOBD=CB.SOBD and CB1.MACB=LB.MACB))