iv. Multi-Step ######################## This problem involves a series of sequen
ID: 3840528 • Letter: I
Question
iv. Multi-Step
########################
This problem involves a series of sequential steps. Make sure to test each step carefully before proceeding to the next.
10. - 23 pts -
a. (1 statement)
Create a table called Employees with the following fields: LastName, FirstName, AnnualSalary, Location. You may either create a new database or just put the new table in Lyric. Give each field an appropriate data type. Location should be a three-character field. Do not use ALTER TABLE.
b. (1 statement)
Add an EmployeeID field and make it the primary key that is automatically supplied with a unique value. Make it the first column of the table.
c.
Populate the table with three records with the following data: Mary Jones earns $200,000 per year and works at location NLJ; Bob Smith earns $75,000 per year and works at location NLJ; Bill Johnson earns $38,000 per year and works at location LCA.
d. (1 statement)
All employees who earn less than $76,000 receive 12% raises. Update the table accordingly.
e. (1 statement)
Add a new column showing the employee's telephone extension, which is an integer.
f. (1 statement)
Add a new constraint that requires that the extension must be unique.
g.
Update the table with telephone extension numbers for each employee.
h.
USE A TRANSACTION FOR THIS PART:
Bill Johnson quits because he is underpaid. Delete his record from the table.
While the command in the last step was executing, the company rehired Bill Johnson at a new salary of $75,000. Roll Back the transaction, then change his salary to $75,000.
i.
Show each location with the number of employees who work there
#############
Extra credit
#############
EC (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 |
...
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
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.