Praktijk 1 SQL - Relationele databases en SQL

Diagnostische toets - praktijk 1 SQL-DML - Relationele databases en SQL
De volgende opdrachten gaan over de database van verzekeringsmaatschappij SAVE.
Korte beschrijving van de database: De tabel VERZEKERDE bevat de persoonsgegevens van mensen die bij de
verzekeringsmaatschappij SAVE zijn verzekerd.
Het veld bankreknr is numeriek, de overige velden zijn allemaal tekstvelden (alfanumeriek).
De tabel POLIS bevat alle mogelijke polissen die klanten bij SAVE kunnen afsluiten.
Het veld jaarpremie is numeriek, de overige velden zijn allemaal tekstvelden.
In het veld datum_lw staat wanneer de jaarpremie van deze polis voor het laatst is gewijzigd.
De tabel VER_POL tenslotte bevat per afgesloten polis één record. Dit betekent dat een klant die meerdere
polissen heeft lopen, meerdere keren in deze tabel voorkom.
Strokendiagram en voorbeeldtabellen gegevensbank SAVE:
klantnr
naam
klantnr
polisnr
adres
woonplaats
postcode
verzekerde
ver_pol
polisnr
polis
ingangsdatum
jaarpremie
datum_lw
bankreknr
gebdatum
sekse
0 Een strokendiagram (zie boven) noemen we ook wel het relationele model van een database (zie Instruct
module 8 hoofdstuk 4). In het relationele model geven we de primaire sleutels aan door onderstreping en
de vreemde sleutels met hoekhaken. Neem de onderstaande beschrijving van het relationele model van de
database van SAVE over in je schrift en maak deze daarna verder af.
Verzekerde
( klantnr , naam , adres , woonplaats , postcode , bankreknr , gebdatum , sekse )
Ver_Pol
( klantnr , polisnr )
Polis
( polisnr , ingangsdatum , jaarpremie , datum_lw )
1a Hoe is een relationele database opgebouwd? (2 punten)
In een relationele database komen alle gegevens in tabellen te staan.
1b Waar staat de afkorting SQL voor en wat is SQL voor iets? (2 punten waarvan 1 voor de afkorting)
Sql= structured query language. Dit is een gestructureerde vraagtaal om gegevens in een database op te
vragen.
1c Wat wordt er bedoeld DML uit SQL-DML? (2 punten waarvan 1 voor de afkorting)
Staat niet in het lesmateriaal.
Aanwijzingen bij de volgende opdrachten
De volgende vragen en opdrachten maak je zonder computer. Pas nadat alle SQL opdrachten gemaakt zijn
kun je een en ander uitproberen met SOLID of ACCESS voor zover je nog tijd hebt. Bij de schoolexamens zal
dit onderdeel zowel theoretisch (schriftelijk en zonder computer) als praktisch (met de computer) getoetst
worden. Enkel de formulering van de SQL opdracht wordt in de beoordeling betrokken en niet het
antwoord op de vraag zelf.
Geef voor elk van de volgende vragen de bijbehorende SQL-opdracht en noteer die in het schrift:
2 Het aantal polissen dat bij SAVE kan worden afgesloten waarvan de jaarpremie hoger dan 2500 Euro is.
(4 punten)
Select count(*)
From polis
Where jaarpremie>2500
Group by polisnr
3 Van alle verzekerden de naam, het adres, en het aantal polissen dat ze hebben afgesloten bij SAVE; zorg
ervoor dat de verzekerde(n) met het grootste aantal polissen bovenaan wordt getoond en sorteer
vervolgens op naam.
(5 punten)
Select v.naam,v.adres,count(*)
From verzekerde as v,ver_pol as vp
Where vp.klantnr=v.klantnr
Group by naam, adres
4 De gegevens van de verzekerden die in Schiedam wonen en waarvan het bankrekeningnummer begint
met 25.
(5 punten)
Select *
From verzekerde
Where woonplaats=’schiedam’
And banreknr=’25%’
5 Het totale bedrag aan premies dat de verzekeringsmaatschappij per jaar kan innen.
(5 punten)
Select sum(jaarpremie)
From polis
6 Alle gegevens van de vrouwelijke verzekerden die in Amsterdam, Zwolle of Schiedam wonen en tevens
van de mannelijke verzekerden die een klantnummer hebben dat groter is dan K20000.
(5 punten)
Select *
From verzekerde
Where (
Sekse=’v’
And ( woonplaats=’Schiedam’
Or woonplaats=’Zwolle’
Or woonplaats=”Amsterdam’))
Or (sekse=’m’
And klantnr> 'K200000')
7 De gegevens van de verzekerden waarvan de achternaam met een ‘R’ begint, gesorteerd op deze naam.
(5 punten)
Select *
From verzekerde
Where naam=’R%’
Order by naam
8 Alle gegevens van de polis die het laatst gewijzigd is.
(5 punten)
Select p.ploisnr,v.klantnr,v.naam, max(datum_lw)
From polis as p, verzekerde as v,ver_pol as vp
Where p.polisnr=vp.polisnr
And vp.klantnr=v.klantnr
9 De verzekeringsmaatschappij wil weten in welke woonplaatsen verzekerden wonen. Maak hier een
alfabetische lijst van.
(3 punten)
Select distinct woonplaats
From verzekerde
10 Maak een lijst van de woonplaatsen en het aantal verzekerden per woonplaats gesorteerd op het aantal
verzekerden (van groot naar klein).
(4 punten)
Select distnct woonplaats,count(*) as aantal
From verzekerde
Order by aantal
opg
pnt
0
3
1a
2
1b
2
1c
2
2
4
3
5
4
5
5
5
6
5
7
5
8
5
Cijfer Praktijk 1 SQL (oefenpracticum) = aantal behaalde punten / 5 (afgerond op één decimaal).
9
3
10
4