Introduction to SQL (Part Three)
This is the third introduction to learning how to program with SQL. Perfect for beginners or people looking to refresh their memory on SQL.
RIGHT JOIN
A RIGHT JOIN is essentially the same as the LEFT JOIN, but the tables are switched. This would be the same as switching the table order in LEFT OUTER JOIN.
Example syntax:
SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
This is the opposite of LEFT OUTER JOIN. Instead of returning rows that can be exclusively found in TableA, a RIGHT OUTER JOIN returns back rows that can be found either exclusively in TableB or in both TableB and TableA. It will not return back rows that are exclusively found in TableA.
Can also add WHERE like in LEFT OUTER JOIN:
SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableA.id IS null
It is up to you to decide how to organize your tables when it comes to picking LEFT or RIGHT join, since depending on the table order you specify in JOIN, you can perform duplicate JOINs with either method.
UNIONS Operator
The UNION operator is used to combine the result set of two or more SELECT statements. It basically serves to directly concatenate two results together.
Example syntax:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Example syntax 2:
SELECT * FROM Sales2021_Q1
UNION
SELECT * FROM Sales2021_Q2
Image example:
Timestamps and Extract
We’ll go over a few commands that report back time and date information. These are more useful when creating our own table rather than querying a database.
PostgreSQL can hold date and time information:
TIME - Contains only time
DATE - Contains only the date
TIMESTAMP - Contains date and time
TIMESTAMPTZ - Contains date, time, and timezone
* Depending on the situation you may or may not need the full level of TIMESTAMPTZ. You can always remove historical information, but you can’t add it.
Functions and operators related to these data types:
TIMEZONE
NOW
TIMEOFDAY
CURRENT_TIME
CURRENT_DATE
If you want to show your TimeZone:
SHOW TIMEZONE
If you want to show your TimeStamp with time zone:
SELECT NOW();
If you want the TimeStamp in string format use:
SELECT TIMEOFDAY()
If you want the current time:
SELECT CURRENT_TIME
If you want the current date:
SELECT CURRENT_DATE
Let’s explore extracting information from a time-based data type using:
EXTRACT()
AGE()
TO_CHAR()
EXTRACT()
Will allow you to 'extract or obtain a sub-component of a date value. If you only want a certain component then you can extract that certain component
YEAR
MONTH
DAY
WEEK
QUARTER
If I want the year then I would write:
EXTRACT(YEAR FROM date_col)
Image example:
*If you want quarter then change YEAR to QUARTER, so it’ll be
SELECT EXTRACT(QUARTER FROM payment_date) FROM payment
AGE()
Calculates and returns the current age given a timestamp.
AGE(date_col)
Image example:
TO_CHAR()
General function to convert data types to text. It is useful for timestamp formatting
TO_CHAR(date_col, ‘mm-dd-yyyy’)
Image example:
You can format it whether you want. In this case, I want the month then the date than the year, so the output formats it that way.
Here is a link for different kinds of formats:
https://www.postgresql.org/docs/current/functions-formatting.html
Mathematical Functions and Operators
This link will provide all the functions that support Postgresql:
https://www.postgresql.org/docs/9.5/functions-math.html
Image example for division:
I’ve divided rental_rate by replacement_cost. Then I rounded the numbers by 2 so only the last two digital show up and then I multiple by 100 to get %. If you don’t multiple by 100 then you’ll get decimals
Image example for multiplication:
Simple 4 * replacement_cost and then I changed the column name to deposit.
String Functions and Operations
Edit, combine, and alter text data columns
Link to string functions:
https://www.postgresql.org/docs/9.1/functions-string.html
Image example:
This will generate the first name and the last name into full name. I have used || ‘ ‘ || to add spacing between first_name and last_name.
You can also write it as upper(first_name) to get all capitalization on the first_name same as last name; upper(last_name)
Image example 2:
Used LOWER to lowercase all the words for both first_name and last_name. Then I used LEFT and 1 in order to get the first word from first_name. Last I added ‘@gmail.com’ to put Gmail on all the names.
SubQuery
A subquery allows you to construct complex queries, essentially performing a query on the results of another query.
Syntax involves two SELECT statements.
Example syntax:
SELECT student, grade FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores)
Image example:
We can also use the IN operator in conjunction with a subquery to check against multiple results returned.
A subquery can operate on a separate table:
SELECT student, grade FROM test_scores
WHERE student IN (SELECT student FROM honor_roll_table)
The EXISTS operator is used to test for the existence of rows in a subquery.
Example syntax:
SELECT column_name FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
Image example:
We get back the names of people who happen to have at least one payment whose amount is greater than 11. The subquery checks the payment table to find if that customer made at least one payment and the amount is greater than 11.
You can also add WHERE NOT EXISTS to get back people who haven’t made a payment greater than 11
Self-Join
A self-join is a query in which the table joined to itself. It is useful for comparing values in a column of rows within the same table.
The self join can be viewed as a join of two copies of the same table (the table isn’t actually copied, but SQL does it like they were).
* When using self join it is necessary to use an alias for the table
Example syntax:
SELECT tableA.col, tableB.col FROM table AS tableA
JOIN table AS tableB ON
tableA.some_col = tableB.other_col
Image example:
I select film one title, film two title, film one length FROM film AS f1. Then I joined film as f2 ON which the f1.film_id doesn’t equal to f2.film_id, but f1.length and f2.length match up.
This concludes part three of SQL.
The next part will cover how to create your own databases and tables
Part Four is up: Introduction to SQL: Creating Databases and Tables (Part Four)