Given the following Table EmpJob for retail stores (assume there is more data):
ID: 3714656 • Letter: G
Question
Given the following Table EmpJob for retail stores (assume there is more data):
EmpId
FirstName
LastName
SSN
DateOfBirth
Company
HQ
JobId
JobName
PayGrade
Salary
111526
Matt
Davis
552124532
08/04/1978
Hot Topic
FL
01
Associate
3
$30,000
111535
Jason
Briggs
442123124
01/24/1981
Spencers
NY
02
Manager
5
$40,000
110343
Steven
Smith
552123121
04/23/1976
Spencers
CA
01
Associate
4
$32,000
223234
John
Walker
443628123
01/24/1968
Hot Topic
CA
02
Manager
6
$45,000
Write the SQL Script to update the pay grade of any manager born before 1970 to be one pay grade higher than what they currently have (10 pts).
Delete any records where Date of Birth is earlier than 01/01/1978 and the company is Hot Topic or Spencers (10 pts).
Retrieve the average salary and HQ for all managers per HQ (10 pts).
Write the script to retrieve the sum of all employee salaries by their respective companies whose HQ is in ‘NY’ or ‘CA’ (10 pts).
EmpId
FirstName
LastName
SSN
DateOfBirth
Company
HQ
JobId
JobName
PayGrade
Salary
111526
Matt
Davis
552124532
08/04/1978
Hot Topic
FL
01
Associate
3
$30,000
111535
Jason
Briggs
442123124
01/24/1981
Spencers
NY
02
Manager
5
$40,000
110343
Steven
Smith
552123121
04/23/1976
Spencers
CA
01
Associate
4
$32,000
223234
John
Walker
443628123
01/24/1968
Hot Topic
CA
02
Manager
6
$45,000
Explanation / Answer
UPDATE EmpJob
SET PayGrade = PayGrade + 1
WHERE DateOfBirth < '01/01/1970'
DELETE FROM EmpJob
WHERE DateOfBirth < '01/01/1978'
AND Company in ('Hot Topic' , 'Spencers')
SELECT AVG(Salary)
FROM EmpJob
GROUP BY HQ
SELECT SUM(Salary)
FROM EmpJob
GROUP BY Company
WHERE HQ in ('NY', 'CA')
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.