Q1:Display the key and quantity of parts which are shipped on April 1st, 1992.
SELECT S.l_quantity, S.l_partkey FROM lineitem S WHERE S.l_shipdate = '1992-04-01'
Q2: For each ship mode, find the total quantity of items that are shipped before October 1st,1992. Display the results in descending order of the total quantity.
SELECT S.l_shipmode, SUM(S.l_quantity) AS total FROM lineitem S WHERE S.l_shipdate < '1992-10-01' GROUP BY S.l_shipmode ORDER BY total DESC;
Q3: Display the total number of comments which contains “carefully” in Table lineitem.
SELECT Count (*) AS total FROM lineitem S WHERE S.l_comment LIKE '%carefully%';
Q4: Display the total quantity of parts which are satisfied the following conditions in LINEITEM:
– the ship mode is RAIL
– the priority of the order that the parts belong to is URGENT
首先在orders 查找 orderpriority ＝‘urgent’的 orderkey
SELECT SUM(S.l_quantity) AS total FROM lineitem S WHERE S.l_shipmode = 'RAIL' AND S.l_orderkey IN (SELECT R.o_orderkey FROM orders R WHERE R.o_orderpriority = '1-URGENT');
Q5: Find out the suppliers that can supply the part whose key is 99 . Display their names, nation, regions and brands.
SELECT S.s_name, R.n_name, T.r_name, F.p_brand FROM supplier S, nation R, region T, part F, partsupp H WHERE S.s_suppkey = H.ps_suppkey AND S.s_nationkey = R.n_nationkey AND R.n_regionkey = T.r_regionkey AND H.ps_partkey = F.p_partkey AND H.ps_partkey = '99';
Q6: Calculate the number of distinct customers who have ordered parts whose type is STEEL.
SELECT COUNT (DISTINCT S.c_custkey) AS total FROM customer S, orders R, lineitem T, part H WHERE S.c_custkey = R.o_custkey AND R.o_orderkey = T.l_orderkey AND T.l_partkey = H.p_partkey AND H.p_type LIKE '%STEEL%';
Q7: Find the top 10 parts, in terms of their total quantity shipped in 1996. List the parts’ keys,names, brands and total quality shipped in 1996. Display the results in ascending order of the total quantity.
SELECT S.p_partkey, S.p_name, S.p_brand, T.total FROM part S, (SELECT R.l_partkey as partkey, SUM(R.l_quantity) AS total FROM lineitem R WHERE R.l_shipdate > '1995-12-31' AND R.l_shipdate < '1997-01-01' GROUP BY R.l_partkey ORDER BY total DESC LIMIT 10 ) AS T WHERE S.p_partkey = T.partkey ORDER BY total ASC;
Q8: Find out the customers who had at least ordered 10 times in 1998. Display their keys, names, addresses, phones and the total of their orders in 1998. List the results in descending order of the total orders.
SELECT S.c_custkey, S.c_name, S.c_address, S.c_phone, T.total FROM customer S, (SELECT R.o_custkey as custkey, COUNT(*) AS total FROM orders R WHERE R.o_orderdate > '1997-12-31' AND R.o_orderdate < '1999-01-01' GROUP BY R.o_custkey HAVING COUNT(*)>= 10 ) AS T WHERE S.c_custkey = T.custkey;
Q9: Find out the lineitem which has the max extended price with a 10% discount and is ordered in 1998. Display the value of this max extended price.
SELECT MAX(S.l_extendedprice) AS maxprice FROM lineitem S WHERE S.l_discount = '0.10' AND S.l_orderkey In (SELECT R.o_orderkey FROM orders R WHERE R.o_orderdate > '1997-12-31' AND R.o_orderdate < '1999-01-01');
Q10: Display total quantity of lineitems which are ordered in May 1995 for each ship mode except air and ship.
SELECT S.l_shipmode, SUM(S.l_quantity) AS total FROM lineitem S WHERE S.l_shipmode != 'AIR' AND S.l_shipmode != 'SHIP' AND S.l_orderkey In (SELECT R.o_orderkey FROM orders R WHERE R.o_orderdate > '1995-04-30' AND R.o_orderdate < '1995-06-01') GROUP BY S.l_shipmode;