Explain how to use LEFT JOIN with COALESCE to handle NULL manager names, write a query to find employees with salary greater than the average salary, write a query to sum values with HAVING > 100000, and explain the difference between RANK() and DENSE_RANK().

For Q1, you join the EMPLOYEE table to itself on the manager ID and use COALESCE to replace a NULL manager name with a placeholder such as 'TBD': SELECT e.Emp_Name, COALESCE(m.Emp_Name, 'TBD') AS Manager_Name FROM EMPLOYEE e LEFT JOIN EMPLOYEE m ON e.Mgr_Id = m.emp_id; This ensures every employee row is returned even if they have no manager. Q2 requires a subquery that calculates the average salary: SELECT Emp_Name, salary FROM EMPLOYEE WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE); This returns only those employees whose salary exceeds the overall average. Q3 uses an aggregate with a HAVING clause: SELECT dept_id, SUM(salary) AS total_salary FROM EMPLOYEE GROUP BY dept_id HAVING SUM(salary) > 100000; This filters groups whose summed salary exceeds 100k. Q4 compares window functions: RANK() assigns a unique rank to each row and leaves gaps when ties occur, whereas DENSE_RANK() assigns consecutive ranks without gaps. For example, if two rows tie for first, RANK() will give them rank 1 and the next row rank 3, while DENSE_RANK() will give the next row rank 2. Use RANK() when you need gaps to reflect ties, and DENSE_RANK() when you want a compact ranking.