Introduction to SQL (Part Two)
This is the second introduction to learning how to program with SQL. Perfect for beginners or people looking to refresh their memory on SQL.
GROUP BY Function
GROUP BY will allow you to aggregate data and apply functions to understand how data is distributed per category.
The main idea of the aggregate function is to take multiple inputs and return them to one single output
Most common aggregate function:
AVG() - returns the average value
COUNT() - returns a number of values
MAX() - returns the maximum value
MIN() - returns the minimum value
SUM() - returns the sum of all values
Aggregate function calls happen only in the SELECT clause or HAVING clause
Example syntax:
SELECT category_col, AVG(data-col)
FROM table
GROUP BY category_col;
Second example syntax:
SELECT company, division, SUM(sales)
FROM finance_table
GROUP BY company, division;
The GROUP BY clause must appear right after a FROM or WHERE statement
In the SELECT statement, columns must either have an aggregate function (COUNT,SUM,MAX,MIN,AVG) or be in the GROUP BY call
WHERE statement should not refer to the aggregate result
Image example:
In the example above, we receive the customer_id and the max amount for that particular customer_id.
Look at how customer_id 184 equals 9.99. It shows that the most amount (MAX) that was spent for customer_id 184 is 9.99
Image example 2:
In this image above, I have used the MIN function and it shows the least amount spent
customer_id 184 now equals 0.99 because it is the lowest amount (MIN) that was spent for customer_id 184.
Image example 3:
In the image above, I have used SUM(amount) to get the total amount of money spent.
So in customer_id 184, we now get the total sum of all the amount that was spent which is 80.80. Before it was 9.99 (MAX), 0.99 (MIN), and now 80.80 (SUM).
HAVING Clause
The HAVING clause allows filtering after an aggregation has taken place
NOTE: We cannot use WHERE to filter based on aggregate results because the aggregate function happens after a WHERE is executed since GROUP BY comes after WHERE.
Example syntax:
SELECT company, SUM(sales) FROM finance_table
WHERE company != ‘Apple’
GROUP BY company
HAVING SUM(sales) > 1000
Image example:
In this image, I used HAVING SUM(amount) > 80 to return amounts greater than 80. So you can see in the image above that only SUM(amount) greater than 80 appears. You can compare this image to the image above this one to see the difference.
Image example 2:
If you want to filter customer_id then you would use WHERE customer_id before using GROUP BY. In this case, I want to filter customer_id greater than 200, so I used WHERE customer_id > 200. Use the WHERE function before using GROUP BY.
AS Clause
AS clause will allow you to create an ‘alias’ for a column or result
Example syntax: SELECT column AS new_name FROM table
Example syntax 2: SELECT SUM(amount) AS new_name FROM payment
The AS operator gets executed at the very end of a query, which means that we can’t use ALIAS inside the WHERE operator.
In short AS clause changes the name of the column.
Image example:
In the image above I have added AS amount in the query and the only thing it did was change the name to ‘amount’. Before the name was ‘SUM’ now I have changed the column name to ‘amount’.
The AS clause is useful for making it easier to read.
JOINS Function
JOINS allows us to combine information from multiple tables
There are different kinds of JOINS:
INNER JOINS
OUTER JOINS
FULL JOINS
UNIONS
The reason for different JOIN types is to decide how to deal with information present in one of the joined tables
We’ll start with INNER JOIN
An INNER JOIN will result with the set of records that match in both tables
Example syntax:
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.col_match = TableB.col_match
Example syntax 2:
SELECT * FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name
Example syntax 3:
SELECT reg_id, Logins.name,log_id FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name
Example syntax 3:
SELECT paymet_id,payment.customer_id,first_name FROM payment
INNER JOIN customer
ON payment.customer_id = customer.customer_id
Remember: Table order won’t matter in an INNER JOIN
For example, you can write:
SELECT * FROM Login
INNER JOIN Registration
ON Registrations.name = Logins.name;
Instead off:
SELECT * FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name;
It will produce the same result
Image example:
In the image above I have combined Table customer with Table payment. On the left, you can see the different kinds of Tables and I have chosen the payment table and the customer table. It joins them together to produce an output of both payment and customer.
Image example 2:
I can specify what I want using SELECT. So in this case I want the output to return email, payment_id, and first_name.
In short INNER JOIN is a way to combine two Tables together to form one big table.
OUTER JOINS
They will allow us to specify how to deal with values only present in one of the tables being joined.
Example syntax:
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
* The output of the code above is the same as using INNER JOIN
The difference is adding a WHERE on OUTER JOIN
Example syntax:
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableA.id IS null OR TableB.id IS null
Example 2:
SELECT * FROM Registrations
FULL OUTER JOIN Logins
ON Registrations.name = Logins.name
WHERE Registrations.reg_id IS null OR Logins.log_id IS null
Image example:
In the image above, I have combined both customer and payment using FULL OUTER JOIN. We use WHERE to filter it to find rows that are unique to the customer table. The reason why it returns nothing is that we don’t have any email formation not associated with some customers. Because every column has a matching record, we get nothing.
This link will give you a good understanding of FULL OUTER JOIN:
https://www.w3schools.com/sql/sql_join_full.asp
LEFT OUTER JOIN
A LEFT OUTER JOIN results in the set of records on the left table, if there aren’t any matches with the right table then the results are null.
Example syntax:
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
We’re grabbing information from TableA where there’s either exclusive to TableA or TableA has a match with something in TableB, if something is only found in TableB and not TableA then it is not going to be returned in the query.
Example syntax 2:
SELECT * FROM Registrations
LEFT OUTER JOIN Logins
ON Registrations.name = Logins.name
* Order matters for LEFT OUTER JOIN, so you can’t switch TableB to TableA like:
SELECT * FROM TableB
LEFT OUTER JOIN TableA
ON TableA.col_match = TableB.col_match
We can add WHERE with LEFT OUTER JOIN (if you want entries unique to TableA)
Example syntax:
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableB.id IS null
If TableB is null then the resulting rows would only be unique to TableA
Example syntax 2:
SELECT * FROM Registrations
LEFT OUTER JOIN Logins
ON Registrations.name = Logins.name
WHERE Logins.log_id IS null
In short LEFT OUTER JOIN grab tables unique to the left-hand table which is TableA
Image example:
In the image above, the left is the film joining on inventory. So because the film is on the left, we will only see rows that are either in just the film table or both film and inventory. Any films that are in inventory will not appear.
Image example 2:
In this image, it shows us the film_id and the title which doesn’t have it in inventory. So if someone wants to watch a movie on Apollo Teen (using this query) we can answer that we don’t have Apollo Teen in our inventory or any store. All the title that has a null on inventory_id and store_id means that we don’t have them in our inventory or in any store.
This concludes part two of SQL.
Part three is up: Introduction to SQL (Part Three)