can u provide solution for excercis 4 where we dont use cte or my sqljoins but cross join?
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';
1 answers ( 0 marked as helpful)
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