Level 5 · Monthly salary sheet

This ticket uses the moves from the margin level, on a new table: employees, one row per person at Harbor & Pine, with a yearly salary in whole dollars. Two columns are built rather than picked: one from text, one from numbers.

Gluing text and doing math in the SELECT list

|| is the SQL operator that joins text values end to end. Put a literal space between the names, or they run together. Arithmetic works the same way, one row at a time:

SELECT
  first_name || ' ' || last_name AS full_name,
  salary,
  ROUND(salary / 12.0, 2) AS monthly_salary
FROM employees;

The first two rows are Catherine Hale, 285,000 a year and 23,750 a month, and Robert Kim, 235,000 a year and 19,583.33 a month. CONCAT(first_name, ' ', last_name) gives the same text and is available in almost every database.

salary / 12.0 uses 12.0 on purpose. DuckDB, the engine in this game, returns a decimal for salary / 12 anyway, but several databases throw the remainder away when both sides are integers. Writing 12.0 makes the division decimal everywhere, so the query survives being pasted into the company warehouse.

Common mistakes

• Concatenating a column that can be NULL. With ||, one NULL makes the whole string NULL. No employee name is missing here, but the next levels deal with it.
• Rounding too early. Round once, in the final column. A rounded monthly figure multiplied back by 12 drifts away from the real salary.
• Writing first_name + ' ' + last_name. In DuckDB and Postgres + is for numbers only, so the database tries to add text and reports an error. Use || or CONCAT for text.
• Calling the new column name. It works, but full_name says what is in it, and the products table already has a name column that means something else.
• Dropping the original column. Keep salary next to monthly_salary, so the reader can check the math on any row by eye. Laura asked for both on purpose.

In SQL Server

SQL Server joins text with + (or CONCAT), and salary / 12 on an INT column truncates to a whole number there:

SELECT FirstName + ' ' + LastName AS FullName,
       ROUND(Salary / 12.0, 2) AS MonthlySalary
FROM Employees;