PostgreSQL kursu

Kurs Haqqında

• Introduction to PostgreSQL
  • 1 What is PostgreSQL? History & Features
  • 2 PostgreSQL vs MySQL, Oracle, SQL Server
  • 3 Installation & Setup (Linux, Windows, Mac)
  • 4 `psql` command-line basics
• SQL Fundamentals
  • 1 Database objects (schemas, tables, views, sequences)
  • 2 Data types: numeric, text, boolean, date/time, JSON/JSONB
  • 3 DDL (CREATE, ALTER, DROP)
  • 4 DML (INSERT, UPDATE, DELETE, SELECT)
  • 5 Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL)
• Querying Data
  • 1 SELECT basics, WHERE, ORDER BY
  • 2 Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
  • 3 GROUP BY, HAVING
  • 4 Joins: INNER, LEFT, RIGHT, FULL
  • 5 Subqueries, Common Table Expressions (CTEs)
  • 6 Window functions (OVER, PARTITION BY, RANK, ROW_NUMBER)
• Advanced SQL
  • 1 Complex joins and set operations (UNION, INTERSECT, EXCEPT)
  • 2 Recursive CTEs
  • 3 CASE expressions
  • 4 Advanced filtering with EXISTS, IN, ANY, ALL
  • 5 Full-text search basics
  • 6 JSON/JSONB functions and operators
• Database Design & Modeling
  • 1 Normalization (1NF, 2NF, 3NF, BCNF)
  • 2 ER Diagrams
  • 3 Indexes: B-Tree, Hash, GIN, GiST, BRIN
  • 4 Partitioning & Sharding
  • 5 Constraints and Referential Integrity
• Performance & Optimization
  • 1 Query Execution Plans (`EXPLAIN`, `EXPLAIN ANALYZE`)
  • 2 VACUUM, ANALYZE, autovacuum
  • 3 Indexing strategiesIndexing strategies
  • 4 Materialized views
  • 5 Parallel queries
  • 6 Optimizing joins & subqueries
• Administration & Security
  • 1 PostgreSQL configuration files (`postgresql.conf`, `pg_hba.conf`)
  • 2 Role-based access control (users, groups, privileges)
  • 3 Authentication methods (peer, md5, scram-sha-256, SSL/TLS)
  • 4 Backup & Restore (`pg_dump`, `pg_restore`, `pg_basebackup`)
  • 5 Monitoring with `pg_stat_activity`, `pg_stat_statements`
• Advanced Features
  • 1 Stored procedures & functions (PL/pgSQL)
  • 2 Triggers & Events
  • 3 Views & Materialized Views
  • 4 Extensions (PostGIS, pgcrypto, uuid-ossp, hstore)
  • 5 Logical & Physical Replication
  • 6 Partitioning strategies (range, list, hash)
• High Availability & Scalability
  • 1 Streaming replication
  • 2 Hot Standby / Warm Standby
  • 3 Failover & Switchover
  • 4 Connection pooling (PgBouncer, Pgpool-II)
  • 5 Scaling reads & writes
• Real-World Applications
  • 1 ETL with PostgreSQL (COPY, Foreign Data Wrappers)
  • 2 Using PostgreSQL with Python/Java (JDBC, psycopg2, Hibernate)
  • 3 Data Warehousing concepts
  • 4 Audit logging and compliance
  • 5 JSON as a document store vs relational modeling
• Final Project Ideas
  • 1 Build a mini banking system (accounts, transactions, balances)
  • 2 Implement an e-commerce database (products, orders, customers)
  • 3 Design a real-time analytics dashboard using PostgreSQL + Power BI