SELECT City, CompanyName, ContactName, 'Customer' AS Relationship
FROM customers
UNION
SELECT City, CompanyName, ContactName, 'Supplier' AS Relationship
FROM Suppliers ;
DELIMITER $$ -- Procedura per nxjerrjen e nje raporti per konsumatorin (numrin e porosive), (Shpenzimi total i realizuar)
-- Daten porosise se pare te realizuar, Daten e porosise se fundit te realizuar
DROP PROCEDURE IF EXISTS Cus_info $$
CREATE PROCEDURE Cus_info (IN Cus_ID CHAR(5))
BEGIN
DECLARE Nr_Orders INT;
DECLARE Total_amount FLOAT;
DECLARE First_date DATE;
DECLARE Last_date DATE;
SELECT COUNT(OrderID) INTO Nr_orders
FROM orders
WHERE CustomerID = Cus_ID;
SELECT SUM(b.UnitPrice * b.Quantity * (1-b.Discount)) INTO Total_amount
FROM orders AS a
INNER JOIN
order_details AS b ON a.OrderID = b.OrderID
WHERE a.CustomerID = Cus_ID;
SELECT MIN(OrderDate) INTO First_date
FROM orders
WHERE CustomerID = Cus_ID;
SELECT MAX(OrderDate) INTO Last_date
FROM orders
WHERE CustomerID = Cus_ID;
SELECT CustomerID, CompanyName, Nr_Orders, Total_amount, First_date,
Last_date
FROM Customers
WHERE CustomerID = Cus_ID;
END $$
DELIMITER ;
-- -------------------------------------------------------------------------------------------------------------
DELIMITER $$ -- Procedure qe kerkon ne baze te emrit per nr total te porosive te realizuar nga secili punonjes
DROP PROCEDURE IF EXISTS Search_emp $$
CREATE PROCEDURE Search_emp (IN FName VARCHAR(25))
BEGIN
SELECT CONCAT(b.EmployeeID,'-', b.FirstName,' ', b.LastName) AS Employee,
Count(a.OrderID) AS Num_orders
FROM orders AS a
INNER JOIN
employees AS b ON a.EmployeeID = b.EmployeeID
WHERE b.FirstName LIKE CONCAT('%',FName,'%')
GROUP BY b.EmployeeID;
END $$
DELIMITER ;
-- -----------------------------------------------------------------------------------------------------------------
DELIMITER $$ -- procedure qe nxjerr 5 produktet me te shitura ne vitin e dhene
DROP PROCEDURE IF EXISTS Max_Product $$
CREATE PROCEDURE Max_Product (IN Prod_Year YEAR)
BEGIN
SELECT b.ProductID, a.ProductName, SUM(b.Quantity) AS Prod_order
FROM products AS a
INNER JOIN
order_details AS b ON b.ProductID = a.ProductID
INNER JOIN
orders AS c ON c.OrderID = b.OrderID
WHERE YEAR(c.OrderDate) = Prod_Year
GROUP BY b.ProductID
ORDER BY Prod_order DESC
LIMIT 5;
END $$
DELIMITER ;
-- ------------------------------------------------------------------------------------------------------------------
DELIMITER $$ -- procedure qe nxjerr numrin e porosive te realizuar nga secili punetor ne harkun kohor te dy datave
DROP PROCEDURE IF EXISTS Emp_orders $$
CREATE PROCEDURE Emp_orders (IN Start_date DATE, IN End_date DATE)
BEGIN
SELECT a.EmployeeID, a.FirstName, a.LastName,
COUNT(b.OrderID) AS Nr_orders
FROM employees AS a
LEFT OUTER JOIN
orders AS b ON a.EmployeeID = b.EmployeeID
WHERE b.OrderDate BETWEEN Start_date AND End_Date
GROUP BY a.EmployeeID;
END $$
DELIMITER ;
