1

How does one write & run queries against a database/warehouse if they have no knowledge of how the data is structured?

By "structure" I mean table names, fields, etc. Unless I have table names, how can I possibly query something?

clearly, I don't know much about databases, but this part especially confuses me.

5

There's standard system tables that will tell you the names of the custom user-defined tables and their columns and types (metadata packaged as well-known tables & columns).  And this is how some abstract database user interfaces, management tools, and debugging tools can be made to work against any (SQL, user-defined) database that they have never seen.

However, you would almost certainly need to drive those tools with a human who can make sense of what they see.

A normal application would know the tables and columns already, by a schema, which defines what entities are stored and what relationships are between them.  Often the application (and its requirements) dictate the schema, which the database follows, and the application is programmed to be aware of.

2

I'd need to know better the situation in which you had to do that, otherwise my answer may not be what you're looking for.

In general you know the structure; either you created the application and thus the tables and obviously then you know their structure, or the person who created it documented to you, or provided a script to create the tables that you can read.

If you fell from the sky and neither of these conditions apply, you must at least know which database you're connecting to and have a command-line client to it. Then check the documentation for that specific database to discover how to inspect user-created tables. For instance, in MySQL/MariaDB, this will list the existing tables:

show tables;

And this will print the statement that created a particular table:

show create table <tablename>;
0

How does one write & run queries against a database/warehouse if they have no knowledge of how the data is structured?

Short answer - you can't.

It's like asking how you can do exploratory surgery on someone, but you know nothing about Anatomy.
OK, you can do slightly less damage in a database, but the principle is the same.

You have to find out what's in there before you can [usefully] start working with it.

How to find that depends on the DBMS but, luckily, most of them support standardised metadata, held in tables within "information_schema".

select * from information_schema.tables; 
select * from information_schema.columns; 

.. and many others.

If you're really lucky, there will be constraints between the tables - these relationships can be priceless in working out how one thing relates to another. There are even tools that will pull this information and graph out the tables and how they join together.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.