Query Template
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
Optimizer Diagrams
|