查询语句
--Q1.1select sum(lo_revenue) as revenuefrom lineorder join dates on lo_orderdate = d_datekeywhere year(d_datekey) = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;--Q1.2select sum(lo_revenue) as revenuefrom lineorderjoin dates on lo_orderdate = d_datekeywhere d_yearmonthnum = 199401and lo_discount between 4 and 6and lo_quantity between 26 and 35;--Q1.3select sum(lo_revenue) as revenuefrom lineorderjoin dates on lo_orderdate = d_datekeywhere d_weeknuminyear = 6 and year(d_datekey) = 1994and lo_discount between 5 and 7and lo_quantity between 26 and 35;--Q2.1select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brandfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin part on lo_partkey = p_partkeyjoin supplier on lo_suppkey = s_suppkeywhere p_category = 'MFGR#12' and s_region = 'AMERICA'group by year(d_datekey), p_brandorder by year, p_brand;--Q2.2select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brandfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin part on lo_partkey = p_partkeyjoin supplier on lo_suppkey = s_suppkeywhere p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'group by year(d_datekey), p_brandorder by year, p_brand;--Q2.3select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brandfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin part on lo_partkey = p_partkeyjoin supplier on lo_suppkey = s_suppkeywhere p_brand = 'MFGR#2239' and s_region = 'EUROPE'group by year(d_datekey), p_brandorder by year, p_brand;--Q3.1select c_nation, s_nation, year(d_datekey) as year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere c_region = 'ASIA' and s_region = 'ASIA' and year(d_datekey) between 1992 and 1997group by c_nation, s_nation, year(d_datekey)order by year asc, lo_revenue desc;--Q3.2select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES'and year(d_datekey) between 1992 and 1997group by c_city, s_city, year(d_datekey)order by year asc, lo_revenue desc;--Q3.3select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere (c_city='UNITED KI1' or c_city='UNITED KI5')and (s_city='UNITED KI1' or s_city='UNITED KI5')and year(d_datekey) between 1992 and 1997group by c_city, s_city, year(d_datekey)order by year asc, lo_revenue desc;--Q3.4select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = '199712'group by c_city, s_city, year(d_datekey)order by year(d_datekey) asc, lo_revenue desc;--Q4.1select year(d_datekey) as year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profitfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeyjoin part on lo_partkey = p_partkeywhere c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')group by year(d_datekey), c_nationorder by year, c_nation;--Q4.2select year(d_datekey) as year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profitfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeyjoin part on lo_partkey = p_partkeywhere c_region = 'AMERICA'and s_region = 'AMERICA'and (year(d_datekey) = 1997 or year(d_datekey) = 1998)and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')group by year(d_datekey), s_nation, p_categoryorder by year, s_nation, p_category;--Q4.3select year(d_datekey) as year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profit, c_region, s_nation, p_categoryfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeyjoin part on lo_partkey = p_partkeywhere(year(d_datekey) = 1997 or year(d_datekey) = 1998)and s_nation='ALGERIA'group by year(d_datekey), s_city, p_brand, c_region, s_nation, p_categoryorder by year, s_city, p_brand;
经验总结扩展阅读
- 怎么从行驶证上看出车辆是否过户
- 国际象棋怎么玩，新手入门（国际象棋新手入门必看)
- 研究生从报考到考试的流程
- 煮鸡蛋正确的方法
- 春运时间什么时候开始到结束 春运是从什么时间开始到结束
- 切除子宫和闭经一样吗
- 从零开始学Graph Database：什么是图
- SQL基础语句入门
- 咸鱼之王龙鱼义从怎么搭配
- 哪些星座女婚后从不乱花钱