Scribd
Upload
1 views3 pages

Exercise

The document contains a series of SQL queries related to student and course data management. It includes examples of selecting student scores, calculating average scores, identifying students not registered for courses, and counting enrolled students in courses. Additionally, it highlights the importance of understanding SQL execution order for effective query writing.

Uploaded by

chongtuonglaicuathanh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
1 views3 pages

Exercise

The document contains a series of SQL queries related to student and course data management. It includes examples of selecting student scores, calculating average scores, identifying students not registered for courses, and counting enrolled students in courses. Additionally, it highlights the importance of understanding SQL execution order for effective query writing.

Uploaded by

chongtuonglaicuathanh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Ex1.

1)
SELECT TenMH, Diem
FROM SinhVien SV INNERJOIN KetQua KQ
ON SV. MaSV = KQ. Ma SV
INNERJOIN MonHoc MH
ON MH. MaHP = KQ. MaMH
WHERE HoTen = ‘Nguyen Van A’
2)
SELECT MaSV, AVG (Diem)
FROM SinhVien SV INNER JOIN KetQua KQ
ON SV. MaSV = KQ. Ma SV
GROUP BY MaSV, HoTen
3)
SELECT SV. MaSV, SV. HoTen
FROM SinhVien SV
WHERE NOT EXIST (
SELECT *
FROM DangKy DK
WHERE DK. MaSV = SV. MaSV
)
---> Dạng này thầy sẽ cho vào đề
5)
SELECT MH. MaMH, MH. TenMH, COUNT (DK. MaSV) AS SoLuongSV
FROM MonHoc MH
JOIN Lop L ON MH. MaMH = L. MaMH
JOIN DangKy DK ON L. MaLop = DK. MaLop
GROUP BY MH. MaMH, MH. TenMH
HAVING COUNT (DK. MaSV) >10
6)
SELECT DISTINCT SV. MaSV, SV. HoTen
FROM SinhVien SV INNER JOIN Ketqua. KQ
ON SV. MaSV = KQ. MaSV
INNERJOIN MonHoc. MH
ON KQ. MaMH = MH. MaMH
WHERE MH. SoTinChi >3
AND KQ. MaSV = SV. MaSV
7)

8)
SELECT MH. MaMH, MH. TenMH
FROM MonHoc MH
WHERE EXISTS
9)

Bài đề thi trước


1)
SELECT SalesOrder ID ,
OrderDate,
(SubTotal + TaxAmt + Freight) AS TotalValue,
(TaxAmt * 100.0 / SubTotal) AS TaxRatePercentage
FROM SOH. SalesOrderHeader
WHERE (SubTotal + TaxAmt + Freight) >10000
ORDER BY TotalValue DESC
Note lỗi: where không được viết total value >10000 vì select thưc hiện sau
Orderby lại dùng totalvalue được vì order by thực hiện sau select
---> Học lại về thứ tự thực hiện câu lệnh
2)
SELECT CustomerKey,
(FirstName + ‘ ‘ + ISNULL (MiddleName + ‘ ‘, ‘ ‘) + ‘ ‘ + LastName) AS CustomerName,
DATEDIFF (YEAR, DateFirstPurchase, BirthDate) AS AgeAtFirstPurchase,
DATEDIFF (YEAR, BirthDate, GETDATE) AS CurrentAge
FROM DimCustomer

You might also like