SQL Learning

2025-04-14 23:56

List the first 10 employees with their emp_no, first_name, and last_name from the employees table. Sort them by hire_date in ascending order.
```
 SELECT emp_no, first_name, last_name 
 FROM employees
 ORDER BY hire_date
 LIMIT 10;
```

Find the number of employees hired each year. Output columns: hire_year, employee_count and sort by hire_year ascending.

 SELECT YEAR(hire_date) AS hire_year, COUNT(*) as employee_count 
 FROM employees
 GROUP BY YEAR(hire_date)
 ORDER BY hire_year ASC;

Write a SQL query to retrieve the name of each department, along with the employee number (emp_no) of its manager and the manager's full name (concatenated as first_name + last_name). Use the departments, dept_manager, and employees tables. The output should include columns named dept_name, manager_emp_no, and manager_name, sorted alphabetically by dept_name.
```
 SELECT  dept.dept_name as dept_name, e.emp_no as manager_emp_no, concat(e.first_name, " ", e.last_name) as manager_name FROM employees as e
```

JOIN dept_manager as dm ON e.emp_no = dm.emp_no
JOIN departments as dept ON dm.dept_no = dept.dept_no
ORDER BY dept.dept_name ASC;
```

Write a SQL query to retrieve the top 3 highest-paid employees in each department. Use the employees, dept_emp, and salaries tables. The output should include columns named dept_no, emp_no, salary, and rank_in_dept, where rank_in_dept represents the employee’s rank based on salary within the department.
Find the average salary per department.

Reference