TRAINING | DAY 7

CASE Inside Aggregates, String & NULL Functions, Set Operations

Day 6 assignment review (pivoting with CASE), SQL Server string functions, COALESCE, CAST and ROUND, then UNION, INTERSECT and EXCEPT.

The Crux in 60 Seconds

CASE inside SUM pivots rows to columns

SUM(CASE WHEN ... THEN sales END) gives separate columns (returned sales, 2019 sales, 2020 sales) per group. Most complex interview questions are this pattern.

Combine position functions

SUBSTRING takes start position and length; CHARINDEX finds a position; use them together with LEFT to cut a first name or a year out of text.

NULL handling for reports

ISNULL takes two arguments; COALESCE takes many and returns the first non-null value, left to right.

Set operations combine rows

UNION ALL keeps everything; UNION, INTERSECT and EXCEPT remove duplicates. Column count must match and types must be compatible. A join, by contrast, combines columns.

Foundational Concepts & Comparisons

JOIN

Matches rows on a key and brings columns from both tables together.

Set operation

Stacks rows of two queries vertically: 100 rows plus 200 rows gives 300 with UNION ALL.

UNION ALL vs UNION

UNION ALL: every row, rows of table 1 plus rows of table 2.
UNION: combines, sorts and removes duplicates, even duplicates inside one table.

INTERSECT vs EXCEPT

INTERSECT: rows common to both.
EXCEPT (MINUS in some databases): rows of the first query not in the second.

REPLACE vs TRANSLATE

REPLACE swaps a whole string for another.
TRANSLATE maps characters one to one (A to T, G to P).

ISNULL vs COALESCE

ISNULL(column, default): two arguments only.
COALESCE(a, b, c, ...): first non-null value; can end with a default.

INTERSECT vs INNER JOIN

INTERSECT is a set operation without duplicates, handy when comparing many columns.
Use a join when you need columns from both tables.

WHERE vs HAVING for spaces

Checking each name for stray spaces is row by row, so WHERE. HAVING is only for filtering an aggregation.

Architectural Mechanics & Workflows

Day 6 assignment review: questions and approaches

Question	Approach discussed
Employee name, manager name and age difference, where the employee's birth year is before the manager's	Self join on manager id; a new date of birth column was created for it. Compare the years of birth.
Subcategories that never had a return in November	Similar to an earlier assignment question; add a month filter: `DATEPART(month, order_date) = 11`.
Orders with only one product	Student logic accepted: group by order id and keep the groups where `COUNT(product_id) = 1`. No alias is needed with a single table; with more than one table, always alias.
Employee names per manager	Do the manager-name self join, then `STRING_AGG` on employee names grouped by manager name. A student on SQL Server 2008 had no STRING_AGG; advice: upgrade, and the instructor would check for a workaround. Understanding the logic matters; practice platforms were promised.
Business days between order date and ship date	Days = DATEDIFF in days; weeks = DATEDIFF in weeks; business days = days - weeks x 2. With one week between the dates, 2 days are subtracted.
Top five cities in the west region by average days between order and ship date	DATEDIFF in days, average by city (for example 4, 4, 5, 5 days), sort by the average, limit to five.
Last question: manager and senior manager	Self join twice: join the employee to the manager, then the manager to the next employee record for the senior manager. "Just one more join."

Q: category, total sales and total sales from returned orders

LEFT JOINreturns on order id (keeps all orders)

→

CASEsales only when return reason IS NOT NULL

→

SUM + GROUP BYcategory

SELECT o.category, SUM(o.sales) AS total_sales,
       SUM(CASE WHEN r.return_reason IS NOT NULL THEN o.sales ELSE 0 END) AS return_sales
FROM orders o LEFT JOIN returns r ON o.order_id = r.order_id
GROUP BY o.category

Why LEFT JOIN: total sales needs all orders; INNER JOIN would lose them.
A return reason is one of three values, or NULL if the order was not returned.
Step by step: first select category, sales and return reason with the CASE, no aggregation, to see sales only on returned rows. Then SUM. ELSE 0 is optional: without it the other rows are NULL, and SUM ignores NULLs.
A student who disconnected asked for a recap: sales gives total; for return sales take sales when the return reason is not null, else zero, then aggregate.

Q: category, total sales in 2019 and total sales in 2020 (pivot)

SELECT category,
  SUM(CASE WHEN DATEPART(year, order_date) = 2019 THEN sales ELSE 0 END) AS sales_2019,
  SUM(CASE WHEN DATEPART(year, order_date) = 2020 THEN sales ELSE 0 END) AS sales_2020
FROM orders GROUP BY category

Filtering the date to 2019 and 2020 would not give separate columns; use one CASE per year.
Run it without GROUP BY first: 2019 rows show zero in the 2020 column and the reverse. Then aggregate.
Do not put the year in GROUP BY: both years already have their own columns.
This is a pivot: data in rows turned into columns. Build in steps: SELECT and CASE first, then the aggregation.

Stage	What you see
CASE columns, no GROUP BY	One row per order: a 2019 order has 0 in the 2020 column, a 2020 order has 0 in the 2019 column.
SUM with GROUP BY category	One row per category with both yearly totals side by side.

Instructor's advice on practiceMost complex questions combine CASE WHEN with an aggregate. Cracking them puts you ahead of 50% of candidates. Questions follow 10 to 12 patterns that also appear in daily work. After subqueries and window functions, solve questions from the instructor's playlist and other sites. A first-attempt failure is normal; speed comes with practice. A student credited the instructor's videos for this CASE-in-aggregate trick, which also cover the business-days question.

String functions (SQL Server)

Examples use order_id and customer_name. There are many string functions; the instructor covers the important ones, and the rest are on the SQL Server documentation site or W3Schools.

Function	What it does and what was shown
`LEN(col)`	Number of characters per value (a name of 11 characters gives 11). A space counts. Leading spaces counted; in the example trailing spaces did not seem to be counted. Ideally data has no trailing spaces.
`LEFT(col, n)`, `RIGHT(col, n)`	First n or last n characters. `LEFT(customer_name, 4)` gives the first four.
`SUBSTRING(col, start, length)`	Three arguments: column, position to start at, number of characters. `SUBSTRING(customer_name, 4, 5)` starts at the 4th character and takes five.
Year from order id	Order id is two characters, a hyphen, then the year: `SUBSTRING(order_id, 4, 4)` gives 2020 or 2019, even with no order date column.

Common confusionNot "from the 4th to the 5th character". The start character is included and the third argument is a count: with 5 you get one more character.

CHARINDEX, CONCAT and combining functions

Function	What it does and what was shown
`CHARINDEX(search, col [, start])`	Position of the first occurrence. A space in the first name example is at position 7. A missing character returns 0. N occurs three times in one name, first at position 4, so 4 is returned. The optional third argument is the start location (default: the beginning); start at first position plus one to find the next occurrence.
`CONCAT(a, b, ...)` or `+`	Joins strings or columns. Put a space, hyphen, underscore or any text between them as an argument.
First name	Combine: `LEFT(customer_name, CHARINDEX(' ', customer_name) - 1)`, the position of the space minus one, so the space is not included. The instructor was unsure about the minus one and said he would check.

CHARINDEX gives only a position, never the text; pass it to LEFT or SUBSTRING.
There is no dedicated middle-name function; use a workaround with SUBSTRING, LEFT and CHARINDEX.

REPLACE, TRANSLATE, REVERSE, TRIM

Function	Behaviour
`REPLACE(col, 'CA', 'PB')`	Finds the whole string and replaces it, everywhere it occurs; other values stay unchanged. Works for one character too (A to B in names). Remove spaces by replacing a space with nothing: all spaces go.
`TRANSLATE(col, 'AG', 'TP')`	Maps character one to one (A to T, G to P). The second and third arguments must have the same length, or an error. Missing characters are fine; every occurrence changes, with no limit. Can map to a pipe, plus or minus. Example: C to a pipe.
`REVERSE(col)`	Reverses the string.
`TRIM(col)`	Removes leading and trailing spaces, not spaces in the middle.

Why TRIM matters, and trainer Q&A

Application data may arrive without sanity checks. A trailing space in a customer name can make a filter miss that customer. Trim before giving data to a consumer, or use TRIM in the WHERE condition when searching.
Find values with leading or trailing spaces: compare LEN(customer_name) with LEN(TRIM(customer_name)). Equal means no such spaces; different means there are. Put it in WHERE: it is a row check, not an aggregation, so not HAVING.
Questions: can REPLACE or TRANSLATE remove spaces? Yes with REPLACE (replace with nothing). Does TRANSLATE work if the string lacks one of the characters? Yes. Is there a limit on occurrences? No. "Transact-SQL" in the documentation is nothing to worry about here.
Go through the other string functions in the documentation and try them yourself.

NULL handling

Filter with IS NULL / IS NOT NULL. For reports, NULL looks poor (for example a NULL city), so replace it.

ISNULL(city, 'Unknown')                      -- two arguments only
COALESCE(city, state, 'Unknown')             -- first non-null, left to right

ISNULL: value if present, otherwise the second argument.
COALESCE: takes more than two arguments. With city NULL it checks the next column (state gave Washington). If all are NULL and no default is given, it returns NULL. Works for any data type, including dates and integers; for a sales column the default must be a compatible type, since SQL Server tries to convert it and gives an error if it cannot.
COALESCE can replace ISNULL with two arguments; it is your choice.

CAST and ROUND

CAST(sales AS INT)      -- change the data type in the query (sales is float or decimal)
ROUND(sales, 1)         -- one decimal place; 2, 3 and so on also work

Numeric functions are many; the instructor will share a link and answer doubts next session.

Set operations

Example tables orders_east and orders_west: same structure (order id, region, sales), different names, order ids may repeat. Rule for every set operation: same number of columns in both queries and compatible data types, or an error. Columns are compared by position, and names need not match. They can be applied to the same table too.

Operator	Behaviour
`UNION ALL`	Everything: rows of the first plus rows of the second (100 + 300 = 400). The only operator that keeps duplicates. With a row present in both tables the example gave 5 rows including the duplicate.
`UNION`	Combines, sorts and removes duplicates; the repeated "3 East 100" shows once. Removes duplicates within one table too. To dedupe each table but keep duplicates across, use `DISTINCT` in each SELECT, then UNION ALL.
`INTERSECT`	Common rows only (order id 3), duplicates removed even if repeated in one table. You may select only some columns (for example only order id) if both queries match. To see other columns too, use an inner join.
`EXCEPT` (`MINUS`)	Rows in the first query but not the second. In SQL Server it is EXCEPT. Example result: "4 East 300". Also removes duplicates.

Rows only in either tableRun EXCEPT one way, EXCEPT the other way, and combine with UNION.

Testing useEXCEPT shows which rows differ between expected output and actual output, or between two tables.

Student question	Answer
INTERSECT vs inner join?	INTERSECT is a set operation with no duplicates; a join is for data from both tables. With ten columns to match, INTERSECT is more convenient. Different use cases.
Does INTERSECT return NULL when both sides have NULL?	Tested live: yes, NULL appears in the result. "When in doubt, just try it."
Columns selected in a different order?	May run if types are compatible, but comparison is by position.

From now on assignment questions mix all topics, so decide yourself which tool to use.

Best Practices & Golden Rules

Build CASE queries in stepsSelect and CASE first, check the output, then add SUM and GROUP BY.

LEFT JOIN when you also need totalsAn INNER JOIN would lose the unreturned orders.

Match columns in set operationsSame count, compatible types, compared by position.

Remember what removes duplicatesUNION, INTERSECT and EXCEPT do; only UNION ALL keeps all rows.

Clean text before comparingUse TRIM; check with LEN versus LEN of TRIM in WHERE.

If unsure, try itTest the function or operator on small data before relying on it.

Numbers & Metrics to Remember

days subtracted per week for business days

CHARINDEX result when the character is absent

arguments of SUBSTRING

rows from the UNION ALL example, duplicate included

Mini Glossary

Pivot: turning row values into columns, here with CASE inside SUM.

Business days: days between dates minus 2 for each week.

SUBSTRING: extracts a given number of characters from a start position.

CHARINDEX: returns the position of the first occurrence, or 0.

COALESCE: returns the first non-null value from a list.

Set operation: combines the rows of two queries (UNION, INTERSECT, EXCEPT).

INTERSECT: rows present in both queries, without duplicates.

EXCEPT: rows of the first query missing from the second (MINUS elsewhere).

Quick Self-Test

1Why does the returned-sales query use a LEFT JOIN, and what does omitting ELSE 0 do?
2Scenario: you need category, sales for 2019 and sales for 2020 in separate columns. Which expressions go in SELECT, and why is year not in GROUP BY?
3How are business days computed from order and ship dates?
4An order id is two characters, a hyphen, then the year. What does SUBSTRING(order_id, 4, 4) return, and what do the two 4s mean?
5Scenario: extract the first name from customer name. Which functions combine, and what caution did the instructor mention?
6How do REPLACE and TRANSLATE differ, and what error appears with TRANSLATE?
7Troubleshooting: a customer cannot be found by name though it looks correct. How do you detect and handle trailing spaces?
8Scenario: two tables hold the same rows apart from a few differences. Which operators show rows only in one, and which keeps duplicates?