harsha547

-- Examples Queries to run on Indian Premier League Database.

-- Matches won by chasing and first_batting  

SELECT Team_Name , 
       SUM( CASE WHEN Toss_Name = 'field' THEN 1 ELSE 0 END ) AS "Chasing",
       SUM( CASE WHEN Toss_Name = 'bat' THEN 1 ELSE 0 END ) AS "First_Batting"
FROM 
     Match

harsha547

-- Number of Players participated in Indian Player League by respective Countries.
-- Atleast those who played a single match.

SELECT Country.Country_Name , 
	   Count(*) As "Players"
FROM 
	   Player
INNER JOIN 
       Country
ON 

harsha547

-- Orange Cap Holders in Indian Premier League Season wise

SELECT Season.Season_Year , Player.Player_Name
FROM
        Season
INNER JOIN
        Player
ON Season.Orange_Cap = Player.Player_Id

harsha547

-- You can pass the Season_Id Value in the Season_Id Variable ( Indian Premier League Database )

-- Either you can create the Table-Valued-Function to pass the Season_ID value to the function.


SELECT TOP 5 Player_Name , SUM(Runs_Scored) AS Total_Runs
	FROM Batsman_Scored BS
	
	INNER JOIN
	Ball_by_Ball Ball

harsha547

-- Number of Teams participated in Indian Premier League Databse

SELECT RANK() OVER (ORDER BY Team_Name ) AS Serial_No ,Team.Team_Name
FROM Team
ORDER BY Serial_No 

harsha547

-- Number of hosted matches season wise

SELECT
       RANK() OVER ( ORDER BY Season.Season_Year ) As Season_Id, 
       Season.Season_Year , 
	   COUNT(*) As 'Hosted Matches'
FROM 
    Match
INNER JOIN
	Season

harsha547

-- Top 12 players who have most man of the matches in 
-- Indian Premier League ( 2008 - 2016 )

SELECT TOP 12 Player.Player_Name , 
		COUNT(*) As 'Man_Of_The_Matches'
FROM 
	Match
INNER JOIN 
	Player
ON 

harsha547

-- Players who has taken most catches in
-- Indian Premier League ( 2008 - 2016 )

SELECT TOP 10 Player.Player_Name , 
       COUNT(*) AS 'Catches'
FROM Wicket_Taken
  INNER JOIN Out_Type
ON Wicket_Taken.Kind_Out = Out_Type.Out_Id
   INNER JOIN Player
ON Player.Player_Id = Wicket_Taken.Fielders

harsha547

-- Number of times team has won 
-- marign over of 100 Runs

SELECT Team.Team_Name , Match.Win_Margin
FROM
  Match
INNER JOIN
  Win_By
ON Match.Win_Type = Win_By.Win_Id
INNER JOIN

harsha547

-- Top ten youngest players in Indian Premier League Database

SELECT TOP 10 Player_ID , Player_Name , FORMAT(DOB,'dd,MMMM,yyyy') AS 'Date_Of_Birth'
FROM Player
ORDER BY DOB DESC