Introduction. SQL Overview.
- SQL At a Glance
- Handout Material (PDF file): Syntax Examples
Installation and Setup for Lab Exercises
- Section Overview
- Installation Overview, How to get the files
- Install PostgreSQL for Windows
- Install PostgreSQL for MAC
- Solving installation problems
- pgAdmin Overview
Creating Tables. Relationships Between Tables.
- Creating Tables. Data Types.
- Quick note about the exercise tables
- Relationships between tables. Primary Key, Foreign Key.
Basic Queries - SELECT ... FROM ... WHERE...
- 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 in text
- 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!
