DFU 03, Advanced Queries

Slideshow

# DFU 03
## Advanced Queries

----

### Notice

- Eksemplerne i denne præsentation er lavet med PostgreSQL da I bad om PostgreSQL
    - der vil være forskelle ift. MSSQL og MySQL
    - forskellene er dog små

---

## Subqueries

----

### Hvad er subqueries?

- En forespørgsel **inde i en anden** forespørgsel**

- Returnerer data, som bruges af den "ydre" forespørgsel

- Kan stå i `SELECT`, `FROM`, `WHERE`, `HAVING` m.fl.

----

### Hvorfor bruge subqueries?

- Bryd komplekse problemer op i mindre bidder

- Genbrug logik uden views

- Dynmiske filterværdier

----

### Ulemper ved subqueries

- Kan være sværere at læse og forstå

- Potentielt dårligere ydeevne end joins

----

### Typer af subqueries

- **Scalar** (enkelt værdi): Returnerer et enkelt værdi (én række, én kolonne)

- **Row**: Returnerer en enkelt række (én række, flere kolonner)

- **Table**: Returnerer flere rækker og kolonner (flere rækker, flere kolonner)

- **Correlated**: Refererer til kolonner i den ydre forespørgsel

----

### Eksempel: Scalar Subuqery

```sql [2]
SELECT name,
    (SELECT COUNT(*) FROM courses WHERE programId = programs.id) AS courseCount
FROM programs;
```

- Indlejret query giver én værdi

- Bruges i SELECT til at tilføje beregnet kolonne

----

### Eksempel Row Subuqery

```sql []
SELECT s.*
FROM "Students" s
WHERE (s.id, s."programId") =
      (SELECT e."studentId", e."programId"
       FROM "Exams" e
       ORDER BY e.grade DESC
       LIMIT 1);
```
- Match på **flere kolonner** med row value comparison

- Finder den **student** hvis `id`, `programId` matcher `studentId`, `programId` fra subquery

----

### Eksempel Table Subuqery

```sql []
SELECT name
FROM programs
WHERE id IN (
    SELECT programId FROM courses 
    WHERE level = 'AP'
);
```

- Subquery returnerer en tabel med id'er

- Bruges i WHERE til at filtrere programmer med AP-kurser

----

### Eksempel Correlated Subuqery

```sql []
SELECT p.name,
       (SELECT COUNT(*) FROM courses c WHERE c."programId" = p.id) AS courseCount
FROM programs p;
```

- Subquery refererer til ydre query (`p.id`)

- Beregner antal kurser for hvert program

----

### Subqueries vs. Joins

- Subqueries kan være mere intuitive for visse problemer

- Joins kan være mere effektive for store datasæt

- Valg afhænger af kontekst og præference

----

## Opgaver

[https://ucl.kjc.dk/dfu/dfu-03-exercises-subqueries.html](https://ucl.kjc.dk/dfu/dfu-03-exercises-subqueries.html)

---

## Views

----

### Hvad er et view?

- Logiske "virtuelle tabeller"

- Gemmer en SELECT-forespørgsel under et navn

- Bruges som en tabel i queries

----

### Views behavior

- Opdateres dynamisk med underliggende data

- Oprettelse af et view er DDL (Data Definition Language)

- Selecte fra et view er DQL (Data Query Language)

- Performance: view er bare en "gemt query"

----

### Hvorfor bruge views?

- Gør komplekse queries simple

- Genbrug af logik på tværs af systemet

- Sikkerd: skjul følsomme kolonner

- Konsistens: ét sted at ændre logikken

----

### Ulemper ved Views

- Kan påvirke ydeevnen, især med komplekse forespørgsler

- Kan gøre debugging sværere

----

### Typer af Views

- **Simple Views**: Basere på en enkelt tabel, uden aggregeringer eller joins

- **Complex Views**: Involverer flere tabeller, aggregeringer, joins eller subqueries

----

### Oprettelse af Views

```sql []
CREATE OR REPLACE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;
```

- `CREATE OR REPLACE VIEW`: Opretter eller opdaterer et view

- `OR REPLACE`: Valgfrit, erstatter eksisterende view

----

### Brug af Views

```sql []
SELECT * FROM view_name WHERE condition;
```

- Bruges som en almindelig tabel i SQL-forespørgsler

- Bemærk at conditionals er tilladt både i view-definitionen og i forespørgslen

----

### Sletning af Views

```sql []
DROP VIEW IF EXISTS view_name;
```

- `IF EXISTS`: Valgfrit, undgår fejl hvis viewet ikke findes

----

### Views vs. ORM e.g. Entity Framework

- Views kan bruges sammen med ORM-værktøjer som Entity Framework

- ORM kan mappe views til entiteter ligesom tabeller

- Fordel: Genbrug af komplekse forespørgsler uden at skulle skrive SQL i applikationskoden

- Ulempe: Mindre fleksibilitet sammenlignet med direkte SQL-forespørgsler

----

## Opgaver

[https://ucl.kjc.dk/dfu/dfu-03-exercises-views.html](https://ucl.kjc.dk/dfu/dfu-03-exercises-views.html)

---

## Functions

----

### Indbyggede funktioner

- SQL har mange indbyggede funktioner

- F.eks. `COUNT()`, `SUM()`, `AVG()`, `UPPER()`, `LOWER()`, `NOW()`

- Bruges i `SELECT`, `WHERE`, `HAVING`, `ORDER BY` m.fl.

----

### Hvad er en custom functions?

- Konceptuelt det samme som i programmering

- En `function` er en gemt "procedure", der returnerer en værdi

- Kan tage inputparametre

- Kan bruges i SQL som indbyggede funktioner

----

### Hvorfor bruge custom functions?

- Genbrug af kompleks logik

- Forbedret læsbarhed af SQL-forespørgsler

- Centraliseret vedligeholdelse af logik

----

### Udfordringer ved custom functions

- Kan påvirke ydeevnen, især hvis de kaldes mange gange

- Man kan have en tendens til at flytte logik fra applikationen til databasen

- Ikke gennemsigtigt hvilke functions der findes

----

### Eksempel: Oprettelse af en simpel function

```sql []
CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    RETURNS NULL ON NULL INPUT;
```

----

### Brug af en function

```sql []
SELECT add(2, 3) AS sum;
```

----

### Eksempler på use cases

- Beregninger, f.eks. momsberegning

- Datomanipulation, f.eks. formatering af strenge

- Datavalidering, f.eks. tjekke gyldighed af data

Det er selvfølgelig et valg om man ønsker denne logik i databasen eller i applikationen

----

## Opgaver

[https://ucl.kjc.dk/dfu/dfu-03-exercises-functions.html](/dfu/dfu-03-exercises-functions.html)

---

## Stored procedures

----

### Hvad er en stored procedure?

- En gemt procedure i databasen

- Kan udføre komplekse operationer

- Kan returnere resultater eller status

----

### Hvorfor bruge stored procedures?

- Centraliseret logik i databasen

- Forbedret ydeevne ved at reducere netværkstrafik

- Sikkerhed: begræns adgang til data via procedurer

----

### Udfordringer ved stored procedures

- Kan gøre debugging sværere, da logikken er i databasen

- Kan føre til "vendor lock-in" pga. database-specifik syntaks

- Vedligeholdelse kan blive kompleks med mange procedurer

----

### Forskellen mellem `functions` og `stored procedures`

- **Functions** returnerer en værdi og kan bruges i SQL-forespørgsler

- **Stored Procedures** kan udføre handlinger og returnere status

- **Stored Procedures** et "fully self-contained"

----

### Oprettelse af en simpel stored procedure

```sql []
CREATE OR REPLACE PROCEDURE log_student_enrollment(student_id INT, course_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO enrollments ("studentId", "courseId", "enrollmentDate")
    VALUES (student_id, course_id, NOW());
END;
$$;
```

- `CREATE OR REPLACE PROCEDURE`: Opretter eller opdaterer en procedure

- `LANGUAGE plpgsql`: Angiver sproget (PostgreSQL's PL/pgSQL)

- `AS $$` ... `$$;`: Definerer procedurekroppen / afgrænsning

----

### Kald af en stored procedure

```sql []
CALL log_student_enrollment(1, 101);
```

----

### Eksempler på use cases

- Data manipulation, f.eks. indsættelse eller opdatering af flere tabeller

- Kompleks forretningslogik, f.eks. validering og Beregninger

- Batch processer, f.eks. periodiske Opgaver

----

## Opgaver

[https://ucl.kjc.dk/dfu/dfu-03-exercises-sp.html](/dfu/dfu-03-exercises-sp.html)

---

## Transactions

----

### Hvad er en transaction?

- En **transaction** er en serie af SQL operationer, der udføres som en enkelt enhed

- Enten gennemføres alle operationer, eller ingen af dem

----

### Hvorfor Transactions?

- Bevarer **dataintegritet**

- Sikrer **konsistens** i databasen

- Gør ændringer **atomiske** (alt eller intet)

----

### ACID-principperne

- **Atomicity**: Hele transactionen gennemføres eller ingen del af den

- **Consistency**: Databasen forbliver i en gyldig tilstand før og efter transactionen

- **Isolation**: Transaktioner er isolerede fra hinanden, så samtidige transaktioner ikke påvirker hinanden

- **Durability**: Når en transaction er gennemført, er ændringerne permanente, selv ved systemfejl

----

### Grundlæggende syntax

```sql []
BEGIN; -- Start transaction

-- SQL operationer her ->
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- <- SQL operationer her

COMMIT; -- Gem ændringer

-- ROLLBACK; -- Fortryd ændringer (bruges ved fejl)
```

----

### Eksempel

```sql []
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

IF (/* fejl opstår */) THEN
    ROLLBACK; -- Fortryd ændringer
ELSE
    COMMIT; -- Gem ændringer
END IF;
```

----

### Transactions i Stored Procedures

```sql []
CREATE PROCEDURE proc2()
LANGUAGE plpgsql
AS $$
BEGIN
   FOR idx IN 1..100 LOOP
       INSERT INTO my_tbl(col_num) VALUES(idx);
       IF idx % 10 = 0 THEN A
            COMMIT; B
        ELSE
            ROLLBACK; C 
        END IF;
    END LOOP;
END
$$;
```

----

### Best Practices

- Brug transactions til operationer, der involverer flere trin

- Hold transactions så korte som muligt for at minimere table locks

----

### Opgaver

[https://ucl.kjc.dk/dfu/dfu-03-exercises-transactions.html](/dfu/dfu-03-exercises-transactions.html)