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?
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.
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.
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!