Downloaded some data on Real Personal Consumption Expenditures by Major Type of

Question

Downloaded some data on Real Personal Consumption Expenditures by Major Type of Product and by Major Function the consumer spending data from the Bureau of Economic Analysis (http://www.bea.gov). and decided to extract and load the data into a DBMS. The following tables are created:

create table prodhierarchy (
   level1 varchar(80),
   level2 varchar(80),
   level3 varchar(80) not null primary key);

create table pceprod (
   prod varchar(80) not null,
   year int not null,
   quarter int not null,
   pce int,
   primary key(prod,year,quarter),
   foreign key(prod) references prodhierarchy
);

create table pcefunc (
   func varchar(80) not null,
   year int not null,
   quarter int not null,
   pce int,
   primary key(func,year,quarter)
);

The prodhierarchy table stores a three-level product hierarchy.

The pceprod table stores the personal consumption expenditure (PCE) for each level-3 product, for each quarter of each year in millions of dollars in the United States.

The pcefunc table stores the PCE for each function, quarter and year. The attachments contain the DDLs and sample data.

Need to write exactly one SQL query to answer each of the 10 questions by utilizing the consumer spending data from the Bureau of Economic Analysis (http://www.bea.gov). Unless otherwise stated, all floating point numbers should be displayed with two decimal points.

Q1. How much money is spent for what purpose by consumers in the U.S. in the first quarter of 2010 ? Another way to phrase this is : What is the personal consumption expenditure of each function in the first quarter of 2010 ?
-The result should have the columns: func, pce

Q2. Which function, year, and quarter has the smallest PCE of all time?
-The result should have the columns: func, year, quarter, pce

Q3. What is the function (and the corresponding PCE) that consumers spent the most money on in the first quarter of 2010 ?
-The result should have the columns: func, pce

Q4. Which function, year and quarter saw the biggest increase in PCE from year to year?
-The result should have the columns: func, year2, year1, quarter, pce2, pce1

Hint: think of finding for each function and quarter the difference in pce between year x and year x-1. Then do that for every year x.

Q5. What is the annual PCE for each function ?
-The result should have the columns: func, year, annualpce

Q6. What is the function, year, and PCE with the most annual PCE ?
-The result should have the columns: func, year, annualpce

Q7. Which year has the largest total PCE ? (Use the pcefunc table for this question).
-The result should have the columns: year, annualpce

Q8.  What is the total PCE for each level two product in the year 2009 ?
-The result should have the columns: prodlevel2, totalpce

Q9. What is the annual PCE of "Goods" for each year ?
-The result should have the columns: year, annualpce

Q10. What is the difference between the annual PCE for "Goods" and the annual PCE for "Services" (expressed as a percentage over the annual PCE for Services) for the years when the annual PCE for "Services" is greater than the annual PCE for "Goods"? You want the results sorted by most recent years first and the percentage expressed in two decimal points.
-The result should have the columns: year, diffpce

Explanation / Answer

create table prodhierarchy (
level1 varchar(80),
level2 varchar(80),
level3 varchar(80) not null primary key);

create table pceprod (
prod varchar(80) not null,
year int not null,
quarter int not null,
pce int,
primary key(prod,year,quarter),
foreign key(prod) references prodhierarchy
);

Note: In first table there is no column prod but you referred that column in second table.

create table pcefunc (
func varchar(80) not null,
year int not null,
quarter int not null,
pce int,
primary key(func,year,quarter)
);

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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