2022-Basics of SQL using PostgreSQL

Course Curriculum

Chapter 1: Introduction

Lecture 1: Learning Objectives

Lecture 2: A Word about Udemy Reviews

Lecture 3: Introduction to Relational Databases and Management Systems

Lecture 4: Introduction to SQL

Lecture 5: Install PostgreSQL in Windows

Lecture 6: Install PostgreSQL in MAC

Lecture 7: PGAdmin and Sample Databases

Chapter 2: The Basics of SQL

Lecture 1: SELECT columns and ROWS FROM tables

Lecture 2: More SELECT using ALIASes

Lecture 3: ORDER rows in ASCENDING or DESCENDING order

Lecture 4: Exercises with SELECT and ORDER BY

Lecture 5: Selecting unique entries using DISTINCT

Lecture 6: Exercises with DISTINCT clause

Lecture 7: Filtering rows using the WHERE clause

Lecture 8: Exercises using the WHERE clause

Lecture 9: Limiting the number of rows using the LIMIT clause

Chapter 3: Joining multiple tables with JOINS

Lecture 1: INNER JOINS- Explanation

Lecture 2: INNER JOINS – Example

Lecture 3: INNER JOINS – A more complex example

Lecture 4: OUTER JOINS

Lecture 5: SELF JOINS

Chapter 4: Create, Read, Update and Delete Operations (CRUD)

Lecture 1: Basic Datatypes in SQL

Lecture 2: Basic CRUD on databases, tables and rows

Lecture 3: Manipulating columns in SQL

Lecture 4: Constraints on columns: PRIMARY KEY and SERIAL

Lecture 5: Constraints on columns: NOT NULL, DEFAULT, UNIQUE and CHECK

Lecture 6: Foreign Keys Constraints and their implications

Chapter 5: Built-in functions, Date, Time and Timestamps

Lecture 1: Useful built-in functions in Postgres

Lecture 2: The DATE Datatype

Lecture 3: The TIME Datatype

Lecture 4: TIMESTAMPs and TIMESTAMPs with TIMEZONE

Chapter 6: Combining rows into GROUPS

Lecture 1: Basic grouping of data using the GROUP BY and HAVING clauses

Lecture 2: Aggregate calculation deep dive

Lecture 3: Complex grouping using multiple columns and extracted data

Lecture 4: Combine many grouping operations into a single query

Lecture 5: Common SET operations to stack rows on top of each other.

Chapter 7: Mini-Project – I

Chapter 8: Complex SQL queries

Lecture 1: Use Sub-queries to construct complex queries.

Lecture 2: Use the CASE statement to take decisions

Lecture 3: Common Table Expressions (CTE)

Lecture 4: Virtual Tables using VIEWS

Lecture 5: Virtual Tables with MATERIALIZED VIEWS

Chapter 9: Transactions in SQL

Lecture 1: An introduction to SQL TRANSACTIONS

Lecture 2: Implicit and Explicit Transactions

Chapter 10: SQL Programming using PLPGSQL

Lecture 1: Introduction to PLPGSQL

Lecture 2: IF-ELSE Control structures in Functions

Chapter 11: Triggers and Trigger Functions

Lecture 1: Introduction to TRIGGERS and TRIGGER functions

Lecture 2: Updating the materialized view automatically

Lecture 3: Manipulating data before insertion into the table using trigger functions

Chapter 12: Mini-Project – II

Lecture 1: Problem Description: Build an audit table to track any changes in other tables

Lecture 2: Solution

Chapter 13: Database Optimizations

Lecture 1: Speed up searches of text and numbers using INDEX

Lecture 2: The Binary Search Algorithm

Lecture 3: The Price we Pay – UPDATES and INSERTS take longer

Lecture 4: Dead Tuples and the VACUUM command

Chapter 14: Database Administration and Access Control

Lecture 1: Database Administration Basics

Lecture 2: Postgres Schemas and Roles

Lecture 3: Postgres Schemas, Roles and Groups

Lecture 4: Controlling column and row access

Chapter 15: Using Python to interface with the PostgreSQL database

Lecture 1: A quick tour of basic Python concepts

Lecture 2: Python functions

Lecture 3: Using psycopg2 to connect to a database

Lecture 4: Using variables to execute multiple queries in Postgres

Chapter 16: A Primer on Flask Web Development

Lecture 1: A primer on Flask Web development

Lecture 2: Using Flask and Jinja2 templates to create dynamic websites

Chapter 17: Building a 3-Tier Web Application

Lecture 1: Introduction and Database setup

Lecture 2: Building the students search page

Lecture 3: Building the students scores page

Lecture 4: Building the add new students page

Lecture 5: Building the course statistics page