SQL & Relationele datamodellen in interactieve media HVA-CMD-V1-datamodelleren oefeningen deel 1: SQL 2012-2013
Inhoud Inhoud ................................................................................................................................................ 2 Selecties uit een enkelvoudige datatabel............................................................................................ 3 Selecties uit een meerdere gerelateerde tabellen ............................................................................... 7 Het toevoegen en veranderen van data ............................................................................................ 10 Het verwijderen van data ................................................................................................................. 12 Meer selecties uit een relationele database ...................................................................................... 13
Selecties uit een enkelvoudige datatabel
Vertaal onderstaande vragen over de tabel 'product' naar een SQL query. Gebruik hiervoor de database ‘sql_oefeningen1_producten.sql’. De benodigde SQLcode is allemaal te vinden in hoofdstuk 1 tot en met 10 van het boek ‘SQL in 10 minutes’ en/of de mySQL reference op http://dev.mysql.com/doc/refman/5.1/en/index.html 1) Maak een lijst met productnamen en productprijzen SELECT naam, prijs FROM product SELECT naam, FORMAT(prijs ,2) FROM product
2) Maak een lijst met alle productgegevens SELECT * FROM product
3) Maak een lijst met productnamen en productprijzen , gesorteerd op prijs SELECT naam, prijs FROM product ORDER BY prijs
4) maak een lijst met productnamen en productprijzen , gesorteerd op categorie en op prijs met de duurste bovenaan SELECT categorie, naam, prijs FROM product ORDER BY categorie, prijs DESC
5) selecteer alle producten van categorie 'boek' SELECT * FROM product WHERE categorie = 'boek'
6) selecteer alle producten die duurder zijn dan 10 euro SELECT * FROM product WHERE prijs > 10
7) selecteer alle producten van categorie voedsel die geproduceerd zijn door 'unilever' SELECT * FROM product WHERE categorie='voedsel' AND producent = 'unilever'
8) selecteer alle producten waar geen leverancier is ingevuld ( dit is een moeilijke ! ) SELECT * FROM product WHERE producent IS NULL
9) selecteer alle producten die niet door unilever zijn geproduceerd SELECT * FROM product WHERE NOT ( producent = 'unilever' )
10) selecteer alle producten die iets te maken hebben met 'lekker' en sorteer per categorie SELECT * FROM product WHERE omschrijving LIKE '%lekker%' ORDER BY categorie
11) selecteer een prijslijst van alle producten met de prijs plus btw. SELECT naam, prijs, prijs + ( prijs * btw_tarief / 100 ) AS prijs_met_btw FROM product
12) selecteer alle producten die nu beschikbaar zijn SELECT * FROM product WHERE beschikbaarheidsdatum < CURRENT_DATE
13) selecteer alle producten die in 2013 beschikbaar komen SELECT * FROM product WHERE YEAR (beschikbaarheidsdatum ) = 2013
14) selecteer alle producten met de eerste 15 karakters van de omschrijving, gevolgd door '…' SELECT naam , CONCAT( SUBSTRING( omschrijving, 1, 15 ), "..." ) AS omschr FROM product
15) bereken de gemiddelde prijs alle producten SELECT AVG(prijs) AS gemiddeldeprijs FROM product
16) tel het aantal producten van de producent 'unilever' SELECT COUNT(*) AS aantalproducten FROM product WHERE producent = 'unilever'
17) bereken de gemiddelde prijs van de producten van 'unilever' SELECT AVG(prijs) AS gemiddeldeprijs FROM product WHERE producent = 'unilever'
18) hoe duur is het duurste product, en het goedkoopste product SELECT MAX(prijs) AS maxPrijs, MIN(prijs) AS minPrijs FROM product
19) maak een lijst van alle categorieen SELECT DISTINCT categorie FROM product SELECT categorie FROM product GROUP BY categorie
20) maak een lijst van alle producenten SELECT DISTINCT producent FROM product SELECT producent FROM product GROUP BY producent
21) maak een lijst met het aantal producten per categorie SELECT categorie, COUNT( * ) AS aantalproducten FROM product GROUP BY categorie
22) maak een lijst met de gemiddelde prijs van producten per producent SELECT producent, AVG(prijs) AS gemiddeldePrijs FROM product GROUP BY producent
Selecties uit een meerdere gerelateerde tabellen
datamodel voor de backend van een webwinkel Vertaal onderstaande vragen over de tabellen naar een SQL query. Gebruik hiervoor de database ‘sql_oefeningen2_webwinkel.sql’. De benodigde SQLcode is allemaal te vinden in hoofdstuk 11 tot 14 en hoofdstuk 18 van het boek ‘SQL in 10 minutes’ 23) Selecteer de lijst producten met daaraan gekoppeld de categorienaam uit de tabel categorie SELECT product.* , categorie.naam AS categorieNaam FROM product LEFT JOIN categorie ON categorie.ID = product.categorieID
24) Selecteer de lijst producten met daaraan gekoppeld de het btw tarief in percentages uit de tabel btwtarief SELECT product.* , btwtarief.percentage AS btwPercentage FROM product LEFT JOIN btwtarief ON btwtarief.ID = product.btwtariefID
25) ** Selecteer de lijst producten met daaraan gekoppeld de producentnaam uit de tabel producent Letop: niet bij ieder product is de producentID ingevuld. Dat product moet wel geselecteerd worden en de producentnaam moet leeg blijven SELECT product.* , producent.naam AS producentNaam FROM product LEFT JOIN producent ON producent.ID = product.producentID
26) ** Selecteer de lijst producten met daaraan gekoppeld de categorienaam uit de tabel ‘categorie’, het btwtarief uit de tabel ‘ btwtarief’ en de producentnaam uit de tabel ‘producent’ SELECT product.* , producent.naam AS producentNaam , btwtarief.percentage AS btwPercentage , categorie.naam AS categorieNaam FROM product LEFT JOIN producent ON producent.ID = product.producentID LEFT JOIN categorie ON categorie.ID = product.categorieID LEFT JOIN btwtarief ON btwtarief.ID = product.btwtariefID
27) ** Selecteer de lijst producten met daaraan gekoppeld de categorienaam uit de tabel ‘categorie’, de producentnaam uit de tabel ‘producent’, en de berekende prijs_met_btw SELECT product.* , producent.naam AS producentNaam , btwtarief.percentage AS btwPercentage , categorie.naam AS categorieNaam, ROUND( prijs + ( prijs * btwtarief.percentage / 100 ), 2 ) AS prijs_met_btw FROM product LEFT JOIN producent ON producent.ID = product.producentID LEFT JOIN categorie ON categorie.ID = product.categorieID LEFT JOIN btwtarief ON btwtarief.ID = product.btwtariefID
28) ** Selecteer een lijst van producenten met voor iedere producenten het aantal producten in de product-tabel dat door de producent geleverd wordt SELECT producent.* , COUNT(product.ID) AS aantalproductent FROM producent LEFT JOIN product ON product.producentID = producent.ID GROUP BY producent.ID SELECT * , ( SELECT COUNT(*) FROM product WHERE product.producentID = producent.ID ) AS aantalProducten FROM producent
29) Selecteer een lijst van klanten die iets besteld hebben in 2012 SELECT DISTINCT klant.* FROM klant JOIN bestelling ON bestelling.klantID = klant.ID WHERE YEAR(bestelling.datum) = 2012 SELECT * FROM klant WHERE ID IN ( SELECT klantID FROM bestelling WHERE YEAR ( datum ) = 2012 )
30) ** Selecteer alle bestellingen en bereken voor iedere bestelling het aantal bestelde producten en de totaalprijs met btw SELECT bestelling.* , SUM( bestelling_product.aantal ) AS aantalBesteldeProducten , SUM( bestelling_product.aantal * ROUND(( product.prijs + ( product.prijs * btwtarief.percentage / 100 ) ) , 2) ) AS totaalPrijs FROM bestelling LEFT JOIN bestelling_product ON bestelling_product.bestellingID = bestelling.ID JOIN product ON bestelling_product.productID = product.ID LEFT JOIN btwtarief ON btwtarief.ID = product.btwtariefID GROUP BY bestelling.ID
Het toevoegen en veranderen van data Vertaal onderstaande vragen over de tabellen naar een SQL query. Gebruik hiervoor de database ‘sql_oefeningen2_webwinkel.sql’. De benodigde SQLcode is allemaal te vinden in hoofdstuk 15 en 16 van het boek ‘SQL in 10 minutes’ 31) Voeg een nieuw product toe aan de tabel ‘product’ In de categorie voedsel: Pindakaas voor 4,20 euro geproduceerd door unilever met het lage btwtarief en beschikbaar vanaf 5 januari 2013. Er zijn 3000 potten invoorraad INSERT INTO product ( ID , naam , omschrijving , prijs , aantal_in_voorraad , beschikbaar_vanaf , producentID , categorieID , btwtariefID ) VALUES ( NULL , 'pindakaas', 'de lekkerste pindakaas van amsterdam', ' 40.20', '3000', '2013-01-01', '7', '3', 'laag' )
32) Bedenk zelf een nieuwe categorie, een nieuwe producent, en voeg een product toe van die producent in die categorie TIP: dit zijn dus drie verschillende SQL statements op een rij!
33) Maak jezelf tot klant van de winkel 34) Plaats voor jezelf een bestelling van producten nar keuze. TIP: voeg eerst de bestelling toe aan de tabel ‘bestelling’ - lees de ID van die bestelling uit - voeg de gekozen producten met aantallen toe de besteling
35) Verander de prijs van ‘Pindakaas’ van 4.20 naar 3.95
36) Verander de geboorte datum van Joost van den Vondel
37) Verander de naam van de producent ‘fons’
38) Verander de naam van de producent ‘vishandel ‘’haring’’’ naar ‘vishandel ‘’makreel’’’.
Het verwijderen van data De volgende vragen hebben betrekking op het verwijderen van data. We gaan nog geen sql maken om rijen uit kolommen te verwijderen. Eerst gaan we nadenken over complicaties die kunnen optreden als je onvoorzichtig bent. 39) Stel je wilt een producent uit de tabel ‘producent’ verwijderen. Wat zou er dan moeten gebeuren met de producten die door de producent zijn geproduceerd? Mag je zomaar een producent verwijderen? Alle producten zouden verwijdert moeten worden, en daarmee ook alle bestelling van die producten. Maar dat is niet juist t.o.v. de klanten. Daarom is het misschien beter om nooit een producent uit de database te verwijderen.
40) Stel dat je een categorie verwijdert. Wat moet er gebeuren met de product in die categorie? Ik denk dat ie dan niet gecategoriseerd moet worden , maar volgens het datamodel moet de categorie zijjn ingevuld. Daarom moeten eerst alle producten in een andere categorie worden geplaatst voordat de categorie uit de categorie tabel weg mag.
41) Stel dat je een klant verwijdert, wat moet er gebeuren met de bestellingen van die klant? Ook verwijderen. Maar misschien is het beter klanten nooit te verwijderen.
42) Stel dat een een product verwijdert? Wat moet er gebeuren met de bestellingen waarin dat product is besteld? Je mag een product niet verwijderen als ie is besteld
43) Stel dat je een bestelling verwijdert? Wat moet er dan gebeuren met de tabel ‘bestelling_product’? Als je dan toch een bestelling verwijdert, dan moeten de gerelateerde rijen uit bestelling_product natuurlijk ook weg
44) Is het antwoord op bovenstaande vragen steeds hetzelfde? Het hangt een beetje af van het belang van de rijen in de echte wereld. Het is dus geen technische vraag. Wel is duidelijk dat het verwijderen van data in een relationele database vaak allerlei consequeties moet hebben.
Meer selecties uit een relationele database Nog een aantal SQL opdrachten met een relationele database: Gebruik hiervoor de database ‘sql_oefeningen3_festival.sql’
datamodel voor de database festival gegevens. Acts (bands, artiesten ) treden op in een zaal en bezoekers schrijven recensies over optredens. 45) Selecteer alle optredens van Jan Smit met daarbij datum, tijdstip en zaal. SELECT optreden.* , zaal.* FROM optreden LEFT JOIN act ON optreden.actID = act.ID LEFT JOIN zaal ON optreden.zaalID = zaal.ID WHERE act.naam = “Jan Smit”
46) Hoeveel mensen kunnen tegelijkertijd een optreden bijwonen. ( met andere woorden: selecteer het totaal maximum aantal personen in de zalen ) SELECT SUM(aantalplaatsen) AS maximumaantalbezoekers FROM zaal
47) ** Selecteer alle acts met daarbij het aantal optredens van deze act op dit festival. SELECT act.* , COUNT(optreden.ID) AS aantaloptredens FROM act LEFT JOIN optreden ON optreden.actID = act.ID GROUP BY act.ID
48) ** Selecteer alle recensies van optredens van Jan Smit SELECT recensie.* FROM recensie JOIN optreden ON optreden.ID = recensie.optredenID JOIN act ON optreden.actID = act.ID WHERE act.naam = 'Jan Smit'
49) ** Selecteer alle recensies en koppel daaraan de auteur, de act, tijd en plaats van het optreden en sorteer op datum en tijdstip. SELECT act.*, optreden.*, bezoeker.*, recensie.* FROM recensie LEFT JOIN optreden ON recensie.optredenID = optreden.ID LEFT JOIN zaal ON optreden.zaalID = zaal.ID LEFT JOIN act ON optreden.actID = act.ID LEFT JOIN bezoeker ON recensie.bezoekerloginnaam=bezoeker.loginnaam ORDER BY optreden.datum, optreden.aanvang
50) ** Selecteer alle acts en het gemiddelde cijfer dat de act krijgt van de bezoekers SELECT act.* , AVG(cijfer) AS gemiddeldcijfer FROM act LEFT JOIN optreden ON optreden.actID = act.ID LEFT JOIN recensie ON recensie.optredenID = optreden.ID GROUP BY act.ID
51) *** Selecteer alle optredens waarover géén recensie is geschreven SELECT act.*, optreden.*, COUNT(recensie.ID) AS aantalrecensies FROM optreden JOIN act ON optreden.actID = act.ID LEFT JOIN recensie ON recensie.optredenID = optreden.ID GROUP BY optreden.ID HAVING aantalrecensies = 0
52) *** Wat zou je doen om te voorkomen dat er per ongeluk twee optredens tegelijkertijd in dezelfde zaal zijn gepland zijn. Een trigger op het updaten en inserten van een optreden, waarin gecontroleerd wordt of de betreffende zaal op dat tijdstip nog vrij is. Zo nee dan zou ik een foutmelding genereren.
