Databases (DBs) and SQL with PostgreSQL

  1. DB Theory

    1. Why use DBs?
    2. DB Organization: Data Abstraction
    3. Relational DB Concepts and Vocabulary
    4. Normalization and Normal Forms
      Exercise I
    5. Entity Relationship (ER) Model
    6. ER Model Reference
    7. Mapping Cardinality
    8. DB Construction and Design
      Exercise II
    9. SQL as Relational Language
    10. Relational Algebra vs. Relational Calculus

  2. Getting Started with PostgreSQL

    1. Documentation
    2. DB Server Client Architecture
    3. PostgreSQL Installation
    4. The PostgreSQL Client: psql
      Exercise I
    5. Creating DBs
    6. Destroying DBs: DROP
      Exercise II
    7. Accessing DBs: Users and Groups
    8. Privileges (Permissions):
      GRANT and REVOKE
      Exercise III

  3. The SQL Language

    1. SQL Basics
    2. Creating a New Table: CREATE
    3. Modifying a Table: ALTER TABLE
    4. Data Types
      Exercise I
    5. INSERT Data
    6. Querying a Table: Basic SELECT
      Exercise II + III
    7. Data Deletions: DELETE
    8. Modifying Data: UPDATE
      Exercise IV
    9. SELECT with WHERE and Operators:
      Boolean, BETWEEN, IN, LIKE, IS NULL
      Exercise V
    10. PostgreSQL Parameters:
      SET, SHOW, and RESET
    11. Aliases: AS
    12. Select Distinct Rows
    13. Select ORDER BY
    14. LIMIT and OFFSET
      Exercise VI
    15. Build-In Functions and Operators
    16. Aggregate Functions
    17. GROUP BY and HAVING
      Exercise VII
      ----------- End of SQL Part 1 ----------

    18. Constraints: NOT NULL, UNIQUE, CHECK
    19. Keys
      Exercise VIII
    20. Joins Between Tables
      Exercise IX
    21. Combining Queries:
      Union, INTERSECT, EXCEPT
    22. Sub-Queries, Nested Queries
    23. Inserting and Creating Tables
      Using INSERT/SELECT INTO
      Exercise X
    24. Import and Export Data: COPY
    25. Backup and Restore
      Exercise XI
    26. Large Objects (BLOBs)
    27. Regular Expressions
    28. Views
    29. Inheritance
    30. Indexes
    31. Rules
    32. LISTEN and NOTIFY
      Exercise XII
    33. Sequences
    34. Cursors
      Exercise XIII
    35. CASE Clause
    36. Stored Procedures (Server-Side Functions)
    37. Custom Aggregate Functions
    38. Trigger
      Exercise XIV Solution
    39. Script Files
      Exercise XV
    40. Transactions, Isolation and Locks
      Exercise XVI

  4. Advanced Topics

    1. Tools: pgaccess
    2. Performance
    3. Use Explain to Debug Query Performance
    4. Programming Interfaces to SQL:
      C++ (LIBPQ++), Perl, PHP

Literature

Online Tutorials and Manuals