SQL Table Joins and What's Next

Learning outcomes:

  • Write a SQL statement that joins multiple tables using foreign keys and primary keys
  • List resources for continuing to learn SQL
  • Describe query options that are not based on SQL
  • Describe what UI/UX is and why it's important

Would you like to download my PowerPoint to follow along?

  • Why do we have so many table join types
    • Tables will have different relationships
    • The results of the different joins will show combinations of data
    • Depending on the problem we're trying to solve with our query we might need different combos of the data
    • For example, maybe we want to see the winner of a reading campaign. If we have a table of library patrons who signed up, and a table of books checked out for the campaign, we can do an inner join on them to see who the winner of the campaign is by ordering the results by descending(desc)
                          SELECT "Patrons"."Name", COUNT(*) 
                          FROM "Patrons" 
                          INNER JOIN "CampaignCheckouts" 
                          ON "Patrons"."PatronID" = "CampaignCheckouts"."Patron" 
                          GROUP BY "Patrons"."Name" 
                          ORDER BY COUNT(*) DESC";
    • SQL Join types explained visually
    • A Visual Explanation of SQL Joins, includes venn diagrams, sample SQL queries and example tables
  • Inner Join
    • Tables have columns of information you need to know the names of the columns so that you can figure out what needs to match for the join
    • An Inner Join is going to be where the information on tables A and B match looking at a particular column
    • This is ONLY where they match
    • Example:If we wanted to know patrons of the library who borrowed books in the last 6 months we would look at where patron's table and the books checkouts tables as seen in the example query on slide 1 or above
  • Outer Join
    • An Outer Join is all information in both tables including where they match
    • This is less common then inner joins
    • Example: We can take a books table and a patrons table and look at both, not all books have been checked out, not all patrons have borrowed a book, but we want all the data anyway, anything not filled in will have a NULL value
  • Left and Right Joins
    • Left Outer Join is all results from Table A including where there are matches on Table B
    • Left Outer join where we won't have the matches from Table B is done by using the WHERE to specify that if Table A has matches in Table B mark it as null in our results
    • We can do the same for the right with Right Outer Join by switching the order of the tables in the query
  • Where to go next for more SQL
  • NoSQL
    • NoSQL stands for Not Only SQL
    • NoSQL databases are not relational
    • Examples of NoSQL databases are document, key-value, graph, or wide-column stores
    • Many people argue NoSQL is more flexible and scalable then SQL based Databases
    • NoSQL can handle unstructured and semi-structured data
    • Examples of implementations are MongoDB, Redis and Cassandra
    • An example of where you might want to use NoSQL instead of SQL might be if you wanted a database of images or videos (Large Items)
    • Explanation of NoSQL by Google
  • What is UI/UX
    • UI is User Interface
    • UX is User Experience
    • Both are topics that can be an entire course or job
    • UI/UX is how we interact with software, data or other things
    • UI is the look and feel of the interaction
    • UX is the overall experience
  • Why UI/UX is important
    • It's important to be able to communicate your information to others
    • If people don't want to use your software or interact with your data it doesn't matter how good your data is, or how important your analytics are
    • If others can't follow what you're doing with your data or product they won't know how to interact with it and will miss the things you want them to see, or not realize what you are trying to focus on
    • Reasonable or not people, tend to judge credibility by useability, if your data is hard to understand or present, people may not think it's credible
  • Ways we can communicate our data with others
    • We can generate reports of our data, for example PostgreSQL can generate a CSV for results of a query
    • Query results can also be sent to other programs if they are hooked into the database
    • Dashboards can be an example of a way to interact with a database
    • Business Intelligence (BI) tools can be used to create a data analysis report with charts, tables, filters, and parameters
    • You can also put your query results on a website, such as when you search stores for items, a lot of stores use databases to keep track of their inventory, and your search is creating a query

Suggested Activities and Discussion Topics:

Would you like to see some more classes? Click here