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

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

Submit an answer

related questions