You are a young entrepreneur looking to start a profitable business, but you have no idea what business could be profitable. You decided to do your homework by looking at some consumer spending data from the Bureau of Economic Analysis. You downloaded some data on Real Personal Consumption Expenditures (PCE) – what are consumers spending money on. The PCE data is organized in two different ways. The first organization is by Major Type of Product - what products are consumers spending on. The second way is by Major Function - what purpose/function are consumers spending on. You decided to extract and load the data into a DBMS using the following schema.
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.
Sample data for testing your queries can be found here: http://www2.hawaii.edu/~lipyeow/ics321/core/ics321a2.zip
You need to load the sample data into the tables using the Oracle SQL Loader utility. An example of how to use the load utility is given here:
http://www2.hawaii.edu/~lipyeow/ics321/core/oracleload.zip.
You should also read the documentation on the load utility http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_concepts.htm
In order to understand trends in consumer spending, you need to write exactly one SQL query to answer each of the following questions. Unless otherwise stated, all floating point numbers should be displayed with two decimal points (look up how to do this with the “DECIMAL” function).
Note that your SQL queries must run correct in Oracle DBMS to get credit. You should definitely develop the queries using the Oracle DBMS instance on your laptop or desktop.
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.
Which function, year, and quarter has the smallest PCE of all time? The result should have the columns: func, year, quarter, pce.
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.
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.
What is the annual PCE for each function ? The result should have the columns: func, year, annualpce.
What is the function, year, and PCE with the most annual PCE ? The result should have the columns: func, year, annualpce.
Which year has the largest total PCE ? (Use the pcefunc table for this question). The result should have the columns: year, annualpce.
What is the total PCE for each level two product in the year 2009 ? The result should have the columns: prodlevel2, totalpce.
What is the annual PCE of “Goods” for each year ? The result should have the columns: year, annualpce.
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.
You are required to submit your solutions to this assignment electronically using laulima. You should submit your solutions as attachments to laulima->assignments. Name your solution files q1.sql, q2.sql, q3.sql … q10.sql. Each file should only contain the SQL query followed by the semi-colon (statement terminator). You should not put connect statements into the sql files. Each file should be executable by Oracle DBMS using the “@q1.sql” construct in the sqlplus prompt.