· 9 years ago · Jan 13, 2017, 01:02 AM
1/* ********* Oefeningreeks 1 ************/
2/*
3Studenten (studnr, voornaam, familienaam, geboortedatum, geslacht, betaald, email)
4Cursussen (cursusnr, cursusnaam, inschrijvingsgeld)
5Studenten_Cursussen (studnr, cursusnr)
6
7*/
8
9/*
101. Geef een lijst van de studenten, gesorteerd op familienaam en voornaam.
11Enkel de eerste 12 studenten uit de alfabetische lijst worden weergegeven.
12Zorg dat spaties niet meespelen in het bepalen van de sorteervolgorde.
13*/
14select top 12 *
15from studenten
16order by replace(familienaam,' ', ''), replace(voornaam, ' ', '')
17
18
19/*
202. Geef een lijst van de mannelijke studenten met hun geboortedatum, gesorteerd volgens
21dalende geboortedatum. Indien de geboortedatum niet ingevuld is, toon dan de tekst ‘Niet gekend’.
22Zorg ook voor zinvolle kolomkoppen.
23*/
24select *, isnull(cast(geboortedatum as varchar(20)), 'Niet gekend') as DOB
25from studenten
26where geslacht = 'M'
27order by geboortedatum desc
28
29
30/*
313. Geef alle studenten met hun leeftijd (huidig jaar – geboortejaar). Sorteer op leeftijd, familienaam en voornaam.
32*/
33select *, DATEDIFF(year, geboortedatum, '2016') as leeftijd
34from studenten
35order by leeftijd, familienaam, voornaam
36
37
38/*
394. Geef de gegevens van de studenten waarvan de familienaam begint met v (klein).
40*/
41select *
42from studenten
43where familienaam like 'v%'
44collate latin1_general_CS_AS
45
46/*
475. Geef de studenten waarvan de familienaam ongeveer klinkt als ‘Vanmale’.
48*/
49select *
50from studenten
51where SOUNDEX(familienaam) = SOUNDEX('Van male')
52
53
54/*
556. Geef een overzicht van de studenten met de cursussen die ze volgen.
56Geef per student: het studnr, de familienaam, de voornaam en de naam van de cursus
57die ze volgen. Sorteer alfabetisch op familienaam en voornaam.
58*/
59select s.studnr, s.familienaam, s.voornaam, c.cursusnaam
60from studenten s
61join Studenten_Cursussen sc on sc.studnr = s.studnr
62join Cursussen c on c.cursusnr = sc.cursusnr
63order by s.familienaam, s.voornaam
64
65
66/*
677. Idem vorige vraag, maar zorg ervoor dat ook de studenten die geen enkele cursus volgen, weergegeven worden.
68*/
69select s.studnr, s.familienaam, s.voornaam, c.cursusnaam
70from studenten s
71left join Studenten_Cursussen sc on sc.studnr = s.studnr
72left join Cursussen c on c.cursusnr = sc.cursusnr
73order by s.familienaam, s.voornaam
74
75
76/*
778. Idem vorige vraag, maar zorg ervoor dat ook de cursussen die door geen enkele student gevolgd worden, weergegeven worden.
78*/
79select *
80from studenten s
81full outer join studenten_cursussen sc on sc.studnr = s.studnr
82full outer join cursussen c on c.cursusnr = sc.cursusnr
83order by s.familienaam, s.voornaam
84
85
86/*
879. Geef voor elke student wat hij reeds betaald heeft (de kolom betaald uit de tabel studenten)
88en wat de student zou moeten betalen (tebetalen) volgens de cursussen die hij volgt
89(voor elke cursus moet inschrijvingsgeld betaald worden). Als de student geen cursussen volgt,
90is tebetalen 0 (en niet NULL).
91*/
92select s.studnr, s.voornaam, s.familienaam, s.betaald, (c.inschrijvingsgeld - s.betaald) as 'tebetalen'
93from studenten s
94left join Studenten_Cursussen sc on sc.studnr = s.studnr
95left join Cursussen c on c.cursusnr = sc.cursusnr
96order by s.familienaam, s.voornaam
97
98/*
9910. Geef de gegevens van de studenten die geen enkele cursus volge
100*/
101select s.*
102from studenten s
103left join Studenten_Cursussen sc on sc.studnr = s.studnr
104left join Cursussen c on c.cursusnr = sc.cursusnr
105order by s.familienaam, s.voornaam
106
107/*
10811. Geef de gegevens van de studenten die alle cursussen volgen.
109*/
110--Er bestaat geen cursus die ze niet volgen
111select *
112from studenten
113where not exists (
114 select *
115 from cursussen
116 where cursusnr not in (select cursusnr
117 from studenten_cursussen
118 where studnr = studenten.studnr))
119--ook
120
121select *
122from studenten
123where (select COUNT(*)
124 from studenten_cursussen sc
125 where sc.studnr = studenten.studnr)
126 =
127 (select COUNT(*)
128 from cursussen)
129
130
131
132/*
13312. Geef de gegevens van de studenten die het hoogst aantal cursussen volgen.
134*/
135select top 1 with ties *,
136 (select count(*)
137 from studenten_cursussen
138 where studnr = studenten.studnr) as aantal
139from studenten
140order by aantal desc
141
142
143/*
14413. Geef de gegevens van de cursussen met het hoogst aantal ingeschreven studenten.
145*/
146select top 1 with ties *,
147 (select count(*)
148 from studenten_cursussen
149 where cursusnr = cursussen.cursusnr) as aantal
150from cursussen
151order by aantal desc
152
153
154/*
15514. Geef de gegevens van de cursussen waarvoor uitsluitend vrouwen zijn ingeschreven.
156*/
157select MAX(c.cursusnr) as cursusnr, c.cursusnaam, MAX(c.inschrijvingsgeld) as inschrijvingsgeld from cursussen c
158join studenten_cursussen sc on sc.cursusnr = c.cursusnr
159where studnr in (
160 select studnr
161 from studenten
162 where geslacht='V'
163 )
164group by c.cursusnaam
165
166select *
167from cursussen
168where cursusnr in (
169 select cursusnr
170 from studenten_cursussen
171 where studnr not in (
172 select studnr
173 from studenten
174 where geslacht = 'M'
175 )
176 )
177
178/*
17915. Geef de gegevens van de studenten die een naamgenoot hebben in de school.
180Een naamgenoot is een persoon met dezelfde familienaam. Sorteer de lijst alfabetisch
181op familienaam en voornaam.
182*/
183select s1.*
184from studenten s1
185where familienaam in (
186 select familienaam
187 from studenten s2
188 where s1.familienaam = s2.familienaam and s1.studnr != s2.studnr
189 )
190order by familienaam, voornaam
191/*
19216. Als de kolom email leeg is, vul dan deze kolom op met een standaard e-mailadres:
193voornaam.familienaam@howest.be. Hierbij moeten spaties in de naam vervangen worden
194door punten en moeten ‘ in de naam verwijderd worden.
195*/
196update studenten
197set email = replace(replace(voornaam + '.' + familienaam,' ','.'),'''','') + '@howest.be'
198where email is null
199
200update studenten
201set email = replace(voornaam + '.' + familienaam,' ','.') + '@howest.be'
202where email is null
203
204/*
20517. Maak een nieuwe tabel studentenOud. Vul deze nieuwe tabel met de studenten die
206geboren zijn in 1980 of eerder.
207*/
208select *
209into studentenOud
210from studenten
211where year(geboortedatum) <= 1985
212
213/*
21418. Voeg aan de tabel studentenOud de auteurs uit de pubs-databank toe.
215*/
216insert into studentenOud(voornaam, familienaam)
217 select au_fname, au_lname
218 from pubs.dbo.authors
219
220/*
22119. Verwijder uit de tabel studentenOud de studenten vanaf de letter V (familienaam).
222*/
223delete from studentenOud where familienaam > 'v%'
224
225delete from studentenOud
226where left(familienaam, 1) >= 'V'
227
228/*
22920. Maak een view vStudentenMetCursussen die de studenten weergeeft met hun cursussen.
230Gebruik de view om een alfabetische lijst te bekomen.
231*/
232
233create view vStudentenMetCursussen
234as
235select s.studnr, s.voornaam as studentvoornaam, s.familienaam as studentfamilienaam,
236 c.cursusnr, c.cursusnaam
237from studenten s
238join studenten_cursussen sc on sc.studnr = s.studnr
239join cursussen c on sc.cursusnr = c.cursusnr
240go
241select * from vStudentenMetCursussen
242order by studentfamilienaam, studentvoornaam
243
244/*
245=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
246*/
247
248/* ********* Oefeningenreeks 2
249*
250* Databank SCHOOL
251* ***********************************************************************************
252* Studenten (studnr, voornaam, familienaam, geboortedatum, geslacht, betaald, email)
253* Cursussen (cursusnr, cursusnaam, inschrijvingsgeld)
254* Studenten_Cursussen (studnr, cursusnr)
255*
256*
257* --> Na elke oefening mag de trigger verwijderd worden.
258*
259*/
260
261/*
2621. Welke index ligt op de tabel studenten? Is dit een clustered of non-clustered index?
263*/
264pk_studenten, clustered index op primaire sleutel (studnr)
265/*
2662. Zorg dat deze index non-clustered wordt.
267*/
268alter table studenten_cursussen
269 drop constraint fk1_stud_curs
270alter table studenten
271 drop constraint pk_studenten
272alter table studenten
273 add constraint pk_studenten PRIMARY KEY NONCLUSTERED(studnr)
274alter table studenten_cursussen
275 add constraint fk1_stud_curs FOREIGN KEY(studnr) REFERENCES studenten(studnr)
276
277
278/*
2793. Maak een clustered index aan op de kolom familienaam.
280*/
281create clustered index iFamilienaam on studenten(familienaam)
282
283/*
2844. Geef een lijst van alle studenten. Is er iets gewijzigd aan deze lijst nadat de clustered index werd aangemaakt?
285*/
286/* --> De rijen staan alfabetisch op familienaam */
287
288/*
2895. Voeg een kolom woonplaats toe aan de tabel studenten. Kies een gepast datatype.
290*/
291alter table studenten
292add woonplaats nvarchar(80)
293/*
2946. Zorg ervoor dat, als een student verwijderd wordt, ook al zijn inschrijvingen verwijderd worden.
295*/
296alter table studenten_cursussen
297drop constraint fk1_stud_curs
298
299alter table studenten_cursussen
300add constraint fk1_stud_curs FOREIGN KEY(studnr) REFERENCES studenten(studnr)
301ON DELETE CASCADE
302
303
304/*
3057. De kolom betaald mag enkel positieve bedragen bevatten.
306*/
307alter table studenten
308add constraint ch_betaald check (betaald >= 0)
309
310
311/*
3128. Voeg een kolom meter/peter toe. Een meter of peter is een medestudent die in de tabel studenten is opgenomen.
313*/
314alter table studenten
315add meter int foreign key references studenten(studnr)
316
317alter table studenten
318add peter int foreign key references studenten(studnr)
319
320/*
3219. Geef aan enkele studenten een meter of peter.
322*/
323update studenten
324set meter = 74
325where studnr in (1,2,3)
326
327update studenten
328set peter = 76
329where studnr = 4
330
331
332
333/*
33410. Geef een lijst van de studenten die een meter of peter hebben, samen met de gegevens van deze meter of peter.
335*/
336select s1.studnr, s1.voornaam, s1.familienaam,
337s2.studnr, s2.voornaam, s2.familienaam
338from studenten s1
339join studenten s2 on s1.meter = s2.studnr
340order by s1.studnr
341
342
343/*
34411. Een cursus wordt gegeven door 1 leraar. Maak een tabel leraars met leraargegevens en zorg dat de gepaste relaties gelegd worden.
345*/
346create table leraars (
347 leraarnr int identity primary key,
348 voornaam nvarchar(50),
349 familienaam nvarchar(50)
350 )
351
352alter table cursussen
353add leraarnr int foreign key references leraars(leraarnr)
354
355
356
357/*
35812. Vul de leraarstabel op met de auteurs uit de state CA, uit de databank pubs.
359*/
360insert into leraars(voornaam, familienaam)
361select au_fname, au_lname
362from pubs.dbo.authors
363where state = 'CA'
364
365/*
36613. Ken aan elke cursus een leraar toe.
367*/
368update cursussen
369 set leraarnr = 1
370 where cursusnr = 1
371update cursussen
372 set leraarnr = 5
373 where cursusnr = 2
374update cursussen
375 set leraarnr = 6
376 where cursusnr = 3
377update cursussen
378 set leraarnr = 10
379 where cursusnr = 4
380update cursussen
381 set leraarnr = 12
382 where cursusnr = 5
383
384
385
386/*
387=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
388*/
389
390/*********** Oefeningenreeks 3
391=> Databank SCHOOL
392
393Studenten (studnr, voornaam, familienaam, geboortedatum, geslacht, betaald, email)
394Cursussen (cursusnr, cursusnaam, inschrijvingsgeld)
395Studenten_Cursussen (studnr, cursusnr)
396
397**/
398
399
400/**
4011. Schrijf een stored procedure vulEmail die voor de studenten waarvan de kolom email leeg is,
402de kolom email opvult met een standaard e-mailadres: voornaam.familienaam@howest.be.
403Hierbij moeten spaties in de naam vervangen worden door punten en moeten ‘ in de naam verwijderd worden..
404**/
405/*----------Creating the statemmnt */
406create procedure vulEmail
407as
408set nocount on
409update studenten
410set email = voornaam + '.' + replace(familienaam, ' ', '') + '@howest.be';
411
412/*-----------Excecuting the created statement */
413exec vulEmail
414
415/*-----------Checking the results */
416select *
417from studenten
418go
419
420/**
4212. Voorbereidend werk: voeg in de tabel studenten een nieuwe kolom toe: boete (type: int).
422Schrijf een stored procedure berekenBoetes die voor alle studenten de kolom boete opvult.
423Als een student 18 jaar is of ouder
424én hij volgt minstens 2 cursussen
425én hij heeft nog niets betaald,
426dan krijgt hij een boete: 50% van het inschrijvingsgeld dat hij zou moeten betalen.
427In het andere geval wordt de boete 0.
428**/
429alter table studenten
430add boete int
431go
432create procedure berekenBoetes
433as
434set nocount on
435update studenten
436set boete = (select isnull(sum(c.inschrijvingsgeld), 0)/2 as bedragboete
437 from studenten_cursussen sc
438 join cursussen c on sc.cursusnr = c.cursusnr
439 where sc.studnr = ss.studnr)
440from studenten ss
441where (year(getdate()) - year(geboortedatum) >= 18)
442 and studnr IN
443 (select studnr
444 from studenten_cursussen
445 group by studnr
446 having count(*) >= 2)
447 and (betaald = 0 or betaald is null)
448
449update studenten
450set boete = 0
451where not((year(getdate()) - year(geboortedatum) >= 18)
452 and studnr IN
453 (select studnr
454 from studenten_cursussen
455 group by studnr
456 having count(*) >= 2)
457 and (betaald = 0 or betaald is null))
458
459exec berekenBoetes
460
461select * from studenten
462go
463
464
465/*
4663. Schrijf een stored procedure betaal die voor een bepaalde student het te betalen bedrag
467(som van de inschrijvingsgelden) betaalt (kolom betaald invullen) en de eventuele boete vereffent (op 0 zet).
468*/
469create procedure betaal
470 @studnr int
471as
472set nocount on
473update studenten
474set betaald = (
475 select isnull(sum(c.inschrijvingsgeld), 0)
476 from studenten_cursussen sc
477 join cursussen c on sc.cursusnr = c.cursusnr
478 where sc.studnr = @studnr
479 ),
480 boete = 0
481where studnr = @studnr
482
483
484
485/*
4864. Schrijf een stored procedure betaalAlleStudenten die voor alle studenten het te betalen
487bedrag betaalt en de eventuele boete vereffent.
488*/
489create procedure betaalAlleStudenten
490as
491set nocount on
492update studenten
493set betaald =
494 (select isnull(SUM(inschrijvingsgeld), 0)
495 from studenten_cursussen sc
496 join cursussen c on sc.cursusnr = c.cursusnr
497 where sc.studnr = ss.studnr),
498 boete = 0
499from studenten ss
500
501/*
5025. Schrijf een stored procedure verwijderInschrijvingen. Deze stored procedure verwijdert
503de inschrijvingen van alle mannen die jonger zijn dan 18 jaar.
504*/
505create procedure verwijderInschrijvingen
506as
507set nocount on
508delete from studenten_cursussen
509where studnr in (
510select studnr
511from studenten
512where geslacht = 'M' and datediff(year, geboortedatum, getdate()) < 18) --and year(getdate())-year(geboortedatum) < 18)
513
514exec verwijderInschrijvingen
515
516/*
5176. Schrijf een stored procedure studentenlijst1 met 2 inputparameters: geboortejaar en geslacht.
518Deze procedure levert de studenten die geboren zijn na het opgegeven geboortejaar en met het opgegeven geslacht.
519*/
520create procedure studentenlijst1
521 @geboortejaar int,
522 @geslacht char(1)
523as
524set nocount on
525select *
526from studenten
527where year(geboortedatum) > @geboortejaar and geslacht = @geslacht
528
529go
530exec studentenlijst1 1980, 'M'
531exec studentenlijst1 @geboortejaar = 1980, @geslacht = 'V'
532
533
534/*
5357. Schrijf een stored procedure studentenlijst2 die het resultaat van de stored procedure
536studentenlijst1 verder gebruikt. De verkregen studenten worden afgeleverd met de cursussen die ze volgen.
537*/
538create procedure studentenlijst2
539 @geboortejaar int,
540 @geslacht char(1)
541as
542set nocount on
543select s.*, c.*
544from studenten s
545join studenten_cursussen sc on sc.studnr = s.studnr
546join cursussen c on c.cursusnr = sc.cursusnr
547where geslacht = @geslacht and year(geboortedatum) > @geboortejaar
548
549go
550exec studentenlijst2 1980, 'M'
551exec studentenlijst2 @geboortejaar = 1980, @geslacht = 'V'
552
553/* --------------2nd method --------------- */
554create procedure studentenlijst2
555 @geboortejaar int,
556 @geslacht char(1)
557as
558set nocount on
559create table #temp --een tijdelijke tabel
560(studnr INT NOT NULL,
561 voornaam VARCHAR(30),
562 familienaam VARCHAR(30),
563 geboortedatum DATETIME,
564 geslacht CHAR(1),
565 betaald INT DEFAULT 0,
566 email varchar(50) DEFAULT NULL,
567 boete INT,
568 CONSTRAINT pk_studenten PRIMARY KEY (studnr)
569)
570insert into #temp
571 exec studentenlijst1 @geboortejaar, @geslacht
572select *
573 from #temp
574 left join studenten_cursussen sc on #temp.studnr = sc.studnr
575 left join cursussen c on sc.cursusnr = c.cursusnr
576go
577exec studentenlijst2 1980, 'V'
578
579
580/*
5818. Schrijf een stored procedure vulTeGoed. Deze stored procedure maakt een tabel TeGoed aan.
582Als er reeds een tabel TeGoed bestaat in de databank, dan wordt deze oude tabel verwijderd en
583wordt een nieuwe tabel TeGoed aangemaakt.
584De structuur van de tabel TeGoed: studnr, tegoed.
585De tabel wordt opgevuld met het studentnummer en het te goed bedrag van de studenten die een
586bedrag te goed hebben. Het te goed bedrag is het betaalde bedrag (betaald) verminderd met de
587som van de inschrijvingsgelden volgens hun inschrijvingen.
588*/
589create procedure vulTeGoed
590as
591set nocount on
592if object_id('TeGoed', 'u') is not null
593begin
594 drop table TeGoed
595end
596
597create table TeGoed
598(studnr int primary key,
599 tegoed int)
600
601insert into TeGoed(studnr, tegoed)
602 select studnr,
603 (select avg(betaald) - sum(inschrijvingsgeld)
604 from studenten s
605 left join studenten_cursussen sc on s.studnr = sc.studnr
606 left join cursussen c on sc.cursusnr = c.cursusnr
607 where s.studnr = ss.studnr) as tegoed
608 from studenten ss
609 where ((select avg(betaald) - sum(inschrijvingsgeld)
610 from studenten s
611 left join studenten_cursussen sc on s.studnr = sc.studnr
612 left join cursussen c on sc.cursusnr = c.cursusnr
613 where s.studnr = ss.studnr)) > 0
614
615
616/*
6179. Schrijf een stored procedure vulWeekends. Deze stored procedure maakt een tabel Weekends
618aan. Als er reeds een tabel Weekends bestaat in de databank, dan wordt deze oude tabel
619verwijderd en wordt een nieuwe tabel Weekends aangemaakt.
620De structuur van de tabel Weekends: nr, datum.
621De tabel wordt opgevuld met de data van de zaterdagen en de zondagen in een bepaald jaar.
622Dit jaar wordt als inputparameter meegegeven aan de stored procedure.
623*/
624
625create procedure vulWeekends
626 @jaar int
627as
628set nocount on
629if object_id('Weekends', 'u') is not null
630begin
631 drop table Weekends
632end
633
634create table Weekends
635(nr int identity primary key,
636 datum datetime)
637
638declare
639 @startdatum datetime,
640 @startdatumstring varchar(10),
641 @stopdatum datetime,
642 @stopdatumstring varchar(10),
643 @datum datetime
644
645--select @@DATEFIRST
646--bevat de current instelling van datefirst
647set datefirst 1 --maandag is de eerste dag van de week
648set @startdatumstring = convert(char(4),@jaar)+'-01-01'
649set @startdatum = convert(datetime,@startdatumstring)
650set @stopdatumstring = convert(char(4),@jaar)+'-12-31'
651set @stopdatum = convert(datetime,@stopdatumstring)
652
653set @datum = @startdatum
654while year(@datum) = year(@stopdatum)
655begin
656 if datepart(weekday,@datum)= 6 -- zaterdag
657 begin
658 insert into Weekends(datum)
659 values(@datum)
660 set @datum = dateadd(day,1,@datum)
661 end
662 else
663 if datepart(weekday,@datum)= 7 -- zondag
664 begin
665 insert into Weekends(datum)
666 values(@datum)
667 set @datum = dateadd(day,6,@datum)
668 end
669 else
670 begin
671 set @datum = dateadd(day,1,@datum)
672 end
673end
674go
675exec vulWeekends 2017
676select * from Weekends
677
678
679
680/*
681=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
682*/
683
684/* ********* Oefeningenreeks 4
685*
686* Databank SCHOOL
687* ***********************************************************************************
688* Studenten (studnr, voornaam, familienaam, geboortedatum, geslacht, betaald, email)
689* Cursussen (cursusnr, cursusnaam, inschrijvingsgeld)
690* Studenten_Cursussen (studnr, cursusnr)
691*
692*
693* --> Na elke oefening mag de trigger verwijderd worden.
694*
695*/
696
697/*
6981. Schrijf een trigger max4InschrijvingenPerStudent.
699Een student kan maximaal maar 4 inschrijvingen hebben.
700Indien de student zich probeert in te schrijven voor meer cursussen, mag dat niet gebeuren.
701*/
702go
703create trigger max4InschrijvingenPerStudent on studenten_cursussen
704after insert
705as
706if exists
707 (select studnr
708 from studenten_cursussen
709 where studnr in (select studnr from inserted)
710 group by studnr
711 having count(*) > 4)
712begin
713 rollback transaction
714end
715
716
717/*
7182. Schrijf een trigger enkelInschrijvenZonderBoete.
719Een student met een boete kan zich niet inschrijven voor een cursus.
720*/
721go
722create trigger enkelInschrijvenZonderBoete
723on studenten_cursussen
724after insert
725as
726if exists
727 (select *
728 from inserted
729 join studenten on inserted.studnr = studenten.studnr
730 where boete != 0)
731begin
732 print 'een student met een boete kan zich niet inschrijven'
733 rollback transaction
734end
735
736--to remove it back
737drop trigger enkelInschrijvenZonderBoete
738
739-- to test it
740insert into studenten_cursussen(studnr, cursusnr) values(3, 3)
741
742
743
744/*
7453. Schrijf een trigger nietVerwijderenMetBoete. Een student met een boete kan niet verwijderd worden!
746*/
747go
748create trigger nietVerwijderenMetBoete on studenten
749after delete
750as
751if exists (select *
752 from deleted
753 where boete != 0)
754begin
755 print 'Kan niet verwijderen: heeft een boete'
756 rollback transaction
757end
758
759/*
7604. Schrijf een trigger max6InschrijvingenPerCursus. Deze trigger staat niet toe dat een cursus meer dan 6 inschrijvingen heeft.
761*/
762create trigger max6InschrijvingenPerCursus on studenten_cursussen
763after insert
764as
765if exists
766 (select cursusnr
767 from studenten_cursussen
768 where cursusnr in (select cursusnr from inserted)
769 group by cursusnr
770 having count(*) > 6)
771begin
772 rollback transaction
773end
774
775/*
7765. Schrijf een trigger geenInschrijvingen. Er is een inschrijvingsstop: er mogen geen inschrijvingen meer gebeuren!
777*/
778create trigger geenInschrijvingenon on studenten_cursussen
779instead of insert
780as
781 print 'Er is een inschrijvingsstop!'
782
783/*
7846. Schrijf een trigger controleUpdateBetaald. Als de kolom betaald gewijzigd wordt, dan mag het nieuwe ingevulde
785bedrag niet groter zijn dan de som van de inschrijvingsgelden die moeten betaald worden volgens de inschrijvingen.
786*/
787
788create trigger controleUpdateBetaald on studenten
789after update, insert
790as
791if(update(betaald))
792begin
793 if exists
794 (select i.studnr
795 from inserted i
796 left join studenten_cursussen sc on i.studnr = sc.studnr
797 left join cursussen c on sc.cursusnr = c.cursusnr
798 group by i.studnr
799 having isnull(avg(betaald),0) > isnull(sum(inschrijvingsgeld),0))
800 begin
801 rollback transaction
802 end
803end
804
805
806/*
8077. Schrijf een trigger geenNieuweTabel. Er mag geen nieuwe tabel aangemaakt worden in de databank School.
808*/
809create trigger geenNieuweTabel on database
810after create_table
811as
812 print 'Er mag geen nieuwe tabel aangemaakt worden!'
813 rollback transaction
814
815
816/*
8178. Schrijf een trigger geenNieuweDatabank. Er mag geen nieuwe databank aangemaakt worden.
818*/
819
820create trigger geenNieuweDatabank on all server
821after create_database
822as
823 print 'Er mag geen nieuwe databank aangemaakt worden!'
824 --geef de tekst van het SQL-statement dat de trigger geactiveerd heeft
825 --select eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(1000)')
826 rollback transaction
827
828
829
830/*
831=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
832*/
833
834/* ********* Oefeningenreeks 5
835*
836* Databank SCHOOL
837* ***********************************************************************************
838* Studenten (studnr, voornaam, familienaam, geboortedatum, geslacht, betaald, email)
839* Cursussen (cursusnr, cursusnaam, inschrijvingsgeld)
840* Studenten_Cursussen (studnr, cursusnr)
841*
842*/
843
844/*
8451. Schrijf een scalaire functie fAantalStudenten die het aantal studenten aflevert voor een bepaalde cursus.
846Gebruik deze functie om een lijst te leveren van alle cursussen met het aantal ingeschreven studenten.
847*/
848go
849if exists(select name, type from sys.objects where name='fAantalStudenten' and type = 'FN')
850drop function fAantalStudenten
851go
852create function fAantalStudenten(@cursusnr int)
853returns int
854as
855begin
856declare @aantal int
857set @aantal = (select count(*)
858 from studenten_cursussen
859 where cursusnr=@cursusnr
860 group by cursusnr )
861return @aantal
862end
863/*testing the function */
864go
865select c.*, dbo.fAantalStudenten(c.cursusnr) as aantal
866from cursussen c
867
868
869/*
8702. Schrijf een scalaire functie fTeBetalen die aangeeft hoeveel een bepaalde student nog moet betalen volgens zijn inschrijvingen.
871Gebruik deze functie om een lijst te leveren van alle studenten met het bedrag dat ze nog moeten betalen.
872*/
873go
874if exists(select name, type from sys.objects where name='fTeBetalen' and type = 'FN')
875drop function fTeBetalen
876go
877create function fTeBetalen(@studnr int)
878returns int
879as
880begin
881declare @nogteBetalen int
882set @nogteBetalen = (select (isnull(sum(inschrijvingsgeld),0) - avg(betaald)) as diff
883from studenten s
884join studenten_cursussen sc on sc.studnr = s.studnr
885join cursussen c on sc.cursusnr = c.cursusnr
886where s.studnr = @studnr)
887return @nogteBetalen
888end
889/* testing the function */
890go
891select s.*, dbo.fTeBetalen(s.studnr) as NogTeBetallen
892from studenten s
893
894
895/*
8963. Schrijf een scalaire functie fTileCase die een tekst omzet naar ‘tile case’. Dit betekent: de eerste letter van elk woord
897is een hoofdletter, de rest zijn kleine letters.
898*/
899create function fTileCase(@strIn nvarchar(100))
900returns nvarchar(100)
901as
902begin
903 declare @strOut nvarchar(100), --outputstring
904 @currentPosition int,
905 @nextSpace int,
906 @strLen int,
907 @lastWord bit --waarde 0 of 1 of NULL
908
909 set @strOut = ''
910 set @currentPosition = 1
911 set @strLen = len(@strIn)
912 set @lastWord = 0
913
914 while(@lastWord = 0)
915 begin
916 set @nextSpace = charindex(' ', @strIn, @currentPosition)
917 if(@nextSpace is null or @nextSpace = 0)
918 -- @strIn is NULL of geen spatie gevonden
919 begin
920 set @lastWord = 1
921 set @nextSpace = @strLen
922 end
923 set @strOut = @strOut
924 + upper(substring(@strIn, @currentPosition, 1))
925 + lower(substring(@strIn, @currentPosition+1, @nextSpace-@currentPosition))
926 set @currentPosition = @nextspace + 1
927 end
928
929 return @strOut
930end
931
932go
933select dbo.fTileCase('RAF VAN DE WOESTIJNE')
934go
935select * , dbo.fTileCase(voornaam), dbo.fTileCase(familienaam)
936from studenten
937go
938
939
940/*
9414. Schrijf een functie fStudentenlijst die een tabel aflevert met de studenten geboren voor een opgegeven jaar.
942Bij elke student wordt ook de som van zijn inschrijvingsgelden vermeld.
943*/
944create function fStudentenlijst(@geboortejaar int = 1986)
945returns table
946as
947return (
948 select *,
949 (select isnull(sum(inschrijvingsgeld), 0)
950 from studenten_cursussen sc
951 join cursussen c on sc.cursusnr = c.cursusnr
952 where sc.studnr = studenten.studnr) as [som inschr.geld]
953 from studenten
954 where year(geboortedatum) < @geboortejaar
955)
956
957go
958
959select *
960 from dbo.fStudentenlijst(1999)
961
962select *
963from dbo.fStudentenlijst(default)
964--Hierop kan ev. verder gewerkt worden met een join
965
966
967
968
969/*
970=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
971*/
972
973/* ********* Oefeningenreeks 7
974
975Databank SCHOOL
976Studenten (studnr, voornaam, familienaam, geboortedatum, geslacht, betaald, email)
977Cursussen (cursusnr, cursusnaam, inschrijvingsgeld)
978Studenten_Cursussen (studnr, cursusnr)
979*/
980
981/*
9821. Lees alle FAQ uit
983https://technet.microsoft.com/en-us/library/ms345522(v=sql.105).aspx.
984Kies 2 vragen, bestudeer en voer de oplossing uit voor de databank school.
985
986Vraag 1: How do I find all the user-defined tables in a specified database?
987*/
988select *
989from sys.tables
990
991/*
992Vraag 2: How do I find all the stored procedures in a database?
993*/
994SELECT name AS procedure_name
995 ,SCHEMA_NAME(schema_id) AS schema_name
996 ,type_desc
997 ,create_date
998 ,modify_date
999FROM sys.procedures;
1000
1001/*
10022. Geef alle stored procedures in de databank. Geef 2 oplossingen: een oplossing gebruikmakend van catalog
1003views en een oplossing gebruikmakend van information schema views.
1004(a) Catalog view */
1005select * from sys.procedures
1006
1007/* (b) Schema views */
1008select * from INFORMATION_SCHEMA.ROUTINES
1009where ROUTINE_TYPE = 'Procedure'
1010
1011/*
10123. Geef alle stored procedures die gewijzigd zijn in de laatste 100 (50) dagen. Opnieuw 2 oplossingen geven.
1013*/
1014
1015/* (a) */
1016select *
1017from sys.procedures
1018where datediff(dd, modify_date, Current_Timestamp) > 50
1019
1020/* (b) */
1021select *
1022from INFORMATION_SCHEMA.ROUTINES
1023where ROUTINE_TYPE = 'Procedure' and
1024datediff(dd, LAST_ALTERED, CURRENT_TIMESTAMP) > 50
1025
1026/*
10274. Welke kolommen zijn de primary key in studenten_cursussen? Geef 3 oplossingen: een oplossing gebruikmakend van
1028system stored procedures, een oplossing gebruikmakend van information schema views en een oplossing gebruikmakend van catolog views.
1029Voor de oplossing met de catalog views: verken eerst sys.indexes, sys.index_columns en sys.columns. Join dan deze views op de juiste wijze.
1030*/
1031
1032--met system stored procedure
1033exec sp_pkeys studenten_cursussen
1034
1035--met information schema views
1036select *
1037from [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
1038where table_name = 'studenten_cursussen'
1039and constraint_name like 'pk%'
1040
1041--met catalog views
1042--eerst verkennen van sys.indexes, sys.index_columns en sys.columns
1043select *
1044from sys.indexes
1045where object_id = object_id('studenten_cursussen')
1046
1047select *
1048from sys.index_columns
1049where object_id = object_id('studenten_cursussen')
1050
1051select *
1052from sys.columns
1053where object_id = object_id('studenten_cursussen')
1054
1055--daarna alles aan elkaar hangen (join)
1056select *
1057from sys.indexes i
1058join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
1059join sys.columns c on ic.object_id = c.object_id AND ic.column_id = c.column_id
1060where i.object_id = object_id('studenten_cursussen')
1061and i.is_primary_key = 1
1062
1063/*
10645. Wat is de Collation in de databank?
1065*/
1066select databasepropertyex('school', 'collation') as collation
1067
1068/*
1069=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
1070*/
1071
1072/* --------> Object_Id */
1073if (OBJECT_ID('studenten') is not null)
1074select OBJECT_ID('studenten')
1075else
1076print 'there is no ID with this name'
1077
1078/* --------> Object_Name */
1079if (OBJECT_NAME('565577053') is not null)
1080select OBJECT_NAME('565577053')
1081else
1082print 'there is no object with this id'
1083
1084/* --------> User ID */
1085if (USER_ID('guest') is not null)
1086select USER_ID('guest')
1087else
1088print 'there is no user-id with this name'
1089
1090/* --------> User Name */
1091if (USER_NAME(2) is not null)
1092select USER_NAME(2)
1093else
1094print 'there is no user name with this ID'
1095
1096/* --------> database property */
1097if (DATABASEPROPERTYEX('school','status') is not null)
1098select DATABASEPROPERTYEX('school','status')
1099else
1100print 'the database is not online (its property "status" gives a null value'