232 (Postgre)SQL basics [1 PT]

Posted About 1 year ago. Visible to the public.

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

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.

    INSERT 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

  A   B   C
-------------
|   | f |   |
| 1 |---|   |
|   | t |   |
|---|---|   |
|   |   |   |
|   | f | 4 |
| 2 |   |   |
|   |---|   |
|   | t |   |
|   |---|   |
|   | n |   |
|---|---|---|
Claus-Theodor Riegg
Last edit
12 months ago
Claus-Theodor Riegg
License
Source code in this card is licensed under the MIT License.
Posted by Claus-Theodor Riegg to DevOps Curriculum (2023-01-23 12:03)