SQL Querying for PostgreSQL
Course
Online
Description
-
Type
Course
-
Methodology
Online
This is a course for those who will be writing SQL queries to extract data from PostgreSQL databases for either reporting or data analysis purposes. It does not, therefore, deal with SQL statements that modify either the database, or the data in a database. The emphasis is on the design of simple and complex queries using SQL (and, also, including PostgreSQL specific features).
Reviews
Subjects
- SQL
- IT
Course programme
- overview of relational database and the client - server model
- database concepts - tables, rows and columns, primary keys and foreign keys
- database data types
- creating and running SQL scripts
- overview of SQL syntax
- guidelines for writing readable and maintainable SQL
- 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
- 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
- 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
- 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
- Overview of the Aggregate Functions (MAX(), SUM(), AVG(), COUNT() ... )
- Correct use of Aggregate functions
- GROUP BY clause
- HAVING clause
- Overview of Subqueries and their use
- Strategies fo designing and constructing subqueries
- Filtering using subqueries
- Derived Columns based on subqueries
- The VIEW concept
- VIEWS as a means of simplifying complex queries
- Creating and Dropping Views
SQL Querying for PostgreSQL