Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Challenge with orchestras

Instruction

Well done! Another one?

Exercise

For each instrument, show its type, maker, the owner's name, the corresponding orchestra name, and the number of concerts (name this column as concert_number) in which the owner played from 2013 to 2016. Take into consideration only instruments produced in 2013 or earlier.

Stuck? Here's a hint!

First select the type, maker, the owner's name, and the corresponding orchestra name for each instrument produced in 2013 or earlier (no need for a subquery). Then add a correlated subquery to compute the number of concerts the owner played between 2013 and 2016.

The structure of the query is similar to the final query from the previous part (German orchestras in Ukraine):

SELECT 
  name,
  rating,
  city_origin,
  (SELECT COUNT(*)  FROM concerts WHERE orchestras.id = concerts.orchestra_id AND country = 'Ukraine') AS count
FROM orchestras
WHERE country_origin = 'Germany'