January 29, 2021 at 6:32 pm
Hello,
I need help in answering this query. I just cannot get it right. I have attached the tables for reference.
A record company requests the names of all recording artists, as well as the number of rock songs each artist sings. Note that some artists may not sing any rock songs but should still be included in the list. The record company would like this list to be ordered so that the artist with the least number of rock songs appears first in the list, and the artist with the most appears last in the last. Write a single query that obtains these results.
My query does not give the desired results.
select count(s.song_id) as 'Number of Rock Songs', r.artist_name
from song s
join album a on s.album_id=a.album_id
join Recording_artist r on a.recording_artist_id=r.recording_artist_id
group by r.artist_name
January 29, 2021 at 7:42 pm
Please share sample codes in text format. You can achieve the result by below query
with Artist as (
select recording_artist_id,artist_name,
(select count(*) from album,song,musical_genre mg
where album.album_id=song.album_id and mg.musical_genre_id=album.musicalgenre_id and
musical_genre='Rock' and album.recording_artist_id=recording_artist.recording_artist_id) NoOfRockSongs
from recording_artist
)
select * from Artist order by NoOfRockSongs
January 29, 2021 at 8:46 pm
Hopefully you understand why the query works otherwise you won't do well on the test.
January 30, 2021 at 5:34 pm
Hello,
I need help in answering this query. I just cannot get it right. I have attached the tables for reference.
A record company requests the names of all recording artists, as well as the number of rock songs each artist sings. Note that some artists may not sing any rock songs but should still be included in the list. The record company would like this list to be ordered so that the artist with the least number of rock songs appears first in the list, and the artist with the most appears last in the last. Write a single query that obtains these results.
My query does not give the desired results.
select count(s.song_id) as 'Number of Rock Songs', r.artist_name from song s join album a on s.album_id=a.album_id join Recording_artist r on a.recording_artist_id=r.recording_artist_id group by r.artist_name
You need a join to the genre table and a WHERE clause that looks for "Rock" in that table. That is kind of an important requirement that you missed in your code.
[EDIT] Bad observation on my part. The goal is to return all artists with the number of rock songs even if that number is zero.
--Jeff Moden
January 30, 2021 at 8:38 pm
select count(m.musical_genre_id) as 'Number of Rock Songs', r.artist_name
from song s
inner join album a
on s.album_id = a.album_id
inner join Recording_artist r
on a.recording_artist_id = r.recording_artist_id
left join musical_genre m
on m.musical_genre_id = a.musical_genre_id
and m.musical_genre = 'Rock'
group by r.artist_name
This works because nulls are not included in COUNT()
January 31, 2021 at 1:42 am
Hi Jonathan,
This does not give the desired results.
I'm thinking that you need to take a look again...
April Wine had 2 rock albums according to the data you provided as a picture. Album 1 had 4 songs and album 4 had 2 songs for a total of 6 songs. So far, Jonathan's first answer is correct.
Elvis Costello had 1 rock album, album number 3, and it had 3 songs.
That makes Jonathon's query correct.
[EDIT] Ah... I see why you say it was the wrong answer... the requirement was to return ALL artists whether or not they had any rock songs or not and put them in ascending order by the number of rock songs they had.
--Jeff Moden
January 31, 2021 at 2:11 am
Hi Jonathan,
This does not give the desired results.
The query you had was the one I originally put in before I edited it a few minutes later. So try this, it also has the ORDER BY that Jeff spotted I'd missed.
select count(m.musical_genre_id) as 'Number of Rock Songs', r.artist_name
from song s
inner join album a
on s.album_id = a.album_id
inner join Recording_artist r
on a.recording_artist_id = r.recording_artist_id
left join musical_genre m
on m.musical_genre_id = a.musical_genre_id
and m.musical_genre = 'Rock'
group by r.artist_name
order by count(m.musical_genre_id), r.artist_name
January 31, 2021 at 2:46 am
Hi Jonathan,
This worked. Thanks a ton.
January 31, 2021 at 3:23 pm
Hi Jonathan,
This worked. Thanks a ton.
The question now is... do you know both how and why it works and you're able to fully explain each section of the code as well as a possible alternative to the quoted alias in the select and the formula in the ORDER BY? Since you're studying, those would be good things to look into because they're going to come up in real life a whole lot.
--Jeff Moden
January 31, 2021 at 6:59 pm
In the future, would you mind following forum netiquette? That means you're supposed to post DDL, and not pretty colored pictures. What little we can figure out from your DML looks completely wrong. For example, a song's genre is not an entity in itself; it's either a property of a song, or perhaps an album. We have no idea of any industry standards you might be using for your data. I'm going to make some wild guesses and violate the rule about not opening attachments from people you don't know to guess what you might have meant, if you have a valid design. Did you know that you printed 10 pages of jpegs? 10 pages? Really?
https://en.wikipedia.org/wiki/Schwann_Catalog. This will give you an industry standard for your work. It looks like you've just assigned a sequential number to the Rows in a non-table. The reason I say it's a non-table is that it's not even first normal form. This is how non-relational databases use pointer chains.
You need to have ways of modeling relationships among the parts of the data model:
1) Artist sing songs
2) albums contain one or more songs a song can be sung by more than one artist (how many people have covered a Frank Sinatra song?)
3) A song can be sung by several artists at the same time (think about a trio)
4) EITHER album have a genre attribute
OR songs have a genre attribute (this attribute cannot belong to an album)
OR both albums and songs have a genre
In RDBMS we do this with other tables. This is called the entity-relationship model is the basis of ER diagrams
were you just trying to get somebody to do your homework for you quickly, or is this an actual problem? Do you want to do it right, or just get a query to run?
Please post DDL and follow ANSI/ISO standards when asking for help.
January 31, 2021 at 7:26 pm
Dear Joe... the Op had nothing to do with the creation of these tables. It's a homework problem. Please track down the instructor and vet on them. 😉
--Jeff Moden
January 31, 2021 at 7:29 pm
I apologize for not following the forum netiquette. I will post DDL in the future asks.
As far as DML or design is concerned, this is the university assignment and not really the real-world design.
I didn't really quite get what you meant by printing 10 pages?
January 31, 2021 at 8:48 pm
The "forum etiquette" which Joe refers to can be found at the first link in my signature line below. It will usually help you get a good, coded answer much more quickly. Joe is correct that images don't help so much in that area even if he can't successfully count them. 😀
As for the rest of his post, it's a standard bash/rant of his and he seems hell bent on inflicting it on everyone that he hasn't bashed before. He always points at the OP about design even though it's clear that the OP didn't design the tables/data and also doesn't seem to understand the differences between a simple example to explain a problem and real tables.
Sometimes there is a shiny penny to be found in his posts that make suffering through his interminable rants worthwhile.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply