SQL Flashcards
1
Q
Je wil een lijst met bieren waarbij de naam de tekst ‘ale’ bevat.
A
SELECT naam FROM bieren WHERE naam like ‘%ale%’
2
Q
Je wil een lijst met alle bieren met een alcoholpercentage tussen 5% en 7%.
A
SELECT naam FROM bieren WHERE alcohol between 5 and 7
3
Q
Je wil een lijst met alle bieren met 0%, 5% of 8% als alcoholpercentage.
A
SELECT naam FROM bieren WHERE alcohol in (0, 5, 8)
4
Q
Je wil een lijst van de brouwerijen uit Leuven, Genk, Antwerpen, Dendermonde en Wevelgem.
A
SELECT brnaam FROM brouwers
WHERE gemeente in (‘Leuven’, ‘Genk’, ‘Antwerpen’, ‘Dendermonde’, ‘Wevelgem’)
5
Q
Je wil een lijst met alle bieren waarbij het alcoholpercentage niet ingevuld is.
A
SELECT naam FROM bieren WHERE alcohol is Null
6
Q
Je wil een lijst met alle bieren waarbij in de naam het woord ‘wit’ voorkomt en met een
alcoholpercentage hoger dan 5.
A
SELECT * FROM bieren WHERE naam like ‘%wit%’ and alcohol > 5
7
Q
Je wil een lijst met alle bieren waarbij het woord ‘wit’ niet in de naam voorkomt.
A
SELECT * FROM bieren WHERE naam not like ‘%wit%’
8
Q
Je wil een alfabetische lijst van de bieren met hun alcoholpercentage.
A
SELECT naam, alcohol FROM bieren ORDER BY naam asc
9
Q
Je wil een alfabetische lijst van de bieren met hun alcoholpercentage.
A
SELECT naam, alcohol FROM bieren ORDER BY 1 asc
10
Q
Je wil een lijst met alle bieren (naam en alcoholpercentage). De bieren met het hoogste
alcoholpercentage staan bovenaan. Zorg ook voor een sortering op naam.
A
SELECT naam, alcohol FROM bieren ORDER BY alcohol desc, naam asc
11
Q
Geef een overzicht, met alle gegevens, van de leveranciers uit Aalsmeer.
A
select * from leveranciers where woonplaats=’aalsmeer’
12
Q
Geef een alfabetisch overzicht op plantennaam, met plantId, plantennaam en prijs, van alle planten.
A
select plantId, plantnaam, prijs from planten order by 2 asc
13
Q
Welke planten beginnen in de maand maart te bloeien?
Toon plantId, plantennaam, en de beginmaand van de bloeiperiode.
A
select plantId, plantnaam, beginbloeimaand from planten where beginbloeimaand = 3
14
Q
Maak een overzicht uit de table artikelsleveranciers, gesorteerd op plantId en binnen plantId een
sortering op artikelcode van de leverancier.
Toon alleen de gegevens plantId, artikelcode van de leverancier en leverancierId.
A
select plantId, artikelLeverancierCode, leverancierId from artikelsLeveranciers order by 1, 2
15
Q
Maak een gesorteerd overzicht van alle waterplanten. Sorteer op hoogte, grootste voorop.
A
select * from planten where soort=’water’ order by hoogte desc
16
Q
Maak een lijst van de verschillende kleuren die bij de planten uit de table planten horen.
A
select distinct kleur from planten order by kleur
17
Q
Maak een lijst van alle planten waarvan de kolom kleur niet ingevuld is.
A
select * from planten where kleur is null
18
Q
Toon de verschillende soorten planten in de table planten.
A
select distinct soort from planten
19
Q
Geef een overzicht van alle vaste planten met gele bloemen. Van iedere plant toon je de volgende
gegevens: plantId, plantennaam, hoogte en de beginmaand van de bloeiperiode.
A
select plantId, plantnaam, hoogte, beginbloeimaand from planten where kleur=’geel’ and soort=’vast’
20
Q
Geef een overzicht van alle planten met een prijs boven de 10 € die niet tot de soort bomen behoren.
Resultaat: 8 records
A
SELECT * FROM planten WHERE prijs > 10 and soort<>’boom’
21
Q
Maak een lijst van alle planten die in juni beginnen te bloeien en witte bloemen geven en tevens van alle planten die in augustus voor het eerst bloeien en gele bloemen hebben.
Plaats alle beschikbare gegevens in het overzicht.
A
SELECT * FROM planten WHERE (beginBloeiMaand=6 and kleur=’wit’) or (beginBloeiMaand=8 and kleur=’geel’)
22
Q
Welke planten met gemengde bloeikleuren worden maximum 60 cm hoog?
Toon plantId, plantennaam en hoogte.
Resultaat: 13 records
A
select plantId, plantnaam, hoogte from planten where kleur=’gemengd’ and hoogte <= 60
23
Q
Geef een overzicht van alle leveranciers die niet in Hillegom wonen.
Resultaat: 9 records
A
select * from leveranciers where woonplaats <> ‘hillegom’
24
Q
Van welke planten is de kleur onbekend? Geef plantId en plantennaam.
Resultaat: 11 records
A
select plantId, plantnaam from planten where kleur is null
25
Welke planten bloeien in ieder geval in de periode augustus tot en met oktober? Geef plantId en
plantennaam.
Resultaat: 6 records
select plantId, plantnaam from planten where beginBloeiMaand<=8 and eindBloeiMaand>=10
26
Welke planten bloeien in ieder geval in de maand september? Geef plantId en plantennaam.
Resultaat: 26 records
select plantId, plantnaam from planten where beginBloeiMaand<=9 and eindBloeiMaand>=9
27
Geef een overzicht van alle vaste planten met een prijs tussen 3 € en 5 €.
Resultaat: 17 records
select * from planten where soort='vast' and prijs between 3 and 5
28
Geef een overzicht van alle planten die in maart, april, september of oktober beginnen te bloeien.
Resultaat: 15 records
select * from planten where beginBloeiMaand in (3,4,9,10)
29
Van welke planten is zowel de kleur als de hoogte onbekend? Geef plantId, plantennaam, kleur en
hoogte.
Resultaat: geen records
select plantId, plantnaam, kleur, hoogte from planten where hoogte is null and kleur is null
30
Bij welke planten komt het woord BOOM voor? Geef plantId en plantennaam.
Resultaat: 5 records
select plantId, plantnaam from planten where plantnaam like '%boom%'
31
Geef plantId en plantennaam van alle planten die als derde letter een N hebben.
Resultaat: 11 records
select plantId, plantnaam from planten where plantnaam like '__n%'
32
Welke 1- en 2-jarige planten staan er in de table planten?
Probeer deze vraag met en zonder de operator LIKE op te lossen.
Resultaat: 23 records
select * from planten where soort like ('__jarig')!!! met like
select * from planten where soort in ('1-jarig', '2-jarig')!!! zonder like
33
Geef een overzicht van alle planten, behalve de bomen en de heesters, die tussen de 100 en 200 cm
hoog zijn, rode of blauwe bloemen geven, en vóór augustus beginnen te bloeien.
Alle gegevens behalve de prijs zijn belangrijk.
Sorteer de lijst op soort en binnen soort op plantennaam.
Resultaat: 4 records
```
select plantId, plantnaam, soort, kleur, hoogte, beginBloeiMaand, eindBloeiMaand from planten
where soort not in ('boom', 'heester')
and hoogte between 100 and 200
and kleur in ('rood','blauw')
and beginBloeiMaand <=8
order by soort, plantnaam
```
34
Bij welke planten, die niet behoren tot de soort kruid, komt het woord KRUID voor in hun
plantennaam?
Geef plantId en plantennaam.
Resultaat: 6 records
select plantId, plantNaam from planten
| where soort <> 'kruid' and plantnaam like '%kruid%'
35
Geef plantId en plantennaam van alle planten die beginnen met de letter L en eindigen met de letter
E.
Resultaat: 4 records
select plantId, plantNaam from planten where plantnaam like 'l%e'
36
Welke planten hebben een plantennaam van precies 5 letters lang? Geef plantId en plantennaam.
Resultaat: 7 records
select plantId, plantNaam from planten where plantnaam like '_____'
37
Welke planten hebben een plantennaam van minimum 5 letters lang? Geef plantId en plantennaam.
Resultaat: 111 records
select plantId, plantNaam from planten where plantnaam like '_____%'
38
Je wil het totaal aantal bieren kennen.
SELECT count(*) as aantal FROM bieren
39
Je wil het gemiddelde, het grootste, het kleinste en het totale alcoholpercentage uit de table bieren.
SELECT avg(alcohol) as gemiddeld, max(alcohol) as maximum, min(alcohol) as minimum,
sum(alcohol) as som
FROM bieren
40
Je wil het aantal tafelbieren (soortnr 51) kennen.
SELECT count(biernr) FROM bieren WHERE soortnr=51
41
Volgend voorbeeld kan je weer uittesten in de database Bieren.
Maak een lijst van alle brouwerijen met hun omzet in dollar en in yen.
SELECT brnaam, omzet*0.9118 as omzet_dollar, omzet*116.6 as omzet_yen
FROM brouwers
Resultaat: 118 records
42
Hoeveel leveranciers telt ons tuincentrum?
select count(*) as aantal from leveranciers
43
Geef de gemiddelde prijs van alle waterplanten.
select avg(prijs) as gemiddelde_prijs from planten where soort = 'water'
44
Geef de gemiddelde, de laagste en de hoogste offerteprijs van leverancier 4.
select avg(offerteprijs) as gemiddelde, max(offerteprijs) as hoogste, min(offerteprijs) as laagste from artikelsleveranciers where leverancierId=4
45
Wat is de maximale hoogte van de bomen in de table planten?
select max(hoogte) as maximale_hoogte from planten where soort = 'boom'
46
Wat is de laagste offerteprijs voor artikel 1?
select min(offerteprijs) as minimum from artikelsleveranciers where plantId=1
47
Geef bestelnummer, artikelLeverancierId en het totale bestelbedrag per bestelrij uit de table
bestellijnen.
Resultaat: 93 records
SELECT bestelId, artikelLeverancierId, (aantal*bestelPrijs) as totaal FROM bestellijnen
48
Maak een overzicht van de heesters uit de table planten, waarbij je de prijzen met 5% verhoogt.
Resultaat: 14 records
select plantId, plantnaam, (prijs*1.05) as verhoogd from planten where soort='heester'
49
Hoeveel stuks van leveranciersartikelnummer 59 zijn er besteld?
select sum(aantal) from bestellijnen where artikelLeverancierId=59
50
Wat is het totale bestelbedrag (exclusief korting) voor leveranciersartikelnummer 8?
SELECT sum(aantal*bestelPrijs) as totaal FROM bestellijnen WHERE artikelLeverancierId=8
51
Bereken het gemiddelde alcoholpercentage per brouwer.
SELECT brouwernr, avg(alcohol) as gem
FROM bieren
GROUP BY brouwernr
Resultaat: 113 records
52
Bereken het aantal bieren per biersoort.
SELECT soortnr, count(*) as aantal
FROM bieren
GROUP BY soortnr
53
Bepaal het minimum alcoholpercentage per brouwernr. De lijst toont enkel de brouwernr's en
percentages die kleiner zijn dan 5%.
Om dit op te lossen gebruiken we HAVING i.p.v. WHERE.
Je gebruikt having als de selectie gebaseerd is op het resultaat van een bewerking met een aggregate functie. In alle andere gevallen gebruik je WHERE.
SELECT brouwernr, min(alcohol) as mini
FROM bieren
GROUP BY brouwernr
HAVING min(alcohol)<5
54
Bereken het gemiddelde alcoholpercentage per brouwernr voor alle brouwers die meer dan 10
bieren produceren.
SELECT brouwernr, avg(alcohol) as gem
FROM bieren
GROUP BY brouwernr
HAVING count(*)>10
55
Tel het aantal planten per plantensoort uit de table planten.
Resultaat: 10 records
select soort, count(*) as aantal from planten group by soort
56
Tel het aantal bestelregels per bestelling.
Resultaat: 15 records
planten
SELECT bestelId, count(*) as aantal FROM bestellijnen GROUP BY bestelId
57
Wat is de gemiddelde prijs per plantensoort?
| Resultaat: 10 records
select soort, avg(prijs) as gemiddelde from planten group by soort
58
Hoeveel planten zijn er per plantensoort-kleurgroep?
| Resultaat: 54 records
select soort, kleur, count(*) as aantal from planten group by soort, kleur
59
Maak een overzicht zodat duidelijk is welke kleur van de vaste planten de hoogste gemiddelde prijs
heeft.
Resultaat: 10 records
select kleur, avg(prijs) as gemiddelde from planten where soort='vast' group by kleur order by 2 desc
60
Laat per leveranciersId het aantal artikelen zien dat de leverancier aanbiedt onder voorwaarde dat
de levertijd van het artikel minder dan 18 dagen is.
Resultaat: 10 records
select leverancierId, count(*) as aantal from artikelsleveranciers where levertijd < 18 group by leverancierId
61
Wat is de gemiddelde prijs per plantensoort, exclusief de geelbloemige planten?
Resultaat: 10 records
select soort, avg(prijs) as gemiddelde from planten where kleur <> 'geel' group by soort
62
Maak een overzicht met de laagste en de hoogste offerteprijs per plant.
Resultaat: 119 records
select plantId, min(offerteprijs) as min, max(offerteprijs) as max from artikelsleveranciers group by plantId order by plantId asc
63
Wat is de gemiddelde prijs per plantensoort voor soorten met minstens 10 exemplaren in de table
planten?
Resultaat: 5 records
select soort, avg(prijs) as gemiddelde from planten group by soort having count(*) >= 10
64
Hebben de planten met korte levertijden in het algemeen een hogere gemiddelde offerteprijs?
Resultaat: 4 records
select levertijd, avg(offerteprijs) as gemiddelde from artikelsLeveranciers group by levertijd
65
Maak een overzicht met de laagste en de hoogste bestelprijs per artikelLeverancierId.
Resultaat: 70 records
select artikelLeverancierId, min(bestelprijs) as laagste, max(bestelprijs) as hoogste
from bestellijnen
group by artikelLeverancierId
order by artikelLeverancierId
66
Geef een overzicht van het aantal beschikbare planten per beginBloeiMaand/hoogte/kleur groep?
Resultaat: 109 records
SELECT beginBloeiMaand, hoogte, kleur, count(*) as aantal
FROM planten
GROUP BY beginBloeiMaand, hoogte, kleur
67
Wat is de laagste prijs per plantensoort van de planten die in ieder geval bloeien in de periode mei tot en met juni?
Resultaat: 5 records
select soort, min(prijs) as laagste from planten
where beginBloeiMaand <=5 and eindBloeiMaand >=6
group by soort
68
Tel het aantal planten per prijs. De prijs moet je afronden op een geheel getal.
Resultaat: 17 records
select round(prijs,0) as afgeronde_prijs, count(*) as aantal
from planten
group by afgeronde_prijs
order by round(afgeronde_prijs,0)
69
Geef een lijst van de klanten uit Gent of Wetteren met de velden naam, voornaam, postcode,
woonplaats en klantstatus. Sorteer op klantstatus en naam.
Video
SELECT naam, voornaam, postcode, woonplaats, klantstatus FROM klanten
WHERE (woonplaats='wetteren') OR (woonplaats='gent')
ORDER BY klantstatus, naam
70
Maak een lijst van de klanten waarvan de postcode groter of gelijk is aan 9000 en de klanten
waarvan het huuraantal groter is dan 200. Deze lijst moet gesorteerd worden op postcode
video
SELECT naam, voornaam, postcode, woonplaats, totaalgehuurd FROM klanten
WHERE postcode >= 9000 AND totaalgehuurd > 200 ORDER BY postcode
71
Geef een lijst van de klanten wiens naam niet begint met een 'd'.
video
SELECT * FROM klanten WHERE naam NOT LIKE 'd%' ORDER BY naam
72
Geef een lijst van klanten waar in de naam van de gemeente op de derde plaats een 'n' staat.
video
SELECT * FROM klanten WHERE woonplaats LIKE '__n%'
73
Bereken voor alle films de prijs incl BTW (21%).
video
SELECT titel, prijs AS prijs_excl_BTW, (prijs*1.21) AS prijs_incl_BTW FROM films
74
Uit welke woonplaatsen komen onze klanten?
video
SELECT DISTINCT woonplaats FROM klanten ORDER BY woonplaats
75
Maak een lijst van het aantal klanten per gemeente.
video
SELECT woonplaats, count(*) AS aantal FROM klanten GROUP BY woonplaats ORDER BY 1
76
Maak een lijst die het mogelijk maakt een inzicht te krijgen in de verhuringen per gemeente.
Op deze lijst moet de gemeente/stad met de meeste verhuringen bovenaan staan.
Indien in een bepaalde gemeente geen 200 verhuringen gebeurd zijn, mag deze gemeente
niet op de lijst staan.
video
SELECT woonplaats, sum(totaalgehuurd) AS SumTotaalGehuurd FROM klanten GROUP BY woonplaats HAVING SumTotaalGehuurd >= 200 ORDER BY SumTotaalGehuurd DESC
77
We willen een lijst van alle bieren met daarbij de naam van de brouwerij.
We moeten de tables bieren en brouwers koppelen via het veld brouwernr. Hierdoor kunnen we de
naam van het bier en de naam van de bijhorende brouwer tonen.
SELECT naam, brnaam
FROM bieren INNER JOIN brouwers
ON bieren.brouwernr = brouwers.brouwernr
Resultaat: 1215 records
78
Geef een lijst van alle bieren met de velden naam, brouwerij en soort.
SELECT naam, brnaam, soort
FROM brouwers
INNER JOIN bieren ON bieren.brouwernr=brouwers.brouwernr
INNER JOIN soorten ON bieren.soortnr=soorten.soortnr
OF
SELECT naam, brnaam, soort
FROM soorten
INNER JOIN bieren ON soorten.soortnr = bieren.soortnr
INNER JOIN brouwers ON bieren.brouwernr = brouwers.brouwernr
Resultaat: 1215 records
79
In volgend voorbeeld worden de tables soorten en bieren gekoppeld op het veld soortnr. Deze instructie genereert een lijst van alle soorten, inclusief de soorten waarvan er geen bieren aanwezig zijn in de table bieren.
SELECT naam, soort
FROM soorten LEFT JOIN bieren
ON soorten.soortnr = bieren.soortnr
Resultaat: 1215 records
80
In dit voorbeeld worden de tables brouwers en bieren gekoppeld. De select-instructie genereert een lijst van alle brouwers, inclusief de brouwerijen waarvan er geen bieren aanwezig zijn in de table bieren.
```
SELECT brnaam, naam
FROM bieren RIGHT JOIN brouwers
ON bieren.brouwernr = brouwers.brouwernr
ORDER BY naam
Resultaat: 1220 records
```
81
Maak een lijst van brouwers die in dezelfde gemeente wonen.
Aan de table brouwers wordt telkens een aliasnaam gegeven. Als je een kolomnaam gebruikt, ben je verplicht om die aliasnaam te gebruiken.
SELECT b1.brnaam, b2.brnaam
FROM brouwers as b1 INNER JOIN brouwers as b2
ON b1.gemeente = b2.gemeente and b1.brouwernr < b2.brouwernr
b1.brouwernr < b2.brouwernr zorgt ervoor dat de combinaties zoals Artois – Artois en dubbele
records zoals Artois – Domus en Domus – Artois vermeden worden.
Resultaat: 10 records
82
Maak een overzicht van de bestellingen met het bestelnummer, de naam van de leverancier en de
leverdatum.
Planten
Resultaat: 15 records
SELECT bestelId, leverancierNaam, leveringsDatum FROM bestellingen
INNER JOIN leveranciers
ON bestellingen.leverancierId = leveranciers.leverancierId
83
Welke planten hebben een levertijd van maximum 10 dagen?
Plaats in het overzicht de gegevens artikelcode, leveranciers-artikelcode, plantennaam en levertijd.
Resultaat: 113 records
SELECT planten.plantId, artikelLeverancierCode, plantNaam, levertijd FROM artikelsleveranciers
INNER JOIN planten
ON artikelsleveranciers.plantId= planten.plantId
WHERE levertijd<=10
84
Geef een overzicht waaruit blijkt bij welke leverancier(s) de planten besteld kunnen worden.
In het overzicht moeten de volgende gegevens staan:
plantid, plantennaam, leverancierscode, naam van de leverancier, levertijd en offerteprijs.
Sorteer het overzicht op plantennaam.
Resultaat: 218 records
SELECT planten.plantId, plantNaam, leveranciers.leverancierId, leverancierNaam, levertijd, offerteprijs
FROM planten
INNER JOIN artikelsleveranciers ON planten.plantId=artikelsleveranciers.plantId
INNER JOIN leveranciers ON artikelsleveranciers.leverancierId = leveranciers.leverancierId
ORDER BY plantNaam
85
Maak een overzicht van die planten die een prijs hebben welke minstens 50% boven de offerteprijs ligt.
Geef plantId, plantennaam, prijs van de plant, leveranciersId en offerteprijs.
Sorteer je lijst op plantennaam.
Resultaat: 212 records
SELECT planten.plantId, plantNaam, prijs, leverancierId, offertePrijs
FROM planten
INNER JOIN artikelsleveranciers ON planten.plantId = artikelsleveranciers.plantId
WHERE prijs>=(offertePrijs*1.5)
ORDER BY plantNaam
86
Hoeveel verschillen de gehanteerde bestelprijzen met de huidige offerteprijzen?
Presenteer in de lijst de gegevens bestelnummer, artikelcode van de leverancier en het positieve of negatieve verschil.
Resultaat: 93 records
SELECT bestellijnen.bestelId, artikelLeverancierCode, bestelPrijs - offertePrijs as verschil
FROM artikelsleveranciers
INNER JOIN bestellijnen ON artikelsleveranciers.artikelLeverancierId = bestellijnen.artikelLeverancierId
87
In welke plaatsen kan het tuincentrum vaste planten bestellen?
Resultaat: 2 records
SELECT DISTINCT woonplaats
FROM leveranciers
INNER JOIN artikelsleveranciers ON leveranciers.leverancierId = artikelsleveranciers.leverancierId
INNER JOIN planten ON artikelsleveranciers.plantId=planten.plantId
WHERE soort='vast'
88
Maak een overzicht van alle rode planten geleverd door leveranciers die niet in Aalsmeer wonen.
Vermeld de gegevens plantId, plantennaam, soort, naam van de leverancier, woonplaats.
Sorteer op soort en plantennaam.
Resultaat: 10 records
SELECT planten.plantId, plantNaam, soort, leverancierNaam, woonplaats
FROM planten
INNER JOIN artikelsleveranciers ON planten.plantId = artikelsleveranciers.plantId
INNER JOIN leveranciers ON artikelsleveranciers.leverancierId = leveranciers.leverancierId
WHERE woonplaats<>'Aalsmeer' and kleur='rood'
ORDER BY soort, plantNaam
89
Bepaal voor iedere artikelcode de laagste offerteprijs.
Vermeld in het overzicht plantId, plantennaam en de bijbehorende offerteprijs.
Sorteer het overzicht op plantennaam.
Resultaat: 119 records
SELECT planten.plantId, plantNaam, min(offertePrijs) AS minimum
FROM planten INNER JOIN artikelsleveranciers
ON planten.plantId= artikelsleveranciers.plantId
GROUP BY plantNaam, planten.plantId
ORDER BY 2
90
Zoek de bestellingen welke een besteldatum hebben die gelijk is aan de leverdatum van één of meer andere bestellingen.
Geef bestelnummer 1e bestelling, besteldatum 1e bestelling, bestelnummer 2e bestelling, leveringsdatum 2e bestelling.
Resultaat: 2 records
SELECT e.bestelId as eerstenr, e.bestelDatum as eerstedatum, t.bestelId as tweedenr, t.leveringsDatum as tweededatum
FROM bestellingen e INNER JOIN bestellingen t
ON e.bestelDatum=t.leveringsDatum and e.bestelId<>t.bestelId
91
Geef een overzicht van alle leveranciers en de bijbehorende bestellingen. Toon ook de leveranciers bij wie we nog niets besteld hebben.
We zijn enkel geïnteresseerd in de velden bestelId en leverancierNaam.
Resultaat: 17 records
```
SELECT leveranciernaam, bestelid
FROM leveranciers
LEFT JOIN bestellingen
ON leveranciers.leverancierId = bestellingen.leverancierId
ORDER BY bestelId
```
92
Selecteer alle bieren met soortnr 3 of 5
SELECT * FROM bieren WHERE soortnr=3
UNION
SELECT * FROM bieren WHERE soortnr=5
Resultaat: 8 records
in praktijk
SELECT * FROM bieren WHERE soortnr in (3,5)
93
Je wil een lijst met alle bieren. Elk bier krijgt de vermelding 'Alcoholvrij', 'Alcoholarm', 'Bevat alcohol' of 'Niet gekend'.
De juiste boodschap kan je bepalen op basis van het alcoholpercentage (<0,2; <0,5; >0,5 en IS NULL).
Sorteer op alcoholpercentage.
SELECT naam, alcohol, 'Alcoholvrij' AS type FROM bieren WHERE alcohol<0.2
UNION
SELECT naam, alcohol, 'Alcoholarm' AS type FROM bieren WHERE alcohol>=0.2 and alcohol
<0.5
UNION
SELECT naam, alcohol, 'Bevat alcohol' AS type FROM bieren WHERE alcohol>=0.5
UNION
SELECT naam, alcohol, 'Niet gekend' AS type FROM bieren WHERE alcohol IS NULL
ORDER BY alcohol
94
Geef een lijst van alle bieren met het hoogste alcoholpercentage.
1 record
SELECT naam
FROM bieren
WHERE alcohol = (SELECT max(alcohol) FROM bieren)
In dit voorbeeld mag de subquery slechts één waarde als resultaat geven. Dit is zo voor de
operatoren =, , >=, <= en <>.
Resultaat: 1 record
95
Geef een lijst van alle bieren die in Oudenaarde gebrouwen worden.
SELECT naam
FROM bieren
WHERE brouwernr in (SELECT brouwernr FROM brouwers WHERE gemeente =
'Oudenaarde')
Alleen met de operator in kan je met een set van waarden werken.
Resultaat: 14 records
96
Geef de soortnr van de soorten die maar door 1 brouwerij gebrouwen worden.
SELECT soortnr
FROM (SELECT DISTINCT soortnr, brouwernr FROM bieren) as lijst
GROUP BY soortnr
HAVING count(*)=1
De subquery staat in de from-clausule. Eerst wordt de subquery uitgevoerd, nadien wordt de rest van de select-instructie uitgevoerd.
Als de substring in de from-component staat, moet deze een alias krijgen.
Resultaat: 5 records
97
Maak een lijst met het gemiddelde alcoholpercentage per soort.
SELECT soort, gemiddelde
FROM (SELECT soortnr, avg(alcohol) as gemiddelde FROM bieren GROUP BY soortnr) as r1
INNER JOIN soorten
ON r1.soortnr = soorten.soortnr
In de subquery wordt eerst het gemiddelde alcoholpercentage berekend per soortnr.
Gemiddelde is een aliasnaam uit de subquery die als kolomnaam gebruikt wordt in de buitenste query.
Om het resultaat van de subquery te kunnen joinen met een andere table moet je een aliasnaam geven aan de subquery.
Resultaat: 38 records
98
Maak een lijst van alle bieren met een lagere alcoholpercentage dan het gemiddelde
alcoholpercentage van zijn eigen soort.
SELECT b1.*
FROM bieren as b1
WHERE b1.alcohol < (SELECT avg(b2.alcohol) FROM bieren as b2 WHERE
b2.soortnr=b1.soortnr)
Hier wordt tweemaal gebruik gemaakt van de table bieren. Daarom wordt er aan de table een alias toegekend. Om aan te duiden tot welke table de velden behoren gebruiken we hier de alias i.p.v. de tablenaam bieren.
Resultaat: 603 records
99
Het tuincentrum wil graag een lijst waarop is aangegeven welke bomen in Aalsmeer en welke buiten
Aalsmeer verkrijgbaar zijn. Op het overzicht moeten de volgende gegevens verschijnen : plantid,
plantennaam, artikelcode van de leverancier, alsmede een aanduiding 'AALSMEER' of 'BUITEN
AALSMEER'.
Sorteer de lijst op plantId.
Resultaat: 23 records
SELECT planten.plantId, plantNaam, artikelLeverancierCode, 'Aalsmeer' as plaats
FROM planten
INNER JOIN artikelsleveranciers ON planten.plantId = artikelsleveranciers.plantId
INNER JOIN leveranciers ON artikelsleveranciers.leverancierId = leveranciers.leverancierId
WHERE woonplaats='Aalsmeer' and soort='boom'
UNION
SELECT planten.plantId, plantNaam, artikelLeverancierCode,'buiten Aalsmeer' as plaats
FROM planten
INNER JOIN artikelsleveranciers ON planten.plantId = artikelsleveranciers.plantId
INNER JOIN leveranciers ON artikelsleveranciers.leverancierId = leveranciers.leverancierId
WHERE woonplaats<>'Aalsmeer' and soort='boom'
ORDER BY plantId
100
Je wil een overzicht waarop is aangegeven welke bestellingen te laat zijn. Voor de bestellingen die te laat zijn moet in een extra kolom de opmerking 'TE LAAT' worden geplaatst; voor de andere bestellingen wordt in die kolom een aantal streepjes geplaatst.
Sorteer het overzicht op bestelid.
Geef bestelid, besteldatum, artikelLeverancierId, leveringsdatum, levertijd, bericht 'TE LAAT' of '-------'.
Resultaat: 93 records
SELECT bestellingen.bestelId, bestellijnen.artikelLeverancierId, bestelDatum, leveringsDatum, levertijd, 'TE LAAT' as bericht
FROM bestellingen
INNER JOIN bestellijnen ON bestellingen.bestelId=bestellijnen.bestelId
INNER JOIN artikelsleveranciers ON artikelsleveranciers.artikelLeverancierId=bestellijnen.artikelLeverancierId
WHERE datediff(leveringsDatum,bestelDatum) > levertijd
UNION
SELECT bestellingen.bestelId, bestellijnen.artikelLeverancierId, bestelDatum, leveringsDatum, levertijd, '-------' as bericht
FROM bestellingen
INNER JOIN bestellijnen ON bestellingen.bestelId=bestellijnen.bestelId
INNER JOIN artikelsleveranciers ON artikelsleveranciers.artikelLeverancierId=bestellijnen.artikelLeverancierId
WHERE datediff(leveringsDatum,bestelDatum) <= levertijd
ORDER BY bestelId
101
Welke planten zijn hoger dan de gemiddelde hoogte van alle planten samen? Toon alle gegevens.
Resultaat: 21 records
SELECT * FROM planten WHERE hoogte > (SELECT avg(hoogte) FROM planten)
102
Welke planten zijn duurder dan de gemiddelde prijs van de bomen? Toon alle gegevens.
Resultaat: 13 records
SELECT * FROM planten WHERE prijs > (SELECT avg(prijs) FROM planten WHERE soort='boom')
103
Maak een overzicht van de leveranciers (alle gegevens) waar nog bestellingen uitstaan met een
leverdatum die vóór 1 april 2016 ligt.
Resultaat: 7 records
SELECT *
FROM leveranciers
WHERE leverancierId in (SELECT leverancierId FROM bestellingen WHERE leveringsDatum
104
Welke rijen hebben de laagste offerteprijs van alle offertes in de table artikelsleveranciers? Geef alle
gegevens.
Resultaat: 2 records
SELECT * FROM artikelsleveranciers WHERE offertePrijs=(SELECT min(offertePrijs) FROM artikelsleveranciers)
105
Welke planten zijn lager dan de laagste vaste plant?
Toon alle gegevens.
Planten waar de hoogte 0 is, worden niet meegerekend.
Resultaat: 5 records
"SELECT *
FROM planten
WHERE hoogte > 0 and hoogte 0)"
106
Welke planten zijn hoger dan de gemiddelde hoogte van vaste planten en tevens goedkoper dan de
gemiddelde prijs van vaste planten? Geef alle gegevens.
Resultaat: 13 records
SELECT * FROM planten WHERE hoogte > (SELECT AVG(hoogte) FROM planten WHERE soort='vast')
AND prijs < (SELECT AVG(prijs) FROM planten WHERE soort='vast')
107
Welke planten hebben een prijs die tussen de laagste en hoogste prijs van de klimplanten ligt? Geef
alle gegevens.
Resultaat: 11 records
SELECT * FROM planten
WHERE prijs > (SELECT min(prijs) FROM planten WHERE soort = 'klim')
AND prijs < (SELECT max(prijs) FROM planten WHERE soort = 'klim')
108
Maak een overzicht van alle plantIds die een lagere offerteprijs hebben dan de gemiddelde
offerteprijs voor de betreffende plantId.
Plaats de gegevens plantId, leveranciersnaam en offerteprijs op het overzicht. Sorteer op plantId.
Resultaat: 71 records
SELECT a1.plantid, leverancierNaam, a1.offertePrijs
FROM artikelsleveranciers a1 INNER JOIN leveranciers
ON a1.leverancierId = leveranciers.leverancierId
WHERE a1.offerteprijs < (SELECT avg(a2.offertePrijs) FROM artikelsleveranciers a2 WHERE a2.plantId=a1.plantId)
ORDER BY a1.plantId
109
Maak een overzicht van bestelde planten die een bestelprijs hebben welke hoger is dan de maximum
offerteprijs voor zo’n plant.
Plaats de volgende gegevens op het overzicht : bestelnummer, artikelcode van de leverancier,
plantennaam en bestelprijs.
Resultaat: 34 records
SELECT bestellijnen.bestelid, a1.artikelLeverancierCode, plantNaam, bestelPrijs
FROM planten
INNER JOIN artikelsleveranciers a1 ON planten.plantId=a1.plantId
INNER JOIN bestellijnen ON bestellijnen.artikelLeverancierId = a1.artikelLeverancierId
INNER JOIN bestellingen ON bestellijnen.bestelId = bestellingen.bestelId and bestellingen.leverancierId=a1.leverancierId
WHERE bestelPrijs > (SELECT max(offertePrijs) FROM artikelsleveranciers a2 WHERE a2.plantId=planten.plantId)
110
Geef een lijst van de films die in de titel noch het woord "kill" noch het woord "blood"
hebben. We willen enkel de films van de genres griezel en thriller.
video
SELECT filmId, titel, genre FROM films
INNER JOIN genres ON films.genreId=genres.genreId
WHERE genre in ('thriller','griezel') AND titel NOT LIKE '%blood%' AND titel NOT LIKE '%kill%'
111
We willen een lijst maken van wie welke films huurt. De lijst wordt oplopend gesorteerd op
naam en titel.
video
SELECT naam, voornaam, titel FROM klanten
INNER JOIN verhuringen ON verhuringen.klantId=klanten.klantId
INNER JOIN films ON films.filmId=verhuringen.filmId
ORDER BY 1,3
112
Wat is de totale voorraad per genre?
video
SELECT genres.genreId, genre, sum(voorraad) as TotaalVoorraad FROM genres
INNER JOIN films ON films.genreId=genres.genreId
GROUP BY 1
ORDER BY 1
113
Op basis van het huuraantal willen we een label toevoegen aan elke klant:
Als het huuraantal groter is dan 250 krijgt de klant het label ‘Zeer goede klant’, als het
huuraantal kleiner is dan 150 krijgt hij het label ‘Slechte klant’. In de andere gevallen komt er
als label ‘Goede klant’.
video
SELECT *, 'Zeer goede klant' AS label FROM klanten
WHERE totaalGehuurd > 250
UNION
SELECT *, 'Goede klant' AS label FROM klanten
WHERE totaalGehuurd BETWEEN 150 AND 250
UNION
SELECT *, 'Slechte klant' AS label FROM klanten
WHERE totaalGehuurd < 150
114
Geef alle gegevens van de duurste film.
video
SELECT * FROM films
| WHERE prijs = (SELECT max(prijs) FROM films)
115
Voeg een nieuwe soort 'Extra donker' toe aan de table soorten.
INSERT INTO soorten (Soort)
| VALUES ('Extra donker')
116
Voeg een nieuwe brouwer toe aan de table brouwers.
Gegevens van de nieuwe brouwerij: Brouwerij Vaattappers is gevestigd aan Interleuvenlaan 2 in 3000
Heverlee met een omzet van 1000.
INSERT INTO brouwers (BrNaam, Adres, Postcode, Gemeente, Omzet)
VALUES ('Brouwerij Vaattappers', 'Interleuvenlaan 2', 3000, 'Heverlee', 1000)
117
Voeg met één instructie drie nieuwe biersoorten toe aan de table soorten: witbier, Ice bier en
honingbier.
```
INSERT INTO soorten (Soort)
VALUES
('witbier'),
('Ice bier'),
('honing bier')
```
118
Voeg de bieren met een alcoholpercentage hoger dan 10 toe aan de table bieren_oud.
Selecteer eerst de gegevens om je instructie te controleren:
SELECT *
FROM bieren
WHERE alcohol > 10
Deze select-instructie voeg je in in je insert into-instructie:
INSERT INTO bieren_oud
SELECT *
FROM bieren
WHERE alcohol > 10
119
Verhoog het alcoholpercentage van de bieren (met soortnr 21) met 0,5. Doe deze aanpassing in de
table bieren_oud.
UPDATE bieren_oud
SET alcohol = alcohol+0.5
WHERE soortnr = 21
120
Verlaag het alcoholpercentage met 1 van de bieren waarvan de brouwerij een omzet heeft die groter
is dan 25000. Doe deze aanpassing in de table bieren_oud.
UPDATE bieren_oud
SET alcohol = alcohol - 1
WHERE brouwernr in (SELECT brouwernr FROM brouwers WHERE omzet > 25000)
121
Verwijder het bier met biernr 750 uit de table bieren_oud.
DELETE
FROM bieren_oud
WHERE biernr=750
122
Verwijder uit de table bieren_oud de bieren die in Soy gebrouwen worden.
DELETE
FROM bieren_oud
WHERE brouwernr in (SELECT brouwernr FROM brouwers WHERE gemeente='Soy')
123
Het tuincentrum breidt zijn leverancierskring uit met GROEN BV. De volgende gegevens zijn bekend :
Zie kader p.96
INSERT INTO leveranciers (leverancierNaam, adres, woonplaats)
VALUES ('GROEN BV.', 'ONDER DE LINDE 234', 'AALSMEER' )
124
Wijzig in de table bestellingen de leverdatum in 5 april 2016 voor de bestelling met nummer 8.
UPDATE bestellingen
SET leveringsDatum='2016-4-5'
WHERE bestelId=8
125
Verwijder alle rijen uit de table artikelsleveranciers die betrekking hebben op de heesters van
leverancier 8.
Resultaat: 14 records verwijderd
DELETE
FROM artikelsleveranciers
WHERE leverancierId=8 and plantId in (SELECT plantId FROM planten WHERE soort='heester')
126
Op 23 april 2016 is een nieuwe bestelling geplaatst bij leverancier 4. Dit zijn de details:
Zie kader p.97
De leverancier geeft 8% korting op het bruto bestelbedrag. De leverdatum is één week na de
bestelling.
Doe de nodige aanpassingen in de tables bestellingen en bestellijnen. Denk aan de volgorde!
Toevoegingen aan de table bestellingen
INSERT INTO bestellingen (leverancierId, bestelDatum, leveringsDatum,korting)
VALUES (4, '2016-4-23', '2016-4-30', 0.08)
Bestelnr opzoeken
Zoek het bestelnr van de laatst toegevoegde bestelling.
SELECT * FROM bestellingen
Het laatste record heeft als bestelId 16.
Toevoegingen aan de table bestellijnen
INSERT INTO bestellijnen (bestelid, artikelLeverancierId, aantal, bestelprijs)
VALUES (16, 62, 10, 8.15)
INSERT INTO bestellijnen (bestelid, artikelLeverancierId, aantal, bestelprijs)
VALUES (16, 58, 200, 0.4)
INSERT INTO bestellijnen (bestelid, artikelLeverancierId, aantal, bestelprijs)
VALUES (16, 59, 25, 2.3)
INSERT INTO bestellijnen (bestelid, artikelLeverancierId, aantal, bestelprijs)
VALUES (16, 74, 50, 1.3)
127
Verhoog alle offerteprijzen van de bolgewassen in de table artikelsleveranciers met 10%.
Resultaat: 7 records aangepast
UPDATE artikelsleveranciers
SET offertePrijs = offertePrijs*1.1
WHERE plantId in (SELECT plantId FROM planten WHERE soort='bol')
128
Voeg de informatie van de planten met een hoogte groter of gelijk aan 1000 toe aan de table
planten_oud. Zorg dat de hoogste planten bovenaan staan.
Resultaat: 11 records toegevoegd
```
INSERT INTO planten_oud
SELECT *
FROM planten
WHERE hoogte >=1000
ORDER BY hoogte desc
```
129
Maak een nieuwe table klanten met de velden klantnr, klnaam, kladres, klpost en klgemeente.
De velden klnr en klnaam moeten een waarde bevatten.
denk video..?
```
CREATE TABLE klanten
(klantnr integer not null, klnaam varchar(30) not null, kladres varchar(40), klpost char(4),
klgemeente varchar(40))
```
130
Maak een table alcoholarm. Plaats hierin de gegevens van de alcoholarme bieren. Neem naam van
de brouwer en naam van het bier mee.
CREATE TABLE alcoholarm
AS
SELECT naam, brnaam
FROM bieren
INNER JOIN brouwers ON bieren.brouwernr=brouwers.brouwernr
INNER JOIN soorten ON bieren.soortnr=soorten.soortnr
WHERE soort='Alcoholarm'
131
Voeg een veld Opmerkingen toe aan de table brouwers.
ALTER TABLE brouwers
| ADD Opmerkingen varchar(25)
132
Voeg een veld Contactpersoon toe aan de table brouwers. Zorg dat hier altijd iets ingevuld moet
worden.
ALTER TABLE brouwers
| ADD Contactpersoon varchar(25) NOT NULL
133
Verwijder de velden Opmerkingen en Contactpersoon uit de table brouwers.
ALTER TABLE brouwers
| DROP Opmerkingen, DROP Contactpersoon
134
Unique
Hiermee kan je een veld aanwijzen als een unieke index. Dit kan voor elk veld van je table toegepast
worden.
Unique voorkomt dat twee records in de table dezelfde waarde hebben voor een bepaald veld.
Als de unieke index toegepast wordt op meerdere velden, betekent dit dat de combinatie van de
waarden in die velden uniek moet zijn.
Ga uit van volgende table:
Zie kader p. 106
Situatie 1: unique op veld 2
Het record met veld 1=1, veld 2=4 en veld 3=2 kan toegevoegd worden aan de table.
Een record met veld 1=1, veld 2=1 en veld 3=2 kan niet toegevoegd worden aan de table omdat er
reeds een record is met 1 als waarde in veld 2.
Situatie 2: unique op veld 1 en veld 2
Het record met veld 1=1, veld 2=3 en veld 3=1 kan toegevoegd worden aan de table.
Een record met veld 1=1, veld 2=1 en veld 3=3 kan niet toegevoegd worden omdat deze combinatie
van veld 1 en veld 2 reeds bestaat in de table.
135
Maak een table klanten met de velden klantId en klantnaam.
KlantId is de primaire sleutel.
De naam mag maximaal 30 tekens lang zijn en moet altijd ingevuld worden.
denk video..?
CREATE TABLE klanten (
klantId integer not null auto_increment,
klantnaam varchar(30) not null,
CONSTRAINT pk_klantId primary key (klantId))
De beperkende voorwaarde krijgt hier de naam pk_klantId:
• pk want het gaat om een primaire sleutel
• op het veld klantId
Na de naam komt het sleutelwoord primary key omdat we een primaire sleutel willen maken.
136
Maak een table gebruikers met de velden nummer, naam en userId.
De inhoud van het veld userId moet een unieke waarde hebben maar is toch niet de primaire sleutel.
Het veld nummer moet de primaire sleutel zijn.
denk video..?
```
CREATE TABLE gebruikers (
nummer integer not null,
naam varchar(30),
userId varchar(8),
CONSTRAINT pk_nr primary key(nummer),
CONSTRAINT u_userid unique(userId))
Hier hebben we 2 constraint-instructies:
• CONSTRAINT pk_nr primary key
Hiermee maken we een primaire sleutel met de naam pk_nr voor het veld nr.
• CONSTRAINT u_userid unique
Met deze instructie maken we een unieke index met de naam u_userid op het veld userId.
```
137
Maak een table bestellingen met de velden bestelId, klantnummer, besteldatum.
De primaire sleutel is het veld bestelId.
Tussen het veld klantnummer van deze table en het veld klantId van de table klanten wordt een 1-
op-veel-relatie gedefinieerd.
denk video..?
CREATE TABLE bestellingen (
bestelId integer,
klantnummer integer,
besteldatum datetime,
CONSTRAINT pk_bestelId primary key (bestelId),
CONSTRAINT f_klantnummer foreign key (klantnummer) references klanten (klantId))
Ook hier hebben we 2 constraint-instructies:
• CONSTRAINT pk_bestelId primary key
Hiermee maken we een primaire sleutel met de naam pk_bestelId voor het veld bestelId.
• CONSTRAINT f_klantnummer references klanten (klantId)
Met deze instructie maken we een externe sleutel (foreign key) met de naam
f_klantnummer op het veld klantnummer. Dit veld wordt gelinkt aan klantId (de primaire
sleutel) in de table klanten.
138
Maak een table bestellijnen met de velden: bestelnummer, biernummer en aantal.
Definieer een samengestelde sleutel met de velden bestelnummer en biernummer.
```
CREATE TABLE bestellijnen
(bestelnummer integer, biernummer integer, aantal integer, CONSTRAINT pk_bestbier
primary key (bestelnummer, biernummer))
```
139
Definieer een 1-op-veel-relatie tussen de tables bestellingen (bestelId) en bestellijnen
(bestelnummer).
ALTER TABLE bestellijnen
ADD CONSTRAINT f_bestelnummer foreign key (bestelnummer) references bestellingen
(bestelId)
140
Verwijder de beperkende voorwaarde f_bestelnummer.
ALTER TABLE bestellijnen
| DROP FOREIGN KEY f_bestelnummer
141
Verwijder de primaire sleutel van de table bestellijnen.
ALTER TABLE bestellijnen
| DROP PRIMARY KEY
142
Maak een index voor het veld klnaam van de table klanten.
```
CREATE INDEX i_naam
ON klanten (klantnaam)
```
143
Verwijder de index i_naam van de table klanten.
DROP INDEX i_naam ON klanten
144
Creëer een table klachten met de volgende velden: Zie P 113
Voeg nadien volgende klacht toe aan deze table:
Klacht nr. 1 heeft betrekking op plantid 10. De klacht luidt : “Planten (leveranciercode 2) verkocht op
14-1-2016 vertoonden bruine vlekken na circa 2 maanden”.
De status is GL (gemeld aan leverancier) en de klacht wordt ingebracht op 15-3-2016.
Table klachten maken
CREATE TABLE klachten
(klachtId integer, plantId integer, datum datetime, klacht varchar(100), status char(2))
Klacht toevoegen
INSERT INTO klachten
VALUES (1, 10, '2016/03/15', 'Planten (leveranciercode 2) verkocht op 14-1-2016 vertoonden bruine vlekken na circa 2 maanden', 'GL')
145
Er is behoefte aan een table aanbied waaruit snel de goedkoopste leveranciers van de planten uit de
table planten kunnen worden opgezocht.
De table heeft de volgende kolommen : plantId, plantNaam, leverancierid en offerteprijs.
Vul deze table vanuit de tables planten en artikelsleveranciers.
CREATE TABLE aanbied
AS
SELECT planten.plantId, plantNaam, a1.leverancierId, offertePrijs
FROM artikelsleveranciers a1 INNER JOIN planten ON a1.plantId = planten.plantId
WHERE offertePrijs = (SELECT min(offertePrijs) FROM artikelsleveranciers a2 WHERE a2.plantId = a1.plantId)
146
Breid de table planten uit met een kolom voorraad om het aantal stuks dat het tuincentrum nog in
voorraad heeft bij te houden.
ALTER TABLE planten
| ADD voorraad integer
147
Definieer alle relaties tussen de tables van de database Planten. Zie p. 113
ALTER TABLE artikelsleveranciers
ADD CONSTRAINT f_plantId foreign key (plantId) references planten (plantId)
ALTER TABLE artikelsleveranciers
ADD CONSTRAINT f_leverancierId foreign key (leverancierId) references leveranciers (leverancierId)
ALTER TABLE bestellingen
ADD CONSTRAINT f_leverancierId2 foreign key (leverancierId) references leveranciers (leverancierId)
ALTER TABLE bestellijnen
ADD CONSTRAINT f_bestelId foreign key (bestelId) references bestellingen (bestelId)
ALTER TABLE klachten
ADD CONSTRAINT f_klachten foreign key (plantId) references planten (plantId)
ALTER TABLE planten
ADD CONSTRAINT f_categorie foreign key (categorieId) references categorieen (categorieId)
ADD CONSTRAINT f_artikelsleveranciers foreign key(artikelLeverancierId) references artikelsleveranciers(artikelLeverancierId)
ALTER TABLE bestellijnen
ADD CONSTRAINT f_artikelLeverancierId
foreign key(artikelLeverancierId) references artikelsleveranciers(artikelLeverancierId)
148
Maak een index BSRIDX01 op de samengestelde sleutel bestelId en artikelLeverancierId van de table
bestellijnen.
```
CREATE INDEX bsridx01
ON bestellijnen (bestelId, artikelLeverancierId)
```
149
De table artikelsleveranciers wordt regelmatig gejoined met de tables planten, leveranciers en
bestellijnen.
Welke indexen zijn waardevol voor de table artikelsleveranciers?
```
CREATE INDEX i_plantId
ON artikelsleveranciers (plantId)
```
```
CREATE INDEX i_leverancierId
ON artikelsleveranciers (leverancierId)
```
150
Maak een view bierlijst. Op deze lijst staat de naam van het bier, de naam van de brouwer en het
soort bier.
CREATE VIEW bierlijst
AS
SELECT naam, brnaam, soort
FROM bieren
INNER JOIN brouwers ON bieren.brouwernr = brouwers.brouwernr
INNER JOIN soorten on bieren.soortnr=soorten.soortnr
151
Verwijder de view bierlijst.
DROP VIEW bierlijst
152
Definieer een view vastlaag waarin alle gegevens van alle vaste planten uit de table planten
voorkomen met een hoogte van maximaaal 15 cm.
```
CREATE VIEW vastlaag
AS
SELECT *
FROM planten
WHERE soort like 'vast' and hoogte<=15
```
153
Definieer een view offerteprijzen met de kolommen plantId, minOff, maxOff en gemOff waarin
respectievelijk plantid, laagste, hoogste en gemiddelde offerteprijs vermeld is.
```
CREATE VIEW offerteprijzen
AS
SELECT plantId, min(offertePrijs) AS minOff, max(offertePrijs) AS maxOff, avg(offertePrijs) AS gemOff
FROM artikelsleveranciers
GROUP BY plantId
```
154
Maak een view zomerplanten waarmee de gegevens plantid, plantennaam, soort en prijs zijn te
benaderen van alle planten die in de maanden juni, juli en augustus beginnen te bloeien.
```
CREATE VIEW zomerplanten
AS
SELECT plantId, plantNaam, soort, prijs
FROM planten
WHERE beginBloeiMaand between 6 and 8
```
155
Definieer een view bomen met de gegevens plantId, plantennaam, hoogte en prijs van alle bomen.
```
CREATE VIEW bomen
AS
SELECT plantId, plantNaam, hoogte, prijs
FROM planten
WHERE soort like 'boom'
```
156
Definieer een view leverancier5 waarin alleen van leverancier 5 de volgende gegevens staan: plantId,
plantNaam, artikelLeverancierCode, offertePrijs, prijs
CREATE VIEW leverancier5
AS
SELECT artikelsleveranciers.plantId, planten.plantNaam, artikelLeverancierCode, offertePrijs, planten.prijs
FROM artikelsleveranciers INNER JOIN planten ON planten.plantId = artikelsleveranciers.plantId
WHERE leverancierId=5
157
Maak een view waardoor alleen de offertegegevens van de leveranciers uit Lisse zijn te selecteren.
```
CREATE VIEW lisse
AS
SELECT artikelsleveranciers.*
FROM artikelsleveranciers INNER JOIN leveranciers ON artikelsleveranciers.leverancierId = leveranciers.leverancierId
WHERE woonplaats like 'Lisse'
```
158
Maak een view besteldeplanten die een overzicht geeft van bestelid, artikelcode van de leverancier
en de plantnaam.
CREATE VIEW besteldeplanten
AS
SELECT bestellijnen.bestelid, artikelleveranciercode, plantNaam
FROM planten
INNER JOIN artikelsleveranciers ON planten.plantId = artikelsleveranciers.plantId
INNER JOIN bestellijnen ON artikelsleveranciers.artikelLeverancierId = bestellijnen.artikelLeverancierId
159
Voeg jezelf toe als klant.
video
INSERT INTO klanten (naam, voornaam, adres, postcode, woonplaats, klantStatus, totaalGehuurd, datumLid, lidgeld)
VALUES ('AHADDOUR', 'ISMAEL','GROTE MARKT 1', 1000, 'BRUSSEL', 1, 0, '2020-9-18', 1)
160
Geef alle films met maatschappijCode VH een prijsverhoging van 10%.
video
UPDATE films
SET prijs = prijs*1.1
WHERE maatschappijId IN (SELECT maatschappijId FROM maatschappijen WHERE maatschappijCode = 'VH')
161
Maak een table met enkel de films van het genre Thriller.
video
denk dat aangepast moet worden , zie feedback coach in mail
CREATE VIEW Thrillers
AS
SELECT * FROM films WHERE genreId IN (SELECT genreId FROM genres WHERE genreId = 13)
162
Voeg aan de table maatschappijen een veld mailadres toe. Dit veld mag maximaal 40 tekens
lang zijn en mag niet leeg zijn.
video
ALTER TABLE maatschappijen
| ADD mailadres varchar (40) NOT NULL
163
Definieer alle nodige primary en foreign keys. (zie test coach 3)
video
ALTER TABLE films
ADD CONSTRAINT f_maatschappijId FOREIGN KEY (maatschappijId) REFERENCES maatschappijen (maatschappijId)
ALTER TABLE films
ADD CONSTRAINT f_genreId FOREIGN KEY (genreId) REFERENCES genres (genreId)
ALTER TABLE verhuringen
ADD CONSTRAINT f_filmId FOREIGN KEY (filmId) REFERENCES films (filmId)
ALTER TABLE verhuringen
ADD CONSTRAINT f_klantId FOREIGN KEY (klantId) REFERENCES klanten (klantId)
