1. Find the right table
The previous chapter focused on using SQL functions to answer real world questions using the data stored in your SQL tables. In these exercises you knew exactly which tables to use to get the data you need.
2. Where is your data?
But, what if you didn't know which table contains the data you need?
What if you didn't even know which tables exist in your database?
This isn't an uncommon scenario when working with large institutional databases and learning how to find the data you need can be as important as knowing how to manipulate it.
As such, this chapter is dedicated to the tools you can use to find the data that you need.
3. What table should I use?
Let's begin with a simple scenario. You have a list of tables which may contain the data you need but you need to figure out which you should use. Ultimately, this translates to two questions that can help you figure which table you need:
What column names are in your table of interest? and
what do these columns contain that is relevant to your question.
The simplest way to answer this question is to run a simple query selecting all of the columns for a given table one at a time until you've found the one that you need.
4. LIMIT your results
In practice, this simple step can be exceedingly slow if you have a large number of rows in your table. Often times you only need a few rows to figure out if the table you're exploring is the one you need.
5. LIMIT your results
As such, the LIMIT command becomes indispensable. Rather than taxing your machine and database server to return all rows, simply make it a discipline to always use limit when quickly exploring your tables of interest.
6. What tables are in my database?
What happens if you don't actually know what tables exist in your database?
Well, the good news is that it is possible to query the list of tables in a database by querying a specific system table.
In this course, we are using Postgres so we need to query the system table called pg_catalog-pg_tables.
This returns the list of all tables for all schemas in our database along with various meta information about these tables.
7. What tables are in my database?
If you know the schema that your data is stored in you may want to filter for it. This is especially useful to avoid returning a long list of system tables which wouldn't likely be helpful for your data search.
In our database, all of the data is stored in the schema called public which we can filter like so.
8. What tables are in my database?
This tool isn't limited to Postgres and can easily be used on other database systems.
For instance, in SQL Server databases the system table you would use is called information_schema-tables.
Or for MySQL databases the command is simply SHOW TABLES.
Whatever system you're working with, the command to list your table catalog is a quick web search away.
9. Find the tables you need!
Let's practice using the new tools you've learned to find the right tables to answer your data questions.