Community Colleges

-- Let's look at the total course offerings for each community college in Oregon 
-- and consider the respective college's county rankings.

select
    courses.institution,
    count(courses.title) as total_courses,
    colleges.county,
    counties.county_ranking
from oregon_education.oregon_community_colleges as colleges
inner join oregon_education.course_listings as courses
on colleges.school = courses.institution
inner join oregon_education.oregon_counties as counties
on colleges.county = counties.county
group by courses.institution, colleges.county, counties.county_ranking
order by county_ranking;

-- What correlation exists between the total courses in a given county 
-- and that county's education ranking?

select
    corr("total_courses", "county_ranking")
from(
    select
        courses.institution,
        count(courses.title) as total_courses,
        colleges.county,
        counties.county_ranking
    from oregon_education.oregon_community_colleges as colleges
    inner join oregon_education.course_listings as courses
    on colleges.school = courses.institution
    inner join oregon_education.oregon_counties as counties
    on colleges.county = counties.county
    group by courses.institution, colleges.county, counties.county_ranking
) as a

-- Let's look at the community college in the lowest-ranking county from this list. 
-- What does its course breakdown look like?

select 
    courses.activity_code,
    count(courses.title) as number_of_courses,
    codes.title
from oregon_education.course_listings as courses
left join oregon_education.activity_codes as codes
on courses.activity_code = codes.activity_code
where courses.institution = 'Treasure Valley Community College'
    and courses.activity_code is not null
group by courses.activity_code, codes.title
order by number_of_courses desc;

-- What happens when we compare the course breakdown for the community college 
-- from the lowest-ranking county to the course breakdown for the community colleges 
-- from the highest-ranking county?

select 
    courses.activity_code,
    count(courses.title) as number_of_courses,
    codes.title
from oregon_education.course_listings as courses
left join oregon_education.activity_codes as codes
on courses.activity_code = codes.activity_code
left join oregon_education.oregon_community_colleges as colleges
on courses.institution = colleges.school
where colleges.county = 'Multnomah'
    and codes.activity_code is not null
group by courses.activity_code, codes.title
order by number_of_courses desc;

-- Based on data from the Counties table, we're drilling down Marion, Linn,
-- and Douglas counties.
-- Let's start with their respective community colleges. 

select 
    school,
    county
from oregon_education.oregon_community_colleges
where county = 'Marion'
    or second_location_county = 'Marion'
    or third_location_county = 'Marion'
    or fourth_location_county = 'Marion'
    or fifth_location_county = 'Marion';
    
-- Marion County has 1 community college: Chemeketa Community College.

select 
    school,
    county
from oregon_education.oregon_community_colleges
where county = 'Linn'
    or second_location_county = 'Linn'
    or third_location_county = 'Linn'
    or fourth_location_county = 'Linn'
    or fifth_location_county = 'Linn';
    
-- Linn County has 1 community college: Linn-Benton Community College.

select 
    school,
    county
from oregon_education.oregon_community_colleges
where county = 'Douglas'
    or second_location_county = 'Douglas'
    or third_location_county = 'Douglas'
    or fourth_location_county = 'Douglas'
    or fifth_location_county = 'Douglas';
    
-- Douglas County has 1 community college: Umpqua Community College.

-- What is the distribution of course offerings at Chemeketa Community
-- College (located in Marion County)?

select 
    courses.institution, 
    courses.activity_code,
    count(courses.activity_code) as total,
    codes.title
from oregon_education.course_listings as courses
left join oregon_education.activity_codes as codes
on courses.activity_code = codes.activity_code
where courses.institution = 'Chemeketa Community College'
group by courses.institution, courses.activity_code, codes.title
order by count(courses.activity_code) desc;

-- How many of the courses offered through Chemeketa Community College
-- actively work toward the adult education goals set by the state? 

select 
    courses.activity_code,
    count(courses.activity_code) as total,
    case
        when codes.activity_code = 100
            or codes.activity_code = 310
            or codes.activity_code = 320
            or codes.activity_code = 330
            or codes.activity_code = 340
            or codes.activity_code = 350
            or codes.activity_code = 351
            or codes.activity_code = 352
            or codes.activity_code = 360
            or codes.activity_code = 361
            or codes.activity_code = 362
            or codes.activity_code = 363
            or codes.activity_code = 510
            or codes.activity_code = 511
            or codes.activity_code = 512
            or codes.activity_code = 513 then 'No'
        when codes.activity_code = 210
            or codes.activity_code = 211
            or codes.activity_code = 220
            or codes.activity_code = 230 then 'Yes'
    else 'No course data'
    end qualifies_toward_goal
from oregon_education.course_listings as courses
left join oregon_education.activity_codes as codes
on courses.activity_code = codes.activity_code
where courses.institution = 'Chemeketa Community College'
group by courses.activity_code, codes.activity_code
order by count(courses.activity_code) desc;

-- Of the 2,930 total courses offered at Chemeketa Community College,
-- 1,613 (55.0%) actively work toward the adult education
-- attainment goals set by the state. 
-- This is in the county that ranks #5 in the state for the most
-- additional adult education awards needed by 2030.