Write the following queries in SQL using the Employees schema. The employee sche

Question

Write the following queries in SQL using the Employees schema.

The employee schema is as follows:

3.1 Find all the distinct titles. The result should be following:

3.2 Find the average salaries per the title. The result should be the following:

3.3 Find the number of employees for each department. The result should be the following:

3.4. Find the number of Male/Female employees for each department. The result should be the following:

3.5. Find the highest and lowest salary for each department. The result should be the following:

Eemployees Tales ] departments Columns dept_no dept-name Indexes Foreign Keys Triggers dept_emp Columns emp_no dept_no from_date todate Indexes Foreign Keys Triggers dept manager Columns emp_no dept-no from_date to date

Explanation / Answer

There is no title attribute/field in the given Tables. I assumed that title is in employees table...

-- 3.1

SELECT DISTINCT title

FROM employees;

-- 3.2

SELECT title, AVG(salary) AS avg_salary

FROM employees e, salaries s

WHERE e.emp_no = s.emp_no

GROUP BY title;

-- 3.3

SELECT dept_name, COUNT(emp_no) AS Emp_numbers

FROM departments d, dept_emp de

WHERE d.dept_no = de.dept_no

GROUP BY d.dept_no, dept_name;

-- 3.4

SELECT dept_name, COUNT(case when e.gender='Male' then 1 end) as Male_Emp, COUNT(case when e.gender='female' then 1 end) as Female_Emp

FROM departments d, dept_emp de, employees e

WHERE d.dept_no = de.dept_no AND de.emp_no = e.emp_no

GROUP BY d.dept_no, dept_name;

-- 3.5

SELECT dept_name, MAX(salary) highest_salary, MIN(salary) lowest_salary

FROM departments d, dept_emp de, salaries s

WHERE d.dept_no = de.dept_no AND de.emp_no = s.emp_no

GROUP BY d.dept_no, dept_name;

Let me know if you have any clarifications. Thank you

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.