/*1. feladat*/
/*1A: Készítsen olyan SQL lekérdezést a Northwind adatbázisban, mely termék-kategóriánként és évenként
adja meg a 90 dollárnál drágább egységárú termékek rendelési tételeken szereplő darabszámai összegét!*/

SELECT top 5 * FROM Northwind.dbo.[ORDER Details]
SELECT top 5 * FROM Northwind.dbo.Orders
SELECT top 5 * FROM Northwind.dbo.Products
SELECT top 5 * FROM Northwind.dbo.Categories

SELECT CategoryName, YEAR(OrderDate), SUM(Quantity)
FROM Northwind.dbo.Categories c
JOIN Northwind.dbo.Products p ON c.CategoryID = p.CategoryID
JOIN Northwind.dbo.[ORDER Details] od ON p.ProductID = od.ProductID
JOIN Northwind.dbo.Orders o ON od.OrderID = o.OrderID
WHERE od.UnitPrice > 90
GROUP BY CategoryName, YEAR(OrderDate)

/*1B: Az előző lekérdezés eredménytábláját pivotálja az egyik alkalmasan választott mező szerint.*/
SELECT [YEAR], pvt.[Beverages], pvt.[Meat/Poultry]
FROM (SELECT CategoryName, YEAR(OrderDate) 'year', SUM(Quantity) 'db'
    FROM Northwind.dbo.Categories c
    JOIN Northwind.dbo.Products p ON c.CategoryID = p.CategoryID
    JOIN Northwind.dbo.[ORDER Details] od ON p.ProductID = od.ProductID
    JOIN Northwind.dbo.Orders o ON od.OrderID = o.OrderID
    WHERE od.UnitPrice > 90
    GROUP BY CategoryName, YEAR(OrderDate)
) SOURCE
pivot (SUM(db) FOR CategoryName IN ([Beverages], [Meat/Poultry])) pvt

/*2. feladat*/
/*Írjon egy T-SQL scriptet, mely a Northwind cég menedzserének munkáját segíti az alábbiak szerint.
        A scriptnek egy paramétere van: egy dolgozó azonosítója. A script a paraméterül kapott
            azonosítóra ellenőrzi, nincs-e már az illető dolgozónak 5-nél több területe, ha van, hibaüzenettel kilép.
        Ha nincs, akkor ellenőrzi, hogy van-e olyan terület, melynek még nincs felelőse. Ha minden
            területnek van már felelőse, hibaüzenettel kilép.
        Ha van még szabad terület, akkor hozzárendeli a dolgozóhoz a betűrendben első szabad területet,
            és megemeli az alkalmazott fizetését 25%-kal. Ezután visszaírja a dolgozóhoz hozzárendelt terület nevét.
        Ha bármilyen hiba történik, a script kiírja a hibakódot és a rendszertől származó angol nyelvű hibaüzenetet.
A tesztelést végző scriptben szerepeljen az eredeti helyzet helyreállítása és az eredmény ellenőrzése!
A tesztelést a script minden ágára végezze el.
*/


SELECT *
FROM Employees

GO
DECLARE @employee_id INT = 1
DECLARE @territory_id INT
DECLARE @territory_desc VARCHAR(30)
    IF EXISTS (SELECT COUNT(TerritoryID)
    FROM Northwind.dbo.EmployeeTerritories
    WHERE EmployeeID = @employee_id
    HAVING COUNT(TerritoryID) <= 5
)
BEGIN
    SELECT top 1 @territory_id = TerritoryID, @territory_desc = TerritoryDescription
        FROM Territories
        WHERE TerritoryID NOT IN(
            SELECT TerritoryID
            FROM EmployeeTerritories
        ) --and TerritoryID = 1 -- teszt
        ORDER BY TerritoryDescription

    IF @territory_id IS NULL
    BEGIN
        print 'HIBA: Nincs szabad terület!'
        RETURN
    END
    ELSE
    BEGIN
        INSERT INTO EmployeeTerritories VALUES(@employee_id, @territory_id)
        DELETE FROM EmployeeTerritories WHERE EmployeeID=@employee_id AND CAST(TerritoryID AS INT)=@territory_id

        UPDATE Employees SET Extension = 'fasz'
        WHERE EmployeeID = @employee_id -- nem volt Salary oszlop...

        --update Employees set Extension = Extension*0.8 where EmployeeID = @employee_id
        print CAST(@territory_desc AS VARCHAR(30))
    END
END
ELSE
BEGIN
    print 'HIBA: Több, mint 5 terület'
    RETURN
END
GO

/*
tesztelés
@employee_id = 1 esetén 'DirrDurr', mert 2 területe van a dolgozónak, ami nem több, mint 5
@employee_id = 2 esetén 'Fail', mert 7 területe van a dolgozónak, ami több, mint 5
*/

SELECT EmployeeID, COUNT(TerritoryID)
FROM EmployeeTerritories
GROUP BY EmployeeID