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";
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
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 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
There are places you can go to get case studies to trial your SQL knowledge
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