SQL> select l_orderkey, l_partkey + 1 as plus1, l_partkey + 1 as plus2 from lineitem where l_orderkey > -1;
{
"IsDistinct": false,
"Projections": [
{
"Expr": {
"ColumnName": "l_orderkey",
"OpType": "Column"
},
"OpType": "Target"
},
{
"AliasName": "plus1",
"Expr": {
"LeftExpr": {
"ColumnName": "l_partkey",
"OpType": "Column"
},
"RightExpr": {
"Value": "1",
"OpType": "Literal"
},
"OpType": "Plus"
},
"OpType": "Target"
},
{
"AliasName": "plus2",
"Expr": {
"LeftExpr": {
"ColumnName": "l_partkey",
"OpType": "Column"
},
"RightExpr": {
"Value": "1",
"OpType": "Literal"
},
"OpType": "Plus"
},
"OpType": "Target"
}
],
"Expr": {
"Condition": {
"LeftExpr": {
"ColumnName": "l_orderkey",
"OpType": "Column"
},
"RightExpr": {
"IsNegative": true,
"Expr": {
"Value": "1",
"OpType": "Literal"
},
"OpType": "Sign"
},
"OpType": "GreaterThan"
},
"Expr": {
"Relations": [
{
"TableName": "lineitem",
"OpType": "Relation"
}
],
"OpType": "RelationList"
},
"OpType": "Filter"
},
"OpType": "Projection"
}
SQL> select * from region where r_name like '%s'
{
"IsDistinct": false,
"Projections": [
{
"Expr": {
"OpType": "Asterisk"
},
"OpType": "Target"
}
],
"Expr": {
"Condition": {
"IsNot": false,
"IsCaseInsensitive": false,
"LeftExpr": {
"ColumnName": "r_name",
"OpType": "Column"
},
"RightExpr": {
"Value": "%s",
"OpType": "Literal"
},
"OpType": "LikePredicate"
},
"Expr": {
"Relations": [
{
"TableName": "region",
"OpType": "Relation"
}
],
"OpType": "RelationList"
},
"OpType": "Filter"
},
"OpType": "Projection"
}
SQL> select l_orderkey, avg(l_partkey) total, sum(l_linenumber) as num from lineitem group by l_orderkey having total >= 2 or num = 3;
{
"IsDistinct": false,
"Projections": [
{
"Expr": {
"ColumnName": "l_orderkey",
"OpType": "Column"
},
"OpType": "Target"
},
{
"AliasName": "total",
"Expr": {
"IsDistinct": false,
"Signature": "avg",
"Params": [
{
"ColumnName": "l_partkey",
"OpType": "Column"
}
],
"OpType": "GeneralSetFunction"
},
"OpType": "Target"
},
{
"AliasName": "num",
"Expr": {
"IsDistinct": false,
"Signature": "sum",
"Params": [
{
"ColumnName": "l_linenumber",
"OpType": "Column"
}
],
"OpType": "GeneralSetFunction"
},
"OpType": "Target"
}
],
"Expr": {
"Conditions": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "total",
"OpType": "Column"
},
"RightExpr": {
"Value": "2",
"OpType": "Literal"
},
"OpType": "GreaterThanOrEquals"
},
"RightExpr": {
"LeftExpr": {
"ColumnName": "num",
"OpType": "Column"
},
"RightExpr": {
"Value": "3",
"OpType": "Literal"
},
"OpType": "Equals"
},
"OpType": "Or"
},
"Expr": {
"Groups": [
{
"GroupType": "OrdinaryGroup",
"Dimensions": [
{
"ColumnName": "l_orderkey",
"OpType": "Column"
}
]
}
],
"Expr": {
"Relations": [
{
"TableName": "lineitem",
"OpType": "Relation"
}
],
"OpType": "RelationList"
},
"OpType": "Aggregation"
},
"OpType": "Having"
},
"OpType": "Projection"
}
SQL> select l_shipmode, sum(case when o_orderpriority ='1-URGENT' or o_orderpriority ='2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and (l_shipmode = 'MAIL' or l_shipmode = 'SHIP') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= '1994-01-01' and l_receiptdate < '1995-01-01' group by l_shipmode order by l_shipmode;
{
"IsDistinct": false,
"Projections": [
{
"Expr": {
"ColumnName": "l_shipmode",
"OpType": "Column"
},
"OpType": "Target"
},
{
"AliasName": "high_line_count",
"Expr": {
"IsDistinct": false,
"Signature": "sum",
"Params": [
{
"WhenExprs": [
{
"Condition": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "o_orderpriority",
"OpType": "Column"
},
"RightExpr": {
"Value": "1-URGENT",
"OpType": "Literal"
},
"OpType": "Equals"
},
"RightExpr": {
"LeftExpr": {
"ColumnName": "o_orderpriority",
"OpType": "Column"
},
"RightExpr": {
"Value": "2-HIGH",
"OpType": "Literal"
},
"OpType": "Equals"
},
"OpType": "Or"
},
"Result": {
"Value": "1",
"OpType": "Literal"
}
}
],
"ElseExprs": {
"Value": "0",
"OpType": "Literal"
},
"OpType": "CaseWhen"
}
],
"OpType": "GeneralSetFunction"
},
"OpType": "Target"
},
{
"AliasName": "low_line_count",
"Expr": {
"IsDistinct": false,
"Signature": "sum",
"Params": [
{
"WhenExprs": [
{
"Condition": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "o_orderpriority",
"OpType": "Column"
},
"RightExpr": {
"Value": "1-URGENT",
"OpType": "Literal"
},
"OpType": "NotEquals"
},
"RightExpr": {
"LeftExpr": {
"ColumnName": "o_orderpriority",
"OpType": "Column"
},
"RightExpr": {
"Value": "2-HIGH",
"OpType": "Literal"
},
"OpType": "NotEquals"
},
"OpType": "And"
},
"Result": {
"Value": "1",
"OpType": "Literal"
}
}
],
"ElseExprs": {
"Value": "0",
"OpType": "Literal"
},
"OpType": "CaseWhen"
}
],
"OpType": "GeneralSetFunction"
},
"OpType": "Target"
}
],
"Expr": {
"SortSpec": [
{
"Key": {
"ColumnName": "l_shipmode",
"OpType": "Column"
},
"IsAsc": true,
"IsNullFirst": false
}
],
"Expr": {
"Groups": [
{
"GroupType": "OrdinaryGroup",
"Dimensions": [
{
"ColumnName": "l_shipmode",
"OpType": "Column"
}
]
}
],
"Expr": {
"Condition": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "o_orderkey",
"OpType": "Column"
},
"RightExpr": {
"ColumnName": "l_orderkey",
"OpType": "Column"
},
"OpType": "Equals"
},
"RightExpr": {
"LeftExpr": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "l_shipmode",
"OpType": "Column"
},
"RightExpr": {
"Value": "MAIL",
"OpType": "Literal"
},
"OpType": "Equals"
},
"RightExpr": {
"LeftExpr": {
"ColumnName": "l_shipmode",
"OpType": "Column"
},
"RightExpr": {
"Value": "SHIP",
"OpType": "Literal"
},
"OpType": "Equals"
},
"OpType": "Or"
},
"RightExpr": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "l_commitdate",
"OpType": "Column"
},
"RightExpr": {
"ColumnName": "l_receiptdate",
"OpType": "Column"
},
"OpType": "LessThan"
},
"RightExpr": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "l_shipdate",
"OpType": "Column"
},
"RightExpr": {
"ColumnName": "l_commitdate",
"OpType": "Column"
},
"OpType": "LessThan"
},
"RightExpr": {
"LeftExpr": {
"LeftExpr": {
"ColumnName": "l_receiptdate",
"OpType": "Column"
},
"RightExpr": {
"Value": "1994-01-01",
"OpType": "Literal"
},
"OpType": "GreaterThanOrEquals"
},
"RightExpr": {
"LeftExpr": {
"ColumnName": "l_receiptdate",
"OpType": "Column"
},
"RightExpr": {
"Value": "1995-01-01",
"OpType": "Literal"
},
"OpType": "LessThan"
},
"OpType": "And"
},
"OpType": "And"
},
"OpType": "And"
},
"OpType": "And"
},
"OpType": "And"
},
"Expr": {
"Relations": [
{
"TableName": "orders",
"OpType": "Relation"
},
{
"TableName": "lineitem",
"OpType": "Relation"
}
],
"OpType": "RelationList"
},
"OpType": "Filter"
},
"OpType": "Aggregation"
},
"OpType": "Sort"
},
"OpType": "Projection"
}
SQL> select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer_rc as c join nation_rc as n on c.c_nationkey = n.n_nationkey join orders as o on c.c_custkey = o.o_custkey and o.o_orderdate >= '1993-10-01' and o.o_orderdate < '1994-01-01' join lineitem_rc as l on l.l_orderkey = o.o_orderkey and l.l_returnflag = 'R' group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc;
{
"IsDistinct": false,
"Projections": [
{
"Expr": {
"ColumnName": "c_custkey",
"OpType": "Column"
},
"OpType": "Target"
},
{
"Expr": {
"ColumnName": "c_name",
"OpType": "Column"
},
"OpType": "Target"
},
{
"AliasName": "revenue",
"Expr": {
"IsDistinct": false,
"Signature": "sum",
"Params": [
{
"LeftExpr": {
"ColumnName": "l_extendedprice",
"OpType": "Column"
},
"RightExpr": {
"LeftExpr": {
"Value": "1",
"OpType": "Literal"
},
"RightExpr": {
"ColumnName": "l_discount",
"OpType": "Column"
},
"OpType": "Minus"
},
"OpType": "Multiply"
}
],
"OpType": "GeneralSetFunction"
},
"OpType": "Target"
},
{
"Expr": {
"ColumnName": "c_acctbal",
"OpType": "Column"
},
"OpType": "Target"
},
{
"Expr": {
"ColumnName": "n_name",
"OpType": "Column"
},
"OpType": "Target"
},
{
"Expr": {
"ColumnName": "c_address",
"OpType": "Column"
},
"OpType": "Target"
},
{
"Expr": {
"ColumnName": "c_phone",
"OpType": "Column"
},
"OpType": "Target"
},
{
"Expr": {
"ColumnName": "c_comment",
"OpType": "Column"
},
"OpType": "Target"
}
],
"Expr": {
"SortSpec": [
{
"Key": {
"ColumnName": "revenue",
"OpType": "Column"
},
"IsAsc": false,
"IsNullFirst": false
}
],
"Expr": {
"Groups": [
{
"GroupType": "OrdinaryGroup",
"Dimensions": [
{
"ColumnName": "c_custkey",
"OpType": "Column"
},
{
"ColumnName": "c_name",
"OpType": "Column"
},
{
"ColumnName": "c_acctbal",
"OpType": "Column"
},
{
"ColumnName": "c_phone",
"OpType": "Column"
},
{
"ColumnName": "n_name",
"OpType": "Column"
},
{
"ColumnName": "c_address",
"OpType": "Column"
},
{
"ColumnName": "c_comment",
"OpType": "Column"
}
]
}
],
"Expr": {
"Relations": [
{
"JoinType": "INNER",
"JoinCond": {
"LeftExpr": {
"LeftExpr": {
"Qualifier": "l",
"ColumnName": "l_orderkey",
"OpType": "Column"
},
"RightExpr": {
"Qualifier": "o",
"ColumnName": "o_orderkey",
"OpType": "Column"
},
"OpType": "Equals"
},
"RightExpr": {
"LeftExpr": {
"Qualifier": "l",
"ColumnName": "l_returnflag",
"OpType": "Column"
},
"RightExpr": {
"Value": "R",
"OpType": "Literal"
},
"OpType": "Equals"
},
"OpType": "And"
},
"IsNatural": false,
"LeftExpr": {
"JoinType": "INNER",
"JoinCond": {
"LeftExpr": {
"LeftExpr": {
"Qualifier": "c",
"ColumnName": "c_custkey",
"OpType": "Column"
},
"RightExpr": {
"Qualifier": "o",
"ColumnName": "o_custkey",
"OpType": "Column"
},
"OpType": "Equals"
},
"RightExpr": {
"LeftExpr": {
"LeftExpr": {
"Qualifier": "o",
"ColumnName": "o_orderdate",
"OpType": "Column"
},
"RightExpr": {
"Value": "1993-10-01",
"OpType": "Literal"
},
"OpType": "GreaterThanOrEquals"
},
"RightExpr": {
"LeftExpr": {
"Qualifier": "o",
"ColumnName": "o_orderdate",
"OpType": "Column"
},
"RightExpr": {
"Value": "1994-01-01",
"OpType": "Literal"
},
"OpType": "LessThan"
},
"OpType": "And"
},
"OpType": "And"
},
"IsNatural": false,
"LeftExpr": {
"JoinType": "INNER",
"JoinCond": {
"LeftExpr": {
"Qualifier": "c",
"ColumnName": "c_nationkey",
"OpType": "Column"
},
"RightExpr": {
"Qualifier": "n",
"ColumnName": "n_nationkey",
"OpType": "Column"
},
"OpType": "Equals"
},
"IsNatural": false,
"LeftExpr": {
"TableName": "customer_rc",
"TableAlias": "c",
"OpType": "Relation"
},
"RightExpr": {
"TableName": "nation_rc",
"TableAlias": "n",
"OpType": "Relation"
},
"OpType": "Join"
},
"RightExpr": {
"TableName": "orders",
"TableAlias": "o",
"OpType": "Relation"
},
"OpType": "Join"
},
"RightExpr": {
"TableName": "lineitem_rc",
"TableAlias": "l",
"OpType": "Relation"
},
"OpType": "Join"
}
],
"OpType": "RelationList"
},
"OpType": "Aggregation"
},
"OpType": "Sort"
},
"OpType": "Projection"
}