Lesson 5: Advanced SQL Commands
In this chapter we discuss the followings:
Timestamps and EXTRACT
Math Functions
String Functions
Sub-query (A query within a query)
Self-join
Four data types related to time:
TIME: contains only time
DATE: contains only date
TIMESTAMP: contains date and time
TIMESTAMPZ: Contains date, time, and time zone
Five functions related to those data types:
TIMEZONE
NOW
TIMEOFDAY
CURRENT_TIME
CURRENT_DATE
➔ Pg Admin
Extract information from a time-based data type:
EXTRACT()
AGE()
TO_CHAR()
EXTRACT() allows you to extract (obtain) a sub-component of a date value.
Sub-components of a date-value are: year, month, day, week, quarter
Syntax: EXTRACT(YEAR FROM date_column)
AGE() calculates and returns the current age given a timestamp.
Syntax: AGE(date_column)
Returns: 16 years 8 mons 21 days 01:05:27
It takes the current date that you are querying and calculates how old the timestamp is
TO_CHAR converts data types to text (you can use it on non-timestamp data types too)
Syntax: TO_CHAR(date_column, ‘mm-dd-yyyy’)
➔ Pg Admin
Mathematical Functions and Operators
➔ Pg Admin
String Functions and Operators
➔ Pg Admin
SubQuery and EXISTS function
A subquery is a query on the results of another query
Consider table_3:
SELECT * FROM table_3;
name sex height age
Soroosh male 74 37
Arash male 75 40
Kiana female 60 30
Sarah female 66 32
How can we get a list of people who are older than average?
SELECT AVG(age) FROM table_3;
--34.75
SELECT * FROM table_3
WHERE age > 34.75;
--Second way: we can use a subquery
SELECT * FROM table_3
WHERE age > (SELECT AVG(age) FROM table_3);
name sex height age
Soroosh male 74 37
Arash male 75 40
Since subquery is inside parenthesis, it is performed first.
A subquery can operate on a separate table:
SELECT * FROM table_3
WHERE name IN (SELECT name FROM table_4)
table_4
name sex weight age
Soroosh male 172 37
Reza male 160 34
Jennifer female 150 45
Sarah female 110 32
Result:
name sex height age
Soroosh male 74 37
Sarah female 66 32
➔ Pg Admin
The EXISTS operator
The EXISTS operator is used to test for existence of rows in a subquery
A subquery is passed in the EXISTS() function to check if any rows are returned with the subquery.
Syntax:
SELECT column_name FROM table_name
WHERE EXISTS
(SELECT column_name2 FROM table_name2 WHERE conditions)
➔ Pg Admin
SELF JOIN
A SELF JOIN is a query in which a table is joined to itself.
Self-joins are useful for comparing values in a column within the same table.
The self join can be interpreted as a join of two copies of the same table.
There is no special keyword for a self join. We use standard JOIN syntax with the same table on both
sides.
When using a self join you need to use an alias for the table, otherwise the table name would be
ambiguous.
Syntax:
SELECT table_nameA.col, table_nameB.col
FROM table_name AS table_nameA
JOIN table_name AS table_nameB
ON table_nameA.some_column = table_nameB.another_column
Example:
love_table
person_id name in_love_with
1 Soroosh 5
2 Alex 4
3 Kiana 1
4 Sarah 1
5 Jennifer 2
We want to get to the table:
name in_love
Soroosh Jennifer
Alex Sarah
Kiana Soroosh
Sarah Soroosh
Jennifer Alex
SELECT [Link], [Link] AS in_love
FROM love_table AS lover
JOIN love_table AS loved
ON lover. Person_id = loved. in_love_with
➔ Pg Admin