Share: Bài tập thực hành 3 môn SQL

/*Cau 1: Hien thi tat ca thong tin ve sinh vien*/
SELECT * FROM SINHVIEN

/*Cau 2: Hien thi masv,hodem,ten cua sinh vien khoa 1*/

SELECT SINHVIEN.MASV,HODEM,TEN
FROM SINHVIEN INNER JOIN LOP ON SINHVIEN.MALOP=LOP.MALOP
WHERE NAMNHAPHOC=2007

/*Cau 3: Hien thi ds sinh vien nu cua truong gom cac thong tin:masv,hedem,ten,ngaysinh,gioitinh
Ket qua sap xep tang dan theo ten, hodem */
SELECT MASV,HODEM,TEN,NGAYSINH,GIOITINH
FROM SINHVIEN
WHERE GIOITINH=0
ORDER BY TEN ,HODEM

/*Cau 4 Hien thi bang diem lan 1 co mahp la "SQL" gom cac thong tin...*/

SELECT SINHVIEN.MASV,HODEM,TEN,DIEM,MAHOCPHAN
FROM SINHVIEN INNER JOIN DIEMTHI ON SINHVIEN.MASV=DIEMTHI.MASV
WHERE LANTHI=1 AND MAHOCPHAN=N'SQL'

/*Cau 5:Hien thi nhung sv nu thi lai SQL */

select sinhvien.masv, hodem, ten, tenlop
from hocphan join diemthi on hocphan.mahocphan=diemthi.mahocphan
join sinhvien on diemthi.masv=sinhvien.masv
join lop on sinhvien.malop=lop.malop
where gioitinh=0 and hocphan.mahocphan=N'sql' and lanthi=2

/*Cau 6:Hien thi ca lop thuoc khoa KHoa hoc may tinh*/

SELECT MALOP AS 'Ma lop', TENLOP AS 'Ten lop'
FROM LOP INNER JOIN KHOA ON LOP.MAKHOA=KHOA.MAKHOA
WHERE LOP.MAKHOA=N'khmt'

/*Cau 7: Hien thi ds sinh vien khoa thuong mai dien tu gom cac thong tin: masv, hodem, ten, tenlop, namnhaphoc*/
SELECT SINHVIEN.MASV, HODEM, TEN, TENLOP, NAMNHAPHOC
FROM KHOA JOIN LOP ON KHOA.MAKHOA=LOP.MAKHOA
JOIN SINHVIEN ON LOP.MALOP=SINHVIEN.MALOP
WHERE khoa.MAKHOA=N'tmdt'

/*Cau 8:Hien thi ten va so tiet cac hoc phan*/
select MAHOCPHAN,TENHOCPHAN, SOTINCHI*15 AS 'SO TIET'
FROM HOCPHAN

/*Cau 9: Hien thi masv, ho dem, ten cua sinh vien thi lai sql va tinvp*/
select sinhvien.masv, hodem, ten
from sinhvien join diemthi on sinhvien.masv=diemthi.masv
where lanthi=2 and mahocphan=N'sql'
Intersect
select sinhvien.masv, hodem, ten
from sinhvien join diemthi on sinhvien.masv=diemthi.masv
where lanthi=2 and mahocphan=N'tindc'

/*Cau 10:Hien thi masv,hodem,ten cua 2 sinh vien co diem thi lan 1 hoc phan sql caoo nhat*/
select top 2 sinhvien.masv, hodem, ten, diem
from sinhvien join diemthi on sinhvien.masv=diemthi.masv
where lanthi=1 and mahocphan=N'sql'
order by diem desc

/*Cau 11: Hien thi masv, hodem, ten, diem cua sinh vien co diem thi lan 1 hoc phan Tin van phong tu 5 den 7*/
select sinhvien.masv, hodem+' '+ten as 'He & Ten', diem
from sinhvien inner join diemthi on sinhvien.masv=diemthi.masv
where lanthi=1 and mahocphan=N'tinvp' and diem between 5 and 7

/* Cau 12iem thi lan 1 tindc la 4,6 hoac 8*/
select sinhvien.masv, hodem+' '+ten as 'He & Ten', diem, tenlop
from sinhvien inner join diemthi on sinhvien.masv=diemthi.masv
inner join lop on sinhvien.malop=lop.malop
where lanthi=1 and mahocphan=N'tindc' and diem in(4,6,8)

/*Cau 13:Sinh vien nu co hoc "Ngo" */
select masv, hodem, ten
from sinhvien
where hodem like N'Ngo%'
order by ten desc
/*Cau 14: Hien thi nhung sv khoa 1 co diem thi tu 8-->9*/
SELECT SINHVIEN.MASV,HODEM,TEN,TENLOP,DIEM,TENKHOA,NAMNHAPHOC
FROM SINHVIEN INNER JOIN DIEMTHI ON SINHVIEN.MASV = DIEMTHI.MASV
INNER JOIN HOCPHAN ON DIEMTHI.MAHOCPHAN = HOCPHAN.MAHOCPHAN
INNER JOIN LOP ON SINHVIEN.MALOP = LOP.MALOP
INNER JOIN KHOA ON LOP.MAKHOA = KHOA.MAKHOA
WHERE(DIEM >= 8 AND DIEM <= 9) AND NAMNHAPHOC = 2007
ORDER BY MASV

/*Cau 15:Tao bang luu thong tin kq thi TIn DC cua lop DL01 */
SELECT SINHVIEN.MASV,HODEM, TEN, TENLOP,TENHOCPHAN, DIEM,TENKHOA,DIEMTHI.MAHOCPHAN
into KQ
FROM SINHVIEN INNER JOIN LOP ON SINHVIEN.MALOP = LOP.MALOP
INNER JOIN DIEMTHI ON SINHVIEN.MASV =DIEMTHI.MASV
INNER JOIN KHOA ON LOP.MAKHOA = KHOA.MAKHOA
INNER JOIN HOCPHAN ON DIEMTHI.MAHOCPHAN =HOCPHAN.MAHOCPHAN
GROUP BY SINHVIEN.MASV,HODEM,TEN,TENLOP,TENHOCPHAN,DIEM,TENKHOA,DIEMTHI.MAHOCPHAN
HAVING DIEMTHI.MAHOCPHAN = N'tindc' AND LOP.TENLOP = N'Dữ liệu 1'

/*Cau 16: Hien thi makhoa, tenkhoa khong quan li lop*/
select tenkhoa from khoa
where makhoa not in (select makhoa from lop)

/*Hien thi khoa quan li lop*/
select * from khoa
where /*not*/ exists (select * from lop where makhoa=khoa.makhoa)

/*Cau 17: Hien thi ten lop, ten khoa co tu 3 sv tro len*/
SELECT TENLOP,TENKHOA
FROM LOP INNER JOIN KHOA ON LOP.MAKHOA = KHOA.MAKHOA
INNER JOIN SINHVIEN ON LOP.MALOP = SINHVIEN.MALOP
GROUP BY TENLOP,TENKHOA
HAVING COUNT(SINHVIEN.MASV)>= 3
/*Cau 18: Hien thi msv, ten, ngay sinh, dtb thi lan 1*/
SELECT SINHVIEN.MASV, HODEM, TEN, NGAYSINH, AVG(DIEM) AS DTB
FROM SINHVIEN INNER JOIN DIEMTHI ON SINHVIEN.MASV=DIEMTHI.MASV
GROUP BY SINHVIEN.MASV,HODEM, TEN, NGAYSINH,LANTHI
HAVING LANTHI=1
/*Cau 19: Hien thi hoten,malop cua nhung sv thi lai ca 2 hp tinvp va sql */
select hodem, ten, lop.malop
from sinhvien inner join diemthi on sinhvien.masv=diemthi.masv
inner join lop on sinhvien.malop=lop.malop
where lanthi=2 and diemthi.mahocphan=N'tinvp'
Intersect
select hodem, ten, lop.malop
from sinhvien inner join diemthi on sinhvien.masv=diemthi.masv
inner join lop on sinhvien.malop=lop.malop
where lanthi=2 and diemthi.mahocphan=N'sql'

/*Cau 20:Hien thi tenhocphan, masv, diem, tong sl sinh vien thi lan 1 theo tung nhom hoc phan */

select tenhocphan, masv, diem
from diemthi inner join hocphan on diemthi.mahocphan=hocphan.mahocphan
where lanthi=1
order by tenhocphan
compute count(masv) by tenhocphan

/*Cau 21:Hien thi masv, hodem ,ten, mahp, diem va tb cac lan thi theo tung vinh vien*/
select sinhvien.masv, hodem, ten,mahocphan, diem, lanthi
from sinhvien inner join diemthi on sinhvien.masv=diemthi.masv
order by masv
compute avg(diem) by masv

/*Cau 22:Hien thi malop, hodem, ten, ... va tong so luong sinh vien theo tung lop*/

select hodem, ten,sinhvien.malop, masv, hodem, ten, ngaysinh
from sinhvien inner join lop on sinhvien.malop=lop.malop
order by malop
compute count(masv) by malop

/*Cau 23:Hien thi masv, hodem, ten, enhp, diem cua sv co thi lan 1 <5 nhung chua co diem lan 2*/

select * from diemthi

select sv.masv, hodem, ten, tenhocphan, diem
from sinhvien sv inner join diemthi d on sv.masv=d.masv
inner join hocphan hp on d.mahocphan=hp.mahocphan
where lanthi=1 and diem<5 AND not exists (select * from diemthi
where lanthi=2 and diemthi.masv=d.masv and diemthi.mahocphan=d.mahocphan)

/*Cau 24:Hoc phan diem thi cao nhat lan 2 > lan 1 */
select mahocphan
from diemthi
where (select max(diem) from diemthi where lanthi=2)<(select max(diem) from diemthi where lanthi=1)

/*Cau 25: Ten hoc phan diem trung binh lon hon 6*/

SELECT TENHOCPHAN, AVG(DIEM) AS DTB
FROM DIEMTHI INNER JOIN HOCPHAN ON DIEMTHI.MAHOCPHAN = HOCPHAN.MAHOCPHAN
WHERE LANTHI = 1
GROUP BY TENHOCPHAN
HAVING AVG(DIEM) > 6

/*Cau 26: Hien thi MSV, hoten, lop cua nhung sv hoc lop it 2 sv */
select sinhvien.masv,hodem,ten, tenlop
from sinhvien inner join lop on sinhvien.malop=lop.malop
group by sinhvien.masv, hodem, ten, tenlop
having count(masv) <2

/*Cau 27: Hien thi ten khoa, so sv thi lai cua moi khoa*/
select tenkhoa,count(diemthi.masv) as 'soluong'
from diemthi inner join sinhvien on diemthi.masv=sinhvien.masv
inner join lop on sinhvien.malop=lop.malop
inner join khoa on lop.makhoa=khoa.makhoa
where lanthi=2
group by tenkhoa

/*Cau 28: Hien thi ten khoa co nhieu sinh vien thi lai nhat*/
select k.tenkhoa
from khoa k join lop l on khoa.makhoa=l.makhoa
join sinhvien sv on l.malop=sv.malop
join diemthi d on sv.masv=d.masv
where lanthi=2
group by tenkhoa
having count(s.masv)=(select top 1 count(sv.masv)
from khoa k join lop l on khoa.makhoa=l.makhoa
join sinhvien sv on l.malop=sv.malop
join diemthi d on sv.masv=d.masv
where lanthi=2
group by tenkhoa
order by 2 desc)


/*Cau 29: Hien thi ten khoa co 2 sv thi lai tro len*/
SELECT KHOA.TENKHOA
FROM KHOA INNER JOIN LOP ON KHOA.MAKHOA=LOP.MAKHOA
INNER JOIN SINHVIEN ON LOP.MALOP=SINHVIEN.MALOP
INNER JOIN DIEMTHI ON SINHVIEN.MASV=DIEMTHI.MASV
WHERE DIEMTHI.LANTHI=2
GROUP BY KHOA.TENKHOA
HAVING COUNT(KHOA.MAKHOA)>2

/*Cau 30: Sinh vien co diem trung binh cac hoc phan >=8 va khong co hp nao <5*/
select sinhvien.masv,hodem ,ten, tenlop, avg(diem), min(diem)
from diemthi inner join sinhvien on diemthi.masv=sinhvien.masv
inner join lop on sinhvien.malop=lop.malop
group by sinhvien.masv, hodem , ten, tenlop
having avg(diem)>=8 and min(diem)>5


/*Cau 31:Hien thi masv co trung binh lan 1 cao nhat */

select masv, avg(diem)as diem
from diemthi
where lanthi=1
group by masv
having avg(diem)>= all(select avg(diem) from diemthi)


/*Bai tap tren lop/
select * from hocphan
where sotinchi>=all (select sotinchi from hocphan where mahocphan=N'tindc')

select hodem, ten
from sinhvien join lop on sinhvien.malop=lop.malop
where lop.malop=N'dh01' and year(ngaysinh) = any (select year(ngaysinh) from sinhvien join lop on sinhvien.malop=lop.malop
where lop.malop=N'dl01')

cam on nhiu

Tks..Thế mà không share sớm làm hồi tối thức khuya mệt ghê

Câu 19 làm j mà dài vậy

Mã:

SELECT HODEM, TEN, MALOP
FROM SINHVIEN JOIN DIEMTHI ON SINHVIEN.MASV=DIEMTHI.MASV
WHERE DIEMTHI.LANTHI=2 AND DIEMTHI.MAHOCPHAN='TVP' AND DIEMTHI.MAHOCPHAN='SQL'

Câu 29

Mã:

SELECT KHOA.TENKHOA
FROM KHOA JOIN LOP ON KHOA.MAKHOA=LOP.MAKHOA JOIN SINHVIEN ON LOP.MALOP=SINHVIEN.MALOP JOIN DIEMTHI 
	 ON SINHVIEN.MASV=DIEMTHI.MASV
WHERE DIEMTHI.LANTHI=2
GROUP BY KHOA.TENKHOA
HAVING COUNT(KHOA.MAKHOA)>2

Câu 28// Có ai hiếp dùm

Câu 19 ni làm trên lớp rồi mà, ^^

Câu 22 Join làm j cho mệt vinh
Tui làm tnay thui--->
select malop,masv,hodem,ten,ngaysinh
from sinhvien s
order by malop
compute count(masv) by malop

câu 28 nè xem thế nào nhé

Mã:

select top 1 tenkhoa,count(diemthi.masv)as'so sinh vien' 
from khoa,lop,sinhvien,diemthi 
where khoa.makhoa=lop.makhoa and lop.malop=sinhvien.malop and sinhvien.masv=diemthi.masv and lanthi=2 
group by tenkhoa order by 2 desc

reply

Câu 29-->
Mọi ng xem sao------>
Code

đã update đầy đủ ^^

sao nhìn khó hiểu quá vậy nè

khó hiểu thiệt. @@

các sinh viên nữ

sinh viên thi lại là sinh viên có lần thi là 1 và điểm thi <5

theo mình

câu này hình như không đúng vì select avg(diem) from diemthi ==> sẽ trả về 1 giá trị avg nào đó

theo mình thì tạo 1 cái view như thế này

rùi select từ cái view đó ta

thanks for sharing