SQL bt

Bai 2: Dia ly VN:
[codes=sql]
— Câu 1-a)
select TEN_T_TP, DS
from tinh_tp
where ds>=5000
— Câu 1-b)
select TEN_T_TP, DS
from tinh_tp
where ds>=6000
— Câu 2-a)
select ten_t_tp  , DS
FROM TINH_TP
WHERE MIEN = N’Bắc’
— Câu 2-b)
select ten_t_tp  , DS
FROM TINH_TP
WHERE MIEN =’Nam’
— Câu 3)
select ten_t_tp, nuoc
from tinh_tp, biengioi
where biengioi.ma_t_tp = tinh_tp.ma_t_tp and  mien = ‘Nam’
— Câu 4)
select sum(dt)/count (ma_t_tp) as [Diện tích TB] from tinh_tp
— Câu 5)
select ten_t_tp, ds
from tinh_tp
where dt > 7000
— Câu 6)
select ten_t_tp, ds
from tinh_tp
where mien = N’Bắc’
— Câu 7)
select NUOC,TEN_T_TP
FROM TINH_TP, BIENGIOI
WHERE TINH_TP.MA_T_TP = BIENGIOI.MA_T_TP AND MIEN =’NAM’
— CÂU 8)
SELECT AVG(DT) FROM TINH_TP AS [DIỆN TÍCH TB]
— Câu 9)
select TEN_T_TP,DS*1/DT*1 as [Mật độ Dân số] from TINH_TP
— CÂU 10)
SELECT TEN_T_TP
FROM LANGGIENG, TINH_TP
WHERE LANGGIENG.MA_T_TP = TINH_TP.MA_T_TP AND LG = ‘LA’
— CÂU 11)
SELECT COUNT(NUOC) AS [SL T_TP GIAP CPC] FROM BIENGIOI WHERE NUOC = ‘CPC’
— CÂU 12)
SELECT TEN_T_TP FROM TINH_TP
WHERE DT = (SELECT MAX(DT) FROM TINH_TP)
— CÂU 13)
SELECT TEN_T_TP FROM TINH_TP
WHERE DS/DT=
(select MAX(DS/DT) from TINH_TP)
–CÂU 14)
select TEN_T_TP from TINH_TP T,BIENGIOI B
where T.MA_T_TP=B.MA_T_TP
group by TEN_T_TP
having count (*)=2
–CÂU 15)
SELECT MIEN,TEN_T_TP FROM TINH_TP GROUP BY MIEN,TEN_T_TP
ORDER BY MIEN
–câu 16)
create view So_LG_Nhieu_Nhat
as
  select top 1 MA_T_TP, count(*) as [SOLANGGIENG]
  from LANGGIENG group by MA_T_TP
  order by [SOLANGGIENG] DESC

select T.MA_T_TP,TEN_T_TP,count(*) as [So_LG_Nhieu_nhat]
from TINH_TP T,LANGGIENG L
where T.MA_T_TP =L.MA_T_TP
group by T.MA_T_TP,TEN_T_TP
having count(*) IN (select SOLANGGIENG from So_LG_Nhieu_Nhat)

–Câu 16, cách 2: Ko cần tạo View.
select T.MA_T_TP,TEN_T_TP,count(*) as [So_LG_Nhieu_nhat]
from TINH_TP T,LANGGIENG L
where T.MA_T_TP =L.MA_T_TP
group by T.MA_T_TP,TEN_T_TP
having count(*) >= ALL
(select count(*)
from TINH_TP T1,LANGGIENG L1
where T1.MA_T_TP =L1.MA_T_TP
group by T1.MA_T_TP)
–Câu 17)
SELECT TEN_T_TP, DT
FROM TINH_TP
WHERE
DT < (SELECT AVG(DT) FROM TINH_TP )
–Câu 18)
SELECT T.MA_T_TP, TEN_T_TP, MIEN
FROM TINH_TP T, LANGGIENG L
WHERE T.MA_T_TP = L.MA_T_TP
AND MIEN<>‘TRUNG’
–Câu 19)
SELECT DISTINCT TEN_T_TP
FROM TINH_TP T1, LANGGIENG L
WHERE T1.MA_T_TP = L.MA_T_TP
    AND T1.DT>=ALL
    (SELECT T2.DT FROM TINH_TP T2, LANGGIENG L2
     WHERE T2.MA_T_TP = L2.MA_T_TP )
–Câu 20)
SELECT TEN_T_TP, L1.LG AS [TINH 1],
  L2.LG AS [TINH 2], L3.LG AS [TINH 3]
FROM TINH_TP T, LANGGIENG L1,LANGGIENG L2,LANGGIENG L3
WHERE T.TEN_T_TP =N’TP. Hồ Chí Minh’ AND
  T.MA_T_TP = L1.MA_T_TP AND
  L1.LG = L2.MA_T_TP AND
  L2.LG = L3.MA_T_TP AND
  L3.LG <> L2.MA_T_TP AND
  L2.LG <> L1.MA_T_TP AND
  L3.LG <> (SELECT MA_T_TP FROM TINH_TP
        WHERE TEN_T_TP = N’TP. Hồ Chí Minh’)
  AND
  L2.LG <> (SELECT MA_T_TP FROM TINH_TP
        WHERE TEN_T_TP = N’TP. Hồ Chí Minh’)
[/codes]

Bai 3:

[codes=sql]
— Câu 2)
SELECT NGK.TENNGK AS [TÊN NƯỚC GIẢI KHÁT], LOAINGK.TENLOAINGK AS [LỌAI NƯỚC GIẢI KHÁT]
FROM NGK, LOAINGK
WHERE NGK.MALOAINGK = LOAINGK.MALOAINGK
— CÂU 3)
SELECT NHACC.TENNCC AS[NHÀ CUNG CẤP],
     NHACC.DIACHINCC AS[ĐỊA CHỈ],
     NHACC.DTNCC AS[ĐIỆN THỌAI]
FROM NHACC
WHERE DIACHINCC LIKE ‘%TP.HCM’
— CÂU 4)
SELECT *
FROM HOADON
WHERE MONTH(NGAYLAPHOADON)=5
      AND YEAR(NGAYLAPHOADON)=2010
–CÂU 5)
SELECT DISTINCT NHACC.TENNCC
FROM NHACC, NGK, LOAINGK
WHERE NHACC.MANCC = LOAINGK.MANCC
    AND LOAINGK.MALOAINGK = NGK.MALOAINGK
    AND NGK.TENNGK = N’Coca Cola’
–CÂU 6
select  top 1 CC.TenNCC
from NGK N, LoaiNGK L, NhaCC CC
where N.MaLoaiNGK = L.MaLoaiNGK
  and L.MaNCC = CC.MaNCC
–câu 7
select TenNCC
from NhaCC
where TenNCC not in(SELECT DISTINCT NHACC.TENNCC
       FROM NHACC, NGK, LOAINGK
       WHERE NHACC.MANCC = LOAINGK.MANCC
    AND LOAINGK.MALOAINGK = NGK.MALOAINGK
    AND NGK.MaNGK like ‘PS%’)
–câu 8
select distinct TenNGK
from NGK
where TenNGK not in (SELECT DISTINCT NGK.TenNGK
       FROM NGK, CT_HoaDon
       WHERE NGK.MaNGK = CT_HoaDon.MaNGK)
— câu 9
select N.TenNGK, sum(C.SLKHmua) as [Số Lượng KH Mua]
from NGK N, CT_hoadon C
where N.MaNGK = C.MaNGK
group by N.TenNGK
having sum(C.SLKHmua) >=all
(select sum(C1.SLKHmua)
from CT_hoadon C1
group by MaNGK)
— câu 10
select N.TenNGK, sum(C.SLDat) as [Số lượng đặt ít nhất]
from NGK N, CT_DDH C
where N.MaNGK = C.MaNGK
group by N.TenNGK
having sum(C.SLDat) <= all(select sum (SLDat)
                           from CT_DDH c1, NGK  n1
                           where C1.MaNGK = N1.MaNGK group by TenNGK)
–câu 11
select distinct SoDDH, sum(C1.SLDat) as[So luong dat]
from CT_DDH C1
group by C1.SoDDH
having sum(SLDat)>=all(select sum(SLDat)
            from CT_DDH C
            group by C.SoDDH)
–câu 12
select distinct TenNGK as [Ten Nuoc Giai Khat]
from NGK N, DDH D, CT_DDH CT
where D.SoDDH = CT.SoDDH
  and CT.MaNGK = N.MaNGK
  and month (D.NgayDH) <> 1
  and year (D.NgayDH) = 2010
–câu 13
select distinct TenNGK as [Ten Nuoc Giai Khat]
from NGK N, PhieuGH P, CT_PGH CT
where P.SoPGH = CT.SoPGH
  and CT.MaNGK = N.MaNGK
  and month (P.NgayGH) <>6
  and year (P.NgayGH) = 2010
–câu 14
select count(*) as [Số lượng NGK của cửa hàng]
from NGK
–câu 15
select count(*) as [Số lượng loại NGK của cửa hàng]
from LOAINGK
–câu 16
select K.MaKH, K.TenKH
from KH K, HOADON H, CT_HOADON C
where K.MaKH=H.MaKH and H.SoHD=C.SoHD
group by K.MaKH, K.TenKH
having sum(C.SLKHMua) >= all(select sum(C.SLKHMua)
            from HOADON H, CT_HOADON C
            where H.SoHD=C.SoHD
            group by H.MaKH)
–câu 17
select sum(C.SLKHMua*C.DGBan) as [Tổng doanh thu trong 2010]
from HOADON H, CT_HOADON C
where H.SoHD=C.SoHD and year(H.NgaylapHoaDon)=2010
–câu 18
select top 5 L.TenLoaiNGK, sum(C.SLKHMua) as [Số lượng bán được]
from NGK N, LOAINGK L, HOADON H, CT_HOADON C
where N.MaLoaiNGK=L.MaLoaiNGK and N.MaNGK=C.MaNGK and H.SoHD=C.SoHD
  and month(H.NgaylapHoaDon)=5 and year(H.NgaylapHoaDon)=2010
group by L.TenLoaiNGK
–câu 19
select N.MaNGK as [Mã NGK bán trong 5/2010], N.TenNGK [Tên NGK], C.SLKHMua as [Số lýợng], (C.DGBan*C.SLKHMua) as [Doanh thu]
from NGK N, LOAINGK L, HOADON H, CT_HOADON C
where N.MaLoaiNGK=L.MaLoaiNGK and N.MaNGK=C.MaNGK and H.SoHD=C.SoHD
  and month(H.NgaylapHoaDon)=5 and year(H.NgaylapHoaDon)=2010
–câu 20
select N.MaNGK, N.TenNGK, L.TenLoaiNGK, count(*) as SoLuongNguoiMua
from NGK N, LOAINGK L, HOADON H, CT_HOADON C
where N.MaLoaiNGK=L.MaLoaiNGK and N.MaNGK=C.MaNGK and H.SoHD=C.SoHD
group by N.MaNGK, N.TenNGK, L.TenLoaiNGK
having count(*) >=all(select count(*)
          from NGK N, CT_HOADON C
          where N.MaNGK=C.MaNGK
          group by N.MaNGK)
–câu 21
select top 1 convert(varchar(10),NgayGH,103) as[Ngày nhập]
from PHIEUGH
–câu 22
select count(*) as[Số lần mua hàng]
from HOADON
where MaKH=’KH01′
–câu 23
select soHD,MaNGK,DGBan,SLKHMua,(DGBan*SLKHMua) as [Thành tiền]
from CT_HOADON
–câu 24
select K.MaKH, K.TenKH,H.SoHD,convert(varchar(10),H.NgaylapHoaDon,103),sum(C.DGBan*C.SLKHMua) as [Tổng trị giá]
from KH K, HOADON H, CT_HOADON C
where K.MaKH=H.MaKH and H.SoHD=C.SoHD
group by K.MaKH, K.TenKH,H.SoHD,H.NgaylapHoaDon
order by  (H.NgayLapHoaDon) asc,(sum(C.DGBan*C.SLKHMua)) desc
–câu 25
select H.SoHD,sum(C.DGBan*C.SLK
HMua) as [Tổng trị giá]
from HOADON H, CT_HOADON C
where H.SoHD=C.SoHD
group by H.SoHD
having sum(C.DGBan*C.SLKHMua)> all(select avg(C.DGBan*C.SLKHMua)
                  from HOADON H, CT_HOADON C
                  where H.SoHD=C.SoHD
                    and convert(varchar(10),H.NgaylapHoaDon,103)=’18/06/2010′
                  group by C.SoHD)
–câu 26
select month(H.ngaylaphoadon) as Thang,sum(C.SLKHMua) as SLgNGK,l.tenloaingk
from HOADON H, CT_HOADON C,loaingk l,ngk n
where H.SoHD=C.SoHD and  n.maloaingk=l.maloaingk and c.mangk=n.mangk
group by month(H.ngaylaphoadon),l.tenloaingk
–câu 27
select MaNGK, TenNGK
from NGK
where MaNGK not in (select C.MaNGK
          from HOADON H, CT_HOADON C
          where H.SoHD=C.SoHD
            and month(H.NgaylapHoaDon)=9 and year(H.NgaylapHoaDon)=2010)
–câu 28
select MaKH, TenKH
from KH
where MaKH in (select H.MaKH
        from HOADON H, CT_HOADON C, KH K
        where H.SoHD=C.SoHD
            and K.MaKH = H.MaKH
          and month(H.NgaylapHoaDon)=9 and year(H.NgaylapHoaDon)=2010)
   and DCKH like ‘%Tp.HCM’
–câu 29
select N.MaNGK, N.TenNGK, C.SLKHMua
from NGK N, HOADON H, CT_HOADON C
where N.MaNGK=C.MaNGK and H.SoHD=C.SoHD
  and month(H.NgaylapHoaDon)=9 and year(H.NgaylapHoaDon)=2010
–câu 30
select distinct K.*
from KH K, HOADON H, CT_HOADON C
where K.MaKH=H.MaKH and H.SoHD=C.SoHD
–câu 31
select K.*, sum(SoTienTra) as [Số tiền nợ]
from KH K, HOADON H, PHIEUTRANO P
where K.MaKH=H.MaKH and H.SoHD=P.SoHD
  and year(H.NgaylapHoaDon)=2010
group by K.MaKH, K.TenKH, K.DCKH, K.DTKH
having sum(SoTienTra) >=all(select sum(SoTienTra)
              from KH K, HOADON H, PHIEUTRANO P
              where K.MaKH=H.MaKH and H.SoHD=P.SoHD
                and year(H.NgaylapHoaDon)=2010
              group by K.MaKH)
–câu 32
select count(*) as SoHDChuaThanhToan
from HOADON
where SoHD in (select SoHD
      from PHIEUTRANO)
–câu 33
select H.SoHD, K.TenKH
from HOADON H, KH K
where H.MaKH = K.MaKH
  and SoHD not in (select SoHD
      from PHIEUTRANO)
–câu 34
select N.MaNCC, N.TenNCC, sum(C.SLDat) as SLDat
from NHACC N, DDH D, CT_DDH C
where N.MaNCC=D.MaNCC and D.SoDDH=C.SoDDH
  and year(D.NgayDH)=2010
group by N.MaNCC, N.TenNCC
having sum(C.SLDat) >=all(select sum(C.SLDat)
            from NHACC N, DDH D, CT_DDH C
            where N.MaNCC=D.MaNCC and D.SoDDH=C.SoDDH
              and year(D.NgayDH)=2010
            group by N.MaNCC)
–câu 35
select distinct K.*, sum(DGBan*SLKHMua) as [Thành Tiền]
from KH K, HOADON H, CT_HOADON C
where K.MaKH=H.MaKH and H.SoHD=C.SoHD
  and year(H.NgaylapHoaDon)=2010
group by K.MaKH, K.TenKH, K.DCKH, K.DTKH
order by (sum(DGban*SLKHMua)) desc
–câu 36
create view View36
as
  select N.MaNGK, N.TenNGK, N.QuyCach, C.SLKHMua
  from NGK N, CT_HoaDon C
  where C.MaNGK = N.MaNGK
–câu 37
create view View37 as
       (select top 1 *
        from View36
        order by SLKHMua desc)
———————————-
select *from View37
— Câu 38
create view View38 as
(select K.*
from KH K, HOADON H, CT_HOADON C
where K.MaKH=H.MaKH and H.SoHD=C.SoHD
  and convert(varchar(10),H.NgaylapHoaDon,103)=’20/09/2010′)
———————————-
select *
from view38
–Câu 39
create procedure DanhMucNGK(@SoHD varchar(5)) as
(select *
from View36 V, CT_HOADON C
where V.MaNGK=C.MaNGK
  and C.SoHD=@SoHD)
——————————–
DanhMucNGK ‘HD01’
–Câu 40
create procedure NgayBanNGK(@Ngay varchar(10)) as
(select N.MaNGK, N.TenNGK, N.Quycach as DVT, sum(C.SLKHMua) as SoLuongBan
from NGK N, HOADON H, CT_HOADON C
where N.MaNGK=C.MaNGK and C.SoHD=H.SoHD
  and convert(varchar(10),H.NgaylapHoaDon,103)=@Ngay
group by N.MaNGK, N.TenNGK, N.Quycach)
–// check
NgayBanNGK ’20/09/2010′
–Câu 41
create trigger Cau41 on CT_HOADON
for Insert, Update
as
declare @SL bigint, @DG bigint
If (SELECT count(*) FROM inserted)>0
begin
  set @SL=(select SLKHMua from Inserted)
  set @DG=(select DGBan from Inserted)
  if (@SL<=0)or(@DG<=0)
  begin
    RAISERROR (‘Du lieu nhap khong hop le’, 16, 10)
      –ROLLBACK TRANSACTION
  end
  else
    begin
      RAISERROR (‘Du lieu nhap thanh cong’, 16, 10)
    end
end

[/codes]

Quan ly Tour

[codes=sql]
–Câu 1
select *
from NhanVienHDDL
— Câu 2
select *
from tour
— Câu 3
select *
from tour
where songay>=3
— Câu 4
select *
from DiemTQ
— Câu 5
select distinct T.Matour, T.TenTour, T.soNgay, T.soDem
from DiemDungChan D, LoTrinh_tour L, Tour T
where   D.MaDDC = L.MaNoiDen
    and L.MaTour = T.MaTour
    and D.ThanhPho = ‘Nha Trang’
— Câu 6
select D.MaDoan,D.HoTen, D.Phai, D.Ngaysinh, D.diaChi, D.DienThoai, count(D.MaDoan) as [Số Chuyến đi Nhiều Nhất]
from Doan D, HopDong H
where D.MaDoan = H.MaDoan
group by D.MaDoan,D.HoTen, D.Phai, D.Ngaysinh, D.diaChi, D.DienThoai
having count(D.MaDoan) >=all
            (select count (D.MaDoan)
             from Doan D, HopDong H
             where D.MaDoan = H.MaDoan
             group by D.MaDoan)
— Câu 7
select D.MaDoan, H.SoNguoidi as [Số Lượng Khách]
from Doan D, hopDong H
where D.MaDoan = H.MaDoan
    and D.HoTen = N’Nguyễn Văn A’
    and year(H.NgayLapHD) = 2010
— Câu 8
select count(*) as [Số chuyến đi đến Nha Trang]
from Chuyen
where TenChuyen like ‘%Nha Trang’
— Câu 9
select N.MaNVHDDL, N.TenNV, convert(varchar(10),N.NgaySinhNV,103) as [Ngày Sinh], N.PhaiNV, N.diaChiNv, N.dienThoaiNv
from NhanVienHDDL N, Chuyen C, Tour T
where T.Matour = C.Matour
   and C.MaNVHDDL = N.MaNVHDDL
   and (C.NgayDiCuaChuyen + T.SoNgay) > ‘6/1/2010’
   and C.NgayDicuaChuyen <= '6/1/2010'
group by N.MaNVHDDL, N.TenNV, convert(varchar(10),N.NgaySinhNV,103), N.PhaiNV, N.diaChiNv, N.dienThoaiNv
–Câu 10
select N.MaNVHDDL, N.TenNV, convert(varchar(10),N.NgaySinhNV,103) [Ngày Sinh],N.PhaiNV, N.diaChiNv, N.dienThoaiNv
from NhanVienHDDL N, Chuyen C, Tour T
where N.MaNVHDDL not in (select N.MaNVHDDL
             from NhanVienHDDL N, Chuyen C, Tour T
             where T.Matour = C.Matour
                and C.MaNVHDDL = N.MaNVHDDL
                and (C.NgayDiCuaChuyen + T.SoNgay) > ‘6/1/2010’
                and C.NgayDicuaChuyen <= '6/1/2010'
            group by N.MaNVHDDL)
group by N.MaNVHDDL, N.TenNV, convert(varchar(10),N.NgaySinhNV,103),N.PhaiNV, N.diaChiNv, N.dienThoaiNv
–Câu 11
–Câu 12
[/codes]