2nd one

DELIMITER $
DROP procedure if exists testProc $
Create procedure testProc()
BEGIN
select c.name as coach, sg.id as groupID
from coaches as c
join sportGroups as sg
on c.id = sg.coach_id;
END $
DELIMITER ;

call testProc();

DELIMITER $
DROP procedure if exists testProc $
Create procedure testProc(in testParam VARCHAR(255))
BEGIN
select testParam;
set testParam= 'Ivan';
select testParam;
END $
DELIMITER ;

set @testName = 'Georgi';

call testProc(@testName);
select @testName;




use school_sport_clubs;
#drop procedure checkMothTax;
delimiter |
CREATE procedure checkMothTax(IN studId INT, IN groupId INT, IN paymentMonth INT, IN paymentYear INT)
BEGIN
DECLARE result char(1);
SET result = 0;
	IF( (SELECT paymentAmount
		FROM taxespayments
		WHERE student_id = studId
		AND group_id = groupId
		AND MONTH = paymentMonth
		AND year = paymentYear) IS NOT NULL)
    THEN
		SET result = 1;
	ELSE
		SET result = 0;
    END IF;

SELECT result as IsTaxPayed;
end;
|
delimiter ;
CALL `school_sport_clubs`.`checkMothTax`(1, 1,1,2020);





use school_sport_clubs;
#drop procedure getAllPaymentsAmountOptimized;
delimiter |
CREATE procedure getAllPaymentsAmountOptimized(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studId INT)
BEGIN
    DECLARE iterator int;
    CREATE TEMPORARY TABLE tempTbl(
    student_id int,
    group_id int,
    paymentAmount double,
    month int
    ) ENGINE = Memory;


	IF(firstMonth >= secMonth)
    THEN
		SELECT 'Please enter correct months!' as RESULT;
	ELSE IF((SELECT COUNT(*)
			FROM taxesPayments
			WHERE student_id =studId ) = 0)
        THEN SELECT 'Please enter correct student_id!' as RESULT;
		ELSE

	SET ITERATOR = firstMonth;

		WHILE(iterator >= firstMonth AND iterator <= secMonth)
		DO
			INSERT INTO tempTbl
			SELECT student_id, group_id, paymentAmount, month
			FROM taxespayments
			WHERE student_id = studId
			AND year = paymentYear
			AND month = iterator;

			SET iterator = iterator + 1;
		END WHILE;
		END IF;

    END IF;
		SELECT *
        FROM tempTbl;
        DROP TABLE tempTbl;
END;
|
DELIMITER ;
CALL  getAllPaymentsAmountOptimized(1,6,2021,1);

-- 1 1. Създайте процедура, с която по подадено име на треньор се
-- извеждат името на спорта, мястото, часът и денят на тренировка,
-- както и имената и телефоните на учениците, които тренират.

DELIMITER $
DROP procedure if exists firstTask $
Create procedure firstTask(IN coachParam VARCHAR(255))
BEGIN
select coaches.name as coach, sports.name as sport, sportGroups.location, sportGroups.hourOfTraining, sportGroups.dayOfWeek, students.name as student, students.phone
from sports
join sportGroups
on sports.id = sportGroups.sport_id
join student_sport
on sportGroups.id = student_sport.sportGroup_id
join students
on students.id = student_sport.student_id
join coaches
on coaches.id = sportGroups.coach_id
and coaches.name = coachParam;
END $
DELIMITER ;

call firstTask('Ivan Todorov Petkov');


-- 2. Създайте процедура, с която по подадено id на спорт се извеждат: името на спорта, имената на учениците,
-- които тренират и имената на треньорите, които водят тренировките по този спорт.
delimiter $
DROP procedure if exists SecondTask $
CREATE PROCEDURE SecondTask(IN idParam INT)
begin
SET @sportID = idParam;
SELECT sports.id as sport_ID, sports.name as Sport, students.name as Student, coaches.name as Coach
from students
join student_sport
on students.id = student_sport.student_id
join sportGroups
on sportGroups.id = student_sport.sportGroup_id
join coaches
on coaches.id = sportGroups.coach_id
join sports
on sports.id = sportGroups.sport_id
and sports.id = @sportID;
END $
DELIMITER ;

CALL SecondTask(1);

-- 3. Създайте процедура, която по подадено име на студент и година извежда средната сума на платените от него такси.
delimiter $
DROP procedure if exists ThirdTask $
CREATE PROCEDURE ThirdTask(IN studentName VARCHAR(255), yearParam INT)
BEGIN
SELECT students.name, taxesPayments.year, AVG(paymentAmount) as Average
from taxesPayments
join students
on students.id = taxesPayments.student_id
and students.name = studentName
and taxesPayments.year = yearParam;
END $
DELIMITER ;

Call ThirdTask('Elena Petrova Petrova', 2022);

--  4. Използвайте базата данни transaction_test. Създайте процедура
-- за прехвърляне на пари от една сметка в друга. Нека процедурата
-- да извежда съобщение за грешка ако няма достатъчно пари, за да
-- се осъществи успешно трансакцията или ако трансакцията е
-- неуспешна. За целта може да използвате функцията ROW_COUNT(),
-- която връща броя на засегнатите редове след последната Update
-- или Delete заявка. Процедурата да получава като параметри ID на
-- сметката от която се прехвърля, ID на сметката на получателя и
-- сумата, която трябва да се преведе.

delimiter $
DROP procedure if exists thirdTask $
create procedure thirdTask(IN IDsend int, IDreceive INT, amountParam INT)
BEGIN
START TRANSACTION;
		IF((SELECT ca.amount FROM customer_accounts AS ca WHERE ca.id = IDsend) < amountParam OR amountParam<0)  THEN ROLLBACK;

        ELSE
			UPDATE customer_accounts
			SET amount = amount - amountParam
			WHERE customer_accounts.id = IDsend;
			IF (ROW_COUNT() != 1) THEN ROLLBACK;

			ELSE
			UPDATE customer_accounts
			SET amount = amount + amountParam
			WHERE customer_accounts.id = IDreceive;
				IF (ROW_COUNT() != 1) THEN ROLLBACK;
				END IF;
            END IF;
		END IF;
        IF (ROW_COUNT() != 1) THEN SELECT 'FAIL';
        END IF;
	COMMIT;
END $
DELIMITER ;



CALL thirdTask(1,2,541);

SELECT * FROM customer_accounts
JOIN customers ON customers.id = customer_accounts.customer_id;