Databases MSSQL Server Exam - 19 Feb 2023

-- Databases MSSQL Server Exam - 19 Feb 2023

CREATE DATABASE Boardgames
GO

USE Boardgames
GO

-- Problem 01. DDL

CREATE TABLE [Categories]
(
    [Id] INT PRIMARY KEY IDENTITY(1, 1),
    [Name] VARCHAR(50) NOT NULL
)

CREATE TABLE [Addresses]
(
    [Id] INT PRIMARY KEY IDENTITY(1, 1),
    [StreetName] NVARCHAR(100) NOT NULL,
    [StreetNumber] INT NOT NULL,
    [Town] VARCHAR(30) NOT NULL,
    [Country] VARCHAR(50) NOT NULL,
    [ZIP] INT NOT NULL
)

CREATE TABLE [Publishers]
(
    [Id] INT PRIMARY KEY IDENTITY(1, 1),
    [Name] VARCHAR(30) UNIQUE NOT NULL,
    [AddressId] INT FOREIGN KEY REFERENCES [Addresses]([Id]) NOT NULL,
    [Website] NVARCHAR(40),
    [Phone] NVARCHAR(20)
)

CREATE TABLE [PlayersRanges]
(
    [Id] INT PRIMARY KEY IDENTITY(1, 1),
    [PlayersMin] INT NOT NULL,
    [PlayersMax] INT NOT NULL
)

CREATE TABLE [Boardgames]
(
    [Id] INT PRIMARY KEY IDENTITY(1, 1),
    [Name] NVARCHAR(30) NOT NULL,
    [YearPublished] INT NOT NULL,
    [Rating] DECIMAL(18,2) NOT NULL,
    [CategoryId] INT FOREIGN KEY REFERENCES [Categories]([Id]) NOT NULL,
    [PublisherId] INT FOREIGN KEY REFERENCES [Publishers]([Id]) NOT NULL,
    [PlayersRangeId] INT FOREIGN KEY REFERENCES [PlayersRanges]([Id]) NOT NULL
)

CREATE TABLE [Creators]
(
    [Id] INT PRIMARY KEY IDENTITY(1, 1),
    [FirstName] NVARCHAR(30) NOT NULL,
    [LastName] NVARCHAR(30) NOT NULL,
    [Email] NVARCHAR(30) NOT NULL
)

CREATE TABLE [CreatorsBoardgames]
(
    [CreatorId] INT FOREIGN KEY REFERENCES [Creators]([Id]) NOT NULL,
    [BoardgameId] INT FOREIGN KEY REFERENCES [Boardgames]([Id]) NOT NULL,
    PRIMARY KEY ([CreatorId], [BoardgameId])
)

GO

-- Problem 02. Insert

INSERT INTO [Boardgames]([Name], [YearPublished], [Rating], [CategoryId], [PublisherId], [PlayersRangeId]) VALUES
('Deep Blue', 2019, 5.67, 1, 15, 7),
('Paris', 2016, 9.78, 7, 1, 5),
('Catan: Starfarers', 2021, 9.87, 7, 13, 6),
('Bleeding Kansas', 2020, 3.25, 3, 7, 4),
('One Small Step', 2019, 5.75, 5, 9, 2)

INSERT INTO [Publishers]([Name], [AddressId], [Website], [Phone]) VALUES
('Agman Games', 5, 'www.agmangames.com', '+16546135542'),
('Amethyst Games', 7, 'www.amethystgames.com', '+15558889992'),
('BattleBooks', 13, 'www.battlebooks.com', '+12345678907')

GO

-- Problem 03. Update

UPDATE [PlayersRanges]
   SET [PlayersMax] = [PlayersMax] + 1
 WHERE [PlayersMin] = 2 AND [PlayersMax] = 2

UPDATE [Boardgames]
   SET [Name] = [Name] + 'V2'
 WHERE [YearPublished] >= 2020

GO

-- Problem 04. Delete

DELETE FROM [CreatorsBoardgames]
WHERE [BoardgameId] IN (SELECT [Id] FROM [Boardgames] WHERE [PublisherId] = 1)

DELETE FROM [Boardgames]
WHERE [PublisherId] IN (SELECT [Id] FROM [Publishers] WHERE [AddressId] = 5)

DELETE FROM [Publishers]
WHERE [AddressId] IN (SELECT [Id] FROM [Addresses] WHERE [Town] LIKE 'L%')

DELETE FROM [Addresses]
WHERE [Town] LIKE 'L%'

GO

-- Problem 05. Boardgames by Year of Publication

  SELECT [Name], [Rating]
    FROM [Boardgames]
ORDER BY [YearPublished] ASC, [Name] DESC

GO

-- Problem 06. Boardgames by Category

  SELECT [b].[Id],
         [b].[Name],
         [b].[YearPublished] AS 'YearPublished',
         [c].[Name] AS 'CategoryName'
    FROM [Boardgames] AS [b]
    JOIN [Categories] AS [c] ON [b].[CategoryId] = [c].[Id]
   WHERE [c].[Name] LIKE 'Strategy Games' OR
         [c].[Name] LIKE 'Wargames'
ORDER BY YearPublished DESC

GO

-- Problem 07. Creators without Boardgames

   SELECT [c].[Id],
          [c].[FirstName] + ' ' + [c].[LastName] AS 'CreatorName',
          [c].[Email]
     FROM [Creators] AS [c]
LEFT JOIN [CreatorsBoardgames] AS [cb] ON [c].[Id] = [cb].[CreatorId]
    WHERE [cb].[BoardgameId] IS NULL
 ORDER BY [CreatorName] ASC

GO

-- Problem 08. First 5 Boardgames

SELECT TOP(5)   [b].[Name],
                [b].[Rating],
                [c].[Name] AS 'CategoryName'
      FROM [Boardgames] AS [b]
      JOIN [PlayersRanges] AS [pr] ON [b].[PlayersRangeId] = [pr].[Id]
      JOIN [Categories] AS [c] ON [b].[CategoryId] = [c].[Id]
     WHERE ([Rating] > 7.00 AND [b].[Name] LIKE '%a%') OR
           ([Rating] > 7.50 AND [pr].[PlayersMin] = 2 AND [pr].[PlayersMax] = 5)
  ORDER BY [b].Name ASC, [b].[Rating] DESC

GO

-- Problem 09. Creators with Emails

  SELECT [c].[FirstName] + ' ' + [c].[LastName] AS 'FullName',
         [c].[Email],
         MAX([b].[Rating])
    FROM [Creators] AS [c]
    JOIN [CreatorsBoardgames] AS [cb] ON [cb].[CreatorId] = [c].[Id]
    JOIN [Boardgames] AS [b] ON [cb].[BoardgameId] = [b].[Id]
   WHERE [c].[Email] LIKE '%.com'
GROUP BY [c].[FirstName], [c].[LastName], [c].[Email]
ORDER BY [FullName] ASC

GO

-- Problem 10. Creators by Rating

   SELECT [c].[LastName],
          CEILING(AVG([b].[Rating])),
          [p].[Name]
     FROM [Creators] AS [c]
LEFT JOIN [CreatorsBoardgames] AS [cb] ON [c].[Id] = [cb].[CreatorId]
LEFT JOIN [Boardgames] [b] ON [b].[Id] = [cb].[BoardgameId]
LEFT JOIN [Publishers] [p] ON [p].[Id] = [b].[PublisherId]
    WHERE [cb].[BoardgameId] IS NOT NULL AND [p].[Name] LIKE 'Stonemaier Games'
 GROUP BY [c].[LastName], [p].[Name]
 ORDER BY AVG([b].[Rating]) DESC

GO

-- Problem 11. Creator with Boardgames

CREATE FUNCTION [udf_CreatorWithBoardgames](@name NVARCHAR(30))
RETURNS INT
AS
BEGIN
        DECLARE @creatorId INT

        SET @creatorId = (SELECT [Id]
                            FROM [Creators]
                           WHERE [FirstName] = @name)

        DECLARE @result INT

        SET @result = (SELECT COUNT([cb].[CreatorId])
                         FROM [Creators] AS [c]
                         JOIN [CreatorsBoardgames] AS [cb] ON [c].[Id] = [cb].[CreatorId]
                         JOIN [Boardgames] AS [b] ON [b].[Id] = [cb].[BoardgameId]
                        WHERE [c].[Id] = @creatorId
                     GROUP BY [cb].[CreatorId])

        IF (@result IS NULL)
        BEGIN
            SET @result = 0
        END

        RETURN @result
END

GO

SELECT [dbo.udf_CreatorWithBoardgames]('BRUNO')

GO

-- Problem 12. Search for Boardgame with Specific Category

CREATE PROCEDURE [usp_SearchByCategory](@category VARCHAR(50))
AS
BEGIN
          SELECT [b].[Name],
                 [b].[YearPublished],
                 [b].[Rating],
                 [c].[Name] AS 'CategoryName',
                 [p].[Name] AS 'PublisherName',
                 CONCAT([pr].[PlayersMin], ' people') AS 'MinPlayers',
                 CONCAT([pr].[PlayersMax], ' people') AS 'MaxPlayers'
            FROM [Categories] AS [c]
            JOIN [Boardgames] AS [b] ON [b].[CategoryId] = [c].[Id]
            JOIN [PlayersRanges] AS [pr] ON [b].[PlayersRangeId] = [pr].[Id]
            JOIN [Publishers] AS [p] ON [b].[PublisherId] = [p].[Id]
           WHERE [c].[Name] = @category
        ORDER BY [p].[Name] ASC, [b].[YearPublished] DESC
END

GO

EXEC [usp_SearchByCategory] 'Wargames'

GO