Query Template
SELECT
N_NAME,
O_YEAR,
SUM(AMOUNT)
FROM
(
SELECT
N_NAME,
YEAR(O_ORDERDATE) AS O_YEAR,
L_EXTENDEDPRICE * (1 - L_DISCOUNT) - PS_SUPPLYCOST * L_QUANTITY AS AMOUNT
FROM
PART,
SUPPLIER,
LINEITEM,
PARTSUPP,
ORDERS,
NATION
WHERE
S_SUPPKEY = L_SUPPKEY
AND PS_SUPPKEY = L_SUPPKEY
AND PS_PARTKEY = L_PARTKEY
AND P_PARTKEY = L_PARTKEY
AND O_ORDERKEY = L_ORDERKEY
AND S_NATIONKEY = N_NATIONKEY
AND P_NAME LIKE '%GREEN%'
AND S_ACCTBAL :varies
AND PS_SUPPLYCOST :varies
) AS PROFIT
GROUP BY
N_NAME,
O_YEAR
ORDER BY
N_NAME,
O_YEAR DESC
Optimizer Diagrams
|