Write a SQL query to retrieve the information requested. 1. Find the last names

Question

Write a SQL query to retrieve the information requested.

1. Find the last names of all members who live in California with the names of all studios that are located in California in a single list. (1 column, multiple rows)

2. Find the memberID and birthday of every member in Vermont (VT) who is older than all the members from NY.

3. Find the trackTitles of all tracks recorded by artists which were entered into the database (that is, have entrydates) after August 1, 2003. Use one or more joins;
do not use any subqueries.

4. Find the same data selected in the last question. This time, use one or more subqueries and do *not* use any joins.

5. Show the name of each artist which has recorded at least one title at a studio where The Bullets have recorded (Including The Bullets). Use subqueries, not joins.

Tracks TitleID TrackNum TrackTitle LengthSeconds MP3 RealAud Titles Title ID ArtistID Title Studi oID UPC Gee nre Genre B Genre Artists Y ArtistID ArtistName City Region Country Web Address Entry Date Lead Source Xref Artists Members MemberID V Y Art tID RespParty Studios StudioID StudioN ame Address City Region PostalCode Country WebAdd Contact: EMa Phone Sales ID Members B MemberID ame LastName Address City Region PostalCode Country HomePhone Work Phone EMa Gender Birthday SalesID Sales People Sales ID FirstNam La Name nitials Base Supervisor

Explanation / Answer

1) select Lastname from members where SalesId in (select SalesId from SalesPeople where SalesId in (select SalesId from Studios where City='california') )
and City='california'

2) select MemberId,Birthday
from Members where region = 'Vermont' and year(Birthday) >
(select max(Birthday) from Members where region = 'NY')

3) select t.TrackTitle,a.ArtistName,ti.Title   from Tracks t,Artists a,Titles ti where a.ArtistId=ti.ArtistId and ti.TitleId=t.TitleId and
Date('a.EntryDate') > '2003-08-01'

4) select TrackTitle from Tracks where TitleId in (select TitleId from Titles where ArtistId Like (select ArtistId from Artists where Date('a.EntryDate') > '2003-08-01'))

5) select ArtistName from Artists where ArtistId in ( select ArtistId from Titles where StudioId in (
select StudioId from Titles where ArtistId in ( select ArtistId from Artists where ArtistName='The Bullets'
) ) )

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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