-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql_queries
89 lines (77 loc) · 2.43 KB
/
sql_queries
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
# CHART 1
# Create a visualization that provides a breakdown between the male and female employees working in the company each year, starting from 1990.
SELECT
YEAR(d.from_date) AS calendar_year,
e.gender,
COUNT(e.emp_no) AS num_of_employees
FROM
t_employees e
JOIN
t_dept_emp d ON d.emp_no = e.emp_no
GROUP BY calendar_year , e.gender
HAVING calendar_year >= 1990;
# CHART 2
# Compare the number of male managers to the number of female managers from different departments for each year, starting from 1990.
SELECT
d.dept_name,
ee.gender,
dm.emp_no,
dm.from_date,
dm.to_date,
e.calendar_year,
CASE
WHEN YEAR(dm.to_date) >= e.calendar_year AND YEAR(dm.from_date) <= e.calendar_year THEN 1
ELSE 0
END AS active
FROM
(SELECT
YEAR(hire_date) AS calendar_year
FROM
t_employees
GROUP BY calendar_year) e
CROSS JOIN
t_dept_manager dm
JOIN
t_departments d ON dm.dept_no = d.dept_no
JOIN
t_employees ee ON dm.emp_no = ee.emp_no
ORDER BY dm.emp_no, calendar_year;
# CHART 3
# Compare the average salary of female versus male employees in the entire company until year 2002, and add a filter allowing you to see that per each department.
SELECT
e.gender,
d.dept_name,
ROUND(AVG(s.salary), 2) AS salary,
YEAR(s.from_date) AS calendar_year
FROM
t_salaries s
JOIN
t_employees e ON s.emp_no = e.emp_no
JOIN
t_dept_emp de ON de.emp_no = e.emp_no
JOIN
t_departments d ON d.dept_no = de.dept_no
GROUP BY d.dept_no , e.gender , calendar_year
HAVING calendar_year <= 2002
ORDER BY d.dept_no;
# CHART 4
# Create an SQL stored procedure that will allow you to obtain the average male and female salary per department within a certain salary range. Let this range be defined by two values the user can insert when calling the procedure.
DROP PROCEDURE IF EXISTS filter_salary;
DELIMITER $$
CREATE PROCEDURE filter_salary (IN p_min_salary FLOAT, IN p_max_salary FLOAT)
BEGIN
SELECT
e.gender, d.dept_name, AVG(s.salary) as avg_salary
FROM
t_salaries s
JOIN
t_employees e ON s.emp_no = e.emp_no
JOIN
t_dept_emp de ON de.emp_no = e.emp_no
JOIN
t_departments d ON d.dept_no = de.dept_no
WHERE s.salary BETWEEN p_min_salary AND p_max_salary
GROUP BY d.dept_no, e.gender;
END$$
DELIMITER ;
CALL filter_salary(50000, 90000);