Module SQL
Relationele database. Een database, gegevensbank of databank is een verzameling van gegevens, ingericht met oog op makkelijke raadpleging en gebruik. Databases spelen een belangrijke rol in het opslaan en beheren van gegevens bij onder meer de overheid, financiële instellingen en bedrijven en worden op kleinere schaal ook privé gebruikt. Databases heb je in allerlei soorten en maten. Je hebt ook eenvoudige tabellen of kaartenbakken. Wanneer je de computer gebruikt heb je een kleine database in allerlei programma’s, zoals bijvoorbeeld het adressenboekjes bij je e-mailprogramma. De grootste databases vind je bij banken, verzekeringen en andere instellingen en deze zijn digitaal opgeslagen. Hoe een database er ook uit mag zien, ze moeten allemaal aan de volgende minimale (CRUD) voorwaarden voldoen om als database gezien te worden: 1. Gegevens moeten eenvoudig kunnen worden opgeslagen (Create). 2. Gegevens moeten eenvoudig kunnen worden opgezocht en doorzocht (Read). 3. Gegevens moeten onderhouden kunnen worden (Update). 4. Gegevens moeten verwijderd kunnen worden zonder dat dat de werking van dat systeem nadelig beïnvloedt (Delete). Om aan deze voorwaarden te kunnen voldoen is een essentiële regel belangrijk: De database moeten ‘integer’ zijn: 1. Gegevens moeten consistent zijn en mogen bijvoorbeeld niet dubbel worden opgeslagen. 2. De gegevens moeten geautoriseerd toegevoegd, onderhouden of verwijderd worden. Met andere woorden: de database moet beschermd worden tegen onbevoegde gebruikers. Vaak wordt dit gedaan aan de hand van een gebruikersnaam en een wachtwoord. Wanneer databases groter worden, is het erg belangrijk dat ze goed georganiseerd zijn. Dit is nodig om goed beheer mogelijk te maken en ervoor te zorgen dat informatie uit de database opgevraagd kan worden. Bij het beheren van een database horen hulpmiddelen. We noemen dit een database-managementsystem (DBMS), bijvoorbeeld Microsoft Access. Om informatie uit een digitale database op te vragen kan je de database vragen stellen met behulp van een query. Er zijn verschillende soorten talen voor queries maar wij houden ons vooral bezig met Structured Query Language (SQL). Het relationele model Er bestaan verschillende modellen voor databasesystemen, verschillende manieren om een database op te zetten. Het belangrijkste model is het relationele model. In een relationeel model worden alle gegevens opgeslagen in tabellen. Zo zal bijvoorbeeld alle contactgegevens van leerlingen op een school in 1 tabel opgeslagen zijn.
LEERLINGEN
LLNR
VOOR NAAM
TUSSEN VOEGSEL
ACHTER NAAM
STRAAT
HUIS POSTCODE PLAATS NUMMER
GE
TELEFOON
GEB_DATUM KLAS
SLACHT
80 Lorraine
Portiek
Vaartserijnstraat
4
3523TC
Utrecht
03473-44862 v
29-sep-81 5b
81 Patrick
Heijmans
Krozengaarde
21
3992JB
Houten
030-6989764 m
21-apr-82 5a
82 Gita
Kalisadra
Frederik van Eedenstr.
8
3451AZ
Vleuten
030-6753451 v
11-apr-82 6a
83 Aniel
Mangakas Verl. 216 Hoogravenseweg
3523KH
Utrecht
030-2743434 m
08-okt-81 6b
Berk
Langeweg
127
4136aW
Vianen
03473-17491 v
29-aug-83 5b
Kesteren
Merwedekade
151
3522JB
Utrecht
030-2234574 v
07-nov-82 4a
84 Wendy
van der
85 Nastasja van
Het relationele model is bedacht door de wiskundige E.F. Codd, toen werkzaam bij de computergigant IBM, en gepubliceerd en 1970. De term relationeel is afkomstig uit de wiskunde. In de verzamelingenleer bestaat het begrip relatie. Het idee is dat je met zinnen als 'Leerling y zit in klas x' een relatie legt tussen leerlingen en klassen. Bijvoorbeeld "Jantine de Bakker zit in 4b". De elementen van deze relatie geven de kolommen leerlingen en klas. In een rij van de tabel komen dan "Jantine de Bakker' en "4b". Hier komen we bij het maken van het project op terug.
Leerling Jantine de Bakker ...
klas 4b ...
Voorbeeld van een database. In deze module gaan we aan het werk met twee verschillende databases om mee te oefenen. Eén van deze databases behoort tot een schoolbibliotheek. Deze database heeft 6 tabellen die elk uit verschillende kolommen bestaan. De kolomnamen zijn zo gekozen dat ze zichzelf verklaren. In sommige gevallen kort je de kolomnaam in.
In de schoolbibliotheek kunnen meerdere exemplaren aanwezig van hetzelfde boek. Om de boeken toch te onderscheiden in een database heeft elk boek zijn eigen exemplaarnummer, wat terug te vinden is in de database als ‘EXNR’. In de tabel Exemplaren kan je zien dat aan elk exemplaar een boeknummer is gekoppeld. Dit boeknummer is weer terug te vinden in de het tabel Boeken en via het Auteurnummer (AUTNR) kan je terugvinden wie het heeft geschreven. Dit lijkt omslachtig maar is wel van belang. Stel je voor dat je de tabellen Boeken en Auteur samenvoegt en je dus bij elk boek alle gegevens van de auteur erbij zet. Auteurs schrijven vaak meerdere boeken en dus staan de gegevens van de auteur meerdere keren in de database en, zoals al eerder aangegeven, mogen gegevens niet dubbel voorkomen in een database. Mocht je bijvoorbeeld een auteur hebben die 15 boeken heeft geschreven die overlijdt dan moet je zijn sterfdatum op 15 verschillende plekken invullen en zodra je er één vergeet klopt je database niet meer. Opdrachten a. Bekijk de inhoud van de tabellen in de bibliotheekdatabase. √ b. Waarom kent de tabel reserveringen geen kolom Exnr. Een persoon bestelt meestal niet meerdere exact dezelfde versies van een boek.
SQL Een sql-query kent een vaste structuur. Je moet aangeven over welk tabel het gaat en welke kolommen en rijen je te zien wilt krijgen. In SQL kun je die vaste structuur herkennen aan de sleutelwoorden. De sleutelwoorden kun je het best steeds vooraan een nieuwe regel zetten en worden met hoofdletter opgeschreven. Dit is niet noodzakelijk, maar maakt het wel overzichtelijker. De basisstructuur van een eenvoudige query is: SELECT (geef aan welke kolommen je wilt zien) FROM (geef aan uit welke tabellen deze kolommen komen) WHERE (Geef aan welke voorwaarden moeten gelden. Dit bepaalt welke rijen je te zien krijgt); Let op! Een query eindigt altijd met ; Een eerste query. Laten we beginnen met een simpele query die alle namen van de leerlingen opvraagt uit de database van de schoolbibliotheek. 1. Om te beginnen start je Access op en open je het bibliotheek.mdb databasebestand. 2. Selecteer het ‘create’-tabblad bovenaan je scherm 3. Klik op 'Query Design'
4. Klik op close
5. Verander de view naar SQL.
6. Voer in: SELECT voornaam, tussenvoegsel,achternaam FROM leerlingen; 7. druk op run Als alles goed gegaan is heb je nu een tabel met alle namen van de leerlingen. Opdrachten a. Voor de volgende query uit: SELECT achternaam, tussenvoegsel, achternaam, FROM leerlingen; Wat is het verschil in de resultaten en vergelijking met: SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen; De eerste query geeft nu een error omdat de SELECT een komma heeft aan het eind, wat niet zo hoort.
b. Hoe ziet de query eruit als je alle kolommen van de tabel leerlingen wil selecteren? SELECT * FROM leerlingen;
Om bovenstaande opdracht uit te voeren kan je ervoor kiezen om elke kolomnaam van de tabel leerlingen achter SELECT te plaatsen. Dit kost tijd en is gevoelig voor schrijffouten. Eén schrijffout en je query werkt niet. Als je alle kolommen van een tabel wil hebben is het niet nodig om al die namen op te schrijven maar simpel weg een * te gebruiken. SELECT * FROM leerlingen; ORDER BY Lijsten van leerlingen zijn altijd in volgorde van hun achternaam opgeschreven. Dit maakt het vinden van een leerling op zo'n lijst makkelijker. Als we onze query uitbreiden met een regel ORDER BY wordt de lijst met leerlingen gesorteerd op hun achternaam: SELECT voornaam, tussenvoegsel,achternaam FROM leerlingen ORDER BY achternaam; Je kan de kolommen waarop je sorteert ook anders aanduiden. In plaats van de naam van deze kolom noem je dan het volgnummer van de kolom. Dit nummer vind je door naar de volgorde te kijken waarin ze achter SELECT staan vermeld. SELECT voornaam, tussenvoegsel,achternaam,klas FROM leerlingen ORDER BY 3; Op vele scholen worden leerlingen niet alleen gesorteerd op hun achternaam, maar ook in welke klas ze zitten. Vaak is het zo dat ze eerst gesorteerd worden op klas en dan pas op achternaam. Om dit voor elkaar te krijgen zet je beide kolommen achter ORDER BY met een komma ertussen: SELECT voornaam, tussenvoegsel,achternaam,klas FROM leerlingen ORDER BY klas, achternaam; Tenslotte kun je op twee manieren sorteren: oplopend (A-Z) of aflopend (Z- A). Gezien er in SQL enkel Engelse woorden worden gebruikt krijg je ascending en descending, afgekort tot ASC en DESC. Als je niks vermeldt, wordt er automatisch oplopend gesorteerd. Om aflopend te sorteren geef je dit aan door achter de kolomaanduiding na ORDER BY je keuze te vermelden. SELECT voornaam, tussenvoegsel,achternaam,klas FROM leerlingen ORDER BY 3 DESC; Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Maak een lijst van alle auteurs gesorteerd op achternaam. SELECT voornaam, achternaam FROM auteurs ORDER BY achternaam; b. Maak een lijst van alle boeken gesorteerd op Rubriek en dan op titel. SELECT rubriek, titel FROM boeken ORDER BY rubriek, titel;
DISTINCT Als we een overzicht willen hebben van de woonplaatsen van alle leerlingen zouden we de volgende query uit kunnen voeren: SELECT plaats FROM leerlingen; Het resultaat levert een lange lijst van woonplaatsen waarvan de eerste tien hier zijn gegeven: Query1 plaats Odijk Nieuwegein Utrecht Houten Utrecht Utrecht Utrecht Schalkwijk Utrecht Bunnik ...
Het valt meteen op dat alleen al in de eerste tien er meerdere keren Utrecht vermeld staat. Dit maakt het niet overzichtelijk om erachter te komen uit welke steden alle leerlingen komen. Om het overzichtelijk te maken willen we elke plaats maar één keer in de lijst hebben staan. We voegen daarvoor het sleutelwoord DISTINCT toe aan de query. SELECT DISTINCT plaats FROM leerlingen; Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Maak een lijst van alle bestaande rubrieken. SELECT DISTINCT rubriek FROM boeken;
b. Maak een oplopende lijst van alle klassen die in zich in het systeem bevinden. SELECT DISTINCT klas FROM leerlingen ORDER BY klas ASC;
WHERE Leraren hebben niet veel aan een lijst met alle leerlingen van hele school, maar willen graag een lijst van leerlingen die in een bepaalde klas zitten. Gelukkig is het in SQL mogelijk om voorwaarden mee te geven aan een query om zo de gewenste rijen te selecteren zodat enkel die rijen worden gegeven. Willen we een lijst hebben van leerlingen die in klas 5a zitten kunnen de we volgende query uitvoeren: SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE klas = '5a' ORDER BY achternaam; Let op! SQL werkt met verschillende datatypes en de meest gebruikte zijn integers (cijfers) en Strings (woorden/zinnen). Bij een String moeten altijd aanhalingstekens gebruikt worden en daarom staat er klas = '5a' Naast enkel het = teken zijn er andere operatoren van de wiskunde die gebruikt kunnen worden. < en > betekenen kleiner dan en groter dan <= en >= beteken kleiner dan of gelijk aan en groter dan of gelijk aan. <> betekent niet gelijk aan elkaar. Dit wil weleens per programma verschillen. Soms komt het voor dat er in een kolom niet alle rijen zijn ingevuld. Als je bijvoorbeeld kijkt bij de tabel auteurs, heeft niet elke auteur een sterfjaar. Een lege cel in een tabel heeft de waarde NULL. Als je alle auteurs wil selecteren zonder sterfjaar gebruik je de query: SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE sterf_jaar IS NULL ORDER BY achternaam; Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Maak een alfabetische lijst van alle titels van de boeken uit de rubriek biologie. SELECT titel FROM boeken WHERE rubriek = "biologie" ORDER BY titel; b. Maak een lijst met alle auteurs die na 1950 zijn geboren. SELECT voornaam, tussenvoegsel, achternaam FROM auteurs WHERE geb_jaar > 1950; c. Maak een lijst met alle leerlingen waarvan het telefoonnummer onbekend is. SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE telefoon IS NULL;
In veel gevallen komt het voor dat je meerdere eisen wil stellen, zoals een lijst van alle meiden uit 5a. Dit noemen we samengestelde voorwaarden. Om voorwaarden samen te stellen gebruik je de sleutelwoorden OR en AND, zoals bij booleaanse algebra (zie module 1 Logica). Om alle meiden uit 5a te selecteren kan je de volgende query gebruiken: SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE klas = '5a' AND geslacht = 'v' ORDER BY achternaam; Je mag zoveel voorwaarden als je wil samenstellen, zolang er maar tussen elke voorwaarde een OR of AND staat. Deze query bijvoorbeeld zoekt naar een leerlingen genaamd ‘Jantine de Bakker’: SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE voornaam = 'Jantine ' AND tussenvoegsel = 'de' AND achternaam = 'Bakker'; Zoals al eerder aangegeven is het niet verplicht om elk sleutelwoord op een nieuwe regel te plaatsen. We kunnen de vorige query ook op de volgende manier opschrijven wat duidelijk een stuk minder overzichtelijk is: SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE voornaam = 'Jantine ' AND tussenvoegsel = 'de AND achternaam = 'Bakker'; Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Maak een lijst met alle leerlingen uit 4b die in Utrecht wonen. SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE klas = ‘4b’ AND plaats = ‘Utrecht’; b. Jantine moet een lijst maken met alle meiden die wonen in Houten of Schalkwijk. Ze gebruikt de query: SELECT voornaam, tussenvoegsel, achternaam, geslacht FROM leerlingen WHERE (plaats = 'houten' OR plaats = 'Schalkwijk') AND geslacht = 'v' ORDER BY achternaam; Tot haar verbazing krijgt ze wel alle meiden uit Schalkwijk maar ze krijgt zowel alle meiden als jongens die in Houten wonen. Repareer de query zo dat ze dus een lijst krijgt met enkel de meiden uit Schalkwijk of Houten. c. Maak een lijst met alle leerlingen die in de 5e klas zitten. SELECT voornaam, tussenvoegsel, achternaam, klas FROM leerlingen WHERE klas = ‘5a’ OR klas = ’5b’;
LIKE De vorige opgave is op te lossen door achter WHERE alle verschillende 5e klassen aan te geven door klas = '5a' OR klas = '5b'. In dit geval zijn er maar 2 verschillende 5e klassen maar vaak komt het voor dat er veel meer 5e klassen zijn. Om ze niet allemaal op te hoeven schrijven kunnen we als hulpmiddel een * (sommige programma's gebruiken %) of _ gebruiken. De * houdt in dat je niet weet welke en hoeveel tekens er nog volgen en de _ houdt in dat er precies één teken mist, maar het niet uit maakt welke. Willen we dus alle jongens uit de 5e klas hebben gebruiken we: SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE klas LIKE '5_ ' AND geslacht = 'm' ORDER BY achternaam; Willen we alle leerlingen die een postcode hebben dat begint met 496 gebruiken we: SELECT voornaam, tussenvoegsel,achternaam FROM leerlingen WHERE postcode LIKE '496* ' ORDER BY achternaam; Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Maak een lijst met alle leerlingen waarvan de achternaam begint met Frederi SELECT voornaam, tussenvoegsel, achternaam FROM leerlingen WHERE achternaam LIKE ‘frederi*’;
b. Je bent op zoek naar de achternaam van de auteur Willem Frederik maar je weet niet zeker of Frederik met een c of k eindigt. SELECT voornaam, tussenvoegsel, achternaam FROM auteurs WHERE voornaam LIKE ‘Willem Frederi_’;
Functies Aan het einde van het schooljaar wil de directie weten hoeveel verschillende soorten boeken ze in de bibliotheek hebben staan, maar ze hebben niet de tijd om ze allemaal te gaan tellen. Gelukkig heeft SQL verschillende functies die daar bij kunnen helpen. Eén daarvan is ‘COUNT’: SELECT COUNT(*) FROM boeken; Het resultaat is enkel een getal dat precies aangeeft hoeveel rijen er de tabel Boeken staat. Zo is er nog een aantal functies, maar in tegenstelling tot COUNT moeten deze functies wel gekoppeld worden aan een kolomnaam. Om te voorkomen dat COUNT dubbele regels telt is het mogelijk DISTINCT toe te voegen, maar dan is het wel van belang om een kolomnaam toe te voegen: COUNT(DISTINCT kolomnaam). SUM(kolomnaam) Hiermee bereken je som van alle getallen in een kolom. Het is dan ook van belang dat het een kolom is met getallen. MAX(kolomnaam) Levert de grootste waarde in een kolom. MIN(kolomnaam) Levert de laagste waarde in een kolom AVG(kolomnaam) Berekent het gemiddelde van alle getallen in een kolom. De query: SELECT SUM(boete) FROM uitleningen; levert het totale bedrag dat betaald is een boetes. Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Wat is de grootste boete die iemand heeft moeten betalen SELECT MAX(boete) FROM uitleningen;
b. Hoeveel boeken zijn er in reparatie. SELECT COUNT(exnr) FROM exemplaren WHERE status = 'in reparatie';
c. In hoeveel rubrieken zijn de boeken in de bibliotheek opgedeeld. SELECT COUNT(DISTINCT rubriek) FROM boeken;
Groeperen Met de functie COUNT is het eenvoudig om van elke klas erachter te komen hoeveel leerlingen erin zitten: SELECT COUNT (*) FROM leerlingen WHERE klas = '4a'; SELECT COUNT (*) FROM leerlingen WHERE klas = '4b'; SELECT COUNT (*) FROM leerlingen WHERE klas = '5a'; SELECT COUNT (*) FROM leerlingen WHERE klas = '5b'; Maar dit is natuurlijk onhandig om dat voor elke klas te doen. We zouden eerst de leerlingen moeten groeperen per klas en dan pas tellen hoeveel leerlingen er in elke groep zitten. Om dit te kunnen doen gebruikt je het sleutelwoord GROUP BY: SELECT klas, COUNT (*) FROM leerlingen GROUP BY klas; Dit is de enige keer dat er een kolom en een functie te combineren zijn in de SELECT-regel, namelijk met een extra regel met GROUP BY (kolomnaam). Het is mogelijk om te groeperen met voorwaarden. Stel je voor dat je alle klassen met meer dan 25 leerlingen wil hebben. Hiervoor kan je het sleutelwoord HAVING voor gebruiken: SELECT klas, COUNT (*) FROM leerlingen GROUP BY klas HAVING COUNT(*) >= 25;
Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Hoeveel jongens en meiden zitten er op school. SELECT geslacht, COUNT(*) FROM leerlingen GROUP BY geslacht;
b. Maak een lijstje van aantallen leerlingen per woonplaats. SELECT plaats, COUNT(*) FROM leerlingen GROUP BY plaats;
c. Hoeveel boeken zijn er van elke rubriek aanwezig? SELECT rubriek, COUNT(*) FROM boeken GROUP BY rubriek;
d. Uit welke plaatsen komen meer dan tien leerlingen? SELECT plaats, COUNT(*) FROM leerlingen GROUP BY plaats HAVING COUNT(*) > 10;
Tabellen koppelen. In het tabel reserveringen zijn er drie leerlingen die hun reserveringkosten nog niet hebben betaald. De medewerkers van de bibliotheek willen graag weten welke leerlingen dit zijn, maar in het tabel staan enkel leerlingnummers. Ze stellen de volgende query op om erachter te komen welke leerlingen nog niet hebben betaald: SELECT voornaam, tussenvoegsel, achternaam, kosten_betaald FROM leerlingen, reserveringen WHERE kosten_betaald='n'; In plaats van dat ze de namen krijgen van de drie leerlingen krijgen ze een lange lijst waarin alle leerlingen drie keer in voor komen. Query1 voornaam tussenvoegsel achternaam kosten_betaald
Janco
Dijke van
n
Janco
Dijke van
n
Janco
Dijke van
n
Rene
Bokker
n
Rene
Bokker
n
Rene
Bokker
n
Bemmel Bemmel Bemmel
n n n
Marco Marco Marco
van van van
Om duidelijkheid te krijgen wat hier misgaat willen ze de leerlingnummers er ook bij hebben. Gezien zowel de tabel leerlingen als reserveringen een kolom hebben met de naam LLNR moet er een onderscheid gemaakt worden. Om aan te geven uit welk tabel de kolom komt zetten we tabelnaam eerste gevolgd door de kolomnaam met een punt ertussen: SELECT voornaam, tussenvoegsel, achternaam, kosten_betaald, leerlingen.llnr, reserveringen.llnr FROM leerlingen, reserveringen WHERE kosten_betaald='n'; Query1 voornaam tussenvoegsel achternaam kosten_betaald leerlingen.llnr reserveringen.llnr
Janco
Dijke van
N
51
58
Janco
Dijke van
N
51
102
Janco
Dijke van
N
51
66
Rene
Bokker
N
52
58
Rene
Bokker
N
52
102
Rene
Bokker
N
52
66
Bemmel Bemmel Bemmel
N N N
53 53 53
58 102 66
Marco Marco Marco
van van van
Hieruit is te zien dat de leerlingennummers van beide tabellen niet goed gekoppeld worden. Om dat voor elkaar te krijgen moet er een voorwaarde bij komen: SELECT voornaam, tussenvoegsel, achternaam, kosten_betaald, leerlingen.llnr, reserveringen.llnr FROM leerlingen, reserveringen WHERE leerlingen.llnr = reserveringen.llnr AND kosten_betaald = ‘n’;
Query1 voornaam tussenvoegsel achternaam
Monique
Aalbers
Ilse
Ebbenhorst
Dennis
Smets
Om duidelijk te maken wat er precies is gebeurd als je twee tabellen samenvoegt, volgt hier een klein voorbeeld. Naam Niels Ingrid Alice
Woonplaats Utrecht Houten Utrecht
Naam Niels Ingrid Alice
Postcode 3522PG 3991BD 3586aS
Stel je wilt deze twee tabellen samenvoegen tot een geheel. Je zou dan de volgende query kunnen gebruiken: SELECT plaats.naam, woonplaats, postcode FROM plaats, postcode Dit levert: Niels Utrecht 3522PG Niels Utrecht 3991BD Niels Utrecht 3586aS Ingrid Houten 3522PG Ingrid Houten 3991BD Ingrid Houten 3586aS Alice Utrecht 3522PG Alice Utrecht 3991BD Alice Utrecht 3586aS Hij koppelt elke rij van de ene tabel aan elke elke rij van de andere tabel. Naam Niels Ingrid Alice
Woonplaats Utrecht Houten Utrecht
Naam Niels Ingrid Alice
Postcode 3522PG 3991BD 3586aS
Het is dus zeer belangrijk dat als je twee of meer tabellen gebruikt in een query, dat je de tabellen op de juist manier koppelt. Wil je bijvoorbeeld weten welke titels de leerlingen lenen uit de bibliotheek, zou je drie tabellen bij elkaar moeten voegen; namelijk leerlingen, uitleningen en boeken: SELECT voornaam, tussenvoegsel, achternaam, titel FROM leerlingen, uitleningen, boeken WHERE leerlingen.llnr = uitleningen.llnr AND uitleningen.boeknr = boeken.boeknr;
Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Maak een lijst van auteurs en de titels van hun boeken die in de bibliotheek zijn. SELECT voornaam, tussenvoegsel, achternaam, titel FROM auteurs, boeken WHERE auteurnr = autnr; b. Maak een lijst van leerlingen en de boetes die ze hebben betaald SELECT voornaam, tussenvoegsel, achternaam, boete FROM leerlingen, uitleningen WHERE leerlingen.llnr = uitleningen.llnr; c. Maak een lijst van alle leerlingen en hun reserveringen SELECT voornaam, tussenvoegsel, achternaam, titel FROM leerlingen, reserveringen, boeken WHERE reserveringen.llnr = leerlingen.llnr AND reserveringen.boeknr = boeken.boeknr; d. Welke boeken zijn er in de bibliotheek van schrijvers uit de 19e eeuw? SELECT voornaam, tussenvoegsel, achternaam, geb_jaar, titel FROM auteurs, boeken WHERE geb_jaar >= 1800 AND geb_jaar < 1900 AND auteurnr = autnr; e. Van welke auteur zijn er boeken in de reparatie? SELECT voornaam, tussenvoegsel, achternaam FROM exemplaren, boeken, auteurs WHERE status = 'in reparatie' AND exemplaren.boeknr = boeken.boeknr AND autnr = auteurnr;
Subquery De bibliotheek wil graag weten welke leerling het afgelopen jaar de hoogste boete heeft betaald. Daarvoor moeten we twee vragen beantwoorden. Wat was de hoogste boete en wie moest die betalen. De eerste vraag is het eenvoudigst: SELECT MAX(boete) FROM uitleningen; De hoogste boete blijkt f4,- te zijn. Nu moeten we alleen nog uitzoeken bij welke leerlingen deze boete hoort: SELECT voornaam, tussenvoegsel, achternaam FROM uitleningen, leerlingen WHERE leerlingen.llnr = uitleningen.llnr AND boete = 4,00; Deze twee queries kunnen samengevoegd worden tot één query met behulp van een subquery: SELECT voornaam, tussenvoegsel, achternaam FROM uitleningen, leerlingen WHERE leerlingen.llnr = uitleningen.llnr AND boete = ( SELECT MAX(boete) FROM uitleningen ); Opdrachten Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. a. Wie is de jongste auteur die ze in hun database hebben staan? SELECT voornaam, tussenvoegsel, achternaam FROM auteurs WHERE geb_jaar = ( SELECT MAX(geb_jaar) FROM auteurs);
Oefeningen Geef in alle gevallen de query die je hebt gebruikt en niet de resultaten. 1a. Maak een lijst van alle presidenten die als hobby vissen hebben SELECT pres_name FROM preshobby WHERE hobby = 'fishing'; 1b. Hoeveel presidenten hebben als hobby vissen? SELECT COUNT(*) FROM preshobby WHERE hobby = 'fishing'; 2. Welke presidenten hebben meer dan 5 kinderen? SELECT pres_name FROM presmarriage
WHERE nr_children > 5; 3. Welke presidenten leefde nog toen deze database werd gemaakt? SELECT pres_name FROM president WHERE death_age IS NULL; 4. Welke president was het oudst op zijn trouwdag en hoe oud was hij? SELECT pres_name, pr_age FROM presmarriage WHERE pr_age = ( SELECT MAX(pr_age) FROM presmarriage); 5. Welke presidenten hebben geen volledige periode afgemaakt (één periode duurt 4 jaar) SELECT pres_name FROM president WHERE yrs_serv < 4; 6. Welke presidenten trouwden nadat ze waren gekozen tot president. SELECT presmarriage.pres_name FROM presmarriage, admin WHERE admin.year_inaugurated < presmarriage.year;
CRUD Tot nu toe hebben we enkel query’s gemaakt die data uit een database kunnen selecteren (Read), de zogenaamde SELECT-queries. SQL kan nog voor veel meer dingen gebruikt worden omtrent databases namelijk het aanmaken van tabellen, invoegen van data (Create), updaten van data (Update) en het verwijderen van data (Delete). CREATE Om te beginnen moet er eerst een database aangemaakt worden. Dit kan met de volgende query: CREATE DATABASE naam; Let erop dat er geen spaties voor mogen komen in de naam van een database. Na het maken van de database kunnen we overgaan op het creëren van tabellen. Net zoals bij de SELECT-query’s kent ook deze CREATE-query een standaard opbouw, namelijk: CREATE TABLE naam ( kolomnaam type, kolomnaam type, kolomnaam type, kolomnaam type, kolomnaam type, … … … );
Stel je voor dat je een tabel wil maken met een klantennummer, voornaam, achternaam, adres en een woonplaats. Om dat voor elkaar te krijgen gebruik je deze query: CREATE TABLE naam ( KlantNR int, Voornaam varchar(255), achternaam varchar(255), adres varchar(255), woonplaats verchar(255) );
Types Bij het maken van een database moet je er ook bij bedenken welke type data je in een kolom wil opslaan. Wil je een getal opslaan, een datum of misschien een naam. SQL kent meerdere types en tussen sommige programma’s wil daar nog wel eens een verschil tussen zitten. Wij gebruiken in deze module de types die bij MySQL horen. Hieronder staat een rijtje van de belangrijkste en wil je de hele lijst zien kan je surfen naar: http://w3schools.com/sql/sql_datatypes.asp Type varchar(grootte) Tekst Enum(‘x’,’y’,’z’) int double date()
Beschrijving Een tekst waarvan je de maximum lengte kan aangeven. Bijvoorbeeld voor voornamen, achternamen, wachtwoorden Een tekst van maximaal 65.535 tekens lang. Wordt vooral gebruikt voor langere stukken tekst bijvoorbeeld een beschrijving van een artikel. Een lijst van mogelijke waardes die ingevuld kunnen worden. Bijvoorbeeld voor geslacht, rubriek, genre Een geheel getal, wordt heel vaak gebruikt voor ID-nummers Een kommageta. Een datum in de vorm van JJJJ-MM-DD
INSERT Nu het hele database is aangemaakt moet het mogelijk zijn om nieuw data in te voegen, bijvoorbeeld een nieuwe klant. Dit wordt gedaan aan de hand van INSERT: INSERT INTO tabel_naam (Kolomnaam, Kolomnaam) VALUES (‘data’, ‘data’); Komt het voor dat je in een bepaalde kolom geen data wil plaatsen dan sla je deze kolomnaam over. Let erop dat je de data in dezelfde volgorde zet als je de kolomnamen hebt staan. Komt het voor dat je alle kolommen wilt vullen dan is het niet verplicht deze allemaal op te schrijven als je er maar voor zorgt dat de data in de juiste volgorde staat. Voorbeeld: INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
UPDATE Het kan voorkomen dat een klant zijn wachtwoord wil veranderen, of dat hij misschien is verhuisd. Dit is mogelijk met de UPDATE-query: UPDATE tabelnaam SET kolomnaam1 = waarde1, kolomnaam2 = waarde2, … WHERE kolomnaam = waarde;
Stel je voor dat de klant met het emailadres
[email protected] zijn wachtwoord wil veranderen. Dan kan je de volgende query gebruiken om dat voor elkaar te krijgen: UPDATE klanten
SET wachtwoord='nieuw_ww' WHERE email = ‘
[email protected]’;
DELETE We hebben nu bijna alles gehad van CRUD. Met INSTERT kunnen we nieuwe objecten aanmaken (Create), met behulp van SELECT-query’s kunnen we data opvragen (Read) en met de UPDATE is het mogelijk om bestaande informatie aan te passen(Update). We missen alleen nu de Delete en dat kan je doen aan de hand van DELETE: DELETE FROM tabelnaam WHERE kolomnaam1 = waarde1; (Je mag meerdere voorwaardes meegeven) Stel je wilt een klant verwijderen met de naam Alfreds Futterkiste gebruik je de volgende query: DELETE FROM klanten WHERE klantnaam='Alfreds Futterkiste'
