SQL Abfragen und Datenbankdesign für PostgreSQL

SQL (Structured Query Language) ist die Standard-Datenbanksprache in allen gängigen relationalen Datenbanksystemen. Am Anfang des Kurses lernen die Teilnehmer das richtige Formulieren von SQL-Anweisungen, um Daten aus der Datenbank auslesen und manipulieren zu können. Danach beschäftigen wir uns mit fortgeschrittenen Abfragetechniken wie JOINs, Unterabfragen und Aggregatfunktionen, um komplexe Anforderungen an die Datenbankabfrage zu erfüllen. Abschließend erlernen die Teilnehmer die Konzepte des Datenbankdesigns. Sie lernen, wie man eine normalisierte Datenbankstruktur entwirft, wie man Beziehungen zwischen Tabellen modelliert und wie man die Leistung von Abfragen optimiert.

Alle Übungen werden auf einer PostgreSQL-Datenbank mit pgAdmin durchgeführt, obwohl das erlernte Wissen auch auf anderen gängigen Datenbanken wie MySQL, MariaDB, SQLite angewendet werden kann. Am Ende des Kurses werden die Teilnehmer in der Lage sein, ihre Kenntnisse in SQL und Datenbankdesign anwenden zu können, um datengetriebene Anwendungen zu entwickeln und zu verwalten.

Datenbank Grundlagen

• Datenbank(DB) Grundbegriffe
• Tabellen Beziehungen
• Data Definition Language – DDL
• Data Manipulation Language – DML
• Data Control Language - DCL
• Datentypen und Aliase (Ganzzahl, Gleitkommazahl, Datum und Uhrzeit, Alphanumerisch)
• Der NULL-Wert
• Tabellenschlüssel (Primary Key, Foreign Key)
• Referenzielle Integrität
• Sichten (Views)
• Zeichensätze und Sortierreihenfolgen
• Datenbank Objekte und Hierarchie gemäß ANSI SQL Standard

PostgresSQL Installation

• Installation von PostgreSQL auf Windows
• Einführung in pgAdmin4 auf Windows
• Datenbank Objekt Darstellung in pgAdmin4
• Grundlagen psql Shell

Grundlagen Tabellen Abfragen mit SELECT

• Ausgabe von speziellen Spalten
• Filtern mit der WHERE und WHERE NOT Klausel definieren
• Arbeiten mit Vergleichsoperatoren (=, <>, !=, >=, >, <=)
• Filtern mit Wildcards (LIKE, ILIKE) und regulären Ausdrücken (SIMILAR TO)
• Arbeiten mit geschlossenen und offenen Intervallen (IN, NOT, BETWEEN, IS [NOT] NULL, EXISTS)
• Verknüpfungsoperatoren AND [NOT] und OR [NOT], Klammern und Vorrangregeln
• Datensätze einschränken mit LIMIT, FETCH und OFFSET
• Behandeln von Duplikaten mit DISTINCT, DISTINCT ON
• Datensätze sortieren mit ORDER BY (ACS, DESC, NULLS, COLLATE)
• Syntax Unterschiede bei SELECT zwischen PostgreSQL, MySQL und MariaDB
• Tipps zum Arbeiten mit pgAdmin4

Konstanten und Aliase

• Erstellen von Kostanten
• Verknüpfen von Spaltenwerten mit Kostanten
• Arbeite mit Spalten und Tabellen Aliase

Datensätze gruppieren mit GROUP BY

• Erstellen von Gruppen (1 oder mehr Spalten) mit GROUP BY
• Anwenden von Aggregatfunktionen auf Gruppen (COUNT, SUM, MIN, MAX, AVG,...)
• Komplexere Gruppierung mit CASE, HAVING und DISTINCT
• Berücksichtigen von NULL Werten und Gruppen

Datensätze und Tabelleninhalte verwalten

• Neue Datensätze mit INSERT einfügen (einfache und komplexere Varianten)
• Bestehende Datensätze mit UPDATE verändern
• Datensätze mit DELETE-WHERE löschen
• Tabellenbereinigung mit DELETE und TRANCATE

Tabellen Verwaltung

• Neue Tabellen mit CREATE TABLE erstellen
• Bestehende Tabellen mit DROP [IF EXISTS] TABLE entfernen
• Spalten mit Auto Inkrement Werten (SMALLSERIAL, SERIAL, BIGSERIAL)
• Erstellen von verändern von SEQUENZ Objekten
• Erstellen und Verwalten von Tabellen CONSTRAINTS (NO NULL, UNIQUE, DEFAULT, CHECK)
• Primärschlüssel und Fremdschlüssel Definitionen
• Syntax Unterschiede zwischen PostgreSQL, MySQL und MariaDB

Tabellen Struktur mit ALTER TABLE anpassen

• Mögliche Varianten um die Tabellenstruktur auszulesen
• ADD, ALTER, CHANGE, MODIFY, DROP, RENAME
• Spalten hinzufügen/entfernen
• Datentypen ändern
• Tabelle umbenennen
• AUTO_INCREMENT
• SET DEFAULT
• Syntax Unterschiede zwischen PostgreSQL, MySQL und MariaDB

Arbeiten mit Tabellenwerten

• Rechnen mit Daten (Addition, Substruktion, Multiplikation und Division)
• Modulo (Restwert), Potenz und Quadratwurzel Berechnungen
• Skalarfunktionen und Textwerte (LOWER, UPPER, INITCAP)
• Löschen von Leerzeichen, Tabulatoren und Characters mit TRIM, LTRIM und RTRIM
• Extrahieren von Texteilen mit SUBSTRING (FROM-FOR, Regulären Ausdrücken)
• Zeichenbestimmungen mit CHAR_LENGTH und POSITION
• Spaltenwerte und Konstanten mit || verbinden
• Sortierungen mit der CASE Funktion
• Arbeiten mit Datum und ZEIT Funktionen (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP)
• Konvertieren von Datumsfelder in neue Formate (TO_CHAR)
• Syntax Unterschiede zwischen PostgreSQL, MySQL und MariaDB

Mengenoperationen

• Überblick Mengenvarianten (Vereinsmenge, Schnittmenge, Differenzmenge)
• Vereinigungsmenge (UNION)
• Schnittmenge (INTERSECT)
• Differenzmenge (EXCEPT)
• Arbeiten mit Duplikaten und NULL-Werten in Mengen
• Mengen in Kombination mit WHERE ORDER BY
• Syntax Unterschiede zwischen PostgreSQL, MySQL und MariaDB

Datenbanken modellieren

• Entwickeln eines Anforderungskataloges
• Entitäten und Schlüsselattribute identifizieren
• Beziehungen zwischen Identitäten festlegen
• Festlegen der Wertebereich der einzelnen Attribute
• Kardinalitäten von Beziehungen (1:1, 1:n, m:n) festlegen
• Definieren der notwendigen Schlüsselattribute
• Darstellen der Objekte in einem Entity Relationship Modell (ERM)
• Redundanzen durch Normalisierungen vermeiden
• Normalformen (1NF, 2NF, 3NF, ...)
• Praktische Umsetzung des entwickelten Datenbank Projektes
• ERM Darstellung in pgAdmin

Datenbank erstellen

• Erstellen von Datenbanken mit CREATE DATABASE
• Zeichensatz und Sortierreihenfolge
• Tablespace
• Template System von PostgreSQL

Tabellen verknüpfen

• PRIMARY KEY, FOREIGN KEY und REFERENCES
• Überblick JOIN Varianten
• Praktische Übungen (CROSS JOIN, INNER JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL (OUTER) JOIN, Self JOIN)
• Mehr als Tabellen mit JOINs verbinden
• JOINs über Tabellennamen und Aliase durchführen
• SELECT Anfragen kombinieren mit UNION ALL

Einführung in Unterabfragen (Subqueries)

• Nicht korrelierende Abfragen
• Korrelierende Abfragen

Virtuelle Tabellen (Sichten)

• View Erstellung mit CREATE VIEW und CREATE MATERIALIZED VIEW
• INSERT, UPDATE und DELETE auf Views anwenden
• Erstellen von PostgreSQL RULES für View Aktualisierungen
• Regeln für UPDATEABLE Views

Arbeiten mit Index

• Index Typen
• Erstellen und Löschen von Tabellen Indexes

Transaktionen

• AUTOCOMMIT-Modus in PostgreSQL
• Erstellen von Transaktionen mit BEGIN TRANSACTION
• Setzen von SAVEPOINTs
• Arbeiten mit der COMMIT und ROLLBACK Funktion

Dieses Training wird auf Windows mit PostgreSQL und pgAdmin4 durchgeführt. Abgesehen von allgemeinen PC Kenntnissen sind keine weiteren Kenntnisse Voraussetzung.