Run the following script to setup the fuel prices tables (if you have not done so already):

FuelPricesSetup.sql

/***********************

 Source:

 Bureau of Labor Statistics

 http://www.bls.gov/

 **********************/

SET NOCOUNT ON

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('FuelPrices') AND type IN ('U'))

 DROP TABLE FuelPrices

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('FuelPeriods') AND type IN ('U'))

 DROP TABLE FuelPeriods

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('FuelItems') AND type IN ('U'))

 DROP TABLE FuelItems

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('FuelAreas') AND type IN ('U'))

 DROP TABLE FuelAreas

GO

CREATE TABLE FuelAreas

(

 AreaCode VARCHAR(4) NOT NULL,

 AreaName VARCHAR(255) NULL,

 CONSTRAINT pk_Areas PRIMARY KEY CLUSTERED (AreaCode ASC)

)

GO

CREATE TABLE FuelItems

(

 ItemCode VARCHAR(8) NOT NULL,

 ItemName VARCHAR(255) NULL,

 CONSTRAINT pk_Items PRIMARY KEY CLUSTERED (ItemCode ASC)

)

GO

CREATE TABLE FuelPeriods

(

 PeriodCode VARCHAR(3) NOT NULL,

 PeriodAbbr VARCHAR(255) NULL,

 PeriodName VARCHAR(255) NULL,

 CONSTRAINT pk_Periods PRIMARY KEY CLUSTERED (PeriodCode ASC)

) 

GO

CREATE TABLE FuelPrices

(

 AreaCode VARCHAR(4) NOT NULL,

 ItemCode VARCHAR(8) NOT NULL,

 DataYear DECIMAL(4, 0) NOT NULL,

 PeriodCode VARCHAR(3) NOT NULL,

 Value DECIMAL(5, 3) NULL,

 CONSTRAINT pk_GasolinePrices PRIMARY KEY CLUSTERED (AreaCode ASC, ItemCode ASC, DataYear ASC, PeriodCode ASC)

)

GO

INSERT FuelAreas (AreaCode, AreaName) VALUES ('0000', 'U.S. city average');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('0100', 'Northeast urban');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('0200', 'Midwest urban');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('0300', 'South urban');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('0400', 'West urban');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A000', 'City size A');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A100', 'Northeast Size A');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A101', 'New York-Northern New Jersey-Long Island, NY-NJ-CT-PA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A102', 'Philadelphia-Wilmington-Atlantic City, PA-NJ-DE-MD');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A103', 'Boston-Brockton-Nashua, MA-NH-ME-CT');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A104', 'Pittsburgh, PA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A200', 'Midwest Size A');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A207', 'Chicago-Gary-Kenosha, IL-IN-WI');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A208', 'Detroit-Ann Arbor-Flint, MI');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A209', 'St. Louis, MO-IL');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A210', 'Cleveland-Akron, OH');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A211', 'Minneapolis-St. Paul, MN-WI');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A212', 'Milwaukee-Racine, WI');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A213', 'Cincinnati-Hamilton, OH-KY-IN');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A214', 'Kansas City, MO-KS');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A300', 'South Size A');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A311', 'Washington-Baltimore, DC-MD-VA-WV');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A316', 'Dallas-Fort Worth, TX');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A318', 'Houston-Galveston-Brazoria, TX');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A319', 'Atlanta, GA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A320', 'Miami-Fort Lauderdale, FL');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A400', 'West Size A');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A421', 'Los Angeles-Riverside-Orange County, CA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A422', 'San Francisco-Oakland-San Jose, CA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A423', 'Seattle-Tacoma-Bremerton, WA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A424', 'San Diego, CA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A425', 'Portland-Salem, OR-WA');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A426', 'Honolulu, HI');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A427', 'Anchorage, AK');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('A433', 'Denver-Boulder-Greeley, CO');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('D000', 'City size D');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('D200', 'Midwest Size D');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('D300', 'South Size D');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('X000', 'City size B/C');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('X100', 'Northeast Size B/C');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('X200', 'Midwest Size B/C');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('X300', 'South Size B/C');

INSERT FuelAreas (AreaCode, AreaName) VALUES ('X400', 'West Size B/C');

INSERT FuelItems (ItemCode, ItemName) VALUES ('74712 ', 'Gasoline, leaded regular (cost per gallon/3.8 liters)');

INSERT FuelItems (ItemCode, ItemName) VALUES ('74713 ', 'Gasoline, leaded premium (cost per gallon/3.8 liters)');

INSERT FuelItems (ItemCode, ItemName) VALUES ('74714 ', 'Gasoline, unleaded regular, per gallon/3.785 liters');

INSERT FuelItems (ItemCode, ItemName) VALUES ('74715 ', 'Gasoline, unleaded midgrade, per gallon/3.785 liters');

INSERT FuelItems (ItemCode, ItemName) VALUES ('74716 ', 'Gasoline, unleaded premium, per gallon/3.785 liters');

INSERT FuelItems (ItemCode, ItemName) VALUES ('74717 ', 'Automotive diesel fuel, per gallon/3.785 liters');

INSERT FuelItems (ItemCode, ItemName) VALUES ('7471A ', 'Gasoline, all types, per gallon/3.785 liters');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M01', 'JAN', 'January');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M02', 'FEB', 'February');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M03', 'MAR', 'March');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M04', 'APR', 'April');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M05', 'MAY', 'May');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M06', 'JUN', 'June');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M07', 'JUL', 'July');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M08', 'AUG', 'August');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M09', 'SEP', 'September');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M10', 'OCT', 'October');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M11', 'NOV', 'November');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M12', 'DEC', 'December');

INSERT FuelPeriods (PeriodCode, PeriodAbbr, PeriodName) VALUES ('M13', 'AN AV', 'Annual Average');

GO

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M01', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M02', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M03', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M04', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M05', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M06', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M07', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M08', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M09', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M10', 0.402);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M11', 0.418);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M12', 0.437);

INSERT FuelPrices VALUES ('0000', '74712', 1973, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M01', 0.465);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M02', 0.491);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M03', 0.528);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M04', 0.537);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M05', 0.55);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M06', 0.556);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M07', 0.558);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M08', 0.554);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M09', 0.55);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M10', 0.534);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M11', 0.528);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M12', 0.528);

INSERT FuelPrices VALUES ('0000', '74712', 1974, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M01', 0.532);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M02', 0.533);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M03', 0.534);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M04', 0.54);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M05', 0.55);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M06', 0.567);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M07', 0.591);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M08', 0.595);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M09', 0.597);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M10', 0.594);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M11', 0.589);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M12', 0.586);

INSERT FuelPrices VALUES ('0000', '74712', 1975, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M01', 0.584);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M02', 0.578);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M03', 0.569);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M04', 0.567);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M05', 0.576);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M06', 0.592);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M07', 0.599);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M08', 0.603);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M09', 0.604);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M10', 0.603);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M11', 0.602);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M12', 0.6);

INSERT FuelPrices VALUES ('0000', '74712', 1976, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M01', 0.6);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M02', 0.607);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M03', 0.612);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M04', 0.619);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M05', 0.626);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M06', 0.63);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M07', 0.631);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M08', 0.63);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M09', 0.629);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M10', 0.627);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M11', 0.626);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M12', 0.627);

INSERT FuelPrices VALUES ('0000', '74712', 1977, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M01', 0.607);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M02', 0.605);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M03', 0.604);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M04', 0.606);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M05', 0.611);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M06', 0.62);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M07', 0.629);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M08', 0.638);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M09', 0.644);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M10', 0.645);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M11', 0.65);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M12', 0.659);

INSERT FuelPrices VALUES ('0000', '74712', 1978, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M01', 0.668);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M02', 0.681);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M03', 0.706);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M04', 0.753);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M05', 0.797);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M06', 0.856);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M07', 0.908);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M08', 0.943);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M09', 0.973);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M10', 0.982);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M11', 0.994);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M12', 1.018);

INSERT FuelPrices VALUES ('0000', '74712', 1979, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M01', 1.086);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M02', 1.159);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M03', 1.202);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M04', 1.212);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M05', 1.215);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M06', 1.217);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M07', 1.216);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M08', 1.21);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M09', 1.197);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M10', 1.188);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M11', 1.188);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M12', 1.197);

INSERT FuelPrices VALUES ('0000', '74712', 1980, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M01', 1.238);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M02', 1.321);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M03', 1.352);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M04', 1.344);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M05', 1.333);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M06', 1.324);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M07', 1.315);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M08', 1.31);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M09', 1.305);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M10', 1.299);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M11', 1.297);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M12', 1.293);

INSERT FuelPrices VALUES ('0000', '74712', 1981, 'M13', NULL);

INSERT FuelPrices VALUES ('0000', '74712', 1982, 'M01', 1.285);

INSERT FuelPrices VALUES ('0000', '74712', 1982, 'M02', 1.26);

INSERT FuelPrices VALUES ('0000', '74712', 1982, 'M03', 1.206);

INSERT FuelPrices VALUES ('0000', '74712', 1982, 'M04', 1.148);

Here is an ERD of the tables. Note that there are no foreign keys setup for these tables.

Write a single query that shows the average fuel price each year for item code 74714 in the area code A207 , during the summer months of June through August, during the 1990's. There should be one row per year. Order the results by year. Make sure all columns have reasonable column headings. Round results to two decimal places.

For this query you may use the codes for the periods (e.g. M06 for June).

Screenshot #5: Fuel Prices Query