SQL 三表聯合查詢

CREATE TABLE student(
student_id INT,
student_name VARCHAR(30),
student_age INT,
student_sex VARCHAR(6)
)
INSERT INTO student VALUES(100,‘小張’,20,‘男’)；
INSERT INTO student VALUES(101,‘小白’,20,‘女’)；
INSERT INTO student VALUES(102,‘小張’,20,‘男’)；
INSERT INTO student VALUES(103,‘小李’,20,‘男’)；
INSERT INTO student VALUES(104,‘小紅’,20,‘女’)；

---

CREATE TABLE score(
student_id INT,
course_id INT,
score INT
)
INSERT INTO score VALUES(100,300,80)
INSERT INTO score VALUES(104,300,90)
INSERT INTO score VALUES(102,300,70)
INSERT INTO score VALUES(101,301,70)
INSERT INTO score VALUES(103,302,60)
INSERT INTO score VALUES(101,301,50)
INSERT INTO score VALUES(104,302,70)

---

CREATE TABLE course{
course_id INT,
course_name VARCHAR(30),
teacher_id)

INSERT INTO course VALUES(300,‘高數’,200)
INSERT INTO course VALUES(301,‘英語’,201)
INSERT INTO course VALUES(302,‘計算機基礎’,209)

---

SELECT s.student_id,s.student_name,s.student_age,s.student_sex,c.score,
a.course_name FROM student s INNER JOIN score c ON
s.student_id =c.student_id INNER JOIN course a ON
a.course_id = c.course_id WHERE c.score<60;