MS SQL - Exam - 16 October 2021

CREATE DATABASE CigarShop

GO

USE CigarShop

GO

--01. Section 1. DDL (30 pts)

CREATE TABLE Sizes (
Id INT PRIMARY KEY IDENTITY,
[Length] INT NOT NULL,
 CHECK([Length] BETWEEN 10 AND 25),
 RingRange DECIMAL(16, 2) NOT NULL,
 CHECK(RingRange BETWEEN 1.5 AND 7.5)
)

CREATE TABLE Tastes (
Id INT PRIMARY KEY IDENTITY,
TasteType VARCHAR(20) NOT NULL,
TasteStrength VARCHAR(15) NOT NULL,
ImageURL NVARCHAR(100) NOT NULL
)

CREATE TABLE Brands(
Id INT PRIMARY KEY IDENTITY,
BrandName VARCHAR(30) UNIQUE NOT NULL,
BrandDescription VARCHAR(MAX)
)

CREATE TABLE Cigars(
Id INT PRIMARY KEY IDENTITY,
CigarName VARCHAR(80) NOT NULL,
BrandId INT FOREIGN KEY REFERENCES Brands (Id) NOT NULL,
TastId INT FOREIGN KEY REFERENCES Tastes (Id) NOT NULL,
SizeId INT FOREIGN KEY REFERENCES Sizes (Id) NOT NULL,
PriceForSingleCigar MONEY NOT NULL,
ImageURL NVARCHAR(100) NOT NULL
)

CREATE TABLE Addresses(
Id INT PRIMARY KEY IDENTITY,
Town VARCHAR(30) NOT NULL,
Country NVARCHAR(30) NOT NULL,
Streat  NVARCHAR(100) NOT NULL,
ZIP VARCHAR(20) NOT NULL
)

CREATE TABLE Clients(
 Id INT PRIMARY KEY IDENTITY,
 FirstName NVARCHAR(30) NOT NULL,
 LastName NVARCHAR(30) NOT NULL,
 Email NVARCHAR(50) NOT NULL,
 AddressId INT FOREIGN KEY REFERENCES Addresses (Id) NOT NULL
)

CREATE TABLE ClientsCigars(
ClientId INT FOREIGN KEY REFERENCES Clients(Id),
CigarId INT FOREIGN KEY REFERENCES Cigars(Id),
PRIMARY KEY (ClientId, CigarId)
)

--02. Section 2. DML (10 pts)
--02.1 INSERT

INSERT INTO Cigars(CigarName, BrandId, TastId, SizeId, PriceForSingleCigar, ImageURL)
VALUES
('COHIBA ROBUSTO', 9, 1, 5, 15.50, 'cohiba-robusto-stick_18.jpg'),
('COHIBA SIGLO I', 9, 1, 10, 410.00, 'cohiba-siglo-i-stick_12.jpg' ),
('HOYO DE MONTERREY LE HOYO DU MAIRE', 14, 5, 11, 7.50, 'hoyo-du-maire-stick_17.jpg'),
('HOYO DE MONTERREY LE HOYO DE SAN JUAN', 14, 4, 15, 32.00, 'hoyo-de-san-juan-stick_20.jpg'),
('TRINIDAD COLONIALES', 2, 3, 8, 85.21, 'trinidad-coloniales-stick_30.jpg' )

 INSERT INTO Addresses(Town, Country, Streat, ZIP)
 VALUES
 ('Sofia', 'Bulgaria', '18 Bul. Vasil levski', '1000'),
 ('Athens', 'Greece', '4342 McDonald Avenue', '10435'),
 ('Zagreb', 'Croatia', '4333 Lauren Drive', '10000')

 --02.2 Update

    UPDATE Cigars
       SET PriceForSingleCigar *= 1.2
     WHERE TastId = (SELECT
                            Id
                       FROM Tastes
                      WHERE TasteType = 'Spicy')


    UPDATE Brands
       SET BrandDescription = 'New description'
     WHERE BrandDescription IS NULL

-- 02.3 Delete

DELETE
  FROM ClientsCigars
 WHERE ClientId IN ( SELECT
                            Id
                       FROM Clients
                      WHERE AddressId IN (SELECT
                                                 Id
                                            FROM Addresses
                                           WHERE LEFT(Country, 1) = 'C'))

DELETE
  FROM Clients
 WHERE AddressId IN (SELECT
                           Id
                      FROM Addresses
                     WHERE LEFT(Country, 1) = 'C')

DELETE
  FROM Addresses
 WHERE LEFT(Country, 1) = 'C'

 --Section 3. Querying (40 pts)
 --3.1 Cigars by Price

 SELECT
        CigarName,
        PriceForSingleCigar,
        ImageURL
   FROM Cigars
   ORDER BY PriceForSingleCigar, CigarName DESC

--3.2 Cigars by Taste

   SELECT
          c.Id,
          c.CigarName,
          c.PriceForSingleCigar,
          t.TasteType,
          t. TasteStrength
     FROM Cigars AS c
LEFT JOIN Tastes AS t ON c.TastId = t.Id
    WHERE t.TasteType IN ('Earthy', 'Woody')
 ORDER BY c.PriceForSingleCigar DESC

 --3.3 Clients without Cigars

   SELECT
          c.Id,
          CONCAT(c.FirstName, ' ', c.LastName) AS ClientName,
          c.Email
     FROM Clients AS c
LEFT JOIN ClientsCigars AS cc ON c.Id = cc.ClientId
    WHERE CigarId IS NULL
 ORDER BY ClientName

 --3.3.1

  SELECT
         Id
         ,CONCAT(FirstName, ' ', LastName) AS ClientName
         ,Email
    FROM Clients
   WHERE NOT EXISTS (SELECT 1
                       FROM ClientsCigars
                      WHERE ClientId = Id)
      --Ще филтрира по тези Id ,които не съвпадат с ClientId,
      --т.е. по тези клиенти, които нямат цигари
ORDER BY ClientName

--3.4 First 5 Cigars

SELECT TOP(5)
              c.CigarName,
              c.PriceForSingleCigar,
              c.ImageURL
         FROM Cigars AS c
    LEFT JOIN Sizes AS s ON c.SizeId = s.Id
          AND s.[Length] >= 12
        WHERE (c.CigarName LIKE '%ci%' OR c.PriceForSingleCigar > 50)
          AND s.RingRange > 2.55
     ORDER BY c.CigarName, c.PriceForSingleCigar DESC

--3.5 Clients with ZIP Codes

   SELECT
          FullName,
          Country,
          ZIP,
          FORMAT(PriceForSingleCigar, 'C', 'en-us') AS CigarPrice
     FROM(SELECT
                 FullName,
                 Country,
                 ZIP,
                 PriceForSingleCigar,
                 DENSE_RANK() OVER (PARTITION BY FullName ORDER BY PriceForSingleCigar DESC) AS PriceRank
           FROM (SELECT
                        CONCAT(c.FirstName, ' ', c.LastName) AS FullName,
                        a.Country,
                        CASE
                          WHEN a.ZIP LIKE '%[^0-9]%' THEN NULL
                          ELSE a.ZIP
                        END  AS ZIP,
                        cr.PriceForSingleCigar
                   FROM Clients AS c
              LEFT JOIN Addresses AS a ON c.AddressId = a.Id
              LEFT JOIN ClientsCigars AS cc ON c.Id = cc.ClientId
              LEFT JOIN Cigars AS cr ON cc.CigarId = cr.Id
                 ) AS DigitZipSubQuary
                  WHERE ZIP IS NOT NULL
     ) AS RankingSubquery
    WHERE PriceRank = 1
 ORDER BY FullName

 --3.5.1 Clients with ZIP Codes2

            SELECT
                   CONCAT(c.FirstName, ' ', c.LastName) AS FullName
                   ,a.Country
                   ,a.ZIP
                   ,(SELECT
                            FORMAT(MAX(cr.PriceForSingleCigar), 'C', 'en-us')
                       FROM Cigars AS cr
                       JOIN ClientsCigars AS cc ON cc.CigarId = cr.Id
                        AND cc.ClientId = c.Id
                     ) AS CigarPrice
              FROM Clients AS c
              JOIN Addresses AS a ON c.AddressId = a.Id
             WHERE ISNUMERIC(a.ZIP) = 1
          ORDER BY FullName

  --3.6 Cigars by Size

           SELECT
                  c.LastName
                  ,AVG(s.[Length]) AS CiagrLength
                  ,CEILING(AVG(s.RingRange)) AS CiagrRingRange
             FROM Clients AS c
        LEFT JOIN ClientsCigars AS cc ON c.Id = cc.ClientId
        LEFT JOIN Cigars as cr ON cc.CigarId = cr.Id
        LEFT JOIN Sizes AS s ON cr.SizeId = s.Id
            WHERE CigarId IS NOT NULL
         GROUP BY c.LastName
         --Това групиране води до грешен резултат защото ако има хора с еднакви фамилии ще се слеят резултатите им
         --GROUP BY c.Id, c.LastName - това е правилното групиране, но не минава в judge
         ORDER BY CiagrLength DESC
GO

--Section 4. Programmability (20 pts)
--4.1 Client with Cigars
GO

CREATE FUNCTION udf_ClientWithCigars(@name NVARCHAR(30))
RETURNS INT
AS
BEGIN
    DECLARE @result INT
    SET @result = (
                    SELECT
                           COUNT(cc.CigarId)
                      FROM Clients AS c
                      JOIN ClientsCigars AS cc ON c.Id = cc.ClientId
                  GROUP BY c.FirstName
                    HAVING c.FirstName = @name)
    RETURN @result
END

GO

SELECT dbo.udf_ClientWithCigars('Betty')

GO

--4.1.1 Client with Cigars2

CREATE FUNCTION udf_ClientWithCigars(@name NVARCHAR(30))
RETURNS INT
AS
BEGIN
    DECLARE @cigarCount INT;
    SET @cigarCount =
                    (SELECT
                            COUNT(*)
                       FROM ClientsCigars
                      WHERE ClientId IN (SELECT Id
                                           FROM Clients
                                          WHERE FirstName = @name)
                     )
    RETURN @cigarCount;
END

GO

DROP FUNCTION udf_ClientWithCigars

GO

SELECT dbo.udf_ClientWithCigars('Rachel')

GO

--4.2 Search for Cigar with Specific Taste

CREATE PROC usp_SearchByTaste @taste VARCHAR(20)
AS
   SELECT
          c.CigarName
          ,FORMAT(c.PriceForSingleCigar, 'C', 'en-us') AS Price
          ,t.TasteType
          ,b.BrandName
          ,CONCAT(s.[Length], ' ', 'cm') AS CigarLength
          ,CONCAT(s.RingRange, ' ', 'cm') AS CigarRingRange
     FROM Cigars AS c
LEFT JOIN Tastes AS t ON c.TastId = t.Id
LEFT JOIN Brands AS b ON c.BrandId = b.Id
LEFT JOIN Sizes AS s ON c.SizeId = s.Id
    WHERE t.TasteType = @taste
 ORDER BY CigarLength, CigarRingRange DESC

 EXEC usp_SearchByTaste 'Woody'