q9

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

plan_diag

reduced_diag

cost_diag

card_diag

plan1

plan2

plan3

plan4

plan5

Album last updated on 5/19/07 1:09 AM