q7

SELECT
    SUPP_NATION,
    CUST_NATION,
    L_YEAR,
    SUM(VOLUME)
FROM
    (
        SELECT
            N1.N_NAME AS SUPP_NATION,
            N2.N_NAME AS CUST_NATION,
            YEAR (L_SHIPDATE) AS L_YEAR,
            L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME
        FROM
            SUPPLIER,
            LINEITEM,
            ORDERS,
            CUSTOMER,
            NATION N1,
            NATION N2
        WHERE
            S_SUPPKEY = L_SUPPKEY
            AND O_ORDERKEY = L_ORDERKEY
            AND C_CUSTKEY = O_CUSTKEY
            AND S_NATIONKEY = N1.N_NATIONKEY
            AND C_NATIONKEY = N2.N_NATIONKEY
            AND (
                (N1.N_NAME = 'FRANCE' AND N2.N_NAME = 'GERMANY')
                OR (N1.N_NAME = 'GERMANY' AND N2.N_NAME = 'FRANCE')
            )
            AND L_SHIPDATE BETWEEN '1995-01-01' AND '1996-12-31'
            AND O_TOTALPRICE :varies
            AND C_ACCTBAL :varies
    )AS SHIPPING
GROUP BY
    SUPP_NATION,
    CUST_NATION,
    L_YEAR
ORDER BY
    SUPP_NATION,
    CUST_NATION,
    L_YEAR

plan_diag

reduced_diag

cost_diag

card_diag

plan1

plan2

plan3

plan4

plan5

plan6

plan7

plan8

plan9

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