Zookal
Zookal

We’d love to hear any feedback or comment from you!

© 2011-2021 Zookal Pty Ltd

View question and answer

From our collection of questions and answers
Engineering · Computer Science
Question details

I need help with the last step of this problem. The requirement is to take the query in Step 7, add the query of Step 9 as a sub query while finding out the demand using a case statement. I cannot figure out how this should be done.

My Code for Step 9 is:

______

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;

My code for 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.

DATA (TABLES) PROVIDED

_______

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

Bachelor’s degree or

30.3232

65 to 80+

Bachelor’s degree or

29.7863

Answer
Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.

Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.Find step-by-step answers from expert tutors to questions asked by students like you. Start 14-day free trial.