SQL — DDL og spørringer

CREATE TABLE og typer

Et relasjonsskjema er en kontrakt: hvilke kolonner finnes, hvilke typer har de, og hvilke rad-kombinasjoner er ulovlige. SQL DDL-en lar deg spesifisere alt dette i én setning. Eksemplet under er hentet fra universitetsdatabasen i lærebokens figur 3.1.

CREATE TABLE department (
  dept_name  VARCHAR(20),
  building   VARCHAR(15),
  budget     NUMERIC(12, 2),
  PRIMARY KEY (dept_name)
);

CREATE TABLE course (
  course_id  VARCHAR(7),
  title      VARCHAR(50),
  dept_name  VARCHAR(20),
  credits    NUMERIC(2, 0),
  PRIMARY KEY (course_id),
  FOREIGN KEY (dept_name) REFERENCES department
);

Datatyper du må kjenne

Integritetskrav på rad-nivå

• NOT NULL — feltet kan ikke være tomt
• PRIMARY KEY (a, b, …) — entydig identifikator, implisitt NOT NULL
• FOREIGN KEY (a) REFERENCES s — verdien må finnes i tabell s's primærnøkkel
• UNIQUE (a, b) — kombinasjonen må være entydig (men kan være NULL i noen DBMS)
• CHECK (uttrykk) — predikat på kolonne eller rad

Egendefinerte domener og typer

SQL gir to mekanismer for å gjenbruke datatype-definisjoner i hele skjemaet.

CREATE DOMAIN degree_level VARCHAR(10)
  CONSTRAINT dl_check
  CHECK (VALUE IN
    ('Bachelors', 'Masters', 'Doctorate'));

CREATE TABLE grad (
  id CHAR(5),
  level degree_level
);

CREATE TYPE Dollars AS
  NUMERIC(12,2) FINAL;

CREATE TABLE department (
  dept_name VARCHAR(20),
  building VARCHAR(15),
  budget Dollars
);

Forskjell: Begge gjenbruker en navngitt definisjon. DOMAIN kan ha constraints (f.eks. NOT NULL eller CHECK) — TYPE definerer en strengt typisert «egen datatype» som ikke kan blandes med andre uten eksplisitt konvertering. I praksis bruker mange DBMS-er disse litt forskjellig; PostgreSQL har f.eks. både CREATE DOMAIN og CREATE TYPE.

Endre og slette skjema

ALTER TABLE instructor
  ADD phone VARCHAR(15);

-- Alle eksisterende rader får
-- phone = NULL

SELECT — projeksjon, kilde, filter

Tre obligatoriske komponenter: SELECT (hvilke kolonner), FROM (fra hvilke relasjoner), WHERE (hvilke rader). Tenk på dem som π, × og σ i relasjonsalgebra-termer.

Eksempel: enkel én-tabell-spørring

SELECT name
FROM instructor
WHERE dept_name = 'Comp. Sci.'
  AND salary > 70000;

Aritmetikk og aliaser

SELECT kan inneholde uttrykk: + − * / og funksjonskall. Bruk AS for å gi et lesbart navn til resultat-kolonnen.

SELECT ID, name,
       salary AS dagens,
       salary * 1.1 AS etter_paaslag
FROM instructor;

Flere tabeller — kartesisk produkt + filter

Når FROM har flere relasjoner, bygger SQL først det kartesiske produktet og deretter filtrerer med WHERE. Dette er den mentale modellen — ikke nødvendigvis hva motoren faktisk gjør.

Filtrer rader — og unngå NULL-fellen

Operatorer du har

Tre-verdi-logikk: TRUE, FALSE, UNKNOWN

I SQL er NULL ikke en verdi du sammenligner mot — det er en markør for «ukjent eller fraværende». Sammenligning med NULL gir alltid UNKNOWN, og WHERE beholder kun rader der predikatet er TRUE.

Tekstmønstre med LIKE

Strenger skrives i enkelt-fnutter: 'Computer'. Et fnutt-tegn inni en streng dobles: 'It''s right'.

Mønster-jokere

• % matcher vilkårlig tekst (inkludert tom).
• _ matcher nøyaktig ett tegn.

Escape-tegn

Vil du matche selve % eller _? Bruk ESCAPE:

SELECT *
FROM products
WHERE code LIKE 'ab\%cd%' ESCAPE '\';
-- Matcher alle koder som starter med "ab%cd"

Sortering, dedup og top-N

Sortering

SELECT *
FROM instructor
ORDER BY salary DESC, name ASC;

Default er stigende (ASC). Du kan sortere på flere kolonner — første sorteringsnøkkel har høyest prioritet, så bruker SQL den neste til å bryte uavgjorte verdier.

DISTINCT — fjerne duplikater

SQL er multiset som default: spørringer beholder duplikater. Be eksplisitt om mengde-semantikk:

SELECT dept_name
FROM instructor;

SELECT DISTINCT dept_name
FROM instructor;

Top-N: LIMIT / FETCH FIRST

SELECT name, salary
FROM instructor
ORDER BY salary DESC
LIMIT 5;
-- I standard SQL: FETCH FIRST 5 ROWS ONLY

Fra rader til oppsummeringer

Aggregatfunksjoner reduserer en kolonne med mange rader til én verdi. Standarden definerer fem stykker — alle ignorerer NULL bortsett fra COUNT(*).

GROUP BY: ett aggregat per gruppe

GROUP BY deler tabellen i grupper basert på like verdier i én eller flere kolonner. Hver gruppe gir én rad i resultatet.

SELECT dept_name,
       COUNT(*) AS n_ansatte,
       AVG(salary) AS snittlonn
FROM instructor
GROUP BY dept_name;

HAVING — filter etter aggregering

WHERE filtrerer rader før gruppering. HAVING filtrerer grupper etter aggregering.

SELECT dept_name, COUNT(*) AS cnt
FROM instructor
WHERE dept_name <> 'BIO'
GROUP BY dept_name
HAVING COUNT(*) >= 3;

UNION · INTERSECT · EXCEPT

Kombiner to spørringer som om de var sett. Krav: begge spørringene må ha samme antall kolonner og kompatible typer.

Eksempel: emner som ble undervist i begge semestrene

(SELECT course_id FROM section
 WHERE semester = 'Fall'   AND year = 2017)
INTERSECT
(SELECT course_id FROM section
 WHERE semester = 'Spring' AND year = 2018);

Endre data

INSERT

-- Eksplisitt verdier
INSERT INTO instructor (ID, name, dept_name, salary)
VALUES ('77777', 'Lovelace', 'CS', 85000);

-- Sett inn fra spørring
INSERT INTO hi_paid (ID, name)
SELECT ID, name
FROM instructor
WHERE salary > 100000;

UPDATE

UPDATE instructor
SET salary = salary * 1.05
WHERE dept_name = 'CS';

DELETE

DELETE FROM instructor
WHERE dept_name = 'BIO'
  AND salary < 50000;

CASE-uttrykk i SET

UPDATE instructor
SET salary =
  CASE
    WHEN salary < 60000 THEN salary * 1.10
    ELSE                       salary * 1.03
  END;

CASE er det nærmeste SQL kommer en if/else, og fungerer både i SELECT, WHERE og SET.

Det du skal kunne

1. Skrive CREATE TABLE med riktige typer, primær- og fremmednøkler, og forklare hvorfor hver constraint er der.
2. Lese en SELECT-spørring og gjenkjenne den logiske evalueringsrekkefølgen (FROM → WHERE → GROUP → HAVING → SELECT → ORDER → LIMIT).
3. Forklare tre-verdi-logikken og hvorfor = NULL aldri matcher.
4. Skille mellom WHERE (filtrer rader) og HAVING (filtrer grupper).
5. Bruke UNION, INTERSECT og EXCEPT samt deres ALL-varianter.
6. Vite at SELECT-listen ved GROUP BY enten må være i GROUP-listen eller pakket i et aggregat.

Klar for joins og subqueries? Gå til 3B → Joins og subqueries.