mobinni / complete-sql-database-bootcamp-zero-to-mastery Goto Github PK

Hi Mo,
Only a small clarification here - "Who is the oldest employee?" query is returning the age not the name of the oldest employee...
Your question is opening a challenge and accept it - I will come back with the solution.
Thanks.

Question:

/*

• DB: Employees
• Table: employees
• Question: Filter employees who have emp_no 110183 as a manager
  */

Answer:

-- Written with JOIN
SELECT e.emp_no, first_name, last_name
FROM employees as e
JOIN dept_emp as de USING (emp_no)
JOIN dept_manager as dm USING (dept_no)
WHERE dm.emp_no = 110183

In this answer, if we write it like that we will see all the employees who are in the department - d003.
In that dep are 2 managers. and emp_no = 110183 was manager only until 1992-03-21.

My resolution to this:

Select * from "public"."employees" as e
join dept_emp as de on de.emp_no = e.emp_no
join dept_manager as d on d.dept_no = de.dept_no

where d.emp_no = 110183 and de.to_date <= '1992-03-21'
.

Let me know if I'm wrong. Thank you

Question:

/*
* DB: Employees
* Table: employees
* Question: Get me all the employees above 60, use the appropriate date functions
*/

I think this statement is asking to return all employees whose age is above 60 years.
So, as per the requirement 60 years 0 month 1 days should also count as age is above 60 years (60.0027397260274 after days converting in year) but answers provided returns results for those employees whose age is above >= 61 years.

I think the answer should be:

as it returns every employee with age above 60 counting days and months too.

Hello. The first question in SQL Deep Dive/Operator precendence/questions.sql is a bit misleading after the update.

* Select people either under 30 or over 50 with an income above 50000
[added later on] Include people that are 50
* that are from either Japan or Australia

There are 3 options to read this after the update for their location filter:

• People under 30 or over/at 50 from either Japan or Australia
• People over/at 50 from either Japan or Australia
• People at exactly 50 from either Japan or Australia

The solution clearly reveals the logic that should be applied (location should apply to the entire range group <30 and >=50).
Please consider question update to avoid confusion for the students.

Please change the question to Show me all the employees that have had more than 15 salary changes and have ever worked in the department development

The original sentence conveys showing the employees that are currently working in the "Development" department which is not the intended result and need an extra de.to_date > NOW() condition:

SELECT e.emp_no, count(s.from_date) as "amount of raises"
FROM employees as e
JOIN salaries as s USING(emp_no)
JOIN dept_emp AS de USING(emp_no)
WHERE de.to_date > NOW() And de.dept_no = 'd005'
GROUP BY e.emp_no
HAVING count(s.from_date) > 15
ORDER BY e.emp_no;

When I ran the query I got a slightly different result, 59361.925908612832:

In the course we checked how many times one employee had an increase. So we already know there are multiple salary values per single employee. Getting simple AVG(salary) is also including historical values which will not give us the actual current value for average salary, instead average for the entire column. Not a good output if you are to answer the CEO asking how much we spend monthly on salaries (and you miss to report the extra ~10k).
Looking at the data itself the current salary of the employees has a to_date '9999-01-01' and adding a WHERE clause would give us accurate info.
As this is early on the course exercise I realize the goal is different and students should simply practice use of AVG. Maybe consider changing the question from

• What is the average salary for the company? to
• What is the all time average salary for the company?