SQL Queries

select firstname,lastname,jobtitle,salary,

case 

when jobtitle='salesman' then salary+(salary*.10)

when jobtitle='accountant' then salary+(salary*.05)

when jobtitle='hr' then salary+(salary*.000001)

else salary+(salary*.03)

end as saary_after_raise

from employeedemographics as e1

join employeesalary as e2

using(employeeid);

select *,

case

when jobtitle='salesman' then salary+1000

when jobtitle='hr' then salary+2000

else salary+3000

end as salary_after_bonus

from employeedemographics as e1

join employeesalary as e2

using(employeeid);

use empdemographics;

select * from employeesalary;

select jobtitle,count(jobtitle)

from employeedemographics as e1

join employeesalary as e2

using(employeeid)

group by jobtitle;

select jobtitle,count(jobtitle) as count_jobtitle

from employeedemographics as e1

join employeesalary as e2

using(employeeid)

group by jobtitle

having count_jobtitle>1;

select jobtitle,avg(salary) as avg_salary

from employeedemographics as e1

join employeesalary as e2

using(employeeid)

group by jobtitle;

select jobtitle,avg(salary) as avg_salary

from employeedemographics as e1

join employeesalary as e2

using(employeeid)

group by jobtitle

order by avg_salary desc;