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
Exercises

Instruction

Time for a break? No? Then here comes the next exercise!

Correlated subqueries can be used to find the best object in a certain category. Here we select orchestras which have the best rating among orchestras coming from the same city:

SELECT
  Name,
  CityOrigin,
  Rating
FROM Orchestras O1
WHERE Rating = (SELECT MAX(Rating) FROM Orchestras O2 WHERE O1.CityOrigin = O2.CityOrigin)

In the subquery, we select the maximal rating for all orchestras which come from the same city as the orchestra in the outer query. Note that we use aliases O1, O2 to distinguish between subquery orchestra and outer query orchestra. In the outer query we select orchestras which have Rating equal to the rating found in the subquery.

Exercise

Select the Name, Wage, and Experience of all members who earned the most within each orchestra.

Stuck? Here's a hint!

The query is similar to the example. In the subquery select the maximal wage for all members coming from the orchestra as the member in the outer query. Use the = operator to select members with the same wage.