Sqlite display table with informations about the databas

[giovanni.srg]

They are asking me to write a query that display the following table:

• Select each table as a string
• Select the number of attributes as an integer (count the number of attributes per table).
• Select the number of rows using the COUNT(*) function
• Use the compound-operator UNION ALL to bind these rows together.

How do I do that?

I knew that for getting the name of each table I have to use

SELECT name AS table_name
  FROM sqlite_schema
 WHERE type = 'table'

but the rest is a mistery to me. Would you help me?

Thank you!!!

Here you can find the link for the database

[giovanni.srg]

@Elena_Kosourova
I know that you’re good at sql

[Elena_Kosourova]

Hi Giovanni,

Oh, it seems that this time I don’t know the exact answer, you over-estimated me! Anyway, I guess that for the number of attributes, you can find helpful the PRAGMA table_info(). Take a look at this resource, for example. About counting rows, it should be somehow simple, but I don’t know, honestly. And as for UNION ALL, just put it between each of the 3 queries to combine the results into one table.

By the way, is your task for SQLite or for another SQL flavor?

[giovanni.srg]

Thank you! You helped a lot!!!