harsha547

-- Number of IPL Seasons each team has participated

SELECT Team.Team_Name , 
		COUNT ( DISTINCT Season.Season_Year) As ' Seasons_Played'
FROM 
	Season
INNER JOIN
	Match
ON Match.Season_Id = Season.Season_Id
INNER JOIN

harsha547

-- Top ten Players who have played highest number of matches

SELECT TOP 10 Player_Name , 
		COUNT(*) As 'Matches_Played'
FROM
	Player_Match
INNER JOIN
	Player
ON Player_Match.Player_Id = Player.Player_Id

harsha547

-- Team wise boundaries count 

SELECT Team.Team_Name, 
	SUM ( CASE WHEN Batsman_Scored.Runs_Scored = 4 THEN 1 ELSE 0 END ) As 'Fours' ,
	SUM ( CASE WHEN Batsman_Scored.Runs_Scored = 6 THEN 1 ELSE 0 END ) As 'Sixes'
FROM
	Batsman_Scored
INNER JOIN
	Match
ON Batsman_Scored.Match_Id = Match.Match_Id

harsha547

-- Season wise boundaries.

SELECT season.season_year, 
       Sum (CASE 
              WHEN batsman_scored.runs_scored = 4 THEN 1 
              ELSE 0 
            END) AS 'Fours', 
       Sum (CASE 
              WHEN batsman_scored.runs_scored = 6 THEN 1 
              ELSE 0 

harsha547

-- Team, Year wise bpundaries

SELECT Team.Team_Name,season.season_year, 
       Sum (CASE 
              WHEN batsman_scored.runs_scored = 4 THEN 1 
              ELSE 0 
            END) AS 'Fours', 
       Sum (CASE 
              WHEN batsman_scored.runs_scored = 6 THEN 1 
              ELSE 0 

harsha547

-- Stadium wise matches hosted

SELECT venue.venue_name, 
       Count(venue.venue_name) AS 'Matches_Hosted' 
FROM   match 
       INNER JOIN venue 
               ON match.venue_id = venue.venue_id 
GROUP  BY venue.venue_name 
ORDER  BY matches_hosted DESC 

harsha547

WITH Data
AS (
	SELECT Player.Player_Name
		,CASE Team.Team_Name
			WHEN 'Chennai Super Kings'
				THEN 'CSK'
			WHEN 'Deccan Chargers'
				THEN 'DC'
			WHEN 'Kings XI Punjab'
				THEN 'KXIP'

harsha547

--Download the super samplestore database for SQL Server

SELECT  [Product Name], 
		[Order Date],[Ship Date] , 
		DATEDIFF("d",[Order Date],[Ship Date]) As 'Shipping Days'
 FROM Orders 
 ORDER BY [Shipping Days] Desc

harsha547

--  Orders recieved by month and year

SELECT YEAR([Order Date]) As 'Year' ,
       month([Order Date]) As ' Month',
	   count( [Order ID]) As ' Orders Recieved'
	   FROM Orders
GROUP BY Year([Order Date]),MONTH( [Order Date])
Order BY Year([Order Date]) , Month([Order Date])

harsha547

-- Code Snippet of the Day [ March 25th , 2017 }

-- Use Children with the dimension to exclude All Geographies 

-- Following MDX query is used to retrieve data for the all geographies with measures of Internet sales orders and Total orders

SELECT
	NON EMPTY { [Measures].[Internet Order Count],
				[Measures].[Order Count]
				} ON COLUMNS,