1st one

CREATE VIEW coachInfo(Coach, Sport, Day)
AS
Select coaches.name, sports.name, sportGroups.dayOfWeek
from coaches join sportGroups
on coaches.id = sportGroups.coach_id
join sports on sports.id = sportGroups.sport_id;



select * from coachInfo;

Drop View coachInfo;

-- 1. Изведете имената, класовете и телефоните на всички ученици, които тренират футбол.

SELECT students.name, students.class, students.phone, sports.name
FROM students
JOIN student_sport
on students.id = student_sport.student_id
JOIN sportGroups
ON student_sport.sportGroup_id = sportGroups.id
JOIN sports
ON sportGroups.sport_id = sports.id
AND sports.name = "Football";

-- 2. Изведете имената на всички треньори по волейбол.

SELECT coaches.name, sports.name
FROM coaches
JOIN sportGroups
ON coaches.id = sportGroups.coach_id
JOIN sports
ON sportGroups.sport_id = sports.id
AND sports.name = "Volleyball";

-- 3. Изведете името на треньора и спорта, който тренира ученик с име Илиян Иванов.

SELECT distinct coaches.name, sports.name, students.name
FROM coaches
JOIN sportGroups
ON coaches.id = sportGroups.coach_id
JOIN sports
ON sports.id = sportGroups.sport_id
JOIN student_sport
ON student_sport.sportGroup_id = sportGroups.id
JOIN students
ON students.id = student_sport.student_id
AND students.name = "Iliyan Ivanov";

-- 4. Изведете сумите от платените през годините такси на учениците по месеци, но само за
-- ученици с такси по месеци над 700 лева и с треньор с ЕГН 7509041245.

select students.name, SUM(taxesPayments.paymentAmount) as sumPayment, taxesPayments.year
from taxesPayments
JOIN students
on taxesPayments.student_id = students.id
JOIN sportGroups
on sportGroups.id = taxesPayments.group_id
join coaches
on coaches.id = sportGroups.coach_id
and coaches.egn = '7509041245'
group by taxesPayments.student_id, taxesPayments.year
having SUM(paymentAmount) > 700;

-- 5. Изведете броя на студентите във всяка една от групите.

SELECT count(students.id) as numberOfStudents, sportGroups.id as Group_ID
from students
join student_sport
on student_sport.student_id = students.id
right JOIN sportGroups
ON student_sport.sportGroup_id = sportGroups.id
group by sportGroups.id;

-- 6. Определете двойки ученици на базата на спортната група, в която тренират, като
-- двойките не се повтарят. В двойките да участват само ученици, трениращи футбол.
-- Учениците от една двойка трябва да тренират в една и съща група.
SELECT firstpl.name as firstPlayer, secondpl.name as secondPlayer, sports.name as sportName
FROM students as firstpl JOIN students as secondpl
ON firstpl.id > secondpl.id
JOIN sports ON (
        secondpl.id IN(
        SELECT student_id
        FROM student_sport
        WHERE sportGroup_id IN(
        SELECT id
        FROM sportgroups
        WHERE sport_id = sports.id
        )
        AND sports.name = 'Football'
        )
AND (firstPl.id IN
    ( SELECT student_id
    FROM student_sport
    WHERE sportGroup_id IN(
    SELECT id
    FROM sportgroups
    WHERE sport_id = sports.id)
                )
    )
)
WHERE firstPL.id IN(
      SELECT student_id
      FROM student_sport
      WHERE sportGroup_id IN(
            SELECT sportGroup_id
            FROM student_sport
            WHERE student_id = secondPl.id
        )
)
ORDER BY firstPlayer;


-- 7. Изведете имената на учениците, класовете им, местата на тренировки и името на
-- треньорите за тези ученици, чийто тренировки започват в 8.00 часа. Създайте виртуална
-- таблица с този селект.

CREATE VIEW `Virtual_Table` AS
SELECT students.name as Student_Name, students.class, sportGroups.location, coaches.name as Coach_Name, sportGroups.hourOfTraining
from students
JOIN student_sport
on students.id = student_sport.student_id
JOIN sportGroups
ON student_sport.sportGroup_id = sportGroups.id
AND sportGroups.hourOfTraining = '08:00:00'
JOIN coaches
on coaches.id = sportGroups.coach_id;

select * from Virtual_Table;

-- 8. Използвайте базата данни transaction_test.
-- Създайте трансакция, с която прехвърляте 50000 лв. от сметката в лева на Stoyan Pavlov Pavlov в сметката в лева на Ivan Petrov Iordanov
-- при подадени само име на титуляр и вид валута.

BEGIN;
update customer_accounts
set amount = amount - 50000
where id = 3 and customer_accounts.currency = "BGN";

update customer_accounts
set amount = amount + 50000
where id = 1 and customer_accounts.currency = "BGN";
COMMIT;

select * from customer_accounts;