SQL for PostgreSQL

Syllabus

Module 1: Introduction to PostgreSQL and SQL

• Overview of PostgreSQL features and capabilities
• Basics of SQL syntax and data types
• Introduction to PostgreSQL-specific SQL functions and operators

Module 2: Writing Basic Queries

• Using SELECT statements to query data
• Filtering data with WHERE clauses
• Sorting and limiting results with ORDER BY and LIMIT
• Combining multiple tables with JOIN operations

Module 3: Advanced SQL Queries

• Subqueries and nested SELECT statements
• Using aggregate functions (COUNT, AVG, SUM, etc.)
• GROUP BY and HAVING clauses for summarizing data
• Complex JOINs (INNER, LEFT, RIGHT, FULL OUTER)

Module 4: Data Modification and Management

• Inserting, updating, and deleting data
• Using transactions for data integrity
• Implementing constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.)

Module 5: Database Design and Schema Management

• Creating and altering tables and schemas
• Data normalization and designing relational databases
• Creating indexes for performance improvement

Module 6: Advanced PostgreSQL Features

• Working with stored procedures and functions
• Triggers and event-driven programming
• Using PostgreSQL’s JSON and array data types
• Window functions for advanced analytics

Module 7: Optimizing and Tuning Queries

• Identifying slow queries and performance bottlenecks
• Query optimization techniques
• Analyzing query execution plans with EXPLAIN
• Indexing strategies for faster query execution

Module 8: Managing Security and Permissions

• User management and role-based access control
• Granting and revoking permissions
• Database security best practices in PostgreSQL

Module 9: Backup and Recovery

• Performing database backups using pg_dump and pg_basebackup
• Restoring databases and handling disaster recovery
• Point-in-time recovery (PITR)

Module 10: Final Project and Use Case Scenarios

• Developing a full database application using SQL
• Capstone project: building a PostgreSQL database from scratch
• Solving real-world data manipulation and query optimization problems