SQL 13.1
-- Opdracht 13.1

USE DoctorWho;
GO

-- 1. Maak een view aan waarin je de naam van de auteur, naam van de doctor, title en datum van de aflevering plaatst.
--	  Hier mogen alleen de afleveringen in die beginnen met de letter F.
CREATE VIEW vAfleveringF
AS
SELECT AuthorName, DoctorName, Title, EpisodeDate
FROM tblEpisode AS Episode
JOIN tblDoctor AS Doctor
ON Doctor.DoctorId = Episode.DoctorId
JOIN tblAuthor AS Author
ON Author.AuthorId = Episode.AuthorId
WHERE Title LIKE 'F%'
GO

-- 2. Toon de inhoud van de zojuist aangemaakte view.
SELECT *
FROM vAfleveringF

-- 3. Toon uit de view de afleveringen van auteur 'Steven Moffat'.
SELECT *
FROM vAfleveringF
WHERE AuthorName = 'Steven Moffat'

-- 4. Pas de bestaande view aan en plaats alleen de afleveringen die beginnen met de letter A.
GO
ALTER VIEW vAfleveringF
AS
SELECT AuthorName, DoctorName, Title, EpisodeDate
FROM tblEpisode AS Episode
JOIN tblDoctor AS Doctor
ON Doctor.DoctorId = Episode.DoctorId
JOIN tblAuthor AS Author
ON Author.AuthorId = Episode.AuthorId
WHERE Title LIKE 'A%'
GO

-- 5. Toon uit de view de afleveringen van doctor 'Matt Smith'.
SELECT *
FROM vAfleveringF
WHERE DoctorName = 'Matt Smith'

-- 6. Maak een nieuwe view aan waarin je het serienummer, afleveringnummer, titel en de vijand(en) van de aflevering plaatst.
--    Afleveringen waarin meerdere vijanden zitten, zullen meerdere records hebben.
GO
CREATE VIEW vAfleveringVijand
AS
SELECT SeriesNumber, EpisodeNumber, Title, EnemyName
FROM tblEpisode AS Episode
JOIN tblEpisodeEnemy AS EpisodeEnemy
ON Episode.EpisodeId = EpisodeEnemy.EpisodeId
JOIN tblEnemy AS Enemy
ON Enemy.EnemyId = EpisodeEnemy.EnemyId
GO

-- 7. Toon het resultaat van de zojuist aangemaakte view en sorteer het resultaat op serienummer en dan op afleveringnummer.
SELECT *
FROM vAfleveringVijand
ORDER BY SeriesNumber ASC, EpisodeNumber ASC;

-- 8. Toon de afleveringen uit de view (op alfabetische volgorde) met vijand 'Cybermen'
SELECT Title
FROM vAfleveringVijand
WHERE EnemyName = 'Cybermen'
ORDER BY Title ASC;

-- 9. Geef, met behulp van de view, per vijand weer in hoeveel afleveringen hij/zij voorkomt.
--    Toon de vijandnaam en het aantal afleveringen.
--	  Sorteer de lijst op aantal afleveringen van hoog naar laag.



-- 10. Verwijder beide views (2 queries)
DROP VIEW vAfleveringVijand;
GO
DROP VIEW vAfleveringF;
GO
=====================================
SQL 13.2
-- Opdracht 13.2

USE WorldEvents;
GO

-- 1a. Maak een view van alle events.
--     Toon hiervan de kolommen op de volgende manier:
--     * eventnaam: Hetzelfde als in de tabel
--	   * datum van het event: Op de Nederlandse manier, dus dag-maand-jaar, bijv. 20-3-2018.
--     * landnaam: naam van het land ( naam van het continent ), bijv. 'Netherland (Europe)'.
GO
CREATE VIEW vEvents
AS
SELECT EventName, CONVERT(VARCHAR(20), EventDate, 103) AS Datum, CountryName + '(' + ContinentName + ')' AS Country
FROM tblEvent AS Event
JOIN tblCountry AS Country
ON Event.CountryID = Country.CountryID
JOIN tblContinent AS Continent
ON Country.ContinentID = Continent.ContinentID

SELECT *
FROM vEvents
-- 1b. Pas de view, die je bij 1a hebt gemaakt, aan zodat alleen de events die plaatsvonden in Europa worden getoond.
GO
ALTER VIEW vEvents
AS
SELECT EventName, CONVERT(VARCHAR(20), EventDate, 103) AS Datum, CountryName + '(' + ContinentName + ')' AS Country
FROM tblEvent AS Event
JOIN tblCountry AS Country
ON Event.CountryID = Country.CountryID
JOIN tblContinent AS Continent
ON Country.ContinentID = Continent.ContinentID
WHERE ContinentName = 'Europe'
GO

-- 1c. Toon alle inhoud van de view
SELECT *
FROM vEvents

-- 1d. Toon de inhoud van de view voor de evenementen die plaatsvonden in Frankrijk.
SELECT *
FROM vEvents
WHERE Country = 'France(Europe)'

-- 1e. Wat is een nadeel van een view bij de query van 1d?
--Dat je niet alleen het land kan invullen bij de Where omdat dat samen is gevoegd met het continent

-- 1f. Verwijder de view
GO
DROP VIEW vEvents
GO

SELECT EventDate
FROM tblEvent
-- 2a. Maak een nieuwe view met alle gebeurtenissen van 1980 t/m 1989.
GO
CREATE VIEW vGebeurtenissen
AS
SELECT EventName, CONVERT(VARCHAR(20),EventDate, 103) AS Datum
FROM tblEvent AS Event
WHERE YEAR(EventDate) BETWEEN 1980 AND 1989
GO

-- 2b. Toon met de view alle events die plaats hebben gevonden in maart.
SELECT *
FROM vGebeurtenissen
WHERE Datum LIKE '%03%'

-- 3a. Maak een view met daarin alle landen en per land het aantal event dat daar hebben plaatsgevonden.
-- 	   Geef per land ook aan wanneer het eerste en laatste event plaatsvond.
GO
CREATE VIEW vLanden
AS
SELECT CountryName, COUNT(Event.CountryID) AS Aantal
FROM tblEvent AS Event
JOIN tblCountry AS Country
ON Country.CountryID = Event.CountryID
GROUP BY CountryName

-- 3b. Toon met een SELECT de landen met meer dan 10 events. Je mag de view nu niet gebruiken.
SELECT CountryName
FROM tblEvent AS Event
JOIN tblCountry AS Country
ON Country.CountryID = Event.CountryID
GROUP BY CountryName
HAVING COUNT(Event.CountryID) > 10

-- 3c. Toon met bovenstaande view de landen met meer dan 10 events.
SELECT CountryName
FROM vLanden
WHERE Aantal > 10

-- 4a. Schrijf de query waarmee je een lijst toont met alle views.
SELECT *
FROM vLanden, vGebeurtenissen, vEvents

-- 4b. Verwijder alle views die nog bestaan.
DROP VIEW vEvents, vGebeurtenissen, vLanden

-- 5. Bedenk zelf nog een logische view en maak de query.
GO
CREATE VIEW vCategory
AS
SELECT EventName, CategoryName
FROM tblEvent AS Event
JOIN tblCategory AS Category
ON Event.CategoryID = Category.CategoryID
WHERE CategoryName = 'Sports'
GO

SELECT *
FROM vCategory