-- Where NOT Exists (Select p from sp Where p.p = sp.p);
Select * from p
Where p NOT IN (Select p FROM sp);
7. 自己想吧！
-- 第 ７ 題 下列SQL可完成什麼？
SELECT sp.s, SUM(QTY*cost) AS Total
FROM sp, p
WHERE sp.p = p.p
GROUP BY sp.s
ORDER BY Total DESC;
8.
-- 8. 列出有訂購30號產品者(P=30)的S 與addr (住址)。
SELECT *
FROM s
WHERE s IN (SELECT s FROM sp WHERE p IN (
SELECT p
FROM sp
WHERE p = '30'));
9. -- 9. 列出訂購總金額加總為前三名者的S#,P#,qty,cost,與訂購總金額加總
SELECT *, QTY*cost AS Total
FROM sp JOIN p
ON sp.p = p.p
ORDER BY Total DESC
LIMIT 3;
10.
-- 10. 列出 未有訂購產品 的廠商編號與地址。
SELECT *
FROM s
WHERE s.s NOT IN (SELECT sp.s FROM sp);
11.
-- 11. 列出 有訂購產品 的廠商編號 (s) 與地址 (Addr)，及其訂購的商品編號(p)、品名(name)，價格 (Cost)，和數量(QTY)，以及各項商品的訂購金額(QTY*cost)。
SELECT *
FROM
s AS tb1
JOIN sp AS tb2 ON tb1.s = tb2.s
JOIN p AS tb3 ON tb2.p = tb3.p;
全部共 34則留言
04-02 15:13:
-- Executing query:
SELECT SP_S AS 供應商,SP_P ,SP_QTY AS 供應量
FROM sporder;
Total query runtime: 11 ms.
5 rows retrieved.
-- Executing query:
SELECT SP_S AS 供應商,SP_P ,SP_QTY AS 供應量
FROM sporder;
Total query runtime: 11 ms.
5 rows retrieved.
04-02 15:14:
(羅老師)
-- Executing query:
SELECT SP_S AS 供應商,SP_P ,SP_QTY AS 供應量
FROM sporder;
Total query runtime: 11 ms.
5 rows retrieved.
-- Executing query:
SELECT SP_S AS 供應商,SP_P ,SP_QTY AS 供應量
FROM sporder;
Total query runtime: 11 ms.
5 rows retrieved.
04-09 13:48:
(羅老師)SELECT sp_p,SUM(sp_qty) AS "供應量加總"
FROM sporder
GROUP BY sp_p
ORDER BY sp_p;
Total query runtime: 10 ms.
4 rows retrieved.
04-09 13:48:
(羅老師)SELECT sp_p,SUM(sp_qty) AS "供應量加總"
FROM sporder
GROUP BY sp_p
ORDER BY sp_p;
Total query runtime: 10 ms.
4 rows retrieved.
04-09 14:03:
(羅老師第4題)-- Executing query:
SELECT * FROM sporder
ORDER BY sp_s DESC;
Total query runtime: 11 ms.
7 rows retrieved.
04-09 14:10:
(羅老師第5題) -- Executing query:
DELETE FROM sporder
WHERE sporder.sp_s NOT IN (
SELECT supplier.s FROM sporder, supplier
WHERE sporder.sp_s = supplier.s);
Query returned successfully: 0 rows affected, 11 ms execution time.
04-09 14:15:
(羅老師第6題) -- Executing query:
Select * from product
Where p NOT In (Select sp_p from sporder
Where product.p = sporder.sp_p);
Total query runtime: 11 ms.
2 rows retrieved.
04-09 14:19:
(羅老師第7題 輸出結果的意義自己想！！) -- Executing query:
SELECT sporder.sp_s,SUM(sp_qty*cost) AS Total FROM sporder , product
WHERE sporder.sp_p = product.p
GROUP BY sporder.sp_s
ORDER BY Total DESC;
Total query runtime: 11 ms.
3 rows retrieved.
04-09 14:35:
(羅老師第 9 題， 您會不會找出前五名？) -- Executing query:
SELECT *,sp_qty*cost AS Total
FROM sporder JOIN product
ON sporder.sp_p = product.p
ORDER BY Total DESC
LIMIT 3;
Total query runtime: 11 ms.
3 rows retrieved.
04-09 14:40:
(羅老師 第 10 題) -- Executing query:
SELECT *
FROM supplier
WHERE supplier.s NOT IN
(SELECT sporder.sp_s FROM sporder);
Total query runtime: 11 ms.
1 row retrieved.
04-09 14:40:
(羅老師 第3題) -- Executing query:
SELECT sp_s,SUM(sp_qty*cost) AS Total
FROM sporder sp JOIN product p
ON sp.sp_p = p.p
GROUP BY sp_s
HAVING SUM(sp_qty*cost) > 120000
ORDER BY Total DESC;
Total query runtime: 10 ms.
04-09 14:42:
楊羚 B卷 第一題
SELECT "S#" AS "供應商","P#" ,"QTY" AS "供應量"
INTO tempb
FROM "SP"
SELECT *
FROM "tempb"
04-09 14:56:
(羅老師 第 1 題) -- Executing query:
SELECT sp_s AS "供應廠商",sp_p ,sp_qty AS "供應量"
INTO tempa5
FROM sporder;
SELECT "供應廠商", sp_p, "供應量"
FROM tempa5;
Total query runtime: 31 ms.
7 rows retrieved.
04-09 15:12:
(9914D156) SELECT sp_p,SUM(sp_qty) AS "供應量加總" FROM sporder GROUP BY sp_p ORDER BY sp_p;
04-09 15:20:
(黃秉聖) Executing query: SELECT * FROM sporder ORDER BY sp_s DESC; Total query runtime: 11 ms. 7 rows retrieved.
04-09 15:21:
(9914D159)DELETE FROM sporder WHERE sporder.sp_s NOT IN ( SELECT supplier.s FROM sporder, supplier WHERE sporder.sp_s = supplier.s); Query returned successfully: 0 rows affected, 11 ms execution time.
04-09 15:21:
SELECT sp_p,(9914D151)SUM(sp_qty) AS "供應量加總" FROM sporder GROUP BY sp_p ORDER BY sp_p; Total query runtime: 10 ms. 4 rows retrieved.
04-09 15:21:
(99143061 第2題)SELECT P#,SUM(qty) AS '供應量加總'
FROM SP
GROUP BY P#
04-09 15:22:
(9914D001 第2題)SELECT P#,SUM(qty) AS '供應量加總'
FROM SP
GROUP BY P#
04-09 15:22:
(9914D117) SELECT sp_s,SUM(sp_qty*cost) AS Total FROM sporder sp JOIN product p ON sp.sp_p = p.p GROUP BY sp_s HAVING SUM(sp_qty*cost) > 10000 ORDER BY Total DESC;
04-09 15:22:
(9914D145) Executing query: SELECT SP_S AS 供應商,SP_P ,SP_QTY AS 供應量 FROM sporder; Total query runtime: 11 ms. 5 rows retrieved. -- Executing query: SELECT SP_S AS 供應商,SP_P ,SP_QTY AS 供應量 FROM sporder;
04-09 15:23:
(9914D151)SELECT sp_p,SUM(sp_qty) AS "供應量加總" FROM sporder GROUP BY sp_p ORDER BY sp_p; Total query runtime: 10 ms. 4 rows retrieved.
04-09 15:24:
(9914D154) DELETE FROM sp WHERE sp.sp_s NOT IN ( SELECT supply.s FROM sp, supply WHERE sp.sp_s = supply.s); Query returned successfully: 0 rows affected, 11 ms execution time.
04-09 15:27:
(9914D134) B卷 第五題
-- Executing query:
SELECT *
FROM supply
WHERE supply.s NOT IN (SELECT sporder.s FROM sporder);
Total query runtime: 11 ms.
1 rows retrieved.
04-11 11:12:
(10014D152 第1題) -- Executing query:
SELECT sp_s AS "供應廠商",sp_p ,sp_qty AS "供應量" FROM sporder
WHERE sp_s >= '2';
Total query runtime: 11 ms.
4 rows retrieved.
04-23 15:29:
9914d128 田鈞帆 b卷第4題
SELECT *
FROM SP
ORDER BY S# DESC
04-23 15:43:
(9914d142) 第三題 SELECT sp_order.s,SUM(sp_order.qty * item.p_cost)
FROM sp_order JOIN item ON sp_order.p = item.p_id
GROUP BY sp_order.s
HAVING SUM(sp_order.qty * item.p_cost) > 10000
07-06 11:35:
USE 學生
SELECT S# AS 供應商,P# ,QTY AS 供應量
INTO tempa2
FROM SP
SELECT *
FROM tempa2
-- SP 資料表中，各P#(產品編號)之供應量加總
SELECT P#,SUM(qty) AS '供應量加總'
FROM SP
GROUP BY P#
--3列出 供應商之供應總金額加總(為P#之供應量*成本價Cost)大於10,000者之供應商。
SELECT S#,SUM(qty*cost) AS Total
FROM SP JOIN P
ON SP.P# = P.P#
GROUP BY S#
HAVING SUM(qty*cost) > 10000
ORDER BY Total DESC
--4.按供應商 由大到小排序，列出 SP 資料表中所有資料列
SELECT *
FROM SP
ORDER BY S# DESC
--5.因為 Relation SP 中，有一個tuple之 S#為3，參考到S relation 中為null，
--故不合referential integrity，請寫SQL更正此一情形。
DELETE
FROM SP
WHERE SP.S# NOT IN
(SELECT S.S# FROM SP,S
WHERE SP.S# = S.S#);
SELECT P.P#,SUM((qty*cost)) AS Total,COUNT(*) AS 筆數
FROM SP JOIN P
ON P.P#=SP.P#
GROUP BY P.P#
Select * from P
Where NOT Exists (Select P# from SP
Where P.P#=SP.P#)
Select * from P
Where P# NOT IN (Select P# FROM SP)
SELECT SP.S#,SUM(qty*cost) AS Total FROM SP , P
WHERE SP.P#=P.P# GROUP BY SP.S# ORDER BY Total DESC
SELECT *
FROM S
WHERE S# IN (SELECT S# FROM SP WHERE P# IN (SELECT P#
FROM SP
WHERE P# = '30'))
SELECT TOP 3 *,qty*cost AS Total
FROM SP JOIN P
ON SP.P# = P.P#
ORDER BY Total DESC
--10.列出 未有訂購產品 的廠商編號與地址。
SELECT *
FROM S
WHERE S.S# NOT IN (SELECT SP.S# FROM SP)
07-06 11:35:
USE 學生
SELECT S# AS 供應商,P# ,QTY AS 供應量
INTO tempa2
FROM SP
SELECT *
FROM tempa2
-- SP 資料表中，各P#(產品編號)之供應量加總
SELECT P#,SUM(qty) AS '供應量加總'
FROM SP
GROUP BY P#
--3列出 供應商之供應總金額加總(為P#之供應量*成本價Cost)大於10,000者之供應商。
SELECT S#,SUM(qty*cost) AS Total
FROM SP JOIN P
ON SP.P# = P.P#
GROUP BY S#
HAVING SUM(qty*cost) > 10000
ORDER BY Total DESC
--4.按供應商 由大到小排序，列出 SP 資料表中所有資料列
SELECT *
FROM SP
ORDER BY S# DESC
--5.因為 Relation SP 中，有一個tuple之 S#為3，參考到S relation 中為null，
--故不合referential integrity，請寫SQL更正此一情形。
DELETE
FROM SP
WHERE SP.S# NOT IN
(SELECT S.S# FROM SP,S
WHERE SP.S# = S.S#);
SELECT P.P#,SUM((qty*cost)) AS Total,COUNT(*) AS 筆數
FROM SP JOIN P
ON P.P#=SP.P#
GROUP BY P.P#
Select * from P
Where NOT Exists (Select P# from SP
Where P.P#=SP.P#)
Select * from P
Where P# NOT IN (Select P# FROM SP)
SELECT SP.S#,SUM(qty*cost) AS Total FROM SP , P
WHERE SP.P#=P.P# GROUP BY SP.S# ORDER BY Total DESC
SELECT *
FROM S
WHERE S# IN (SELECT S# FROM SP WHERE P# IN (SELECT P#
FROM SP
WHERE P# = '30'))
SELECT TOP 3 *,qty*cost AS Total
FROM SP JOIN P
ON SP.P# = P.P#
ORDER BY Total DESC
--10.列出 未有訂購產品 的廠商編號與地址。
SELECT *
FROM S
WHERE S.S# NOT IN (SELECT SP.S# FROM SP)
07-06 11:36:
USE AAAA
SELECT S# AS 供應商,P# ,QTY AS 供應量
INTO tempa4
FROM SP
SELECT *
FROM tempa4
-- SP 資料表中，各P#(產品編號)之供應量加總
SELECT P#,SUM(qty) AS '供應量加總'
FROM SP
GROUP BY P#
--3列出 供應商之供應總金額加總(為P#之供應量*成本價Cost)大於10,000者之供應商。
SELECT S#,SUM(qty*cost) AS Total
FROM SP JOIN P
ON SP.P# = P.P#
GROUP BY S#
HAVING SUM(qty*cost) > 10000
ORDER BY Total DESC
--4.按供應商 由大到小排序，列出 SP 資料表中所有資料列
SELECT *
FROM SP
ORDER BY S# DESC
--5.因為 Relation SP 中，有一個tuple之 S#為3，參考到S relation 中為null，
--故不合referential integrity，請寫SQL更正此一情形。
DELETE
FROM SP
WHERE SP.S# NOT IN
(SELECT S.S# FROM SP,S
WHERE SP.S# = S.S#);
SELECT P.P#,SUM((qty*cost)) AS Total,COUNT(*) AS 筆數
FROM SP JOIN P
ON P.P#=SP.P#
GROUP BY P.P#
Select * from P
Where NOT Exists (Select P# from SP
Where P.P#=SP.P#)
Select * from P
Where P# NOT IN (Select P# FROM SP)
SELECT SP.S#,SUM(qty*cost) AS Total FROM SP , P
WHERE SP.P#=P.P# GROUP BY SP.S# ORDER BY Total DESC
SELECT *
FROM S
WHERE S# IN (SELECT S# FROM SP WHERE P# IN (SELECT P#
FROM SP
WHERE P# = '30'))
SELECT TOP 3 *,qty*cost AS Total
FROM SP JOIN P
ON SP.P# = P.P#
ORDER BY Total DESC
--10.列出 未有訂購產品 的廠商編號與地址。
SELECT *
FROM S
WHERE S.S# NOT IN (SELECT SP.S# FROM SP)
07-06 11:37:
USE AAAA
SELECT S# AS 供應商,P# ,QTY AS 供應量
INTO tempa4
FROM SP
SELECT *
FROM tempa4
-- SP 資料表中，各P#(產品編號)之供應量加總
SELECT P#,SUM(qty) AS '供應量加總'
FROM SP
GROUP BY P#
--3列出 供應商之供應總金額加總(為P#之供應量*成本價Cost)大於10,000者之供應商。
SELECT S#,SUM(qty*cost) AS Total
FROM SP JOIN P
ON SP.P# = P.P#
GROUP BY S#
HAVING SUM(qty*cost) > 10000
ORDER BY Total DESC
--4.按供應商 由大到小排序，列出 SP 資料表中所有資料列
SELECT *
FROM SP
ORDER BY S# DESC
--5.因為 Relation SP 中，有一個tuple之 S#為3，參考到S relation 中為null，
--故不合referential integrity，請寫SQL更正此一情形。
DELETE
FROM SP
WHERE SP.S# NOT IN
(SELECT S.S# FROM SP,S
WHERE SP.S# = S.S#);
SELECT P.P#,SUM((qty*cost)) AS Total,COUNT(*) AS 筆數
FROM SP JOIN P
ON P.P#=SP.P#
GROUP BY P.P#
Select * from P
Where NOT Exists (Select P# from SP
Where P.P#=SP.P#)
Select * from P
Where P# NOT IN (Select P# FROM SP)
SELECT SP.S#,SUM(qty*cost) AS Total FROM SP , P
WHERE SP.P#=P.P# GROUP BY SP.S# ORDER BY Total DESC
SELECT *
FROM S
WHERE S# IN (SELECT S# FROM SP WHERE P# IN (SELECT P#
FROM SP
WHERE P# = '30'))
SELECT TOP 3 *,qty*cost AS Total
FROM SP JOIN P
ON SP.P# = P.P#
ORDER BY Total DESC
--10.列出 未有訂購產品 的廠商編號與地址。
SELECT *
FROM S
WHERE S.S# NOT IN (SELECT SP.S# FROM SP)
07-06 13:52:
Good!
10-26 19:43:
同時查詢多個 tables
SELECT *
FROM s, p, sp
WHERE s.s = sp.s AND p.p = sp.p;
10-26 19:47:
SELECT sp.s, sp.QTY, p.p, p.cost, SUM( QTY * cost ) AS Total
FROM sp, p
WHERE sp.p = p.p
GROUP BY sp.p, sp.s
ORDER BY Total DESC 第七題用這段可以看得更清楚
