Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


Create Table A_TBL as
  Select * From 
(Select 1 "ID", 'A' "A_LETTER" From Dual Union All
 Select 2 "ID", 'B' "A_LETTER" From Dual Union All
 Select 3 "ID", 'C' "A_LETTER" From Dual);
3 rows affected

Create Table B_TBL as
  Select * From 
(Select 1 "ID", 'A' "A_LETTER" From Dual Union All
 Select 2 "ID", 'B' "A_LETTER" From Dual);
2 rows affected

Create Table C_TBL as
  Select * From 
(Select 1 "ID", 'A' "A_LETTER" From Dual Union All
 Select 2 "ID", 'B' "A_LETTER" From Dual Union All
 Select 3 "ID", 'C' "A_LETTER" From Dual Union All
 Select 4 "ID", 'D' "A_LETTER" From Dual);
4 rows affected

Select Case TABLE_NAME
            When 'A_TBL' Then 'Amsterdam'
            When 'B_TBL' Then 'Berlin'
            When 'C_TBL' Then 'California'
      End "CITY",
      NUM_ROWS "AMOUNT"
From user_tables 
Where TABLE_NAME IN('A_TBL', 'B_TBL', 'C_TBL')
UNION ALL
Select 'TOTAL', Sum(NUM_ROWS)
From user_tables 
Where TABLE_NAME IN('A_TBL', 'B_TBL', 'C_TBL');


CITY
AMOUNT


Amsterdam
3

Berlin
2

California
4

TOTAL
9