CREATE DATABASE debug;
USE debug;

CREATE TABLE `currency` (
  `currency_id` char(3) NOT NULL,
  `num_of_decimal` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO currency (currency_id, num_of_decimal) VALUES ('NOK', 2), ('USD', 2), ('JPY', 0);

CREATE TABLE `exchange_rate` (
  `source_currency` char(3)       NOT NULL,
  `target_currency` char(3)       NOT NULL,
  `exchange_rate`   decimal(16,8) NOT NULL,
  PRIMARY KEY (`source_currency`,`target_currency`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO exchange_rate (source_currency, target_currency, exchange_rate) VALUES ('NOK', 'USD', 9.12345678), ('NOK', 'JPY', 9.12345678);

SELECT
  ROUND((100 / exchange_rate), c.num_of_decimal),
  target_currency,
  num_of_decimal
FROM exchange_rate er
INNER JOIN currency c ON er.target_currency = c.currency_id
WHERE source_currency = 'NOK';

SELECT
  ROUND((100 / exchange_rate), 0),
  target_currency,
  num_of_decimal
FROM exchange_rate er
INNER JOIN currency c ON er.target_currency = c.currency_id
WHERE source_currency = 'NOK';

SELECT
  ROUND((100 / exchange_rate), 2),
  target_currency,
  num_of_decimal
FROM exchange_rate er
INNER JOIN currency c ON er.target_currency = c.currency_id
WHERE source_currency = 'NOK';

SELECT
  ROUND((100 / exchange_rate), 0),
  target_currency,
  num_of_decimal
FROM exchange_rate er
INNER JOIN currency c ON er.target_currency = c.currency_id
WHERE source_currency = 'NOK' AND target_currency = 'JPY'
UNION ALL
SELECT
  ROUND((100 / exchange_rate), 2),
  target_currency,
  num_of_decimal
FROM exchange_rate er
INNER JOIN currency c ON er.target_currency = c.currency_id
WHERE source_currency = 'NOK' AND target_currency = 'USD';

SELECT
  ROUND(
   (100 / exchange_rate),
   (SELECT num_of_decimal FROM currency WHERE currency_id = 'USD')
   ),
   target_currency
FROM exchange_rate er
WHERE source_currency = 'NOK'
  AND target_currency = 'USD';

SELECT 
  ROUND(
    (100 / exchange_rate),
    (SELECT num_of_decimal FROM currency WHERE currency_id = 'JPY')
  ),
  target_currency
FROM exchange_rate er
WHERE source_currency = 'NOK'
  AND target_currency = 'JPY';

SELECT
  ROUND(
    (100 / exchange_rate),
    (SELECT num_of_decimal FROM currency WHERE currency_id = 'JPY')
  ),
  target_currency
FROM exchange_rate er
WHERE source_currency = 'NOK'
  AND target_currency = 'JPY'
UNION ALL
SELECT
  ROUND(
    (100 / exchange_rate),
    (SELECT num_of_decimal FROM currency WHERE currency_id = 'USD')
  ),
  target_currency
FROM exchange_rate er
WHERE source_currency = 'NOK'
  AND target_currency = 'USD';

