Beste forumleden,
Ik moet voor maandag een SQL tentamen inleveren en wil het graag zo goed mogelijk maken.
Zouden jullie kijken of er eventueel verbeteringen zijn?
Alvast bedankt!
Deel B: SQL
Chalet- en appartementverhuur Alphûs
Alphûs treedt op als tussenpersoon voor de verhuur van vakantiehuisjes in de Alpen (Zwitserland, Oostenrijk, Frankrijk en Italië). De eigenaren van de huisjes besteden de verhuur uit aan Alphûs. Er wordt onderscheid gemaakt tussen twee soorten huisjes: chalets en appartementen.
De huurprijs van een huisje kan per week verschillen. Het huren van een huisje in de voorjaarsvakantie is bijvoorbeeld vele malen duurder dan in oktober. De huurprijzen per huisje per week zijn vastgelegd in de tabel huurperiode. Bij verhuur van een huisje wordt het huurdernr vastgelegd bij de betreffende huurperiode. Is bij een huurperiode geen huurdernr bekend, dan is het huisje in de betreffende week nog vrij.
Op de volgende bladzijde is het strokendiagram weergegeven.
Onderstreept = primaire sleutel
Cursief = verwijssleutel
op = optionele kolom
Eigenaar
Eigenaarnr Naam Adres Postcode Plaats Land Telefoon Email op Bankrekening
Huis
Huisnr Eigenaarnr Gebouwnaam Adres Plaats Land Soortcode AantalPersonen Oppervlakte
Land
Land
Soorthuis
Soortcode Omschrijving
Huurperiode
Huisnr Startdatum Weeknr Huurprijs Huurdernr op
Huurder
Huurdernr Naam Adres Postcode Plaats Telefoon Email op
Schrijf queries om de onderstaande vragen te beantwoorden of acties uit te voeren.
1. Geef van alle huurders hun naam en hun emailadres. Zorg ervoor dat huurders zonder emailadres niet op de lijst voorkomen.
SELECT naam, email
FROM Huurder
WHERE email IS NOT NULL
2. Maak een lijst van alle chalets (omschrijving van het soort huis is chalet) in Zwitserland die geschikt zijn voor 6 of meer personen. Orden de lijst op plaatsnaam en daarna op oppervlakte.
SELECT *
FROM Huis h
JOIN Soorthuis s ON h.Soortcode = s.Soortcode
WHERE h.Land = “Zwitserland”
AND s.Omschrijving = “chalet”
AND h.AantalPersonen = > 6
ORDER BY plaats, oppervlakte
3. Geef een overzicht van de verhuur in het jaar 2006 van huisje 331. Geef per huurperiode: weeknr, huurprijs en de naam van de huurder. Ook huurperiodes waarin het huisje niet is verhuurd, moeten in de lijst worden weergegeven. Toon in dit geval in plaats van de naam van de huurder de tekst ‘vrij’. Geef het overzicht een logische volgorde.
SELECT h.Weeknr, h.Huurprijs, e.Naam
NVL (e.naam, ‘vrij’)
FROM Huis h
JOIN Eigenaar e ON h.Eigenaarnr = e.Eigenaarnr
WHERE startdatum BETWEEN TO_DATE (01-01-2006) AND (01-01-2007)
AND h.Huisnr = ‘331’
4. Hoeveel huisjes verhuurt Alphûs in Frankrijk?
SELECT count(e.Eigenaarnr)
FROM Eigenaar e
JOIN Huis h ON e.Eigenaarnr = h.Eigenaarnr
WHERE e.Naam = "Alphûs"
AND h.Land = "Frankrijk"
5. Alphûs wil graag weten wat haar belangrijkste huurders zijn. Maak daarom een lijst met per huurder zijn naam en het aantal weken dat de huurder een huisje via Alphûs heeft gehuurd. Alleen huurders die minimaal 3 keer een huisje hebben gehuurd moeten in het overzicht weergegeven worden. Zet de huurders die het vaakst een huisje hebben gehuurd bovenaan de lijst.
6. Welk huisje is het grootst (heeft de grootste oppervlakte)? Geef huisnummer en oppervlakte.
SELECT huisnr, oppervlakte
FROM Huis
WHERE oppervlakte =
( SELECT max(oppervlakte)
FROM Huis)
7. Geef de huisjes (gebouwnaam, adres, plaats, land en naam van de eigenaar) waarvan de gebouwnaam begint met het woord ‘Alp’.
SELECT h.Gebouwnaam, e.Adres, e.Plaats, e.Land, e.Naam
FROM Huis h
JOIN Eigenaar e ON h.Eigenaarnr = e.Eigenaarnr
WHERE gebouwnaam LIKE “Alp%”
8. Geef voor alle eigenaren die in Amsterdam wonen het aantal huisjes weer dat ze via Alphûs verhuren.
SELECT count(e.Eigenaarnr)
FROM Eigenaar e
WHERE e.Naam = “Alphûs”
AND e.Plaats = “Amsterdam”
9. Een huurder wil graag een huisje huren in Meribel (Frankrijk). Hij wil graag weten welke huisjes de grootste oppervlakte per persoon hebben. Geef daarom van alle huisjes in Meribel de gebouwnaam, het aantal personen en de oppervlakte per persoon.
SELECT h.gebouwnaam, h.AantalPersonen, (‘h.Oppervlakte / h.AantalPersonen’)
FROM Huis h
JOIN Eigenaar e ON h.Eigenaarnr = e.Eigenaarnr
WHERE plaats = ‘Meribel’
10. Verwijder alle huurperiodes van vóór 1 januari 2005.
DELETE *
FROM Huurperiodes
WHERE DATE < 01-01-2005
Ik moet voor maandag een SQL tentamen inleveren en wil het graag zo goed mogelijk maken.
Zouden jullie kijken of er eventueel verbeteringen zijn?
Alvast bedankt!
Deel B: SQL
Chalet- en appartementverhuur Alphûs
Alphûs treedt op als tussenpersoon voor de verhuur van vakantiehuisjes in de Alpen (Zwitserland, Oostenrijk, Frankrijk en Italië). De eigenaren van de huisjes besteden de verhuur uit aan Alphûs. Er wordt onderscheid gemaakt tussen twee soorten huisjes: chalets en appartementen.
De huurprijs van een huisje kan per week verschillen. Het huren van een huisje in de voorjaarsvakantie is bijvoorbeeld vele malen duurder dan in oktober. De huurprijzen per huisje per week zijn vastgelegd in de tabel huurperiode. Bij verhuur van een huisje wordt het huurdernr vastgelegd bij de betreffende huurperiode. Is bij een huurperiode geen huurdernr bekend, dan is het huisje in de betreffende week nog vrij.
Op de volgende bladzijde is het strokendiagram weergegeven.
Onderstreept = primaire sleutel
Cursief = verwijssleutel
op = optionele kolom
Eigenaar
Eigenaarnr Naam Adres Postcode Plaats Land Telefoon Email op Bankrekening
Huis
Huisnr Eigenaarnr Gebouwnaam Adres Plaats Land Soortcode AantalPersonen Oppervlakte
Land
Land
Soorthuis
Soortcode Omschrijving
Huurperiode
Huisnr Startdatum Weeknr Huurprijs Huurdernr op
Huurder
Huurdernr Naam Adres Postcode Plaats Telefoon Email op
Schrijf queries om de onderstaande vragen te beantwoorden of acties uit te voeren.
1. Geef van alle huurders hun naam en hun emailadres. Zorg ervoor dat huurders zonder emailadres niet op de lijst voorkomen.
SELECT naam, email
FROM Huurder
WHERE email IS NOT NULL
2. Maak een lijst van alle chalets (omschrijving van het soort huis is chalet) in Zwitserland die geschikt zijn voor 6 of meer personen. Orden de lijst op plaatsnaam en daarna op oppervlakte.
SELECT *
FROM Huis h
JOIN Soorthuis s ON h.Soortcode = s.Soortcode
WHERE h.Land = “Zwitserland”
AND s.Omschrijving = “chalet”
AND h.AantalPersonen = > 6
ORDER BY plaats, oppervlakte
3. Geef een overzicht van de verhuur in het jaar 2006 van huisje 331. Geef per huurperiode: weeknr, huurprijs en de naam van de huurder. Ook huurperiodes waarin het huisje niet is verhuurd, moeten in de lijst worden weergegeven. Toon in dit geval in plaats van de naam van de huurder de tekst ‘vrij’. Geef het overzicht een logische volgorde.
SELECT h.Weeknr, h.Huurprijs, e.Naam
NVL (e.naam, ‘vrij’)
FROM Huis h
JOIN Eigenaar e ON h.Eigenaarnr = e.Eigenaarnr
WHERE startdatum BETWEEN TO_DATE (01-01-2006) AND (01-01-2007)
AND h.Huisnr = ‘331’
4. Hoeveel huisjes verhuurt Alphûs in Frankrijk?
SELECT count(e.Eigenaarnr)
FROM Eigenaar e
JOIN Huis h ON e.Eigenaarnr = h.Eigenaarnr
WHERE e.Naam = "Alphûs"
AND h.Land = "Frankrijk"
5. Alphûs wil graag weten wat haar belangrijkste huurders zijn. Maak daarom een lijst met per huurder zijn naam en het aantal weken dat de huurder een huisje via Alphûs heeft gehuurd. Alleen huurders die minimaal 3 keer een huisje hebben gehuurd moeten in het overzicht weergegeven worden. Zet de huurders die het vaakst een huisje hebben gehuurd bovenaan de lijst.
6. Welk huisje is het grootst (heeft de grootste oppervlakte)? Geef huisnummer en oppervlakte.
SELECT huisnr, oppervlakte
FROM Huis
WHERE oppervlakte =
( SELECT max(oppervlakte)
FROM Huis)
7. Geef de huisjes (gebouwnaam, adres, plaats, land en naam van de eigenaar) waarvan de gebouwnaam begint met het woord ‘Alp’.
SELECT h.Gebouwnaam, e.Adres, e.Plaats, e.Land, e.Naam
FROM Huis h
JOIN Eigenaar e ON h.Eigenaarnr = e.Eigenaarnr
WHERE gebouwnaam LIKE “Alp%”
8. Geef voor alle eigenaren die in Amsterdam wonen het aantal huisjes weer dat ze via Alphûs verhuren.
SELECT count(e.Eigenaarnr)
FROM Eigenaar e
WHERE e.Naam = “Alphûs”
AND e.Plaats = “Amsterdam”
9. Een huurder wil graag een huisje huren in Meribel (Frankrijk). Hij wil graag weten welke huisjes de grootste oppervlakte per persoon hebben. Geef daarom van alle huisjes in Meribel de gebouwnaam, het aantal personen en de oppervlakte per persoon.
SELECT h.gebouwnaam, h.AantalPersonen, (‘h.Oppervlakte / h.AantalPersonen’)
FROM Huis h
JOIN Eigenaar e ON h.Eigenaarnr = e.Eigenaarnr
WHERE plaats = ‘Meribel’
10. Verwijder alle huurperiodes van vóór 1 januari 2005.
DELETE *
FROM Huurperiodes
WHERE DATE < 01-01-2005
