Q7:
select
	supp_nation,
	cust_nation,
	l_year,
	sum(volume) as revenue
from
	(
		select
			n1.n_name as supp_nation,
			n2.n_name as cust_nation,
			extract(year from 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 date '1995-01-01' and date '1996-12-31'
	) as shipping
group by
	supp_nation,
	cust_nation,
	l_year
order by
	supp_nation,
	cust_nation,
	l_year;

Oracle Execution Plan

Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: S_SUPPKEY^S_NATIONKEY
Object_name: SUPPLIER
Alias: SUPPLIER@SEL$2
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$F5BB74E1
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: L_ORDERKEY^L_SUPPKEY^L_EXTENDEDPRICE^L_DISCOUNT^L_SHIPDATE
Object_name: LINEITEM
Alias: LINEITEM@SEL$2
Extended_information:    FP:L_SHIPDATE>=TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND L_SHIPDATE<=TO_DATE('1996-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Access_predicates: 
Filter_predicates: L_SHIPDATE>=TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')&L_SHIPDATE<=TO_DATE('1996-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Select_level: SEL$F5BB74E1
--------------------
Operation: hash join
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  S_NATIONKEY^L_ORDERKEY^L_SHIPDATE^L_EXTENDEDPRICE^L_DISCOUNT
Object_name: 
Alias: 
Extended_information:    AP:S_SUPPKEY=L_SUPPKEY
Access_predicates: S_SUPPKEY=L_SUPPKEY
Filter_predicates: 
Select_level: 
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: O_ORDERKEY^O_CUSTKEY
Object_name: ORDERS
Alias: ORDERS@SEL$2
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$F5BB74E1
--------------------
Operation: hash join
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  S_NATIONKEY^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^O_CUSTKEY
Object_name: 
Alias: 
Extended_information:    AP:O_ORDERKEY=L_ORDERKEY
Access_predicates: O_ORDERKEY=L_ORDERKEY
Filter_predicates: 
Select_level: 
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: N1.N_NATIONKEY^N1.N_NAME
Object_name: NATION
Alias: N1@SEL$2
Extended_information:    FP:N1.N_NAME='FRANCE' OR N1.N_NAME='GERMANY'
Access_predicates: 
Filter_predicates: N1.N_NAME='FRANCE'|N1.N_NAME='GERMANY'
Select_level: SEL$F5BB74E1
--------------------
Operation: hash join
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  O_CUSTKEY^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^N1.N_NAME
Object_name: 
Alias: 
Extended_information:    AP:S_NATIONKEY=N1.N_NATIONKEY
Access_predicates: S_NATIONKEY=N1.N_NATIONKEY
Filter_predicates: 
Select_level: 
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: N2.N_NATIONKEY^N2.N_NAME
Object_name: NATION
Alias: N2@SEL$2
Extended_information:    FP:(N2.N_NAME='FRANCE' OR N2.N_NAME='GERMANY') AND (N1.N_NAME='FRANCE' AND N2.N_NAME='GERMANY' OR N1.N_NAME='GERMANY' AND N2.N_NAME='FRANCE')
Access_predicates: 
Filter_predicates: (N2.N_NAME='FRANCE'|N2.N_NAME='GERMANY')&(N1.N_NAME='FRANCE'&N2.N_NAME='GERMANY'|N1.N_NAME='GERMANY'&N2.N_NAME='FRANCE')
Select_level: SEL$F5BB74E1
--------------------
Operation: nested loops
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  O_CUSTKEY^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^N1.N_NAME^N2.N_NATIONKEY^N2.N_NAME
Object_name: 
Alias: 
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: 
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: C_CUSTKEY^C_NATIONKEY
Object_name: CUSTOMER
Alias: CUSTOMER@SEL$2
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$F5BB74E1
--------------------
Operation: hash join
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  N2.N_NAME^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^N1.N_NAME
Object_name: 
Alias: 
Extended_information:    AP:C_CUSTKEY=O_CUSTKEY AND C_NATIONKEY=N2.N_NATIONKEY
Access_predicates: C_CUSTKEY=O_CUSTKEY&C_NATIONKEY=N2.N_NATIONKEY
Filter_predicates: 
Select_level: 
--------------------
Operation: sort
Options: GROUP BY
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  N1.N_NAME^N2.N_NAME^EXTRACT(YEAR FROM INTERNAL_FUNCTION(L_SHIPDATE))^SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
Object_name: 
Alias: 
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$F5BB74E1
--------------------
Operation: select statement
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection: 
Object_name: 
Alias: 
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: 
--------------------

Calpont Execution Plan

>SELECT MAIN
>>Returned Columns
1. SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N1

2. SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N2

3. ArithmeticColumn: FunctionColumn: extract(YEAR FROM INTERNAL_FUNCTION(L_SHIPDATE))

4. ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE*(1-L_DISCOUNT))
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
  s/t/c/T/A: ///0/

ConstantColumn: 1(n)
SimpleColumn L_DISCOUNT
  s/t/c/T/A: ///0/

Operator: -
Operator: *


>>Filters
SimpleFilter
  SimpleColumn tpch.LINEITEM.L_SHIPDATE
  s/t/c/T/A: tpch/LINEITEM/L_SHIPDATE/35/LINEITEM
  Operator: >=  ArithmeticColumn: FunctionColumn: to_date('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

SimpleFilter
  SimpleColumn tpch.LINEITEM.L_SHIPDATE
  s/t/c/T/A: tpch/LINEITEM/L_SHIPDATE/35/LINEITEM
  Operator: <=  ArithmeticColumn: FunctionColumn: to_date('1996-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

Operator: AND
SimpleFilter
  SimpleColumn tpch.SUPPLIER.S_SUPPKEY
  s/t/c/T/A: tpch/SUPPLIER/S_SUPPKEY/55/SUPPLIER
  Operator: =  SimpleColumn tpch.LINEITEM.L_SUPPKEY
  s/t/c/T/A: tpch/LINEITEM/L_SUPPKEY/27/LINEITEM

Operator: and
SimpleFilter
  SimpleColumn tpch.ORDERS.O_ORDERKEY
  s/t/c/T/A: tpch/ORDERS/O_ORDERKEY/16/ORDERS
  Operator: =  SimpleColumn tpch.LINEITEM.L_ORDERKEY
  s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/LINEITEM

Operator: and
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N1
  Operator: =  ConstantColumn: FRANCE(l)
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N1
  Operator: =  ConstantColumn: GERMANY(l)
Operator: OR
Operator: and
SimpleFilter
  SimpleColumn tpch.SUPPLIER.S_NATIONKEY
  s/t/c/T/A: tpch/SUPPLIER/S_NATIONKEY/58/SUPPLIER
  Operator: =  SimpleColumn tpch.NATION.N_NATIONKEY
  s/t/c/T/A: tpch/NATION/N_NATIONKEY/4/N1

Operator: and
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N2
  Operator: =  ConstantColumn: FRANCE(l)
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N2
  Operator: =  ConstantColumn: GERMANY(l)
Operator: OR
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N1
  Operator: =  ConstantColumn: FRANCE(l)
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N2
  Operator: =  ConstantColumn: GERMANY(l)
Operator: AND
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N1
  Operator: =  ConstantColumn: GERMANY(l)
SimpleFilter
  SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N2
  Operator: =  ConstantColumn: FRANCE(l)
Operator: AND
Operator: OR
Operator: AND
Operator: and
SimpleFilter
  SimpleColumn tpch.CUSTOMER.C_CUSTKEY
  s/t/c/T/A: tpch/CUSTOMER/C_CUSTKEY/8/CUSTOMER
  Operator: =  SimpleColumn tpch.ORDERS.O_CUSTKEY
  s/t/c/T/A: tpch/ORDERS/O_CUSTKEY/17/ORDERS

SimpleFilter
  SimpleColumn tpch.CUSTOMER.C_NATIONKEY
  s/t/c/T/A: tpch/CUSTOMER/C_NATIONKEY/11/CUSTOMER
  Operator: =  SimpleColumn tpch.NATION.N_NATIONKEY
  s/t/c/T/A: tpch/NATION/N_NATIONKEY/4/N2

Operator: AND
Operator: and
>>Group By Columns
SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N1

SimpleColumn tpch.NATION.N_NAME
  s/t/c/T/A: tpch/NATION/N_NAME/5/N2

ArithmeticColumn: FunctionColumn: extract(YEAR FROM INTERNAL_FUNCTION(L_SHIPDATE))

ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE*(1-L_DISCOUNT))
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
  s/t/c/T/A: ///0/

ConstantColumn: 1(n)
SimpleColumn L_DISCOUNT
  s/t/c/T/A: ///0/

Operator: -
Operator: *


SessionID: 4882
TxnID: 338
VerID: 338
--- Column Map ---
C_CUSTKEY : 0x92fb960
C_NATIONKEY : 0x92fb990
L_DISCOUNT : 0x92fc258
L_EXTENDEDPRICE : 0x92fc228
L_ORDERKEY : 0x92f7758
L_SHIPDATE : 0x92fc288
L_SUPPKEY : 0x92f7788
N_NAME : 0x92fc180
N_NAME : 0x92fb920
N_NATIONKEY : 0x92fc150
N_NATIONKEY : 0x9308ed0
O_CUSTKEY : 0x92fc450
O_ORDERKEY : 0x92fc2f8
S_NATIONKEY : 0x92f7728
S_SUPPKEY : 0x92f0df8