SELECT Statements SQL practice problemHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Capstone — Compose a Projection

Return staff_id, first_name, last_name, record_type, bonus, and salary_plus_manager.

Interview brief

Understand the request

Data contract owner A new employee feed needs one carefully ordered projection with stable output labels.

Return one focused projection with staff_id (employee_id), first_name, last_name, the literal record_type "Employee", bonus (salary * 0.10), and salary_plus_manager (salary + manager_id), in that order.

Return

Alias employee_id as staff_id.
Use Employee as the record_type literal.
Calculate bonus as salary * 0.10 and salary_plus_manager as salary + manager_id.

Constraints

Keep the exercise inside one SELECT list with no filtering or sorting.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

employee_idINTEGER
first_nameVARCHAR(50)
last_nameVARCHAR(50)
salaryINTEGER
manager_idINTEGER

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 contract. Add and verify one projected item at a time.

Hint 2

The projection combines one renamed source column, two ordinary columns, one text literal, and two arithmetic expressions.

Hint 3

Start with employee_id AS staff_id and the two name columns; then add the literal and the two named calculations before FROM employees.

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, salary * 0.10 AS bonus, salary + manager_id AS salary_plus_manager FROM employees;

Why this works

This capstone stays deliberately inside the SELECT-list competency. It combines source-column aliases, ordinary projections, a constant literal, arithmetic, and NULL propagation without introducing filtering, sorting, functions, or CASE from other curriculum modules.

Success check

The six output columns appear in the requested order and preserve NULL arithmetic.

Expected result

Use this output to verify values, aliases, ordering, and row count.

staff_id	first_name	last_name	record_type	bonus	salary_plus_manager
100	John	Smith	Employee	12000	NULL
101	Alice	Johnson	Employee	8500	85100
102	Bob	Wilson	Employee	8000	80100
103	Carol	Davis	Employee	6000	NULL
104	David	Brown	Employee	7000	NULL
105	Emma	Taylor	Employee	9500	NULL
106	Frank	Green	Employee	6500	65105
107	Grace	White	Employee	9000	90100
108	Henry	Clark	Employee	5500	55103
109	Ivy	Martinez	Employee	6800	68104

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: