GH_SQL_international.sql

/* Task 1 */
SELECT
	ProductName,
	QuantityPerUnit, -- Packaging or sales-unit description.
	UnitsInStock, -- Current stock quantity.
	CategoryID,
	UnitPrice
FROM dbo.Products
WHERE QuantityPerUnit LIKE '%box%' -- Filters products whose packaging description contains the word "box".
-- The wildcard pattern allows matching "box" anywhere in the text value.
	AND UnitsInStock BETWEEN 20 AND 35
	AND CategoryID IN (2, 3) -- Restricts the result set to the selected product categories.
ORDER BY UnitPrice DESC; -- Sorts products from the highest unit price.

/* Task 2 */
SELECT
	o.OrderID,
	o.ShipCountry,
	o.ShipCity,
	o.OrderDate,
	o.ShippedDate
FROM dbo.Orders AS o
WHERE o.ShipCountry IN ('France', 'Germany') -- Limits the orders to the selected destination countries.
-- City filtering is handled separately using prefix-based text matching.
	AND (o.ShipCity LIKE 'B%' OR o.ShipCity LIKE 'K%' OR o.ShipCity LIKE 'P%')
	AND o.ShippedDate IS NOT NULL -- Keeps only orders with a recorded shipment date.
ORDER BY o.OrderDate;

/* Task 3 */
SELECT DISTINCT -- Returns each qualifying customer only once, regardless of the number of matching orders.
	c.CustomerID,
	c.CompanyName
FROM dbo.Customers AS c
JOIN dbo.Orders AS o -- Joins customers with their orders to evaluate order-date conditions.
	ON c.CustomerID = o.CustomerID
WHERE DAY(o.OrderDate) BETWEEN 1 AND 5 -- Filters orders placed during the first five days of any month.
ORDER BY c.CompanyName;

/* Task 4 */
SELECT
	EmployeeID,
	AVG(CAST(DATEDIFF(day, OrderDate, ShippedDate) AS decimal(10, 2))) -- Calculates the processing time between order placement and shipment.
	-- Aggregates the average fulfilment time per employee.
	-- Casts the date difference to a decimal value before averaging.
	-- The decimal precision keeps the average readable and consistent.
	-- The expression first computes duration, then converts it, then aggregates it.
		AS Średni_czas_realizacji
FROM dbo.Orders
WHERE ShippedDate IS NOT NULL -- Excludes orders without shipment data from the fulfilment-time calculation.
GROUP BY EmployeeID
ORDER BY EmployeeID;

/* Task 5 */
SELECT
	YEAR(BirthDate) AS Rok_urodzenia, -- Extracts the birth year for grouping.
	COUNT(*) AS Liczba_pracowników -- Counts employees within each birth-year group.
FROM dbo.Employees -- Uses the Employees table as the source of individual staff records.
GROUP BY YEAR(BirthDate) -- Groups employees by extracted year of birth.
ORDER BY Rok_urodzenia;

/* Task 6 */
SELECT
	EmployeeID,
	COUNT(*) AS Liczba_zamówień -- Counts orders handled by each employee during the selected period.
FROM dbo.Orders -- Treats each row in Orders as a single order record.
WHERE OrderDate >= '1996-07-01' -- Filters orders placed in Q3 1996 using a half-open date range.
AND OrderDate < '1996-10-01'
GROUP BY EmployeeID -- Groups order records by employee.
HAVING COUNT(*) > 5 -- Keeps only employees whose order count exceeds the required threshold.
-- Applies the aggregate-level condition after grouping.
ORDER BY Liczba_zamówień DESC;

/* Task 7 */
SELECT
	ShipVia,
	COUNT(*) AS Liczba_zamówień, -- Counts orders handled by each shipper in 1997.
	SUM(Freight) AS Łączny_koszt_przewozu -- Calculates the total freight value assigned to each shipper.
FROM dbo.Orders
WHERE OrderDate >= '1997-01-01' -- Limits the analysis to orders placed in 1997.
AND OrderDate < '1998-01-01'
GROUP BY ShipVia -- Groups orders by shipper identifier.
ORDER BY ShipVia;

/* Task 8 */
SELECT
	c.CompanyName AS Nazwa_klienta,
	COUNT(DISTINCT o.OrderID) AS Liczba_zam -- Counts each order once, even when it contains multiple jar-packaged products.
FROM dbo.Customers AS c
JOIN dbo.Orders AS o -- Connects customers with their orders.
	ON c.CustomerID = o.CustomerID
JOIN dbo.[Order Details] AS od -- Connects orders with their line-item details.
	ON o.OrderID = od.OrderID
JOIN dbo.Products AS p -- Connects line items with product metadata.
	ON od.ProductID = p.ProductID
WHERE o.OrderDate >= '1997-01-01' -- Restricts the order period to 1997.
	AND o.OrderDate < '1998-01-01'
	AND p.QuantityPerUnit LIKE '%jar%' -- Identifies products sold in jar packaging.
GROUP BY c.CompanyName
HAVING COUNT(DISTINCT o.OrderID) >= 3 -- Filters customer groups by the number of qualifying orders.
ORDER BY Liczba_zam DESC;
-- Multiple joins are required because customer, order, line-item, and product attributes are stored in separate tables.

/* Task 9 */
SELECT
	c.CompanyName AS Nazwa_klienta,
	SUM(od.UnitPrice * od.Quantity) AS wartość_zam -- Calculates total purchase value per customer from order-line amounts.
FROM dbo.Customers AS c
JOIN dbo.Orders AS o -- Links orders to their customers.
	ON c.CustomerID = o.CustomerID
JOIN dbo.[Order Details] AS od -- Links orders to their detailed line items.
	ON o.OrderID = od.OrderID
WHERE c.Country = 'Germany' -- Keeps only customers located in Germany.
GROUP BY c.CompanyName
HAVING SUM(od.UnitPrice * od.Quantity) > 5000 -- Returns only customers whose aggregated purchase value exceeds 5000.
ORDER BY Nazwa_klienta;

/* Task 10 */
SELECT
	e.FirstName AS Imię,
	e.LastName AS Nazwisko,
	COUNT(*) AS Liczba_zam -- Counts qualifying on-time shipments handled by each employee.
FROM dbo.Employees AS e
JOIN dbo.Orders AS o
	ON e.EmployeeID = o.EmployeeID
WHERE o.OrderDate >= '1997-01-01'
	AND o.OrderDate < '1999-01-01'
	AND o.ShippedDate IS NOT NULL
	AND o.ShippedDate <= o.RequiredDate -- Compares actual shipment date with the required delivery deadline.
GROUP BY 
	e.FirstName,
	e.LastName
ORDER BY Liczba_zam DESC;

/* Task 11 */
SELECT
	c.CustomerID,
	c.CompanyName
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o -- Uses a left join to retain customers even when they have no matching orders.
-- Customers remain the primary dataset in this anti-join pattern.
-- The NULL check identifies customers without any related order records.
	ON o.CustomerID = c.CustomerID
WHERE o.CustomerID IS NULL
ORDER BY c.CompanyName;

/* Task 12 */
SELECT
	c.CategoryName AS Kategoria,
	p.ProductName AS Nazwa_produktu,
	p.UnitPrice AS Cena_przed_rabatem,
	CASE -- Assigns discount rates according to product category.
		WHEN c.CategoryName = 'Condiments' THEN 5
		WHEN c.CategoryName = 'Seafood' THEN 8
		WHEN c.CategoryName = 'Produce' THEN 15
		ELSE 4
	END AS Rabat_procent,
	p.UnitPrice * (1 - CASE -- Applies the discount multiplier to calculate the final price.
		WHEN c.CategoryName = 'Condiments' THEN 0.05
		WHEN c.CategoryName = 'Seafood' THEN 0.08
		WHEN c.CategoryName = 'Produce' THEN 0.15
		ELSE 0.04
		END) AS Cena_po_rabacie
FROM dbo.Products AS p
JOIN dbo.Categories AS c
	ON p.CategoryID = c.CategoryID
ORDER BY c.CategoryName, p.ProductName;

/* Task 13 */
SELECT
	p.ProductName AS Nazwa_produktu,
	p.Discontinued AS Informacja_o_wycofaniu,
	MAX(od.UnitPrice) AS Cena_maksymalna
FROM dbo.Products AS p
JOIN dbo.[Order Details] AS od
	ON od.ProductID = p.ProductID
GROUP BY
	p.ProductName,
	p.Discontinued
HAVING (p.Discontinued = 0 AND MAX(od.UnitPrice) >= 40) -- Active products are evaluated against the higher price threshold.
	OR (p.Discontinued = 1 AND MAX(od.UnitPrice) > 25) -- Discontinued products are evaluated against a separate price threshold.
	-- Applies grouped conditions based on product status and maximum sale price.
	-- Each branch combines product status with the corresponding price condition.
	-- Uses the highest historical transaction price for each product.
	-- Transaction-level prices are taken from Order Details rather than the catalogue price.
ORDER BY p.Discontinued, 
	Cena_maksymalna DESC;

/* Task 14 */
SELECT
	p.ProductName
FROM dbo.Products AS p
WHERE NOT EXISTS -- Returns products for which no matching summer sales record exists.
(SELECT 1 -- SELECT 1 is used only to test existence inside the subquery.
FROM dbo.[Order Details] AS od
JOIN dbo.Orders AS o
	ON o.OrderID = od.OrderID
WHERE od.ProductID = p.ProductID
	AND MONTH(o.OrderDate) IN (6, 7, 8)) -- Correlates the product with orders placed in June, July, or August.
ORDER BY p.ProductName;

/* Task 15 */
SELECT
    FirstName,
    LastName,
    HireDate -- Returns employees hired after the reference employee.
FROM dbo.Employees
WHERE HireDate > (SELECT HireDate
	FROM dbo.Employees
	WHERE FirstName = 'Steven'
		AND LastName  = 'Buchanan')
ORDER BY HireDate;

/* Task 16 */
SELECT DISTINCT
	c.CustomerID,
	c.CompanyName
FROM dbo.Customers AS c
WHERE EXISTS -- Checks whether a customer has at least one late shipment.
	(SELECT 1
	FROM dbo.Orders AS Dostawa_opóźniona
	WHERE Dostawa_opóźniona.CustomerID = c.CustomerID
		AND Dostawa_opóźniona.ShippedDate IS NOT NULL
		AND Dostawa_opóźniona.RequiredDate IS NOT NULL
		AND Dostawa_opóźniona.ShippedDate > Dostawa_opóźniona.RequiredDate
		AND EXISTS -- Confirms that a later order exists after the delayed shipment.
			(SELECT 1
			FROM dbo.Orders AS Dostawa_następna
			WHERE Dostawa_następna.CustomerID = c.CustomerID
				AND Dostawa_następna.OrderDate > Dostawa_opóźniona.ShippedDate))
				-- The shipment date is used as the temporal reference for the subsequent order.
ORDER BY c.CompanyName;
-- Nested EXISTS clauses model a time-dependent business condition.

/* Task 17 */
GO
CREATE OR ALTER VIEW dbo.vw_rodzaje_zamowien_klientow -- Creates or updates a reusable view for customer order-size classification.
-- The view is defined before it is queried in the next batch.
AS -- The SELECT statement defines the view output.
SELECT
	c.CompanyName AS Nazwa_klienta,
    CASE
		WHEN SUM(od.UnitPrice * od.Quantity) < 500  THEN 'Małe' -- Order value is derived from line-item price multiplied by quantity.
        WHEN SUM(od.UnitPrice * od.Quantity) < 1000 THEN 'Średnie'
        ELSE 'Duże'
    END AS Rodzaj_zamówienia
FROM dbo.Customers AS c
JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID
JOIN dbo.[Order Details] AS od
    ON o.OrderID = od.OrderID
GROUP BY
    c.CompanyName,
    o.OrderID;
GO -- Separates SQL Server batches so the view can be created before it is queried.
-- The workflow creates the view, queries it, and then removes it.

SELECT -- Counts how many orders of each size category belong to each customer.
    Nazwa_klienta,
    Rodzaj_zamówienia,
    COUNT(*) AS Liczba_zamówień
FROM vw_rodzaje_zamowien_klientow
GROUP BY
    Nazwa_klienta,
    Rodzaj_zamówienia
ORDER BY
    Nazwa_klienta,
    Rodzaj_zamówienia;
GO

DROP VIEW vw_rodzaje_zamowien_klientow; -- Removes the temporary view after the analysis.
GO