think in terms of sets, not iterators; the sql statements define the properties of the desired output set (aka table/relation)
all venueNames such that for every bandCountry there's a band from that country that plays in venue of that name
the result of this (if i understood your intentions correctly!) would be the set of venues that have at least one band that plays in that venue. The iteration over bandCountry is unnecessary, as the PLAYS relation already has the information that you seek, you just have to eliminate the duplicates
so in SQL this would be:
select
distinct venueName
from PLAYS
EDIT: ok, so the actual set desired is a bit more complicated. The question being asked of the database is: what venues have hosted bands from all countries?
So, we define the membership criteria for an element of the desired set as the goal, then work backwards to populate the set. A venue is a member of the output set if it has a PLAYS row for at least one band from every country. How do we get this information?
One way is to count the distinct countries for each venue and compare it to the count of all countries. But we don't have a COUNTRY relation. If we think about the model given for a moment, we see that the set of all countries is not the right criteria; it is the set of all countries that have at least one band. So we don't need a country table (though for a normalized model we should have one), and we don't care about the country of the venue, we can just count the countries that have bands, e.g. (in MS-SQL)
declare @BandCountryCount int
select
@BandCountryCount = COUNT(distinct bandCountry)
from BAND
We can count the band countries for each venue
select
P.venueName, COUNT(distinct B.bandCountry) as VenueBandCountryCount
from PLAYS P
inner join BAND B on B.bandName = P.bandName
and we can piece the two together using a subquery
select
venueName
from (
select
P.venueName, COUNT(distinct B.bandCountry) as VenueBandCountryCount
from PLAYS P
inner join BAND B on B.bandName = P.bandName
) X
where X.VenueBandCountryCount = @BandCountryCount
Now, that's not the prettiest query possible (GROUP BY and HAVING might be considered a more 'elegant' solution than temp variables and a subquery) but it's fairly obvious what we're after so we'll leave it at that for the OP's purpose.
The OP's purpose was to learn how to shift the mindset from imperative to declarative. To that end, look at what the imperative solution described was doing:
for each venueName iterate over all the bandCountries and for each bandCountry get the list of bands that come from it. If none of them play in venueName, go to next venueName. Else, at the end of the bandCountries iteration add venueName to the set of good venueNames
What is the determining criteria in the above? I think it is:
...If none of them [the set of bands from a particular country] play in venueName...
This is a disqualifying criteria. The imperative thought process is starting with a full bucket and throwing out things that don't fit the criteria. We're filtering out data.
That's fine for simple stuff, but it helps to think in terms of constructing the desired result set; what is the corresponding qualifying criteria that would allow one to fill up the bucket instead?
- disqualifier: if there is no band from a bandCountry that plays at a venue, the venue is disqualified
- (partial) qualifier: if at least one band from a bandCountry plays at a venue, then the venue might be ok; keep checking the rest of the bandCountries
- (full) qualifier: if at least one band from each bandCountry plays at a venue, then the venue is qualified
The final qualifier can be simplified using counts: a bandCountry is 'satisfied' if at least one band from there plays at a venue; the number of 'satisfied' band countries for a venue must equal the number of band countries for the venue to be qualified.
Now we can reason across relations by navigation:
- start with the VENUE relation [we don't need it for the answer, but it is the conceptual starting point for the relational navigation]
- join to PLAYS on venueName
- join to BAND on bandName to get the bandCountry
- we don't care about the band name; select only the venueName and bandCountry
- we don't care about redundant bandCountries; eliminate duplicates using DISTRICT or GROUP BY
- we only care about the count of distinct bandCountries, not the names
- we only want venues where the count of distinct bandCountries is the same as the total number of bandCountries
which leads up back to the solution above (or a reasonable facsimile thereof)
SUMMARY
- set theory
- relational navigation paths
- inclusive vs exclusive criteria (qualifying vs disqualifying)
