Bài tập về SQL Server

#1

Cho em hỏi em có 3 bảng là
SINHVIEN(MaSV, TenSV,…)
KETQUA(MaSV,MaKhoaHoc, Diem)
GIANGDAY(MaKhoaHoc, MaMH)

Hỏi: Cho biết mã, tên sinh viên học cả 2 môn có mã môn học là CSLD và CTDL và có điểm 1 trong 2 môn >= 8 làm như thế nào ạ? em cảm ơn

#2 
SELECT MaSV, TenSV FROM SINHVIEN WHERE MaSV =(
    SELECT MaSV FROM KETQUA WHERE MaKhoaHoc = (
    SELECT MaKhoaHoc FROM GIANGDAY WHERE MaMH IN ['CSLD', 'CTDL'] ))
1 Like
#3

còn điều kiện điểm >=8 thì ntn anh ?

#4

bạn thêm điều kiện DIEM >= 8 AND trước điệu kiện MaKhoaHoc = ( ...

1 Like
#5

trong sql còn có từ khóa distinct và 'group by ’ giải quyết được cái việc trùng MaSV sau khi chọn từ bảng kết quả, ví dụ:
SELECT MaSV FROM KETQUA WHERE MaKhoaHoc ... GROUP BY MaSV
SELECT DISTINCT MaSV FROM KETQUA WHERE MaKhoaHoc ...

2 Likes
#6

ở phần chọn MaSV anh chỉ chọn những sinh viên đã học ít nhất một môn CSDL hoặc CTDL thôi, ở đây học cả 2 môn luôn anh.

#7

Hỏi bài hay nhờ làm bài

1 Like
#8

đề bài là 1 trong 2 môn mà bạn

1 Like
#9

trước mình chưa gặp bài dạng này.
Trong sql có từ khóa COUNT() và HAVING bạn dùng nó với bảng KETQUA. ví dụ:

SELECT MaSV FROM KETQUA WHERE .... () HAVING COUNT(MaSV) = 2

vì số môn cần lấy là 2 [‘CSDL’, CTDL’]

1 Like
#10

Đề bài là: học cả 2 môn và có điểm 1 trong 2 môn là >=8
VD: SV001 có điểm CSDL là 6 và CTDL là 9 thì chọn

2 Likes
#11

em hỏi hướng giải thôi chứ không cần viết rõ ràng hết đâu. ^^

#12

em đang bí phần điểm số á anh >=8 một trong 2 môn TT :pensive:

#14

thì anh nói rồi e thêm điều kiện đó cùng với toán tử AND vì nó không thể lấy 1 môn dưới 8 được, công với điều kiện là COUNT() = 2 thì loại ra rồi

mà anh cũng không chắc cái HAVING, anh chưa bao giờ làm việc với thằng này :frowning:

#15

http://codepad.org/3LPd72gb “Phần khởi tạo DB”

SELECT *
FROM dbo.SINHVIEN
WHERE MaSV IN (
	SELECT MaSV 
	FROM KETQUA 
	WHERE DIEM >=8 AND MaKhoaHoc IN (
		SELECT MaKhoaHoc
		FROM GIANGDAY 
		WHERE MaMH IN ('CSDL','CTDL')
	)
	GROUP BY MaSV
	HAVING COUNT(MaSV) = 2
)

ý anh là vậy phải không? nó không có kq nó chỉ hiện nếu cả 2 môn đều trên 8 thôi

#16

sr em, vậy không thể gộp 2 môn thành 1 query rồi.
mình phải tách ra rồi dùng từ khóa UNION
câu query mẫu, có thể lỗi.

SELECT MaSV, TenSV FROM SINHVIEN WHERE MaSV = (
      SELECT MaSV FROM KETQUA WHERE DIEM >= 8 AND MaKhoaHoc = 
            (SELECT MaKhoaHoc FROM GIANGDAY WHERE MaMH = 'CSDL')
      UNION
      SELECT MaSV FROM KETQUA WHERE DIEM >= 8 AND MaKhoaHoc = 
            (SELECT MaKhoaHoc FROM GIANGDAY WHERE MaMH = 'CTDL')
)
#17

Anh có thể nói rõ khúc điểm 1 trong 2 môn được không ạ, em đang bí phần đó , nếu mình tách 2 môn ra rồi gọp lại còn phần kiểm tra điểm thì ntn anh ?

#18

khi truy vấn 1 câu query mà có truy vấn con thì câu truy vấn con bị nhóm bỡi điều kiện where nhưng do đề bài yêu cầu là chọn đã học 2 môn và 1 hoặc 2 môn có điểm trên 8 - là 2 nhóm khác nhau và cùng là điều kiện nhóm sv cần tìm nên dùng union là ổn

2 Likes
#20

Bạn có thể JOIN các table với nhau như sau:

SELECT DISTINCT sv.MaSV, sv.TenSV
FROM KETQUA kq1
INNER JOIN (
  SELECT kq2.MaSV, gd1.MaMH, kq2.Diem
  FROM KETQUA kq2 
  INNER JOIN GIANGDAY gd1 
  ON kq2.MaKhoaHoc = gd1.MaKhoaHoc
  WHERE gd1.MaMH = 'CTDL'
 ) a ON a.MaSV = kq1.MaSV
 INNER JOIN GIANGDAY gd2 on kq1.MaKhoaHoc = gd2.MaKhoaHoc
 INNER JOIN SINHVIEN sv on sv.MaSV = kq1.MaSV
 WHERE gd2.MaMH = 'CSDL'
 AND kq1.Diem >=8 OR a.Diem >=8;

Giải thích:
Chúng ta tạo một table tạm nhờ join 2 tables KETQUA và GIANGDAY với nhau với điều kiện là MaMH = 'CTDL'. Table tạm này chỉ có các record liên quan đến môn 'CTDL' mà thôi. Sau đó chúng ta lại join table tạm này với table KETQUA và table GIANGDAY lần nữa, nhưng lần này với điều kiện là MaMH từ table GIANGDAY phải là ‘CSDL’ để đảm bảo rằng kết quả cuối cùng có cả hai môn. Chúng ta cũng sử dụng điều kiện là các kết quả của một trong hai môn này từ 8 trở lên. Và cuối cùng, chúng ta JOIN với table SINHVIEN và sử dụng SELECT DISTINCT để lấy Mã và Tên Sinh viên đồng thời loại bỏ các kết quả trùng lặp.

3 Likes
#21

Cũng có thể viết lại cho dễ thấy:

select SV.tensv, SV.masv
from SinhVien SV
inner join (select diem, masv
from GiangDay join KetQua on GiangDay.MaKhoaHoc = KetQua.MaKhoaHoc
where MaMonHoc = 'CSDL') T1 on T1.masv = SV.masv
inner join (select diem, masv
from GiangDay join KetQua on GiangDay.MaKhoaHoc = KetQua.MaKhoaHoc
where MaMonHoc = 'CTDL') T2 on T2.masv = SV.masv
where T1.diem >= 8 or T2.diem >= 8

Cả 2 câu đều join 5 bảng, có thể so sánh query plan.

3 Likes
#23

Bài này có thể chia làm 2 phần:
1- Tìm sv học cả 2 môn.
2- Điểm 1 trong 2 môn phải >=8, => max(diem) phải >=8

1- Tìm SV học cả 2 môn.
Có nhiều cách để có kết quả như dùng Correlated Subquery hoặc Group by+Having.
Trong câu bên dưới toi dùng Goup+Having

 select k.masv
   from KETQUA k, GIANGDAY g
  where g.makhoahoc=k.makhoahoc   --join giữa 2 tables
    and g.mamh in ('CSLD','CTDL') --filter dữ liệu.
  group by k.masv
 having count(*)=2

Notes: Câu này sẽ sai nếu SV bị thi lại, nên thêm 1 colum nửa vào để phân biệt số lần thi !

2-Tìm SV có max điểm của 1 trong 2 môn >=8

  select k.masv, max(k.diem) max_diem
   from KETQUA k, GIANGDAY g
  where g.makhoahoc=k.makhoahoc
    and g.mamh in ('CSLD','CTDL')
  group by k.masv
 having max(k.diem)>=8

Nếu để ý sẽ thấy câu 1 &2 y chang nhau, chỉ khác điều kiện HAVING !

Đề bài yêu cầu chúng ta SV phải học 2 môn trên VÀ max(diem) của 2 môn >=8, nên chúng ta sẽ nối 2 câu lại.
Nhiều trường hợp chúng ta phải dùng UNION/ INTERSECT…
Do 2 câu này giống y chang nên chúng ta sẽ dùng AND !

 select k.masv
   from KETQUA k, GIANGDAY g
  where g.makhoahoc=k.makhoahoc
    and g.mamh in ('CSLD','CTDL')
  group by k.masv
 having count(*)=2 AND max(k.diem)>=8

Có MaSV, giở chỉ cần join với table SinhVien là lấy được đầy đủ thông tin!

2 Likes
83% thành viên diễn đàn không hỏi bài tập, còn bạn thì sao?