-- 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 teams played the first match of EURO cup 2016. Return match number, country name.
SELECT match_no,country_name
FROM match_details a, soccer_country b
WHERE a.team_id=b.country_id
AND a.match_no=1;
-- 2. From the following tables, write a SQL query to find the winner of EURO cup 2016. Return country name.

SELECT country_name as Team 
FROM soccer_country 
WHERE country_id in (
SELECT team_id 
FROM match_details 
WHERE play_stage='F' and win_lose='W');

-- 3. From the following table, write a SQL query to find the most watched match in the world. Return match_no, play_stage, goal_score, audience.

SELECT match_no, play_stage, goal_score, audence 
FROM match_mast 
WHERE audence=(
SELECT max(audence) 
FROM match_mast);
-- 12. From the following tables, write a SQL query to find the team, which was defeated by Portugal in EURO cup 2016 final. Return the country name of the team.
SELECT country_name 
FROM soccer_country 
WHERE country_id=(
SELECT team_id 
FROM match_details 
WHERE play_stage='F'
AND team_id<>(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Portugal'));

-- 13. From the following table, write a SQL query to find the club, which supplied the most number of players to the 2016-EURO cup. Return club name, number of players.
SELECT playing_club, COUNT(playing_club) 
FROM player_mast  GROUP BY playing_club 
HAVING COUNT (playing_club)=( 
SELECT MAX(mycount) 
FROM ( 
SELECT playing_club, COUNT(playing_club) mycount 
FROM player_mast 
GROUP BY playing_club) pm);

-- 14. From the following tables, write a SQL query to find the player who scored the first penalty of the tournament. Return player name and Jersey number.
SELECT player_name,jersey_no FROM player_mast 
WHERE  player_id=(SELECT player_id FROM goad_details 
WHERE goal_type='P' AND match_no=(SELECT MIN (match_no) 
FROM goal_details
WHERE goal_type='P'AND play_stage='G'));

-- 15. From the following tables, write a SQL query to find the player who scored the first penalty in the tournament. Return player name, Jersey number and country name.
SELECT a.player_name,a.jersey_no,d.country_name
FROM player_mast a, goal_details b, goal_details c, soccer_country d
WHERE a.player_id=b.player_id AND a.team_id=d.country_id AND 
a.player_id=(
SELECT b.player_id 
FROM goal_details b
WHERE b.goal_type='P' AND b.match_no=(
SELECT MIN(c.match_no) 
FROM goal_details c
WHERE c.goal_type='P' AND c.play_stage='G'))
GROUP BY player_name,jersey_no,country_name;

-- 16. From the following tables, write a SQL query to find the goalkeeper for Italy in penalty shootout against Germany in Football EURO cup 2016. Return goalkeeper name.
SELECT player_name 
FROM player_mast 
WHERE player_id=(
SELECT player_gk 
FROM penalty_gk 
WHERE match_no=(
SELECT match_no 
FROM penalty_gk
WHERE team_id=(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Italy') 
OR team_id=(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Germany') 
GROUP BY match_no 
HAVING COUNT(DISTINCT team_id)=2
) 
AND team_id=(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Italy')
);

-- 17. From the following tables, write a SQL query to find the number of goals Germany scored at the tournament.
SELECT COUNT(player_id) 
FROM goal_details 
WHERE team_id=
(
SELECT country_id 
FROM soccer_country
WHERE country_name='Germany'
);

-- 18. From the following tables, write a SQL query to find the players who were the goalkeepers of England squad in 2016-EURO cup. Return player name, jersey number, club name.
SELECT player_name, jersey_no, playing_club 
FROM player_mast 
WHERE posi_to_play='GK' AND team_id=(
SELECT country_id 
FROM soccer_country
WHERE country_name='England');

-- 19. From the following tables, write a SQL query to find the players under contract to Liverpool were in the Squad of England in 2016-EURO cup. Return player name, jersey number, position to play, age.
SELECT player_name, jersey_no, posi_to_play, age
FROM player_mast 
WHERE playing_club='Liverpool'
AND team_id=(
SELECT country_id
FROM soccer_country
WHERE country_name='England'
);
-- 20. From the following tables, write a SQL query to find the players who scored the last goal in the 2nd semi-final, i.e., 50th match in EURO cup 2016. Return player name, goal time, goal half, country name.
SELECT a.player_name,b.goal_time,b.goal_half,c.country_name FROM player_mast a,goal_details b,soccer_country c WHERE a.player_id=b.player_id AND b.team_id=c.country_id AND match_no=50 AND goal_time=(SELECT MAX (goal_time) FROM goal_details WHERE match_no=50);
-- 22. From the following tables, write a SQL query to count the number of players played for 'France' in the final. Return 'Number of players shared fields'.
SELECT COUNT(*)+11 as "Number of players shared fields" 
FROM player_in_out 
WHERE match_no=(
SELECT match_no 
FROM match_mast 
WHERE play_stage='F')
AND in_out='I' 
AND team_id=(
SELECT country_id 
FROM soccer_country 
WHERE country_name='France');

-- 23. From the following tables, write a SQL query to find the Germany goalkeeper who didn't concede any goal in their group stage matches. Return goalkeeper name, jersey number.
SELECT player_name,jersey_no 
FROM player_mast 
WHERE player_id IN(
SELECT player_gk 
FROM match_details 
WHERE  play_stage='G' and team_id IN(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Germany'));

-- 24. From the following tables, write a SQL query to find the runners-up in Football EURO cup 2016. Return country name.
SELECT country_name 
FROM soccer_country 
WHERE country_id=(
SELECT team_id 
FROM match_details 
WHERE play_stage='F' AND win_lose='L'
AND team_id<>(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Portugal'));

-- 25. From the following tables, write a SQL query to find the maximum penalty shots taken by the teams. Return country name, maximum penalty shots.
SELECT a.country_name, COUNT(b.*) shots 
FROM soccer_country a, penalty_shootout b
WHERE b.team_id=a.country_id
GROUP BY a.country_name
having COUNT(b.*)=(
SELECT MAX(shots) FROM (
SELECT COUNT(*) shots 
FROM penalty_shootout
GROUP BY team_id) inner_result);

-- 26. From the following tables, write a SQL query to find the maximum number of penalty shots taken by the players. Return country name, player name, jersey number and number of penalty shots.
SELECT c.country_name,a.player_name, a.jersey_no,COUNT(b.*) shots 
FROM player_mast a, penalty_shootout b, soccer_country c
WHERE b.player_id=a.player_id
AND b.team_id=c.country_id
GROUP BY c.country_name,a.player_name,a.jersey_no
HAVING COUNT(b.*)=(
SELECT MAX(shots) FROM (
SELECT COUNT(*) shots 
FROM penalty_shootout
GROUP BY player_id) inner_result);

-- 27. From the following table, write a SQL query to find those match where the highest number of penalty shots taken.
SELECT match_no,
       COUNT(*) shots
FROM penalty_shootout
GROUP BY match_no
HAVING COUNT(*)=
  (SELECT MAX(shots)
   FROM
     (SELECT COUNT(*) shots
      FROM penalty_shootout
      GROUP BY match_no) inner_result);
-- From the following table, write a SQL query to find the match number where highest number of penalty shots had been taken. Return match number, country name.
SELECT b.match_no,
       a.country_name
FROM penalty_shootout b,
     soccer_country a
WHERE b.team_id=a.country_id
  AND match_no=
    (SELECT match_no
     FROM penalty_shootout
     GROUP BY match_no
     HAVING COUNT(*)=
       (SELECT MAX(shots)
        FROM
          (SELECT COUNT(*) shots
           FROM penalty_shootout
           GROUP BY match_no) inner_result))
GROUP BY b.match_no,
         a.country_name;
-- From the following tables, write a SQL query to find the player of 'Portugal' who taken the seventh kick against 'Poland'. Return match number, player name and kick number.
SELECT a.match_no,
       b.player_name,
       a.kick_no
FROM penalty_shootout a,
     player_mast b
WHERE a.player_id=b.player_id
  AND kick_no=7
  AND match_no=
    (SELECT match_no
     FROM penalty_shootout
     WHERE team_id =
         (SELECT country_id
          FROM soccer_country
          WHERE country_name='Portugal' )
     GROUP BY match_no)
GROUP BY match_no,
         player_name,
         kick_id;
-- From the following tables, write a SQL query to find the stage of match where penalty kick number 23 had been taken. Return match number, play_stage.From the following tables, write a SQL query to find the stage of match where penalty kick number 23 had been taken. Return match number, play_stage.
SELECT match_no,
       play_stage
FROM match_mast
WHERE match_no=
    (SELECT match_no
     FROM penalty_shootout
     WHERE kick_id=23);
-- From the following tables, write a SQL query to find the venues where penalty shoot-out matches played. Return venue name.
SELECT venue_name
FROM soccer_venue
WHERE venue_id IN
    (SELECT venue_id
     FROM match_mast
     WHERE match_no IN
         (SELECT DISTINCT match_no
          FROM penalty_shootout));
-- From the following tables, write a SQL query to find the date when penalty shootout matches played. Return playing date.
SELECT play_date FROM match_mast WHERE match_no IN (SELECT DISTINCT match_no FROM penalty_shootout);
-- From the following table, write a SQL query to find the quickest goal at the EURO cup 2016, after 5 minutes. Return 'Quickest goal after 5 minutes'.
SELECT min (goal_time) AS "Most Quickestgoal after 5 minutes"               
FROM (SELECT match_no,goal_time FROM goal_details WHERE goal_time>5 GROUP BY match_no,goal_time order by goal_time)hh;