The EXISTS predicate - interview questions

 The EXISTS predicate in SQL is used to check if a subquery returns any rows. It's commonly used in conditional statements to test for the existence of rows in a subquery. Here are some interview questions related to the EXISTS predicate:

1. Basic Understanding:

• Question: What does the EXISTS predicate do in an SQL query?
• Answer: The EXISTS predicate checks if a subquery returns any rows. If the subquery returns one or more rows, EXISTS evaluates to TRUE; otherwise, it evaluates to FALSE.

2. Usage in Filtering:

• Question: How can you use the EXISTS predicate to filter results in a SELECT statement?
• Answer: You can use EXISTS in the WHERE clause to filter rows based on whether a related subquery returns any rows. For example:

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);

3. Difference from IN:

• Question: What is the difference between EXISTS and IN? When would you prefer one over the other?
• Answer: EXISTS checks if any row is returned by the subquery and stops processing once it finds a match. IN compares a column's value to a list of values or the result of a subquery. EXISTS is generally preferred for correlated subqueries, especially when the subquery involves complex joins or large datasets, as it can be more efficient.

4. Correlated Subqueries:

• Question: What is a correlated subquery, and how is it related to the EXISTS predicate?
• Answer: A correlated subquery is a subquery that references columns from the outer query. The EXISTS predicate is often used with correlated subqueries, where the subquery is evaluated for each row processed by the outer query.

5. Performance Considerations:

• Question: How does the performance of EXISTS compare to JOIN when filtering results?
• Answer: EXISTS can be more efficient than a JOIN when you only need to check for the existence of rows, rather than combining data from multiple tables. EXISTS stops processing as soon as it finds a match, which can reduce the number of rows processed, especially with large datasets.

6. NOT EXISTS:

• Question: How does NOT EXISTS work, and how is it different from using EXISTS?
• Answer: NOT EXISTS returns TRUE if the subquery returns no rows. It is the opposite of EXISTS, which returns TRUE if the subquery returns one or more rows. NOT EXISTS is used to filter out rows where a subquery returns results.

7. Handling NULL Values:

• Question: How does the EXISTS predicate handle NULL values in the subquery?
• Answer: The EXISTS predicate is not affected by NULL values. It only checks for the existence of rows in the subquery. If the subquery returns any rows, EXISTS evaluates to TRUE, regardless of whether those rows contain NULL values.

8. Combining with Other Predicates:

• Question: Can you combine EXISTS with other predicates like AND, OR, or NOT in a WHERE clause? Provide an example.
• Answer: Yes, EXISTS can be combined with other predicates. For example:

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
)
AND e.salary > 50000;

9. Nested EXISTS:

• Question: Can you nest EXISTS predicates? What would be the purpose?
• Answer: Yes, you can nest EXISTS predicates to check for multiple levels of existence. This is useful when you need to verify the existence of related data at multiple levels. For example:

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND EXISTS (
        SELECT 1
        FROM locations l
        WHERE l.location_id = d.location_id
        AND l.city = 'New York'
    )
);

10. Comparison with ANY and ALL:

• Question: How does EXISTS differ from ANY and ALL?
• Answer: EXISTS checks for the existence of any rows in a subquery. ANY compares a value against any value returned by the subquery, and ALL compares a value against all values returned by the subquery. EXISTS is a predicate and returns a boolean, while ANY and ALL are used with comparison operators.

Union Operator - tricky interview questions

 The UNION operator in SQL is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements. Here are some tricky interview questions that test your understanding of the UNION operator:

1. Basic Understanding:

• Question: What is the difference between UNION and UNION ALL?
• Answer: UNION removes duplicate rows from the combined result set, whereas UNION ALL includes all rows, even duplicates.

2. Column Order and Type:

• Question: What happens if the columns in the SELECT statements used in a UNION query are not in the same order?
• Answer: The columns must be in the same order for all SELECT statements in a UNION. If they are not, SQL will throw a syntax error or produce incorrect results because the database engine matches columns by position, not by name.

3. Different Data Types:

• Question: Can you use UNION to combine result sets where the corresponding columns have different data types?
• Answer: The corresponding columns in the SELECT statements must have compatible data types. For example, you can combine an INT with a FLOAT, but not an INT with a VARCHAR unless an explicit conversion is done.

4. Order of Execution:

• Question: How does SQL handle the ORDER BY clause in a UNION query?
• Answer: The ORDER BY clause applies to the entire result set, not to individual SELECT statements. If you want to order the combined results, you must place the ORDER BY at the end of the last SELECT statement in the UNION query.

5. Performance Considerations:

• Question: Given two large tables, what are some performance considerations when using UNION vs. UNION ALL?
• Answer: UNION has a performance overhead because it eliminates duplicates, which requires sorting or a hashing operation. UNION ALL is faster because it does not perform duplicate elimination.

6. Using UNION with NULLs:

• Question: What happens when NULL values are present in the columns being combined with a UNION?
• Answer: UNION treats NULL values as equivalent when removing duplicates, so if two rows differ only by having NULL values in some columns, one of those rows will be removed from the final result set.

7. Complex Query with WHERE Clauses:

• Question: Consider two tables, Employees and Contractors, both having columns ID, Name, and Department. Write a UNION query that retrieves all Employees from the IT department and all Contractors from the HR department.
• Answer:

SELECT ID, Name, Department FROM Employees WHERE Department = 'IT'
UNION
SELECT ID, Name, Department FROM Contractors WHERE Department = 'HR';

8. Subqueries with UNION:

• Question: How would you use a UNION in conjunction with a subquery to retrieve the top 5 highest-paid employees and the top 5 highest-paid contractors from two different tables?
• Answer:

(SELECT TOP 5 ID, Name, Salary FROM Employees ORDER BY Salary DESC)
UNION
(SELECT TOP 5 ID, Name, Salary FROM Contractors ORDER BY Salary DESC);

9. Handling Inconsistent Data Across Tables:

• Question: Suppose you have two tables with inconsistent data (e.g., one table uses uppercase names, and the other uses lowercase). How can you write a UNION query that treats names case-insensitively?
• Answer:

SELECT LOWER(Name) AS Name FROM Table1
UNION
SELECT LOWER(Name) AS Name FROM Table2;

10. Detecting and Handling Inconsistencies:

• Question: How would you modify a UNION query to detect and flag inconsistencies in corresponding columns from the two tables being combined?
• Answer:

SELECT Name, 'Source: Table1' AS Source FROM Table1
UNION
SELECT Name, 'Source: Table2' AS Source FROM Table2
WHERE Name NOT IN (SELECT Name FROM Table1);

This query flags names present in Table2 but not in Table1, indicating a possible inconsistency.

Except operator - tricky interview questions and answers

 The EXCEPT operator in SQL is used to return all rows from the first SELECT statement that are not present in the second SELECT statement. It’s the opposite of the INTERSECT operator. Here are some tricky interview questions and answers involving the EXCEPT operator:

1. Basic Understanding:

• Question: What does the EXCEPT operator do, and how is it different from NOT IN?
• Answer: The EXCEPT operator returns all distinct rows from the first query that are not present in the second query. NOT IN is used to filter out rows based on a list of values from a subquery. EXCEPT compares the entire row, while NOT IN typically compares a single column.

2. Column Order and Data Type:

• Question: What happens if the columns in the SELECT statements used with EXCEPT are not in the same order or have different data types?
• Answer: The columns must be in the same order and have compatible data types for EXCEPT to work. If the column order is different or the data types are incompatible, SQL will throw an error.

3. Handling NULLs:

• Question: How does the EXCEPT operator handle NULL values?
• Answer: EXCEPT treats NULL values as equal to other NULL values. If a row in the first query contains NULL in one or more columns, and an identical row in the second query also contains NULL in the same columns, that row will not appear in the result set.

4. Set Operations:

• Question: Can EXCEPT be used in combination with other set operations like UNION or INTERSECT? If so, how would the precedence work?
• Answer: Yes, EXCEPT can be combined with UNION and INTERSECT. The precedence is that INTERSECT is evaluated first, followed by EXCEPT, and then UNION. Parentheses can be used to explicitly define the order of operations.

5. Query Optimization:

• Question: What are some performance considerations when using the EXCEPT operator on large datasets?
• Answer: EXCEPT can be costly on large datasets because it involves sorting and comparing rows. Indexes on the involved columns can help, but in some cases, using NOT EXISTS or LEFT JOIN ... IS NULL might be more efficient.

6. Complex Filtering:

• Question: Write a query using EXCEPT to find employees who are in the Employees table but do not have any matching record in the Contractors table based on their Name and Department.
• Answer:

SELECT Name, Department FROM Employees
EXCEPT
SELECT Name, Department FROM Contractors;

This query will return all employees who are not also contractors, considering both Name and Department.

7. EXCEPT vs. NOT EXISTS:

• Question: How would you rewrite an EXCEPT query using NOT EXISTS? Which one is generally more efficient?
• Answer: Here’s how to rewrite the query:

SELECT e.Name, e.Department
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM Contractors c
    WHERE e.Name = c.Name AND e.Department = c.Department
);

Efficiency depends on the specific database and dataset, but NOT EXISTS can be more efficient when there are indexes on the join columns, as it may avoid the need to sort large datasets.

8. EXCEPT with Multiple Conditions:

• Question: Write a query using EXCEPT to find products in a Products table that are available in one country but not in another.
• Answer:

SELECT ProductID, Country FROM Products WHERE Country = 'USA'
EXCEPT
SELECT ProductID, Country FROM Products WHERE Country = 'Canada';

This query returns products available in the USA but not in Canada.

9. Combining EXCEPT with Aggregation:

• Question: Can you use the EXCEPT operator with aggregated data? Provide an example.
• Answer: Yes, you can use EXCEPT with aggregated data. For example:

SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY Department
EXCEPT
SELECT Department, COUNT(*) AS ContractorCount FROM Contractors GROUP BY Department;

This query returns departments where the number of employees differs from the number of contractors.

10. Edge Cases with Empty Result Sets:

• Question: What happens if the second query in an EXCEPT operation returns no rows?
• Answer: If the second query returns no rows, the result of the EXCEPT operation will be the same as the first query, since there’s nothing to exclude.

These questions are designed to probe a candidate’s deep understanding of the EXCEPT operator, its nuances, and its practical applications in different scenarios.

Intersect operator - tricky interview questions

 The INTERSECT operator in SQL is used to return the common records from two or more SELECT statements. Here are some tricky interview questions related to the INTERSECT operator:

1. Basic Understanding:

• Question: How does the INTERSECT operator differ from the INNER JOIN?
• Answer: INTERSECT returns common rows from two SELECT statements, based on all columns, without needing explicit join conditions. INNER JOIN combines rows from two tables based on a related column but returns columns from both tables.

2. Order of Operations:

• Question: What happens if you use the INTERSECT operator between queries with different ORDER BY clauses?
• Answer: The INTERSECT operator doesn't preserve the order of the results from the original queries. If you need a specific order, you must apply an ORDER BY clause after the INTERSECT.

3. Null Handling:

• Question: How does the INTERSECT operator handle NULL values?
• Answer: The INTERSECT operator treats NULL as a comparable value. If NULL appears in the same position in both queries, it will be included in the results.

4. Performance Considerations:

• Question: How does the performance of INTERSECT compare to EXISTS or INNER JOIN for finding common records?
• Answer: INTERSECT can be slower than using EXISTS or INNER JOIN due to the fact that it eliminates duplicates and compares all columns. EXISTS might be faster when checking for existence without needing to return the exact rows.

5. Set Operations:

• Question: Can you use INTERSECT with more than two queries?
• Answer: Yes, you can chain multiple INTERSECT operations. The result will be the intersection of all the sets involved.

6. Combining with Other Set Operators:

• Question: What is the result of combining INTERSECT with UNION or EXCEPT?
• Answer: These set operators can be combined in a single query. For example, you can use INTERSECT to find common records and then use UNION to combine them with results from another query, or EXCEPT to exclude certain records.

7. Data Types:

• Question: What happens if the data types in the columns of the queries being intersected don’t match?
• Answer: For the INTERSECT operation to work, the number of columns and their data types must match. Otherwise, SQL will throw an error.

8. Complexity:

• Question: How would you use INTERSECT to find common records across three tables without directly using the INTERSECT keyword?
• Answer: You can achieve this by using nested INNER JOIN queries or EXISTS clauses. For example:

SELECT column_list
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table1.common_column = table2.common_column
)
AND EXISTS (
    SELECT 1
    FROM table3
    WHERE table1.common_column = table3.common_column
);

These questions test both the fundamental understanding of the INTERSECT operator and the candidate's ability to think critically about how it is used in more complex scenarios.

Inner join - tricky interview questions

 Here are some tricky interview questions related to INNER JOIN in SQL Server, along with explanations and answers:

1. Explain the difference between INNER JOIN and OUTER JOIN. Provide an example where switching from INNER JOIN to LEFT JOIN changes the result.

• Answer:

  • INNER JOIN returns only the rows that have matching values in both tables.
  • LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there are no matches, NULLs are returned for columns from the right table.

  Example:

  -- Using INNER JOIN
  SELECT a.ID, a.Name, b.Score
  FROM Students a
  INNER JOIN Scores b ON a.ID = b.StudentID;
  
  -- Using LEFT JOIN
  SELECT a.ID, a.Name, b.Score
  FROM Students a
  LEFT JOIN Scores b ON a.ID = b.StudentID;

  If there are students without scores in the Scores table, they will be excluded in the INNER JOIN result but included in the LEFT JOIN result with NULL values for Score.

2. How would you use INNER JOIN to find rows in one table that do not exist in another table?

• Answer:

  • Typically, INNER JOIN is used to find matches, but you can use it with a subquery to find non-matching rows.

  Example:

  SELECT a.ID, a.Name
  FROM Students a
  WHERE a.ID NOT IN (SELECT b.StudentID FROM Scores b);

  Alternatively, using LEFT JOIN and filtering for NULLs:

  SELECT a.ID, a.Name
  FROM Students a
  LEFT JOIN Scores b ON a.ID = b.StudentID
  WHERE b.StudentID IS NULL;

3. Describe a scenario where an INNER JOIN might result in a Cartesian product. How would you prevent it?

• Answer:

  • A Cartesian product occurs when there is no ON condition or the ON condition does not properly filter rows.

  Example:

  -- Cartesian product example (incorrect join condition)
  SELECT a.ID, a.Name, b.Course
  FROM Students a
  INNER JOIN Courses b;

  Preventing Cartesian Product: Ensure the ON clause correctly filters rows based on a logical relationship.

  SELECT a.ID, a.Name, b.Course
  FROM Students a
  INNER JOIN StudentCourses c ON a.ID = c.StudentID
  INNER JOIN Courses b ON c.CourseID = b.ID;

4. What is a self join? Provide an example where a self join might be useful.

• Answer:

  • A self join is a join of a table with itself.

  Example:

  -- Finding employees who are managers and their subordinates
  SELECT e1.EmployeeID, e1.EmployeeName, e2.EmployeeName AS ManagerName
  FROM Employees e1
  INNER JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

5. How can you join more than two tables using INNER JOIN? Provide an example.

• Answer:

  • You can join multiple tables by chaining INNER JOIN clauses.

  Example:

  SELECT 
      o.OrderID, 
      c.CustomerName, 
      p.ProductName, 
      od.Quantity
  FROM Orders o
  INNER JOIN Customers c ON o.CustomerID = c.CustomerID
  INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
  INNER JOIN Products p ON od.ProductID = p.ProductID;

6. How would you use an INNER JOIN to aggregate data from multiple tables?

• Answer:

  • Use INNER JOIN with aggregate functions like SUM, COUNT, etc.

  Example:

  -- Calculating total sales per product
  SELECT 
      p.ProductName, 
      SUM(od.Quantity * od.UnitPrice) AS TotalSales
  FROM OrderDetails od
  INNER JOIN Products p ON od.ProductID = p.ProductID
  GROUP BY p.ProductName;

7. What happens if you join tables on columns that contain NULL values?

• Answer:
  • In an INNER JOIN, rows with NULL values in the join columns will not match and therefore be excluded from the result set.

8. Explain a scenario where using INNER JOIN can be more efficient than using a subquery.

• Answer:

  • When retrieving related data from two tables, INNER JOIN can be more efficient than a subquery because it allows the SQL optimizer to better optimize the query execution plan.

  Example:

  -- Using INNER JOIN
  SELECT e.EmployeeName, d.DepartmentName
  FROM Employees e
  INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
  
  -- Using subquery (less efficient)
  SELECT e.EmployeeName, 
         (SELECT d.DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) AS DepartmentName
  FROM Employees e;

9. Can you use an INNER JOIN to join tables on multiple columns? Provide an example.

• Answer:

  • Yes, you can join tables on multiple columns by specifying multiple conditions in the ON clause.

  Example:

  SELECT 
      a.OrderID, 
      a.ProductID, 
      b.ProductName, 
      a.OrderDate
  FROM Orders a
  INNER JOIN Products b ON a.ProductID = b.ProductID AND a.SupplierID = b.SupplierID;

10. What is the impact of indexing on INNER JOIN performance?

• Answer:
  • Proper indexing can significantly improve the performance of INNER JOIN by allowing the SQL Server to quickly locate the rows to be joined, reducing the amount of data that needs to be scanned and processed.

11. Describe a situation where an INNER JOIN might return no rows even if there are rows in both tables.

• Answer:

  • If there are no matching values in the columns used in the ON clause, an INNER JOIN will return no rows.

  Example:

  SELECT a.ID, a.Name, b.Course
  FROM Students a
  INNER JOIN Courses b ON a.CourseID = b.CourseID
  WHERE b.CourseID IS NULL;

These questions not only test the candidate's understanding of INNER JOIN but also their ability to think critically and solve complex problems using SQL.

Left Join - Tricky interview questions

 Here are some tricky interview questions involving LEFT JOINs, designed to test your understanding of the concept and its nuances:

Question 1: Basic Understanding

Q: What is the difference between LEFT JOIN and INNER JOIN?

A: A LEFT JOIN returns all records from the left table, along with the matching records from the right table. If there is no match, the result is NULL from the right table. An INNER JOIN, on the other hand, returns only the records that have matching values in both tables.

Question 2: Handling NULLs

Q: Given the following tables, write a query to return all employees and their department names, including employees without a department.

• employees table:

id	name	department_id
1	Alice	1
2	Bob	2
3	Charlie	NULL
4	David	4

• departments table:

id	department_name
1	HR
2	Engineering
3	Sales

A:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Question 3: Filtering with LEFT JOIN

Q: Write a query to find employees who are not assigned to any department.

A:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

Question 4: Multiple LEFT JOINs

Q: Given an additional projects table, write a query to return all employees with their department names and project names, including employees without a department or project.

• projects table:

id	employee_id	project_name
1	1	Project A
2	2	Project B

A:

SELECT e.name, d.department_name, p.project_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id;

Question 5: Finding Unmatched Records

Q: Write a query to find employees who are either not assigned to any department or not assigned to any project.

A:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id
WHERE d.id IS NULL OR p.id IS NULL;

Question 6: Self Join

Q: Using the same employees table, write a query to return each employee and their manager's name. Assume manager_id refers to the id of the employee who is their manager.

A:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Question 7: Aggregation with LEFT JOIN

Q: Write a query to find the total number of employees in each department, including departments with no employees.

A:

SELECT d.department_name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;

Question 8: Complex Filtering

Q: Write a query to find all departments that have at least one employee and no employee is assigned to a project.

A:

SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN projects p ON e.id = p.employee_id
WHERE e.id IS NOT NULL
AND p.id IS NULL;

These questions cover a range of complexities and edge cases that can arise with LEFT JOINs, ensuring a comprehensive understanding of the topic.

Right Join - tricky interview questions

 Here are some tricky interview questions involving RIGHT JOINs, which will test your understanding of the concept and its nuances:

Question 1: Basic Understanding

Q: What is the difference between LEFT JOIN and RIGHT JOIN?

A: A LEFT JOIN returns all records from the left table and the matching records from the right table. If there is no match, the result is NULL on the right side. A RIGHT JOIN returns all records from the right table and the matching records from the left table. If there is no match, the result is NULL on the left side.

Question 2: Handling NULLs

Q: Given the following tables, write a query to return all departments and their employees, including departments without any employees.

• employees table:

id	name	department_id
1	Alice	1
2	Bob	2
3	Charlie	NULL
4	David	4

• departments table:

id	department_name
1	HR
2	Engineering
3	Sales

A:

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Question 3: Filtering with RIGHT JOIN

Q: Write a query to find departments that do not have any employees assigned.

A:

SELECT d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

Question 4: Multiple RIGHT JOINs

Q: Given an additional projects table, write a query to return all projects with their employee names and department names, including projects without employees or departments.

• projects table:

id	employee_id	project_name
1	1	Project A
2	2	Project B

A:

SELECT p.project_name, e.name, d.department_name
FROM projects p
RIGHT JOIN employees e ON p.employee_id = e.id
RIGHT JOIN departments d ON e.department_id = d.id;

Question 5: Finding Unmatched Records

Q: Write a query to find projects that do not have any employees assigned.

A:

SELECT p.project_name
FROM projects p
LEFT JOIN employees e ON p.employee_id = e.id
WHERE e.id IS NULL;

Question 6: RIGHT JOIN with Aggregation

Q: Write a query to find the total number of employees in each department, including departments with no employees.

A:

SELECT d.department_name, COUNT(e.id) AS employee_count
FROM departments d
RIGHT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;

Question 7: Complex Filtering

Q: Write a query to find all employees who are assigned to a department but not to any project.

A:

SELECT e.name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id
WHERE p.id IS NULL AND e.department_id IS NOT NULL;

Question 8: RIGHT JOIN with Self Join

Q: Using the same employees table, write a query to return each department and their respective managers' names. Assume manager_id refers to the id of the employee who is their manager.

A:

SELECT d.department_name, m.name AS manager
FROM departments d
RIGHT JOIN employees e ON d.id = e.department_id
LEFT JOIN employees m ON e.manager_id = m.id;

Question 9: RIGHT JOIN with Multiple Tables

Q: Write a query to list all departments and the names of employees who are working on projects in those departments. Include departments with no employees working on any projects.

A:

SELECT d.department_name, e.name, p.project_name
FROM departments d
RIGHT JOIN employees e ON d.id = e.department_id
RIGHT JOIN projects p ON e.id = p.employee_id
ORDER BY d.department_name;

Question 10: RIGHT JOIN with Subqueries

Q: Write a query to find departments with more than 2 employees, including departments with no employees, using RIGHT JOIN.

A:

SELECT d.department_name
FROM departments d
RIGHT JOIN (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 2
) e_counts ON d.id = e_counts.department_id
OR e_counts.department_id IS NULL;

These questions cover a variety of scenarios and edge cases involving RIGHT JOINs, ensuring a deep understanding of the concept.