schema-exploration

Files (1)

• SKILL.md

SKILL.md (first lines)

# Schema Exploration Skill

## When to Use This Skill

Use this skill when you need to:
- Understand the database structure
- Find which tables contain certain types of data
- Discover column names and data types
- Map relationships between tables
- Answer questions like "What tables are available?" or "What columns does the Customer table have?"

## 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 artists
2. Album - Music albums
3. Track - Individual songs
4. Genre - Music genres
5. MediaType - File formats (MP3, AAC, etc.)
6. Playlist - User-created playlists
7. PlaylistTrack - Tracks in playlists
8. Customer - Store customers
9. Employee - Store employees
10. Invoice - Customer purchases
11. 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:many
Album (ArtistId, AlbumId)
  ↓ 1:many
Track (AlbumId, TrackId)
  ↓ 1:many
InvoiceLine (TrackId, UnitPrice, Quantity)
```

**Response:**
```
To find revenue by artist, you'll need to:
1. Start with the Artist table
2. JOIN to Album on ArtistId
3. JOIN to Track on AlbumId
4. JOIN to InvoiceLine on TrackId
5. SUM the revenue (UnitPrice * Quantity)
6. GROUP BY artist name

This requires the query-writing skill to execute.
```

## Quality Guidelines

**For "list tables" questions:**

From bharathrajp14/myai, MIT.

More from bharathrajp14/myai