Lektion 15 — Øvelser

Man lærer SQL bedst ved at skrive sætningerne selv og læse, hvad der kommer tilbage. Detaljerne, der ikke står på slidesne, står her.

Hvis du ikke nåede forberedelsen, så læs de fem lectures og lav SQLBolt lektion 1, 2 og 13. Opret Neon-kontoen og projektet lektion15 (afsnit 3 i forberedelsen), før du opretter patients.

Øvelse Hvornår Tid
1 Patienttabellen Efter quizzen om tabel og typer ~35 min
2 Ret, slet, og de fem Efter quizzen om WHERE ~30 min
3 Gruppens database Efter øvelse 2 ~30 min

Øvelse 1 — Patienttabellen

Mål: patients findes i dit Neon-projekt lektion15, med to rækker du kan hente via cpr.

Arbejd i Neons SQL-editor. Rør ikke server.js.

Det skal du lave

  1. Åbn console.neon.tech, vælg projektet lektion15, og åbn SQL Editor. Tjek øverst, at databasen er neondb.

  2. Opret tabellen:

CREATE TABLE patients (
  id SERIAL PRIMARY KEY,
  cpr VARCHAR(10) NOT NULL UNIQUE,
  navn VARCHAR(200) NOT NULL
);
  1. Læg to rækker i. id skal du ikke selv skrive.
INSERT INTO patients (cpr, navn)
VALUES ('2512489996', 'Nancy Ann Test Berggren');

INSERT INTO patients (cpr, navn)
VALUES ('0107729995', 'Max Test Berggren');
  1. Hent Max, og tjek at CPR starter med 0:
SELECT id, cpr, navn
FROM patients
WHERE cpr = '0107729995';
  1. Kør \d patients, og peg på primærnøglen og på UNIQUE.

Tjekliste

  • Editoren står på projektet lektion15
  • \d patients viser id som primary key og cpr som character varying
  • Max' CPR i resultatet er 0107729995, ikke 107729995
  • server.js er uændret

Øvelsen er i hus, når en sidekammerat kan køre SELECT i din editor og få Max tilbage.

Hvis du sidder fast

  • Første Run efter en pause tager et par sekunder. Databasen var gået i dvale. Vent, og prøv ikke igen med det samme.
  • Dobbelte anførselstegn er navne på kolonner. Tekst skal stå i enkelte anførselstegn.
  • relation "patients" does not exist betyder, at du står i et andet projekt eller en anden database. Tjek projektnavnet og neondb øverst i editoren.
  • Flere sætninger i editoren giver ét resultat pr. sætning, i hver sin fane under Run — fx 1: CREATE og 2: INSERT 5. Klik på fanen for den sætning, du vil se.

Hvis du har ekstra tid

  • Kør SELECT * FROM patients;, og sammenlign med den SELECT, der navngiver kolonnerne.
  • Prøv at indsætte Max én gang til, og læs fejlen fra UNIQUE.

Øvelse 2 — Ret, slet, og de fem

Mål: Du kan ændre én række og slette én række, og patients ender med de fem testpatienter fra lektion 7.

Det skal du lave

  1. Læg en række i, som ikke skal blive:
INSERT INTO patients (cpr, navn)
VALUES ('0000000000', 'Slet mig');
  1. Skriv WHERE som SELECT, før du retter Nancy:
SELECT id, cpr, navn
FROM patients
WHERE cpr = '2512489996';
  1. Ret navnet, og hent rækken igen:
UPDATE patients
SET navn = 'Nancy Test Berggren'
WHERE cpr = '2512489996';

SELECT navn FROM patients WHERE cpr = '2512489996';
  1. Sæt det rigtige navn tilbage med en ny UPDATE og samme WHERE.
  2. Slet fejlrækken, og vis, at den er væk:
DELETE FROM patients
WHERE cpr = '0000000000';

SELECT * FROM patients WHERE cpr = '0000000000';
  1. Læg de tre, der mangler, ind, så tabellen har alle fem. Navnene står på slidesne. Password skal ikke med.
INSERT INTO patients (cpr, navn) VALUES
  ('2911829996', 'Kirsten Test Berggren'),
  ('1509819996', 'Brita Test Berggren'),
  ('3103979995', 'Anders Test Jensen');
  1. SELECT cpr, navn FROM patients ORDER BY id; skal vise fem rækker.

Tjekliste

  • Nancy hedder igen Nancy Ann Test Berggren
  • SELECT på 0000000000 giver nul rækker
  • Fem rækker, og Max' CPR starter med 0
  • Ingen password-kolonne

Øvelsen er i hus, når de fem rækker kan læses op, og du kan forklare, hvorfor UPDATE og DELETE fik en WHERE.

Hvis du sidder fast

  • UPDATE uden WHERE har ændret alle navne. Kør SELECT * FROM patients;, og ret række for række med WHERE cpr = '...'.
  • DELETE uden WHERE har tømt tabellen. Kør øvelse 1's to INSERT igen, og derefter de tre her.
  • Fejlen duplicate key betyder, at CPR allerede findes. Spring den INSERT over.

Hvis du har ekstra tid

  • Ret Kirstens navn med WHERE id = ... i stedet for WHERE cpr = ..., og sæt det tilbage.
  • Kør \d patients igen, og forklar forskellen på PRIMARY KEY og UNIQUE.

Øvelse 3 — Gruppens database

Mål: Gruppen har én database til projektet, på ét gruppemedlems Neon-konto. Den har patients med de fem testpatienter og én tabel til gruppens eget projekt. Alle i gruppen har connection-stringen gemt et sikkert sted, klar til lektion 17.

Øvelse 1 og 2 var jeres egne databaser at eksperimentere i. Nu vælger I én. Brug syntetiske data. Ingen rigtige CPR, navne eller målinger.

Det skal I nå

  1. Vælg én i gruppen, der ejer databasen. Det er som med Vercel: ejeren har konsollen, resten får adgang via connection-stringen.
  2. Ejeren opretter et nyt projekt på sin konto — samme trin som i forberedelsen, men kald det efter jeres projekt. Region AWS Europe Central 1 (Frankfurt).
  3. Sæt jer ved ejerens skærm. Kør CREATE TABLE patients fra øvelse 1 og INSERT med alle fem testpatienter i det nye projekt.
  4. Vælg én slags række mere, projektet skal kunne gemme. Skriv kolonnerne, typerne og primærnøglen på papir, før I kører CREATE TABLE. Brug SERIAL PRIMARY KEY på id, medmindre I kan forklare en anden primærnøgle.
  5. INSERT én syntetisk række. SELECT den med WHERE.
  6. Skriv denne linje på papir, og kør den ikke:
patient_id INTEGER REFERENCES patients (id)

Den er fremmednøglen, hvis en anden tabel skal pege på en patient. I opretter ikke den anden tabel i dag.

  1. Ejeren trykker Connect øverst til venstre, under projektnavnet og production. Connect-knappen under projekt og branch i menuen til venstre
  2. Connect to your branch åbner. Lad det stå, som det er: databasen neondb, rollen neondb_owner og Connection pooling slået til. Connection-stringen starter med postgresql:// og indeholder brugernavn og password. Passwordet er skjult med stjerner, men Copy snippet kopierer det med. Tryk Copy snippet. Connect to your branch: connection-stringen med skjult password, Copy snippet og Reset password
  3. Ejeren deler den med gruppen i en privat kanal. Hver gemmer den et sikkert sted, fx i en password manager eller en lokal fil uden for repoet. I bruger den i lektion 17.
  4. Skriv tre bullets til projektloggen: hvad ligger i server.js, hvad ligger i gruppens Neon-projekt, og hvem der ejer det.

Connection-stringen er et password

Den, der har den, kan læse, rette og slette alt i databasen — uden at logge ind på Neon.

  • Commit den aldrig. Ikke i server.js, ikke i en README.
  • Læg den ikke på screenshots, i slides eller i en offentlig kanal. Tryk ikke Show password, mens nogen kigger med.
  • Er den lækket: ejeren trykker Reset password i Connect-dialogen og deler den nye. Den gamle virker ikke længere.

Tjekliste

  • \dt i ejerens editor viser både patients og gruppens tabel
  • patients har de fem testpatienter
  • SELECT med WHERE finder den syntetiske række
  • Fremmednøgle-linjen står på papiret og er ikke kørt
  • Alle i gruppen har connection-stringen gemt uden for repoet
  • server.js er stadig uændret

Øvelsen er i hus, når en anden end ejeren kan pege på primærnøglen i \d på ejerens skærm og sige, hvor vedkommende har gemt connection-stringen.

Hvis I sidder fast

  • CREATE TABLE fejler, hvis navnet findes. Kør \dt, og brug den tabel, I allerede har.
  • Tekst i VALUES skal i enkelte anførselstegn. Tal skal ikke.
  • Hold tabellen til de kolonner, den centrale handling i D2 skal bruge. Flere tabeller kan vente.
  • Ejeren er der ikke: en anden opretter et projekt på sin konto, og I bruger det i stedet. Det er derfor, alle har en konto.

Hvis I har ekstra tid

  • Læg en række mere i, og hent begge med SELECT uden WHERE.
  • Skriv, hvilken kolonne I ville filtrere på i brugergrænsefladen. Det er jeres WHERE.