Sqlite display table with informations about the databas

Q&A DQ Courses
#1

They are asking me to write a query that display the following table:
sqlite 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

#2

@Elena_Kosourova
I know that you’re good at sql
:wink:

1 Like
#3

Hi Giovanni,

Oh, it seems that this time I don’t know the exact answer, you over-estimated me! :sweat_smile: 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?

1 Like
#4

Thank you! You helped a lot!!!

1 Like
#5

I know this is a couple of months old but I really struggled with completing this and hoping what I found will help others who might be searching for help.

I created a separate query for each table in the database using WHERE sch.name = X with X being the specific table. Elena’s resource was very helpful for getting the count of attributes but to count the number of rows, I created a COUNT(*) subquery that referenced the table in question. Each query created a row for the individual tables so I put UNION ALL between them to combine all rows into a single table.

2 Likes
#6

I also came to this conclusion! And was REALLY stumped at first. It was not very satisfying to see that the DQ solution had the # of columns hard coded in, and I’m still not 100% on having to know the name of each table. Ideally I would want this mini script to be dynamic and reusable for future dbs. I wish I understood recursion better because I feel like it would be useful in this situation, iterating over tables, but maybe that’s where some sort of cursor would come into play.

1 Like
#7

Hi Julia,

Could you please send your feedback:

It was not very satisfying to see that the DQ solution had the # of columns hard coded in, and I’m still not 100% on having to know the name of each table. Ideally I would want this mini script to be dynamic and reusable for future dbs.

to the Technical Support Team of Dataquest? Just click the ? button in the upper-right corner of the platform screen where you detected the issue, describe the problem and click Send feedback. Thanks a lot for making Dataquest a better place to learn! :heavy_heart_exclamation:

1 Like