SOFTUNI T-SQL EXAM 19 June 2022

CREATE DATABASE Zoo

USE Zoo

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) UNIQUE NOT NULL,
                            AnimalId INT FOREIGN KEY REFERENCES Animals(Id) UNIQUE 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
                        )

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)


--3.    Update

UPDATE Animals
SET OwnerId = ( SELECT Id
                FROM Owners
                WHERE [Name] = 'Kaloqn Stoqnov')
WHERE OwnerId IS NULL

--4.    Delete


DELETE FROM Volunteers
WHERE DepartmentId = (
                        SELECT Id
                        FROM VolunteersDepartments
                        WHERE DepartmentName = 'Education program assistant')


DELETE FROM VolunteersDepartments
WHERE DepartmentName = 'Education program assistant'


--5.    Volunteers

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

--6.    Animals data

SELECT [Name],
        (SELECT AnimalType
        FROM AnimalTypes
        WHERE Id =AnimalTypeId) AS AnimalType,
       FORMAT(BirthDate, 'dd.MM.yyyy') AS BirthDate
FROM Animals
ORDER BY [Name]


--7.    Owners and Their Animals

  SELECT TOP(5)[Name] AS [Owner],
              (SELECT COUNT(Id)
               FROM Animals
               WHERE OwnerId = o.Id
               GROUP BY OwnerId) AS CountOfAnimals
    FROM Owners AS o
ORDER BY CountOfAnimals DESC, [Owner]


--8.    Owners, Animals and Cages

SELECT CONCAT(o.[Name],'-',a.[Name]) AS OwnersAnimals,
       o.PhoneNumber,
       ac.CageId
FROM Owners AS o
JOIN Animals AS a ON o.Id = a.OwnerId
JOIN AnimalTypes AS aty ON a.AnimalTypeId = aty.Id
JOIN AnimalsCages AS ac ON a.ID = ac. AnimalId
WHERE aty.AnimalType = 'mammals'
ORDER BY o.[Name], a.[Name] DESC


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


--10.   Animals for Adoption

  SELECT a.[Name],
         DATEPART(year, a.BirthDate) AS BirthYear,
         aty.AnimalType
    FROM Animals AS a
    JOIN AnimalTypes AS aty ON a.AnimalTypeId = aty.Id
   WHERE aty.AnimalType != 'Birds' AND a.OwnerId IS NULL AND DATEDIFF(year,a.BirthDate,'2022-01-01') < 5
ORDER BY a.[Name]


--11.   All Volunteers in a Department
GO
CREATE OR ALTER FUNCTION udf_GetVolunteersCountFromADepartment (@VolunteersDepartment VARCHAR(50))
RETURNS INT
BEGIN

RETURN (
          SELECT COUNT(Id)
            FROM Volunteers
           WHERE DepartmentId = (
                                SELECT Id
                                FROM VolunteersDepartments
                                WHERE DepartmentName = @VolunteersDepartment)
        GROUP BY DepartmentId
        )
END
GO

SELECT dbo.udf_GetVolunteersCountFromADepartment ('Education program assistant')
SELECT dbo.udf_GetVolunteersCountFromADepartment ('Guest engagement')
SELECT dbo.udf_GetVolunteersCountFromADepartment ('Zoo events')


--12.   Animals with Owner or Not

GO
CREATE OR ALTER PROC 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

EXEC usp_AnimalsWithOwnersOrNot 'Pumpkinseed Sunfish'
EXEC usp_AnimalsWithOwnersOrNot 'Hippo'
EXEC usp_AnimalsWithOwnersOrNot 'Brown bear'