Introduction to SQL PostgreSQL — A 2-Day Course

 Closed In-House Course

Course Overview

This is a course for those who will be writing SQL both to extract data from PostgreSQL databases for either reporting or data analysis purposes, and also to update data. 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.

Suitable For

• No prior knowledge of databases or SQL is assumed, but, the course is oriented towards the needs of IT professionals.

Scheduled and On-site Courses

Courses in this subject are scheduled on an 'ad-hoc' basis. We can arrange a course at our Carshalton centre or on customer site for any client wishing to send two or more delegates on the same course.

For courses at the Carshalton Centre the fee will be:

• £1400 + VAT for the first two delegates
• £660 + VAT for the third delegate
• £480 + VAT for the fourth delegate
• £160 + VAT for each additional delegate to a maximum of eight

On-site course fees are as above + expenses recharged at cost.

For a 1:1 course in Carshalton the fee is £1100 + VAT. For a 1:1 on-site course the fee is £1100 + VAT + expenses recharged at cost.

If there are any dates showing below this line, a course has been scheduled and places for individuals are available at £700. Otherwise, no courses are currently scheduled and the above fees apply.

Publicly scheduled dates, locations, and prices

Course Contents

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