Home » Other » Training & Certification » does this need a union?
icon5.gif  does this need a union? [message #275252] Fri, 19 October 2007 00:17 Go to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
I have this question that my instructor gave us "for fun."

I'll try to keep this simple for ease of conveyance. The SQL script follows for the DB.

Here's the question: Choose a player who plays in a band with someone who plays in a band with another player. Who is it and what are the bands?

Now, this database is a little scatter-brained, but I didn't make it. Don't blame me.

I can do the joins to pull the names of the musicians and the bands, so what's stumping me is how to query the plays_in table for the IDs.

Please HELP!!!
create table place (
     place_no		INTEGER NOT NULL PRIMARY KEY
    ,place_town		VARCHAR2(20)
    ,place_country	VARCHAR2(20)
);


create table musician (
     m_no 		INTEGER NOT NULL PRIMARY KEY
    ,m_name		VARCHAR2(20)
    ,born 		DATE
    ,died 		DATE
    ,born_in		INTEGER
    ,living_in		INTEGER
);


create table performer (
     perf_no		integer	primary key
    ,perf_is		integer
    ,instrument		VARCHAR2(10) not null
    ,perf_type		VARCHAR2(10) default 'not known' 
);

create table composer (
     comp_no		integer primary key
    ,comp_is		integer not null references musician (m_no)
    ,comp_type		VARCHAR2(10)
);

create table band (
     band_no		INTEGER NOT NULL PRIMARY KEY
    ,band_name		VARCHAR2(20)
    ,band_home		INTEGER NOT NULL REFERENCES PLACE (place_no)
    ,band_type		VARCHAR2(10)
    ,b_date 		DATE
    ,band_contact	INTEGER NOT NULL REFERENCES musician (m_no)
);

create table plays_in (
     player	integer not null references performer (perf_no)
    ,band_id	integer not null references band (band_no)
    ,primary key ( player, band_id)
);

create table composition (
     c_no	integer primary key
    ,comp_date 	DATE
    ,c_title	VARCHAR2(40) not null
    ,c_in	integer references place (place_no)
);

create table has_composed (
	 cmpr_no	integer not null references composer (comp_no)
	,cmpn_no	integer not null references composition (c_no)
	,primary key ( cmpr_no, cmpn_no )
);

create table concert (
     concert_no		integer primary key
    ,concert_venue	VARCHAR2(20)
    ,concert_in		integer not null references place (place_no)
    ,con_date 		DATE
    ,concert_orgniser 	integer references musician (m_no)
);

create table performance (
     pfrmnc_no		integer primary key
    ,gave		integer references band (band_no)
    ,performed		integer references composition (c_no)
    ,conducted_by	integer references musician (m_no)
    ,performed_in	integer references concert (concert_no)
);

GRANT SELECT ON performance TO PUBLIC;
GRANT SELECT ON concert TO PUBLIC;
GRANT SELECT ON has_composed TO PUBLIC;
GRANT SELECT ON composition TO PUBLIC;
GRANT SELECT ON plays_in TO PUBLIC;
GRANT SELECT ON band TO PUBLIC;
GRANT SELECT ON composer TO PUBLIC;
GRANT SELECT ON performer TO PUBLIC;
GRANT SELECT ON musician TO PUBLIC;
GRANT SELECT ON place TO PUBLIC;

-- place

insert into place values (1,'Manchester','England');
insert into place values (2,'Edinburgh','Scotland');
insert into place values (3,'Salzburg','Austria');
insert into place values (4,'New York','USA');
insert into place values (5,'Birmingham','England');
insert into place values (6,'Glasgow','Scotland');
insert into place values (7,'London','England');
insert into place values (8,'Chicago','USA');
insert into place values (9,'Amsterdam','Netherlands');

-- musician

insert into musician values (1,'Fred Bloggs','02-JAN-1948',NULL,1,2);
insert into musician values (2,'John Smith','03-MAR-1950',NULL,3,4);
insert into musician values (3,'Helen Smyth','08-AUG-1948',NULL,4,5);
insert into musician values (4,'Harriet Smithson','09-MAY-1909','20-SEP-1980',5,6);
insert into musician values (5,'James First','10-JUN-1965',NULL,7,7);
insert into musician values (6,'Theo Mengel','12-AUG-1948',NULL,7,1);
insert into musician values (7,'Sue Little','21-FEB-1945',NULL,8,9);
insert into musician values (8,'Harry Forte','28-FEB-1951',NULL,1,8);
insert into musician values (9,'Phil Hot','30-JUN-1942',NULL,2,7);
insert into musician values (10,'Jeff Dawn','12-DEC-1945',NULL,3,6);
insert into musician values (11,'Rose Spring','25-MAY-1948',NULL,4,5);
insert into musician values (12,'Davis Heavan','03-OCT-1975',NULL,5,4);
insert into musician values (13,'Lovely Time','28-DEC-1948',NULL,6,3);
insert into musician values (14,'Alan Fluff','15-JAN-1935','15-MAY-1997',7,2);
insert into musician values (15,'Tony Smythe','02-APR-1932',NULL,8,1);
insert into musician values (16,'James Quick','08-AUG-1924',NULL,9,2);
insert into musician values (17,'Freda Miles','04-JUL-1920',NULL,9,3);
insert into musician values (18,'Elsie James','06-MAY-1947',NULL,8,5);
insert into musician values (19,'Andy Jones','08-OCT-1958',NULL,7,6);
insert into musician values (20,'Louise Simpson','10-JAN-1948','11-FEB-1998',6,6);
insert into musician values (21,'James Steeple','10-JAN-1947',NULL,5,6);
insert into musician values (22,'Steven Chaytors','11-MAR-1956',NULL,6,7);

-- performer

insert into performer values (1,2,'violin','classical');
insert into performer values (2,4,'viola','classical');
insert into performer values (3,6,'banjo','jazz');
insert into performer values (4,8,'violin','classical');
insert into performer values (5,12,'guitar','jazz');
insert into performer values (6,14,'violin','classical');
insert into performer values (7,16,'trumpet','jazz');
insert into performer values (8,18,'viola','classical');
insert into performer values (9,20,'bass','jazz');
insert into performer values (10,2,'flute','jazz');
insert into performer values (11,20,'cornet','jazz');
insert into performer values (12,6,'violin','jazz');
insert into performer values (13,8,'drums','jazz');
insert into performer values (14,10,'violin','classical');
insert into performer values (15,12,'cello','classical');
insert into performer values (16,14,'viola','classical');
insert into performer values (17,16,'flute','jazz');
insert into performer values (18,18,'guitar','not known');
insert into performer values (19,20,'trombone','jazz');
insert into performer values (20,3,'horn','jazz');
insert into performer values (21,5,'violin','jazz');
insert into performer values (22,7,'cello','classical');
insert into performer values (23,2,'bass','jazz');
insert into performer values (24,4,'violin','jazz');
insert into performer values (25,6,'drums','classical');
insert into performer values (26,8,'clarinet','jazz');
insert into performer values (27,10,'bass','jazz');
insert into performer values (28,12,'viola','classical');
insert into performer values (29,18,'cello','classical');

-- composer

insert into composer values (1,1,'jazz');
insert into composer values (2,3,'classical');
insert into composer values (3,5,'jazz');
insert into composer values (4,7,'classical');
insert into composer values (5,9,'jazz');
insert into composer values (6,11,'rock');
insert into composer values (7,13,'classical');
insert into composer values (8,15,'jazz');
insert into composer values (9,17,'classical');
insert into composer values (10,19,'jazz');
insert into composer values (11,10,'rock');
insert into composer values (12,8,'jazz');

-- band

insert into band values (1,'ROP',5,'classical','01-JAN-30 ',11);
insert into band values (2,'AASO',6,'classical',NULL,10);
insert into band values (3,'The J Bs',8,'jazz',NULL,12);
insert into band values (4,'BBSO',9,'classical',NULL,21);
insert into band values (5,'The left Overs',2,'jazz',NULL,8);
insert into band values (6,'Somebody Loves this',1,'jazz',NULL,6);
insert into band values (7,'Oh well',4,'classical',NULL,3);
insert into band values (8,'Swinging strings',4,'classical',NULL,7);
insert into band values (9,'The Rest',9,'jazz',NULL,16);

-- plays_in

insert into plays_in values (1,1);
insert into plays_in values (1,7);
insert into plays_in values (3,1);
insert into plays_in values (4,1);
insert into plays_in values (4,7);
insert into plays_in values (5,1);
insert into plays_in values (6,1);
insert into plays_in values (6,7);
insert into plays_in values (7,1);
insert into plays_in values (8,1);
insert into plays_in values (8,7);
insert into plays_in values (10,2);
insert into plays_in values (12,2);
insert into plays_in values (13,2);
insert into plays_in values (14,2);
insert into plays_in values (14,8);
insert into plays_in values (15,2);
insert into plays_in values (15,8);
insert into plays_in values (17,2);
insert into plays_in values (18,2);
insert into plays_in values (19,3);
insert into plays_in values (20,3);
insert into plays_in values (21,4);
insert into plays_in values (22,4);
insert into plays_in values (23,4);
insert into plays_in values (25,5);
insert into plays_in values (26,6);
insert into plays_in values (27,6);
insert into plays_in values (28,7);
insert into plays_in values (28,8);
insert into plays_in values (29,7);

-- composition

insert into composition values (1,'17-JUN-1975','Opus 1',1);
insert into composition values (2,'21-JUL-1976','Here Goes',2);
insert into composition values (3,'14-DEC-1981','Valiant Knight',3);
insert into composition values (4,'12-JAN-1982','Little Piece',4);
insert into composition values (5,'13-MAR-1985','Simple Song',5);
insert into composition values (6,'14-APR-1986','Little Swing Song',6);
insert into composition values (7,'13-MAY-1987','Fast Journey',7);
insert into composition values (8,'14-FEB-1976','Simple Love Song',8);
insert into composition values (9,'21-JAN-1982','Complex Rythms',9);
insert into composition values (10,'23-FEB-1985','Drumming Rythms',9);
insert into composition values (11,'18-MAR-1978','Fast Drumming',8);
insert into composition values (12,'13-AUG-1984','Slow Song',7);
insert into composition values (13,'14-SEP-1968','Blue Roses',6);
insert into composition values (14,'15-NOV-1983','Velvet Rain',5);
insert into composition values (15,'16-MAY-1982','Cold Wind',4);
insert into composition values (16,'18-JUN-1983','After the Wind Blows',3);
insert into composition values (17,NULL,'A Simple Piece',2);
insert into composition values (18,'12-JAN-1985','Long Rythms',1);
insert into composition values (19,'12-FEB-1988','Eastern Wind',1);
insert into composition values (20,NULL,'Slow Symphony Blowing',2);
insert into composition values (21,'12-JUL-1990','A Last Song',6);

-- has_composed

insert into has_composed values (1,1);
insert into has_composed values (1,8);
insert into has_composed values (2,11);
insert into has_composed values (3,2);
insert into has_composed values (3,13);
insert into has_composed values (3,14);
insert into has_composed values (3,18);
insert into has_composed values (4,12);
insert into has_composed values (4,20);
insert into has_composed values (5,3);
insert into has_composed values (5,13);
insert into has_composed values (5,14);
insert into has_composed values (6,15);
insert into has_composed values (6,21);
insert into has_composed values (7,4);
insert into has_composed values (7,9);
insert into has_composed values (8,16);
insert into has_composed values (9,5);
insert into has_composed values (9,10);
insert into has_composed values (10,17);
insert into has_composed values (11,6);
insert into has_composed values (12,7);
insert into has_composed values (12,19);

-- concert

insert into concert values (1,'Bridgewater Hall',1,'06-JAN-1995',21);
insert into concert values (2,'Bridgewater Hall',1,'08-MAY-1996',3);
insert into concert values (3,'Usher Hall',2,'03-JUN-1995',3);
insert into concert values (4,'Assembly Rooms',2,'20-SEP-1997',21);
insert into concert values (5,'Festspiel Haus',3,'21-FEB-1995',8);
insert into concert values (6,'Royal Albert Hall',7,'12-APR-1993',8);
insert into concert values (7,'Concertgebouw',9,'14-MAY-1993',8);
insert into concert values (8,'Metropolitan',4,'15-JUN-1997',21);

-- performance

insert into performance values (1,1,1,21,1);
insert into performance values (2,1,3,21,1);
insert into performance values (3,1,5,21,1);
insert into performance values (4,1,2,1,2);
insert into performance values (5,2,4,21,2);
insert into performance values (6,2,6,21,2);
insert into performance values (7,4,19,9,3);
insert into performance values (8,4,20,10,3);
insert into performance values (9,5,12,10,4);
insert into performance values (10,5,13,11,4);
insert into performance values (11,3,5,13,5);
insert into performance values (12,3,6,13,5);
insert into performance values (13,3,7,13,5);
insert into performance values (14,6,20,14,6);
insert into performance values (15,8,12,15,7);
insert into performance values (16,9,16,21,8);
insert into performance values (17,9,17,21,8);
insert into performance values (18,9,18,21,8);
insert into performance values (19,9,19,21,8);
insert into performance values (20,4,12,10,3);

Report message to a moderator

Re: does this need a union? [message #275255 is a reply to message #275252] Fri, 19 October 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for providing the full test case.
Just 2 misses:
- Oracle version, SQL features are added at each version
- An example of one result row and why it has to in the result set.

Regards
Michel

Report message to a moderator

Re: does this need a union? [message #275319 is a reply to message #275255] Fri, 19 October 2007 08:02 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Actually, one more.
Quote:

insert into concert values (1,'Bridgewater Hall',1,'06-JAN-1995',21);


The string in the 4th column should be wrapped in a TO_DATE function.

Report message to a moderator

Re: does this need a union? [message #275321 is a reply to message #275255] Fri, 19 October 2007 08:11 Go to previous messageGo to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
Oracle version: 10.2.0.1.0
I don't understand your second "miss"

Michel Cadot wrote on Fri, 19 October 2007 01:16

Thanks for providing the full test case.
Just 2 misses:
- Oracle version, SQL features are added at each version
- An example of one result row and why it has to in the result set.

Regards
Michel

Report message to a moderator

Re: does this need a union? [message #275325 is a reply to message #275321] Fri, 19 October 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

I don't understand your second "miss"

You posted data, with these ones what should one or two of the result line and why they are result.

For instance, one result line should be
X Y Z
Because X satisfies that condition and Y is related to ... and Z ...

Regards
Michel

Report message to a moderator

Re: does this need a union? [message #275333 is a reply to message #275325] Fri, 19 October 2007 08:51 Go to previous messageGo to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
I'm still a little lost.

I think what he wants is:

Musician A because they're in a band with Musician B, who is in a band with Musician C.

Band 1 because A and B are in the band together.

And reading the question, possibly band 2 because B and C are in it.

My question is, how do you get A and not C? Here's what I mean:
http://pc-vortex.com/images/sql.gif
I think he wants Musician A's name. Wouldn't the query return A and B? What would make the DB return one and not the other?

Ok, so I think he wants

Musician Band1 Band2
---------------------------------------------
A's name 1's name 2's name

Report message to a moderator

Re: does this need a union? [message #275334 is a reply to message #275333] Fri, 19 October 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the input? A, B or C?
If it is A, do you want all B that also plays in another band but not alone (there is a C)? I understand what you say like this.

Regards
Michel

Report message to a moderator

Re: does this need a union? [message #275335 is a reply to message #275252] Fri, 19 October 2007 08:59 Go to previous messageGo to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
I don't think so. The question asks the player (A) who plays in a band with someone(B) who plays in a band with another player(C). Who is it(singular) and what are the bands(1 & 2?)?

I think it's asking for both band names since the reference is plural.

Quote:

Here's the question: Choose a player who plays in a band with someone who plays in a band with another player. Who is it and what are the bands?

[Updated on: Fri, 19 October 2007 09:08]

Report message to a moderator

Re: does this need a union? [message #275337 is a reply to message #275252] Fri, 19 October 2007 09:43 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Well it's Friday afternoon so I had a play Very Happy 

select distinct m.m_name, b.band_name, b3.band_name, m3.m_name
from musician m, 
     plays_in p, 
     band b, 
     plays_in p2, 
     plays_in p3, 
     band b3, 
     musician m3
where m.m_no = p.player
and   b.band_no = p.band_id
-- Find out if there is another player in the same band
and   b.band_no = p2.band_id
and   p2.player != p.player
-- Get the band of the player in the second band
and   b3.band_no != b.band_no
and   b3.band_no = p3.band_id
and   p3.player = p2.player
and   m3.m_no = p3.player

[Updated on: Fri, 19 October 2007 09:44]

Report message to a moderator

Re: does this need a union? [message #275339 is a reply to message #275337] Fri, 19 October 2007 09:48 Go to previous messageGo to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
ok, that returned 50 rows. I think this is supposed to return one. Although, for reasons stated in my previous post, I think it will actually return 2.

Report message to a moderator

Re: does this need a union? [message #275340 is a reply to message #275252] Fri, 19 October 2007 09:51 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
In that case would you not have to provide the player you wanted to return as you have multiple musicians in multiple bands?

Report message to a moderator

Re: does this need a union? [message #275343 is a reply to message #275340] Fri, 19 October 2007 10:02 Go to previous messageGo to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
I think you're right. But providing the musician would, I think, defeat the purpose of devising the query. Isn't SQL's purpose to tell you data you don't already know?

Ok, I put out an email to my instructor for clarification. Obviously, there is more than one result. I ran a simple query, and I see several people who are in a band with someone who is in a band with someone else.
select
      m.m_name,
      b.band_name
from
      musician m,
      band b,
      plays_in pi,
      performer p
where
      m.m_no = p.perf_is and
      p.perf_no = pi.player and
      pi.band_id = b.band_no
order by b.band_name;

[Updated on: Fri, 19 October 2007 10:03]

Report message to a moderator

Re: does this need a union? [message #275347 is a reply to message #275252] Fri, 19 October 2007 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO
select
      m.m_name,
      b.band_name
from
      musician m,
      band b,
      plays_in pi,
      performer p
where
      m.m_no = p.perf_is and
      p.perf_no = pi.player and
      pi.band_id = b.band_no
order by b.band_name;

should be rewritten as
select
      m.m_name,
      b.band_name
from
      musician m,
      band b
where
      m.m_no in ( select p.perf_is from performer p
                  where p.perf_no in 
                  (select pi.player from plays_in pi
                    where pi.band_id = b.band_no)
                 )
order by b.band_name;

Only tables which contribute data to the SELECT clause
should exist within the FROM cluase.

Report message to a moderator

Re: does this need a union? [message #275353 is a reply to message #275347] Fri, 19 October 2007 10:34 Go to previous messageGo to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
ok, maybe you're right, but the purpose of that query was simply to see the bands and their members to see which people matched the original question.

anacedent wrote on Fri, 19 October 2007 10:15

IMO
select
      m.m_name,
      b.band_name
from
      musician m,
      band b,
      plays_in pi,
      performer p
where
      m.m_no = p.perf_is and
      p.perf_no = pi.player and
      pi.band_id = b.band_no
order by b.band_name;

should be rewritten as
select
      m.m_name,
      b.band_name
from
      musician m,
      band b
where
      m.m_no in ( select p.perf_is from performer p
                  where p.perf_no in 
                  (select pi.player from plays_in pi
                    where pi.band_id = b.band_no)
                 )
order by b.band_name;

Only tables which contribute data to the SELECT clause
should exist within the FROM cluase.

Report message to a moderator

Re: does this need a union? [message #275549 is a reply to message #275353] Sun, 21 October 2007 10:53 Go to previous messageGo to next message
krazymike
Messages: 8
Registered: October 2007
Junior Member
I appreciate everyone's help. My instructor said something about using a stored procedure. I don't think that's necessary.
I have a statement that I'm building, but I'm hitting a snag. Remember, I'm still building it, so I'm not saying it's perfect. I'm trying to use a correlated subquery. iSQLPlus is rejecting the table identifier in the subquery. here's what i have:
select
      t1.m_name "Musician A",
      t2.band_name "Band 1",
      t2.m_name "Musician B",
      t3.band_name "Band 2",
      t3.m_name "Musician C"
from 
     (select
          m.m_name,
          b.band_name,
          pi.band_id
      from
          musician m,
          performer p,
          plays_in pi,
          band b
      where
          m.m_no = p.perf_is and
          pi.player = p.perf_no and
          b.band_no = pi.band_id) t1,
     (select
          m.m_name,
          b.band_name,
          pi.band_id
      from
          musician m,
          performer p,
          plays_in pi,
          band b
      where
          m.m_no = p.perf_is and 
          pi.player = p.perf_no and
          b.band_no = pi.band_id) t2,
     (select
          m.m_name,
          b.band_name,
          pi.band_id
      from
          musician m,
          performer p,
          plays_in pi,
          band b
      where
          m.m_no = p.perf_is and
          pi.player = p.perf_no
          and b.band_no = pi.band_id
          and m.m_name = t2.m_name) t3
where
      t3.band_id = (select
                          b.band_id
                    from
                          musician m,
                          performer p,
                          plays_in pi,
                          band b
                    where
                          m.m_no = p.perf_is and
                          pi.player = p.perf_no and
                          b.band_no = pi.band_id and
                          m.m_name = t2.m_name and
                          b.band_id <> t1.band_id) and
      t1.band_id = t2.band_id and
      t2.m_name <> t1.m_name


See? told you it's messy. I can get a list of two people who are in the same band. now, I'm trying to get a list of people who's in the same band as Musician B. I can pull out 3 people in the same band, too. I'm getting this error:
ERROR at line 1:
ORA-00904: "T2"."M_NAME": invalid identifier
on the t2.m_name in the subquery. Oracle gives this example for correlated subqueries:

SELECT department_id, last_name, salary 
   FROM employees x 
   WHERE salary > (SELECT AVG(salary) 
      FROM employees 
      WHERE x.department_id = department_id) 
   ORDER BY department_id;

What am i doing wrong?

Report message to a moderator

Re: does this need a union? [message #275652 is a reply to message #275252] Mon, 22 October 2007 03:47 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
I'm sure there are plenty of people who can explain it better than me Very Happy but I think the problem is the fact that Oracle builds the subqueries first, then assigns the subquery identifier, so that subquery doesn't know what t2 is at the stage the query is executed.

Report message to a moderator

Re: does this need a union? [message #275654 is a reply to message #275252] Mon, 22 October 2007 04:03 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
The problem is NOT in the subquery in the WHERE clause.
The problem IS in the last subquery in the FROM clause.

Report message to a moderator

Re: does this need a union? [message #275785 is a reply to message #275252] Mon, 22 October 2007 12:35 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Just a hint, a nice way to do this is with an intersect union.

Report message to a moderator

Previous Topic: Block structure
Next Topic: counting weekdays since birthday
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 07:32:46 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.