Question 49: Which of the queries return a result like the one below?

(Find all)

+--------------------+--------+-----------+-----------+--------+-----------+
| table_schema       | tables | myisam_tb | innodb_tb | mem_tb | dbsize_mb |
+--------------------+--------+-----------+-----------+--------+-----------+
| information_schema |     17 |         4 |         0 |     13 |         0 |
| mysql              |     17 |        17 |         0 |      0 |         0 |
| test               |      4 |         4 |         0 |      0 |         0 |
| tt                 |     49 |        45 |         3 |      0 |        53 |
| tt2                |     10 |        10 |         0 |      0 |         0 |
+--------------------+--------+-----------+-----------+--------+-----------+
5 rows in set (0.14 sec)

a)
select table_schema,count(*) as tables,
  sum(if(engine='myisam',1,0)) as myisam_tb,
  sum(if(engine='innodb',1,0)) as innodb_tb,
  sum(if(engine='memory',1,0)) as mem_tb,
  floor((sum(index_length)+sum(data_length))/1024/1024) as dbsize_mb
from information_schema.tables group by table_schema;

b)
select table_schema,engine,count(*) as tables,
  floor((sum(index_length)+sum(data_length))/1024/1024) as dbsize_mb
from information_schema.tables group by table_schema,engine;

c)
select v1.table_schema,
  ifnull(v1.tables,0) as tables,ifnull(v2.myisam_tb,0) as myisam_tb,
  ifnull(v3.innodb_tb,0) as innodb_tb,ifnull(v4.mem_tb,0) as mem_tb,
  ifnull(v1.dbsize_mb,0) as dbsize_mb
from (select table_schema,count(*) as tables,
  floor((sum(index_length)+sum(data_length))/1024/1024) as dbsize_mb
  from information_schema.tables group by table_schema) v1
left join (select table_schema,count(*) as myisam_tb
  from information_schema.tables
  where engine='myisam' group by table_schema) v2
on (v1.table_schema=v2.table_schema)
left join (select table_schema,count(*) as innodb_tb
  from information_schema.tables
  where engine='innodb' group by table_schema) v3
on (v1.table_schema=v3.table_schema)
left join (select table_schema,count(*) as mem_tb
  from information_schema.tables
  where engine='memory' group by table_schema) v4
on (v1.table_schema=v4.table_schema) ;
 

 
[ Obtaining Database Metadata (10%) - Using INFORMATION_SCHEMA to Obtain Metadata ]