MSSQL Server Exam - 19 June 2022

CREATE DATABASE Zoo

GO

USE Zoo

--problem01
CREATE TABLE Owners(
    Id INT PRIMARY KEY IDENTITY,
    [Name] VARCHAR(50) NOT NULL,
    PhoneNumber VARCHAR(15) NOT NULL,
    [Address] VARCHAR(50)
)

CREATE TABLE AnimalTypes(
    Id INT PRIMARY KEY IDENTITY,
    AnimalType VARCHAR(30) NOT NULL
)

CREATE TABLE Cages(
    Id INT PRIMARY KEY IDENTITY,
    AnimalTypeId INT FOREIGN KEY REFERENCES AnimalTypes(Id) NOT NULL
)

CREATE TABLE Animals(
    Id INT PRIMARY KEY IDENTITY,
    [Name] VARCHAR(30) NOT NULL,
    BirthDate DATE NOT NULL,
    OwnerId INT FOREIGN KEY REFERENCES Owners(Id),
    AnimalTypeId INT FOREIGN KEY REFERENCES AnimalTypes(Id) NOT NULL
)

CREATE TABLE AnimalsCages(
    CageId INT FOREIGN KEY REFERENCES Cages(Id) NOT NULL,
    AnimalId INT FOREIGN KEY REFERENCES Animals(Id) NOT NULL,
    PRIMARY KEY (CageId, AnimalId)
)

CREATE TABLE VolunteersDepartments(
    Id INT PRIMARY KEY IDENTITY,
    DepartmentName VARCHAR(30) NOT NULL,
)

CREATE TABLE Volunteers(
    Id INT PRIMARY KEY IDENTITY,
    [Name] VARCHAR(50) NOT NULL,
    PhoneNumber VARCHAR(15) NOT NULL,
    [Address] VARCHAR(50),
    AnimalId INT FOREIGN KEY REFERENCES Animals(Id),
    DepartmentId INT FOREIGN KEY REFERENCES VolunteersDepartments(Id) NOT NULL
)

--Problem02
INSERT INTO Volunteers([Name], PhoneNumber, [Address], AnimalId, DepartmentId)
    VALUES
     ('Anita Kostova',  '0896365412',   'Sofia, 5 Rosa str.',   15, 1),
     ('Dimitur Stoev',  '0877564223',    NULL,  42, 4),
     ('Kalina Evtimova','0896321112',   'Silistra, 21 Breza str.',  9,  7),
     ('Stoyan Tomov',   '0898564100',   'Montana, 1 Bor str.',  18, 8),
     ('Boryana Mileva', '0888112233',    NULL,  31, 5)

INSERT INTO Animals([Name], BirthDate, OwnerId, AnimalTypeId)
    VALUES
    ('Giraffe', '2018-09-21', 21,   1),
    ('Harpy Eagle', '2015-04-17', 15, 3),
    ('Hamadryas Baboon', '2017-11-02', NULL, 1),
    ('Tuatara', '2021-06-30', 2,   4)

--Problem03
UPDATE Animals
SET OwnerId = 4
WHERE OwnerId IS NULL

--Problem04
DELETE FROM Volunteers
      WHERE DepartmentId = 2


DELETE FROM VolunteersDepartments
      WHERE Id = 2

--Problem05
SELECT [Name],
       PhoneNumber,
       [Address],
       AnimalId,
       DepartmentId
  FROM Volunteers
ORDER BY [Name], AnimalId, DepartmentId

--Problem06
SELECT a.[Name],
       at.AnimalType,
       FORMAT(a.BirthDate,'dd.MM.yyyy') AS [BirthDate]
  FROM Animals AS a
 JOIN AnimalTypes AS at ON a.AnimalTypeId = at.Id
 ORDER BY a.[Name]

--Problem07
SELECT TOP(5)
        o.[Name] AS [Owner],
       COUNT(*) AS CountOfAnimals
    FROM Animals AS a
JOIN Owners AS o ON a.OwnerId = o.Id
GROUP BY o.[Name]
ORDER BY CountOfAnimals DESC

--Probem08
   SELECT CONCAT_WS('-',o.[Name], a.[Name]) AS [OwnersAnimals],
          PhoneNumber,
          CageId
     FROM OWNERS AS o
     JOIN Animals AS a ON a.OwnerId = o.Id
     JOIN AnimalsCages AS ac ON a.Id = ac.AnimalId
     JOIN AnimalTypes AS at ON a.AnimalTypeId = at.Id
     WHERE at.AnimalType = 'Mammals'
 ORDER BY o.[Name], a.[Name] DESC


 --Probem09
 SELECT  v.[Name],
         v.[PhoneNumber],
         SUBSTRING(v.[Address], CHARINDEX(',', v.[Address]) + 2,LEN(v.[Address])) AS [Address]
     FROM Volunteers AS v
LEFT JOIN VolunteersDepartments AS vd ON vd.Id = v.DepartmentId
    WHERE v.DepartmentId = 2 AND v.[Address] LIKE '%Sofia%'
 ORDER BY v.[Name]

 --Probem10
 SELECT a.[Name],
        DATEPART(YEAR, a.BirthDate) AS BirthYear,
        [at].AnimalType
    FROM Animals AS a
Join AnimalTypes AS [at] ON a.AnimalTypeId = [at].Id
 WHERE OwnerId IS NULL AND DATEPART(YEAR, a.BirthDate) > 2017 AND [at].AnimalType <> 'Birds'
 ORDER BY a.[Name]

 GO
 --Problem11
CREATE FUNCTION udf_GetVolunteersCountFromADepartment (@VolunteersDepartment VARCHAR(30))
RETURNS INT
AS
BEGIN
        DECLARE @Count INT = (SELECT COUNT(v.[Name])
                             FROM Volunteers AS v
                             JOIN VolunteersDepartments AS vd ON v.DepartmentId = vd.Id
                             WHERE vd.DepartmentName = @VolunteersDepartment
                             )
        RETURN @Count
END

GO

GO

--Problem12

GO

CREATE PROCEDURE usp_AnimalsWithOwnersOrNot @AnimalName VARCHAR(30)
AS
BEGIN
        SELECT a.[Name],
                   CASE
                      WHEN a.OwnerId IS NULL THEN 'For adoption'
                      ELSE o.[Name]
                   END AS [OwnersName]
         FROM Animals AS a
    LEFT JOIN Owners AS o ON a.OwnerId = o.Id
        WHERE a.[Name] = @AnimalName
END

GO

--END