More on Reporting the Roy Moore Story
Table Joining
Task: Join the SHEP Scores with the Pending Wait Times Data.
Question: How so we link these two tables?
Joining Tables
Part 1: Data Cleaning. Pending Wait Times Data
- Open spreadsheet. Pending Wait Times Data. Copy data to a new tab
- In the VISN column Find and Replace ) for a ,
- In the VISN column Find and Replace ( with nothing
- Insert four new columns adjacent to VISN
- Define VISN column, Data | text to columns
- Select comma delimited. Run Text to columns
- You should have three columns
- Clean the Data. Tools:
=Trim
Paste Special, Values
Transpose
Find and Replace
9. Shorten the column titles
10. Eliminate any empty columns
11. Save as PENDING in a .csv file
12. Review SHEP Data and repeat steps , save in a .csv file
Part 2: SQL
Import Pending into SQL
–remember to check the first row is the column headings. Numeric or Text for the fields.
Import SHEP into SQL
–what i just said. again.
The JOIN sequence
SELECT SHEP.facilityID,
SHEP.SHEPScore,
Pending.Facility1,
Pending.facilityID,
Pending.More30Days,
Pending.Days30Under
FROM SHEP
INNER JOIN Pending
ON SHEP.facilityID = Pending.FacilityID ;
Check the resulting table. Duplication? Why?
Solution?
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
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.