Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Practice Set in MS SQL Server

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 ConcertNumber) 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,
  CityOrigin,
  (SELECT COUNT(*) FROM Concerts WHERE Orchestras.Id = Concerts.OrchestraId AND Country = 'Ukraine') AS count
FROM Orchestras
WHERE CountryOrigin = 'Germany'