get help with homework

Write SQL queries for 7 out of the 10 following questions:
A. Compute the average lo_quantity for each c_city and c_nation combination.
B. Find the minimum, maximum, and average discount (in a single query) for suppliers whose
address includes at least one comma (“,”) and who are located outside of Canada.
C. For each combination of even year (1992, 1994, …) and odd month (1, 3, …) find the number
of different p_color and c_mktsegment values
D. P_container entry always consists of 2 words separated by a space. For each p_container
where the 2nd word has exactly 4 characters (e.g., ‘WRAP DRUM’) find the number of unique
lo_shippriority and c_mktsegment values. (NOTE: You can use LIKE or REGEXP_LIKE, as
you prefer).
E. Find all lineorder entries (lo_orderdate, lo_orderpriority, lo_shippriority, lo_revenue and
lo_tax) for suppliers whose city has at least one space (e.g., ‘BRAZIL 5’ but not
‘INDONESIA5’). Your result should be sorted by d_dayofweek in ascending order.
F. Find the dates (d_dayofweek, d_month, d_year) on which at least one lineorder record had
higher-than-average lo_quantity. No date listing should appear more than once and your output
should be sorted by d_year in decreasing order.
G. Find the c_nation (or s_nation) that has the greatest, combined amount of both suppliers and
customers. (e.g., if Canada has 100 suppliers and 100 customers, the total amount would be 200
for Canada).
H. Find all parts where the p_color appears in the p_name. (e.g., the p_color of ‘red’ appears in
the p_name ‘red brick’).
I. Find the unique set of customer names that ordered parts with a larger than the average p_size
from a supplier with a s_region that is different than c_region.
J. Using a single query, return two values: 1) the total number of parts where the p_type contains
‘COPPER’, and 2) the total number of parts where the p_type contains ‘STEEL’. Your output
should contain two columns (COPPER and STEEL) and one record that is the total number of
parts for each.

Rate this post