Class overview :

Class goals :

This is a course that covers SQL for querying and modifying databases, as well as database transaction processing, creation and dropping of tables and databases and tables. The aim is to provide a sound foundation of SQL, both in general, and in the context of PostgreSQL.

The course is a hands on course and the emphasis is on creating SQL scripts textually, rather than through a GUI.

Content of Foundations of SQL for PostgreSQL

Introduction to Database Concepts

• overview of relational database and the client – server model
• database concepts – tables, rows and columns, primary keys and foreign keys
• database data types

Introduction to SQL

• creating and running SQL scripts
• overview of SQL syntax
• guidelines for writing readable and maintainable SQL

Retrieving data – SELECT, WHERE and ORDER

• Overview of the SELECT statement and its clauses
• specifying source table(s) – using the FROM clause
• specifying which columns in a table ( or the entire table ) are to be retrieved
• result sets
• Renaming columns using aliases
• Sorting the query results using ORDER BY

Filter Results using the WHERE clause

• logical expressions using numeric and string comparison Operators
• basic numeric and string based filtering
• filtering based on results of calculations
• duplicate removal – using DISTINCT
• combining and extending logical expressions using AND , OR and NOT
• specifing ranges – BETWEEN and IN
• handling NULL values
• basic pattern matching – LIKE
• Using UNION, INTERSECT and EXCEPT to join SELECT Results
• Conditional expressions
  • The CASE statement
  • The COALESCE function
  • The NULLIF function

Queries involving multiple tables

• Overview of the concept of a Join
• Specifying specific columns in specific tables
• Inner Joins, Outer Joins , Cross joins, Natural joins
• Systematic techniques for devising and simplifying complex joins

Working with the Standard PostgreSQL Functions

• overview and syntax
• Math, String and Conversion Functions
• Date modification and calculation functions
• replacing NULLs with a specific value
• standard PostgreSQL functions in WHERE clauses

Grouping and Summarizing Results

• Overview of the Aggregate Functions (MAX(), SUM(), AVG(), COUNT() … )
• Correct use of Aggregate functions
• GROUP BY clause
• HAVING clause

Subqueries

• Overview of Subqueries and their use
• Strategies fo designing and constructing subqueries
• Filtering using subqueries
• Derived Columns based on subqueries

Views

• The VIEW concept
• VIEWS as a means of simplifying complex queries
• Creating and Dropping Views

Inserting, Updating and Deleting Data

• Inserting single and multiple rows
• specifying which columns values are to be inserted in – by column position vs. column name
• working with Auto-Incrementing Values
• handling NULL values
• Inserting Data from one Table into Another
• Updating and Deleting Data
• Modifying Data through a View

Inserts, Updates and Deletes in a Transaction Oriented Environment

• the concept and purpose of a transaction
• setting up a Transaction Environment
• Checking the Work to be done
• Undoing Changes with ROLLBACK
• Committing a Transaction

Creating and Modifying Tables

• The CREATE TABLE clause
• Specifying Primary and Foreign Keys
• DEFAULT values
• Constraining Input values
• Creating and using Temporary Tables
• Creating a New Table From an Existing Table
• Altering and Dropping Tables