Simple SQL question for you? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple SQL question for you?

Author

Topic

java_lang
Starting Member

3 Posts

Posted - 2007-11-29 : 16:16:45

I found a tutorial website for SQL which is quite good for beginner like me.
http://www.upriss.org.uk/db/22001.html
click : On-line SQL, and then
Registered Account (you can use any email address), it allows you to use the site 11 days, then you will recieve an email for extension.

It provides tutorial information which you can find out how to do the questions. However you cannot find the answers if you don't know how to do it. There have 5 tutorial exercises. You will have two Assessment Questions after finished each exercise (start from tutorial 2). It is valuable to try if you are beginner and want to challenge yourself.

I've done most questions. But I dont know how to do the last two Assessment questions. Any people can help me here?

The first question:

**"List the band name and the number of players for the band whose number of players is greater than the average number of players in each band."**
[It is musician database which have 9 tables, I put the relative tables here:]

1st Table :
SELECT * from Band
BAND_NO BAND_NAME BAND_HOME BAND_TYPE B_DATE BAND_CONTACT
1 ROP 5 classical 1-Jan-30 11
2 AASO 6 classical 10
3 The J Bs 8 jazz 12
4 BBSO 9 classical 21
5 The left Overs 2 jazz 8
6 Somebody Loves this 1 jazz 6
7 Oh well 4 classical 3
8 Swinging strings 4 classical 7
9 The Rest 9 jazz 16

2nd Table:
SELECT * from Plays_in
BAND_ID PLAYER
1 1
1 3
1 4
1 5
1 6
1 7
1 8
2 10
2 12
2 13
2 14
2 15
2 17
2 18
3 19
3 20
4 21
4 22
4 23
5 25
6 26
6 27
7 1
7 4
7 6
7 8
7 28
7 29
8 14
8 15
8 28

The keys to link two tables are :
band (band_id)
plays_in (band_no):

** it means "band_id" = "band_no" **

My code can find all band include zero player band:

However, I dont know how to do for
"number of players is greater than the average number of players in each band"
I've tried different methods, the last one is the following code, but it doesn't worked.

SELECT band.band_name, count(player)
from band, plays_in
where band.band_no = plays_in.band_id(+)
group by band.band_name
having count(player) > (
SELECT avg(count(player))
from player_in
group by band_id;

Question 2:

Give a list of musicians associated with Glasgow. Include the name of the musician and the nature of the association - one or more of "LIVES_IN", "BORN_IN", "PERFORMED_IN" AND "IN_BAND_IN".
Musicians who have more than one association with Glasgow should be listed more than once, for example:
Jeff Dawn IN_BAND_IN
Jeff Dawn LIVES_IN

The ER Diagram for Musician is in http://db.grussell.org/ermusician.html

The second question is much difficult. It requests
1) musician is associated with Glasgow
2) shows the musicians' information like "LIVES_IN", "BORN_IN", "PERFORMED_IN"
AND "IN_BAND_IN"
3) the musician names may be appear more than once.

Who want to take this challenges meanwhile teaching this junior self learner, I am looking forward to hearing from you soon.

rgds
Annie

srimathi.mani
Starting Member

3 Posts

Posted - 2007-11-30 : 02:42:43

Hey,

This query will return you the band name and the number of players for the band whose number of players is greater than the average number of players in each band

select band.BAND_NO,band.BAND_NAME,band.BAND_TYPE
from
Band
inner join
plays_in
on
Band.BAND_NO = Plays_in.BAND_ID
where
plays_in.PLAYER > (SELECT
AVG(Player)
from
Plays_in)

Regards
Srimathi

java_lang
Starting Member

3 Posts

Posted - 2007-11-30 : 15:57:23

hi Srimathi

Thank you for your code. However the query is just request
1) the band name
2) the number of players in band is greater than average number of players in other bands.

List the band name and the number of players for the band whose number of players is greater than the average number of players in each band.

I found the answer manually
BAND NAME NUMBER OF PLAYER
AASO 7
ROP 7
Ohwell 6

There have 9 bands and one of them has no player in the band. The total players in 9 bands are 31, so the average player is 3.4444444

I found 9 bands including the 0 players one, but dont know how to compare the average players

java_lang
Starting Member

3 Posts

Posted - 2007-11-30 : 16:37:09

hi Srimathi,

I know why your answer is incorrected, because you compared the wrong field.

As per my understand:

PLAYER is the Performer Number (means performer ID)
BAND_NO is the band ID which the performer Number plays in.

I cannot any table to tell me how the exactly players in each band, so we have to count how many perf_no and in band_no

My code can found the band name and number of player of each band,
but I dont know how to compare with average players in bands

quote:
SELECT band.band_name, count(player)
from band, plays_in
where band.band_no = plays_in.band_id(+)

quote:
Originally posted by java_lang

hi Srimathi

Thank you for your code. However the query is just request
1) the band name
2) the number of players in band is greater than average number of players in other bands.

List the band name and the number of players for the band whose number of players is greater than the average number of players in each band.

I found the answer manually
BAND NAME NUMBER OF PLAYER
AASO 7
ROP 7
Ohwell 6

There have 9 bands and one of them has no player in the band. The total players in 9 bands are 31, so the average player is 3.4444444

I found 9 bands including the 0 players one, but dont know how to compare the average players

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources