T-SQL/MSSQL REGULAR EXAM 19/06/22

--_________________________________________________________________
--01. DDL
--_________________________________________________________________
CREATE TABLE Owners
(
Id INT PRIMARY KEY IDENTITY,
Name NVARCHAR(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) ,
    AnimalsTypeId INT FOREIGN KEY REFERENCES AnimalTypes(Id) NOT NULL,
)
CREATE TABLE AnimalsCages
(
    CageId INT FOREIGN KEY REFERENCES Cages(Id) NOT NULL UNIQUE,
    AnimalId INT FOREIGN KEY REFERENCES Animals(Id) NOT NULL UNIQUE,
    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 NVARCHAR(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
)
--_________________________________________________________________
--02. Insert
--_________________________________________________________________
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)
--_________________________________________________________________
 --03. Update
--_________________________________________________________________
UPDATE Animals
SET OwnerId  =   ( SELECT
Id
FROM owners
WHERE Name  =   'Kaloqn Stoqnov'
)
WHERE OwnerId is NULL
--_________________________________________________________________
--04. Delete
--_________________________________________________________________
UPDATE Animals
ALTER TABLE Volunteers
ALTER COLUMN DepartmentId INT
UPDATE Volunteers
SET DepartmentId  =  NULL
WHERE DepartmentId  =  2
DELETE
FROM VolunteersDepartments
WHERE Id  =  2
--_________________________________________________________________
--05. Volunteers
--_________________________________________________________________
SELECT
[Name],
[PhoneNumber],
[Address],
[AnimalId],
[DepartmentId]

FROM [Volunteers]

ORDER BY [Name], [AnimalId], [DepartmentId]
--_________________________________________________________________
--06. Animals data
--_________________________________________________________________
SELECT
[Name],
[t].[AnimalType],
FORMAT([BirthDate], 'dd.MM.yyyy') AS [BirthDate]
 FROM [Animals] AS [a]
JOIN [AnimalTypes] AS [t]
ON [a].[AnimalTypeId]  =  [t].[Id]
ORDER BY [Name]
----_________________________________________________________________
--07. Owners AND Their Animals
--_________________________________________________________________
SELECT top (5)
[o].[Name],
count(*) [CountOfAnimals]
 FROM [Animals] AS [a], [Owners] AS [o]
 WHERE [a].[OwnerId] = [o].[Id]
 GROUP BY [o].[Name]
 ORDER BY count(*) DESC;
--_________________________________________________________________
--08.
--_________________________________________________________________

SELECT concat(O.Name,'-',a.Name)  AS [OwnersAnimals], [O].[PhoneNumber], [ac].[CageId]  FROM [Animals] [a], [AnimalTypes] AS [at], [Owners] AS [o], [AnimalsCages] AS [ac]
WHERE [a].[AnimalTypeId] = [at].[Id]
AND [a].[OwnerId] = [O].[Id]
AND [a].[Id] = [ac].[AnimalId]
AND at.AnimalType = 'mammals'
ORDER BY [o].[Name], [a].[Name] DESC
--_________________________________________________________________
--09. Volunteers in Sofia
--_________________________________________________________________
SELECT
[Name],
[PhoneNumber],
REPLACE(REPLACE([Address], 'Sofia', ''), ', ', '') AS [Address]
FROM [Volunteers] AS [v]
JOIN [VolunteersDepartments] AS [d]
ON [v].[DepartmentId]  =  [d].[Id]
WHERE
([Address] LIKE '%Sofia%') AND
([d].[DepartmentName]  =  'Education program ASsistant' )

ORDER BY [Name]

 --_________________________________________________________________
--10.
--_________________________________________________________________

SELECT [Name], [BirthYear],[AnimalType]
 FROM (
SELECT [Name], FORMAT(BirthDate,'yyyy') AS [BirthYear], [at].[AnimalType],DATEDIFF(YEAR, BirthDate,'2022-01-01') AS [v] FROM [Animals] a,AnimalTypes at
WHERE [a].[AnimalTypeId] = [at].[Id]
AND [OwnerId] is NULL
AND [at].[AnimalType] NOT IN ('Birds'))
WHERE [a].[v]<5
ORDER BY 1;
--_________________________________________________________________
--11.
--_________________________________________________________________

CREATE FUNCTION [udf_GetVolunteersCountFROMADepartment] (@VolunteersDepartment varchar(30))
RETURNS INT
AS

BEGIN
     DECLARE @result INT
        SET @result  =  (SELECT count(*) FROM [Volunteers]
        WHERE [DepartmentId] IN
        (SELECT [id]
         FROM [VolunteersDepartments]
         WHERE [DepartmentName]  =  @VolunteersDepartment))
     return @result
END
--_________________________________________________________________
--12.
--_________________________________________________________________
CREATE PROCEDURE [usp_AnimalsWithOwnersOrNot] @AnimalName varchar(30)
AS
BEGIN
    SELECT a.Name, CASE WHEN 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