PostgreSQL for Beginners

PostgreSQL for Beginners

This is a beginners level course designed to take a learner from absolute zero to being able to use PostgreSQL for eventful and most commonly performed database oriented tasks. Although this course is not designed to make a expert out of the user, it will instill enough knowledge to be able to tackle the most common problems pertaining to structured databases. Most importantly, this course has been designed to equip the learner with enough knowledge to learn more by himself / herself should the need arise.

Learning Outcome

The expected learning outcome from this course may be summarized as follows:

• Create a relational database from scratch
• Create, alter and delete tables from a database
• Insert, update and delete data from a table
• Retrieve data from a database using select queries
• Filter data using where clauses and logical operators
• Order and limit data retrieved from a table
• Use aggregate functions and group data to gain more meaningful insights from a database
• Relate tables together using database relationships
• Use SQL join queries to retrieve data from multiple tables in a single query

Prerequisites

Skills:

• Basic filing system and OS operation

System:

• PostgreSQL 11 or above installed
• pgAdmin installed
• Access to ports: 5432, 5433, 5434, 5435, 80, 8080, 8000
• Read / Write access to storage
• Webkit based browser (MS Edge or Google Chrome)

Outline

Duration: 1 day.

Daily routine: Each day shall constitute 8 hours inclusive of two 15 minute breaks and an hour’s lunch break.

Difficulty: Intensive

1. Basics
   1. RDBMS
   2. Db Structure
   3. Table structure
   4. Data types
   5. SQL
2. Operations
   1. CRUD
   2. Select
   3. Where
   4. Logical Operators
   5. Inclusion
   6. Negation
   7. Comparisons
   8. Range
   9. Ordering
   10. Aliases
   11. Concatenations
3. Aggregations
   1. Count
   2. Sum
   3. Min and Max
   4. Average
   5. Grouping
   6. Having
   7. Arithmetics
4. Relationships
   1. One to One
   2. One to Many
   3. Many to Many
5. Joins
   1. Inner Joins
   2. Left Joins
   3. Right Joins
   4. Full Joins
   5. Union
   6. Intersect
6. Subtopics
   1. Subqueries
   2. Functions