Go Back
Report Abuse

Postgree SQL

0 (0 Reviews)
New
Postgree SQL
Postgree SQL

Description

1. Introduction to PostgreSQL

What is PostgreSQL
Relational Database Management Systems (RDBMS)
Features of PostgreSQL
PostgreSQL vs MySQL vs SQL Server vs Oracle
Use cases of PostgreSQL
Course project overview

2. Installing PostgreSQL

Installing PostgreSQL on Windows, Linux, and macOS
Installing pgAdmin
Using the PostgreSQL Command Line (psql)
Connecting to a PostgreSQL server
Creating your first database

3. Database Fundamentals

Databases
Schemas
Tables
Rows and Columns
Primary Keys
Foreign Keys
Relationships
Normalization concepts

4. SQL Fundamentals

SQL syntax
SQL statements
Comments
Identifiers
Data types
Creating databases
Dropping databases
Altering databases

5. Data Types

Numeric data types
Character data types
Boolean
Date and Time
UUID
JSON and JSONB
Arrays
ENUM types

6. Creating and Managing Tables

CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE
Temporary tables
Generated columns

7. CRUD Operations

INSERT
SELECT
UPDATE
DELETE
RETURNING clause
Bulk inserts

8. Querying Data

SELECT statement
Aliases
DISTINCT
LIMIT and OFFSET
ORDER BY
Filtering with WHERE
Logical operators

9. SQL Operators

Comparison operators
Logical operators
Arithmetic operators
BETWEEN
IN
LIKE
ILIKE
ANY and ALL

10. Aggregate Functions

COUNT()
SUM()
AVG()
MIN()
MAX()
GROUP BY
HAVING

11. Joins

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN
Multiple table joins

12. Advanced Queries

Subqueries
Correlated subqueries
Common Table Expressions (CTEs)
Recursive CTEs
UNION
UNION ALL
INTERSECT
EXCEPT

13. String Functions

UPPER()
LOWER()
CONCAT()
TRIM()
REPLACE()
SUBSTRING()
LENGTH()
POSITION()

14. Date and Time Functions

CURRENT_DATE
CURRENT_TIMESTAMP
NOW()
AGE()
DATE_TRUNC()
EXTRACT()
INTERVAL
Formatting dates

15. Mathematical Functions

ROUND()
CEIL()
FLOOR()
ABS()
POWER()
RANDOM()
MOD()

16. Constraints

PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
NOT NULL
DEFAULT
Deferrable constraints

17. Indexes

Creating indexes
Unique indexes
Composite indexes
Partial indexes
Expression indexes
GIN and GiST indexes
Index performance

18. Views

Creating views
Updating views
Materialized views
Refreshing materialized views
Security considerations

19. Sequences and Identity Columns

SERIAL
BIGSERIAL
IDENTITY columns
Managing sequences
Auto-increment strategies

20. Transactions

BEGIN
COMMIT
ROLLBACK
SAVEPOINT
Transaction isolation levels
ACID properties

21. Functions

Creating SQL functions
PL/pgSQL functions
Function parameters
Return values
Returning tables
Overloaded functions

22. Stored Procedures

Creating procedures
Calling procedures
IN, OUT, and INOUT parameters
Transaction control inside procedures

23. Triggers

Creating triggers
BEFORE triggers
AFTER triggers
INSTEAD OF triggers
Trigger functions
Audit logging examples

24. JSON and JSONB

Creating JSON data
JSON operators
JSON functions
JSONB indexing
Querying JSON documents
Working with APIs

25. Window Functions

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()
FIRST_VALUE()
LAST_VALUE()
Running totals

26. User Management and Security

Creating users
Roles
Privileges
GRANT
REVOKE
Role inheritance
Schema permissions

27. Backup and Restore

pg_dump
pg_restore
Exporting data
Importing data
Database migration
Disaster recovery basics

28. Performance Optimization

Query planning
EXPLAIN
EXPLAIN ANALYZE
VACUUM
ANALYZE
Partitioning
Query optimization techniques

29. PostgreSQL Administration

Configuration files
Monitoring database performance
Logs
Extensions
Updating PostgreSQL
Maintenance tasks

30. PostgreSQL Extensions

Installing extensions
PostGIS overview
pg_stat_statements
uuid-ossp
pgcrypto
Useful community extensions

31. PostgreSQL with Applications

Connecting using Python (psycopg)
Connecting using Node.js (pg)
Connecting using .NET (Npgsql)
Connecting using PHP (PDO)
Connecting using Java (JDBC)
ORM overview (Prisma, Sequelize, Entity Framework Core)

32. Best Practices

Database naming conventions
Schema design
Normalization vs Denormalization
Security best practices
Performance tuning
Backup strategies
Coding standards

33. Mini Projects

Student Management System Database
Library Management System
Hospital Database
Inventory Management Database
Sales Reporting Database
Employee Management System

34. Final Project

Students will design and implement a production-ready PostgreSQL database solution that includes:

Database design and normalization (up to 3NF)
Complex relationships and constraints
CRUD operations
Advanced SQL queries and joins
Views and materialized views
Stored procedures and functions (PL/pgSQL)
Triggers for auditing and automation
JSONB data handling
Window functions for reporting
Indexes and performance optimization
Role-based security and permissions
Backup, restore, and deployment best practices

Review

Login to Write Your Review

There are no reviews yet.

Related Listings

Scroll to Top