Dmkhoa( ma khoa, ten khoa)
Monhoc(mamh, ten mh, so tiet)
Sinhvien(masv, hosv, tensv, gioi tinh, ngay sinh, noi sinh, dia chi,
quan, ma khoa)
Ket qua(masv, mamh, diem)
Cau 1: Viet cau lenh de tinh diem trung binh cua tung sinh vien?
Gom cac truong: masv, hosv, ten sv diem trung binh)
select a.masv, a.hosv, a.tensv, avg(b.diem) as dtb
from sinhvien a, ketqua b
where a.masv = b.masv
group by a.masv, a.hosv, a.tensv
Cau 2: Viet cau lenh hien thi cc sinh vien co noisinh la “Da Nang”
va co diem >= 5
select a.masv, tensv, gioitinh, ngaysinh, noisinh, diachi, quan, makhoa
from sinhvien a, ketqua b
where a.masv=b.masv and noisinh='da nang' and diem>=5;
Cau 3: Hien thi danh sach nhung sinh vien co diem cao nhat cua
mon co so du lieu
select a.masv, a.hosv, a.tensv, b.mamh, b.diem
from sinhvien a, ketqua b
where
a.masv=b.masv and b.mamh=01 and diem=(select max(diem)
from sinhvien, ketqua
where sinhvien.masv=ketqua.masv and b.mamh=ketqua.mamh)
Cau 4: Viet lenh tao bang Sinhvien o tren biet Masv lam khoa
chinh va ma khoa la khoa ngoai tham chieu den bang dmkhoa.
create table sinhvien
(masv varchar(10) is not null constraint pk1 primary key;
ho varchar(20),
ten varchar(10),
makhoa varchar(10) is not null constraint pk2 references dmkhoa(makhoa),
constraint pk03 primary key (manv, makhoa));
Cau 5: Hien thi tat ca sinh vien gom cac thong tin: ma sinh, ten
sinh vien, ten khoa, diem, ghi chu voi ghi chu la “thi lai” cho
nhung sinh vien nao co diem so nho hon5, con lai thi bo trong.
select a.masv, a.hosv, a.tensv,a.makhoa, b.diem, case when (diem<5) then
'thi lai' end as 'ghi chu'
from sinhvien a, ketqua b
where a.masv=b.masv
Cau 6: Hien thi diem trung binh cua tung sinh vien gom cac thong
tin: Masv, tensv, lop, dtb.
select a.masv, a.hosv, a.tensv, avg(b.diem) as dtb
from sinhvien a, ketqua b
where a.masv = b.masv
group by a.masv, a.hosv, a.tensv
Cau 7: Hien thi nhung sinh vien thi lai la nhung sinh vien nao co
diem so nho hon 5, con lai thi bo trong
select a.masv, a.hosv, a.tensv,a.makhoa, b.diem, case when (diem<5) then
'thi lai' end as 'ghi chu'
from sinhvien a, ketqua b
where a.masv=b.masv
Cau 8: Hien thi nhung sinh vien co ten bat dau ky tu “H”, hoc khoa
“AV” gom cac thong tin: Masv, tensv, khoa.
select masv, hosv, tensv, makhoa
from sinhvien
where tensv like 'h%' and makhoa='av'
Cau 9: Hien thi nhung sinh vien co diem cao nhat cua moi khoa
select a.masv,a.hosv , a.tensv, a.gioitinh, a.makhoa, b.diem
from sinhvien a, ketqua b
where
a.masv=b.masv and b.diem=(select max(diem)
from sinhvien, ketqua
where sinhvien.masv=ketqua.masv and a.makhoa=sinhvien.makhoa)
Cau 10: Hien thi nhung sinh vien co diem cao nhat cua moi mon
hoc.
select a.masv, a.hosv, a.tensv, a.gioitinh, b.mamh, b.diem
from sinhvien a, ketqua b
where
a.masv=b.masv and diem=(select max(diem)
from sinhvien, ketqua
where sinhvien.masv=ketqua.masv and b.mamh=ketqua.mamh)
- Xem thêm -
.DOC 
2 
300 
133 
