z51CtTKK

1--Views en Procedures.
2use Series_DB;
3GO
4-- 1. Maak een view waarbij je alle series met hun afleveringen laat zien sorteer deze op een omgedraaide alphabetische volgorde van de serie naam. 
5IF OBJECT_ID('EpsSeries', 'V') IS NOT NULL
6    DROP VIEW EpsSeries;
7GO
8create view EpsSeries 
9as
10select e.Seizoen, e.Nummer, e.airdate, e.Omschrijving as [Episode Omschrijving], s.Naam, s.Omschrijving from Serie s
11left join Episode e on e.serie = s.Naam
12GO
13
14
15-- 2. Maak een view waarbij je het totaal aantal afleveringen per serie kan zien.
16IF OBJECT_ID('TotaalEpsPer', 'V') IS NOT NULL
17    DROP VIEW TotaalEpsPer;
18GO
19create view TotaalEpsPer as 
20select count(e.serie) [Aantal Eps],e.serie from Episode e group by e.serie;
21GO
22-- 3. Maak een view waarbij je de eerste aflevering van een serie kan opvragen.
23IF OBJECT_ID('EersteEp', 'V') IS NOT NULL
24    DROP VIEW EersteEp;
25GO
26Create view EersteEp as
27select * from episode e where episodeId = (select episodeid from episode sub_e where sub_e.serie = e.serie AND Nummer = 1 AND Seizoen = 1) 
28GO
29-- 4. Maak een view waarin alle acteurs uit 1978 te zien zijn.
30IF OBJECT_ID('ActeursVan1978', 'V') IS NOT NULL
31    DROP VIEW ActeursVan1978;
32GO
33CREATE VIEW ActeursVan1978
34AS
35SELECT * FROM acteur WHERE geboortedatum between '1978-01-01' and '1978-12-31'
36GO
37-- 5. Maak een view die de nieuwste aflevering van een serie laat zien.
38IF OBJECT_ID('LaatsteEp', 'V') IS NOT NULL
39    DROP VIEW LaatsteEp;
40GO
41CREATE VIEW LaatsteEp
42AS
43SELECT e.airdate, e.serie from Episode e where EpisodeId = (select Max(EpisodeId) from Episode sub_e where e.serie = sub_e.serie) group by e.serie, airdate
44GO
45
46
47-- 6. Maak een procedure die een nieuwe aflevering kan toevoegen voor de serie Lucifer.
48IF OBJECT_ID('addNewLuciferEp', N'P') IS NOT NULL
49    DROP Procedure addNewLuciferEp;
50GO
51Create Procedure addNewLuciferEp @Naam varchar(255), @Seizoen int,@Nummer int,@Omschrijving text
52AS
53Insert into Episode (Naam,Seizoen,Nummer,Omschrijving,airdate,serie) values(@Naam,@Seizoen,@Nummer,@Omschrijving,GETDATE(),'Lucifer');
54GO
55
56-- 7. Maak een trigger als de serie naam niet wordt gevonden dat deze automatisch wordt aangemaakt.
57IF OBJECT_ID('checkForSerie', N'TR') IS NOT NULL
58    DROP trigger  checkForSerie;
59GO
60create trigger checkForSerie
61On episode
62INSTEAD of INSERT
63AS
64BEGIN
65	DECLARE @serieNaam varchar;
66	select @serieNaam = inserted.serie  from inserted;
67	if NOT exists(select * from serie where serie.Naam = @serieNaam)
68		insert into Serie values(@serieNaam,'Not Defined');
69END
70GO
71
72
73-- 8. Maak een procedure die eerst zal kijken of dat de record al bestaat voordat deze wordt ingevoerd doe dit voor de tabel Episode.
74IF OBJECT_ID('addNewLuciferEp', N'P') IS NOT NULL
75    DROP Procedure addNewLuciferEp;
76GO
77Create Procedure addNewLuciferEp @Naam varchar(255), @Seizoen int,@Nummer int,@Omschrijving text, @serie varchar(255)
78AS
79if (not exists (select * from Episode where Nummer = @Nummer and seizoen = @Seizoen and serie =@serie))
80	Insert into Episode (Naam,Seizoen,Nummer,Omschrijving,airdate,serie) values(@Naam,@Seizoen,@Nummer,@Omschrijving,GETDATE(),@serie);
81GO
82
83
84
85-- 9. Maak een functie die de acteurs tabel terug stuurt met de acteurs van de opgegeven serie.
86IF OBJECT_ID (N'ActeursInSerie', N'IF') IS NOT NULL  
87    DROP FUNCTION ActeursInSerie;  
88GO    
89CREATE FUNCTION ActeursInSerie
90(@V_SerieName varchar(255))
91RETURNS TABLE
92RETURN(
93	select distinct a.naam from Acteur a
94	left join ActeurEpisode ae on a.ActeurId =ae.Acteur
95	left join Episode e on e.EpisodeId =ae.Episode
96	Where e.serie = @V_SerieName
97);
98GO  
99--select * from ActeursInSerie('Lucifer');
100
101
102-- 10. Maak een functie die laat zien hoeveel afleveringen een acteur in speelt. Acteur is variabel.
103IF OBJECT_ID (N'EpsPerAct', N'FN') IS NOT NULL  
104    DROP FUNCTION EpsPerAct;  
105GO    
106CREATE FUNCTION EpsPerAct 
107(@V_personName varchar(255))	
108RETURNS INT
109AS
110BEGIN
111	Declare @V_select int
112
113	select @V_select = sum(ae.Episode) from ActeurEpisode ae
114	left join Acteur a on a.ActeurId = ae.Acteur 
115	where a.naam = @V_personName
116
117     IF (@V_select IS NULL)   
118        SET @V_select = 0
119	return @V_select
120end
121GO
122--select dbo.EpsPerAct('Lauren German') as [Eps Per Act];