-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';
-- 1. From the following tables, write a SQL query to find the venue where EURO cup 2016 final match held. Return venue name, city.

SELECT venue_name, city
FROM soccer_venue a
JOIN soccer_city b ON a.city_id=b.city_id
JOIN match_mast d ON d.venue_id=a.venue_id 
AND d.play_stage='F';
-- 2. From the following tables, write a SQL query to find the number of goal scored by each team in every match within normal play schedule. Return match number, country name and goal score.
SELECT match_no,country_name,goal_score
FROM match_details a
JOIN soccer_country b
ON a.team_id=b.country_id
WHERE decided_by='N'
ORDER BY match_no;

3. From the following tables, write a SQL query to count the number of goals scored by each player within normal play schedule. Group the result set on player name and country name and sorts the result-set according to the highest to the lowest scorer. Return player name, number of goals and country name.
SELECT player_name,country_name,count(*) FROM goal_details a JOIN player_mast b on a.player_id=b.player_id JOIN soccer_country c ON a.team_id=c.team_id  WHERE goal_schedule='NT' GROUP BY player_name,country_name order by count(*) DESC;

-- 4. From the following tables, write a SQL query to find the highest individual scorer in EURO cup 2016. Return player name, country name and highest individual scorer.
SELECT player_name,country_name,count(player_name)
FROM goal_details gd
JOIN player_mast pm ON gd.player_id =pm.player_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
GROUP BY country_name,player_name HAVING COUNT(player_name) >= ALL
  (SELECT COUNT(player_name)
   FROM goal_details gd
   JOIN player_mast pm ON gd.player_id =pm.player_id
   JOIN soccer_country sc ON pm.team_id = sc.country_id
   GROUP BY country_name,player_name);
-- 5. From the following tables, write a SQL query to find the scorer in the final of EURO cup 2016. Return player name, jersey number and country name.
SELECT player_name,jersey_no,country_name
FROM goal_details a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
WHERE play_stage='F';

-- 6. From the following tables, write a SQL query to find the country where Football EURO cup 2016 held. Return country name.
SELECT country_name
FROM soccer_country a
JOIN soccer_city b ON a.country_id=b.country_id
JOIN soccer_venue c ON b.city_id=c.city_id
GROUP BY country_name;
-- 7. From the following tables, write a SQL query to find the player who scored first goal of EURO cup 2016. Return player_name, jersey_no, country_name, goal_time, play_stage, goal_schedule, goal_half.
SELECT a.player_name,a.jersey_no,b.country_name,c.goal_time,
c.play_stage,c.goal_schedule, c.goal_half 
FROM player_mast a
JOIN soccer_country b
ON a.team_id=b.country_id
JOIN goal_details c
ON c.player_id=a.player_id
WHERE goal_id=1;

-- 8. From the following tables, write a SQL query to find the referee who managed the opening match. Return referee name, country name.
SELECT b.referee_name, c.country_name 
FROM match_mast a
NATURAL JOIN referee_mast b 
NATURAL JOIN soccer_country c
WHERE match_no=1;

-- From the following tables, write a SQL query to find the referee who managed the final match. Return referee name, country name.
SELECT b.referee_name, c.country_name 
FROM match_mast a
NATURAL JOIN referee_mast b 
NATURAL JOIN soccer_country c
WHERE play_stage='F';
-- 
SELECT ass_ref_name, country_name 
FROM asst_referee_mast a
JOIN soccer_country b
ON a.country_id=b.country_id
JOIN match_details c
ON a.ass_ref_id=c.ass_ref
WHERE match_no=1;

-- From the following tables, write a SQL query to find the referee who assisted the referee in the final match. Return associated referee name, country name.
SELECT ass_ref_name, country_name 
FROM asst_referee_mast a
JOIN soccer_country b
ON a.country_id=b.country_id
JOIN match_details c
ON a.ass_ref_id=c.ass_ref
WHERE play_stage='F';

-- 12. From the following tables, write a SQL query to find the city where the opening match of EURO cup 2016 played. Return venue name, city.
SELECT a.venue_name, b.city
FROM soccer_venue a
JOIN soccer_city b ON a.city_id=b.city_id
JOIN match_mast c ON a.venue_id=c.venue_id
WHERE match_no=1;

-- 13. From the following tables, write a SQL query to find the stadium hosted the final match of EURO cup 2016. Return venue_name, city, aud_capacity, audience.
SELECT a.venue_name, b.city, a.aud_capacity, c.audence
FROM soccer_venue a
JOIN soccer_city b ON a.city_id=b.city_id
JOIN match_mast c ON a.venue_id=c.venue_id
WHERE play_stage='F';
-- 14. From the following tables, write a SQL query to count the number of matches played in each venue. Sort the result-set on venue name. Return Venue name, city, and number of matches.
SELECT a.venue_name, b.city, count(c.match_no)
FROM soccer_venue a
JOIN soccer_city b ON a.city_id=b.city_id
JOIN match_mast c ON a.venue_id=c.venue_id
GROUP BY venue_name,city
ORDER BY venue_name;
-- 15. From the following tables, write a SQL query to find the player who was the first player to be sent off at the tournament EURO cup 2016. Return match Number, country name and player name.
SELECT match_no, country_name, player_name, 
booking_time as "sent_off_time", play_schedule, jersey_no
FROM player_booked a
JOIN player_mast b
ON a.player_id=b.player_id
JOIN soccer_country c
ON a.team_id=c.country_id
AND  a.sent_off='Y'
AND match_no=(
	SELECT MIN(match_no) 
	from player_booked)
ORDER BY match_no,play_schedule,play_half,booking_time;
-- 16. From the following tables, write a SQL query to find those teams that scored only one goal to the tournament. Return country_name as "Team", team in the group, goal_for.
SELECT country_name as "Team" ,team_group, goal_for
FROM soccer_team
JOIN soccer_country 
ON soccer_team.team_id=soccer_country.country_id
AND goal_for=1;

-- 17. From the following tables, write a SQL query to count the yellow cards received by each country. Return country name and number of yellow cards
SELECT country_name, COUNT(*)
FROM soccer_country 
JOIN player_booked
ON soccer_country.country_id=player_booked.team_id
GROUP BY country_name
ORDER BY COUNT(*) DESC;
-- 18. From the following tables, write a SQL query to count number of goals that has seen. Return venue name and number of goals.
SELECT venue_name, count(venue_name)
FROM goal_details
JOIN soccer_country
ON goal_details.team_id=soccer_country.country_id
JOIN match_mast ON goal_details.match_no=match_mast.match_no
JOIN soccer_venue ON match_mast.venue_id=soccer_venue.venue_id
GROUP BY venue_name
ORDER BY COUNT(venue_name) DESC;

-- 19. From the following tables, write a SQL query to find the match where no stoppage time added in first half of play. Return match number, country name.
SELECT match_details.match_no, soccer_country.country_name 
FROM match_mast
JOIN match_details 
ON match_mast.match_no=match_details.match_no
JOIN soccer_country
ON match_details.team_id=soccer_country.country_id
WHERE stop1_sec=0;
-- 20. From the following tables, write a SQL query to find the team(s) who conceded the most goals in EURO cup 2016. Return country name, team group and match played.
SELECT country_name,team_group,match_played,
won,lost,goal_for,goal_agnst
FROM soccer_team 
JOIN soccer_country 
ON soccer_team.team_id=soccer_country.country_id
WHERE goal_agnst=(
SELECT MAX(goal_agnst) 
FROM soccer_team);

-- 21. From the following tables, write a SQL query to find those matches where highest stoppage time added in 2nd half of play. Return match number, country name, stoppage time(sec.).
SELECT match_details.match_no, soccer_country.country_name,
match_mast.stop2_sec as "Stoppage Time(sec.)" 
FROM match_mast
JOIN match_details 
ON match_mast.match_no=match_details.match_no
JOIN soccer_country
ON match_details.team_id=soccer_country.country_id
WHERE stop2_sec IN (
SELECT MAX(stop2_sec) 
FROM match_mast);

-- 22. From the following tables, write a SQL query to find those matches ending with a goalless draw in-group stage of play. Return match number, country name.
SELECT match_no,country_name 
FROM match_details
JOIN soccer_country ON soccer_country.country_id=match_details.team_id
WHERE win_lose='D' AND goal_score=0 AND play_stage='G' 
ORDER BY match_no;
-- 23. From the following tables, write a SQL query to find those match(s) where the 2nd highest stoppage time had been added in the second half of play. Return match number, country name and stoppage time.
SELECT a.match_no, c.country_name, a.stop2_sec
FROM match_mast a
JOIN match_details b ON a.match_no=b.match_no
JOIN soccer_country c ON b.team_id=c.country_id
WHERE (2-1) = (
SELECT COUNT(DISTINCT(b.stop2_sec))
FROM match_mast b
WHERE b.stop2_sec > a.stop2_sec);

-- 24. From the following tables, write a SQL query to find the number of matches played a player as a goalkeeper for his team. Return country name, player name, number of matches played as a goalkeeper.
SELECT b.country_name,c.player_name,COUNT(a.player_gk) count_gk
FROM match_details a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_gk=c.player_id
GROUP BY b.country_name,c.player_name
ORDER BY country_name,player_name,count_gk DESC;

-- 25. From the following tables, write a SQL query to find the venue that has seen the most number of goals. Return venue name, number of goals.
SELECT venue_name, count(venue_name)
FROM goal_details
JOIN soccer_country
ON goal_details.team_id=soccer_country.country_id
JOIN match_mast ON goal_details.match_no=match_mast.match_no
JOIN soccer_venue ON match_mast.venue_id=soccer_venue.venue_id
GROUP BY venue_name
HAVING COUNT (venue_name)=( 
SELECT MAX(mycount) 
FROM ( 
SELECT venue_name, COUNT(venue_name) mycount
FROM goal_details
JOIN soccer_country
ON goal_details.team_id=soccer_country.country_id
JOIN match_mast ON goal_details.match_no=match_mast.match_no
JOIN soccer_venue ON match_mast.venue_id=soccer_venue.venue_id
GROUP BY venue_name) gd);

-- From the following tables, write a SQL query to find the oldest player appeared in a EURO cup 2016 match. Return country name, player name, jersey number and age.
26. From the following tables, write a SQL query to find the oldest player appeared in a EURO cup 2016 match. Return country name, player name, jersey number and age
SELECT a.country_name,b.player_name,b.jersey_no,b.age FROM soccer_country a JOIN player_mast b ON a.country_id=b.team_id WHERE b.age IN(SELECT MAX(age) FROM player_mast);

27. From the following tables, write a SQL query to find those two teams, scored three goals in a single game in this tournament. Return match number and country name.
 From the following tables, write a SQL query to find those two teams, scored three goals in a single game in this tournament. Return match number and country name.
 SELECT a.country_name as Team , b.team_group,b.match_played, 
b.goal_agnst, b.group_position 
FROM soccer_country a
JOIN soccer_team b
ON a.country_id=b.team_id
WHERE goal_agnst=4 AND group_position=4
ORDER BY team_group;
--
SELECT a.player_name, a.jersey_no, a.posi_to_play, a.age, b.country_name
FROM player_mast a 
JOIN soccer_country b
ON a.team_id=b.country_id
WHERE a.playing_club='Lyon'
AND a.team_id IN (
SELECT b.country_id 
FROM soccer_country b
WHERE b.country_id IN (
SELECT c.team_id 
FROM match_details c 
WHERE c.play_stage='F'
));

-- 30. From the following tables, write a SQL query to find the final four teams in the tournament. Return country name.
SELECT country_name
FROM match_details a
JOIN soccer_country b
ON a.team_id=b.country_id
WHERE play_stage='S';
-- 31. From the following tables, write a SQL query to find the captains of the top four teams that participated in the semi-finals (match 48 and 49) in the tournament. Return country name, player name, jersey number and position to play.
SELECT country_name, player_name, jersey_no, posi_to_play 
FROM match_captain a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_captain=c.player_id
WHERE match_no IN(48,49);

-- 32. From the following tables, write a SQL query to find the captains of all the matches in the tournament. Return match number, country name, player name, jersey number and position to play.
SELECT match_no,country_name, player_name, jersey_no, posi_to_play 
FROM match_captain a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_captain=c.player_id
ORDER BY match_no;
-- 33 From the following tables, write a SQL query to find the captain and goalkeeper of all the matches. Return match number, Captain, Goal Keeper and country name.
SELECT a.match_no,c.player_name as "Captain", 
d.player_name as "Goal Keeper",e.country_name
FROM match_captain a
NATURAL JOIN match_details b
JOIN soccer_country e ON b.team_id=e.country_id
JOIN player_mast c ON a.player_captain=c.player_id
JOIN player_mast d ON b.player_gk=d.player_id;

-- 34. From the following tables, write a SQL query to find the player who was selected for the ‘Man of the Match’ award in the finals of EURO cup 2016.Return player name, country name.
SELECT a.player_name, b.country_name
FROM player_mast a 
JOIN match_mast c ON c.plr_of_match=a.player_id
AND c.play_stage='F'
JOIN soccer_country b
ON a.team_id=b.country_id;

-- 35. From the following tables, write a SQL query to find the substitute players who came into the field in the first half of play within normal play schedule. Return match_no, country_name, player_name, jersey_no and time_in_out.
SELECT match_no,country_name,player_name,jersey_no,time_in_out FROM player_in_out a JOIN player_mast b ON a.player_id=b.player_id JOIN soccer_country c ON b.team_id=c.country_id WHERE a.in_out='I'AND a.play_schedule='NT',a.play_half=1 OREDER BY match_no; 
-- 36. From the following table, write a SQL query to prepare a list for the player of the match against each match. Return match number, play date, country name, player of the Match, jersey number
SELECT match_no,play_date,country_name,
player_name AS "Player of the Match",jersey_no
FROM match_mast a
JOIN player_mast b ON 
a.plr_of_match=b.player_id
JOIN soccer_country c ON 
b.team_id=c.country_id;

-- 37. From the following tables, write a SQL query to find the player who taken the penalty shot number 26. Return match number, country name, player name.
SELECT match_no,
       country_name,
       player_name
FROM penalty_shootout a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON b.team_id=c.country_id
WHERE kick_id=26;

-- 38. From the following tables, write a SQL query to find the team against which the penalty shot number 26 had been taken. Return match number, country name.
SELECT match_no,
       country_name
FROM penalty_shootout a
JOIN soccer_country c ON a.team_id=c.country_id
WHERE match_no=
    (SELECT match_no
     FROM penalty_shootout
     WHERE kick_id=26)
  AND country_name<>
    (SELECT country_name
     FROM soccer_country
     WHERE country_id=
         (SELECT team_id
          FROM penalty_shootout
          WHERE kick_id=26))
GROUP BY match_no,
         country_name;

-- 39. From the following tables, write a SQL query to find the captain who was also the goalkeeper. Return match number, country name, player name and jersey number.
SELECT match_no,
       country_name,
       player_name,
       jersey_no
FROM match_captain a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_captain=c.player_id
AND posi_to_play='GK'
ORDER BY match_no;

-- 40. From the following tables, write a SQL query to find the number of captains who was also the goalkeeper. Return number of captains.
SELECT count(DISTINCT player_name)
FROM match_captain a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_captain=c.player_id
AND posi_to_play='GK';

-- 41. From the following tables, write a SQL query to find the players along with their team booked number of times in the tournament. Show the result according to the team and number of times booked in descending order. Return country name, player name, and team booked number of times.
SELECT a.country_name,c.player_name,COUNT(b.*) Booked 
FROM soccer_country a
JOIN player_booked b ON a.country_id=b.team_id
JOIN player_mast c ON b.player_id=c.player_id
GROUP BY a.country_name,c.player_name
ORDER BY a.country_name,Booked DESC;

-- 42. From the following tables, write a SQL query to count the players who booked the most number of times. Return player name, number of players who booked most number of times.
SELECT c.player_name,COUNT(b.*) Booked 
FROM soccer_country a
JOIN player_booked b ON a.country_id=b.team_id
JOIN player_mast c ON b.player_id=c.player_id
GROUP BY c.player_name
having COUNT(b.*)=(
SELECT MAX(mm) FROM (
SELECT COUNT(*) mm 
FROM player_booked 
GROUP BY player_id) inner_result);

-- 43. From the following tables, write a SQL query to find the number of players booked for each team. Return country name, number of players booked.
SELECT a.country_name,COUNT(b.*) Booked 
FROM soccer_country a
JOIN player_booked b ON a.country_id=b.team_id
GROUP BY a.country_name
ORDER BY Booked DESC;
-- 44. From the following tables, write a SQL query to find those matches where most number of cards shown. Return match number, number of cards shown.
SELECT match_no, Booked FROM (
SELECT match_no,COUNT(*) Booked 
FROM player_booked  
GROUP BY match_no) M1 where Booked=(
SELECT MAX(MX1) 
FROM (SELECT match_no,COUNT(*) MX1 
FROM player_booked  
GROUP BY match_no) M2);

45. From the following table, write a SQL query to find the assistant referees. Return match number, country name, assistant referee name.
SELECT a.match_no,
       b.country_name,
       c.ass_ref_name
FROM match_details a
JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
JOIN soccer_country b ON c.country_id=b.country_id
ORDER BY a.match_no;

46. From the following tables, write a SQL query to find the assistant referees of each country assists the number of matches. Sort the result-set in descending order on number of matches. Return country name, number of matches.
SELECT country_name,
       count(DISTINCT match_no)
FROM match_details a
JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY country_name
ORDER BY count(*) DESC;

-- 47. From the following table, write a SQL query to find the countries from where the assistant referees assist most of the matches. Return country name and number of matches
SELECT country_name,
       count(DISTINCT match_no)
FROM match_details a
JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY country_name
HAVING count(DISTINCT match_no)=
  (SELECT max(mm)
   FROM
     (SELECT count(DISTINCT match_no) mm
      FROM match_details a
      JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
      JOIN soccer_country b ON c.country_id=b.country_id
      GROUP BY country_name) hh);

-- 48. From the following table, write a SQL query to find the name of referees for each match. Sort the result-set on match number. Return match number, country name, referee name.
SELECT a.match_no,
       b.country_name,
       c.referee_name
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
ORDER BY a.match_no;
-- 49. From the following tables, write a SQL query to count the number of matches managed by referees of each country. Return country name, number of matches
SELECT country_name,count(match_no) FROM match_mast a JOIN referee_mast c ON a.referee_id=c.referee_id JOIN soccer_country b ON b.country_id=c.country_id GROUP BY country_name ORDER BY count(match_no) DESC;

-- 50. From the following tables, write a SQL query to find the countries from where the referees managed most of the matches. Return country name, number of matches.
SELECT country_name,
       count(match_no)
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY country_name
HAVING count(match_no)=
  (SELECT max(mm)
   FROM
     (SELECT count(match_no) mm
      FROM match_mast a
      JOIN referee_mast c ON a.referee_id=c.referee_id
      JOIN soccer_country b ON c.country_id=b.country_id
      GROUP BY country_name) hh);
-- 51. From the following tables, write a SQL query to find the number of matches managed by each referee. Return referee name, country name, number of matches.
SELECT c.referee_name,
       b.country_name,
       count(a.match_no)
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY c.referee_name,
         b.country_name;

-- 52. From the following tables, write a SQL query to find those referees who managed most of the matches. Return referee name, country name and number of matches.
SELECT c.referee_name,
       b.country_name,
       count(a.match_no)
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
GROUP BY c.referee_name,
         b.country_name
HAVING count(a.match_no) =
  (SELECT max(mm)
   FROM
     (SELECT count(a.match_no) mm
      FROM match_mast a
      JOIN referee_mast c ON a.referee_id=c.referee_id
      JOIN soccer_country b ON c.country_id=b.country_id
      GROUP BY c.referee_name,
               b.country_name) hh);
--  53. From the following tables, write a SQL query to find those referees who managed the number of matches in each venue. Return referee name, country name, venue name, number of matches.
SELECT c.referee_name,
       b.country_name,
       d.venue_name,
       count(a.match_no)
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
JOIN soccer_venue d ON a.venue_id=d.venue_id
GROUP BY c.referee_name,
         country_name,
         venue_name
ORDER BY referee_name;

-- 54. From the following tables, write a SQL query to find the referees and number of booked they made. Return referee name, number of matches.
SELECT c.referee_name,
       count(b.match_no)
FROM player_booked a
JOIN match_mast b ON a.match_no=b.match_no
JOIN referee_mast c ON b.referee_id=c.referee_id
GROUP BY referee_name
ORDER BY count(b.match_no) DESC;.

-- 55. From the following tables, write a SQL query to find those referees who booked most number of players. Return referee name, number of matches.
SELECT c.referee_name,
       count(b.match_no)
FROM player_booked a
JOIN match_mast b ON a.match_no=b.match_no
JOIN referee_mast c ON b.referee_id=c.referee_id
GROUP BY referee_name
HAVING count(b.match_no)=
  (SELECT max(mm)
   FROM
     (SELECT count(b.match_no) mm
      FROM player_booked a
      JOIN match_mast b ON a.match_no=b.match_no
      JOIN referee_mast c ON b.referee_id=c.referee_id
      GROUP BY referee_name) hh);
-- 56. From the following tables, write a SQL query to find those players of each team who wore jersey number 10. Return country name, player name, position to play, age and playing club.
SELECT country_name,
       player_name,
       posi_to_play,
       age,
       playing_club
FROM player_mast a
JOIN soccer_country b ON a.team_id=b.country_id
WHERE jersey_no=10
ORDER BY country_name;

-- 57. From the following tables, write a SQL query to find those defenders who scored goal for their team. Return player name, jersey number, country name, age and playing club.
SELECT player_name,
       jersey_no,
       country_name,
       age,
       playing_club
FROM goal_details a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
WHERE posi_to_play='DF'
ORDER BY player_name;

-- 58. From the following table, write a SQL query to find those players who accidentally scores against his own team. Return player name, jersey number, country name, age, position to play, and playing club.
SELECT player_name,
       jersey_no,
       country_name,
       age,
       posi_to_play,
       playing_club
FROM goal_details a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
WHERE goal_type='O'
ORDER BY player_name;

-- 59. From the following table, write a SQL query to find the results of penalty shootout matches. Return match number, play stage, country name and penalty score.
SELECT match_no,
       play_stage,
       country_name,
       penalty_score
FROM match_details a
JOIN soccer_country b ON a.team_id=b.country_id
WHERE decided_by='P'
ORDER BY match_no;
-- 60. From the following table, write a SQL query to find the goal scored by the players according to their playing position. Return country name, position to play, number of goals.
SELECT country_name,
       posi_to_play,
       count(*) AS "Number of goals"
FROM goal_details a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
GROUP BY country_name,
         posi_to_play
ORDER BY country_name;

-- 61. From the following tables, write a SQL query to find those players who came into the field at the last time of play. Return match number, country name, player name, jersey number and time in out
SELECT match_no,
       country_name,
       player_name,
       jersey_no,
       time_in_out
FROM player_in_out a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
WHERE time_in_out=
    (SELECT max(time_in_out)
     FROM player_in_out)
  AND in_out='I';