Learning Objectives

• HW1
  • I can write a SELECT query that utilizes basic WHERE filtering and ORDER BY.
  • I can properly use DISTINCT in a query to answer a question.
  • I understand how to use basic wildcards with LIKE and ILIKE to find text patterns.
• HW2
  • I can create a table in SQL with desired columns and types.
  • I can insert values into a table.
  • I can populate a table by reading from a CSV.
  • I can convert between data types when necessary.
  • I can output the results of a query to a CSV file.
• HW3
  • I can compute many different calculations between columns in a table.
  • I can compute many different aggregate calculations over an entire column.
  • I can store the results of queries in permanent or temporary tables.
• HW4
  • I can use a basic inner JOIN to combine information from several tables appropriately.
  • I understand when an alternative (LEFT, RIGHT, FULL OUTER, CROSS) JOIN is necessary and can use one to combine information from several tables in those cases.
  • I understand when a self-JOIN is necessary and can compute one in those instances.
• HW5
  • I can create various column constraints to limit data that can be placed in a column.
  • I can create various table constraints to limit data that can be placed in a table.
  • I understand the relationship constraints described by FOREIGN KEY constraints, and can create them to formally link table columns together.
  • I can create simple or compound PRIMARY KEY constraints, and understand what is meant and occurs when I create a primary key.
  • I can identify when and how previously defined constraints are being violated.
• HW6
  • I can use GROUP BY to compare values across categorical variables.
  • I understand when to use HAVING and can implement it accordingly to select a subset of groups.
  • I understand when a table is in 1st normal form, and can convert an unnormalized table into 1st normal form.
  • I understand when a table is in 2nd normal form, and can convert a 1NF table into a collection of 2NF tables.
  • I understand when a table is in 3rd normal form, and can convert a 2NF table into a collection of 3NF tables.
• HW7
  • I can use ALTER TABLE statements to make changes to the structure of an existing table.
  • I can use UPDATE statements to make changes to the specific content within a table that I want to change.
  • I can remove specific rows from a table using DELETE
  • I can get remove tables, columns, constraints or indexes using DROP.
  • I can use window functions to rank information within a table.
• HW8
  • I can use CTE’s to precompute information for later use within a query.
  • I can use subqueries in multiple parts of a query (SELECT, FROM, WHERE, etc.) to access specific information.
  • I can use predicate functions like IN, EXISTS, ANY and ALL alongside subqueries to filter rows from a table.
  • I can create pivot tables using Postgres’s CROSSTAB function.
  • I can work with and add time zones to timestamp columns.
  • I can extract portions of datetime-type columns according to what information I desire.
• HW9
  • I can use regular expressions to split and filter data.
  • I can use regular expressions to select subsets of text from a larger text field.
  • I can use TSVECTOR and TSQUERY objects to find where certain text phrases appear in large bodies of text.
  • I can extract snippets of text near where certain phrases occur using ts_headline.
• HW10
  • I can create geometry and geography columns to hold geospatial data.
  • I can assign geospatial information to appropriate coordinate systems.
  • I can use special geospatial functions to filter geospatial data.
  • I can use special geospatial functions to compute new geospatial data.
  • I can load a shapefile into a Postgres database.