SO Q# 45510313, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

Drop table if exists countries;
Drop table if exists states ;
Drop table if exists cities;

create table countries (
    id int,
    name varchar(30));

create table states (
    id int,
    name varchar(30),
    country_ID int);

create table cities (
    id int,
    name varchar(30),
    state_ID int);

Insert into countries values  (1,'USA');
Insert into countries values (2,'INDIA');
Insert into countries values (3,'CANADA');

Insert into states values (1,'Illinois',1);
Insert into states values (2,'New York,',1);
Insert into states values (3,'California',1);

Insert into states values (4,'Assam (Dispur)',2);
Insert into states values (5,'Bihar (Patna)',2);
Insert into states values (6,'Chhattisgarh (Raipur)',2);

Insert into states values (7,'British Columbia',3);
Insert into states values (8,'Alberta',3);

Insert into cities values (1,'Chicago',1);
Insert into cities values (2,'Springfield',1);
Insert into cities values (3,'Peoria',1);

Insert into cities values (4,'New York',2);
Insert into cities values (5,'Buffalo',2);

Insert into cities values (6,'LA',3);
Insert into cities values (7,'San Fransisco',3);
Insert into cities values (8,'Oakland',3);
Insert into cities values (9,'San Deigo',3);

Insert into cities values (10,'City 1',4);
Insert into cities values (11,'City 2',4);
Insert into cities values (12,'City 3',4);
Insert into cities values (13,'City 4',5);
Insert into cities values (14,'City 5',5);
Insert into cities values (15,'City 6',5);
Insert into cities values (16,'City 7',6);
Insert into cities values (17,'City 8',6);
Insert into cities values (18,'City 9',6);

Insert into cities values (19,'City 10',7);
Insert into cities values (20,'City 11',7);
Insert into cities values (21,'City 12',7);
Insert into cities values (22,'City 13',8);
Insert into cities values (23,'City 15',8);
Insert into cities values (24,'City 15',8);

select c.name, count(s.name) as statecount,  count(ci.name)
 from countries c,states s,cities ci
  where ci.state_id = s.id 
  and s.country_id = c.id 
  group by c.name;
  
  
SELECT c.name, count(distinct s.name) as statecount,  count(Ci.name) as CityCount
FROM countries c
INNER JOIN states s 
  on c.id = s.country_ID
INNER JOIN cities ci
  ON s.id = ci.state_id
GROUP BY C.name


absolute service time: 0,4 sec