Consider the following 5 relations of a database: Songs (title, year, length, ge

Question

Consider the following 5 relations of a database:

Songs (title, year, length, genre, studioName, producerC#)

Singers (songTitle, songYear, singerName )

SongSinger (name, address, gender, birthdate)

SongExec (name, cert#, address, networth)Studio (name, address, presC#)

Find the SQL queries for:

1. Find the names of the studios, and names of presendents of these studios, of "Katy Perry" songs without using subqueries.

2. Find the names of other singers who sang any of the same songs as "Katy Perry" sings between 2010 and 2014.

3. Find the names of singers who did not sing in any songs produced by "Sony" studio in 2014 both with and without using EXCEPT operator

Explanation / Answer

Hi,

I have written the Queries as per Oracle syntax. Please convert them to desired SQL language.

Ans 1-
select name, presC# from Studio s
join Songs so
on s.presC#=so.producerC#
join Singers si
on so.title=si.songTitle
and singerName='Katy Perry'

Ans 2- select singerName from Singers where songTitle in(
select si.songTitle from Singers si
join SongSinger Sosi
on si.name=Sosi.singerName
and si.singerName='Katy Perry'
and songYear between 2010 and 2014)

Ans 3- select singerName from Singers where songTitle not in(select title
from Studio stu
join Songs s
on stu.name=s.studioName
and stu.name='Sony' and year=2014)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.