[SQL 배우기] Section 6. Advanced SQL Commands

The Complete SQL Bootcamp for the Manipulation and Analysis of Data | Udemy

 

Types of time information:

• TIME: time
• DATE: date
• TIMESTAMP: time, date
• TIMESTAMPTZ: date, time, and timezone

 

The time information cannot be added although it can be removed

 

SHOW ALL: shows all the metadata related to the database

 

can show other details by using other functions such as:

• SHOW TIMEZONE
• SELECT NOW()
• SELECT TIMEOFDAY()
• SELECT CURRENT_TIME
• SELECT CURRENT_DATE

 

EXTRACT(): allows the user to obtain a sub-component of a date value

• EXTRACT(YEAR FROM date_col)

 

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

• AGE(date_col)

 

TO_CHAR(): function to convert data types to text

• TO_CHAR(date_col,’mm-dd-yyyy’)
• PostgreSQL: Documentation: 12: 9.8. Data Type Formatting Functions

 

Mathematical functions and operators:

• can be used to perform mathematical operations on columns
• PostgreSQL: Documentation: 9.5: Mathematical Functions and Operators

 

String functions and operators:

• can perform manipulations on strings
• PostgreSQL: Documentation: 9.1: String Functions and Operators

 

Subquery: can construct complex queries, by performing a query on the result of another query

• involves two SELECT statement
• involves parentheses that contain a SELECT statement
• EXISTS: used to test for the existence of rows in a subquery
  • SELECT column_name FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition)

 

Self-join: joining within the same table as if the tables are different

• SELECT tableA.col, tableB.col FROM table AS tableA JOIN table AS tableB ON tableA.some_col = tableB.other_col
• requires the use of an alias