Question 1 = (Single UPDATE Statement) With a single statement change the emails

Question

Question 1 = (Single UPDATE Statement)
  
   With a single statement change the emails of all the members to the format "LastName@ArtistName.com".
   Use the replace function to get rid of spaces in the artistname
   e.g.
   SELECT REPLACE(ArtistName, " ", "") FROM Artists;
   The above lists all the artistnames with spaces removed

   After you are done, this is how the output should look like:
   mysql> select firstname, email from members;
   +-----------+-----------------------------+
   | firstname | email |
   +-----------+-----------------------------+
   | Bryce | Sanders@TheNeurotics.com |
   | Marcellin | Lambert@Sonata.com |
   | Caroline | Kale@Sonata.com |
   | Kerry | Fernandez@Sonata.com |
   | Roberto | Alvarez@Word.com |
   | Mary | Chrisman@Word.com |

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.

Update Members Set Email = CONCAT((Select Replace(LastName," ","") from Members),'@',(Select Replace(ArtistName," ","") from Artists inner join XrefArtistsMembers on Artists.ArtistID = XrefArtistsMembers.ArtistID inner join Members on Members.MemberID = XrefArtistsMembers.MemberID ),'.com');

2.

Select FirstName,LastName from SalesPeople where SalesID = (Select SalesID from Members where MemberID = (Select MemberID from XrefArtistMembers where ArtistID = (Select ArtistID from Artists where ArtistName = 'The Bullets')));

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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