resolucao_procedures_functions.sql

USE WorldEvents;
GO
-- 1. Crie uma stored procedure chamada uspCountriesAsia que liste todos os países com ContinentId igual a 1, 
-- em ordem alfabética.
CREATE PROCEDURE uspCountriesAsia
AS
SELECT CountryName from tblCountry WHERE ContinentID = 1 ORDER BY CountryName

EXEC uspCountriesAsia

-- 2. Agora crie uma segunda stored procedure chamada uspCountriesByContinentId 
-- que receba um parâmetro do tipo INTEGER que representa o id do continente, 
-- que seja uma forma genérica da procedure anterior, ou seja, retorne os países cujo continente 
-- contém o ID passado por parâmetro.
CREATE PROCEDURE uspCountriesByContinentId @ContinentID INTEGER
AS
SELECT CountryName from tblCountry WHERE ContinentID = @ContinentID ORDER BY CountryName

EXEC uspCountriesByContinentId 3

-- 3. Crie uma stored procedure chamada uspCountriesByContinentName, 
-- que receba um atributo do tipo VARCHAR e retorne os países que pertencem 
-- ao continente (nome, neste caso) passado por parâmetro. Verifique a tabela 
-- tblContinent para ver os nomes de continentes disponíveis.
CREATE PROCEDURE uspCountriesByContinentName @ContinentName VARCHAR(255)
AS
SELECT CountryName from tblCountry, tblContinent 
WHERE tblCountry.ContinentID = tblContinent.ContinentID
AND ContinentName = @ContinentName
ORDER BY CountryName

EXEC uspCountriesByContinentName 'Africa'

/*
Crie uma procedure chamada spSummariseEpisodes que mostre:

Os três companheiros que mais aparecem nas séries (nome e número de episódios)
E, em outra consulta, os três inimigos que mais aparecem (com seus números de episódios)
*/
USE DoctorWho;
GO

CREATE PROCEDURE spSummariseEpisodes 
AS
BEGIN
SELECT TOP 3 CompanionName, COUNT(*) AS 'Número de Episódios'
FROM tblCompanion c, tblEpisodeCompanion ec 
WHERE c.CompanionId = ec.CompanionId
GROUP BY CompanionName
ORDER BY COUNT(*) DESC
SELECT TOP 3 EnemyName, COUNT(*) AS 'Número de Episódios'
FROM tblEnemy e, tblEpisodeEnemy ee 
WHERE e.EnemyId = ee.EnemyId
GROUP BY EnemyName
ORDER BY COUNT(*) DESC
END

EXEC spSummariseEpisodes

/* Agora altere sua consulta para mostrar os n companheiros e m vilões com mais aparições. 
Ou seja, a procedure deve receber dois parâmetros que vão determinar a 
quantidade de companheiros e vilões a buscar do banco. 
*/
ALTER PROCEDURE spSummariseEpisodes @nCompanion INT, @nEnemy INT
AS
BEGIN
SELECT TOP (@nCompanion) CompanionName, COUNT(*) AS 'Número de Episódios'
FROM tblCompanion c, tblEpisodeCompanion ec 
WHERE c.CompanionId = ec.CompanionId
GROUP BY CompanionName
ORDER BY COUNT(*) DESC
SELECT TOP (@nEnemy) EnemyName, COUNT(*) AS 'Número de Episódios'
FROM tblEnemy e, tblEpisodeEnemy ee 
WHERE e.EnemyId = ee.EnemyId
GROUP BY EnemyName
ORDER BY COUNT(*) DESC
END

EXEC spSummariseEpisodes 4, 5

-- 6. Crie uma função chamada fnEpisodeDescription para que você possa contar quantos episódios de cada tipo existem:
/*
SELECT 
  dbo.fnEpisodeDescription(Title) AS 'Tipo de episódio', 
  COUNT(*) AS 'Número de episódios' 
FROM 
  tblEpisode 
GROUP BY 
  dbo.fnEpisodeDescription(Title)

  Episódio único -
  Primeira parte -> Part 1
  Segunda parte -> Part 2
*/
CREATE FUNCTION fnEpisodeDescription (
	@title VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
	/*
	IF (@title LIKE '%Part 1%') 
		RETURN ...
	ELSE IF (@title LINE '%Part 2%')
		RETURN ...
	*/

	IF(CHARINDEX('Part 1', @title) <> 0)
		RETURN 'Primeira parte';
	
	IF (CHARINDEX('Part 2', @title) <> 0)
		RETURN 'Segunda parte';
	
	RETURN 'Episódio único';
END

SELECT 
  dbo.fnEpisodeDescription(Title) AS 'Tipo de episódio', 
  COUNT(*) AS 'Número de episódios' 
FROM 
  tblEpisode 
GROUP BY 
  dbo.fnEpisodeDescription(Title)

-- 7. Crie uma função que liste os episódios para qualquer número de temporada e 
-- parte do nome do autor. A sua função poderia começar assim:
-- Faça com que sua função funcione assim:

-- Retorne episódios para qualquer temporada se você passar o primeiro argumento como NULL;
-- Episódios para qualquer autor se você passar o segundo argumento como NULL.
-- Alguns exemplos de como você pode usar essa função:
CREATE OR ALTER FUNCTION fnChosenEpisodes (
  @SeriesNumber int,
  @AuthorName varchar(100)
)
RETURNS TABLE
AS
RETURN
SELECT 
	Title, AuthorName, DoctorName 
FROM 
	tblEpisode e, tblAuthor a, tblDoctor d
WHERE 
	a.AuthorId = e.AuthorId
	AND d.DoctorId = e.DoctorId
	AND ((@SeriesNumber IS NULL) OR (SeriesNumber = @SeriesNumber))
	AND ((@AuthorName IS NULL) OR (AuthorName LIKE CONCAT('%', @AuthorName, '%')));


SELECT * FROM dbo.fnChosenEpisodes(NULL, NULL)
-- há 1 episódio escrito Steven Moffat para a temporada 2
SELECT COUNT(*) FROM dbo.fnChosenEpisodes(2,'moffat')
-- há 14 episódios na temporada 2 (para qualquer autor)
SELECT COUNT(*) FROM dbo.fnChosenEpisodes(2,NULL)
-- há 32 episódios escritos por Steven Moffat (para qualquer temporada)
SELECT COUNT(*) FROM dbo.fnChosenEpisodes(NULL,'moffat')
-- há 117 episódios (qualquer temporada, qualquer autor)
SELECT COUNT(*) FROM dbo.fnChosenEpisodes(null,null)