Can anyone assist me? steps are number 1-10 below in the project.

I am having trouble figuring out the portion of the project below. Part of my confusion is most definitely the way it is poorly written. However, what is giving me problems is below. My problem is how to take the above demographics statement, add a subquery from step 9, add the case statement, and add the demand to finish question number 10? (I've added my code for steps 7 and 9 below and the complete project requirements after that):

10. Use that query from Step 9 as a subquery and join it to the demographics table using the following case statement:

demographics.age =

case when temp_pop.age < 18 then '00 to 17' when temp_pop.age > 64 then '65 to 80+' else '18 to 64' end

temp_pop is an alias for the subquery. Use the following calculation for the demand output:round(sum(temp_pop.total_pop * demographics.coefficient)) as demand Output the demand grouped by year and education level.

______

The previous code I have (which is likely not correct, but does function) is:

SELECT

age as `AGE/YEAR`,

SUM(pop_total) as Population

FROM pop_proj

GROUP BY age with rollup

UNION

SELECT

year as YEAR,

SUM(pop_total) as Population

FROM pop_proj

GROUP BY year with rollup;

_________________

The Demographics execution code from step 7 is:

SELECT

A.Age, B.`Education Attainment`,

(IFNULL(B.pop_count, 0)/A.pop_count)*100 `Coefficient`

FROM

(SELECT

Age, SUM(`Population Count`) pop_count

FROM educational_attainment

GROUP BY Age) A

LEFT JOIN

(SELECT

sum(`Population Count`) pop_count, Age, `Education Attainment`

FROM educational_attainment

GROUP BY Age, `Education Attainment`) B

ON A.Age=B.Age;

______

complete project:

Database Design and Analysis

The following database project will create an educational attainment demand forecast for the state ofCalifornia for years greater than 2010. The demand forecast is the expected number of population who have obtained a certain level of education. The population is divided into age groups and education attainment is divided into different levels. The population of each group is estimated for each year up to year 2050. Implement the following steps to obtain and educational demand forecast for the state of California. The files can be downloaded below (see assignment page).

1. Create a ca_pop schema in your MySQL database.

2. Using your ca_pop schema, create an educational_attainment table which columns match the columns

   in the Excel spreadsheet ca_pop_educational_attainment.csv.

3. Using your ca_pop schema, create a pop_proj table which columns match the columns in the Excel

   spreadsheet pop_proj_1970_2050.csv.

4. Using the data loading technique for a csv file you learned in Module 1, load the data inca_pop_educational_attainment.csv into the table educational_attainment.

5. Using the data loading technique for a csv file you learned in Module 1, load the data inpop_proj_1970_2050.csv into the table pop_proj.

6. Write a query to select the total population in each age group.

7. Use the query from Step 6 as a subquery to find each type of education attained by the population in

   that age group and the fraction of the population of that age group that has that educational attainment. Label the fraction column output as coefficient. For instance, the fraction of the population in age group 00 - 17 who has an education attainment of Bachelor's degree or higher is 0.0015, which is the coefficient.

8. Create a demographics table from the SQL query from Step 7.

9. Create a query on the pop_proj table which shows the population count by date_year and age.

10. Use that query from Step 9 as a subquery and join it to the demographics table using the following case

    statement:

demographics.age =

case when temp_pop.age < 18 then '00 to 17' when temp_pop.age > 64 then '65 to 80+' else '18 to 64' end

temp_pop is an alias for the subquery. Use the following calculation for the demand output:round(sum(temp_pop.total_pop * demographics.coefficient)) as demand Output the demand grouped by year and education level.

I cannot upload the data files because they are too large. Below are the two tables that were included with the project, with only a few lines to provide the structure:

education_attainment (Table)

ID	Year	Age	Gender	Education Attainment	Personal Income	Population Count
1	01/01/2008 12:00:00 AM	00 to 17	male	Children under 15	No Income	650889
62	01/01/2008 12:00:00 AM	18 to 64	Female	High school or equiv	$5,000 to $9,999	241389
108	01/01/2008 12:00:00 AM	65 to 80+	Male	Some college, less t	$50,000 to $74,999	22443

pop_proj (Table)

ID	fips	county	year	age	pop_female	pop_male	pop_total
1	6001	ALAMEDA	1970	0	8533	8671	17204
973	6001	ALAMEDA	1979	63	4652	4105	8757

Step 8 required the creation of a third Table named "Demographics" which looks like:

Age	Education Attainment	Coefficient
00 to 17	Some college, less t	0.9353
18 to 64	Bachelors degree or	30.3232
65 to 80+	Bachelors degree or	29.7863