SQL employee database querying Questions-CodePudding

SQL employee database querying Questions

Consider the employee database of an organization XYZ private limited

                 ______________Questions ________________

Answers that I have been able to do

Question 1

write a query where top 5 country where highest paid employees are based

select top 5 (c.name, sum(s.amount) as high_sal)
from countries as c
join salary as s on c.id=s.id
group by c.name
order by high_sal desc

Question 2

Write a query to extract count of users in age group 18-25, 26-40, 41-60 and 60 respectively. Also, find highest paid in each group.

Select  DATEDIFF(YY,E.dob,GETDATE()) AS age,max(salary),
Case 
 when age >=18 and age<=25  then ‘18-25’ 
when age >=26 and age<=40 then ‘26-40’
when age >=41 and age<=60 then ‘41-60’
when age >=61 then ‘60 plus’
END AS category,
count(category)
From Employees  AS E 
JOIN SALARY AS S
GROUP BY category
Order by age

another possible answer

SELECT COUNT(*) as '18-25', MAX(salary) as 'Highest Paid in 18-25'
FROM users
WHERE age BETWEEN 18 AND 25
UNION
SELECT COUNT(*) as '26-40', MAX(salary) as 'Highest Paid in 26-40'
FROM users
WHERE age BETWEEN 26 AND 40
UNION
SELECT COUNT(*) as '41-60', MAX(salary) as 'Highest Paid in 41-60'
FROM users
WHERE age BETWEEN 41 AND 60
UNION
SELECT COUNT(*) as '60 ', MAX(salary) as 'Highest Paid in 60 '
FROM users
WHERE age>60;

question 3

The Previous_Employment_Details table will contain details of all the past companies that the employee has worked at. Write a query to extract the list of employees who last worked at TCS just before joining the current company.

SELECT u.name
FROM users u
INNER JOIN Previous_Employment_Details p 
ON u.user_id = p.user_id
WHERE p.company_name = 'TCS'
ORDER BY p.date_of_joining DESC
LIMIT 1

Question 5

Write a query to extract the Top 3 highest paying companies (Previous Employment) along with their locations. Include designation with the salary of the employee.

SELECT p.company_name, p.location, p.designation, p.salary
FROM Previous_Employment_Details p
ORDER BY p.salary DESC
LIMIT 3;

CodePudding user response：

Question 3 Ans

Select employees_id
From previous_employment_details
Where company_id="TCS"

CodePudding user response：

Question 4

Answer is somewhat like this Im not sure

Select e.id, s.amount , e. Joined_at, e.email ,s.lastdrwn, c.name
From employee e , salary s, companies c