use moneyball;

select * from performances;
select * from players;
select * from salaries;
select * from teams;

-- Task 1
select round(avg(salary), 2) as "average salary", `year` from salaries group by `year` order by `year` desc;

-- Task 2
select * from players where first_name = "Cal" and last_name = "Ripken"; -- player_id = 15726
select round(avg(salary), 2) as "average salary", `year` from salaries where player_id = 15726 order by `year` desc;

select round(avg(salary), 2) as "average salary", `year`
from salaries
where player_id in (
    select id from players where first_name = "Cal" and last_name = "Ripken"
) group by `year` order by `year` desc;

-- Task 3
select * from players where first_name = "Ken" and last_name = "Griffey" and birth_year = 1969; -- player_id = 7266
select HR, `year` from performances where player_id = 7266;

select HR, `year`
from performances
where player_id in (
    select id from players where first_name = "Ken" and last_name = "Griffey" and birth_year = 1969
);

-- Task 4
select * from salaries where `year` = 2001 order by salary limit 50;
select first_name, last_name from  players order by first_name and last_name or id;

SELECT
(SELECT first_name FROM players WHERE id = player_id) AS first_name,
(SELECT last_name FROM players WHERE id = player_id) AS last_name,
salary
FROM salaries
WHERE `year` = 2001 ORDER BY salary ASC, first_name ASC, last_name ASC, player_id ASC LIMIT 50;

-- Task 5
select * from players where first_name = "Satchel" and last_name = "Paige"; -- player_id = 14190
select * from performances where player_id = 14190;

select `name`
from teams
where id in (
    select team_id from performances where player_id in (
        select id from players where first_name = "Satchel" and last_name = "Paige"
    )
);

-- Task 6
select
(select `name` from teams where id = team_id) as "Team Name",
H as "Total hits"
from performances where `year` = 2001 group by team_id order by H desc limit 5;

-- Task 7
select *, max(salary) from salaries order by salary desc; -- 22000000 player_id = 15912
select * from salaries order by salary desc;
select * from players where id = 15912;

select
(select first_name from players where id = player_id) as "First Name",
(select last_name from players where id = player_id) as "Last Name",
max(salary) as "Salary"
from salaries;

 -- player_id = 15912
 select first_name, last_name
 from players
 where id in (
    select player_id from salaries where salary = (select max(salary) from salaries)
 );

 -- Task 8
 select * from performances where `year` = 2001 order by H desc; -- player_id = 18284
  select max(H) from performances where `year` = 2001;
 select * from salaries where player_id = 18284;

 select salary
 from salaries
 where player_id in (
    select player_id from performances where H in (
     select max(H) from performances where `year` = 2001
    )
 );

 -- Task 9
select *, min(salary) from salaries where `year` = 2001;
select *, round(avg(salary),2) as "Average Salary" from salaries  where `year` = 2001 group by team_id order by round(avg(salary),2) asc limit 5;

select
(select `name` from teams where id = team_id) as "Team Name",
round(avg(salary),2) as "Average Salary"
from salaries
where `year` = 2001 group by team_id order by round(avg(salary),2) asc limit 5;

-- Task 10 име и фамилия на всеки играч, заплати, хоумрънове, годината на получената заплата и кога е постигнал хоумръновете
select first_name, last_name from players;
select H, `year` from performances;
select salary, `year` from salaries;

select
(select HR, `year` from performances where player_id = id group by `year`) as "home runs for each year",
(select salary, `year` from salaries where player_id = id group by `year`) as "salary for each year",
first_name, last_name
from players order by id;

select
(select first_name from players where id = player_id) as "first name",
(select last_name from players where id = player_id) as "last name",
salary,
`year`,
(select sum(HR) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`) as hr
from salaries as s
order by player_id, `year` desc, hr desc, salary desc;

select * from performances where player_id = 896 and year = 2001;

select
(select first_name from players where id = player_id) as FirstName,
(select last_name from players where id = player_id) as LastName,
(salary / (select sum(h) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`)) as DollarsPerHit,
salary
from salaries as s
where `year` = 2001
group by player_id
having DollarsPerHit > 0
order by DollarsPerHit asc, FirstName, LastName
limit 10;

select
(select first_name from players where id = player_id) as FirstName,
(select last_name from players where id = player_id) as LastName,
(salary / (select sum(h) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`)) as DollarsPerHit,
(salary / (select sum(RBI) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`)) as DollarsPerRBI,
salary,
((salary / (select sum(h) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`)) + (salary / (select sum(RBI) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`))) AS "sum"
from salaries as s
where `year` = 2001
group by player_id
having DollarsPerHit > 0 && DollarsPerRBI > 0
order by ((salary / (select sum(h) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`)) + (salary / (select sum(RBI) from performances as p where p.player_id = s.player_id AND p.`year` = s.`year`))) , DollarsPerRBI, FirstName, LastName
limit 10;