TASIOMIND.DEV — OPERATIONAL▸▸▸FULL STACK DEVELOPER @ GWQ SERVICEPLUS AG▸▸▸FOUNDER — K8SGPT.AI▸▸▸OPEN SOURCE: ACTIVE▸▸▸DISTRIBUTED SYSTEMS / KUBERNETES / AI▸▸▸RUST + GO + PYTHON▸▸▸FIELD TESTED / STATUS — NOMINAL▸▸▸LOCATION: EUROPE/BERLIN▸▸▸TASIOMIND.DEV — OPERATIONAL▸▸▸FULL STACK DEVELOPER @ GWQ SERVICEPLUS AG▸▸▸FOUNDER — K8SGPT.AI▸▸▸OPEN SOURCE: ACTIVE▸▸▸DISTRIBUTED SYSTEMS / KUBERNETES / AI▸▸▸RUST + GO + PYTHON▸▸▸FIELD TESTED / STATUS — NOMINAL▸▸▸LOCATION: EUROPE/BERLIN▸▸▸
← databases

Intermediate SQL

November 18, 2017

Erweiterte SQL-Themen: Aggregationen, GROUP BY, Constraints, Foreign Keys.

Aggregatfunktionen

  • COUNT() Anzahl
  • SUM() Summe
  • AVG() Durchschnitt
  • MAX() / MIN() Extremwerte
code
SELECT COUNT(*) FROM movies;
SELECT AVG(tickets) FROM movies;
SELECT MAX(tickets), MIN(tickets) FROM movies;

GROUP BY

code
SELECT genre, SUM(cost)
FROM movies
GROUP BY genre;

HAVING (Filter fuer Gruppen)

code
SELECT genre, COUNT(*)
FROM movies
GROUP BY genre
HAVING COUNT(*) > 1;

Constraints

  • NOT NULL: Pflichtfeld
  • UNIQUE: keine Duplikate
  • PRIMARY KEY: eindeutig + not null
  • CHECK: Validierung
code
CREATE TABLE promotions (
  id INT PRIMARY KEY,
  name VARCHAR(150) NOT NULL UNIQUE,
  category VARCHAR(15)
);

Foreign Keys

code
CREATE TABLE movies (
  id INT PRIMARY KEY,
  title VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE promotions (
  id INT PRIMARY KEY,
  movie_id INT REFERENCES movies(id),
  name VARCHAR(50)
);

Orphan Records

Foreign Keys verhindern, dass Child-Records ohne Parent existieren.

code
DELETE FROM movies WHERE id = 6; -- Fehler, wenn Referenzen existieren

CHECK Constraint

code
CREATE TABLE movies (
  id INT PRIMARY KEY,
  duration INT CHECK (duration > 0)
);

Links