1 / 43

Hoofdstuk 9

Hoofdstuk 9. Inleiding. Relationele databases. en. SQL: basis. SQL. functies. subqueries. twee tabellen. exists. oefeningen. Een aantal termen. Database Gegevensbank = verzameling van gegegevens DBMS Database Management systeem

lynch
Download Presentation

Hoofdstuk 9

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hoofdstuk 9 Inleiding Relationele databases en SQL: basis SQL functies subqueries twee tabellen exists oefeningen Sondervick College – Veldhoven

  2. Een aantal termen • Database Gegevensbank = verzameling van gegegevens • DBMSDatabase Management systeem • Relationeel model Een manier om een database te organiseren, nl. met tabellen • Een rij in de tabel:Alle gegevens van een bepaaldekaart of record • Kolommen in de tabel:Worden ook welveldengenoemd • Een aantal DBMS’en:AccessFilemakerOracleMySQL Sondervick College - Veldhoven

  3. Takenvan een databasesysteem • Het opslaan van gegevens en het mogelijk maken van wijzigingen • Het kunnen opvragen van informatie en het verwerken van informatie • Het bewaken van de integriteit (dus het voorkomen van fouten) Sondervick College - Veldhoven

  4. Voorbeeld database De voorbeelden op de volgende dia’s hebben betrekking op de database Bibliotheek, met de volgende tabellen: Tabel: LEERLINGEN LLNR - VOORNAAM - TUSSENVOEGSEL - ACHTERNAAM - STRAAT - HUISNUMMER - POSTCODE - PLAATS - TELEFOON - GESLACHT - GEB_DATUM - KLAS Tabel: AUTEURS AUTEURNR - VOORNAAM - VOORLETTERS - TUSSEN-VOEGSEL - ACHTERNAAM - GEB_DATUM - STERF_JAAR Tabel: BOEKEN BOEKNR - AUTNR - TITEL - RUBRIEK Tabel: EXEMPLAREN EXNR - BOEKNR - UITLEENBAAR - STATUS Sondervick College - Veldhoven

  5. Kolommen met alleen een nummer Er komen kolommen voor die alleen maar een nummer bevatten: LLNR / AUTEURNR / BOEKNR / EXNR Reden: • Een nummer is altijd uniek. Je kunt twee leerlingen met exact dezelfde naam hebben, maar de leerlingennummers verschillen altijd. • Met een nummer kun je gemakkelijk verwijzen naar een andere tabel. Sondervick College - Veldhoven

  6. SQL in Access Basisstructuur van een eenvoudige query Sondervick College - Veldhoven

  7. SQL: select en from SELECT: Achter SELECT geef je de kolommen op die je in de uitvoer wilt zien. Zet je achter select een sterretje in plaats van kolomnamen, dan krijg je de inhoud van alle kolommen op het scherm. FROM: Achter FROM geef je de naam van de tabel (of tabellen) waaruit deze kolommen komen. Sondervick College - Veldhoven

  8. SQL: order by SELECT VOORNAAM, TUSSENVOEGSEL, ACHTERNAAM, KLAS FROM LEERLINGEN ACHTERNAAM ORDER BY Door de opdracht ORDER BY wordt de lijst gesorteerd. Je kunt ook op twee of meer kolommen laten sorteren, door achter ORDER BY de twee kolommen op te geven. Sondervick College - Veldhoven

  9. SQL: desc SELECT VOORNAAM, TUSSENVOEGSEL, ACHTERNAAM, KLAS LEERLINGEN FROM ORDER BY 3 DESC Nu wordt op de 3e kolom gesorteerd (ACHTERNAAM) DESC betekent: Aflopend (Descending), dus van Z naar A. Als er niets vermeld wordt (of ASC = ascending) wordt oplopend gesorteerd. Sondervick College - Veldhoven

  10. SQL: distinct SELECT PLAATS LEERLINGEN FROM Het resultaat is een lange rij van plaatsnamen.Die plaatsnamen kunnen meerdere keren voorkomen. SELECT DISTINCT PLAATS FROM LEERLINGEN Door DISTINCT wordt elke plaats maar één keer afgedrukt. Sondervick College - Veldhoven

  11. SQL: where Er kunnen voorwaarden worden gesteld aan de rijen, die moeten worden afgedrukt. Voorwaarden geef je achter het sleutelwoord: WHERE SELECT VOORNAAM, TUSSENVOEGSEL, ACHTERNAAM FROM LEERLINGEN WHERE GESLACHT = "M" Alleen de leerlingen, die als geslacht M hebben, worden afgedrukt Sondervick College - Veldhoven

  12. SQL: like Je mag wildcards gebruiken bij de voorwaarden, maar je moet dan geen = maar like als vergelijkingsoperator gebruiken. VOORNAAM, TUSSENVOEGSEL, ACHTERNAAM SELECT FROM LEERLINGEN WHERE VOORNAAM like "M*" Alle leerlingen, waarvan de voornaam met een M begint, worden afgedrukt Sondervick College - Veldhoven

  13. SQL:Wildcards en vergelijkingsoperatoren: In Access: er mag alles staan (ook meerdere tekens) * Standaard SQL: er mag alles staan (ook meer tekens) % In Access: één willekeurig teken ? _ Standaard SQL:één willekeurig teken < en > Kleiner dan / groter dan <= en >= Kleiner dan of gelijk aan / groter dan of gelijk aan Niet gelijk aan < > Sondervick College - Veldhoven

  14. SQL: is null SELECT * FROM LEERLINGEN IS NULL WHERE TELEFOON Dit betekent: Er is niets ingevuld Sondervick College - Veldhoven

  15. SQL:Samengestelde voorwaarden Als er aan meer dan één voorwaarde moet worden voldaan gebruik je AND SELECT * FROM LEERLINGEN WHERE ACHTERNAAM = "Bakker" AND TUSSENVOEGSEL = "de" AND VOORNAAM = "Jantine" Alleen de gegevens van Jantine de Bakker worden afgedrukt Sondervick College - Veldhoven

  16. SQL: OR Als aan de ene of aan de andere voorwaarde moet worden voldaan gebruik je OR Deze OR is niet exclusief. D.w.z.: rijen die aan allebei de voorwaarden voldoen worden ook afgedrukt. SELECT * FROM LEERLINGEN WHEREKLAS = "4A"OR KLAS = "4B" Leerlingen die in klas 4A of 4B zitten worden afgedrukt, dus je krijgt een lijstje van de leerlingen van 4a en van 4b Sondervick College - Veldhoven

  17. Functies in SQL Het aantal getallen of woorden in de kolom COUNT(KOLOMNAAM) SUM(KOLOMNAAM) De som van alle getallen in de kolom MAX(KOLOMNAAM) De grootste waarde in de kolom MIN(KOLOMNAAM) AVG(KOLOMNAAM) De kleinste waarde in de kolom De gemiddelde waarde in de kolom Sondervick College - Veldhoven

  18. SQL: subqueries We willen weten welke leerling de hoogste boete heeft betaald na het lenen van een boek Dat doen we eerst m.b.v. twee queries, of als je de naam van de leerling ook wilt weten, zelfs drie 1: Wat is de maximale boete die betaald is? 2: Welke leerling(en) moesten die boete betalen? Daarna laten we zien hoe het ook met één query kan m.b.v. subqueries Sondervick College - Veldhoven

  19. SQL: subqueries We gaan eerst de maximale boete uitzoeken m.b.v. een query: SELECT MAX (BOETE) FROM UITLENINGEN Die maximale boete blijkt 4 euro te zijn Dan zoeken we uit welk leerlingennummer uit de tabel UITLENINGEN bij die maximale boete hoort. Deze twee queries gaan we combineren tot één query Deze twee queries gaan we combineren tot een query SELECT LLNR FROM UITLENINGEN WHERE BOETE = 4 Sondervick College - Veldhoven

  20. SQL: subqueries Dit is een subquery SELECT LLNR FROM UITLENINGEN WHERE BOETE = ( SELECT MAX (BOETE) FROM UITLENINGEN ) Nu weet je alleen het LLNR en nog niet de naam. Als je die wilt weten moet er nog een subquery bij. Die staat op de volgende dia Sondervick College - Veldhoven

  21. SQL: subqueries SELECT NAAM, KLAS FROM LEERLINGEN WHERE LLNR IN ( SELECT LLNR FROM UITLENING WHERE BOETE = ( SELECT MAX(BOETE) FROM UITLENINGEN ) ) Sondervick College - Veldhoven

  22. SQL:Tabellen combineren We willen weten wie de auteur is van het boek met de titel: De woeste wandeling. Daarvoor moeten we twee tabellen combineren: AUTEURS en BOEKEN Deze nummers moeten gelijk zijn Deze nummers moeten gelijk zijn Tabel Boeken: Tabel Auteurs: Sondervick College - Veldhoven

  23. SQL: subqueries De query, die de naam van de auteur van het boek met de titel De woeste wandeling levert, is: Dit noem je een join SELECT TITEL, VOORNAAM, ACHTERNAAM FROM BOEKEN, AUTEURS WHERE TITEL = "De woeste wandeling" AND BOEKEN.AUTEURNR = AUTEURS. AUTEURNR Denk bij een query met twee tabellen altijd om de join (zie hierboven) Sondervick College - Veldhoven

  24. SQL: GROUP BY Met GROUP BY kun je groeperen: de rijen van een kolom worden ingedeeld in groepen, die dezelfde gegevens in een kolom hebben. Daar kun je dan een functie op toepassen, bijv. het aantal rijen tellen, of de getallen optellen. Voorbeeld: geef de plaatsen, waar leerlingen wonen, met het aantal leerlingen per plaats SELECT PLAATS, COUNT(*) FROM LEERLINGEN Het aantal per plaats wordt geteld GROUP BY PLAATS Sondervick College - Veldhoven

  25. SQL: HAVING Stel je bij het gebruik van GROUP BY een extra voorwaarde aan de uitkomst van de functie, dan doe je dat met HAVING Alleen de plaatsenwaar meer dan vijfleerlingen wonen worden afgedrukt SELECT PLAATS, COUNT(*) FROM LEERLINGEN GROUP BY PLAATS HAVING COUNT(*) > 5 Sondervick College - Veldhoven

  26. SQL: Exists Geef de leerlingen die nooit een boek geleend hebben De query, waarin je om de boeken vraagt die de leerling heeft geleend, moet geen resultaten opleveren! SELECT VOORNAAM, ACHTERNAAM Die zijn er dus niet! FROM LEERLINGEN WHERE NOT EXISTS ( SELECT * FROM UITLENINGEN WHERE UITLENINGEN.LLNR = LEERLINGEN.LLNR) Sondervick College - Veldhoven

  27. SQL-oefeningen Er komen nu een aantal vragen die betrekking hebben op een database waarin de administratie van rundveehouderijen wordt bijgehouden Bedenk na elke opgave zelf wat de sql-query zal zijn, druk dan op PageDown of klik op de muis Sondervick College - Veldhoven

  28. Rundvee administratie: De tabellen en velden Kolommen of Velden Bedrijven Koeien Tabellen Jaarprod Sondervick College - Veldhoven

  29. SQL, de basis Geef naam, adres en plaats van het bedrijf met nummer 213 Select naam,adres,plaats From bedrijven Where bedrijfsnr=‘213’ ; Sondervick College - Veldhoven

  30. Alle kolommen Geef alle gegevens van de koeien van het bedrijf met nummer 213 gesorteerd op geboortedatum Select * From koeien Where bedrijfsnr=‘213’ Order by gebdat ; Sondervick College - Veldhoven

  31. Duplicaten voorkomen Geef een lijstje van alle plaatsen waar bedrijven zijn gevestigd (elke plaats maar één keer tonen) Select distinct plaats From bedrijven; Sondervick College - Veldhoven

  32. Samengestelde voorwaarde Geef de nummers van de koeien die in 2002 een melkproductie hadden van meer dan 8000 kg. Select koenr From jaarprod Where jaar=‘2002’ and kgmelk>8000; Sondervick College - Veldhoven

  33. Wildcards Geef naam, adres en plaats van alle bedrijven waarvan de postcode met 92 begint Select naam, adres, plaats From bedrijven Where postcode like ‘92*’ ; Sondervick College - Veldhoven

  34. De rijen tellen Geef het aantal koeien dat ze op het bedrijf met nummer 213 hebben Select count(*) From koeien Where bedrijfsnr=‘213’ ; Sondervick College - Veldhoven

  35. De som van alle getallen in een kolom Geef het totaal aantal kg. melk dat in 2002 is gemolken Select sum(kgmelk) From jaarprod Where jaar=‘2002’ ; Sondervick College - Veldhoven

  36. Verdeel in groepjes en tel de aantallen daarin Geef een lijstje van alle bedrijfsnummers met het aantal koeien op dat bedrijf Select bedrijfsnr, count(*) From koeien Group by bedrijfsnr; Sondervick College - Veldhoven

  37. Groeperen N.B.:achter group by en achter select altijd dezelfde kolomnamen zetten ! Geef een lijstje van alle koe-nummers met het aantal kg. melk dat ze in totaal hebben gegeven Select koenr, sum(kgmelk) From jaarprod Group by koenr; Sondervick College - Veldhoven

  38. Having Geef een lijstje van alle moeders die meer dan 10 kinderen hebben Select moeder, count(*) From koeien Group by moeder Having count(*)>10; Sondervick College - Veldhoven

  39. Een subquery Geef het nummer van de koe die in 2002 de grootste hoeveelheid melk gaf Select koenr from jaarprodwhere Kgmelk=(select max(kgmelk) from jaarprod where jaar=‘2002’) ; Sondervick College - Veldhoven

  40. Subquery met in Geef een lijstje van de koenummers van de koeien van bedrijven die gevestigd zijn in Oenkerk Select koenr from koeien Where bedrijfsnr in (select bedrijfsnr from bedrijven where plaats = ‘Oenkerk’) ; Sondervick College - Veldhoven

  41. Twee tabellen gebruiken Geef van koenr 213 de geboorte-datum en de naam en plaats van het bijbehorende bedrijf Select gebdatum,naam,plaats From bedrijven, koeien Where koenr=‘213’ and bedrijven.bedrijfsnr=koeien.bedrijfsnr; Sondervick College - Veldhoven

  42. Drie tabellen gebruiken Geef een lijstje met de nummers en de geboorte-data van de koeien en de naam en plaats van het bedrijf van de koeien die in 2002 meer dan 8000 kg. melk gaven. Select koeien.koenr,gebdat,naam,plaats From bedrijven, koeien, jaarprod Where kgmelk>8000 and jaar=‘2002’and bedrijven.bedrijfsnr=koeien.bedrijfsnr and koeien.koenr=jaarprod.koenr; Sondervick College - Veldhoven

  43. Exists en not exists Geef alle bedrijfsnrs van bedrijven die geen koeien hebben die meer dan 8000 kg. melk hebben gegeven Select distinct bedrijfsnr from koeien Where not exists (select koenr From jaarprod where kgmelk>8000 And jaarprod.koenr=koeien.koenr); Einde Sondervick College - Veldhoven

More Related