Data Management with SQL for Ecologists: Reference

Key Points

Introducing Databases and SQL
  • SQL allows us to select and group subsets of data, do math and other calculations, and combine data.

  • A relational database is made up of tables which are related to each other by shared keys.

  • Different database management systems (DBMS) use slightly different vocabulary, but they are all based on the same ideas.

Accessing Data With Queries
  • It is useful to apply conventions when writing SQL queries to aid readability.

  • Use logical connectors such as AND or OR to create more complex queries.

  • Calculations using mathematical symbols can also be performed on SQL queries.

  • Adding comments in SQL helps keep complex queries understandable.

Aggregating and Grouping Data
  • Use the GROUP BY keyword to aggregate data.

  • Functions like MIN, MAX, AVG, SUM, COUNT, etc. operate on aggregated data.

  • Aliases can help shorten long queries. To write clear and readible queries, use the AS keyword when creating aliases.

  • Use the HAVING keyword to filter on aggregate properties.

  • Use a VIEW to access the result of a query as though it was a new table.

Combining Data With Joins
  • Use the JOIN command to combine data from two tables—the ON or USING keywords specify which columns link the tables.

  • Regular JOIN returns only matching rows. Other join commands provide different behavior, e.g., LEFT JOIN retains all rows of the table on the left side of the command.

  • COALESCE allows you to specify a value to use in place of NULL, which can help in joins

  • NULLIF can be used to replace certain values with NULL in results

  • Many other functions like COALESCE and NULLIF can operate on individual values.

Reference

See this cheat sheet for an list of the commands covered in this lesson.