queries.sql

CREATE TABLE LINEITEM(
  ORDERKEY INT, 
  PARTKEY INT, 
  SUPPKEY INT, 
  LINENUMBER INT, 
  QUANTITY DECIMAL, 
  EXTENDEDPRICE DECIMAL, 
  DISCOUNT DECIMAL, 
  TAX DECIMAL, 
  RETURNFLAG CHAR(1), 
  LINESTATUS CHAR(1), 
  SHIPDATE DATE, 
  COMMITDATE DATE, 
  RECEIPTDATE DATE, 
  SHIPINSTRUCT CHAR(25), 
  SHIPMODE CHAR(10), 
  COMMENT VARCHAR(44), 
  PRIMARY KEY (ORDERKEY, LINENUMBER), 
  INDEX SHIPDATE (SHIPDATE), 
  INDEX RECEIPTDATE (RECEIPTDATE), 
  INDEX RETURNFLAG (RETURNFLAG));


CREATE TABLE ORDERS(
  ORDERKEY INT, 
  CUSTKEY INT, 
  ORDERSTATUS CHAR(1), 
  TOTALPRICE DECIMAL, 
  ORDERDATE DATE, 
  ORDERPRIORITY CHAR(15), 
  CLERK CHAR(15), 
  SHIPPRIORITY INT, 
  COMMENT VARCHAR(79), 
  PRIMARY KEY (ORDERKEY), 
  INDEX ORDERDATE (ORDERDATE));

CREATE TABLE PART(  PARTKEY INT, 
  NAME VARCHAR(55), 
  MFGR CHAR(25), 
  BRAND CHAR(10), 
  TYPE VARCHAR(25), 
  SIZE INT, 
  CONTAINER CHAR(10), 
  RETAILPRICE DECIMAL, 
  COMMENT VARCHAR(23), 
  PRIMARY KEY (PARTKEY));

CREATE TABLE CUSTOMER(
  CUSTKEY INT, 
  NAME VARCHAR(25), 
  ADDRESS VARCHAR(40), 
  NATIONKEY INT, 
  PHONE CHAR(15), 
  ACCTBAL DECIMAL, 
  MKTSEGMENT CHAR(10), 
  COMMENT VARCHAR(117), 
  PRIMARY KEY (CUSTKEY), 
  INDEX MKTSEGMENT (MKTSEGMENT));

CREATE TABLE SUPPLIER(
  SUPPKEY INT, 
  NAME CHAR(25), 
  ADDRESS VARCHAR(40), 
  NATIONKEY INT, 
  PHONE CHAR(15), 
  ACCTBAL DECIMAL, 
  COMMENT VARCHAR(101), 
  PRIMARY KEY (SUPPKEY), 
  INDEX NATIONKEY (NATIONKEY));


CREATE TABLE PARTSUPP(
  PARTKEY INT, 
  SUPPKEY INT, 
  AVAILQTY INT, 
  SUPPLYCOST DECIMAL, 
  COMMENT VARCHAR(199), 
  PRIMARY KEY (PARTKEY, SUPPKEY));

CREATE TABLE NATION(
  NATIONKEY INT, 
  NAME CHAR(25), 
  REGIONKEY INT, 
  COMMENT VARCHAR(152), 
  PRIMARY KEY (NATIONKEY));

CREATE TABLE REGION(
  REGIONKEY INT, 
  NAME CHAR(25), 
  COMMENT VARCHAR(152), 
  PRIMARY KEY (REGIONKEY));


SELECT COUNT(REGION.ORDERKEY) FROM ORDERS
GROUP BY ORDERS.ORDERKEY;

SELECT REGION.REGIONKEY,REGION.NAME, REGION.COMMENT 
FROM REGION WHERE REGION.NAME = 'AFRICA';

SELECT
REGION.REGIONKEY, REGION.COMMENT, NATION.REGIONKEY, NATION.COMMENT
FROM
REGION, NATION
WHERE REGION.REGIONKEY = NATION.REGIONKEY;


SELECT
LINEITEM.ORDERKEY,
ORDERS.ORDERKEY,
ORDERS.ORDERDATE,
ORDERS.SHIPPRIORITY
FROM
CUSTOMER,
ORDERS,
LINEITEM
WHERE
CUSTOMER.MKTSEGMENT = 'HOUSEHOLD' 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY
AND ORDERS.ORDERDATE < DATE('1995-03-18')
ORDER BY ORDERS.ORDERDATE;
LIMIT 10;


SELECT
LINEITEM.ORDERKEY,
ORDERS.ORDERDATE,
ORDERS.SHIPPRIORITY
FROM
LINEITEM,
ORDERS 
WHERE LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
AND ORDERS.ORDERDATE < DATE('1995-03-18')
AND LINEITEM.SHIPDATE > DATE('1995-03-18');


SELECT
LINEITEM.RETURNFLAG,
LINEITEM.LINESTATUS,
SUM(LINEITEM.QUANTITY) AS SUM_QTY,
SUM(LINEITEM.EXTENDEDPRICE) AS SUM_BASE_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE*(1-LINEITEM.DISCOUNT)) AS SUM_DISC_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE*(1-LINEITEM.DISCOUNT)*(1+LINEITEM.TAX)) AS SUM_CHARGE, 
AVG(LINEITEM.QUANTITY) AS AVG_QTY,
AVG(LINEITEM.EXTENDEDPRICE) AS AVG_PRICE,
AVG(LINEITEM.DISCOUNT) AS AVG_DISC,
COUNT(*) AS COUNT_ORDER
FROM
LINEITEM
WHERE
LINEITEM.SHIPDATE <= DATE('1999-02-17')
GROUP BY 
LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS 
ORDER BY
LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS;


SELECT
LINEITEM.RETURNFLAG,
LINEITEM.LINESTATUS
FROM
LINEITEM
WHERE
LINEITEM.SHIPDATE <= DATE('1999-03-11');

SELECT
NATION.NAME,
SUM(LINEITEM.EXTENDEDPRICE * (1 - LINEITEM.DISCOUNT)) AS REVENUE
FROM
REGION, NATION, CUSTOMER, ORDERS, LINEITEM, SUPPLIER
WHERE
CUSTOMER.CUSTKEY = ORDERS.CUSTKEY
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY
AND LINEITEM.SUPPKEY = SUPPLIER.SUPPKEY
AND CUSTOMER.NATIONKEY = NATION.NATIONKEY
AND SUPPLIER.NATIONKEY = NATION.NATIONKEY
AND NATION.REGIONKEY = REGION.REGIONKEY
AND REGION.NAME = 'AFRICA'
AND ORDERS.ORDERDATE >= DATE('1993-01-01')
AND ORDERS.ORDERDATE < DATE('1994-01-01')
GROUP BY NATION.NAME
ORDER BY REVENUE DESC; 


SELECT LINEITEM.SHIPMODE, 
SUM(CASE WHEN ORDERS.ORDERPRIORITY = '1-URGENT'
OR ORDERS.ORDERPRIORITY = '2-HIGH'
THEN 1
ELSE 0 END) AS HIGH_LINE_COUNT,
SUM(CASE WHEN ORDERS.ORDERPRIORITY <> '1-URGENT'
AND ORDERS.ORDERPRIORITY <> '2-HIGH'
THEN 1
ELSE 0 END) AS LOW_LINE_COUNT
FROM LINEITEM, ORDERS
WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY
AND (LINEITEM.SHIPMODE='RAIL' OR LINEITEM.SHIPMODE='FOB')
AND LINEITEM.COMMITDATE < LINEITEM.RECEIPTDATE
AND LINEITEM.SHIPDATE < LINEITEM.COMMITDATE
AND LINEITEM.RECEIPTDATE >= DATE('1996-01-01')
AND LINEITEM.RECEIPTDATE < DATE('1997-01-01')
GROUP BY LINEITEM.SHIPMODE
ORDER BY LINEITEM.SHIPMODE;









SELECT LINEITEM.SHIPMODE, 
SUM(CASE WHEN ORDERS.ORDERPRIORITY = '1-URGENT' OR ORDERS.ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS HIGH_LINE_COUNT, 
SUM(CASE WHEN ORDERS.ORDERPRIORITY <> '1-URGENT' AND ORDERS.ORDERPRIORITY <> '2-HIGH' THEN 1 ELSE 0 END) AS LOW_LINE_COUNT 
FROM LINEITEM 
JOIN ORDERS ON ORDERS.ORDERKEY = LINEITEM.ORDERKEY 
WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY
AND LINEITEM.SHIPMODE = 'TRUCK' OR LINEITEM.SHIPMODE = 'FOB' 
AND LINEITEM.RECEIPTDATE >= DATE('1995-01-01') 
AND LINEITEM.RECEIPTDATE < DATE('1996-01-01') 
AND LINEITEM.COMMITDATE < LINEITEM.RECEIPTDATE 
AND LINEITEM.SHIPDATE < LINEITEM.COMMITDATE 
GROUP BY LINEITEM.SHIPMODE ORDER BY LINEITEM.SHIPMODE;







SELECT SUM(LINEITEM.EXTENDEDPRICE * LINEITEM.DISCOUNT) AS REVENUE 
FROM LINEITEM 
WHERE LINEITEM.SHIPDATE >= DATE('1995-01-01') 
AND LINEITEM.SHIPDATE < DATE('1996-01-01') 
AND LINEITEM.DISCOUNT > 0.04 
AND LINEITEM.DISCOUNT < 0.06 
AND LINEITEM.QUANTITY < 24;




SELECT LINEITEM.SHIPMODE, 
SUM(CASE WHEN ORDERS.ORDERPRIORITY = '1-URGENT' OR ORDERS.ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS HIGH_LINE_COUNT, 
SUM(CASE WHEN ORDERS.ORDERPRIORITY <> '1-URGENT' AND ORDERS.ORDERPRIORITY <> '2-HIGH' THEN 1 ELSE 0 END) AS LOW_LINE_COUNT 
FROM LINEITEM 
JOIN ORDERS ON ORDERS.ORDERKEY = LINEITEM.ORDERKEY 
WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY 
AND LINEITEM.SHIPMODE = 'FOB' OR LINEITEM.SHIPMODE = 'MAIL' 
AND LINEITEM.COMMITDATE < LINEITEM.RECEIPTDATE 
AND LINEITEM.SHIPDATE < LINEITEM.COMMITDATE 
AND LINEITEM.RECEIPTDATE >= DATE('1997-01-01') 
AND LINEITEM.RECEIPTDATE < DATE('1998-01-01') 
GROUP BY LINEITEM.SHIPMODE ORDER BY LINEITEM.SHIPMODE;

SELECT LINEITEM.ORDERKEY, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS REVENUE, 
ORDERS.ORDERDATE, 
ORDERS.SHIPPRIORITY 
FROM CUSTOMER 
JOIN ORDERS ON CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
JOIN LINEITEM ON LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
WHERE CUSTOMER.MKTSEGMENT = 'HOUSEHOLD' 
AND CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
AND ORDERS.ORDERDATE < DATE('1995-03-21') 
AND LINEITEM.SHIPDATE > DATE('1995-03-21') 
GROUP BY LINEITEM.ORDERKEY, ORDERS.ORDERDATE, ORDERS.SHIPPRIORITY 
ORDER BY REVENUE DESC, ORDERDATE LIMIT 10;

SELECT SUM(LINEITEM.EXTENDEDPRICE * LINEITEM.DISCOUNT) AS REVENUE
FROM LINEITEM WHERE LINEITEM.SHIPDATE >= DATE('1994-01-01') 
AND LINEITEM.SHIPDATE < DATE('1995-01-01') 
AND LINEITEM.DISCOUNT > 0.06 AND LINEITEM.DISCOUNT < 0.08 AND LINEITEM.QUANTITY < 25;

SELECT NATION.NAME, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS REVENUE 
FROM REGION 
JOIN NATION ON NATION.REGIONKEY = REGION.REGIONKEY 
JOIN CUSTOMER ON CUSTOMER.NATIONKEY = NATION.NATIONKEY 
JOIN ORDERS ON CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
JOIN LINEITEM ON LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
JOIN SUPPLIER ON LINEITEM.SUPPKEY = SUPPLIER.SUPPKEY AND SUPPLIER.NATIONKEY = NATION.NATIONKEY 
WHERE CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
AND LINEITEM.SUPPKEY = SUPPLIER.SUPPKEY 
AND CUSTOMER.NATIONKEY = NATION.NATIONKEY 
AND SUPPLIER.NATIONKEY = NATION.NATIONKEY 
AND NATION.REGIONKEY = REGION.REGIONKEY 
AND REGION.NAME = 'AMERICA' 
AND ORDERS.ORDERDATE >= DATE('1995-01-01') 
AND ORDERS.ORDERDATE < DATE('1996-01-01') 
GROUP BY NATION.NAME ORDER BY REVENUE DESC;

SELECT LINEITEM.RETURNFLAG, 
LINEITEM.LINESTATUS, 
SUM(LINEITEM.QUANTITY) AS SUM_QTY, 
SUM(LINEITEM.EXTENDEDPRICE) AS SUM_BASE_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS SUM_DISC_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT * 1 + LINEITEM.TAX) AS SUM_CHARGE, 
AVG(LINEITEM.QUANTITY) AS AVG_QTY, AVG(LINEITEM.EXTENDEDPRICE) AS AVG_PRICE, 
AVG(LINEITEM.DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER 
FROM LINEITEM 
WHERE LINEITEM.SHIPDATE <= DATE('1999-03-16') 
GROUP BY LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS 
ORDER BY LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS;

SELECT LINEITEM.ORDERKEY, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS REVENUE, 
ORDERS.ORDERDATE, ORDERS.SHIPPRIORITY 
FROM CUSTOMER 
JOIN ORDERS ON CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
JOIN LINEITEM ON LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
WHERE CUSTOMER.MKTSEGMENT = 'HOUSEHOLD' 
AND CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
AND ORDERS.ORDERDATE < DATE('1995-03-12') 
AND LINEITEM.SHIPDATE > DATE('1995-03-12') 
GROUP BY LINEITEM.ORDERKEY, ORDERS.ORDERDATE, ORDERS.SHIPPRIORITY 
ORDER BY REVENUE DESC, ORDERDATE LIMIT 10;

SELECT SUM(LINEITEM.EXTENDEDPRICE * LINEITEM.DISCOUNT) AS REVENUE 
FROM LINEITEM 
WHERE LINEITEM.SHIPDATE >= DATE('1996-01-01') 
AND LINEITEM.SHIPDATE < DATE('1997-01-01') 
AND LINEITEM.DISCOUNT > 0.06 
AND LINEITEM.DISCOUNT < 0.08 
AND LINEITEM.QUANTITY < 24;

SELECT LINEITEM.RETURNFLAG, 
LINEITEM.LINESTATUS, 
SUM(LINEITEM.QUANTITY) AS SUM_QTY, 
SUM(LINEITEM.EXTENDEDPRICE) AS SUM_BASE_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS SUM_DISC_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT * 1 + LINEITEM.TAX) AS SUM_CHARGE, 
AVG(LINEITEM.QUANTITY) AS AVG_QTY, AVG(LINEITEM.EXTENDEDPRICE) AS AVG_PRICE, 
AVG(LINEITEM.DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER 
FROM LINEITEM 
WHERE LINEITEM.SHIPDATE <= DATE('1999-02-16') 
GROUP BY LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS 
ORDER BY LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS;


SELECT LINEITEM.ORDERKEY, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS REVENUE, 
ORDERS.ORDERDATE, ORDERS.SHIPPRIORITY 
FROM CUSTOMER 
JOIN ORDERS ON CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
JOIN LINEITEM ON LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
WHERE CUSTOMER.MKTSEGMENT = 'AUTOMOBILE' 
AND CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
AND ORDERS.ORDERDATE < DATE('1995-03-28') 
AND LINEITEM.SHIPDATE > DATE('1995-03-28') 
GROUP BY LINEITEM.ORDERKEY, ORDERS.ORDERDATE, ORDERS.SHIPPRIORITY 
ORDER BY REVENUE DESC, ORDERDATE LIMIT 10;




SELECT LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS, 
SUM(LINEITEM.QUANTITY) AS SUM_QTY, 
SUM(LINEITEM.EXTENDEDPRICE) AS SUM_BASE_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS SUM_DISC_PRICE, 
SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT * 1 + LINEITEM.TAX) AS SUM_CHARGE, 
AVG(LINEITEM.QUANTITY) AS AVG_QTY, AVG(LINEITEM.EXTENDEDPRICE) AS AVG_PRICE, 
AVG(LINEITEM.DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER 
FROM LINEITEM WHERE LINEITEM.SHIPDATE <= DATE('1999-03-03')
GROUP BY LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS 
ORDER BY LINEITEM.RETURNFLAG, LINEITEM.LINESTATUS;




SELECT NATION.NAME, SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS REVENUE 
FROM REGION 
JOIN NATION ON NATION.REGIONKEY = REGION.REGIONKEY 
JOIN CUSTOMER ON CUSTOMER.NATIONKEY = NATION.NATIONKEY 
JOIN ORDERS ON CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
JOIN LINEITEM ON LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
JOIN SUPPLIER ON LINEITEM.SUPPKEY = SUPPLIER.SUPPKEY AND SUPPLIER.NATIONKEY = NATION.NATIONKEY 
WHERE CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
AND LINEITEM.SUPPKEY = SUPPLIER.SUPPKEY 
AND CUSTOMER.NATIONKEY = NATION.NATIONKEY 
AND SUPPLIER.NATIONKEY = NATION.NATIONKEY 
AND NATION.REGIONKEY = REGION.REGIONKEY 
AND REGION.NAME = 'ASIA' 
AND ORDERS.ORDERDATE >= DATE('1994-01-01') 
AND ORDERS.ORDERDATE < DATE('1995-01-01') 
GROUP BY NATION.NAME ORDER BY REVENUE DESC;


SELECT NATION.NAME, SUM(LINEITEM.EXTENDEDPRICE * 1 - LINEITEM.DISCOUNT) AS REVENUE FROM REGION 
JOIN NATION ON NATION.REGIONKEY = REGION.REGIONKEY 
JOIN CUSTOMER ON CUSTOMER.NATIONKEY = NATION.NATIONKEY 
JOIN ORDERS ON CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
JOIN LINEITEM ON LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
JOIN SUPPLIER ON LINEITEM.SUPPKEY = SUPPLIER.SUPPKEY AND SUPPLIER.NATIONKEY = NATION.NATIONKEY 
WHERE CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY 
AND LINEITEM.SUPPKEY = SUPPLIER.SUPPKEY 
AND CUSTOMER.NATIONKEY = NATION.NATIONKEY 
AND SUPPLIER.NATIONKEY = NATION.NATIONKEY 
AND NATION.REGIONKEY = REGION.REGIONKEY 
AND REGION.NAME = 'MIDDLE EAST' 
AND ORDERS.ORDERDATE >= DATE('1994-01-01') 
AND ORDERS.ORDERDATE < DATE('1995-01-01') 
GROUP BY NATION.NAME ORDER BY REVENUE DESC;


SELECT  NATION.REGIONKEY, REGION.REGIONKEY, REGION.NAME
FROM REGION,NATION 
WHERE NATION.REGIONKEY = REGION.REGIONKEY;


SELECT LINEITEM.ORDERKEY,LINEITEM.SUPPKEY,LINEITEM.EXTENDEDPRICE,LINEITEM.DISCOUNT 
FROM REGION,NATION,CUSTOMER,ORDERS,LINEITEM 
WHERE NATION.REGIONKEY = REGION.REGIONKEY
AND CUSTOMER.NATIONKEY = NATION.NATIONKEY 
AND CUSTOMER.CUSTKEY = ORDERS.CUSTKEY 
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY;











SELECT LINEITEM.SHIPMODE, 
SUM(CASE WHEN ORDERS.ORDERPRIORITY = '1-URGENT'
OR ORDERS.ORDERPRIORITY = '2-HIGH'
THEN 1
ELSE 0 END) AS HIGH_LINE_COUNT,
SUM(CASE WHEN ORDERS.ORDERPRIORITY <> '1-URGENT'
AND ORDERS.ORDERPRIORITY <> '2-HIGH'
THEN 1
ELSE 0 END) AS LOW_LINE_COUNT
FROM LINEITEM, ORDERS
WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY


AND (LINEITEM.SHIPMODE='REG AIR' OR LINEITEM.SHIPMODE='SHIP')
AND LINEITEM.COMMITDATE < LINEITEM.RECEIPTDATE
AND LINEITEM.SHIPDATE < LINEITEM.COMMITDATE
AND LINEITEM.RECEIPTDATE >= DATE('1994-01-01')
AND LINEITEM.RECEIPTDATE < DATE('1995-01-01')


GROUP BY LINEITEM.SHIPMODE
ORDER BY LINEITEM.SHIPMODE;



SELECT COUNT(*)
FROM ORDERS 
WHERE ORDERS.ORDERDATE >= DATE('1997-01-01')
AND ORDERS.ORDERDATE < DATE('1998-01-01');


SELECT
NATION.NAME,
LINEITEM.DISCOUNT,
LINEITEM.EXTENDEDPRICE
FROM
REGION, NATION, CUSTOMER, ORDERS, LINEITEM
WHERE
CUSTOMER.CUSTKEY = ORDERS.CUSTKEY
AND LINEITEM.ORDERKEY = ORDERS.ORDERKEY
AND CUSTOMER.NATIONKEY = NATION.NATIONKEY 
AND SUPPLIER.NATIONKEY = NATION.NATIONKEY
AND NATION.REGIONKEY = REGION.REGIONKEY
AND REGION.NAME = 'EUROPE'
AND NATION.NAME = 'UNITED KINGDOM'
AND ORDERS.ORDERDATE >= DATE('1997-01-01')
AND ORDERS.ORDERDATE < DATE('1998-01-01')
ORDER BY LINEITEM.EXTENDEDPRICE;


ORDER BY REVENUE;