http://www.bea.gov/iTable/iTable.cfm?ReqID=12&step=1#reqid=12&step=3&isuri=1&1203=15). You created the following tables:
Code
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)
);
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
This post was edited by bakalolo on Mar 13 2013 03:50pm