232 (Postgre)SQL basics [1 PT]

Mit SQL spricht man mit relationalen Datenbanken.

Ziele

• Verstehe die folgenden SQL Befehle
  • SELECT
    • WHERE
    • ORDER
    • INNER JOIN, LEFT JOIN
    • GROUP BY
    • LIMIT
  • UPDATE
  • INSERT and INSERT ... SELECT
  • DELETE
  • EXPLAIN ANALYZE
• Du weißt was eine transaction ist.
• Du verstehst was ein index ist
  • Was ist der Zweck eines index?
  • Für was kann ich den Index benutzen?
  • Was ist ein "multiple column index"? Ist die Reihenfolge der Spalten relevant?
• Du weißt wie du dir die PostgreSQL Settings anzeigen lassen kannst.
• Wie kann man sich aktuelle Verbindungen/Prozesse usw. Anzeigen lassen?
• Was ist MVCC und warum braucht man das?
• Du weißt, was VACUUM ist und warum das regelmäßig laufen muss.
• Was ist die "visibility map"?
• Was ist ein "replication slot"?
• Unter welchen Umständen kann hot_standby_feedback zu Problemen mit VACUUM und Indizes führen?

Inhalte

• SQLZOO Tutorial Show archive.org snapshot : Übungsaufgaben in Tutorials
• Joins Visualizer Show archive.org snapshot : Explains the differences between various types of JOIN statements
• SQL Fiddle Show archive.org snapshot : Lets you play with databases from within your browser
• SQL in PostgreSQL Show archive.org snapshot : PostgreSQL supports many powerful extensions of the SQL standard
• PostgreSQL Indexes Show archive.org snapshot
• PostgreSQL Mathematical Operators Show archive.org snapshot
• Useful PostgreSQL commands
• PostgreSQL MVCC Show archive.org snapshot
• How does MVCC work? Show archive.org snapshot
• Write-Ahead Logging (WAL) Show archive.org snapshot
• Log-Shipping Standby Servers Show archive.org snapshot
• PostgreSQL VACUUM Show archive.org snapshot
• Investigating slow PostgreSQL Index Only scans

Aufgaben

1. Starte PostgreSQL mit einem Docker Container. Erstelle eine Datenbank mit dem Namen vacationplan

2. Erstelle 3 table mit den folgenden columns:

   • travel_destinations: id, country, city, cost_per_day
   • tourists: id, last_name, first_name
   • vacations: tourist_id, travel_destination_id, travel_duration_days

3. Befülle deine vacationplan Tabelle:

   • Touristen: Dieter Doedel, Karl Karlsen, Judith Jubilae, Maria Moment
   • Reiseziele: Paris(Frankreich), New York(USA), Singapur(Singapur), Dublin(Irland), (bestimme den Preis selbst)
   • Dieter und Judith reisen für 14 Tage nach New York
   • Karl reist für 30 Tage nach Singapur
   • Maria reist für 5 Tage nach Dublin und 7 Tage nach New York
   • Judith reist 14 Tage nach Singapur

4. Lass dir mit einem SELECT alle Urlaube anzeigen. Dabei sollen folgende columns sichtbar sein: Vorname, Nachname, Reiseziel (Ort und Land) und die Reisedauer.

5. Dieter verlängert seine New York Reise auf 21 Tage. Aktualisiere das entsprechend.

6. Schreibe ein SELECT das den Gesamtpreis für die Reisen ausgibt.

7. Erstelle einen neuen Touristen Dieter Dopf. Schreibe eine SELECT query die dir Touristen mit dem Vornamen Dieter ausgibt.

8. Befülle deine touristen Tabelle mit zufälligen Daten. Diese benötigst du damit die anderen Aufgaben gut funktionieren.

   CopyINSERT INTO tourists VALUES (generate_series(100, 1000), md5(random()::text), md5(random()::text));
   

9. Führe die vorherige SELECT query mit EXPLAIN und EXPLAIN ANALYZE aus. Anschließend erstellst du einen Index für diesen Table mit dem Vornamen. Schau dir EXPLAIN jetzt nochmal an.

10. Schau dir SELECT * FROM pg_file_settings; an. Was wird die hier angezeigt?

11. Öffne zwei SQL Konsolen. In einer führst du select pg_sleep(5 * 60); aus. In der anderen SELECT * FROM pg_stat_activity;. Was kannst du hier sehen?

12. Schau dir den output von SELECT * FROM pg_stat_database; an und besprich das mit deinem Mentor.

13. Besprich mit deinem Mentor Anhang A. Thema ist Multi Column Index.

14. Schau dir zusammen mit deinem Mentor deine Datenbank an.

Anhang A

Copy  A   B   C
-------------
|   | f |   |
| 1 |---|   |
|   | t |   |
|---|---|   |
|   |   |   |
|   | f | 4 |
| 2 |   |   |
|   |---|   |
|   | t |   |
|   |---|   |
|   | n |   |
|---|---|---|