• Relational database consists of several related tables

• The set of multilevel related databases is more practical for data storage

Aggregate functions – apply a certain operation to all rows of a group

Exercise 0

Let's look which topics are more popular in StackOverflow and SuperUser

For this, we can count average number of comments and answers for tags in these communities.

What if we compare activity only for sql tags?

SELECT
  Tags, AVG(AnswerCount) AS AvgAns, AVG(CommentCount) AS AvgCom
FROM
  Posts
WHERE 
  Tags like '%sql%'
GROUP BY
  Tags
ORDER BY
  AvgAns DESC

Exercise 2

Find titles of questions viewed more than 1000 times from site “SciFi and Fantasy”. Order questions from the most viewed to the least viewed.

SELECT
  Title, ViewCount
FROM 
  Posts
WHERE 
  ViewCount > 1000
ORDER BY 
  ViewCount DESC

Exercise 3

You want to reveal what is the age of users from StackOverflow who have the lowest average reputation in the age group from 13 to 80 years old.

SELECT
  Age, AVG(Reputation) AS AvgRep
FROM
  Users
WHERE
  Age >= 30 and Age <= 45
GROUP BY
  Age
ORDER BY
  AvgRep DESC