My attempt (6) at learning a coding language (SQL) in my mid 30s

I have moved on to advanced SQL topics! Woo~ And these are the things i will be looking at:

1) Time stamps and Extract

2) String Functions

3) Sub-query

4) Self-Join

pgAdmin Community, CC BY-SA 3.0 <https://creativecommons.org/licenses/by-sa/3.0>, via Wikimedia Commons

1) Time stamps are more useful when creating our own tables and databases rather than when querying a database. 

The different data type for date and time:

Time - contains only time

Date - contains only date

Timestamp - contains date and time

Timestamptz - contains date, time and timezone

We may not always need the full level of timetamptz and for example we may only need to work with or compute the number of hours which means we do not need time zone information. It is important to take note that we can always remove data like time zone but we cannot add in the information later since we had never recorded them in the first place. 

Very useful for populating the table.

SHOW ALL - show all the parameters as you connect to the database.

SELECT NOW() - return the time information as of now, including the time zone.

SELECT TIMEOFDAY() - return the date in a string as day, month and date, time and timezone.

SELECT CURRENT_TIME - returns time with timezone

SELECT CURRENT_DATE - returns just the date 

EXTRACT() - allows you to "extract" or obtain a sub-component of a date value eg.

YEAR

MONTH

DAY

WEEK

QUARTER

dow - dayofweek

eg.  SELECT EXTRACT(YEAR FROM date_col) FROM table

EXTRACT clause can also be used as a filter statement by pairing with WHERE. 

AGE() -  calculates and returns the current age given a timestamp

eg. AGE(date_col) and it will return xx years x mon x days 00:00:00.

TO_CHAR() - convert data types to text 

eg. TO_CHAR(date_col, 'mm-dd-yyy')

There are many different format for how we want to convert to the text. 

eg. 'MONTH-YYYY' will give the month of the time stamp in capital letters and year. 

To compare dates, we can use mathematical operators.

eg. WHERE date_col > '2021-08-01'

This will return data after 1st Aug 2021. 

2) STRING 

LENGTH() - returns the number of characters in the string

string || string - string concatenation to combine two strings from different column data. 

However there is no space between the two strings if we just type this and there is no name for the new column. 

To add in the space, we can do this: string || ' ' || string AS new_column_name, essentially concatenate an empty space. 

LEFT(string, no. of char) - returns the no. of characters required in the string. This allowed us to extract only a specific number of characters in the string. 

LOWER() - formatting syntax that returns the lower case of the string that passes through it. 

3) Sub-query allows you to construct complex queries where we are performing queries on the result of another query. 

The syntax is straightforward and involves two SELECT statements. 

To get a list of students with grades higher than average grades. 

eg. SELECT student,grade FROM testscores

WHERE grade > (SELECT AVG(grade)) FROM testscore 

Subquery in the () will be run first then the results are used for the outer query. 

Subquery can be used on separate data from another table. 

EXISTS() - test for the existence of rows in a subquery and if true, it will return the first query. 

If subquery gives back a single value then we can use mathematical operators such as greater than or equal directly on them. However, if the subquery gives back a list of results, use IN to check the values against the list of results.

To be more efficient in coding complex queries. break the query into parts then place in brackets before writing the bigger query. 

4) Self Join - a query in which copies of rows from the same table is joined together and is useful to compare values. It uses standard JOIN syntax. Since we may refer to the same table more than once, it is important to use an alias for the table.

eg. SELECT tableA.col, tableB.col FROM table AS tableA

      JOIN table AS tableB ON 

      tableA.some_col = tableB.other_col

This query is referring to the same table. 

This post contains more of the usage of some of the syntax that we had already learnt and as a stand alone, it does not seems like much. Next post on tables creation!