SQL Exercises: Salesmen commission with the percent sign (%)

Last update on March 08 2025 08:18:34 (UTC/GMT +8 hours)

Salespeople with Percent Commission

From the following table, write a SQL query to select all the salespeople. Return salesman_id, name, city, commission with the percent sign (%).

Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

Sample Solution:

-- This query selects specific columns from the 'salesman' table and performs a calculation on the 'commission' column.
-- It also appends '%' to the result of the commission calculation.
SELECT salesman_id, name, city, '%', commission * 100
-- Specifies the table from which to retrieve the data (in this case, 'salesman').
FROM salesman;

Output of the Query:

salesman_id	name		city		?column?	?column?
5001		James Hoog	New York	%		15.00
5002		Nail Knite	Paris		%		13.00
5005		Pit Alex	London		%		11.00
5006		Mc Lyon		Paris		%		14.00
5007		Paul Adam	Rome		%		13.00
5003		Lauson Hen	San Jose	%		12.00

Code Explanation:

The said query in SQL retrieves data from the "salesman" table and returns the following columns:
salesman_id
name
city
The string "%"
The result of commission column multiplied by 100.
The percentage symbol in the query used to represent the data in the "commission" column as a percentage.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Explanation:

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

For more Practice: Solve these Related Problems:

• Write a SQL query to display salespeople with commission values formatted as percentages (e.g., '15.00%') and sorted by city.
• Write a SQL query to select salespeople whose names contain more than one word and format the commission by multiplying by 100 followed by a percent sign.
• Write a SQL query to retrieve salespeople records with commission rounded to two decimal places, appended with a '%' sign, filtering those with commission above the overall average.
• Write a SQL query to list salespeople with commission formatted as a percentage string and exclude those whose city name is shorter than five characters.
• Write a SQL query to output salespeople details with commission displayed as a percentage, ordering the results by the numeric commission value in descending order.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: SQL Formatting Output Exercises Home
Next SQL Exercise: Number of orders booked for each day.