2小時解不完的資料庫練習題，來挑戰一下吧！

寫在前面

我已經記不起來，有多久沒更新文章了。

5月中旬我還在上班，中旬以後一系列發生的事情，真的遠遠超出了可承受範圍，只能硬著頭皮面對！

我是誰，我應該是誰，又能怎樣，只能向前·····

資料庫範例

class表

course表

score表

student表

teacher表

實際語句

1、查詢所有的課程的名稱以及對應的任課老師姓名

# 自連結  
SELECT c.name,t.name FROM course c,teacher t WHERE c.teacher_id=t.id  
# 內連線  
SELECT course.name,teacher.name FROM course  
INNER JOIN teacher ON course.teacher_id=teacher.id

結果：

2、查詢學習課程"資料結構"比課程"java語言"成績低的學生的學號；

# 內連線  
SELECT shuju.student_id FROM  
(SELECT score.course_id,  
score.student_id,  
score.mark  
FROM score  
INNER JOIN course  
ON score.course_id=course.id  
WHERE course.name='資料結構') AS shuju  
INNER JOIN  
(SELECT score.course_id,  
score.student_id,  
score.mark  
FROM score  
INNER JOIN course  
ON score.course_id=course.id  
WHERE course.name='java') AS java  
ON shuju.student_id=java.student_id  
WHERE shuju.mark<java.mark  
  
# 自連線  
SELECT shuju.student_id  
FROM  
(SELECT s.course_id,  
s.student_id,  
s.mark  
FROM score s, course c  
WHERE c.`name`='資料結構'  
AND s.course_id=c.id) shuju,  
(SELECT s.course_id,  
s.student_id,  
s.mark  
FROM score s, course c  
WHERE c.`name`='java'  
AND s.course_id=c.id) java  
WHERE shuju.student_id=java.student_id  
AND shuju.mark<java.mark

結果：

3、查詢平均成績大於65分的同學的id和平均成績(保留兩位小數)

SELECT score.student_id,  
round(AVG(score.mark),2) AS avgScore  
FROM score  
GROUP BY score.student_id  
HAVING avgScore>65

結果：

4、查詢平均成績大於65分的同學的姓名和平均成績(保留兩位小數)

SELECT student.`name`,  
ROUND(AVG(score.mark),2) AS avgScore  
FROM score  
INNER JOIN student  
ON student.id=score.student_id  
GROUP BY score.student_id  
HAVING avgScore>65

結果：

5、查詢所有同學的姓名、選課數、總成績

SELECT student.name AS '名字', COUNT(score.course_id) AS '選課數',SUM(score.mark) AS '總成績'FROM score
INNER JOIN student
ON student.id=score.student_id
GROUP BY  student_id

結果：

6、查詢沒學過"大牛"老師課的同學的姓名

select student.name from student  
where id not in(select student_id from score where course_id in(select course.id from course inner join teacher  
on course.teacher_id = teacher.id where teacher.name='大牛'))

結果：

7、查詢學過"大牛"老師所教的全部課程的同學的姓名

select student.name from student  
where id in(select student_id from score where course_id in(3,3))

結果：

8、查詢所有課程成績小於60分的同學的姓名

select student.name from student inner join score on student.id = score.student_id  
where score.mark<60 group by score.student_id

結果：

9、查詢選修了全部課程的學生姓名

select student.name from student  
where id in (select score.student_id from score group by score.student_id having count(1)=(select count(1) from course))

結果：

10、查詢至少有一門課程與"小草"同學所學課程相同的同學姓名

SELECT student.name
FROM student
WHERE id IN 
    (SELECT student_id
    FROM score
    WHERE course_id IN 
        (SELECT course_id
        FROM score
        WHERE student_id=5))
            AND student.name!='小草'

結果：

11、查詢至少有一門課程和"小草"同學所學課程不相同的同學姓名

select student.name from student  
where id in (select student_id from score  
where course_id not in (select course_id from score  
where student_id=5)) and student.name!='小草'

結果：

12、查詢各科成績最高和最低的分：以如下形式顯示：課程id，最高分，最低分

select course_id as '課程id',max(mark) as '最高分',min(mark) as '最低分'from score group by course_id

結果：

13、查詢只選修了一門課程的學生的學號和姓名

# 感覺有點low，但是能查出來  
select student.id as '學號',student.name as '姓名'from student inner join score on student.id = score.student_id  
where student.id=(select student_id from score group by student_id having count(course_id)=1)

# 這個好一些  
select student.id as '學號',student.name as '姓名'from student inner join score on student.id = score.student_id  
group by student_id having count(course_id)=1

結果：

14、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程id降序排列

select course.id as '課程id',course.name AS "課程名稱",avg(mark) as "平均成績" from course  
inner JOIN score on course.id = score.course_id  
group by course_id order by avg(mark) ,"平均成績",course_id desc

結果：

15、按平均成績倒序顯示所有學生的"資料庫原理"、「java語言」、"C語言"三門的課程成績，

按如下形式顯示： 學生id、資料庫原理、java語言、C語言、課程數、平均分；(高階應用較難)

select sc.student_id as '學生id',  
(select mark from score inner join course on course.id=score.course_id where course.name='資料庫原理' and score.student_id=sc.student_id) as '資料庫原理',  
(select mark from score inner join course on course.id=score.course_id where course.name='java' and score.student_id=sc.student_id) as 'java',  
(select mark from score inner join course on course.id=score.course_id where course.name='C語言' and score.student_id=sc.student_id) as 'C語言',  
count(course_id) as '課程數',  
round(avg(sc.mark),2) as '平均分'  
from score as sc group by sc.student_id  
order by avg(sc.mark) desc

結果：

寫在最後

整個資料庫這部分的複習，早在近一個月前就開始了。

在做了兩道題後，就遇到了各種事情，就被擱置了，差點被遺忘了。。。

今天有時間，接著把學習的感覺續上，總體下來，算是初步複習了下sql的一些常用查詢操作，就一個測試仔來說，我個人感覺這些都能寫正確寫出來，真的很厲害，我也是用了近6小時呢。

不管遇到了什麼難事，學習、跑步都不能停（我又胖了5斤，好扎心）.....

明天繼續我的5公里，加油！