Unable to determine query and subqueries

Question

I am having trouble resolving this issue. I am doing some audit exercises for the university and would like to understand this before the exam in 2 days.

Ive tried something (which is Ill post at the end). Please, kindly, this is my first database object, so my attempts may seem very silly to you.

The question is: Which artist / has the most hits at the moment? Show the first and last artist name / s and their address. The ORDER BY clause cannot be used. Write one SQL expression. Use subqueries.

Relevant tables in the database:

Shows (ShowName, ArtistId, ShowStartDate, ShowEndDate) Artists (ArtistId, FirstName, FamilyName, Address, PhoneNum) 

We assume that ArtistId, ShowStartDate, FirstName, FamilyName, and Address cannot be empty.

Now, I think that I have to count the number of impressions that every artist has at the moment. Then get ArtistId for the artist / s that has / has the most. Use ArtistId to get information about the artist (names and address).

I got to this (which is very wrong):

 SELECT FirstName, FamilyName, Address FROM Artists WHERE ArtistId = (SELECT ArtistId FROM Shows WHERE ArtistId = (SELECT MAX(Counted) FROM (SELECT ArtistId, COUNT(ArtistId) AS Counted FROM Shows WHERE ShowEndDate IS null GROUP BY ArtistId) GROUP BY ArtistId)); 

Well I do not know

 SELECT ArtistId, COUNT(ArtistId) FROM Shows WHERE ShowEndDate IS null GROUP BY ArtistId 

gives me a table counting how many times the ArtistId list is displayed. And this is good. But from this table of results I need to get ArtistId / s of those with the highest score.

And here I am lost.

Can anyone shed some light?

(As for which DBMS I use: we must use one created and provided by the university. Its the simplest SQL. Easier than Access 2010).

thanks

(If you give an answer [thanks, thank you], could you also briefly explain the reasons for this?)

Answer 1

You need to find the maximum number of impressions of the artist, and then find out which artists have this score by rerunning the count request, but using the having corresponding to the maximum just found.

 select FirstName, FamilyName, Address from Artists where ArtistId in -- use an in() to select the artists (select ArtistId from -- just select the artist id from the results (select ArtistId, count(*) c -- re-run the count query, but see having clause from Shows where current_date between ShowStartDate and ShowEndDate group by ArtistId having count(*) = -- use a having clause to only select those with the max count (select max(c) from -- this is simply the maximum count (select ArtistId, count(*) c -- find all counts by artist from Shows where current_date between ShowStartDate and ShowEndDate group by ArtistId ) counts ) ) ) 

Some syntax notes:

• count(*) c means that the column (with the value of count(*) ) is assigned the alias c , so it can be attributed to an external query. You cannot refer to it as count(*) , because it will be interpreted as an attempt to aggregate.
• max(c) gets the maximum of a column named (or aliased) c (AFAIK you cannot encode max(count(*)) - maybe you could try it - I just typed this without the console to check it)
• counts - a table alias, which is a syntax requirement when selecting from a result set

You did not specify which database you are using, so you may need to replace current_date equivalent of your database.

Some dbs allow you to reuse a query in a query (using the with clause), which avoids retyping the count subquery.

This query uses only subsamples, but you can also do this using a join.

Answer 2

Try the following:

 SELECT FirstName, FamilyName, Address FROM Artists WHERE ArtistId IN ( SELECT ArtistId FROM ( SELECT ArtistId, COUNT(ArtistId) AS Counted FROM Shows WHERE ShowEndDate IS null GROUP BY ArtistId) S1 WHERE Counted = ( SELECT MAX(Counted) FROM ( SELECT ArtistId, COUNT(ArtistId) AS Counted FROM Shows WHERE ShowEndDate IS null GROUP BY ArtistId) S2 GROUP BY ArtistId) ); 

It is simple and should work in your case.