can u provide solution for excercis 4 where we dont use cte or my sqljoins but cross join?

Question

query i wrote how can i fix it ?

SELECT
COUNT(emp_no)
FROM
employees e
WHERE
emp_no IN (SELECT
emp_no
FROM
salaries
WHERE
salary < (SELECT
AVG(salary)
FROM
salaries))
AND gender = 'm';

Answer 1

Hi Madhav!
Thanks for reaching out.

Actually, we have to use a CTE. You can use the following code:

WITH cte AS (
SELECT AVG(salary) AS avg_salary FROM salaries
)

SELECT
SUM(CASE WHEN s.salary < c.avg_salary THEN 1 ELSE 0 END) AS no_salaries_below_avg_w_sum,
COUNT(s.salary) AS no_of_salary_contracts
FROM salaries s JOIN employees e ON s.emp_no = e.emp_no AND e.gender = 'M' CROSS JOIN cte c;

Hope this helps.
Best,
Tsvetelin

Answer 2

If we should use CTEs, then please correct the exercise 4 question, because now it clearly says that we should not use CTEs or Subqueries.

Answer 3

Hi Graziella!

Thanks for reaching out.

Please accept my apologies for the delayed response. Indeed, I had marked this question as unanswered because we wanted to the relevant exercise. We did so - can you please verify and let us know if now it makes sense or is there anything else you think requires an update?

Thank you very much.

Looking forward to your answer.
Kind regards,
Martin

can u provide solution for excercis 4 where we dont use cte or my sqljoins but cross join?

Submit an answer

related questions