---
tags: 分享
---

# 0310-資料庫基本語法（一）各自解法 1-4 題

## 第一題：求每門課程的學生人數?

// 第一題丟這裡

// DD:
SELECT
  CId,
  COUNT( SId ) AS SId 
FROM 
  SC 
GROUP BY
  CId

// Jane:
SELECT `CId`,COUNT(`sId`) as sid from sc 
GROUP BY `CId`;

// RJ
SELECT CId,COUNT(SId) AS SId FROM `sc` GROUP BY CId;

// MING
SELECT CID,COUNT(CID) FROM `SC` GROUP BY `CID`;

// Leon
Select Cid,count(Sid)as Sid from sc
group by Cid

// Hong-Shao
select cid, count(sid) as sid from SC
group by cid;

## 第二題：查詢選修了大於等於三堂課的學生資訊

// 第二題丟這裡

// DD
SELECT
  SC.SId,
  COUNT( CId ) AS "class_num" ,
  Sname AS "姓名",
  sex AS "性別"
FROM
  SC INNER JOIN Student  ON SC.SId=Student.SId
GROUP BY
  SId 
HAVING
  COUNT( CId ) >= 3

// Jane:
SELECT a.sid,COUNT(a.SId) as class_num,b.Sname,b.sex from sc as a,student as b 
WHERE a.SId=b.SId 
GROUP By a.SId 
HAVING COUNT(a.SId)>=3;

// RJ
SELECT sc.SId,COUNT(sc.CId) AS class_num,student.Sname AS 姓名,student.sex AS 性別
FROM `sc` LEFT JOIN student ON sc.SId = student.SId
GROUP BY sc.SId
HAVING COUNT(sc.CId) >= 3;

// MING
SELECT `sc`.`SId`,COUNT(`sc`.`CId`),`student`.`Sname`,`student`.`sex` FROM `SC`,`student` WHERE `sc`.`SId`=`student`.`SId` GROUP BY `sc`.`SId` HAVING COUNT(`sc`.`CId`)>=3; 

// Hong-Shao
select counter.sid, number, sname from (
select sid, count(cid) as number from SC
group by sid having number >= 3) as counter 
join student on counter.sid = student.sid;

## 第三題：查詢「李」姓老師的數量

// 第三題丟這裡

// DD
SELECT
  * 
FROM
  Teacher 
WHERE
  Tname LIKE '李%'

// Jane:
SELECT Tid,Tname as 姓名 FROM `teacher`
WHERE Tname LIKE "李%";

//RJ
SELECT teacher.TId,teacher.Tname AS 姓名 FROM `teacher` WHERE Tname LIKE "李%";

// MING
SELECT `teacher`.`TId`,`teacher`.`Tname` FROM `teacher` WHERE `teacher`.`Tname` LIKE "李%"; 

// Hong-Shao
select * from Teacher where tname like "李%";

## 第四題：查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名

// 第四題丟這裡

// DD
SELECT
  SC.SId,
  SC.CId,
  score AS "成績",
  Sname AS "姓名"
FROM
  SC INNER JOIN Student ON  SC.SId=Student.SId
WHERE
  CId = 01 
  AND score >= 80

// Jane
SELECT a.Sid,b.Cid,b.score,a.Sname from student as a LEFT JOIN sc as b ON a.SId=b.sId 
WHERE (b.Cid="01") AND (b.score>=80);

// RJ
SELECT sc.SId,sc.CId,sc.score AS 成績, student.Sname AS 姓名
FROM `sc`,student
WHERE sc.SId = student.SId AND CId = '01' AND score >=80;

// MING
SELECT `sc`.`SId`,`sc`.`CId`,`sc`.`score`,`student`.`Sname` 
FROM `SC`,`student` 
WHERE `sc`.`SId`=`student`.`SId` AND `sc`.`CId`='01' AND`sc`.`score`>=80;

// Hong-Shao
select student.sid, sname from 
(select * from sc where cid = 1 and score >= 80) as filtered
join student on filtered.sid = student.sid;