0
\$\begingroup\$

SQL employee database querying Questions

Consider the employee database of an organization XYZ private limited

enter image description here

                 ______________Questions ________________

enter image description here

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 4 answer

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

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;
\$\endgroup\$
4
  • \$\begingroup\$ Where is the answer to question 4? \$\endgroup\$
    – Mast
    Jun 19 at 5:04
  • \$\begingroup\$ Hi hope you are doing well thanks for reply i have added im not quite sure if its right I'm a student and still learning can you please help me \$\endgroup\$
    – mac
    Jun 19 at 5:18
  • 6
    \$\begingroup\$ Are you answering these question without actually running them on a database? Testing queries is a very important step. \$\endgroup\$ Jun 19 at 11:12
  • \$\begingroup\$ (Question 1) is wondrously ill-defined. I find your take - unexpected.) \$\endgroup\$
    – greybeard
    Jun 19 at 17:48

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.