Database Final Exam Spring

Database Final Exam Spring 2023 When you have completed the assignment, submit your completed document with the code and answers to the assignment drop box. Do not create new permanent tables. I must be able to run your code using the three assigned tables. Only use these tables: `classdata-324219.ppp.pppfull_geo_c` `classdata-324219.ppp.census_income` `bigquery-public-data.census_bureau_acs.censustract_2020_5yr` The geo_id field connects the three tables. See the attachment to find your assigned bank and assessment areas. Answer each of the questions in the document directly under the question. Below all of the questions, include your code and indicate the question it addresses. Part 1: Questions Limit these results to loans that mapped to businesses with good address in census tracts with populations >= 100?

For these questions, use the American Community Survey (ACS) values: total_pop, hispanic_pop, black_pop, white_pop. (`bigquery-public-data.census_bureau_acs.censustract_2020_5yr`)

1. For your bank in your bank’s designated counties, how many loans mapped to census tracts with populations >= 100?

2. For your bank in your bank’s designated counties, what percentage of those loans went to businesses in census tracts that were majority black?

3. For your bank in your bank’s designated counties, what percentage of those loans went to businesses in census tracts that were majority Hispanic?

4. For your bank in your bank’s designated counties, what percentage of those loans went to businesses in census tracts that were majority white?

(Note: Don’t break down the results by individual county.)

5. For all banks that made loans to business in your bank’s designated counties, how many loans went to census tracts with populations >= 100?

6. For all banks that made loans to business in your bank’s designated counties, what percent of those loans (tract populations >= 100) went to businesses in census tracts that were majority black?

7. For all banks that made loans to business in your bank’s designated counties, what percent of those loans (tract populations >= 100) went to businesses in census tracts that were majority Hispanic?

8. For all banks that made loans to business in your bank’s designated counties, what percent of those loans (tract populations >= 100) went to businesses in census tracts that were majority white?

Part 2: Questions Using the income table: `classdata-324219.ppp.census_income` (see guide below). Limit these results to loans that mapped to businesses with good address in tracts with populations >= 100?

9. For your bank, what percent of its loans went to businesses in census tracts in your bank’s designated counties where the median family income is > 0% and < 50% of the MSA/MD median family income (Income_Level_Ind =1)?

10. For your bank, what percent of its loans went to businesses in census tracts in your bank’s designated counties where the median family income is >= 50% and < 80% of the MSA/MD median family income?

11. For your bank, what percent of its loans went to businesses in census tracts in your bank’s designated counties where the median family income is >= 80% and < 120% of the MSA/MD median family income?

12. For your bank, what percent of its loans went to businesses in census tracts in your bank’s designated counties where the median family income is >= 120% of the MSA/MD median family income?

(Note: Don’t break down the results by individual county.) 13. For all banks that made loans to business in your bank’s designated counties, what percent of

those loans (tract populations >= 100) went to businesses in census tracts median family income is > 0% and < 50% of the MSA/MD median family income?

14. For all banks that made loans to business in your bank’s designated counties, what percent of those loans (tract populations >= 100) went to businesses in census tracts median family income is >= 50% and < 80% of the MSA/MD median family income?

15. For all banks that made loans to business in your bank’s designated counties, what percent of those loans (tract populations >= 100) went to businesses in census tracts median family income is >= 80% and < 120% of the MSA/MD median family income?

16. For all banks that made loans to business in your bank’s designated counties, what percent of those loans (tract populations >= 100) went to businesses in census tracts median family income is >= 120% of the MSA/MD median family income?

References

Census Notes `classdata-324219.ppp.census_income` Field Name Definition Year Year of data (2022) MSA Code MSA Code State Code State Code County Code County Code Tract Tract Code

MSA Med Family Income Median Family Income (MFI) of the Metropolitan Statistical Area/Metropolitan Division (MSA/MD) (or statewide non-MSA/MD) in which the tract resides. Tract Med Family Income Tract level MFI Income_Per Tract level MFI divided by the MSA/MD level MFI, and the result is truncated to two decimal places. Income_Level_Ind Income Level Indicator identifies each census tract as not available, low, moderate, middle, or upper. It is determined from the Income % using the following categories: The codes are: 0 — Not Available: if tract median family income = 0 1 — Low: if tract median family income is > 0% and < 50% of the MSA/MD median family income 2 — Moderate: if tract median family income is >= 50% and < 80% of the MSA/MD median family income 3 — Middle: if tract median family income is >= 80% and < 120% of the MSA/MD median family income 4 — Upper: if tract median family income is >= 120% of the MSA/MD median family income

ACS General Census

Area Type GEOID Structure Number of Digits

Example Geographic Area Example GEOID

State STATE 2 Texas 48

County STATE+COUNTY 2+3=5 Harris County, TX

48201

County Subdivision

STATE+COUNTY+COUSUB 2+3+5=10 Pasadena CCD, Harris County, TX

4820192975

Places STATE+PLACE 2+5=7 Houston, TX 4835000

Census Tract

STATE+COUNTY+TRACT 2+3+6=11 Census Tract 2231 in Harris County, TX

48201223100

Block Group

STATE+COUNTY+TRACT+BLOCK GROUP

2+3+6+1=12 Block Group 1 in Census Tract 2231 in Harris County, TX

482012231001

Block* STATE+COUNTY+TRACT+BLOCK 2+3+6+4=15 Block 1050 in 482012231001050

(Note – some blocks also contain a one character suffix (A, B, C, ect.)

Census Tract 2231 in Harris County, TX

For more information see the links below: What is the Community Reinvestment Act (CRA)? https://www.federalreserve.gov/consumerscommunities/cra_about.htm The Equal Credit Opportunity Act https://www.justice.gov/crt/equal-credit-opportunity-act-3