Consolidate and Rank Global Salaries in USD

employees_us

+---------+----------+--------+---------+
| emp_id  | name     | salary | country |
+---------+----------+--------+---------+
| 1       | Alice    | 120000 | US      |
| 2       | Bob      | 95000  | US      |
| 3       | Carol    | 115000 | US      |

exchange_rates

+----------+----------+
| currency | usd_rate |
+----------+----------+
| USD      | 1.0      |
| EUR      | 1.12     |
| JPY      | 0.0091   |

Scenario

Global HR reporting: consolidate country files and rank worldwide salaries.

Question

Append several country-specific employee tables into a single global table. Using an exchange-rate reference table, find the top 10 salaries worldwide in USD.

Hints

Show UNION-ALL, JOIN with exchange rates, order by converted salary, and limit 10.

Global top salaries in USD using UNION and exchange rates

You are given country-specific employee tables and a reference table of currency exchange rates to USD. For this example, only the US employee table is shown (employees_us), but assume similar tables exist for other countries. Append all country-specific employee tables into a single global dataset. Then, by joining to the exchange_rates table and converting all salaries to USD, return the top 10 employees worldwide ordered by salary in USD (highest first). Include employee ID, name, country, currency, and the converted salary in USD.

Table employees_us

column	type	constraints
emp_id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
salary	INTEGER	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	emp_id	name	salary
US	1	Alice	120000
US	2	Bob	95000
US	3	Carol	115000

Table exchange_rates

column	type	constraints
currency	VARCHAR(3)	PRIMARY KEY, NOT NULL
usd_rate	DECIMAL(12,6)	NOT NULL

Sample data:

currency	usd_rate
USD	1
EUR	1.12
JPY	0.0091

Expected output

Top 10 employees worldwide by salary converted to USD, ordered from highest to lowest. With the provided sample data (only US employees, whose salaries are already in USD), the result includes the 3 US employees ordered by salary.

country	currency	emp_id	name	salary_usd
US	USD	1	Alice	120000
US	USD	3	Carol	115000
US	USD	2	Bob	95000

Hints

1. Use UNION ALL to append multiple country-specific employee tables into a single derived table (CTE or subquery).
2. Include a currency column in the unified employee dataset so you can join to exchange_rates.
3. Convert each salary to USD using salary * usd_rate.
4. Order by the converted salary in descending order and return only the first 10 rows (e.g., using LIMIT 10 or an equivalent clause).