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

a)

create table Employees(
LastName varchar(20),
FirstName varchar(20),
AnnualSalary numeric(15,2),
Location char(3)
)

b)

alter table Employees Add EmployeeID INT AUTO_INCREMENT PRIMARY KEY First;

c)
INSERT INTO Employees ( LastName, FirstName,AnnualSalary,Location )
VALUES ( 'Mary' , 'Jones',200000,'NLJ' );

INSERT INTO Employees ( LastName, FirstName,AnnualSalary,Location )
VALUES ( 'Bob' , 'Smith',75000,'NLJ' );

INSERT INTO Employees ( LastName, FirstName,AnnualSalary,Location )
VALUES ( 'Bill' , 'Johnson',38000,'LCA' );

d)
update Employees set AnnualSalary= (AnnualSalary*12)/100 where AnnualSalary > 76,000

e)
Alter table Employees add telephone_extension int

f)
alter table Employees add constraint Telephone_unique unique(telephone_extension)

g)
update Employees set telephone_extension=#####

h)
start transaction
delete from Employees where LastName='Bill' and FirstName='Johnson'
rollback
INSERT INTO Employees ( LastName, FirstName,AnnualSalary,Location )
VALUES ( 'Bill' , 'Johnson',75000,'LCA' );
commit

i)
select Location,count(*) as NoofEmployees from Employees group by Location

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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