50道SQL練習

文章目錄

• 資料表
• 題目
• • 1~10
  • • 1. 查詢" 01 "課程比" 02 "課程成績高的學生的資訊及課程分數
    • • 1.1 查詢同時存在" 01 "課程和" 02 "課程的情況
      • 1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
      • 1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況
    • 2. 查詢平均成績大于等于 60 分的同學的學生編号和學生姓名和平均成績
    • 3. 查詢在 SC 表存在成績的學生資訊
    • 4. 查詢所有同學的學生編号、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )
    • • 4.1 查有成績的學生資訊
    • 5. 查詢「李」姓老師的數量
    • 6. 查詢學過「張三」老師授課的同學的資訊
    • 7. 查詢沒有學全所有課程的同學的資訊
    • 8. 查詢至少有一門課與學号為" 01 "的同學所學相同的同學的資訊
  • 11~20
  • 21~30
  • 31~40
  • 41~50

資料表

1. 學生表

   Student(SId,Sname,Sage,Ssex)

   –SId 學生編号,

   –Sname 學生姓名,

   –Sage 出生年月,

   –Ssex 學生性别

2. 課程表

   Course(CId,Cname,TId)

   –CId 課程編号,

   –Cname 課程名稱,

   –TId 教師編号

3. 教師表

   Teacher(TId,Tname)

   –TId 教師編号,

   –Tname 教師姓名

4. 成績表

   SC(SId,CId,score)

   –SId 學生編号,

   –CId 課程編号,

   –score 分數

題目

1~10

1. 查詢" 01 “課程比” 02 "課程成績高的學生的資訊及課程分數

解答：

SELECT * 
FROM (SELECT SId, score from SC where sc.CId = '01') as t1 JOIN (SELECT SId, score from SC where sc.CId = '02') as t2 JOIN Student
ON t1.SId = t2.SId AND t1.SId = Student.SId
WHERE t1.score > t2.score;
           

結果：

1.1 查詢同時存在" 01 “課程和” 02 "課程的情況

解答：

SELECT * 
FROM (SELECT SId, score from SC where sc.CId = '01') as t1 JOIN (SELECT SId, score from SC where sc.CId = '02') as t2
ON t1.SId = t2.SId ;
           

結果：

1.2 查詢存在" 01 “課程但可能不存在” 02 "課程的情況(不存在時顯示為 null )

解答：

SELECT * 
FROM (SELECT SId, score from SC where sc.CId = '01') as t1 LEFT JOIN (SELECT SId, score from SC where sc.CId = '02') as t2
ON t1.SId = t2.SId ;
           

結果：

1.3 查詢不存在" 01 “課程但存在” 02 "課程的情況

解答：

SELECT *
FROM SC
WHERE CId='02' AND SId NOT IN (SELECT SId FROM SC WHERE CId='01');
           

結果：

2. 查詢平均成績大于等于 60 分的同學的學生編号和學生姓名和平均成績

解答：

SELECT s.SId, s.Sname, t.av
FROM STUDENT AS s
JOIN
(SELECT SId, AVG(score) av
FROM SC
GROUP BY SId
HAVING AVG(score) >= 60) as t
ON s.SId = t.SId;
           

結果：

3. 查詢在 SC 表存在成績的學生資訊

解答：

SELECT *
FROM STUDENT
WHERE SId IN (SELECT DISTINCT(SId) FROM SC);
           

結果：

4. 查詢所有同學的學生編号、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )

解答：

SELECT S.SId, S.Sname, t.counts, t.sums
FROM STUDENT as S
LEFT JOIN 
(SELECT SId, COUNT(CId) as counts, SUM(score) as sums
FROM SC
GROUP BY SId) as t
ON S.SId = t.SId;
           

結果：

4.1 查有成績的學生資訊

解答：

SELECT *
FROM STUDENT
WHERE SId IN (SELECT DISTINCT(SId) FROM SC);
           

結果：

5. 查詢「李」姓老師的數量

解答：

SELECT COUNT(*)
FROM Teacher
WHERE Tname LIKE "李%";
           

結果：

6. 查詢學過「張三」老師授課的同學的資訊

解答：

SELECT * 
FROM STUDENT
WHERE SId IN
(SELECT SC.SId
FROM SC JOIN
	(SELECT C.CId, C.TId 
	FROM Course as C JOIN Teacher
	ON C.TId = Teacher.TId
	WHERE Teacher.Tname = "張三") as a
ON SC.CId = a.CId) ;
           

結果：

7. 查詢沒有學全所有課程的同學的資訊

解答：

SELECT * 
FROM STUDENT
WHERE SId NOT IN
(SELECT SId
FROM SC
GROUP BY SId
HAVING COUNT(CId) = (SELECT COUNT(CId) FROM Course));
           

答案：

8. 查詢至少有一門課與學号為" 01 "的同學所學相同的同學的資訊

11~20

21~30

31~40

41~50