SQL query need help. The tables is below: customer(c_custkey, c_name, c_address,

Question

SQL query need help. The tables is below:

customer(c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment)

lineitem(l-orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extenderprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment)

nation(n_nationkey, n_name, n_regionkey, n_comment)

orders(o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment)

part(p_partkey, p_name, p_mfgr, p_brand, p_type, p_size, p_container, p_retailprice, p_comment)

partsupp(p_spartkey, p_ssuppkey, p_savailqty, p_ssupplycost, p_scoment)

region(r_regionkey, r_name,r_comment)

supplier(s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment)

10. Find the total price of orders made by customers from "EUROPE" in 1996. 11. How many distinct customers received at least a 4% discount at least one time? 12. Find the total number of finished orders for customers from each region and print the result in de- creasing order of these numbers. (see orderstatus; F stands for finished) 13. Find the average account balance of all the customers from EUROPE" i the "MACHINERY" market 14. Find how many "1-URGENT" priority orders have been posted by customers from "Brazil" between 15. Find the total number of "3-MEDIUM" priority orders supplied by suppliers in each country each year. segment 1994 and 1997, included Print the country, the year, and the number of orders.

Explanation / Answer

If you have any doubts, please give me comment...

-- 10)

SELECT SUM(o_total_price)

FROM orders, customer, nation

WHERE c_custkey = o_custkey AND c_nationkey = n_nationkey AND n_name = 'EUROPE' AND YEAR(o_orderdate) = 1996;

-- 11)

SELECT COUNT(DISTINCT o_custkey)

FROM orders, lineitem

WHERE o_orderkey = l_orderkey AND l_discount>=4;

-- 12)

SELECT COUNT(DISTINCT o_orderkey)

FROM orders, customers, nation, region

WHERE o_orderkey = l_orderkey AND c_nationkey = n_nationkey AND n_regionkey = r_regionkey AND o_orderstatus = 'F'

GROUP BY r_name;

-- 13)

SELECT AVG(acctbal)

FROM customer, nation

WHERE c_nationkey = n_nationkey AND n_name = 'EUROPE' AND c_mktsegment = 'MACHINERY';

-- 14)

SELECT COUNT(*)

FROM orders, customer, nation

WHERE o_orderkey = l_orderkey AND c_nationkey = n_nationkey AND n_name = 'Brazil' AND YEAR(o_orderdate) BETWEEN 1994 AND 1997 AND o_orderpriority = '1-URGENT';

SELECT n_name country, YEAR(o_orderdate) year, COUNT(*) AS no_of_orders

FROM orders, lineitem, supplier

WHERE o_orderkey = l_orderkey AND l_suppkey = s_suppkey AND s_nationkey = n_nationkey AND YEAR(o_orderdate) BETWEEN 1994 AND 1997 AND o_orderpriority = '3-MEDIUM'

GROUP BY n_name, o_orderdate;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.