Schema Exploration

Schema Exploration Skill

Workflow

1. List All Tables

Use sql_db_list_tables tool to see all available tables in the database.

This returns the complete list of tables you can query.

2. Get Schema for Specific Tables

Use sql_db_schema tool with table names to examine:

• Column names - What fields are available
• Data types - INTEGER, TEXT, DATETIME, etc.
• Sample data - 3 example rows to understand content
• Primary keys - Unique identifiers for rows
• Foreign keys - Relationships to other tables

3. Map Relationships

Identify how tables connect:

• Look for columns ending in "Id" (e.g., CustomerId, ArtistId)
• Foreign keys link to primary keys in other tables
• Document parent-child relationships

4. Answer the Question

Provide clear information about:

• Available tables and their purpose
• Column names and what they contain
• How tables relate to each other
• Sample data to illustrate content

Example: "What tables are available?"

Step 1: Use sql_db_list_tables

Response:

The Chinook database has 11 tables:1. Artist - Music artists2. Album - Music albums3. Track - Individual songs4. Genre - Music genres5. MediaType - File formats (MP3, AAC, etc.)6. Playlist - User-created playlists7. PlaylistTrack - Tracks in playlists8. Customer - Store customers9. Employee - Store employees10. Invoice - Customer purchases11. InvoiceLine - Individual items in invoices

Example: "What columns does the Customer table have?"

Step 1: Use sql_db_schema with table name "Customer"

Response:

The Customer table has these columns:
- CustomerId (INTEGER) - Primary key, unique customer ID- FirstName (NVARCHAR) - Customer's first name- LastName (NVARCHAR) - Customer's last name- Company (NVARCHAR) - Company name (optional)- Address, City, State, Country, PostalCode - Location fields- Phone (NVARCHAR) - Phone number- Fax (NVARCHAR) - Fax number- Email (NVARCHAR) - Email address- SupportRepId (INTEGER) - Foreign key to Employee table
Sample data shows customers like:- Luís Gonçalves from Brazil- Leonie Köhler from Germany- François Tremblay from Canada

Example: "How do I find revenue by artist?"

Step 1: Identify tables needed

• Artist (has artist names)
• Album (links artists to tracks)
• Track (links albums to sales)
• InvoiceLine (has sales data)
• Invoice (has revenue totals)

Step 2: Map relationships

Artist (ArtistId)  ↓ 1:manyAlbum (ArtistId, AlbumId)  ↓ 1:manyTrack (AlbumId, TrackId)  ↓ 1:manyInvoiceLine (TrackId, UnitPrice, Quantity)

Response:

To find revenue by artist, you'll need to:1. Start with the Artist table2. JOIN to Album on ArtistId3. JOIN to Track on AlbumId4. JOIN to InvoiceLine on TrackId5. SUM the revenue (UnitPrice * Quantity)6. GROUP BY artist name
This requires the query-writing skill to execute.

Quality Guidelines

For "list tables" questions:

• Show all table names
• Add brief descriptions of what each contains
• Group related tables (e.g., music catalog, transactions, people)

For "describe table" questions:

• List all columns with data types
• Explain what each column contains
• Show sample data for context
• Note primary and foreign keys
• Explain relationships to other tables

For "how do I query X" questions:

• Identify required tables
• Map the JOIN path
• Explain the relationship chain
• Suggest next steps (use query-writing skill)