INNER JOIN: Returns all rows when there is at least one match in BOTH tables

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows when there is a match in ONE of the tables

Find the unique profile names of people who have written posts about SQL and commented in these discussions.

SELECT
  distinct Posts.OwnerUserId, Posts.Tags
FROM
  Posts
  INNER JOIN
  Comments ON Comments.UserId = Posts.OwnerUserId
WHERE 
  Posts.Tags like '%sql%'

Find posts in English language community written by users under 20. Choose only those posts which have title and have FavoriteCount higher than 0.

SELECT
  TOP 200
  Users.Age, Posts.FavoriteCount, Posts.Title  
FROM
  Posts 
  INNER JOIN Users ON Posts.OwnerUserId=Users.Id
WHERE 
  Users.Age <= 20 and Posts.Title IS NOT NULL and  Posts.FavoriteCount > 0
ORDER BY
  Posts.FavoriteCount DESC

What is the median reputation for users who are from 30 to 60 years old?

Median number of views?

How to visualize that?

SELECT
  distinct Age,
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY Reputation)
  OVER(PARTITION BY Age) AS Median_Reputation
FROM 
  Users
WHERE 
  Age BETWEEN 30 AND 60
ORDER BY 
  Age

What is the percentage of posts which were called 'Favorite'?

Note: Favorite posts have Vote Type Id number 5

SELECT 
  VoteTypeId, 
  CAST(Count(Votes.VoteTypeId)* 100.0 / (Select Count(*) From Votes) 
  AS DECIMAL(5,2)) AS 'Percentage of Posts' 
FROM 
  Votes 
GROUP BY 
  VoteTypeId

• A subquery is a SQL query within a query

• Subqueries are nested queries that provide data to the enclosing query

• Subqueries can return individual values or a list of records

• Subqueries must be enclosed with parenthesis

What are the most popular tags which attract users with a badge 'Critic'?

SELECT 
  TOP 1000
  Posts.Tags, AVG(Score) as AVGScore 
FROM 
  Posts 
WHERE 
  Posts.Tags IS NOT NULL AND 
  OwnerUserId IN (SELECT Badges.UserId FROM Badges 
  INNER JOIN 
  Posts ON Badges.UserId = Posts.OwnerUserId 
  WHERE Badges.Name = 'Critic') 
GROUP BY
  Tags 
ORDER BY
AVGScore DESC

What is the percentage of users who have written posts which were viewed over 1000 times and who possess at least one gold badge?

Note: gold badges belong to the first class of badges.

SELECT 
  Badges.Class, (Count(Badges.UserId)* 100 / 
  (Select Count(*) From Badges)) 
  AS 'Percentage, %' 
FROM 
  Badges 
WHERE 
  Badges.UserId IN (SELECT Posts.OwnerUserId FROM Posts 
  INNER JOIN 
  Badges ON Posts.OwnerUserId = Badges.UserId 
  WHERE ViewCount > 1000) 
GROUP BY 
  Badges.Class