--1:把SC表中每門課程的平均成績插入到另外一個已經存在的表SC_C(CNO,CNAME,AVG_GRADE)中,其中AVG_GRADE表示每門課程的平均成績
INSERT INTO SC_C (CNO, CNAME, AVG_GRADE) SELECT
SC.CNO,
C.CNAME,AVG(SC.SCGRADE)
FROM
`SC`
INNER JOIN C ON C.CNO = SC.CNO
GROUP BY
SC.CNO;--2:從SC表中把選擇何炅老師的課程的女生的選課記錄刪除
DELETE
FROM
SC,
S,
C
WHERE
SC.SNO = S.SNO
AND SC.CNO = C.CNO
AND C.CTEACHER ='何炅老師'--3:查詢選何炅老師的課程的女生且成績在80分以上
SELECT
S.*
FROM
S
INNER JOIN SC ON SC.SNO = S.SNO
INNER JOIN C ON C.CNO = SC.CNO
WHERE
C.CTEACHER ='何炅老師'
AND SC.SCGRADE >80
AND S.SEX =0--4:找出沒有選修過何炅老師的課程的所有學生的姓名(兩種寫法)
SELECT
S.SNAME
FROM
S
WHERE
S.SNO NOT IN (
SELECT
S.SNO
FROM
S
INNER JOIN SC ON SC.SNO = S.SNO
INNER JOIN C ON C.CNO = SC.CNO
WHERE
C.CTEACHER ='何炅老師')
SELECT
SNAME
FROM
S
WHERE
NOT EXISTS (
SELECT
*
FROM
SC,
C
WHERE
SC.CNO = SC.CNO
AND SC.SNO = S.SNO
AND C.CTEACHER ='何炅老師')--5:列出有兩門以上(包含兩門)不及格課程(成績小於60分)的學生的姓名及其平均成績(兩種方式)
SELECT
S.SNAME,AVG(sc.SCGRADE)
FROM
SC
INNER JOIN S ON S.SNO = sc.SNO
WHERE
sc.SNO IN (
SELECT
sc.SNO
FROM
SC
WHERE
SC.SCGRADE <60
GROUP BY
sc.SNO
HAVING
COUNT(1)>=2)
GROUP BY
sc.SNO;
SELECT
S.SNAME,AVG(sc.SCGRADE)
FROM
s,
sc,(
SELECT
sc.SNO
FROM
SC
WHERE
SC.SCGRADE <60
GROUP BY
sc.SNO
HAVING
COUNT(1)>=2) A
WHERE
A.SNO = s.SNO
AND s.SNO = sc.SNO
GROUP BY
sc.SNO
--6:列舉出即學過1(科學)又學過2(程式設計)的所有學生姓名(兩種方式)
SELECT
s.SNAME
FROM
s,
sc
WHERE
s.sno = sc.SNO
AND sc.CNO =2
AND sc.SNO IN (
SELECT
sc.SNO
FROM
sc
WHERE
sc.CNO =1)
SELECT
s.SNAME
FROM
s,(
SELECT
SNO
FROM
sc
WHERE
CNO IN (1,2)
GROUP BY
SNO
HAVING
count(CNO)>=2) AS a
WHERE
s.sno = a.SNO
--7:列出1(科學)成績比2(程式設計)的學生成績高的所有學生的學號和姓名
SELECT
s.sno,
s.SNAME
FROM
s,(
SELECT
sc.SNO,
sc.SCGRADE
FROM
sc
WHERE
sc.CNO =1) k,(
SELECT
sc.SNO,
sc.SCGRADE
FROM
sc
WHERE
sc.CNO =2) b
WHERE
s.SNO = k.SNO
AND k.SNO = b.SNO
AND k.SCGRADE > b.SCGRADE
--8:列出1(科學)成績比2(程式設計)的學生成績高的所有學號及其1號課(科學)和2號課(程式設計)的成績
SELECT
s.sno,
s.SNAME,
k.SCGRADE AS "1",
b.SCGRADE AS "2"
FROM
s,(
SELECT
sc.SNO,
sc.SCGRADE
FROM
sc
WHERE
sc.CNO =1) k,(
SELECT
sc.SNO,
sc.SCGRADE
FROM
sc
WHERE
sc.CNO =2) b
WHERE
s.SNO = k.SNO
AND k.SNO = b.SNO
AND k.SCGRADE > b.SCGRADE