Assignment #3 Interviews
Fix comments on your papers and post on WordPress with your name, click category VA_Interview
This material will display on a blog:
VA Interviews Blog
No Results Found
The page you requested could not be found. Try refining your search, or use the navigation above to locate the post.
EXERCISES FOR TUESDAY:
QUESTION #1: Count all black borrowers by county.
SELECT COUNT(Black), County_name
FROM HMDA_2015
GROUP BY County_name
Order by count (*) desc;
QUESTION #2: Count all white, asian and black borrowers by county, sort by white borrowers.
Question #3: What is the total number of white, Asian and black borrowers statewide?
Question #4: List the counties top order with more than 200 black borrowers
FROM HMDA_2015
In-Class Assignment for Thursday, Nov. 16
Fact Checking: Pending Wait Times
We find new data. It appears to update the NPR data
https://www.va.gov/health/access-audit.asp
Look at: Pending Appointments (As of 01 November 2017).
Is this the same stuff as the earlier NPR data?
First problem: It’s in a PDF file and needs to be converted to a spreadsheet
See Video:
Second problem: How does the NPR pending wait data relate to the new pending wait VA data? Does the NPR data reflect totals for the hospitals or for the VA systems? How do you double check that?
https://www.va.gov/HEALTH/docs/DR81_112017_Pending_and_EWL_Biweekly_Desired_Date_Division.pdf
Blog Post Item #1: Provide your answer in the in-class blog post for the in-class assignment on Thursday.
Third problem: This new pending wait data has everything in the country. I just want data for Fayetteville, Little Rock, Memphis and Shreveport medical systems and their clinics. Looking at the record layout of the NPR data, tell me how you efficiently sorted the VA data to extract just these four hospital systems.
Blog Post Item #2: Post your sorted spreadsheet with just pending wait data for the Fayetteville, Little Rock, Memphis and Shreveport medical systems and their clinics.
Fourth problem: Finally! We have national data to compare to Fayetteville, Memphis, Little Rock and Shreveport. See the final two pages of the new data: https://www.va.gov/HEALTH/docs/DR81_112017_Pending_and_EWL_Biweekly_Desired_Date_Division.pdf
Blog Post Item #3: Extract the national data from the new VA data set into a separate spreadsheet and post.
To recap: You will post these three items into a SINGLE BLOG POST for Thursday’s in-class assignment. I will check the work Friday morning.
Quick Reference Sheet for SQL Commands
Major Commands:
SELECT
FROM
WHERE
GROUP BY
ORDER BY
—
SELECT: You tell the database what columns you want.
If you want everything, SELECT *
Example: SELECT * FROM Customers;
–Retrieves EVERYTHING (*) from the database called Customers
SELECT CustomerName FROM Customers;
–Retrieves CustmerName from the database called Customers
FROM: The basic command that just identifies which table you’re pulling data from.
Example: SELECT * FROM Customers;
–Retrieves EVERYTHING (*) from the database called Customers
WHERE: A filter.
Allows you to grab only certain records do numerical and text string, or a horizontal slice of your data; you set criteria that has to be true in order for rows to be returned
SELECT * FROM Customers WHERE ContactName = “Maria Anders”;
–retrieves all records with Maria Anders as contact name
SELECT * FROM Customers WHERE ContactName LIKE “Maria%”;
–retrieves all records with Maria in ContactName. Note the addition of Like and the wildcard %
SELECT * FROM Customers WHERE ContactName LIKE “M%”;
–retrieves all records with M leads ContactName.
SELECT * FROM Customers WHERE ContactName LIKE “%M”;
–retrieves all records with M ends ContactName.
Here’s a list of operators:
Exercise #1: GROUP BY
– This groups rows together by a certain field or fields so that you can do math on those groups, such as counting the number of records in each group or summing up the numbers in a field.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
–Here we are counting the number of countries in the database
Remember this command is paired with putting an aggregate function in your SELECT statement, such as COUNT(*) or SUM(noncitizen)
Exercise #2: ORDER BY
– This sorts your results by a field or fields, ascending (default) or descending (DESC)
SELECT * FROM Customers
ORDER BY Country;
–This sorts all of your data by country
SELECT Country, City FROM Customers
ORDER BY Country;
–This displays just the cities and countries, ordered by country
Exercise #3: DESC
–List of countries in descending order
SELECT * FROM Customers
ORDER BY Country DESC;
–List of countries in descending order
SELECT * FROM Customers
ORDER BY Country ASC;
–List of countries in ascending order
Exercise #4: Counting a List
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
Order by count (*) desc;
–This counts the countries and displays the list in descending order
Exercise #5: Having clause
You can’t use an aggregate directly in a WHERE clause; that’s what HAVING clauses are for.
https://www.w3schools.com/sql/sql_having.asp
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
–This filters all Countries with more than 5 records