SQL Fundamentals:

The following is a summary of my work during a few courses on Datacamp

• Functions for manipulating data in PostgreSQL:

  • Using Extensions: SIMILARITY, User-defined datatypes, User-defined functions,
  • Full text search: TSVECTOR, LIKE
  • String operations: SUBSTR, POSITION, LENGTH, CONCAT, REPLACE, Changing Case
  • Datetime operations: DATE_TRUNC, TIMESTAMP, CAST, INTERVAL
  • Filtering using: Contains @> or ANY
  • Accessing data in an Array using index [][1] and =
  • Using INFORMATION_SCHEMA.COLUMNS to understand table datatypes
  • notebook link

• Summary Stats and Window Functions in PostgreSQL:

  • Improving readability with STRING_AGG and COALESCE
  • Using ROLLUP and CUBE to explore summary statistics
  • Pivoting to reshape for readability
  • LAST_VALUE, FIRST_VALUE, LEAD, PAGING, RANK
  • The most commonly used subclauses: ORDER BY and PARTITION BY
  • notebook link

• Data Manipulation in SQL: CTE's, PARTITION, RANK, SLIDING WINDOWS, CASE, Subqueries

  • notebook link

• Joining Data in SQL: FULL JOIN, CROSS JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, WHERE Subqueries

  • notebook link