-- 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 table, write a SQL query to count the number of venues for EURO cup 2016. Return number of venues.
SELECT (*) FROM soccer_venue;

-- 2. From the following table, write a SQL query to count the number of countries participated in the EURO cup 2016.
SELECT count(DISTICT team_id) FROM player_mast;

-- 3. From the following table, write a SQL query to find the number of goals scored in EURO cup 2016 within normal play schedule.
SELECT COUNT(*) 
FROM goal_details;
--  4. From the following table, write a SQL query to find the number of matches ended with a result.
SELECT count(*) FROM match_mast WHERE result='win';

-- 5. From the following table, write a SQL query to find the number of matches ended with a draw.
SELECT COUNT(*) 

FROM match_mast 
WHERE results='DRAW';

-- 6. From the following table, write a SQL query to find the date when Football EURO cup 2016 begins.
SELECT Play_date "Biginnig Date" FROM match_mast WHERE match_no=1:

--  7. From the following table, write a SQL query to find the number of self-goals scored in EURO cup 2016.
SELECT count(*) FROM goal_details WHERE goal_type= 'O';

-- 8. From the following table, write a SQL query to count the number of matches ended with a results in-group stage.
SELECT count(*) FROM match_mast WHERE results='WIN' AND play_stage='G';

-- 9. From the following table, write a SQL query to find the number of matches got a result by penalty shootout.
SELECT count(DISTINCT match_no) FROM penalty_shootout;
-- 10. From the following table, write a SQL query to find the number of matches decided by penalties in the Round 16.
SELECT COUNT(*) 
FROM match_mast 
WHERE decided_by='P' AND play_stage='R';

-- 11. From the following table, write a SQL query to find the number of goal scored in every match within normal play schedule. Sort the result-set on match number. Return match number, number of goal scored.
SELECT match_no,count(*) FROM goal_details GROUP BY match_no ORDER BY match_no;

-- 12. From the following table, write a SQL query to find those matches where no stoppage time added in the first half of play. Return match no, date of play, and goal scored.
SELECT match_no, play_date, goal_score 
FROM  match_mast
WHERE stop1_sec=0;
-- 13. From the following table, write a SQL query to count the number of matches ending with a goalless draw in-group stage of play. Return number of matches.

SELECT COUNT(DISTINCT(match_no)) FROM match_details WHERE win_lose='D' AND goal_score='0' AND play_stage= 'G';

-- 14. From the following table, write a SQL query to count the number of matches ending with only one goal win, except those matches, which was decided by penalty shoot-out. Return number of matches.
SELECT count(goal_score) FROM match_details WHERE win_lose='W' AND decided by <> 'P' AND goal_score=1;

-- 15. From the following table, write a SQL query to count the number of players replaced in the tournament. Return number of players as "Player Replaced".

SELECT COUNT (*) as "Player Replaced" FROM player_in_out WHERE in_out='I';

-- 16. From the following table, write a SQL query to count the total number of players replaced within normal time of play. Return number of players as "Player Replaced".
SELECT COUNT(*) as "Player Replaced" FROM player_in_out WHERE in_out='I' AND play_stage='NG';

--  17. From the following table, write a SQL query to count the number of players replaced in the stoppage time. Return number of players as "Player Replaced".
SELECT count(*) AS "Player Replaced" FROM player_in_out WHERE in_out='I' AND play_stage='ST';SELECT count(*) AS "Player Replaced" FROM player_in_out WHERE in_out='I' AND play_schedule='ST';

-- 18. From the following table, write a SQL query to count the total number of players replaced in the first half of play. Return number of players as "Player Replaced".
SELECT COUNT(*) as "Player Replaced"
FROM player_in_out
WHERE in_out='I'
AND play_schedule='NT'
AND play_half=1;

-- 19. From the following table, write a SQL query to count the total number of goalless draws have there in the entire tournament. Return number of goalless draws.

SELECT COUNT(DISTINCT match_no) 
FROM match_details 
WHERE win_lose='D' 
AND goal_score=0;

-- 20. From the following table, write a SQL query to count the total number of players replaced in the extra time of play.

SELECT COUNT(*) as "Player Replaced"
FROM player_in_out
WHERE in_out='I'
AND play_schedule='ET';

-- 21. From the following table, write a SQL query to count the number of substitute happened in various stage of play for the entire Tournament. Sort the result-set in ascending order by play-half, play-schedule and number of substitute happened. Return play-half, play-schedule, number of substitute happened.
SELECT play_half,play_schedule,count(*) FROM play_in_out WHERE in_out='I' GROUP BY play_half,play_schedule,order BY play_half,play_schedule,count(*);

-- 22. From the following table, write a SQL query to count the number of shots taken in penalty shootout matches. Number of shots as "Number of Penalty Kicks".
SELECT count(*) AS "Number of Penalty Kicks" FROM penalty_shootout;
-- 23. From the following table, write a SQL query to count the number of shots scored goal in penalty shootout matches. Return number of shots scored goal as 'Goal Scored by Penalty Kicks'.
SELECT COUNT(*) AS "Goal Scored by Penalty Kicks"
FROM penalty_shootout
WHERE score_goal='Y';

-- 24. From the following table, write a SQL query to count the number of shots missed or saved in penalty shootout matches. Return number of shots missed as "Goal missed or saved by Penalty Kicks".
SELECT COUNT (*) AS "Goal missed or saved by Penalty Kicks"
FROM penalty_shootout
WHERE score_goal='N'

-- 25. From the following tables, write a SQL query to find the players with shot number they taken in penalty shootout matches. Return match_no, Team, player_name, jersey_no, score_goal, kick_no.
SELECT c.match_no,a.country_name AS "Team", 
b.player_name, b.jersey_no, c.score_goal ,c.kick_no
FROM soccer_country a, penalty_shootout c, player_mast b
WHERE c.team_id=a.country_id
AND c.player_id=b.player_id;

-- 26. From the following tables, write a SQL query to count the number of penalty shots taken by the teams. Return country name, number of shots as "Number of Shots".
SELECT a.country_name, COUNT(b.*) as "Number of Shots" 
FROM soccer_country a, penalty_shootout b
WHERE b.team_id=a.country_id
GROUP BY a.country_name;
-- 27. From the following table, write a SQL query to count the number of booking happened in each half of play within normal play schedule. Return play_half, play_schedule, number of booking happened.
SELECT play_half,play_schedule,COUNT(*) 
FROM player_booked
WHERE play_schedule='NT'
GROUP BY play_half,play_schedule;

-- 28. From the following table, write a SQL query to count the number of booking happened in stoppage time.
SELECT count (*) FROM player_booked WHERE play_schedule='ST';
-- 29. From the following table, write a SQL query to count the number of booking happened in extra time
SELECT COUNT(*)
FROM player_booked
WHERE play_schedule='ET';