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 readable 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 a JOIN clause 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 clauses provide different behavior, e.g., LEFT JOIN retains all rows of the table on the left side of the clause.
  • 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.