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

SELECT sng.studioName, st.presedentName FROM Songs AS sg JOIN Singers AS sgs on sg.title = sgs.songTitle JOIN Studio st on sg.studioName = st.name WHERE sgs.singerName ='Katy Perry';

2. SELECT singerName FROM Singers WHERE singerName <> 'Katy Perry' AND songTitle IN (SELECT songTitle FROM Singers WHERE singerName = 'Katy Perry' AND songYear BETWEEN 2010 AND 2014);

Without except

3. SELECT sgs.singerName FROM Singers AS sgs JOIN Songs AS sg on sgs.songTitle = sg.title WHERE sg.studioName <> 'Sony' AND sg.year <> 2014;

With except

SELECT sgs.singerName FROM Singers AS sgs JOIN Songs AS sg on sgs.songTitle = sg.title WHERE sg.studioName <> 'Sony' AND sg.year <>2014

EXCEPT

SELECT sgs.singerName FROM Singers AS sgs JOIN Songs AS sg on sgs.songTitle = sg.title WHERE sg.studioName = 'Sony' and sg.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.