Group-Level Filters, Self Join, STRING_AGG, Dates & CASE

The Crux in 60 Seconds

Foundational Concepts & Comparisons

Architectural Mechanics & Workflows

Assignment Q4: departments where no two employees share a salary

Department 100 has two employees with the same salary, so it does not qualify; the same for 200. Departments 300 and 500 qualify. The instructor inserted extra employee rows (for 500 and 300, with different salaries) to test the logic. Students were unsure whether to group by employee name; the answer is to group by department, since "none of the employees" means compare within the department.

SELECT e.department_id, d.department_name
FROM employee e LEFT JOIN department d ON e.department_id = d.department_id
GROUP BY e.department_id, d.department_name
HAVING COUNT(DISTINCT e.salary) = COUNT(*)

• Department name needs the department table. Department ID to name is one to one, so putting both in GROUP BY gives the same answer. Add e.department_id too, and it must also be in GROUP BY.
• A student who used INNER JOIN got only HR: department 500 is not in the department table, so its name is NULL with LEFT JOIN. Count with COUNT(1) or COUNT(*), either is fine.

Assignment Q5: subcategories that have all three kinds of returns

Join orders (alias o) with returns, group by subcategory, and require that the number of distinct return reasons is 3. The result had 12 subcategories.

SELECT o.subcategory FROM orders o
JOIN returns r ON o.order_id = r.order_id
GROUP BY o.subcategory
HAVING COUNT(DISTINCT r.return_reason) = 3

Assignment Q6: cities where not a single order was returned

SELECT o.city FROM orders o
LEFT JOIN returns r ON o.order_id = r.order_id
GROUP BY o.city
HAVING COUNT(r.return_reason) = 0

• A student began with COUNT(*) per city; that counts orders, not returns. The count must be on a column from the returns table.
• Why it works: an order that was not returned has NULL in every returns column after the LEFT JOIN, and COUNT ignores NULLs. A city with at least one return has a count of 1 or more.
• Check run: Coral Springs had many orders, all with NULL return reason. Without the filter all cities show; with = 0 only such cities remain (Coral Springs).

Trainer Q&A: why WHERE does not work for Q5 and Q6

Self join: employee name with manager name

employee columns: employee id, name, department id, salary, manager id, age. Manager id holds the employee id of another row in the same table. Example: Ankit has manager id 4, which is Rohit, so Rohit is Ankit's manager. The question will tell you that manager id refers to the same table.

SELECT E1.employee_name AS employee_name, E2.employee_name AS manager_name
FROM employee E1
INNER JOIN employee E2 ON E1.manager_id = E2.employee_id

• Think of two copies of the table. Manager id 4 on E1's row lands on the row with employee id 4 in E2, so that row's name becomes the manager name. Vikas also gets Rohit.
• Reverse condition E1.employee_id = E2.manager_id also works: ultimately the tables are the same. You only change which alias supplies the employee name and which the manager name.
• INNER, LEFT or RIGHT join can be used; the structure is still called a self join.
• Real databases use this design often (manager id in the employee table), and it is one of the most frequently asked SQL interview questions.

Follow-up questions on the same join

SELECT E1.employee_name FROM employee E1
JOIN employee E2 ON E1.manager_id = E2.employee_id
WHERE E1.salary > E2.salary      -- E2.salary is the manager's salary

This returned 3 employees who earn more than their manager. Print the manager salary beside it to check. Other variants: employee age greater or less than the manager's, or a manager's date of birth before the employee's; once the join exists, only the filter changes.

Self join: tips and alias rules (trainer Q&A)

Joins are now covered: inner, left, right, full outer, cross and self. Many-to-one, one-to-many and many-to-many relationships will be discussed again at the weekend; watch the two videos in the folder first.

STRING_AGG: list the values of a group

Counting, averaging and min/max are aggregations; concatenating a column's values per group is one too. Goal: one row per department with all employee names.

SELECT department_id, STRING_AGG(employee_name, ',') AS list_of_employees
FROM employee
GROUP BY department_id

• Two arguments: the column to aggregate, and the separator. Comma, pipe, colon: any character works.
• Other databases use other names (group concat, listagg; a student noted Oracle). SQL Server uses STRING_AGG.
• Sorting the list: some databases put ORDER BY inside the function; in SQL Server add WITHIN GROUP (ORDER BY ...) after it.

STRING_AGG(employee_name, ',') WITHIN GROUP (ORDER BY employee_name)   -- alphabetical
STRING_AGG(employee_name, ',') WITHIN GROUP (ORDER BY salary DESC)     -- highest salary first

Ordering by salary (ascending) put the lowest-paid first: in department 100, Rohit (5,000), then Vikas, Ankit and Mohit. ASC is the default; DESC gives descending. Used often at work and often asked in interviews.

Date functions

DATEPART(part, date)

Returns one part of a date, as an integer. Earlier assignments filtered a year with a range (1 January to 31 December); now: WHERE DATEPART(year, order_date) = 2020.

• Parts: year, quarter, month, day of year, day, week, weekday, hour, minute, second, microsecond. Short forms work too (qq, mm, m and so on). Example: week gave 46, the 46th week of the year.
• Cannot use the column alias in WHERE: WHERE runs before SELECT, so repeat the expression.
• The column must have a date data type. Date functions do not work on strings; that is why correct data types matter when designing tables.

More date tests and trainer Q&A

DATEADD(part, number, date) and DATEDIFF(part, start, end)

Both take three parameters (DATEPART takes two).

DATEADD(day, 5, order_date)         -- 5 days later
DATEADD(week, 5, order_date)        -- 5 weeks later (about 35 days)
DATEADD(day, -5, order_date)        -- subtract: pass a negative number, no separate function
DATEDIFF(day, order_date, ship_date)   -- earlier date first

• DATEDIFF in days gave 3, 3, 4 and 7 days from order to ship. In weeks it gives 0 when both dates are in the same week; in years it is mostly 0 here.
• A student suggested subtracting day parts. That breaks when the month or year changes (day of year too, when the year changes). DATEDIFF takes care of it, so use the functions.

CASE WHEN

Pure SQL has no IF/ELSE; use CASE. One END per CASE, many WHEN, optional ELSE. It is a derived column, so add it after a comma and give it an alias.

CASE WHEN profit < 100 THEN 'low profit'
     WHEN profit < 250 THEN 'medium profit'
     WHEN profit < 400 THEN 'high profit'
     ELSE 'very high profit' END AS profit_category

• Exact ranges: profit >= 100 AND profit < 250. Then order does not matter. With > 100 a profit of exactly 100 would fall to ELSE, so include the boundary; BETWEEN also works but includes both ends.
• Anything valid in WHERE (functions, dates) is valid in WHEN.
• Negative example: > -100 AND < 0 for "loss" misses profits below -100; simply profit < 0 means loss.
• Nested CASE (a CASE inside THEN) works but is rarely needed; normal CASE handles most cases. Used heavily at work and in interviews.

Best Practices & Golden Rules

Numbers & Metrics to Remember

Mini Glossary

Quick Self-Test

1. 1In Q4, why does COUNT(DISTINCT salary) = COUNT(*) identify a qualifying department?
2. 2Why does a LEFT JOIN, not INNER JOIN, show department 500 with a NULL name?
3. 3Scenario: a colleague finds cities with no returns using WHERE r.order_id IS NULL. Why can Bangalore (one returned, one not) appear wrongly?
4. 4Why does COUNT(r.return_reason) = 0 work for cities with no returns?
5. 5Write the join condition to show each employee's manager name, and say which alias gives each name.
6. 6Scenario: you need each department's employees as one sorted list, lowest salary first, in SQL Server. What do you write?
7. 7Troubleshooting: your query orders by a column alias of DATEPART in WHERE and errors. Why, and what is the fix?
8. 8Scenario: your CASE shows no "low profit" rows although profits under 100 exist. What is likely wrong?