SQLSSExam

—1
Write a query that allows you to inspect the schema of the naep table.


SELECT 
	COLUMN_NAME 
FROM 
	INFORMATION_SCHEMA.COLUMNS
WHERE
	TABLE_NAME = 'naep'
	
	

—2
Write a query that returns the first 50 records of the naep table.


SELECT 
	*
FROM 
	naep
LIMIT 50;



—3
Write a query that returns summary statistics for avg_math_4_score by state. 
Make sure to sort the results alphabetically by state name.

SELECT 
	state,
	min(avg_math_4_score) as minmath,
	max(avg_math_4_score) as maxmath,
	avg(avg_math_4_score) as avgmath,
	count(avg_math_4_score) as countmath
FROM
    naep
GROUP BY state
ORDER BY state ASC;



—4
Write a query that alters the previous query so that it returns only the summary 
statistics for avg_math_4_score by state with differences in max and min 
values that are greater than 30.

WITH naep as (SELECT
	state,
	min(avg_math_4_score) as minmath,
	max(avg_math_4_score) as maxmath,
	avg(avg_math_4_score) as avgmath,
	count(avg_math_4_score) as countmath,
	(max(avg_math_4_score) - min(avg_math_4_score)) as mathdiff
FROM naep
GROUP BY state
ORDER BY mathdiff) 
SELECT * FROM naep WHERE mathdiff > 30;



—5
Write a query that returns a field called bottom_10_states that lists the states
in the bottom 10 for avg_math_4_score in the year 2000.

SELECT 
	state AS bottom_10_states 
FROM 
	naep 
WHERE 
	year = '2000' 
ORDER BY avg_math_4_score 
LIMIT 10;



—6
Write a query that calculates the average avg_math_4_score rounded to the 
nearest 2 decimal places over all states in the year 2000.

SELECT 
	round(AVG(avg_math_4_score), 2)
FROM 
	naep
WHERE 
	year = '2000';



—7
Write a query that returns a field called below_average_states_y2000 that lists 
all states with an avg_math_4_score less than the average over all states in the year 2000.

WITH AVR_ST AS
    (SELECT avg(avg_math_4_score) as AVG_STATES
    FROM naep
    WHERE year = '2000'
    GROUP BY state)
SELECT 
	avg_math_4_score AS below_average_states_y2000, state
FROM 
	naep, AVR_ST
WHERE 
	avg_math_4_score < AVG_STATES;
	
	

—8
Write a query that returns a field called scores_missing_y2000 that lists any states 
with missing values in the avg_math_4_score column of the naep data table for the year 2000.

SELECT 
	state as  scores_missing_y2000
FROM 
	naep
WHERE 
	year = '2000' AND avg_math_4_score IS NULL;



—9
Write a query that returns for the year 2000 the state, avg_math_4_score, and total_expenditure from 
the naep table left outer joined with the finance table, using id as the key and ordered by 
total_expenditure greatest to least. Be sure to round avg_math_4_score to the nearest 2 decimal 
places, and then filter out NULL avg_math_4_scores in order to see any correlation more clearly.

SELECT 
	naep.state, ROUND(naep.avg_math_4_score, 2) as avg_math_4_score, finance.total_expenditure
FROM 
	naep
LEFT OUTER JOIN 
	finance ON naep.id = finance.id
WHERE 
	naep.year = '2000' AND avg_math_4_score IS NOT NULL
ORDER BY 
	finance.total_expenditure DESC;