Introduction. SQL Overview.
  • SQL At a Glance
  • Handout Material (PDF file): Syntax Examples
Installation and Setup for Lab Exercises
  • Installation Overview, How to get the files
  • Install PostgreSQL for Windows
  • Install PostgreSQL for MAC
  • pgAdmin Overview
Creating Tables. Relationships Between Tables.
  • Creating Tables. Data Types.
  • Relationships between tables. Primary Key, Foreign Key.
Basic Queries - SELECT ... FROM ... WHERE...
  • Tables in the Database
  • Filtering columns. Expressions.
  • Filtering rows. Operators AND, OR.
  • Test Your Knowledge: Filtering Columns and Rows
  • Exercise 1: Filtering columns and rows.
  • Exercise 1: Solutions
  • More Logical Operators: IN, NOT IN, BETWEEN
  • Nested SELECTs / Subqueries.
  • Test Your Knowledge: Subqueries
  • Exercise 2: Subqueries
  • Exercise 2: Solutions
  • How to write Comments in SQL
Working with NULL values
  • NULL values
  • NULL values and "not equal" Comparison
  • Test Your Knowledge: NULL values
  • Exercise 3: Working with NULL values
  • Exercise 3: Solutions
  • NOT IN and subqueries with NULL values
Aggregations - GROUP BY, HAVING, DISTINCT
  • GROUP BY. Counting rows.
  • HAVING - Filtering rows after aggregation
  • Functions SUM, COUNT
  • Other aggregation functions
  • Counting DISTINCT (unique) values
  • ORDER BY - Sorting the query result
  • Test Your Knowledge: Aggregation
  • Exercise 4: Aggregation
  • Exercise 4: Solutions
Table Joins - INNER JOIN, OUTER JOINs, SELF JOIN, CROSS JOIN
  • INNER JOINs. How the table joins work.
  • Test Your Knowledge: INNER JOIN
  • Exercise 5: INNER JOIN
  • Exercise 5: Solutions
  • Joining Several Tables
  • Duplicate Rows. Missing Rows.
  • LEFT OUTER JOINs.
  • Syntax Variations
  • RIGHT OUTER JOIN
  • Exercise 6: OUTER JOIN
  • Exercise 6: Solutions
  • FULL OUTER JOIN
  • SELF JOIN - joining a table to itself
  • Exercise 7: SELF JOIN
  • Exercise 7: Solutions
  • CROSS JOIN - cartesian join
Set operators - UNION, EXCEPT and INTERSECT
  • UNION ALL and UNION
  • EXCEPT ALL and EXCEPT
  • INTERSECT ALL and INTERSECT
  • Test Your Knowledge: Set Operators:
  • Exercise 8: Set Operators
  • Exercise 8: Solutions
Functions and Operators
  • Overview of functions and operators
  • Useful numeric functions and operators
  • LIKE: finding patterns
  • Useful character functions and operators
  • Useful date/time functions and operators
  • Exercise 9: Functions and Operators
  • Exercise 9: Solutions
Changing data: INSERT, UPDATE, DELETE
  • INSERT - adding new rows
  • UPDATE - modifying existing rows
  • DELETE - deleting rows
  • Exercise 10: Changing data
  • Exercise 10: Solutions
  • Comparing Two Tables
  • Eliminating Discrepancies Between Two Tables
Bonus Topics: CASE
  • CASE: introduction
  • CASE: useful examples
Course Summary
  • Course Summary. Congratulations!