Tong Hop [PDF]

  • 0 0 0
  • Gefällt Ihnen dieses papier und der download? Sie können Ihre eigene PDF-Datei in wenigen Minuten kostenlos online veröffentlichen! Anmelden
Datei wird geladen, bitte warten...
Zitiervorschau

GROUP BY --In ra số hóa đơn và trị giá mỗi hóa đơn select SoHDB , sum(SLBan* DonGiaBan) as TriGia from tChiTietHDB inner join tSach on tChiTietHDB.MaSach=tSach.MaSach group by SoHDB /*In ra các số hóa đơn, trị giá mỗi hóa đơn bán ra trong ngày 1/1/2014 và ngày 31/12/2014.*/ select tHoaDonBan.SoHDB, sum(SLBan* DonGiaBan) as TriGia from tChiTietHDB inner join tHoaDonBan on tChiTietHDB.SoHDB=tHoaDonBan.SoHDB inner join tSach on tChiTietHDB.MaSach=tSach.MaSach where NgayBan='4/10/2014' or NgayBan='6/30/2014' group by tHoaDonBan.SoHDB /* In ra các số hóa đơn, trị giá hóa đơn trong tháng 4/2014, sắp xếp theo ngày (tăng dần) và trị giá của hóa đơn (giảm dần).*/ -chọn cái gì : ở phần "select" : số hóa đơn, trị giá hóa đơn -tính cái gì : hàm gộp : "sum":trị giá hóa đơn -từ những bảng nào : thông tin mình cần : tHoaDonBan(lấy ngày), tChiTietHDB(Lấy số lượng), tSach(lấy đơn giá) -điều kiện là gì: 4/2014 -nhóm theo trường nào : SoHDB -có điều kiện cho hàm gộp ko: không select tHoaDonBan.SoHDB,tHoaDonBan.NgayBan, sum(SLBan* DonGiaBan) as TriGia

from tHoaDonBan join tChiTietHDB on tChiTietHDB.SoHDB=tHoaDonBan.SoHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach where month(ngayban)=4 and year(ngayban)=2014 group by tHoaDonBan.SoHDB , tHoaDonBan.NgayBan order by NgayBan asc, TriGia desc /*In ra số hóa đơn, trị giá các hóa đơn do nhân viên có tên “Trần Huy” lập trong ngày “11/8/2014”*/ select tChiTietHDB.SoHDB, sum(SLBan* DonGiaBan) as TriGia from tNhanVien join tHoaDonBan on tNhanVien.MaNV=tHoaDonBan.MaNV join tChiTietHDB on tHoaDonBan.SoHDB=tChiTietHDB.SoHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach where TenNV=N'Trần Huy' and NgayBan='10/23/2020' group by tChiTietHDB.SoHDB /*Có bao nhiêu đầu sách khác nhau được bán ra trong năm 2014.*/ select count(distinct MaSach) from tChiTietHDB join tHoaDonBan on tChiTietHDB.SoHDB=tHoaDonBan.SoHDB where year(ngayban)=2014 /* Cho biết trị giá hóa đơn cao nhất, thấp nhất là bao nhiêu?*/ select max(TriGia) as TriGiaMax

from (select SoHDB , sum(SLBan* DonGiaBan) as TriGia from tChiTietHDB inner join tSach on tChiTietHDB.MaSach=tSach.MaSach group by SoHDB) A /*Trị giá trung bình của tất cả các hóa đơn được bán ra trong năm 2014 là bao nhiêu?*/ select avg(TriGia) from (select tHoaDonBan.SoHDB, sum(SLBan * DonGiaBan) as TriGia from tChiTietHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach join tHoaDonBan on tChiTietHDB.SoHDB=tHoaDonBan.SoHDB where year(NgayBan)=2014 group by tHoaDonBan.SoHDB) A /*Tính doanh thu bán hàng trong năm 2014*/ select sum(SLBan * DonGiaBan) as TriGia from tChiTietHDB join tHoaDonBan on tChiTietHDB.SoHDB=tHoaDonBan.SoHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach where YEAR(NgayBan)=2014

SELECT select MaSach 'Mã sách', TenSach as 'Tên sách', (case when dongiaban>120000 then N'Đơn giá cao' when DonGiaBan>100000 then N'Đơn giá TB' else N'Đơn giá thấp' end) as 'Đánh giá' from tSach

--Đưa ra mã sách, tên sách, mã nxb của nxb có mã 'NXB05' select MaSach, TenSach, MaNXB

from tSach where MaNXB='NXB05' --Đưa ra mã sách, tên sách, mã nxb của nxb có mã 'NXB05' hoặc 'NXB10' select MaSach, TenSach, MaNXB from tSach where MaNXB='NXB05' or MaNXB='NXB10' --Đưa ra mã sách, tên sách của các sách bắt đầu bằng chữ 'Ngay' select MaSach, TenSach from tSach where TenSach like N'Ngày%' --tạo bảng sách gồm các sách của nhà xuất bản có mã NXB10 select MaSach, TenSach, MaNXB into tSachNXB10 from tSach where MaNXB='NXB10' --sắp xếp giá giảm dần select MaSach, TenSach, DonGiaBan, DonGiaNhap from tSach where MaNXB='NXB10' or MaNXB='NXB05' order by DonGiaBan

asc, DonGiaNhap desc

--In ra danh sach cac sach chi lay (Masach, Tensach) do nhà xuất abnr giao dục xuất bản select MaSach, TenSach from tSach inner join tNhaXuatBan on tSach.MaNXB=tNhaXuatBan.MaNXB where TenNXB=N'NXB Giáo Dục' --cách 2: select MaSach, TenSach from tSach ,tNhaXuatBan where tSach.MaNXB=tNhaXuatBan.MaNXB and TenNXB=N'NXB Giáo Dục' --In ra danh sách các sách (MaSach,TenSach) do Nhà xuất bản Giáo Dục có giá từ 100000 đến 150000 select MaSach, TenSach, DonGiaBan from tSach inner join tNhaXuatBan on tSach.MaNXB=tNhaXuatBan.MaNXB where TenNXB=N'NXB Giáo Dục' and DonGiaBan between 100000 and 150000

--bước 1: tạo cơ sở dữ liệu create database QuanLyDiemm --bước 2: sử dụng database use QuanLyDiemm --bước 3: tạo bảng create table SINHVIEN(

MaSV varchar(15) constraint pk_SINHVIEN_MaSV primary key , Hodem nvarchar(30), Ten nvarchar(10), NgaySinh date, GioiTinh bit, NoiSinh nvarchar(100), MaLop char(15) ) drop table SINHVIEN drop table DIEMTHI

create table MONHOC( MaMonHoc varchar(15), TenMonHoc nvarchar(100), SoDVHT int constraint pk_MONHOC_MaMonHoc primary key (MaMonHoc) ) create table DIEMTHI( MaSV varchar(15), MaMonHoc varchar(15), DiemLan1 numeric(2,2), DiemLan2 numeric(2,2) constraint pk_DIEMTHI primary key (MaSV, MaMonHoc) constraint fk_DIEMTHI_MaSV foreign key(MaSV) references SINHVIEN(MaSV),

constraint fk_DIEMTHI_MaMonHoc foreign key(MaMonHoc) references MONHOC(MaMonHoc) )

alter table DIEMTHI add GhiChu nvarchar(30)

--1.Liệt kê danh sách sinh viên, gồm các thông tin sau: Mã sinh viên, Họ sinh viên, Tên sinh viên, Học bổng, Danh sách sv được sắp xếp theo thứ tự Mã sinh viên tăng dần. select MaSV, HoSV, TenSV, HocBong from DSSinhVien order by MaSV asc --2.Danh sách các sinh viên gồm thông tin sau: Mã sinh viên, họ tên sinh viên, Phái, Ngày sinh. Danh sách sẽ được sắp xếp theo thứ tự Nam/Nữ. select MaSV, HoSV, Phai, NgaySinh from DSSinhVien order by Phai asc --3.Thông tin các sinh viên gồm: Họ tên sinh viên, Ngày sinh, Học bổng. Thông tin sẽ được sắp xếp theo thứ tự Ngày sinh tăng dần và Học bổng giảm dần. select HoSV, TenSV, NgaySinh, HocBong from DSSinhVien order by NgaySinh asc, HocBong desc

--4.Danh sách các môn học có tên bắt đầu bằng chữ T, gồm các thông tin: Mã môn, Tên môn, Số tiết. select MaMH, TenMH, SoTiet from DMMonHoc where TenMH like 'T%' --5.Liệt kê danh sách những sinh viên có chữ cái cuối cùng trong tên là I, gồm các thông tin: Họ tên sinh viên, Ngày sinh, Phái. select HoSV, TenSV, NgaySinh, Phai from DSSinhVien where TenSV like '%I' --6.Danh sách những khoa có ký tự thứ hai của tên khoa có chứa chữ N, gồm các thông tin: Mã khoa, Tên khoa. select MaKhoa, TenKhoa from DMKhoa where TenKhoa like '_N%' --7.Liệt kê những sinh viên mà họ có chứa chữ Thị. select HoSV, TenSV from DSSinhVien where HoSV like N'%Thị%' --8.Cho biết danh sách những sinh viên có ký tự đầu tiên của tên nằm trong khoảng từ a đến m, gồm các thông tin: Mã sinh viên, Họ tên sinh viên, Phái, Học bổng. select MaSV, HoSV, TenSV, Phai, HocBong from DSSinhVien where TenSV like '[a-m]%'

--9.Cho biết danh sách những sinh viên mà tên có chứa ký tự nằm trong khoảng từ a đến m, gồm các thông tin: Họ tên sinh viên, Ngày sinh, Nơi sinh, Học bổng. Danh sách được sắp xếp tăng dần theo họ tên sinh viên. select HoSV, TenSV, NgaySinh, NoiSinh, HocBong from DSSinhVien where TenSV between 'a' and 'm' order by TenSV asc --10.Cho biết danh sách các sinh viên có học bổng lớn hơn 100,000, gồm các thông tin: Mã sinh viên, Họ tên sinh viên, Mã khoa, Học bổng. Danh sách sẽ được sắp xếp theo thứ tự Mã khoa giảm dần. select MaSV, HoSV, TenSV, MaKhoa, HocBong from DSSinhVien where HocBong > 100000 order by MaKhoa desc --11.Liệt kê các sinh viên có học bổng từ 150,000 trở lên và sinh ở Hà Nội, gồm các thông tin: Họ tên sinh viên, Mã khoa, Nơi sinh, Học bổng. select HoSV, TenSV, MaKhoa, NoiSinh, HocBong from DSSinhVien where HocBong >= 150000 and NoiSinh=N'Hà Nội' --12.Danh sách các sinh viên của khoa Anh văn và khoa Vật lý, gồm các thông tin: Mã sinh viên, Mã khoa, Phái. select MaSV, DSSinhVien.MaKhoa, Phai

from DMKhoa join DSSinhVien on DMKhoa.MaKhoa=DSSinhVien.MaKhoa where DMKhoa.TenKhoa=N'Anh Van' or DMKhoa.TenKhoa=N'Vật Lý' --13.Cho biết những sinh viên có ngày sinh từ ngày 01/01/1991 đến ngày 05/06/1992 gồm các thông tin: Mã sinh viên, Ngày sinh, Nơi sinh, Học bổng. select MaSV, NgaySinh, NoiSinh, HocBong from DSSinhVien where NgaySinh between '01/01/1991' and '05/06/1992' --14.Danh sách những sinh viên có học bổng từ 80.000 đến 150.000, gồm các thông tin: Mã sinh viên, Ngày sinh, Phái, Mã khoa. select MaSV, NgaySinh, Phai, MaKhoa, HocBong from DSSinhVien where HocBong between 80000 and 150000 --15.Cho biết những môn học có số tiết lớn hơn 30 và nhỏ hơn 45, gồm các thông tin: Mã môn học, Tên môn học, Số tiết. select MaMH, TenMH, SoTiet from DMMonHoc where SoTiet between 30 and 45 --16.Liệt kê những sinh viên nam của khoa Anh văn và khoa tin học, gồm các thông tin: Mã sinh viên, Họ tên sinh viên, tên khoa, Phái. select DSSinhVien.MaSV, DSSinhVien.HoSV, DSSinhVien.TenSV, DMKhoa.TenKhoa, DSSinhVien.Phai

from DSSinhVien join DMKhoa on DSSinhVien.MaKhoa=DMKhoa.MaKhoa where DSSinhVien.Phai='Nam' and (DMKhoa.TenKhoa=N'Anh Văn' or DMKhoa.TenKhoa=N'Tin học') --17.Liệt kê những sinh viên nữ, tên có chứa chữ NC. Truy vấn sử dụng hàm: year, month, day, getdate, case, …. select MaSV, TenSV, datediff from DSSinhVien where TenSV like '%NC%' --18.Danh sách sinh viên có nơi sinh ở Hà Nội và sinh vào tháng 02, gồm các thông tin: Họ sinh viên, Tên sinh viên, Nơi sinh, Ngày sinh. select HoSV, TenSV, NoiSinh, NgaySinh from DSSinhVien where NoiSinh=N'Hà Nội' and month(NgaySinh)=2 --19.Cho biết những sinh viên có tuổi lớn hơn 20, thông tin gồm: Họ tên sinh viên, Tuổi,Học bổng. select HoSV, TenSV, year(getdate())-year(NgaySinh) as Tuổi, HocBong from DSSinhVien where year(getdate())-year(ngaysinh)>20 --20.Danh sách những sinh viên có tuổi từ 20 đến 25, thông tin gồm: Họ tên sinh viên, Tuổi, Tên khoa. select HoSV, TenSV, year(getdate())-year(NgaySinh) as Tuổi, TenKhoa

from DSSinhVien join DMKhoa on DSSinhVien.MaKhoa=DMKhoa.MaKhoa where (year(getdate())-year(ngaysinh)) between 20 and 25 --21.Danh sách sinh viên sinh vào mùa xuân năm 1990, gồm các thông tin: Họ tên sinh viên, Phái, Ngày sinh. (between 1 and 3) select HoSV, TenSV, Phai, NgaySinh from DSSinhVien where month(NgaySinh) between 1 and 3 and year(NgaySinh)=1990 --22.Cho biết thông tin về mức học bổng của các sinh viên, gồm: Mã sinh viên, Phái, Mã khoa, Mức học bổng. Trong đó, mức học bổng sẽ hiển thị là “Học bổng cao” nếu giá trị của field học bổng lớn hơn 500,000 và ngược lại hiển thị là “Mức trung bình” select MaSV, Phai, MaKhoa, HocBong, case when HocBong>500000 then N'Học bổng cao' else N'Mức trung bình' end as HocBong from DSSinhVien --23.Cho biết tổng số sinh viên của toàn trường select count(MaSV) as N'Tổng sinh viên' from DSSinhVien --24.Cho biết tổng sinh viên và tổng sinh viên nữ. --25.Cho biết tổng số sinh viên của từng khoa.

select TenKhoa, count(MaSV) as N'Tổng số sinh viên' from DSSinhVien join DMKhoa on DSSinhVien.MaKhoa=DMKhoa.MaKhoa group by TenKhoa --26.Cho biết số lượng sinh viên học từng môn. select TenMH, count (distinct MaSV) as N'Số sinh viên' from KetQua join DMMonHoc on KetQua.MaMH=DMMonHoc.MaMH group by TenMH --27.Cho biết số lượng môn học mà sinh viên đã học(tức tổng số môn học có torng bảng kq) select count( distinct MaMH) as N'Số môn' from KetQua --28.Cho biết tổng số học bổng của mỗi khoa. select TenKhoa, sum(HocBong) as N'Tổng học bổng' from DSSinhVien join DMKhoa on DSSinhVien.MaKhoa=DMKhoa.MaKhoa group by TenKhoa --29.Cho biết học bổng cao nhất của mỗi khoa. select TenKhoa, max(HocBong) as N'Học Bổng Max' from DSSinhVien join DMKhoa on DSSinhVien.MaKhoa=DMKhoa.MaKhoa group by TenKhoa --30.Cho biết tổng số sinh viên nam và tổng số sinh viên nữ của mỗi khoa. select MaKhoa, sum(case Phai when N'nam' then 1 else 0 end) as 'Tong sinh vien nam',

sum(case Phai when N'nữ' then 1 else 0 end) as 'Tong sinh vien nu' from DSSinhVien group by MaKhoa --31.Cho biết số lượng sinh viên theo từng độ tuổi. select

year(getdate())-year(NgaySinh) as Tuổi, count(MaSV)

as SoSV from DSSinhVien group by year(getdate())-year(NgaySinh) --32.Cho biết những năm sinh nào có 2 sinh viên đang theo học tại trường. select year(NgaySinh) as N'Năm sinh', count(MaSV) as N'Số sinh viên' from DSSinhVien group by year(NgaySinh) having count(MaSV)=2 --33.Cho biết những nơi nào có hơn 2 sinh viên đang theo học tại trường. select NoiSinh, count(MaSV) as N'Số sinh viên' from DSSinhVien group by NoiSinh having count(MaSV)>2 --34.Cho biết những môn nào có trên 3 sinh viên dự thi. select MaMH as N'Môn học', count(MaSV) as 'Số sinh viên' from KetQua group by MaMH

having count(MaSV)>3 --35.Cho biết những sinh viên thi lại trên 2 lần. select MaSV as N'Sinh viên', count(LanThi) as N'Số lần thi lại' from KetQua group by MaSV having count(LanThi)>2 --36.Cho biết những sinh viên nam có điểm trung bình lần 1 trên 7.0 select HoSV, TenSV ,Phai ,LanThi, avg(Diem) as N'Điểm trung bình' from KetQua join DSSinhVien on KetQua.MaSV=DSSinhVien.MaSV where LanThi=1 and Phai=N'nam' group by LanThi, Phai, HoSV, TenSV having avg(Diem)>7.0 --37.Cho biết danh sách các sinh viên rớt trên 2 môn ở lần thi 1. select MaSV, count(MaMH) as N'Số môn rớt' from KetQua where LanThi=1 and Diem=2 --38.Cho biết danh sách những khoa có nhiều hơn 2 sinh viên nam select MaKhoa, count(MaSV) as N'Số sinh viên' from DSSinhVien

where Phai='Nam' group by MaKhoa having count(MaSV)>=2 --39.Cho biết những khoa có 2 sinh đạt học bổng từ 200.000 đến 300.000. select MaKhoa, count(MaSV) as N'Số sinh viên' from DSSinhVien where HocBong between 200000 and 300000 group by MaKhoa having count(MaSV)>=2 --40.Cho biết số lượng sinh viên đậu và số lượng sinh viên rớt của từng môn trong lần thi 1. select KetQua.MaMH, TenMH, count(MaSV) as N'Số sinh viên đậu' from KetQua

join DMMonHoc on KetQua.MaMH=DMMonHoc.MaMH

where LanThi=1 and Diem>=5 group by KetQua.MaMH, TenMH select KetQua.MaMH, TenMH, count(MaSV) as N'Số sinh viên rớt' from KetQua

join DMMonHoc on KetQua.MaMH=DMMonHoc.MaMH

where LanThi=1 and Diem=all(select count(MaSV) from DSSinhVien group by MaKhoa) --45.Cho biết khoa nào có đông nữ nhất. select TenKhoa from DSSinhVien join DMKhoa on DSSinhVien.MaKhoa=DMKhoa.MaKhoa where Phai=N'Nữ' group by TenKhoa having count(TenKhoa)>=all(select count(MaSV) from DSSinhVien where Phai=N'Nữ' group by MaKhoa) --46.Cho biết môn nào có nhiều sinh viên rớt lần 1 nhiều nhất. select MaMH from

KetQua

where LanThi=1 and Diem=all(select count(Diem) from KetQua where LanThi=1 and Diem( select Diem from KetQua join DSSinhVien on KetQua.MaSV=DSSinhVien.MaSV where MaMH='05' and MaKhoa='AV' ) --48.Cho biết sinh viên có nơi sinh cùng với Hải. select HoSV, TenSV, MaSV from DSSinhVien where NoiSinh=(select NoiSinh from DSSinhVien where TenSV=N'Hải') --49.Cho biết những sinh viên nào có học bổng lớn hơn tất cả học bổng của sinh viên thuộc khoa anh văn select HoSV, TenSV, MaSV from DSSinhVien where HocBong>=all(select HocBong from DSSinhVien where MaKhoa='AV') --50.Cho biết những sinh viên có học bổng lớn hơn bất kỳ học bổng của sinh viên học khóa anh văn select HoSV, TenSV, MaSV from DSSinhVien where HocBong>=any(select HocBong from DSSinhVien

where MaKhoa='AV') --51.Cho biết sinh viên nào có điểm thi môn cơ sở dữ liệu lần 2 lớn hơn tất cả điểm thi lần --1 môn cơ sở dữ liệu của những sinh viên khác. select MaSV from KetQua where LanThi=2 and MaMH='01' and Diem>=all(select Diem from KetQua where LanThi=1 and MaMH='01') --52.Cho biết những sinh viên đạt điểm cao nhất trong từng môn. select MaSV,KetQua.MaMH,Diem from KetQua join (select MaMH, max(Diem) as DiemMax from KetQua group by MaMH) A on KetQua.MaMH=A.MaMH and Diem=A.DiemMax --53.Cho biết những khoa không có sinh viên học. select * from DMKhoa where not exists (select distinct MaKhoa from KetQua join DSSinhVien on KetQua.MaSV=DSSinhVien.MaSV) --54.Cho biết sinh viên chưa thi môn cơ sở dữ liệu. select HoSV, TenSV from DSSinhVien where not exists (select distinct*

from KetQua where MaKhoa = '01' ) --55.Cho biết sinh viên nào không thi lần 1 mà có dự thi lần 2. select MaSV from KetQua where LanThi=2 and not exists( select MaSV from KetQua where LanThi=1 ) --56.Cho biết môn nào không có sinh viên khoa anh văn học. select TenMH from DMMonHoc where

not exists(

select MaMH from DSSinhVien join KetQua on DSSinhVien.MaSV=KetQua.MaSV where MaMH='AV') --57.Cho biết những sinh viên khoa anh văn chưa học môn văn phạm. select MaSV from DSSinhVien where MaKhoa='AV' and not exists( select MaSV from KetQua where MaMH='05') --58.Cho biết những sinh viên không rớt môn nào.

select HoSV, TenSV, MaSV from DSSinhVien where MaSV not in( select MaSV from KetQua where Diem0 and not exists( select MaSV from KetQua where Diem= all(select count(masv) as SvHocBong from DSSinhVien where isnull(HocBong, 0) >0 group by MaKhoa) )union (select MaKhoa, count(masv) as SvHocBong

from DSSinhVien where HocBong is not null group by MaKhoa having count(masv) 0 group by MaKhoa) ) --61.Cho biết 3 sinh viên có học nhiều môn nhất. select top 3 MaSV,count(distinct MaMH)'Số môn học' from KetQua group By MaSV having count(distinct MaMH)>=All(select count( distinct MaMH) from KetQua group by MaSV) --62.Cho biết những môn được tất cả các sinh viên theo học. select MaMH from KetQua group By MaMH having count(distinct MaSV)=(select count(MaSV) from DSSinhVien) --63.Cho biết những sinh viên học những môn giống sinh viên có mã số A02 học. select distinct MaSV from KetQua

where exists(select distinct MaMH from KetQua where MaSV='A02') --64.Cho biết những sinh viên học những môn bằng đúng những môn mà sinh viên A02 học. --65.Tạo một bảng mới tên sinhvien-ketqua: gồm: MASV, HoSV, TenSV, SoMonHoc. Sau đó --Thêm dữ liệu vào bảng này dựa vào dữ liệu đã có. create table SINHVIEN_KETQUA( MaSV varchar(15), HoSV nvarchar(15), TenSV nvarchar(10), SoMonHoc int ) insert into SINHVIEN_KETQUA select DSSinhVien.MaSV, HoSV, TenSV, count(distinct MaMH) from DSSinhVien join KetQua on DSSinhVien.MaSV=KetQua.MaSV group by DSSinhVien.MaSV, HoSV, TenSV --66.Thêm vào bảng khoa cột Siso, cập nhật sĩ số vào khoa từ dữ liệu sinh viên. alter table dmkhoa add Siso tinyint

update DMKhoa set Siso= SoSV from (select MaKhoa, count(masv) as SoSV

from DSSinhVien where HocBong is not null group by MaKhoa) A where dmkhoa.MaKhoa = A.MaKhoa

--67.Tăng thêm 1 điểm cho các sinh viên vớt lần 2. Nhưng chỉ tăng tối đa là 5 điểm update KetQua set Diem=Diem+1 where LanThi=2 and Diem+1=6.5) --69.Thiết lập học bổng bằng 0 cho những sinh viên thi hai môn rớt ở lần 1 update DSSinhVien set HocBong=0 where MaSV in (select MaSV from KetQua

where LanThi=1 and Diem=5 --73.Danh sách sinh viên học môn văn phạm và môn cơ sở dữ liệu select * from DSSinhVien where MaSV in ( select distinct MaSV from KetQua where MaMH='01' or MaMH='05')

--74.Trong mỗi sinh viên cho biết môn có điểm thi lớn nhất. Thông tin gồm: mã sinh viên, tên sinh viên, tên môn, điểm. select * from(select DSSinhVien.MaSV, TenSV, TenMH, max(Diem) from DSSinhVien join KetQua on DSSinhVien.MaSV = KetQua.MaSV join DMMonHoc on KetQua.MaMH = DMMonHoc.MaMH group by DSSinhVien.MaSV, TenSV, TenMH, Diem)

--75.Danh sách sinh viên: Không rớt lần 1 hoặc ,Không học môn văn phạm select * from DSSinhVien where MaSV in ( select MaSV from KetQua where LanThi=1 and Diem>=5 or MaMH not like '05') --76.Danh sách những sinh viên khoa có 2 sinh viên nữ trở lên select * from DSSinhVien where MaKhoa in ( select MaKhoa from DSSinhVien where Phai=N'nữ' group by MaKhoa having count(distinct MaSV)>=2)

/*update tên bảng set cột = giá trị delete from tên bảng where điều kiện insert tên bảng(liệt kê trường) values (giá trị tương ứng)*/

--Giảm giá sách 10% cho các sách của NXB Giáo dục update tSach set DonGiaBan=tSach.DonGiaBan*0.9 from tSach join tNhaXuatBan on tNhaXuatBan.MaNXB=tSach.MaNXB where TenNXB like N'%Giáo Dục'

--Thêm trường tổng tiền cho bảng tHoaDonBan rồi cập nhật tổng tiền của hóa đơn cho trường này alter table thoadonban add TongTien money update tHoaDonBan set TongTien=(select sum(SLBan*DonGiaBan) from tChiTietHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach where tHoaDonBan.SoHDB=tChiTietHDB.SoHDB group by SoHDB) select sohdb, sum(SLBan*DonGiaBan) from tChiTietHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach group by SoHDB

--Giảm 10% trên tổng hóa đơn cho các hóa đơn có trị giá trên 500000 trong tháng 9 update tHoaDonBan set TongTien=tHoaDonBan.TongTien*0.9 from tHoaDonBan where TongTien>500000 and month(NgayBan)=3

--Xóa những hóa đơn do nhân viên 'Trần Huy' lập(lưu ý: xóa chi tiết hóa đơn trước) delete from tChiTietHDB from tHoaDonBan join tNhanVien on tNhanVien.MaNV=tHoaDonBan.MaNV where tHoaDonBan.SoHDB=tChiTietHDB.SoHDB and TenNV=N'Trần Huy' delete from tHoaDonBan from tNhanVien where tNhanVien.MaNV=tHoaDonBan.MaNV and TenNV=N'Trần Huy'

--Đổi tên 'NXB Thăng Long' thành 'NXB Văn học' update tNhaXuatBan set TenNXB=N'NXB Văn Học' where TenNXB=N'NXB Thăng Long' --Tính tổng số lượng sách nhập trong năm 2014 select sum(SLNhap) from tChiTietHDN join tHoaDonNhap on tChiTietHDN.SoHDN=tHoaDonNhap.SoHDN where year(NgayNhap)=2014 --Tính tổng số lượng sách bán trong năm 2014 select sum(SLBan)

from tChiTietHDB join tHoaDonBan on tChiTietHDB.SoHDB=tHoaDonBan.SoHDB where year(NgayBan)=2014 --Tính tổng tiền đã bán trong năm 2014 select sum(SLBan * DonGiaBan) as TriGia from tHoaDonBan join tChiTietHDB on tHoaDonBan.SoHDB = tChiTietHDB.SoHDB join tSach on tChiTietHDB.MaSach = tSach.MaSach where year(ngayban) = 2014 --Tính tổng tiền đã nhập trong năm 2014 select sum(SLNhap * DonGiaNhap) as TriGia from tHoaDonNhap join tChiTietHDN on tHoaDonNhap.SoHDN = tChiTietHDN.SoHDN join tSach on tChiTietHDN.MaSach = tSach.MaSach where year(NgayNhap) = 2014 --Tìm các số hóa đơn đã mua sách "Cấu trúc dữ liệu và giải thuật" select SoHDB from tChiTietHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach where Tensach like N'Cấu trúc dữ liệu và giải thuật' --Tìm các số hóa đơn đã mua sản phẩm có mã số "S01" hoặc "S02", mỗi sản phẩm mua với số lượng từ 10 đến 20 select SoHDB from tChiTietHDB

where (MaSach='S01' or MaSach='S02') and SLBan between 10 and 20 --Tìm số hóa đơn đã mua tất cả các sách của NXB Giáo dục select SoHDB, count(tChiTietHDB.masach) from tChiTietHDB join tSach on tChiTietHDB.MaSach=tSach.MaSach join tNhaXuatBan on tNhaXuatBan.MaNXB=tSach.MaNXB where TenNXB=N'NXB Giáo Dục' group by SoHDB having count(tChiTietHDB.masach) =(select count(Masach) from tSach join tNhaXuatBan on tNhaXuatBan.MaNXB=tSach.MaNXB where TenNXB=N'NXB Giáo Dục')

/*1.Cập nhật điểm trung bình cho học sinh biết DTB=((Toán+Văn)*2+Lý +Hóa)/6*/ alter table DIEM add DTB float

update DIEM set DTB = A.DTB from (select MAHS, SUM(((TOAN+VAN)*2+LY+HOA)/6) as DTB from DIEM group by MAHS) A join DIEM on A.MAHS = DIEM.MAHS

/* 2. Cập nhật điểm thấp nhất cho học sinh biết DTN là điểm thấp nhất trong 4 điểm TOAN, LY, HOA, VAN */ alter table DIEM add DTN float

update DIEM set DTN = case when TOAN= ALL(

SELECT count(hd.SOHD) FROM ( SELECT TOP 10 kh.MAKH FROM KHACHHANG kh WHERE kh.MAKH is not null ORDER BY kh.DOANHSO DESC

) DS, HOADON hd WHERE DS.MAKH = hd.MAKH GROUP BY hd.MAKH )