Database Engineering with PostgreSQL

Learn relational databases and SQL with PostgreSQL: installation, roles, querying, filtering, ER diagrams and joins, with hands-on tasks on the DVD Rental sample database.

Lessons

• Need of Database
  • Where can we store this details ?
  • How to resolve Repeating Group ?
• Database and Database Management System
  • What is a Database?
  • What is a DBMS?
  • Types of Database Models
• What is Relational Database Model ?
  • Library Analogy
  • What is Data Integrity ?
• Installing Postgresql
  • Installing PostgreSQL on Linux
  • Installing PostgreSQL on Windows
  • Installing PostgreSQL on Mac
  • Postgres in Docker
• What happens during the initial installation ?
  • 1. Installation Phase
  • 2. Initialization (initdb)
  • 3. Role Management
  • 4. Background Processes (When PostgreSQL Starts)
• Sample Databases
  • DVD Rental Database

Querying Data

• SELECT Query
  • 1. Basic SELECT Statement
  • 2. Using PostgreSQL SELECT statement to query data from multiple columns example
  • 3. Using PostgreSQL SELECT statement to query data from all columns of a table example
  • 4. Why we should not use SELECT * in a query ?
• Column Aliases
  • What are Column Aliases ?
  • 1. Assigning a column alias to a column example
  • 2. Assigning a column alias to an expression example
  • 3. Column aliases that contain spaces
• Order By
  • 1. Using PostgreSQL ORDER BY clause to sort rows by one column
  • 2. Using PostgreSQL ORDER BY clause to sort rows by one column in descending order
  • 3. Using PostgreSQL ORDER BY clause to sort rows by multiple columns
  • 4. Using PostgreSQL ORDER BY clause to sort rows by expressions
• SELECT DISTINCT
  • PostgreSQL SELECT DISTINCT examples
  • 1. PostgreSQL SELECT DISTINCT one column example
  • 2. SELECT DISTINCT on multiple columns
  • 3. Using the SELECT DISTINCT clause in practice
• DB-TASK-001

Filtering Data

• WHERE ?
  • Intro
  • Using WHERE clause with the equal (=) operator example
  • Using the WHERE clause with the AND operator example
  • Using the WHERE clause with the OR operator example
  • Using the WHERE clause with the IN operator example
  • Using the WHERE clause with the LIKE operator example
  • Using the WHERE clause with the BETWEEN operator example
  • Using the WHERE clause with the not equal operator (<>) example
• AND Operator
  • Basic PostgreSQL AND operator examples
  • Using the AND operator in the WHERE clause
• OR Operator
  • Postgres OR Operator Query Results
  • Using the OR operator in the WHERE clause
• LIMIT
  • Using PostgreSQL LIMIT to constrain the number of returned rows
  • Using the LIMIT clause with the OFFSET clause example
  • Using LIMIT OFFSET to get top/bottom N rows
• FETCH
  • Introduction to PostgreSQL FETCH clause
• IN
  • Introduction to PostgreSQL IN operator
  • Using the PostgreSQL IN operator with a list of numbers
  • Using the PostgreSQL IN operator with a list of strings
  • Using the PostgreSQL IN operator with a list of dates
  • PostgreSQL NOT IN operator
• BETWEEN
  • Introduction to the PostgreSQL BETWEEN operator
  • PostgreSQL BETWEEN operator examples
• LIKE
  • Introduction to PostgreSQL LIKE operator
  • PostgreSQL LIKE operator examples
  • 1) Basic LIKE operator examples
  • 2) Using the LIKE operator with table data
  • 3) Using the LIKE operator a pattern that contains both wildcards
  • 4) PostgreSQL NOT LIKE examples
  • PostgreSQL extensions of the LIKE operator
  • PostgreSQL LIKE operator with ESCAPE option
• SIMILAR TO
  • How SIMILAR TO Works
  • Basic Syntax and Examples
• IS NULL
  • IS NULL operator
  • 1) Basic IS NULL operator example
  • 2) Using the IS NOT NULL operator example
• ESCAPE
  • What is the ESCAPE Clause in SQL?
  • Common Characters You Need to Escape in SQL
• DB-TASK-002
  • Bonus Q/A

Entity Relationship Diagram

• What is an ER Diagram ?
  • Mapping Natural Language
• Entity
  • Entity Type
  • Entity Set
  • Entity Categories
  • What Should an Entity Type Be?
• Identifiers
  • 1. Super Key
  • 2. Candidate Key
  • 3. Primary Key
  • 4. Foreign Key
  • Why Not Just Use the Primary Key for Everything?
  • Declaring a Primary Key
  • Declaring Other Candidate Keys
• Relationship
  • Degree of a relationship set
• Attributes
  • 1. Simple Attribute
  • 2. Composite Attribute
  • 3. Derived Attribute
  • 4. Multivalued Attribute
  • 5. Key Attribute
  • Summary
• Cardinality
• Entity Relationship Diagram Tutorial
• DB-TASK-003

Joins

• Joins
  • Scenario 1:
  • Inner Join:
  • Left Join
  • Right Join
  • Full Outer Join
  • Full Outer Join Without Common Items
• Joins With Employee and Departments
  • INNER JOIN
  • LEFT JOIN (or LEFT OUTER JOIN)
  • RIGHT JOIN (or RIGHT OUTER JOIN)
  • FULL OUTER JOIN

• Joins on E-Commerce
  • Setup: Create Tables
  • 1. INNER JOIN
  • 2. LEFT JOIN
  • 3. FULL OUTER JOIN