dbtutorials

DELIMITER $$

DROP FUNCTION IF EXISTS mosha  $$

CREATE FUNCTION mosha( Datelindja DATE) RETURNS INT
READS SQL DATA
BEGIN
DECLARE emp_age INT;
   
    SET emp_age = YEAR(NOW()) - YEAR(Datelindja);
   
    RETURN emp_age ;

END $$

DELIMITER ;

-- ------------------------------------------------------------------------

SELECT employeeID, FirstName, LastName, mosha(Birthdate) AS Emp_age
FROM employees;

-- ------------------------------------------------------------------------



DELIMITER $$

DROP FUNCTION IF EXISTS Cus_NrOrders $$

CREATE FUNCTION Cus_NrOrders ( CusID CHAR(5)) RETURNS INT
READS SQL DATA
BEGIN
DECLARE Cus_orders INT;
   
    SET Cus_orders = (SELECT COUNT(*) FROM orders WHERE CustomerID = CusID);
   
RETURN Cus_Orders;

END $$

DELIMITER ;

-- ------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_NrOrders(CustomerID) AS Total
FROM Customers

-- -------------------------------------------------------------------------



DELIMITER $$
DROP FUNCTION IF EXISTS Cus_Total $$

CREATE FUNCTION Cus_Total ( CusID CHAR(5)) RETURNS DOUBLE
READS SQL DATA
BEGIN
DECLARE Cus_sum DOUBLE;
   
    SET Cus_sum = (SELECT SUM(b.UnitPrice * b.Quantity * (1-b.Discount))
  FROM orders AS a
INNER JOIN
   order_details AS b ON a.OrderID = b.OrderID
  WHERE a.CustomerID = CusID);
   
RETURN Cus_sum;
END $$
DELIMITER ;

-- ------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_Total(CustomerID) AS Total
FROM Customers

-- ------------------------------------------------------------------------




DELIMITER $$
DROP FUNCTION IF EXISTS VIP_Check $$

CREATE FUNCTION VIP_CHECK (CusID CHAR(5)) RETURNS VARCHAR(15)
READS SQL DATA
BEGIN
DECLARE Third_Cus INT;
    DECLARE Cus_NumOrder INT;
    DECLARE Cus_Result VARCHAR(15);
   
    SET Third_Cus = ( SELECT COUNT(*) AS Num_orders
FROM orders
GROUP BY CustomerID
ORDER BY Num_Orders DESC
LIMIT 2, 1
);
SET Cus_numOrder = (SELECT COUNT(*) FROM orders WHERE CustomerID = CusID);

    IF Cus_numOrder >= Third_Cus THEN
SET Cus_Result = 'VIP';
ELSE
SET Cus_Result = 'NON-VIP';
    END IF;
   
    RETURN Cus_Result ;
END $$
DELIMITER ;

-- -----------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_NrOrders(CustomerID) AS Num_Orders,
 VIP_CHECK(CustomerID) AS Cus_Status
FROM Customers
ORDER BY Num_Orders DESC ;

-- -----------------------------------------------------------------------------




DELIMITER $$

DROP FUNCTION IF EXISTS Emp_NumOrders $$

CREATE FUNCTION Emp_NumOrders (EmpID INT) RETURNS DOUBLE
READS SQL DATA
BEGIN
DECLARE Emp_Total DOUBLE;

    SET Emp_Total = (SELECT SUM(b.UnitPrice * b.Quantity * (1-b.Discount))
FROM orders AS a
INNER JOIN
  order_details AS b ON a.OrderID = b.OrderID
WHERE a.EmployeeID = EmpID
                     );
   
RETURN Emp_total;
END $$

DELIMITER ;

-- ------------------------------------------------------------------------

SELECT EmployeeID, FirstName, LastName,
Emp_NumOrders(EmployeeID) AS Total_sum
FROM employees

-- --------------------------------------------------------------------------




DELIMITER $$
DROP FUNCTION IF EXISTS Emp_Order_status $$

CREATE FUNCTION  Emp_Order_status (EmpID INT) RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
DECLARE Emp_status VARCHAR(20);
    DECLARE Emp_avg DECIMAL(6,2);
    DECLARE Emp_Num_orders INT;
   
SET Emp_avg = ( SELECT AVG(t1.Num_orders) AS Emp_AvgOrders
FROM ( SELECT EmployeeID, Count(OrderID) AS Num_orders
FROM orders
GROUP BY EmployeeID
) AS t1
   );
SET Emp_Num_orders = ( SELECT COUNT(*)
FROM orders
                            WHERE employeeID = EmpID
);
                       
IF Emp_Num_orders >= Emp_avg  THEN
SET Emp_status = 'Above Average';
ELSE
SET Emp_status = 'Below Average';
    END IF;
   
    Return Emp_status;
END $$
DELIMITER ;

-- ----------------------------------------------------------------------------------

SELECT EmployeeID, FirstName, LastName,
Emp_Order_status(EmployeeID) AS Emp_status
FROM employees

-- -----------------------------------------------------------------------------------




DELIMITER $$

DROP FUNCTION IF EXISTS Cus_Year_OrdersNUM $$

CREATE FUNCTION Cus_Year_OrdersNUM (CusID CHAR(5), Orders_Year INT) RETURNS INT
READS SQL DATA
BEGIN
DECLARE Year_Orders INT;
   
SET Year_Orders = ( SELECT COUNT(OrderID)
FROM orders
                        WHERE CustomerID = CusID AND YEAR(OrderDate) = Orders_Year 
    );
   
    RETURN Year_Orders;
END $$
DELIMITER ;

-- ----------------------------------------------------------------------------------

SELECT CustomerID, CompanyName, Cus_Year_OrdersNUM(CustomerID, 2008) AS Orders2008
FROM Customers

-- ----------------------------------------------------------------------------------