SELECT woonplaats, geslacht, count(*)

MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
HOGESCHOOL ROTTERDAM/CMI
Databases 1
(Databases en SQL)
INFDTB01
Goedgekeurd door:
Aantal studiepunten: 2 ects
Modulebeheerder: B. Joziasse
(namens toetscommissie)
Datum:
INFDTB01 15-6-2011
1
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
Modulebeschrijving:
Modulenaam:
Modulecode:
Aantal studiepunten
en
studiebelastinguren:
Vereiste voorkennis:
Werkvorm:
Toetsing:
Leermiddelen:
Draagt bij aan (HBOi) competentie:
Leerdoelen:
Inhoud:
Databases 1 (Inleiding databases, SQL)
INFDTB01
Dit studieonderdeel levert de student 2 studiepunten op, hetgeen overeenkomt
met een studielast van 56 uren. De verdeling van deze 56 uren over de
collegeweken is als volgt:
Begeleide colleges:
gedurende 8 weken: 8 * 120 minuten
16 uur
Begeleide uren practicum :
gedurende 8 weken: 8 * 120 minuten
16 uur
Zelfstudie en individuele opdrachten 8 weken: 8 * 180 minuten
24 uur
Totaal
56 uur
INFIAN01
College en practicum (5 weken)
Schriftelijk tentamen (gesloten boek), Practicumopdracht
Boek:
Auteur:
R.F. van der Lans
Titel:
Het SQL leerboek
Uitgever: Academic Service
ISBN:
90-395-2302-9
Deze module draagt bij tot het verwerven van de volgende competenties:
 Analyseren (niveau 1)
 Ontwerpen (niveau 1)
 Realiseren (niveau 1)


Begrijpen van de principes van relationele databases
Creëren van database-objecten (en de onderlinge relaties) aan de hand van
een gegevensmodel
 Beoordelen of een database structuur voldoet aan de BCNF normaalvorm
 Afleiden van een logische datastructuur uit de SQL definities van de
tabellen
 Invoeren van gegevens in een database m.b.v. SQL
 Opvragen van gegevens uit een database m.b.v. SQL
 Muteren van gegevens in een database m.b.v. SQL
 Kennen en kunnen omschrijven van de volgende begrippen:
 relatie, tabel, rij, kolom, record, veld, attribuut, domein, datatype, constraint,
selectie, vereniging, verschil, doorsnede, cartesisch product, natural join,
DDL, DML, DCL.
 Principes van relationele databases
 Implementeren van logisch ontwerp van een datastructuur in een
relationele database door de tabellen en de relaties tussen die tabellen te
creëren; een logische datastructuur kunnen afleiden uit de SQL definities
van de tabellen
 de SQL datatypen, keywords en commando's die in hoofdstuk 1 t/m 21
(behalve h. 19) van [van der Lans] worden behandeld kennen en kunnen
gebruiken om
o tabellen te definiëren [CREATE TABLE …]
o gegevens toe te voegen, te wijzigen, en te verwijderen [INSERT,
UPDATE, DELETE]
o gegevens uit een database op te vragen [SELECT]
o voorwaarden aan rijen stellen [WHERE]
o rijen kunnen groeperen op waarden van attributen met eventueel
aanvullende voorwaarden [GROUP BY, HAVING]
o gegevens uit meerdere tabellen kunnen combineren [alle JOIN
statements uit H7]
o een willekeurige combinatie van bovenstaande typen vragen met
behulp van SQL kunnen beantwoorden
Bovenstaande onderdelen van de taal SQL moeten kunnen worden toegepast in
databases van gemiddelde grootte, zoals een videotheek, CD winkel, autodealer
of dokterspraktijk.
Opmerkingen
INFDTB01 15-6-2011
2
MODULEWIJZER
Modulebeheerder:
Datum:
INFDTB01 15-6-2011
HOGESCHOOL ROTTERDAM /CMI
B. Joziasse, M.E.Prins-Wenink
November 2010
3
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
1. Inhoudsopgave
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Inleiding ................................................................................................................. 5
Leerdoelen ............................................................................................................ 6
Begineisen ............................................................................................................ 6
Leerstof ................................................................................................................. 6
Kwartaalschema .................................................................................................... 7
Verdere aanwijzingen, organisatorische informatie, en toetsing ............................ 8
Practicumopdracht ................................................................................................ 9
Opdrachtomschrijving ............................................................................................ 9
Inleiding ................................................................................................................. 9
Indeling .................................................................................................................. 9
Oplevering ............................................................................................................. 9
Voorbeeldtentamen ............................................................................................. 10
Hogeschool Rotterdam ........................................................................................ 10
Uitwerking oefententamen ................................................................................... 13
Zelfstudieopdrachten ........................................................................................... 16
INFDTB01 15-6-2011
4
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
2. Inleiding
Wat zijn de titels van de LP’s / Cd's die door de Beatles zijn gemaakt?
Zijn alle videobanden van Titanic al uitgeleend ?
Hoeveel baby's zijn er vorig jaar geboren?
Welk merk en type auto werd vorige maand in Nederland het meest verkocht?
Wat is de gemiddelde leeftijd van een informatica student?
Welke Albert Heijn klant koopt daar geen aardappelen, maar wel groente?
Wat is de favoriete kleur van Ferrarikopers?
Hoeveel studiepunten heb ik dit jaar gehaald?
Hoeveel belminuten heb ik nog over op mijn mobiele telefoon?
In welke maand heb ik het meest gebeld?
Wat kost het als ik alle boeken van Tom Clancy op deze website bestel?
Deze vragen komen uit de dagelijkse praktijk, en iedereen heeft ooit wel eens zo’n vraag gehad. Elke
vraag is te beantwoorden, tenminste als de benodigde gegevens correct zijn opgeslagen in een database
en als je weet hoe je daar de correcte informatie uit moet halen.
Databases zijn niet meer weg te denken in ons dagelijks leven. We komen databases praktisch overal
tegen, bijvoorbeeld bij de supermarkt, de CD-winkel, de bank, het reisbureau, de verzekeringsmaatschappij, de gemeente, de studentenadministratie, de apotheker, de mobiele telefoon, enzovoort.
Als informatica-ingenieur bouw je informatiesystemen. De belangrijkste functie van deze systemen is het
opslaan en leveren van informatie. Die informatie wordt vrijwel altijd opgeslagen in een relationele
databases. Bij een relationele database wordt de vraagtaal SQL gebruikt om gegevensverzamelingen te
definiëren, gegevens in te voeren en / of te wijzigen en om gegevens op te vragen.
Het beheersen van SQL en het kunnen omgaan met gegevensverzamelingen behoort dus tot de
belangrijkste competenties van een informaticus.
In de database-industrie wordt enorm veel geld omgezet. Oracle, IBM, en Microsoft, de drie grootste
leveranciers van databases, behoren niet voor niets tot de allergrootste bedrijven ter wereld, met
miljardenomzetten.
Na het succesvol doorlopen van deze module moet de student voldoende kennis en vaardigheden bezitten
om zelfstandig of in teamverband te kunnen functioneren bij de ontwikkeling van relationele databaseapplicaties. Deze module vormt een belangrijke bijdrage bij het optimaal kunnen vervullen van functies als
gegevensbeheerder, Database Administrator (DBA), database ontwerper, software engineer en applicatie
programmeur.
Deze module is een vervolg op de module INFIAN01, waarin het opzetten van een datastructuur wordt
behandeld. Na deze module is er nog DTB02, waarin wordt ingegaan op de problemen rond multi-userdatabases en data-integriteit.
Verder bevat deze module noodzakelijke voorkennis voor diverse hogerejaars-vakken waarin complete
informatiesystemen dienen te worden gerealiseerd.
In deze modulewijzer is een oefententamen opgenomen. Deze geeft het niveau en de vorm aan waarop
getoetst wordt voor het theoretische deel van deze module.
Verder bevat deze modulewijzer opdrachten die je mogelijk als extra oefening zelfstandig uit kunt voeren.
INFDTB01 15-6-2011
5
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
3. Leerdoelen
Zie blz. 2.
4. Begineisen
Voor deze module is het noodzakelijk om de module INFIAN01 afgerond te hebben. Hierin wordt het
opzetten van een datastructuur behandeld. Deze kennis is vereist om een goede database te kunnen
opzetten met SQL.
Er is geen voorkennis nodig van de taal SQL. Voor studenten met een behoorlijke voorkennis van SQL
(met minstens goed gebruik van geneste queries, het GROUP BY commando en JOINs) is er de
mogelijkheid om vrijstelling te krijgen voor deze module. Neem hiervoor contact op met de docent,
raadpleeg hiervoor hoofdstuk “organisatorische informatie”.
5. Leerstof
Verplicht boek:
Auteur:
Titel:
Uitgever:
ISBN:
R.F. van der Lans
Het SQL leerboek (6e geheel herziene druk)
Academic Service
90-395-2302-9
[van der Lans] wordt gebruikt bij de modules DTB01 en DTB02, en wordt aanbevolen bij de modules
PROJ7, PROJ8 en MULT3
Aanbevolen literatuur:
Als u meer wilt weten over het realiseren van wat grotere database applicaties, dan is dit een geschikt
boek:
Auteur
Titel
Uitgever
ISBN
:
:
:
:
T. Connolly
Database Systems, A practical Approach to Design, Implementation and Management
Addison Wesley
0 201 42277 8
Een standaardwerk uit de VS over dit onderwerp, van een van de belangrijkste personen uit dit vakgebied
is:
Auteur
Titel
Uitgever
ISBN
:
:
:
:
INFDTB01 15-6-2011
C.J. Date
An introduction to database systems, 7th edition
Addison Wesley
0 201 385902
6
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
6. Kwartaalschema
In dit hoofdstuk staat een beknopte omschrijving van de onderwerpen per week. In het volgende hoofdstuk
volgt een gedetailleerde beschrijving van de kennis en vaardigheden die moet zijn opgedaan tijdens een
lesweek.
Toelichting:
TH = theorie, in de vorm van hoorcollege en werkcollege, ZS = zelfstudie, PR = computerpracticum
week
1
15/11
19/11
werk- sbu inhoud
vorm
TH
2
Wat is een relationele database, wat is SQL?
Tabellen, rijen, kolommen, domeinen, waarden, primaire sleutels, constraints.
Verzamelingen en relationele algebra (vereniging, doorsnede, verschil, projectie,
selectie). CREATE, INSERT en SELECT statements.
ZS
4
PR
2
2
TH
2
Het SELECT statement uitgebreid (zonder GROUP BY).
De natural join. Referentiële integriteit. Database ontwerp en normaliseren.
22/11
26/11
ZS
4
Bestudeer de behandelde leerstof en de hoofdstukken 6, 7, 8, 16, 17 en 18 uit het boek.
Maak opdrachten uit die hoofdstukken.
PR
2
3
TH
2
29/11
03/12
ZS
4
PR
2
4
TH
2
06/12
10/12
ZS
4
Bestudeer de behandelde leerstof en de hoofdstukken 20 en 21 uit het boek.
Maak opdrachten uit die hoofdstukken.
PR
2
5
TH
2
13/12
17/12
Inleveren uitwerkingen week 3
Uitwerken case t.b.v. eindopdracht
Cartesisch product, natural join, andere typen joins.
SELECT met subquery. IN, EXISTS, ANY, ALL.
Subqueries vs. joins. INSERT. DELETE. UPDATE
ZS
4
PR
2
INFDTB01 15-6-2011
Bestudeer de modulewijzer.
Bestudeer de behandelde leerstof en de hoofdstukken 1, 2, 4, 5 en 12 uit het boek.
Maak opdrachten uit die hoofdstukken.
Extra oefening: opgaven 9.1 en 9.2 uit de modulewijzer.
Uitwerken case t.b.v. eindopdracht
Extra oefening: opgaven 9.3 en 9.4 uit de modulewijzer.
Inleveren uitwerkingen week 1
Uitwerken case t.b.v. eindopdracht
SET functies (COUNT, MIN, MAX, SUM, AVG, STDEV).
GROUP BY en HAVING.
Implementatie van 1:N en N:M relaties.
Bestudeer de behandelde leerstof en de hoofdstukken 9, 10, 11, en 17 uit het boek.
Maak opdrachten uit die hoofdstukken.
Extra oefening: opgaven 9.5 en 9.6 uit de modulewijzer
Inleveren uitwerkingen week 2
Uitwerken case t.b.v. eindopdracht
GROUP BY op meer kolommen. VIEWs.
Bestudeer de behandelde leerstof en de hoofdstukken 13, 14 en 15 uit het boek.
Maak opdrachten uit die hoofdstukken.
Inleveren uitwerkingen week 4
Uitwerken case t.b.v. eindopdracht.
7
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
7. Verdere aanwijzingen, organisatorische informatie, en
toetsing
Het verkrijgen van vrijstelling voor deze module is alleen mogelijk na overleg met de docent in de eerste
lesweek van de module.
Deze module wordt gegeven in een kwartaal van 10 weken met 5 weken contacttijd, waarbij er per week 4
contacturen met de docent zijn. Daarvan zijn de 2 uren computerpracticum verplicht (tenminste 80%).
Indien niet aan deze aanwezigheidsplicht is voldaan, moeten aanvullende opdrachten worden ingeleverd!
Het practicumverslag moet uiterlijk aan het eind van week 5 zijn ingeleverd.
Bij te laat inleveren van het verslag wordt er 1 punt van het practicumcijfer afgetrokken.
Bij de beoordeling van het practicum worden de verschillende onderdelen volgens onderstaand schema
gewogen:
 Weekopdrachten uit het practicum: 10% per opdracht
 Opleveren database: 50%
De theorie wordt getoetst dmv een schriftelijk tentamen.
Het tentamen valt in de eerstkomende tentamenperiode.
Tijdens dit tentamen mogen geen boeken of aantekeningen worden gebruikt.
Het eindcijfer voor deze module wordt als volgt bepaald uit het tentamencijfer (t) en het practicumcijfer (p):
-
als ( t>=5.5 én p>=5.5 )
als ( t<3.5 óf p<3.5 )
anders
eindcijfer = het gemiddelde van p en t
eindcijfer = het gemiddelde van p en t, maar nooit hoger dan 3
eindcijfer = het gemiddelde van p en t, maar nooit hoger dan 5
In deze modulewijzers is een voorbeeldtentamen (inclusief uitwerking) opgenomen.
INFDTB01 15-6-2011
8
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
8. Practicumopdracht
Opdrachtomschrijving
Ontwerp en implementeer in een groepje van maximaal 2 personen een informatiesysteem.
Inleiding
Een beginnende Dierentuin heeft een informatiesysteem nodig in de vorm van een database. In de huidige
situatie worden de gegevens bijgehouden in een Excel-sheet, maar in verband met diverse toekomstplannen
wil de dierentuin overstappen op een betere onderhoudbare informatievoorziening, een relationele database.
Deze database moet minimaal zorgdragen voor: opslag van belangrijke gegevens en het genereren van
rapportages.
Indeling
Per week wordt een deel van de case uitgewerkt. Dit gebeurt aan de hand van weekopdrachten. Dit kunnen
extra informatie aanvragen zijn of verzoeken om extra gegevens toe te voegen aan de database. De case
krijgt zo steeds meer vorm en wordt aan het eind van de module ingeleverd.
Oplevering
Per week wordt een deelopdracht ingeleverd in de vorm van twee onderdelen;
 De gevraagde informatie (rapportage van de informatie)
 De methode van het ophalen van de informatie (SQL + ERD).
Aan het eind van de module wordt de werkende database opgeleverd in de vorm van een ERD, CREATE
statements en de bijbehorende SELECT, INSERT, DELETE en UPDATE statements.
Het opleveren van de opdrachten gebeurd in elke practicum week en vormt samen met de database in totaal
50% van het eindcijfer.
INFDTB01 15-6-2011
9
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
9. Voorbeeldtentamen
Hogeschool Rotterdam
Datum
Module
infDtb01-theorie
Studierichting
HIO / TLM
Aantal pagina's
2
Aantal opgaven
7
geen
Hulpmiddelen
Eindcijfer = 1 + aantal behaalde punten / 60
Tijd
Duur
90 minuten
Lokaal
Docent
Opgave
Lees de vragen en de inleiding van de vragen goed door. Verdeel je tijd goed : besteed in eerste instantie
niet te veel tijd aan de moeilijke vraag 6. Succes !
Inleiding bij vragen 1 t/m 6
Een leesclubje houdt in een database de beoordeling van gelezen boeken bij.
De tabellen in de database zijn gemaakt door onderstaande SQL statements uit te voeren.
create table auteurs(
auteurnr integer not null,
naam varchar,
geb_datum date,
primary key(auteurnr)
);
auteurs
auteurnr
create table boeken(
boeknr integer not null,
auteurnr integer not null,
titel varchar,
prijs float,
primary key(boeknr)
);
boeken
boeknr
create table gelezen_boeken(
boeknr integer not null,
lezernr integer not null,
cijfer float,
opmerkingen varchar,
primary key(boeknr,lezernr)
);
create table lezers(
lezernr integer not null,
naam varchar,
geslacht char,
geb_datum date,
woonplaats varchar,
primary key(lezernr)
);
INFDTB01 15-6-2011
auteurnr
gelezen_boeken
boeknr
lezernr
lezers
lezernr
naam
naam
titel
cijfer
geb_datum geslacht
geb_datum
prijs
opmerkingen
woonplaats
10
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
1. (2p)
Geef het SQL statement om een overzicht van de lezers te maken met naam, geslacht en
geboortedatum. Sorteer op naam.
2. (2p)
Geef het SQL statement om een overzicht te maken van de vrouwelijke lezers die geboren zijn tussen 1
januari 1975 en 1 januari 1985. Geef naam en leeftijd. Sorteer op leeftijd.
TIP:
De leeftijd van een lezer kan worden berekend met TIMESTAMPDIFF(8,geb_datum,curdate()).
Om je schrijfwerk te besparen mag je in de rest van het tentamen het woord ‘leeftijd’ gebruiken als
vervanging van ‘TIMESTAMPDIFF(8,geb_datum,curdate())’.
3. (6p)
Geef de SQL statements om te berekenen:
a. het aantal mannelijke lezers
b. de leeftijd van de jongste mannelijke lezer
c.
4. (12p)
de gemiddelde leeftijd van de vrouwelijke lezers
Geef het SQL statement om een overzicht te maken van:
a. het aantal lezers per woonplaats
b. het aantal lezers per woonplaats, uitgesplitst naar geslacht
c.
het aantal lezers en de gemiddelde leeftijd van de lezers per woonplaats, uitgesplitst naar geslacht.
d. het aantal lezers en de gemiddelde leeftijd van de lezers per woonplaats, maar alleen als er meer dan
één lezer in die woonplaats woont
5. (9p)
Geef het SQL statement om een overzicht te maken van:
a. gelezen boeken; vermeld boeknummer, beoordeling, en naam van de lezer
b. gelezen boeken; vermeld auteurnr, titel, en de naam van de lezer
c.
6. (14p)
gelezen boeken; vermeld naam van de auteur, titel, en de naam van de lezer
Geef het SQL statement om een overzicht te maken van:
a. het gemiddelde cijfer van de boeken die door minstens twee lezers zijn gelezen; vermeld naam vd
auteur, titel van het boek, en het gemiddelde cijfer
b. vervallen vraag
c.
het gemiddelde cijfer van een auteur mits deze auteur minstens 3 keer beoordeeld is;
INFDTB01 15-6-2011
11
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
7. (15p) Het bedrijf KinderPret organiseert uitstapjes voor kinderen. KinderPret gebruikt een database-applicatie,
in de eerste plaats om de reserveringen bij te houden. Onderstaand rapport is één van de overzichten
die de database applicatie kan maken.
klant
Bakker
Vlas
Voeten
Jansen
Pietersen
de Wit
Hazebroek
Stolk
attractie
Blijdorp
Efteling
Madurodam
Drievliet
Hellendoorn
Duinrell
Plaswijck
Blijdorp
datum
19-1-02
12-1-02
5-1-02
12-1-02
5-1-02
12-1-02
12-1-02
19-1-02
aantal_kinderen
4
5
6
4
5
6
5
6
Begeleider
Joke
Els
Janneke
Marjoes
Hans
Joke
Els
Marjoes
Elke begeleider zorgt zelf voor vervoer. Een uitstapje duurt de hele dag. Dat betekent dat een begeleider
maar één uitstapje per dag kan verzorgen. Klanten kunnen uiteraard meerdere uitstapjes hebben
gereserveerd. Het is geen bezwaar als verschillende begeleiders op dezelfde dag dezelfde attractie
bezoeken.
Ontwerp de genormaliseerde tabellen waarop dit rapport is gebaseerd. Zorg ervoor dat de tabellen zo
gedefinieerd zijn dat dubbele boekingen niet kunnen voorkomen, en dat het verwijderen van een
begeleider en/of klant wordt tegengehouden als er nog uitstapjes door/voor hem/haar zijn gepland.
Geef de SQL statements om de tabellen aan te maken.
INFDTB01 15-6-2011
12
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
10. Uitwerking oefententamen
1. (2p)
Geef het SQL statement om een overzicht van de lezers te maken met naam, geslacht en
geboortedatum. Sorteer op naam.
SELECT naam, geslacht, geb_datum
FROM lezers
ORDER BY naam;
2. (2p)
Geef het SQL statement om een overzicht te maken van de vrouwelijke lezers die geboren zijn tussen 1
januari 1975 en 1 januari 1985. Geef naam en leeftijd. Sorteer op leeftijd.
TIP:
De leeftijd van een lezer kan worden berekend met TIMESTAMPDIFF(8,geb_datum,curdate()).
Om je schrijfwerk te besparen mag je in de rest van het tentamen het woord ‘leeftijd’ gebruiken als
vervanging van ‘TIMESTAMPDIFF(8,geb_datum,curdate())’.
SELECT naam, TIMESTAMPDIFF(8,curdate(),geb_datum) as leeftijd
FROM lezers
WHERE geslacht='V'
AND geb_datum BETWEEN '1975-01-01' AND '1985-01-01'
ORDER BY leeftijd;
3. (6p)
Geef de SQL statements om te berekenen:
a. het aantal mannelijke lezers
SELECT count(*)
FROM lezers
WHERE geslacht='M';
b. de leeftijd van de jongste mannelijke lezer
SELECT MIN(TIMESTAMPDIFF(8,curdate(),geb_datum))
FROM lezers
WHERE geslacht='M';
c.
de gemiddelde leeftijd van de vrouwelijke lezers
SELECT AVG(TIMESTAMPDIFF(8,curdate(),geb_datum))
FROM lezers
WHERE geslacht='V';
4. (12p)
Geef het SQL statement om een overzicht te maken van:
a. het aantal lezers per woonplaats
SELECT woonplaats, count(*)
FROM lezers
GROUP BY woonplaats;
b. het aantal lezers per woonplaats, uitgesplitst naar geslacht
SELECT woonplaats, geslacht, count(*)
FROM lezers
GROUP BY woonplaats, geslacht;
c.
het aantal lezers en de gemiddelde leeftijd van de lezers per woonplaats, uitgesplitst naar geslacht.
INFDTB01 15-6-2011
13
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
SELECT woonplaats, geslacht, count(*),AVG(TIMESTAMPDIFF(8,curdate(),geb_datum))
FROM lezers
GROUP BY woonplaats, geslacht;
d. het aantal lezers en de gemiddelde leeftijd van de lezers per woonplaats, maar alleen als er meer dan
één lezer in die woonplaats woont
SELECT woonplaats, count(*),AVG(TIMESTAMPDIFF(8,curdate(),geb_datum))
FROM lezers
GROUP BY woonplaats
HAVING count(*)>1;
5. (9p)
Geef het SQL statement om een overzicht te maken van:
a. gelezen boeken; vermeld boeknummer, beoordeling, en naam van de lezer
SELECT boeknr, cijfer, naam
FROM gelezen_boeken NATURAL JOIN lezers;
b. gelezen boeken; vermeld auteurnr, titel, en de naam van de lezer
SELECT auteurnr, titel,naam
FROM boeken NATURAL JOIN gelezen_boeken NATURAL JOIN lezers ;
c.
gelezen boeken; vermeld naam vd auteur, titel, en de naam van de lezer
SELECT auteurs.naam, titel, lezers.naam
FROM auteurs, boeken, gelezen_boeken, lezers
WHERE auteurs.auteurnr=boeken.auteurnr
AND gelezen_boeken.lezernr=lezers.lezernr
AND gelezen_boeken.boeknr=boeken.boeknr;
6. (14p)
Geef het SQL statement om een overzicht te maken van:
a. het gemiddelde cijfer van de boeken die door minstens twee lezers zijn gelezen; vermeld naam vd
auteur, titel van het boek, en het gemiddelde cijfer
SELECT naam, titel, AVG(cijfer)
FROM gelezen_boeken NATURAL JOIN boeken NATURAL JOIN auteurs
GROUP BY boeknr,naam,titel
HAVING count(*)>=2;
b. het hoogste cijfer van de gelezen boeken per woonplaats van de lezers; vermeld naam vd auteur, titel,
woonplaats van de lezer, en hoogste cijfer
vervallen vraag
c.
het gemiddelde cijfer van een auteur mits deze auteur minstens 3 keer beoordeeld is;
SELECT auteurs.naam, AVG(cijfer)
FROM auteurs,boeken,gelezen_boeken
WHERE auteurs.auteurnr=boeken.auteurnr
AND gelezen_boeken.boeknr=boeken.boeknr
GROUP BY auteurs.naam,auteurs.auteurnr
HAVING count(*) >=3 ;
INFDTB01 15-6-2011
14
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
7. (15p) Het bedrijf KinderPret organiseert uitstapjes voor kinderen. KinderPret gebruikt een database-applicatie,
in de eerste plaats om de reserveringen bij te houden. Onderstaand rapport is één van de overzichten
die de database applicatie kan maken.
klant
Bakker
Vlas
Voeten
Jansen
Pietersen
de Wit
Hazebroek
Stolk
attractie
Blijdorp
Efteling
Madurodam
Drievliet
Hellendoorn
Duinrell
Plaswijck
Blijdorp
datum
19-1-02
12-1-02
5-1-02
12-1-02
5-1-02
12-1-02
12-1-02
19-1-02
aantal_kinderen
4
5
6
4
5
6
5
6
Begeleider
Joke
Els
Janneke
Marjoes
Hans
Joke
Els
Marjoes
Elke begeleider zorgt zelf voor vervoer. Een uitstapje duurt de hele dag. Dat betekent dat een begeleider
maar één uitstapje per dag kan verzorgen. Klanten kunnen uiteraard meerdere uitstapjes hebben
gereserveerd. Het is geen bezwaar als verschillende begeleiders op dezelfde dag dezelfde attractie
bezoeken.
Ontwerp de genormaliseerde tabellen waarop dit rapport is gebaseerd. Zorg ervoor dat de tabellen zo
gedefinieerd zijn dat dubbele boekingen niet kunnen voorkomen, en dat het verwijderen van een
begeleider en / of klant wordt tegengehouden als er nog uitstapjes door / voor hem / haar zijn gepland.
Geef de SQL statements om de tabellen aan te maken.
CREATE TABLE klanten(
klantnr integer not null,
naam varchar,
telnr char(11),
straat varchar,
huisnr varchar,
postcode varchar,
woonplaats varchar,
PRIMARY KEY(klantnr));
CREATE TABLE attracties(
attractienr integer not null,
naam varchar,
telnr char(11),
straat varchar,
huisnr varchar,
postcode varchar,
woonplaats varchar,
PRIMARY KEY(attractienr));
CREATE TABLE begeleiders(
begeleidernr integer not null,
naam varchar,
telnr char(11),
PRIMARY KEY(begeleidernr));
CREATE TABLE reserveringen(
klantnr integer not null,
begeleidernr integer not null,
attractienr integer not null,
datum date not null,
aantal_kinderen integer,
PRIMARY KEY(datum,begeleidernr),
FOREIGN KEY (klantnr) REFERENCES klanten,
FOREIGN KEY (begeleidernr) REFERENCES begeleiders,
FOREIGN KEY (attractienr) REFERENCES attracties);
INFDTB01 15-6-2011
15
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
11. Zelfstudieopdrachten
De artikelen - leveranciers database:
leveranciers : L(LNR,LNAAM,STATUS,STAD)
LNR
L1
L2
L3
L4
L5
LNAAM
Smith
Chirac
Boulanger
Blair
Papandreou
STATUS
20
10
30
20
30
STAD
Londen
Parijs
Parijs
Londen
Athene
artikelen: A(ANR,ANAAM,KLEUR,GEWICHT,STAD)
ANR
A1
A2
A3
A4
A5
A6
ANAAM
moer
bout
schroef
schroef
kamrad
nok
KLEUR
rood
groen
blauw
rood
blauw
groen
GEWICHT
12
17
17
14
12
19
STAD
Londen
Parijs
Rome
Londen
Parijs
Londen
leverbare artikelen: LA(LNR,ANR,AANTAL)
LNR
L1
L1
L1
L1
L1
L1
L2
L2
L3
L4
L4
L4
ANR
A1
A2
A3
A4
A5
A6
A1
A2
A3
A4
A5
A6
AANTAL
300
200
400
200
100
100
300
400
200
200
300
400
9.1 Geef het SQL commando voor onderstaande opdrachten:
a)
b)
c)
d)
e)
f)
geef de nummers en namen van alle artikelen
geef de nummers en namen van alle leveranciers
geef de nummers en namen van alle rode artikelen
geef de nummers en namen van alle artikelen met een gewicht van 17 kg
geef de nummers en namen van alle leveranciers met status 20
geef de nummers van leverbare artikelen
9.2 Geef het SQL commando voor onderstaande opdrachten:
Geef het SQL commando voor onderstaande opdrachten:
a) geef de artikelnummers van alle rode schroeven
b) geef de nummers van leveranciers in Londen met status kleiner dan 20
c) geef nummer en naam van de alle leveranciers uit Londen en Parijs
INFDTB01 15-6-2011
16
MODULEWIJZER
HOGESCHOOL ROTTERDAM /CMI
d) geef de nummers van artikelen die door leverancier L2 geleverd kunnen worden
9.3 Geef het SQL commando voor onderstaande opdrachten:
Geef het SQL commando voor onderstaande opdrachten:
a) geef een lijst van namen van alle leveranciers die artikelen leveren
b) geef een lijst van namen van alle artikelen die leverbaar zijn
c) geef een lijst van namen van alle leveranciers die artikelnummer A4 kunnen leveren
d) geef een lijst van namen van alle artikelen die door leverancier L2 geleverd worden
9.4 Geef het SQL commando voor onderstaande opdrachten:
Geef het SQL commando voor onderstaande opdrachten:
a) geef een lijst van namen van alle leveranciers die het artikelen ‘moer’ leveren
b) geef een lijst van namen van alle artikelen die door leverancier Smit geleverd worden
9.5 Geef het SQL commando voor onderstaande opdrachten:
Geef het SQL commando voor onderstaande opdrachten:
c) geef het aantal verschillende artikelen
d) geef het aantal verschillende leverbare artikelen
e) geef het aantal leveranciers dat artikel A5 kan leveren
f) geef het aantal leveranciers dat artikel ‘kamrad’ kan leveren
g) geef de hoeveelheid van het artikel A5 die geleverd kan worden
h) geef de hoeveelheid van het artikel ‘kamrad’ die geleverd kan worden
i) geef het artikelnummer met het laagste gewicht
j) geef de naam van de leverancier met de hoogste status
9.6 Geef het SQL commando voor onderstaande opdrachten:
Geef het SQL commando voor onderstaande opdrachten:
a) geef de leveranciernummers die meer dan 3 verschillende artikelen kunnen leveren
b) geef de nummers van de artikelen die door meer dan 2 leveranciers geleverd worden
c) geef de totale hoeveelheid die van artikel A1 geleverd kan worden
d) geef het aantal verschillende artikelen die leverancier L1 kan leveren
e) geef een lijst met artikelnummers waarvan er meer dan 200 leverbaar zijn
INFDTB01 15-6-2011
17