case statement

I want to use a case statement to show an employee name and job title, lchange exempt employees to salaried and non-exempt employees to Hourly. also, using case statement I want to create a temporary field that calculates the total service time (in years) of each employee. In the same query, use a CASE statement to identify employees who are over 55 years old and have over 21 years of service time with the company. Identify them as Potential Retirees starting from todays date

Here is my database.

CREATE TABLE Job_title
(
[Job_ID] int PRIMARY KEY IDENTITY(1,1),
[Job_title] varchar(50) NOT NULL,
[High_wage] money NOT NULL,
[Low_wage] money NOT NULL,
[Exempt_Non_Exempt_Status] varchar(20) NOT NULL,
)
GO

INSERT INTO Job_title
VALUES('Accounting Clerk',16.50,11.00,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Assistant Manager',57.00,33.00,'Exempt');
 
INSERT INTO Job_title
VALUES('Bagger',10.00,6.75,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Cashier',11.75,7.25,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Computer Support Specialist',24.50,13.75,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Dir. of Fin. & Acct.',120000,50000,'Exempt');
 
INSERT INTO Job_title
VALUES('Asst.-Bakery & Pastry',11.75,7.25,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Asst.-Butchers & Seafood Specialists',11.75,7.25,'Non-Exempt');
 
INSERT INTO Job_title
VALUES('Stocker',11.75,7.25,'Non-Exempt');
 

CREATE TABLE Employee
(
[SSN] varchar(9) not null primary key,
[LastName] varchar(25) NOT NULL,
[FirstName] varchar(25) NOT NULL,
[Address] varchar(40) NOT NULL,
[City] varchar(15) NOT NULL,
[State] char(2) NOT NULL,
[Telephone_area_code] varchar(3) NOT NULL,
[Telephone_number] varchar(8) NOT NULL,
[Zipcode] varchar(5) NOT NULL,
[Job_title] varchar(50) NOT NULL,
[DateOfBirth]datetime NOT NULL,
[Hire_date] datetime NOT NULL,
[Wage] money NOT NULL,
[Job_ID] int NOT NULL REFERENCES Job_title(Job_ID)
)
GO
 
   
INSERT INTO Employee
VALUES('555555551','Edleman','Glenn','175 Bishops Lane','LaJolla','CA',
     '619','555-0199','92037','Cashier','01/26/1965','10/7/2003',10.75,'1');
     
INSERT INTO Employee
VALUES('555555552','McMullen','Eric','763 Church St.','Lemon Grove', 'CA',
     '619','555-0133','91945','Bagger','03/16/1969','11/1/2002',6.75,'2');
     
INSERT INTO Employee
VALUES('555555553','Slentz', 'Raj','123 Torrey Dr.','Clairmont','CA','619','555-0123','91711',
     'Assistant Manager','05/26/1972','6/1/2000',48000,'3');
     
INSERT INTO Employee
VALUES('555555554','Broun','Erin','2045 Parkway Apt. 2B','Encinitas','CA','760',
    '555-0100','92024','Bagger','12/05/1966','3/12/2003',6.75,'4');
   
INSERT INTO Employee
VALUES('555555555','Carpenter','Donald','927 Second St.','Encinitas','CA','619',
    '555-0154','92024','Stocker','07/14/1967','11/1/2003',7.50,'5');
   
INSERT INTO Employee
VALUES('555555556','Esquivez','David','10983 N. Coast Hwy Apt. 902','Encinitas',
    'CA','760','555-0108','92024','Asst.-Butchers & Seafood Specialists','06/26/1970',
    '7/25/2003',9.25,'6');
   
INSERT INTO Employee
VALUES('555555557','Sharp','Nancy','10793 Montecino Rd.','Ramona','CA',
    '858','555-0135','92065','Cashier','09/09/1968','7/12/2003',10.50,'7');
   
INSERT INTO Employee
VALUES('555555558','Avery','Ledonna','198 Governor Dr.','Del Mar','CA',
   '619','555-0135','92014','Asst.-Bakery & Pastry','01/31/1965','3/01/2003',11.00,'8');
   
INSERT INTO Employee
VALUES('555555559','Tyink','Thomas','87592 Pacific Heights BLVD.','Del Mar','CA',
   '858','555-0159','92014','Asst.-Bakery & Pastry','11/26/1966','4/01/2001',9.75,'9');

Select * from employee
Select * from job_title
SELECT 'FirstName', 'LastName' FROM Employee

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
WHERE j.High_wage BETWEEN $6.00 AND $18.00

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
WHERE e.Zipcode IN ('91945', '92024')

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
WHERE e.Telephone_area_code LIKE '619%' OR  e.Telephone_area_code LIKE '858%'

UPDATE Employee SET DateOfBirth='12/23/2009' WHERE SSN = 555555555

SELECT * FROM Employee WHERE DateDiff(Year, DateOfBirth, GetDate() )  > 40

SELECT LastName FROM Employee ORDER BY Job_title

SELECT * FROM Job_title j INNER JOIN
      Employee e ON j.Job_ID = E.Job_ID
ORDER BY J.High_wage

UPDATE Employee SET Job_title='Asst. Managers' WHERE Job_title='SUPERVISORS'
 
SELECT * FROM Employee ORDER BY Wage DESC;

SELECT COUNT(Exempt_Non_Exempt_Status)
FROM Job_title


UPDATE employee
SET Wage = Wage + (Wage*2.5)
WHERE Job_title ='Assistant Manager';

UPDATE employee
SET Wage = Wage + (Wage*2.5)
WHERE Job_title ='Bagger';

UPDATE employee
SET Wage = Wage + (Wage*2.5)
WHERE Job_title ='Asst.-Bakery & Pastry';

UPDATE employee
SET Wage = Wage + (Wage*2.5)
WHERE Job_title ='Asst.-Butchers & Seafood Specialists';

UPDATE employee
SET Wage = Wage + (Wage*2.5)
WHERE Job_title ='Stocker';

SELECT * FROM Employee ORDER BY Wage DESC;

UPDATE Job_title
SET Job_title = 'Senior Director',
High_wage = 75000,
Low_wage = 65000,
Exempt_Non_Exempt_Status = 'Exempt'
WHERE Job_ID = 10

DELETE FROM Job_title
WHERE Job_title = 'Senior Director'

SELECT COUNT (Exempt_Non_Exempt_Status) FROM Job_title j INNER JOIN
      Employee e ON j.Job_title = E.Job_title
ORDER BY Job_title DESC;

SELECT j.Job_title
     , j.Exempt_Non_Exempt_Status
     , COUNT(*)
 FROM Job_title j
 JOIN Employee e
  ON j.Job_title = e.Job_title
GROUP BY j.Job_title, j.Exempt_Non_Exempt_Status
ORDER BY j.Job_title DESC, j.Exempt_Non_Exempt_Status;

SELECT j.Job_title
     , j.Exempt_Non_Exempt_Status
     , COUNT(*)
 FROM Job_title j
 JOIN Employee e
  ON j.Job_title = e.Job_title
GROUP BY j.Job_title, j.Exempt_Non_Exempt_Status
ORDER BY j.Job_title DESC, j.Exempt_Non_Exempt_Status;

SELECT MIN(Wage)- MAX(Wage) FROM Employee e join Job_title j ON
j.Job_title = e.Job_title WHERE Exempt_Non_Exempt_Status = 'Non-Exempt'

SELECT MIN(Wage)FROM Employee e join Job_title j ON
j.Job_title = e.Job_title WHERE Exempt_Non_Exempt_Status = 'Exempt'

SELECT MAX(Wage)FROM Employee e join Job_title j ON
j.Job_title = e.Job_title WHERE Exempt_Non_Exempt_Status = 'Non-Exempt'