July 4, 2012 at 7:00 am
1)Please write an SQL query for the following.
Consider the following tables.
Genre
Genre
gid genre_name
521 Classical
523 Pop
627 Fusion
Music
gid mid Era
521 m1 60s
523 m2 70s
627 m3 80s
Artist
sid mid art_id
3112 m1 art1
5431 m1 art2
2341 m1 art3
6598 m2 art3
7854 m3 art1
9874 m3 art3
Artist_name
art_id artist_name
art1 Yanni
art2 Pavarotti
art3 A R Rahman
From the above, write a single SQL query to get the genre_name, artist_name for all the cases where the genre has been composed by BOTH Yanni & A R Rahman and the genre has NOT been composed by BOTH Yanni & Pavarotti. Also write the result based on the data from the above tables.
July 4, 2012 at 7:07 am
Looks like you need to do some research on JOINS for starters.
July 4, 2012 at 7:07 am
July 4, 2012 at 7:15 am
here is dml for the required tables.
create table #Genre(gid int, Genre_name varchar(100))
insert into #Genre values(521,'classical')
insert into #Genre values(523,'pop')
insert into #Genre values ( 627,'fusion')
Create table #music(gid int,mid varchar(10),Era varchar(20))
insert into #music values(521,'m1','60s')
insert into #music values(523,'m2','70s')
insert into #music values(627,'m3','80s')
create table #Artist(sid int,mid varchar(20),art_id varchar(20))
insert into #Artist values(3112,'m1','art1')
insert into #Artist values(5431,'m1','art2')
insert into #Artist values(2341,'m1','art3')
insert into #Artist values(6598,'m2','art3')
insert into #Artist values(7854,'m3','art1')
insert into #Artist values (9874,'m3','art3')
create table #artist_name(art_id varchar(20),artist_name varchar(20))
insert into #artist_name values('art1','yanni')
insert into #artist_name values('art2','pavarotti')
insert into #artist_name values('art3','ar rahman')
July 4, 2012 at 7:31 am
raghavatgk (7/4/2012)
here is dml for the required tables.create table #Genre(gid int, Genre_name varchar(100))
insert into #Genre values(521,'classical')
insert into #Genre values(523,'pop')
insert into #Genre values ( 627,'fusion')
Create table #music(gid int,mid varchar(10),Era varchar(20))
insert into #music values(521,'m1','60s')
insert into #music values(523,'m2','70s')
insert into #music values(627,'m3','80s')
create table #Artist(sid int,mid varchar(20),art_id varchar(20))
insert into #Artist values(3112,'m1','art1')
insert into #Artist values(5431,'m1','art2')
insert into #Artist values(2341,'m1','art3')
insert into #Artist values(6598,'m2','art3')
insert into #Artist values(7854,'m3','art1')
insert into #Artist values (9874,'m3','art3')
create table #artist_name(art_id varchar(20),artist_name varchar(20))
insert into #artist_name values('art1','yanni')
insert into #artist_name values('art2','pavarotti')
insert into #artist_name values('art3','ar rahman')
Hey raghavatgk,
Why don't you try yourself and post your query if not getting the expected result and believe me if you are in learning phase,will give you a lot of confidence.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 4, 2012 at 7:50 am
i implemented the relation between tables like this.
select Genre_name,artist_name from #Genre G inner join #music M on m.gid=G.gid
inner join #Artist A on a.mid=M.mid
inner join #artist_name AM on AM.art_id=A.art_id
but unable to appliy condition.
July 4, 2012 at 8:21 am
You can use the INTERSECT and EXCEPT operators
SELECTAN.artist_name, G.Genre_name
FROM#Artist AS A
INNER JOIN#artist_name AS AN ON A.art_id = AN.art_id
INNER JOIN(
SELECTA.mid
FROM#Artist AS A
WHEREA.art_id IN ( 'art1' )
INTERSECT
SELECTA.mid
FROM#Artist AS A
WHEREA.art_id IN ( 'art3' )
EXCEPT
SELECTA.mid
FROM#Artist AS A
WHEREA.art_id IN ( 'art2' )
) M ON A.mid = M.mid
INNER JOIN#music AS M1 ON M.mid = M1.mid
INNER JOIN#Genre AS G ON M1.gid = G.gid
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 4, 2012 at 8:43 am
Thanks to all.
good to know that, it can be done using intersect and except.
i am done using Exists operator.
Expecting some more Solutions.:-)
July 4, 2012 at 11:20 pm
raghavatgk (7/4/2012)
Thanks to all.good to know that, it can be done using intersect and except.
i am done using Exists operator.
Expecting some more Solutions.:-)
I guess Kingston got the perfect solution for you as your requirement needs very obvious choice of INTERSECT & EXCEPT.
However you can post your query as well.:-)
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 5, 2012 at 6:32 am
Here is the logic which i applyed.
correct me if i am wrong...
select Genre_name, artist_name from #Genre G inner join #music M on m.gid=G.gid
inner join #Artist A on a.mid=M.mid
inner join #artist_name AM on AM.art_id=A.art_id
--where artist_name='yanni'
and exists(
select Genre_name, artist_name from #Genre s inner join #music M on m.gid=G.gid
inner join #Artist A on a.mid=M.mid
inner join #artist_name AM on AM.art_id=A.art_id where s.gid=G.gid and artist_name='ar rahman'
)
and not exists(
select Genre_name, artist_name from #Genre s inner join #music M on m.gid=G.gid
inner join #Artist A on a.mid=M.mid
inner join #artist_name AM on AM.art_id=A.art_id where s.gid=G.gid and artist_name='pavarotti'
)
and exists(
select Genre_name, artist_name from #Genre s inner join #music M on m.gid=G.gid
inner join #Artist A on a.mid=M.mid
inner join #artist_name AM on AM.art_id=A.art_id where s.gid=G.gid and artist_name='yanni'
)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply