Lab 3.5 - MOAR SQL

Commands you may need

• Aggregate functions: can be used in the select query to summarise results.
  • select count(1) ... counts the number of rows in the result set
  • select sum(var) ... sums value of var for rows in the result
  • select max(var) ... returns max of var for rows in the result
• Group by: returns one row for each group. For each group, you can apply an aggregate function.
  select group, aggregate(variable) ... group by group
• Distinct: allows you to remove the duplicate rows in the result set.
  select distinct columns ...

Subqueries

A subquery is a SELECT statement nested in another statement.

select trackid,
       name,
       albumid
from track
where albumid = (
                     select albumid
                     from album
                     where title = 'Nevermind'
                 );

Here, select albumid from album where title = 'Nevermind' is the subquery that lets us grab the albumid for Nirvana’s Nevermind album.