Sinhvien

  • Số trang: 2 |
  • Loại file: DOC |
  • Lượt xem: 98 |
  • Lượt tải: 0
uchihasasuke

Đã đăng 588 tài liệu

Mô tả:

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 -