数据库作业之SQL语句实现简单查询功能

Q1:Display the key and quantity of parts which are shipped on April 1st, 1992.

SELECT l_orderkey,l_partkey,l_suppkey,l_quantity 
FROM lineitem
WHERE l_shipdate = date('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 l_shipmode, sum(l_quantity)
FROM lineitem
WHERE l_shipdate < Date('1992-10-01')
GROUP BY l_shipmode
ORDER BY sum(l_quantity) desc;

Q3: Display the total number of comments which contains “carefully” in Table lineitem.

SELECT count(l_comment)
FROM lineitem
WHERE 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

SELECT sum(l_quantity)
FROM lineitem, orders
WHERE l_orderkey = o_orderkey
AND l_shipmode = 'RAIL'
AND o_orderpriority LIKE '%URGENT%';

Q5: Find out the suppliers that can supply the part whose key is 99 . Display their names, nation, regions and brands.

SELECT s_name, p_name, n_name, r_name, p_brand 
FROM nation, region, part, supplier, partsupp
WHERE p_partkey = 99
AND s_suppkey = ps_suppkey
AND ps_partkey = p_partkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey;

Q6: Calculate the number of distinct customers who have ordered parts whose type is STEEL.

SELECT count(distinct o_custkey)
FROM lineitem, orders, part
WHERE p_type like '%STEEL'
AND l_partkey = p_partkey
AND l_orderkey = o_orderkey;

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 *
FROM (
    SELECT p_partkey, p_name, p_brand, sum(l_quantity) as totalquantity
    FROM lineitem, part
    WHERE p_partkey = l_partkey
    AND l_shipdate >= date('1996-01-01')
    AND l_shipdate < date('1997-01-01')
    GROUP BY p_partkey, p_name, p_brand
    ORDER BY sum(l_quantity) desc
    LIMIT 10) as temp
ORDER BY temp.totalquantity 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 c_custkey, c_name, c_address, c_phone, count(*)
FROM customer, orders
WHERE c_custkey = o_custkey
AND o_orderdate >= date('1998-01-01')
AND o_orderdate < date('1999-01-01')
GROUP BY c_custkey, c_name, c_address, c_phone
HAVING count(*) >= 10
ORDER BY count(*) desc;

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(l_extendedprice)
FROM lineitem, orders
WHERE l_orderkey = o_orderkey
AND l_discount = 0.10
AND o_orderdate >= date('1998-01-01')
AND o_orderdate < date('1999-01-01');

Q10: Display total quantity of lineitems which are ordered in May 1995 for each ship mode except air and ship.

SELECT l_shipmode, sum(l_quantity)
FROM lineitem, orders
WHERE l_orderkey = o_orderkey
AND o_orderdate >= date('1995-05-01')
AND o_orderdate < date('1995-06-01')
AND l_shipmode <> 'AIR'
AND l_shipmode <> 'SHIP'
GROUP BY l_shipmode;