Capstone — Compose a Projection
Build the employee compensation projection required by the downstream export.
- Numeric functions
Exercise brief
Understand the request
Data contract owner A new employee feed needs one carefully ordered projection with stable output labels.
Build a six-column employee export that combines source columns, an alias, a source marker, and related compensation calculations.
Return
- Return staff_id, first_name, last_name, record_type, projected_bonus, and projected_total_compensation in this exact left-to-right order.
- Alias employee_id as staff_id and use the text literal Employee as record_type.
- Calculate projected_bonus as 10 percent of salary, rounded to 2 decimal places.
- Calculate projected_total_compensation as salary plus projected_bonus.
Constraints
- Keep one row per employee.
- Complete the export in one SELECT list without filtering, grouping, or sorting.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)salaryINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Treat the SELECT list as a six-column data contract and verify each projected item in order.
Hint 2
The projection combines one renamed identifier, two source name columns, one text literal, and two related compensation calculations.
Hint 3
Calculate the bonus once conceptually, then use the same 10 percent rule when deriving total compensation.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT employee_id AS staff_id, first_name, last_name, 'Employee' AS record_type, ROUND(salary * 0.10, 2) AS projected_bonus, salary + ROUND(salary * 0.10, 2) AS projected_total_compensation FROM employees;Why this works
This capstone combines projection, aliasing, a constant source marker, and compatible compensation arithmetic. The derived values are meaningful to a downstream payroll forecast and each requirement is stated without relying on Expected Output.
Success check
Every employee appears once with the six requested columns, stable labels, and compensation calculations that reconcile to salary plus the 10 percent projected bonus.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| staff_id | first_name | last_name | record_type | projected_bonus | projected_total_compensation |
|---|---|---|---|---|---|
| 100 | John | Smith | Employee | 12000 | 132000 |
| 101 | Alice | Johnson | Employee | 8500 | 93500 |
| 102 | Bob | Wilson | Employee | 8000 | 88000 |
| 103 | Carol | Davis | Employee | 6000 | 66000 |
| 104 | David | Brown | Employee | 7000 | 77000 |
| 105 | Emma | Taylor | Employee | 9500 | 104500 |
| 106 | Frank | Green | Employee | 6500 | 71500 |
| 107 | Grace | White | Employee | 9000 | 99000 |
| 108 | Henry | Clark | Employee | 5500 | 60500 |
| 109 | Ivy | Martinez | Employee | 6800 | 74800 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
Basic SQL Functions
Practice production-oriented string cleanup, numeric transformations, NULL handling, tolerant conversion, and delimiter parsing.
ORDER BY & Sorting
Practice deterministic SQL ordering with tie-breakers, custom priorities, NULL placement, expressions, joined data, aggregates, and portable top-N patterns.
Open the interactive workspace and practice across SQL topics.