Bài tập quản lí sinh viên SQL Access

Thảo luận trong 'Hỏi đáp - Thủ thuật tin học' bắt đầu bởi bibica, 15/11/16.

  1. bibica

    bibica Moderator

    Bài viết:
    385
    Đã được thích:
    1
    QUẢN LÍ SINH VIÊN

    /*=====================Create DataBase======================*/

    usemaster
    go
    ifexists(selectnamefrom sysdatabases wherename='QuanLyDiemSV')
    dropDatabase QuanLyDiemSV
    go
    CreateDatabase QuanLyDiemSV
    go

    use QuanLyDiemSV
    go


    /*=============DANH MUC KHOA==============*/


    Createtable DMKhoa
    (
    MaKhoa char(2)primarykey,
    TenKhoa nvarchar(30)notnull,
    )


    /*==============DANH MUC SINH VIEN============*/


    Createtable DMSV
    (
    MaSV char(3)notnullprimarykey,
    HoSV nvarchar(15)notnull,
    TenSV nvarchar(7)notnull,
    Phai nchar(7),
    NgaySinh datetimenotnull,
    NoiSinh nvarchar(20),
    MaKhoa char(2),
    HocBong float,

    )




    /*===================MON HOC========================*/



    createtable DMMH
    (
    MaMH char(2)notnull,
    TenMH nvarchar(25)notnull,
    SoTiet tinyint
    Constraint DMMH_MaMH_pk primarykey(MaMH)
    )



    /*=====================KET QUA===================*/

    Createtable KetQua
    (
    MaSV char(3)notnull,
    MaMH char(2)notnull,
    LanThi tinyint,
    Diem decimal(4,2),
    Constraint KetQua_MaSV_MaMH_LanThi_pk primarykey(MaSV,MaMH,LanThi)
    )

    /*==========================TAO KHOA NGOAI==============================*/
    Altertable dmsv
    addConstraint DMKhoa_MaKhoa_fk foreignkey(MaKhoa)
    References DMKhoa (MaKhoa)
    Altertable KetQua
    addconstraint KetQua_MaSV_fk foreignkey(MaSV)references DMSV (MaSV),
    constraint DMMH_MaMH_fk foreignkey(MaMH)references DMMH (MaMH)


    /*==================NHAP DU LIEU====================*/

    /*==============NHAP DU LIEU DMMH=============*/
    Insertinto DMMH(MaMH,TenMH,SoTiet)
    values('01',N'Cơ Sở Dữ Liệu',45)
    Insertinto DMMH(MaMH,TenMH,SoTiet)
    values('02',N'Trí Tuệ Nhân Tạo',45)
    Insertinto DMMH(MaMH,TenMH,SoTiet)
    values('03',N'Truyền Tin',45)
    Insertinto DMMH(MaMH,TenMH,SoTiet)
    values('04',N'Đồ Họa',60)
    Insertinto DMMH(MaMH,TenMH,SoTiet)
    values('05',N'Văn Phạm',60)

    /*==============NHAP DU LIEU DMKHOA=============*/
    Insertinto DMKhoa(MaKhoa,TenKhoa)
    values('AV',N'Anh Văn')
    Insertinto DMKhoa(MaKhoa,TenKhoa)
    values('TH',N'Tin Học')
    Insertinto DMKhoa(MaKhoa,TenKhoa)
    values('TR',N'Triết')
    Insertinto DMKhoa(MaKhoa,TenKhoa)
    values('VL',N'Vật Lý')


    /*==============NHAP DU LIEU DMSV=============*/


    SETDATEFORMAT DMY
    GO

    Insertinto DMSV
    values('A01',N'Nguyễn Thị',N'Hải',N'Nữ','23/02/1990',N'Hà Nội','TH',130000)
    Insertinto DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
    values('A02',N'Trần Văn',N'Chính',N'Nam','24/12/1992',N'Bình Định','VL',150000)
    Insertinto DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
    values('A03',N'Lê Thu Bạch',N'Yến',N'Nữ','21/02/1990',N'TP Hồ Chí Minh','TH',170000)
    Insertinto DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
    values('A04',N'Trần Anh',N'Tuấn',N'Nam','20/12/1990',N'Hà Nội','AV',80000)
    Insertinto DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
    values('B01',N'Trần Thanh',N'Mai',N'Nữ','12/08/1991',N'Hải Phòng','TR',0)
    Insertinto DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
    values('B02',N'Trần Thị Thu',N'Thủy',N'Nữ','02/01/1991',N'TP Hồ Chí Minh','AV',0)

    /*==============NHAP DU LIEU BANG KET QUA=============*/

    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A01','01',1,3)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A01','01',2,6)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A01','02',2,6)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A01','03',1,5)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A02','01',1,4.5)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A02','01',2,7)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A02','03',1,10)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A02','05',1,9)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A03','01',1,2)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A03','01',2,5)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A03','03',1,2.5)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A03','03',2,4)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('A04','05',2,10)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('B01','01',1,7)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('B01','03',1,2.5)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('B01','03',2,5)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)

    values('B02','02',1,6)
    Insertinto KetQua(MaSV,MaMH,LanThi,Diem)
    values('B02','04',1,10)


    /*===============CAP NHAT THONG TIN=================*/

    --câu 2--
    update dmmh
    set sotiet=45
    where mamh='05'
    --câu 3,4---
    update dmsv
    set tensv=N'Kỳ',phai ='Nam'
    where masv='b01'
    -------câu 5-----
    update dmsv
    set ngaysinh='05/07/1990'
    where masv='b02'
    ----------câu 6----------
    update dmsv
    set hocbong=hocbong+100000
    where makhoa='Av'
    -------câu 7----------
    deletefrom ketqua
    where lanthi=2 and diem<5
    -------câu 8---------
    --delete from dmsv
    --where hocbong=0
    --không được vì có ràng buộc khóa ngoại --
    --B.Truy vấn những câu đơn giản
    --9. 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 sẽ được sắp xếp theo thứ tự Mã sinh viên tăng dần.
    select masv as'Mã sinh viên',hosv as'Họ sinh viên',tensv as'Tên sinh viên',hocbong as'Học Bổng'
    from dmsv
    orderby masv asc

    --10. 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 as'Mã sinh viên',hosv+' '+tensv as'họ tên sinh viên',phai as'Phái',ngaysinh as'Ngày sinh'
    from dmsv
    orderby phai asc

    --11. 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 as'họ tên sinh viên',ngaysinh as'Ngày sinh',hocbong as'Học bổng'
    from dmsv
    orderby ngaysinh asc,hocbong desc
    --12. 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 as'Mã môn học',tenmh as'Tên môn học',sotiet as'số tiết'
    from dmmh
    where tenmh like't%'

    --13. 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 as'Họ tên sinh viên',ngaysinh as'Ngày sinh',phai as'Phái'
    from dmsv
    where tensv like'%i'
    --14. 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 as'Mã khoa',tenkhoa as'tên khoa'
    from dmkhoa
    where tenkhoa like'_n%'
    --15. Liệt kê những sinh viên mà họ có chứa chữ Thị.
    select*
    from dmsv
    where hosv like N'%thị%'

    --16. 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 As N'Mã Sinh Viên',HoSV+' '+TenSV As N'Họ Tên Sinh Viên',Phai As N'Phái',HocBong AsN'Học Bổng'
    from dmsv
    where tensv between'a'and'm'



    --17. 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 MaSV As N'Mã Sinh Viên',HoSV+' '+TenSV As N'Họ Tên Sinh Viên',Phai As N'Phái',HocBong AsN'Học Bổng'
    From DMSV
    Where TenSV like'%[a-m]%'
    Orderby HoSV+' '+TenSV ASC

    --18. 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 As N'Mã Sinh Viên',HoSV+' '+TenSV As N'Họ Tên Sinh Viên',MaKhoa As N'Mã Khoa',HocBongAs N'Học Bổng'

    from dmsv
    where hocbong>100000
    orderby makhoa desc


    --19. 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 As N'Họ Tên Sinh Viên',MaKhoa As N'Mã Khoa',NoiSinh As N'Nơi Sinh',HocBong AsN'Học Bổng'
    From DMSV
    Where HocBong>=150000 and NoiSinh=N'Hà Nội'

    --20. 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 As N'Mã Sinh Viên',MaKhoa As N'Mã Khoa',Phai As N'Phái'
    From DMSV
    Where MaKhoa='AV'or MaKhoa='VL'

    --21. 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 As N'Mã Sinh Viên',NgaySinh As N'Ngày Sinh',NoiSinh As N'Nơi Sinh',HocBong As N'Học Bổng'
    From DMSV
    Where NgaySinh>='01/01/1991'and NgaySinh<='05/06/1992'

    --22. 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 As N'Mã Sinh Viên',NgaySinh As N'Ngày Sinh',Phai As N'Phái',MaKhoa As N'Mã Khoa'
    From DMSV
    Where HocBong>=80000 and HocBong<=150000

    --23. 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 As N'Mã Môn Học',TenMH As N'Tên Môn Học',SoTiet As N'Số Tiết'
    From DMMH
    Where SoTiet>30 and SoTiet<45
    --24. 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 masv 'Mã sinh viên','Họ tên sinh viên'=hosv+' '+tensv,tenkhoa 'Tên khoa',phai 'Phái'
    from dmsv sv,dmkhoa khoa
    where sv.makhoa=khoa.makhoa
    --25. Liệt kê những sinh viên nữ, tên có chứa chữ N--
    select*
    from dmsv
    where phai=N'nữ'and tensv like N'%n%'
    --C. Truy vấn sử dụng hàm: year, month, day, getdate, case, ….
    --26. 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 'Họ sinh viên',tensv 'Tên sinh viên',noisinh 'Nơi sinh',ngaysinh 'ngày sinh'
    from dmsv
    where noisinh=N'Hà nội'andmonth(ngaysinh)=2
    --27. 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 'Tên sinh viên','Tuổi'=year(getdate())-year(ngaysinh),hocbong 'Học bổng'
    from dmsv
    whereyear(getdate())-year(ngaysinh)>20
    --28. 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 'Tên sinh viên','Tuổi'=year(getdate())-year(ngaysinh),tenkhoa 'Tên khoa'
    from dmsv sv,dmkhoa khoa
    where sv.makhoa=khoa.makhoa and(year(getdate())-year(ngaysinh)between 20 and 25)
    --29. 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.
    select hosv+' '+tensv 'Tên sinh viên',phai 'Phái',ngaysinh 'Ngày sinh'
    from dmsv
    whereyear(ngaysinh)=1990 and(month(ngaysinh)in(1,2,3))--between 1 and 3)
    --30. 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'Mã sinh viên',phai'Phái',makhoa 'Mã khoa','Mức trung bình'=casewhen hocbong>500000 thenN'Học bổng cao'else N' Mức trung bình'end
    from dmsv
    --D. Truy vấn sử dụng hàm kết hợp: max, min, count, sum, avg và gom
    --nhóm
    --32. Cho biết tổng số sinh viên của toàn trường
    select'Tổng sinh viên toàn trường'=count(masv)
    from dmsv
    --33. Cho biết tổng sinh viên và tổng sinh viên nữ.
    --đây là cách viết gộp trong bảng
    select'Tổng sinh viên'=count(masv),'Tổng sinh viên nữ'=sum(case phai when N'nữ'then 1 else 0 end)
    from dmsv
    ------------
    select'Tổng sinh viên'=count(masv),t.nu 'Tổng sinh viên nữ'
    from dmsv,
    (
    selectcount(masv)as'nu'
    from dmsv
    where phai=N'Nữ'
    )as t
    groupby t.nu



    --34. Cho biết tổng số sinh viên của từng khoa.
    select makhoa 'Mã khoa','Mã sinh viên'=count(masv)
    from dmsv
    groupby makhoa

    --35. Cho biết số lượng sinh viên học từng môn.
    select tenmh'Tên môn học',count(distinct masv)'Mã sinh viên'
    from ketqua kq,dmmh mh
    where kq.mamh=mh.mamh
    groupby tenmh
    --36. 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)
    selectcount(distinct mamh)'Tổng số môn học'
    from ketqua

    --37. Cho biết tổng số học bổng của mỗi khoa.
    select makhoa 'Mã khoa',sum(hocbong)'Tổng học bổng'
    from dmsv
    groupby makhoa
    --38. Cho biết học bổng cao nhất của mỗi khoa.
    select makhoa 'Mã khoa',max(hocbong)'Học bổng cao nhất'
    from dmsv
    groupby makhoa
    --39. 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,'Tổng sinh viên nam'=sum(case phai when N'nam'then 1 else 0 end),'Tổng sinh viên nữ'=sum(case phai when N'nữ'then 1 else 0 end)
    from dmsv
    groupby makhoa
    --40. Cho biết số lượng sinh viên theo từng độ tuổi.
    selectyear(getdate())-year(ngaysinh)'Tuổi',count(masv)'Số sinh viên'
    from dmsv
    groupbyyear(getdate())-year(ngaysinh)

    --41. Cho biết những năm sinh nào có 2 sinh viên đang theo học tại trường.
    selectyear(ngaysinh)'Năm',count(Masv)'Số sinh viên'
    from dmsv
    groupbyyear(ngaysinh)
    havingcount(Masv)=2

    --42. 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)'Số sinh viên'
    from dmsv
    groupby NoiSinh
    havingcount(Masv)>=2
    --43. Cho biết những môn nào có trên 3 sinh viên dự thi.
    select mamh 'Mã môn học',count(masv)'Số Sinh viên'
    from ketqua
    groupby mamh
    havingcount(masv)>3

    --44. Cho biết những sinh viên thi lại trên 2 lần.
    select masv,mamh,count(lanthi)'so lan thi lai'

    from ketqua
    groupby masv,mamh

    havingcount(lanthi)>2

    --45. 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 'Họ tên sinh viên',phai,lanthi,avg(Diem)'diem trung binh'
    from ketqua kq,dmsv sv
    where kq.masv=sv.masv and lanthi=1 and phai=N'nam'
    groupby lanthi,phai, Hosv+' '+tensv
    havingavg(Diem)>7.0

    --46. Cho biết danh sách các sinh viên rớt trên 2 môn ở lần thi 1.
    select masv 'Mã sinh viên',count(mamh)'Số môn rớt'
    from ketqua
    where lanthi=1 and diem<5
    groupby masv
    havingcount(mamh)>=2
    --47. Cho biết danh sách những khoa có nhiều hơn 2 sinh viên nam
    select makhoa 'Mã khoa','Số sinh viên nam'=count(masv)
    from dmsv
    where phai=N'Nam'
    groupby makhoa
    havingcount(masv)>=2

    --48. Cho biết những khoa có 2 sinh đạt học bổng từ 200.000 đến 300.000.
    select makhoa 'Mã khoa','Số sinh viên'=count(masv)
    from dmsv
    where hocbong between 200000 and 300000
    groupby makhoa
    havingcount(masv)>2

    --49. 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.
    --làm từng bảng
    select tenmh,'Số sinh viên Đậu'=count(masv)
    from ketqua kq,dmmh mh
    where kq.mamh=mh.mamh and lanthi=1 and diem>=5
    groupby tenmh

    select tenmh,'Số sinh viên Rớt'=count(masv)
    from ketqua kq,dmmh mh
    where kq.mamh=mh.mamh and lanthi=1 and diem<5
    groupby tenmh
    --Làm gộp
    select tenmh 'Tên môn học','Số sinh viên Đậu'=sum(casewhen diem>=5 then 1 else 0 end),'Số sinh viên Rớt'=sum(casewhen diem<5 then 1 else 0 end)
    from ketqua kq,dmmh mh
    where kq.mamh=mh.mamh and lanthi=1
    groupby tenmh
    --50. Cho biết số lượng sinh viên nam và số lượng sinh viên nữ của từng khoa.
    --trùng lại câu 39
    select makhoa,'Tổng sinh viên nam'=sum(case phai when N'nam'then 1 else 0

    end),'Tổng sinh viên nữ'=sum(case phai when N'nữ'then 1 else 0 end)
    from dmsv
    groupby makhoa
    ------

    --F. Truy vấn con trả về một giá trị
    --51. Cho biết sinh viên nào có học bổng cao nhất.
    select hosv+' '+tensv 'Họ tên sinh viên',hocbong
    from dmsv
    where hocbong=(selectmax(hocbong)from dmsv)
    --52. Cho biết sinh viên nào có điểm thi lần 1 môn cơ sở dữ liệu cao nhất.

    select hosv+' '+tensv 'Họ tên sinh viên',tenmh 'Tên môn học',lanthi,diem
    from ketqua kq,dmmh mh,dmsv sv
    where sv.masv=kq.masv and kq.mamh=mh.mamh and lanthi=1 and tenmh=N'cơ sở dữ liệu'
    and diem=
    (
    selectmax(diem)
    from ketqua kq,dmmh mh
    where kq.mamh=mh.mamh and tenmh=N'cơ sở dữ liệu'and lanthi=1
    )
    --53. Cho biết sinh viên khoa anh văn có tuổi lớn nhất.
    select hosv+' '+tensv 'Họ tên sinh viên',ngaysinh 'Ngày sinh',makhoa 'Mã khoa'
    from dmsv
    where/*makhoa='av' and*/ ngaysinh=(
    selectmin(ngaysinh)
    from dmsv
    where makhoa='av'

    )

    --or
    select hosv+' '+tensv 'Họ tên sinh viên',ngaysinh 'Ngày sinh',makhoa 'Mã khoa'
    from dmsv
    where(getdate()-ngaysinh)=
    (
    selectmax(getdate()-ngaysinh)
    from dmsv
    where makhoa='av'

    )
    --54. Cho biết khoa nào có đông sinh viên nhất.
    --cach 1:
    select tenkhoa
    from dmsv sv,dmkhoa kh
    where sv.makhoa=kh.makhoa
    groupby tenkhoa
    havingcount(tenkhoa)>=all(selectcount(masv)
    from dmsv
    groupby makhoa)
    --cach 2:
    select tenkhoa

    from dmsv sv,dmkhoa kh
    where sv.makhoa=kh.makhoa
    groupby tenkhoa
    havingcount(tenkhoa)=(selectmax(t.tong)
    from
    (
    selectcount(masv)as tong
    from dmsv
    groupby makhoa
    )as t
    )

    --55. Cho biết khoa nào có đông nữ nhất.
    select tenkhoa 'Tên khoa'
    from dmsv sv,dmkhoa kh
    where sv.makhoa=kh.makhoa and phai=N'nữ'
    groupby tenkhoa
    havingcount(tenkhoa)>=all(selectcount(masv)
    from dmsv
    where phai=N'nữ'
    groupby makhoa)
    --56. 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<5
    groupby mamh
    havingcount(diem)>=all(selectcount(diem)
    from ketqua
    where lanthi=1 and diem<5
    groupby mamh)
    --57. Cho biết sinh viên không học khoa anh văn có điểm thi môn phạm lớn hơn điểm thi văn
    --phạm của sinh viên học khoa anh văn.
    selectdistinct kq.masv
    from ketqua kq, dmsv sv
    where sv.masv=kq.masv and mamh='05'and makhoa notlike'av'and diem>(
    select diem
    from ketqua kq, dmsv sv
    where sv.masv=kq.masv and mamh='05'and makhoa='av'
    )
    --G. Truy vấn con trả về nhiều giá trị, sử dụng lượng từ all, any, union, top.
    --58. Cho biết sinh viên có nơi sinh cùng với Hải.
    select masv,hosv+' '+tensv
    from dmsv
    where noisinh=(select noisinh
    from dmsv
    where tensv=N'hải')
    --59. 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 masv
    from dmsv
    where hocbong>=all(select hocbong from dmsv where makhoa='av')
    --60. 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 masv,hocbong
    from dmsv
    where hocbong>=any(select hocbong from dmsv where makhoa='av')
    --61. 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 mamh='01'and lanthi=2 and diem>=all(select diem from ketqua where mamh='01'and lanthi=1)
    --62. 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,(select mamh,max(diem)as maxdiem
    from ketqua
    groupby mamh)a
    where ketqua.mamh=a.mamh and diem=a.maxdiem
    --63. Cho biết những khoa không có sinh viên học.
    select*
    from dmkhoa
    wherenotexists(selectdistinct makhoa
    from ketqua,dmsv where ketqua.masv=dmsv.masv and makhoa=dmkhoa.makhoa)
    --64. Cho biết sinh viên chưa thi môn cơ sở dữ liệu.
    select*
    from dmsv
    wherenotexists
    (selectdistinct*
    from ketqua
    where mamh ='01'and masv=dmsv.masv)
    --65. 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 kq
    where lanthi=2 andnotexists
    (select*
    from ketqua
    where lanthi=1 and masv=kq.masv)
    --66. Cho biết môn nào không có sinh viên khoa anh văn học.
    select tenmh
    from dmmh
    where
    notexists
    (select mamh
    from ketqua kq,dmsv sv
    where sv.masv=kq.masv and sv.makhoa='av'and dmmh.mamh=mamh)

    --67. 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 DMSv dmsv

    Where MaKhoa='AV'AndNotExists(Select*
    From KetQua
    Where MaMH='05'And MaSV=dmsv.MaSV
    )


    --68. Cho biết những sinh viên không rớt môn nào.
    Select MaSV
    From DMSV dmsv
    WhereNotExists(Select*
    From KetQua
    Where Diem<=5 And MaSV=dmsv.MaSV
    )



    --69. Cho biết những sinh viên học khoa anh văn có học bổng và những sinh viên chưa bao
    --giờ rớt.
    Select MaSV,MaKhoa,HocBong
    From DMSv dmsv
    Where MaKhoa='AV'And HocBong>0 AndNotExists(Select*
    From KetQua
    Where Diem<5 And MaSV=dmsv.MaSV
    )


    --70. Cho biết khoa nào có đông sinh viên nhận học bổng nhất và khoa nào khoa nào có ít
    --sinh viên nhận học bổng nhất.
    Select MaKhoa,count(MaSV)'So Luong SV'
    From DMSV
    Where HocBong>0
    GroupBy MaKhoa
    Havingcount(MaSV)>=All(Selectcount(MaSV)
    From DMSv
    where hocbong>0
    GroupBy MaKhoa
    )
    UNION
    Select MaKhoa,count(MaSV)'So Luong SV'
    From DMSV
    Where HocBong>0
    GroupBy MaKhoa
    Havingcount(MaSV)<=All(Selectcount(MaSV)
    From DMSV
    where hocbong>0
    GroupBy MaKhoa
    )


    --71. Cho biết 3 sinh viên có học nhiều môn nhất.

    Selecttop 3 MaSV,Count(Distinct MaMH)'Số môn học'
    From KetQua
    GroupBy MaSV
    HavingCount(Distinct MaMH)>=All(Selectcount(distinct MaMH)
    From KetQua
    GroupBy MaSV
    )


    /*==========================H. Truy vấn dùng phép chia =========================*/
    --72. Cho biết những môn được tất cả các sinh viên theo học.
    Select MaMH
    From KetQua
    GroupBy MaMH
    Havingcount(distinct MaSV)=(Selectcount(MaSV)
    From DMSv
    )


    --73. 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.
    Selectdistinct MaSV
    From KetQua kq
    WhereExists(Selectdistinct MaMH
    From KetQua
    Where MaSV='A02'and MaMH=kq.MaMH
    )


    --74.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.
    Select TenSV
    From KetQua kq,DMSv dmsv,(Select MaSV,MaMH,count(distinct MaMH)SoMon
    From KetQua
    Where MaSV='A02'
    GroupBy MaSV,MaMH)a
    Where kq.MaSV=dmsv.MaSV and kq.MaMH=a.MaMH and kq.MaSV <>a .MaSV
    GroupBy TenSV
    Havingcount(distinct kq.MaMH)=(Selectcount(distinct MaMH)
    From KetQua
    Where MaSV='A02')





    Select dmsv.MaSV
    From KetQua kq, DMSv dmsv
    Where kq.MaSV=dmsv.MaSV and MaMH=(Selectdistinct MaMH
    From KetQua
    Where MaSV='A02'and MaMH=kq.MaMH)and dmsv.MaSV NotLike'A02'
    GroupBy dmsv.MaSV
    Havingcount(distinct MaMH)=(Selectcount(distinct MaMH)
    From KetQua
    Where MaSV='A02')



    --75. 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ó.
    CreateTable SinhVien_KetQua
    (
    MaSV char(3)notnull,
    HoSV nvarchar(15)notnull,
    TenSV nvarchar(7)notnull,
    SoMonHoc tinyint
    )

    InsertInto SinhVien_KetQua
    Select dmsv.MaSV,HoSV,TenSV,count(distinct MaMH)
    From DMSV dmsv,KetQua kq
    Where dmsv.MaSV=kq.MaSV
    GroupBy dmsv.MaSV,HoSV,TenSV


    --76. 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.
    go
    altertable dmkhoa
    add siso tinyint
    go

    update dmkhoa
    set siso=(selectcount(masv)
    from dmsv
    where makhoa='av'
    groupby(makhoa))
    where makhoa='av'

    update dmkhoa
    set siso=(selectcount(masv)
    from dmsv
    where makhoa='TH'
    groupby(makhoa))
    where makhoa='Th'


    update dmkhoa
    set siso=(selectcount(masv)
    from dmsv
    where makhoa='Tr'
    groupby(makhoa))
    where makhoa='Tr'

    update dmkhoa
    set siso=(selectcount(masv)
    from dmsv
    where makhoa='vl'
    groupby(makhoa))
    where makhoa='vl'

    --77. 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<=5

    select*from ketqua
    --78. Tăng học bổng lên 100000 cho những sinh viên có điểm trung bình là 6.5 trở lên
    update dmsv
    set hocbong=hocbong+100000
    where masv in(select masv
    from ketqua
    groupby masv
    havingavg(diem)>=6.5)
    --79. 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 dmsv
    set hocbong=0
    where masv in(select masv
    from ketqua
    where lanthi=1 and diem<5
    groupby masv
    havingcount(mamh)=2
    )

    --80. Xoá tất cả những sinh viên chưa dự thi môn nào.
    deletefrom dmsv
    wherenotexists(select masv
    from ketqua
    where masv=dmsv.masv
    )
    --81. Xóa những môn mà không có sinh viên học.
    deletefrom dmmh
    wherenotexists(select mamh
    from ketqua
    where mamh=dmmh.mamh
    )
    -- Tạo view
    --82. Danh sách sinh viên không bi rớt môn nào
    createview cau82
    as
    select dmsv.masv,hosv,tensv,phai,ngaysinh,noisinh,hocbong
    from dmsv,ketqua
    where dmsv.masv=ketqua.masv
    groupby dmsv.masv,hosv,tensv,phai,ngaysinh,noisinh,hocbong
    havingmin(diem)>=5


    --83. Danh sách sinh viên học môn văn phạm và môn cơ sở dữ liệu
    createview cau83
    as
    select*
    from dmsv
    where masv in
    (selectdistinct ketqua.masv
    from ketqua,dmsv
    where dmsv.masv=ketqua.masv and(mamh='01'or mamh='05')
    )
    dropview cau83

    --84. 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.



    createview cau84
    as
    selectdistinct dmsv.masv,tensv,tenmh,max(diem)diem
    from dmsv,ketqua,dmmh
    where dmsv.masv=ketqua.masv and dmmh.mamh=ketqua.mamh
    groupby dmsv.masv,tensv,tenmh
    select*from cau84
    --85. 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
    createview cau85
    as
    select*
    from dmsv
    where masv in
    (select masv
    from ketqua
    where(lanthi=1 and diem <5)ornotexists
    (Select*
    From KetQua
    Where MaMH='05'And MaSV=ketqua.MaSV
    ))

    --86. Danh sách những sinh viên khoa có 2 sinh viên nữ trở lên
    createview cau86
    as
    select*
    from dmsv
    where makhoa=
    (select sv.makhoa
    from dmsv sv,dmkhoa kh
    where sv.makhoa=kh.makhoa and phai=N'nữ'
    groupby sv.makhoa
    havingcount(tenkhoa)>=all(selectcount(masv)
    from dmsv
    where phai=N'nữ'
    groupby makhoa))



    /*===============HẾT================*/


    Link Download
    https://docs.google.com/document/d/1...O9TLg4ll8/edit
     

    Loading...

    Bình Luận Bằng Facebook

  2. tranhoang559987

    tranhoang559987

    Bài viết:
    90
    Đã được thích:
    0
    Đúng thông tin mình cần , đang tìm kiếm bữa giờ mà chưa thấy
     
  3. tranhoang559987

    tranhoang559987

    Bài viết:
    90
    Đã được thích:
    0
    Đúng thông tin mình cần , đang tìm kiếm bữa giờ mà chưa thấy
     
  4. duhiephoa

    duhiephoa

    Bài viết:
    11
    Đã được thích:
    0
  5. kenkashop

    kenkashop

    Bài viết:
    23
    Đã được thích:
    0
    chịu khó code cái này cũng giỏi lắm ak.
     

Chia sẻ trang này