DB SQL測驗1(A卷)
4按供應商 由大到小排序，列出 SP 資料表中所有資料列。
SELECT S#,P#,QTY FROM SP ORDER BY S# DESC；
S#, P#, QTY
3 10 10
2 20 20
2 30 10
1 30 20
1 10 50
10-26 21:38:
測驗1(b卷)第2題
SELECT S# SUM(QTY)
GROUP BY P# FROM SP
1 10 60
1 30 30
2 20 20
10-26 21:41:
留言...
10-26 21:43:
SELECT S# FROM SP NOT IN (SELECT S# FROMS)
10-26 21:48:
SELECT S# FROM SP NOT IN (SELECT S# FROM S)
10-26 21:48:
DB SQL測驗1(A卷) 4按供應商 由大到小排序，列出 SP 資料表中所有資料列。 SELECT S#,P#,QTY FROM SP ORDER BY S# DESC； S#, P#, QTY 3 10 10 2 20 20 2 30 10 1 30 20 1 10 50
10-26 21:49:
B券第4題
SELECT S#
FROM S
ORDER BY SP DESC
2 30 10
2 20 10
1 30 20
1 10 50
10-27 09:54:
A卷第1題：
SELECT S# AS 供應商, P#, QTY AS 供應量
FORM SP
SAVE AS tempa
10-27 18:01:
A卷第5提SELECT S# FROM SP NOT IN (SELECT S# FROM S)
10-27 18:05:
A卷第五題SELECT S# FROM SP NOT IN (SELECT S# FROM S)
10-27 18:09:
A卷第1題： SELECT S# AS 供應商, P#, QTY AS 供應量 FORM SP SAVE AS tempa
10-27 18:35:
SELECT S# SUM(QTY) GROUP BY P# FROM SP 1 10 60 1 30 30 2 20 20
10-27 18:52:
(B卷)第四題：SELECT S#,P#,QTY FROM SP ORDER BY S# DESC； S#, P#, QTY 3 10 10 2 20 20 2 30 10 1 30 20 1 10 50
10-27 19:01:
A卷第1題(更正)： SELECT S# AS 供應商, P#, QTY AS 供應量 FROM SP SAVE AS tempa
10-27 19:03:
B卷第1題： SELECT S# AS 供應商, P#, QTY AS 供應量 FROM SP SAVE AS tempa
10-27 19:24:
(A卷)第2題 SELECT S# SUM(QTY) GROUP BY P# FROM SP 1 10 60 1 30 30 2 20 20
10-27 19:31:
SELECT SUM(lost*QTY)AS Total Group BY S WHERE P. P#=SP.R# AND Total<10,000
10-27 19:32:
(A卷)第2題 SELECT S# SUM(QTY) GROUP BY P# FROM SP 1 10 60 1 30 30 2 20 20
10-27 19:33:
(B卷)SELECT SUM(lost*QTY)AS Total Group BY S WHERE P. P#=SP.R# AND Total<10,000
10-27 19:34:
(B卷)第3題SELECT SUM(lost*QTY)AS Total Group BY S WHERE P. P#=SP.R# AND Total<10,000
10-27 19:36:
(B卷)第四題：SELECT S#,P#,QTY FROM SP ORDER BY S# DESC； S#, P#, QTY 3 10 10 2 20 20 2 30 10 1 30 20 1 10 50
10-27 19:42:
(A卷)第5題SELECT S# FROM SP NOT IN (SELECT S# FROM S)
10-27 20:05:
B卷第一題 SELECT S# AS 供應商, P#, QTY AS 供應量 FROM SP SAVE AS tempa
10-27 20:45:
感謝以上的同學回覆
A卷參考解答如下 (B卷請自行變換)
1.
SELECT S AS 供應商, P, QTY AS 供應量 INTO tempa
FROM SP;
2.
SELECT S, SUM(QTY) AS 總數
FROM SP
GROUP BY S;
3.
SELECT *
FROM [SELECT SP.S, SUM(Cost*QTY) AS Total
FROM P INNER JOIN SP ON P.P = SP.P
GROUP BY S]. AS [%$##@_Alias]
WHERE Total > 10000;
4.
SELECT *
FROM SP
ORDER BY S;
5.
SELECT *
FROM SP
WHERE S NOT IN (SELECT DISTINCT S FROM S);
10-27 20:46:
感謝以上的同學回覆
A卷參考解答如下 (B卷請自行變換)
1.
SELECT S AS 供應商, P, QTY AS 供應量 INTO tempa
FROM SP;
2.
SELECT S, SUM(QTY) AS 總數
FROM SP
GROUP BY S;
3.
SELECT *
FROM [SELECT SP.S, SUM(Cost*QTY) AS Total
FROM P INNER JOIN SP ON P.P = SP.P
GROUP BY S]. AS [%$##@_Alias]
WHERE Total > 10000;
4.
SELECT *
FROM SP
ORDER BY S;
5.
SELECT *
FROM SP
WHERE S NOT IN (SELECT DISTINCT S FROM S);
10-27 20:49:
感謝以上的同學回覆
A卷參考解答如下 (B卷請自行變換)
第1題.
SELECT S AS 供應商, P, QTY AS 供應量 INTO tempa
FROM SP;
第2題.
SELECT S, SUM(QTY) AS 總數
FROM SP
GROUP BY S;
第3題.
SELECT *
FROM (SELECT SP.S, SUM(Cost*QTY) AS Total
FROM P INNER JOIN SP ON P.P = SP.P
GROUP BY S)
WHERE Total > 10000;
第4題.
SELECT *
FROM SP
ORDER BY S;
第5題.
SELECT *
FROM SP
WHERE S NOT IN (SELECT DISTINCT S FROM S);
11-02 09:42:
A卷第1題： SELECT S# AS 供應商, P#, QTY AS 供應量 FROM SP SAVE AS tempa
11-02 19:57:
目前有22位同學作答，其餘同學請加油！
11-02 21:01:
SELECT * FROM (SELECT SP.S, SUM(Cost*QTY) AS Total FROM P INNER JOIN SP ON P.P = SP.P GROUP BY S) WHERE Total <15000 10 12600 20 6000 30 3150
11-02 21:06:
SELECT S# FROM SP NOT IN (SELECT S# FROMS)
11-02 21:10:
A卷第2題(正確解答):SELECT P, SUM(QTY) AS 總數 FROM SP GROUP BY P;
11-02 21:11:
A卷第4題(正確解答):SELECT * FROM SP ORDER BY S DESC;
11-03 12:02:
更正SELECT * FROM (SELECT SP.S, SUM(Cost*QTY) AS Total FROM P INNER JOIN SP ON P.P = SP.P GROUP BY S) WHERE Total <15000 1 12600 2 7050 3 2100
