MSSQL Create Insert Select Update GROUP BY(總和)

 Create

create table "student"

(s_no nvarchar(80) NULL, 

s_name nvarchar(80) NULL,

s_age nvarchar(80) NULL)

create table "subject"

(s_no nvarchar(80) NULL, 

subject nvarchar(80) NULL,

score nvarchar(80) NULL)

Insert

insert into student (s_no, s_name, s_age) 

values  (N'A1', N'JOHN', 16), (N'A2', N'JOE', 16), (N'A3', N'TONY', 17)  

Select

select*from student

insert into subject (s_no, subject, score) 

values  (N'A1', N'Math' ,58), (N'A1', N'History', 70), (N'A2', N'English' ,49) , (N'A2', N'History' ,90), (N'A3', N'Math', 35), (N'A3', N'English', 77) 

select*from subject

查詢TableA、B，利用關聯s_no做組合查詢

SELECT A.s_no, A.s_name, A.s_age, B.subject, B.score

  FROM student A

  JOIN subject B ON A.s_no=B.s_no

一、撈出JOE的名字、年紀、及格科目。

SELECT 欄位1,欄位2,欄位3

FROM TableName1 代稱A  JOIN TableName2 代稱B ON A.共同編號=B.共同編號

WHERE 欄位='表格內字串'and score>=60   //提供條件，用and增加條件

SELECT s_name,s_age,subject,score

FROM student A JOIN subject B ON A.s_no=B.s_no

WHERE s_name='JOHN'and score>=60

二、教授讓給JOHN一個機會，讓他的數學(Math)及格，請寫出update語法讓JOHN及格。

UPDATE "表格名"

SET "欄位1" = [新值]

WHERE "條件";

UPDATE subject

SET score=60

FROM student A JOIN subject B ON A.s_no=B.s_no

WHERE s_name='JOHN' and subject='Math'

三、算出TABLE subject所有科目各別的分數總和（GROUP BY）

SELECT "欄位1", SUM("欄位2")

FROM "表格名"

GROUP BY "欄位1";

SELECT subject,sum(score) as 總和

from subject

group by subject