One of the easiest ways to get noticed or hired as a junior developer in the tech industry is by documenting everything you are learning. Build great projects, but don't forget to document your journey along the way.
—Olawale Daniel

De SELECT clause in detail

Dit hoofdstuk en de vier volgende zijn eigenlijk één geheel. We bekijken de verschillende componenten van een query in meer detail. De eerste twee hoofdstukken SQL lieten je al kennismaken met SELECT, FROM, WHERE, GROUP BY, HAVING en ORDER BY. Nu is het tijd om wat dieper in deze clauses te duiken.

In dit hoofdstuk bekijken we enkele mogelijkheden van SELECT. Als voorbeeld gebruiken we een uitgebreide versie van de tabel ‘opleidingsonderdeel’ die in de introductie SQL al gebruikt werd. Moeten we het nog zeggen? Dit is een werktekst. Lezen is fijn, doen is nog fijner. Test dingen uit en maak oefeningen.

We gebruiken in dit hoofdstuk een eenvoudig schema met slechts één tabel. Dit schema komt overeen met het volgend conceptueel model:

Het entiteittype ‘opleidingsonderdeel’ heeft acht attributen. Het attribuut ‘opocode’ is het sleutelattribuut.

Je vindt de tabel ‘opleidingsonderdeel’ in het schema ‘uclloket’ in de databank ‘df’. Om deze tabel tot bij jullie te krijgen waren volgende stappen nodig (we komen hier later op terug). Deze stappen zijn enkel ter illustratie. Je kan ze niet zelf uitvoeren want je hebt geen schrijfrechten in de databank ‘df’.

1. Ik open een query tool in de databank ‘df’ en voer deze code uit:

   CREATE SCHEMA uclloket

2. Alle collega’s en studenten moeten toegang krijgen tot dit schema:

   GRANT USAGE on schema uclloket to student;
   GRANT USAGE on schema uclloket to lector;

3. Vervolgens maak ik de tabel ‘opleidingsonderdeel’ aan:

   CREATE TABLE uclloket.opleidingsonderdeel (
     opocode char(6) NOT NULL ,
     studiepunten smallint NOT NULL ,
     naam varchar(100) NOT NULL ,
     van date NOT NULL ,
     tot date ,
     taal char(2) NOT NULL ,
     semester smallint NOT NULL ,
     coordinator char(8) NOT NULL ,
     CONSTRAINT pk_opleidingsonderdeel_opocode PRIMARY KEY ( opocode )
   );

4. Iedereen moet SELECT rechten krijgen. Het is niet de bedoeling dat studenten deze tabel gaan aanpassen, rijen verwijderen of updaten enz. De rechten blijven dus beperkt tot enkel SELECT:

   grant select on all tables in schema uclloket to student;
   grant select on all tables in schema uclloket to lector;

5. Tenslotte werden er 21 rijen toegevoegd via een import van een .CSV bestand. Dat had ook met INSERT INTO gekund natuurlijk.

Iets opvragen

Met SELECT kan je iets vragen aan een databaseserver. Dat kan zelfs een kleine berekening zijn:

SELECT 3*4; -- geeft 12
SELECT sqrt(200); -- geeft als resultaat de wortel uit 200, nl. 14.142...
SELECT TRUE AND FALSE; -- resultaat is FALSE

Wel een beetje onnozel om een databankserver te gebruiken als rekenmachine, maar het kan dus wel.

SELECT ceil(2.1) -- geeft: 3
SELECT ceiling(2.1) -- alternatief, doet juist hetzelfde
SELECT ceil(-2.1) -- geeft: -2 (opgepast met negatieve getallen: -2 > -2.1)

Kolommen selecteren

Wat ons meer interesseert, is informatie halen uit gegevens die in een databank opgeslagen zijn. Dat deden we al bij de inleidende voorbeelden, dus we kunnen hier kort zijn.

Door middel van de * kan je alle kolommen van een tabel opvragen:

SELECT *
FROM opleidingsonderdeel

De databankserver begint met de FROM clause te bekijken. De volledige tabel ‘opleidingsonderdeel’ wordt in het werkgeheugen van de server geladen. Dan bekijkt de server de SELECT clause. De ster * geeft aan dat alle kolommen getoond moeten worden.

Stel dat we alleen de kolommen met de naam van een opo en het semester waarin het gegeven werd willen zien, dan kan dat als volgt:

SELECT naam, semester
FROM opleidingsonderdeel

Alias voor een kolomnaam

Soms wil je een andere hoofding voor een getoonde kolom. Stel dat we volgende query uitvoeren om een overzicht te krijgen van de begindatum waarop elk OPO voor het eerst gegeven is:

SELECT naam, van
FROM opleidingsonderdeel

Boven de tweede kolom zal de naam ‘van’ staan. Misschien wil je daar liever het woord ‘startdatum’ als kolomtitel? Dat kan heel eenvoudig met een alias. Die geef je m.b.v. het woord AS.

SELECT naam, van AS startdatum
FROM opleidingsonderdeel

SELECT naam, van AS "startdatum OPO"
FROM opleidingsonderdeel

Nieuwe kolommen bijmaken

Je hoeft je niet te beperken tot de kolommen die in de databank voorkomen. Het is niet zo moeilijk om nieuwe kolommen bij te maken, al dan niet gebaseerd op bestaande kolommen.

Constante tekst of getal

Als je als kolom een string of een getal zet, dan wordt dit voor elke rij in het resultaat herhaald.

SELECT opocode, naam, 'Toegepaste informatica'
FROM opleidingsonderdeel

SELECT opocode, naam, 'Toegepaste informatica' AS Opleiding
FROM opleidingsonderdeel
 
-- versie 2 met spaties in de hoofding
SELECT opocode, naam, 'Toegepaste informatica' AS "Opleiding Proximus"
FROM opleidingsonderdeel

Rekenen

Een studiepunt komt ruwweg overeen met 25 uur werk, alles inbegrepen (lessen bijwonen, studeren, opdrachten maken, studeren voor examen, examen afleggen, …). De volgende query maakt een nieuwe kolom ‘werkuren’ op basis van de gegeven kolom studiepunten:

SELECT opocode, naam, studiepunten * 25 AS werkuren
FROM opleidingsonderdeel

Strings aan elkaar plakken

Je kan meerdere kolommen combineren tot één kolom. Voor studentencoaches is het nuttig dat de naam van een OPO altijd gevolgd wordt door het aantal SP tussen haakjes, zoals bvb. “Database Foundations (6)”.

Met het dubbele ‘pipe’ karakter (||) kan je kolommen en tekst aan elkaar plakken. De gevraagde combinatie van naam en SP kan door volgende query gerealiseerd worden. Let goed op het verschil tussen enkele en dubbele aanhalingstekens.

SELECT opocode, naam || ' (' || studiepunten || ')' AS "OPO (studiepunten)"
FROM opleidingsonderdeel

Stringfuncties

Hoog tijd dat we even de fantastische documentatie van PostgreSQL bekijken. Die is zeer uitgebreid. Op sommige vragen over SQL-functies zullen de lectoren je trouwens voorstellen om het antwoord zelf in de documentatie op te zoeken. RTFM, dus!

Ik vertrek meestal bij de indexpagina https://www.postgresql.org/docs/current/bookindex.html. Bij de ‘S’ van string vind ik een aantal verwijzingen naar https://www.postgresql.org/docs/current/functions-string.html. Blader eens door de lange lijst met functies. We geven twee voorbeelden van stringfuncties.

Wisselen tussen hoofdletters en kleine letters

Met de functies lower() en upper() kan je wisselen tussen kleine letters (‘lower case’, letterlijk ‘onderkast’) en hoofdletters (‘upper case’, letterlijk bovenkast, zie foto hieronder voor de oorsprong van deze benamingen). Zeker als we in het volgend hoofdstuk willen zoeken naar strings (in de WHERE clause) is het meestal een veilige optie om eerst alles om te zetten naar kleine letters want strings in SQL zijn hoofdlettergevoelig (‘casesensitive’). De string 'Van Hee' is niet dezelfde als 'Van hee'.

De volgende query toont alle namen van OPO’s in kleine letters:

SELECT opocode, lower(naam)
FROM opleidingsonderdeel

Substring

Een tweede basisbewerking met strings is een deel uit de string selecteren. Dat kan met de stringfunctie substring(). De volgende query geeft enkel het cijfergedeelte van de lectorcode (kolom coordinator), dus zonder de ‘u’:

SELECT opocode, substring(coordinator from 2) -- begin pas bij letter 2 tot op het einde
FROM opleidingsonderdeel

Het zal je wel al opgevallen zijn dat alle opocodes beginnen met ‘MBI’ (in elk geval toch in de opleiding TI). Alle lectoren hebben een nummer dat begint met ‘u’. Schrijf een query die de opocode en de coördinator toont, maar zonder de beginletter(s) die altijd het zelfde zijn, zoals onderstaande figuur laat zien. Let je ook op de kolomhoofdingen?

Toon / verberg oplossing

SELECT substring(opocode from 4) AS "korte opocode",
  substring(coordinator from 2) AS "lector(verkort)"
FROM opleidingsonderdeel

CASE

Stel: in plaats van een kolom met studiepunten willen we enkel een kolom tonen met de waarden ‘klein’ (voor OPO’s met 4 of minder SP), ‘middel’ (OPO’s met 5 of 6 SP) en ‘groot’ voor OPO’s met meer dan 6 SP. Die kolom zal gebaseerd zijn op de kolom ‘studiepunten’ en wordt gemaakt met een CASE structuur.

We verwijzen naar de documentatie. Via de indexpagina vinden we ‘CASE: conditional expressions’ op de pagina https://www.postgresql.org/docs/current/functions-conditional.html. Je vindt hier de volgende structuur (en voorbeelden):

CASE
  WHEN condition THEN result
  [WHEN ...]
  [ELSE result]
END

Toegepast op het gevraagde geeft dat volgende mogelijke query:

SELECT naam,
  CASE
    WHEN studiepunten <= 4 THEN 'klein'
    WHEN studiepunten <= 6 THEN 'middel' -- nakijken of > 4 hoeft dus niet meer
    ELSE 'groot'
  END AS omvang
FROM opleidingsonderdeel

De eerste voorwaarde die klopt zorgt voor het toekennen van een waarde in de kolom. De rest van de lijnen in de CASE worden dan overgeslagen. Merk ook op dat we een alias (AS) gebruiken omdat de kolomhoofding anders gewoon ‘case’ toont.

Witruimte (inspringen met tabs of spaties) is niet belangrijk voor de databankserver, maar wel belangrijk voor mensen die je code moeten lezen (zoals lectoren die je opdrachten moeten verbeteren :-).

OPO's die geen einddatum hebben noemen we ‘nieuwe’ OPO's in tegenstelling tot de ‘oude’ OPO's die niet meer gegeven worden en een einddatum hebben in onze tabel. Schrijf de query die het resultaat van onderstaande figuur genereert. Moeten we het nog zeggen? Let op de kolomhoofdingen …

Toon / verberg oplossing

SELECT naam, opocode,
  CASE
    WHEN tot is null THEN 'nieuw'
    ELSE 'oud'
  END AS "oud of nieuw"
FROM opleidingsonderdeel

Distinct

Geef een overzicht van alle mogelijke talen die in de tabel gebruikt worden in OPO’s. De query hiervoor is niet moeilijk om op te stellen:

SELECT taal
FROM opleidingsonderdeel

Het resultaat bevat evenveel rijen als er rijen in de tabel zijn. Dat is niet echt wat we willen. Om herhaling te vermijden gebruik je DISTINCT achter het woord SELECT:

SELECT DISTINCT taal
FROM opleidingsonderdeel

Opgelet: de volledige combinatie van alle kolommen die achter het woord DISTINCT komen moet verschillend zijn. Pas de query aan tot:

SELECT DISTINCT taal, coordinator
FROM opleidingsonderdeel

We krijgen nu meer rijen dan daarnet, maar wel minder dan het volledige aantal rijen omdat er sommige coördinatoren meerder OPO’s in dezelfde taal hebben. Kijk bvb na in de oorspronkelijke volledige tabel dat coördinator ‘u0012047’ drie keer voorkomt met als taal nederlands. De combinatie van taal en coördinator is dus drie keer hetzelfde. Door het keyword DISTINCT zal deze rij maar één keer getoond worden.

-- de lijst zonder dubbels (voor de volledige lijst verwijder je distinct)
SELECT distinct coordinator,studiepunten
FROM opleidingsonderdeel

Werken met een datum

Het datatype date is zeer belangrijk in een databank. Niet voor niets bestaan er tientallen functies die een datum kunnen manipuleren. We bekijken er hier slechts enkele. Bovendien is er niet enkel date als datatype, maar ook timestamp, time en interval (zie documentatie op https://www.postgresql.org/docs/current/datatype-datetime.html).

Voorlopig beperken we ons tot het datatype date. De documentatie geeft een overzicht van de date/time functies die PostgreSQL aanbiedt.

Extract … from

Een datum bevat het jaar, de maand en de dag. Een tijd bovendien ook uren, minuten, seconden enz. Met de functie EXTRACT kan je een stuk uit een datum (of tijd) halen. Een klein voorbeeld uit de OPOlijst om dit te verduidelijken:

SELECT opocode, EXTRACT(year FROM van) AS "start academiejaar"
FROM opleidingsonderdeel

Deze query toont een lijst van alle OPO’s met code en het jaartal waarin dat OPO voor het eerst gegeven is. De lijst met mogelijkheden is nogal uitgebreid: month, week, day, hour, … (zie documentatie).

Rekenen met een datum

Je kan een datum verhogen of verlagen met een integer, data van elkaar aftrekken enz. (documentatie).

Voorbeeld 1: data van elkaar aftrekken

De volgende query berekent voor elk OPO hoeveel dagen het zal lopen of gelopen heeft. Als we einddatum niet kennen, kan het resultaat natuurlijk niet berekend worden en krijg je de waarde NULL:

SELECT opocode, tot - van AS "aantal dagen"
FROM opleidingsonderdeel

Blijkbaar is het OPO met code ‘MBI68A’ het vak met de langste looptijd, nl. 5477 dagen.

Voorbeeld 2: functies age() en now()

Een tweede voorbeeld laat twee functies zien: age() en now(). Hoe oud ben ik vandaag als ik op 7 mei 1967 geboren ben?

SELECT age(now(),'1967-05-07')
-- alternatief is: SELECT age(CURRENT_DATE, '1967-05-07')
-- tweede alternatief: SELECT age(timestamp '1967-05-07')

Veranderen van datatype

Een klassiek probleem in getypeerde programmeertalen is het omzetten van een waarde naar een ander datatype. Je wilt een integer optellen bij een kommagetal, een getal converteren naar een string, … In het Engels spreekt men dan over ‘to cast’.

Het laatste voorbeeld in het vorige deeltje waarin de leeftijd berekend werd is een voorbeeld van een automatische cast. Volgens de documentatie waar we al een paar keer naar verwezen, werkt de functie age() in de eerste versie van de query op twee timestamps. We schreven echter age(now(),'1967-05-07'). De functie now() levert een timestamp, maar het tweede argument (’1967-05-07’) is een date en geen timestamp. PostgreSQL zal echter stilzwijgend van deze date een timestamp maken (door als tijdstip middernacht te nemen).

CAST … AS

Vaak moet je echter zelf waarden casten. Dat kan met twee soorten syntax: ofwel met de CAST(... AS ...) functie, ofwel met de notatie ::. We geven enkele eenvoudige voorbeelden:

-- cast een string naar een geheel getal
SELECT CAST('123' AS integer)
SELECT '123'::integer -- andere notatie, maar doet hetzelfde als de CAST

-- cast een geheel getal naar een numeric
SELECT CAST(1234 AS numeric(8,3))
SELECT 1234::numeric(8,3) -- andere notatie, maar doet hetzelfde

-- een getal naar een string
SELECT CAST(1234 AS char(6)). -- maakt de string '1234  '

TO_CHAR

Het laatste voorbeeld hierboven (getal naar string) is een beetje vreemd. Meestal wil je de waarden in een kolom naar een string converteren met een bepaald formaat, bvb. een datum in Europese notatie met schuine strepen tussen dag, maand en jaar. Hiervoor bestaat de functie TO_CHAR() (documentatie). Enkele voorbeelden:

SELECT TO_CHAR(date '1967-05-07', 'dd/mm/yyyy'); -- resultaat: '07/05/1967'
SELECT TO_CHAR(date '1967-05-07', 'day dd month yyyy'); -- resultaat: 'sunday 07 may 1967'
SELECT TO_CHAR(148.5, '9999.9999'); -- geeft de string ' 148.5000'