1. For each state , list the counties where the number of Doctor degree is less than the average number of Doctor degree *for that state*. Show county_state and the number of Doctor_degree for that county. Order by state, then county

example output

Aleutians East Borough, Alaska | 0 Aleutians West Census Area, Alaska | 9 Bethel Census Area, Alaska | 84 Bristol Bay Borough, Alaska | 2

#2 Same as #1, but list the counties where the ratio of doctor degree to the county's population is less than the average ratio of the state's doctor_degree *for that state*.

example output

Aleutians East Borough, Alaska | 0.00000000000000000000 Aleutians West Census Area, Alaska | 0.00219138056975894814 Bethel Census Area, Alaska | 0.00868037614963315077

#3 List the countie_state where the number of people with a Doctor_degree diploma is above the average for their state. Show county_state and Doctor_degree. Order by state , county_state with both *DESCENDING*.

example output

Uinta County, Wyoming | 4326 Sweetwater County, Wyoming | 7629 Sheridan County, Wyoming | 4367

#4 List the county names and their respective counts where there are duplicate county names (more than one) in any state.

exmaple output

dallas | 2

bob | 2

there is bigger data than this, but this should be enough for test

Correlated subqueries. Most of these will require a correlated subquery.

I need PSQL queries for all questions

county_state

state

population

Doctor_degree

county

Autauga County, Alabama	al	37166	510	dallas
Baldwin County, Alabama	al	146989	2992	dallas
Barbour County, Alabama	al	18173	92	tarrant
Bibb County, Alabama	al	15780	87	tarrant
Blount County, Alabama	al	39627	240	bib
Bullock County, Alabama	al	7104	142	bob
Butler County, Alabama	al	13888	136	bob
Calhoun County, Alabama	al	79135	987	beb
Chambers County, Alabama	al	23564	245	a
Cherokee County, Alabama	al	18673	358	b
Chilton County, Alabama	al	29793	267	a
Choctaw County, Alabama	al	9436	38	arr
Clarke County, Alabama	al	16870	99	s
Clay County, Alabama	al	9285	58	e