microsoft · sagebree · Apr 23, 2026 · Mar 13, 2026 · Mar 13, 2026 · Mar 21, 2026
@@ -272,6 +272,14 @@ client.dataframe.update("account", df, id_column="accountid", clear_nulls=True)
 
 # Delete records by passing a Series of GUIDs
 client.dataframe.delete("account", new_accounts["accountid"])
+
+# SQL query directly to DataFrame (supports JOINs, aggregates, GROUP BY)
+df = client.dataframe.sql(
+    "SELECT a.name, COUNT(c.contactid) as contacts "
+    "FROM account a "
+    "JOIN contact c ON a.accountid = c.parentcustomerid "
+    "GROUP BY a.name"
+)
 ```
 
 ### Query data
@@ -375,19 +383,65 @@ results = (client.query.builder("account")
            .execute())
 ```
 
-**SQL queries** provide an alternative read-only query syntax:
+**SQL queries** provide an alternative read-only query syntax with support for
+JOINs, aggregates, GROUP BY, DISTINCT, and OFFSET FETCH pagination:
 
 ```python
+# Basic query
 results = client.query.sql(
     "SELECT TOP 10 accountid, name FROM account WHERE statecode = 0"
 )
-for record in results:
-    print(record["name"])
+
+# JOINs and aggregates work
+results = client.query.sql(
+    "SELECT a.name, COUNT(c.contactid) as cnt "
+    "FROM account a "
+    "JOIN contact c ON a.accountid = c.parentcustomerid "
+    "GROUP BY a.name"
+)
+
+# SQL results directly as a DataFrame
+df = client.dataframe.sql(
+    "SELECT name, revenue FROM account ORDER BY revenue DESC"
+)
+
+# SQL helpers: discover columns and JOINs from metadata
+cols = client.query.sql_select("account")  # "accountid, name, revenue, ..."
+join = client.query.sql_join("contact", "account", from_alias="c", to_alias="a")
+# Returns: "JOIN account a ON c.parentcustomerid = a.accountid"
+
+# Build queries using helpers -- no OData knowledge needed
+sql = f"SELECT TOP 10 c.fullname, a.name FROM contact c {join}"
+df = client.dataframe.sql(sql)
+
+# Discover all possible JOINs from a table (including polymorphic)
+joins = client.query.sql_joins("opportunity")
+for j in joins:
+    print(f"{j['column']:30s} -> {j['target']}.{j['target_pk']}")
 ```
 
-**Raw OData queries** are available via `records.get()` for cases where you need direct control over the OData filter string:
+**Raw OData queries** are available via `records.get()` for cases where you need direct control over the OData filter string. The SDK provides helpers to eliminate the most error-prone parts:
 
 ```python
+# Discover columns for $select (returns list ready for select= parameter)
+cols = client.query.odata_select("account")
+for page in client.records.get("account", select=cols, top=10):
+    ...
+
+# Discover $expand navigation properties (auto-resolves PascalCase names)
+nav = client.query.odata_expand("contact", "account")
+# Returns: "parentcustomerid_account"
+for page in client.records.get("contact", select=["fullname"], expand=[nav], top=5):
+    for r in page:
+        acct = r.get(nav) or {}
+        print(f"{r['fullname']} -> {acct.get('name')}")
+
+# Build @odata.bind for lookup fields (no manual name construction)
+bind = client.query.odata_bind("contact", "account", account_id)
+# Returns: {"parentcustomerid_account@odata.bind": "/accounts(guid)"}
+client.records.create("contact", {"firstname": "Jane", **bind})
+
+# Raw OData query with manual parameters
 for page in client.records.get(
     "account",
     select=["name"],
@@ -437,6 +491,18 @@ client.tables.add_columns("new_Product", {"new_Category": "string"})
 # Remove columns
 client.tables.remove_columns("new_Product", ["new_Category"])
 
+# List all columns (attributes) for a table to discover schema
+columns = client.tables.list_columns("account")
+for col in columns:
+    print(f"{col['LogicalName']} ({col.get('AttributeType')})")
+
+# List only specific properties
+columns = client.tables.list_columns(
+    "account",
+    select=["LogicalName", "SchemaName", "AttributeType"],
+    filter="AttributeType eq 'String'",
+)
+
 # Clean up
 client.tables.delete("new_Product")
 ```
@@ -489,6 +555,16 @@ rel = client.tables.get_relationship("new_Department_Employee")
 if rel:
     print(f"Found: {rel['SchemaName']}")
 
+# List all relationships
+rels = client.tables.list_relationships()
+for rel in rels:
+    print(f"{rel['SchemaName']} ({rel.get('@odata.type')})")
+
+# List relationships for a specific table (one-to-many + many-to-one + many-to-many)
+account_rels = client.tables.list_table_relationships("account")
+for rel in account_rels:
+    print(f"{rel['SchemaName']} -> {rel.get('@odata.type')}")
+
 # Delete a relationship
 client.tables.delete_relationship(result['relationship_id'])
 ```

@@ -40,6 +40,18 @@ Deep-dive into production-ready patterns and specialized functionality:
   - Column metadata management and multi-language support  
   - Interactive cleanup and best practices
 
+- **`sql_examples.py`** - **SQL QUERY END-TO-END** 🔍
+  - Schema discovery before writing SQL (list_columns, list_relationships)
+  - Full SQL capabilities: SELECT, WHERE, TOP, ORDER BY, LIKE, IN, BETWEEN
+  - JOINs (INNER, LEFT, multi-table), GROUP BY, DISTINCT, aggregates
+  - OFFSET FETCH for server-side pagination
+  - Polymorphic lookups via SQL (ownerid, customerid, createdby)
+  - SQL read -> DataFrame transform -> SDK write-back (full round-trip)
+  - SQL-driven bulk create, update, and delete patterns
+  - SQL to DataFrame via `client.dataframe.sql()`
+  - Limitations with SDK fallbacks (writes, subqueries, functions)
+  - Complete reference table: SQL vs SDK method mapping
+
 - **`file_upload.py`** - **FILE OPERATIONS** 📎
   - File upload to Dataverse file columns with chunking
   - Advanced file handling patterns
@@ -68,13 +80,17 @@ python examples/basic/functional_testing.py
 ```bash
 # Comprehensive walkthrough with production patterns
 python examples/advanced/walkthrough.py
+
+# SQL queries end-to-end with SDK fallbacks for unsupported operations
+python examples/advanced/sql_examples.py
 ```
 
 ## 🎯 Quick Start Recommendations
 
 - **New to the SDK?** → Start with `examples/basic/installation_example.py`
 - **Need to test/validate?** → Use `examples/basic/functional_testing.py`  
 - **Want to see all features?** → Run `examples/advanced/walkthrough.py`
+- **Using SQL queries?** → Run `examples/advanced/sql_examples.py`
 - **Building production apps?** → Study patterns in `examples/advanced/walkthrough.py`
 
 ## 📋 Prerequisites

@@ -42,7 +42,7 @@ def delete_relationship_if_exists(client, schema_name):
     """Delete a relationship by schema name if it exists."""
     rel = client.tables.get_relationship(schema_name)
     if rel:
-        rel_id = rel.get("MetadataId")
+        rel_id = rel.relationship_id
         if rel_id:
             client.tables.delete_relationship(rel_id)
             print(f"   (Cleaned up existing relationship: {schema_name})")
@@ -236,11 +236,11 @@ def _run_example(client):
         )
     )
 
-    print(f"[OK] Created relationship: {result['relationship_schema_name']}")
-    print(f"  Lookup field: {result['lookup_schema_name']}")
-    print(f"  Relationship ID: {result['relationship_id']}")
+    print(f"[OK] Created relationship: {result.relationship_schema_name}")
+    print(f"  Lookup field: {result.lookup_schema_name}")
+    print(f"  Relationship ID: {result.relationship_id}")
 
-    rel_id_1 = result["relationship_id"]
+    rel_id_1 = result.relationship_id
 
     # ============================================================================
     # 5. CREATE LOOKUP FIELD (Convenience Method)
@@ -265,10 +265,10 @@ def _run_example(client):
         )
     )
 
-    print(f"[OK] Created lookup using convenience method: {result2['lookup_schema_name']}")
-    print(f"  Relationship: {result2['relationship_schema_name']}")
+    print(f"[OK] Created lookup using convenience method: {result2.lookup_schema_name}")
+    print(f"  Relationship: {result2.relationship_schema_name}")
 
-    rel_id_2 = result2["relationship_id"]
+    rel_id_2 = result2.relationship_id
 
     # ============================================================================
     # 6. CREATE MANY-TO-MANY RELATIONSHIP
@@ -292,10 +292,10 @@ def _run_example(client):
         )
     )
 
-    print(f"[OK] Created M:N relationship: {result3['relationship_schema_name']}")
-    print(f"  Relationship ID: {result3['relationship_id']}")
+    print(f"[OK] Created M:N relationship: {result3.relationship_schema_name}")
+    print(f"  Relationship ID: {result3.relationship_id}")
 
-    rel_id_3 = result3["relationship_id"]
+    rel_id_3 = result3.relationship_id
 
     # ============================================================================
     # 7. QUERY RELATIONSHIP METADATA
@@ -308,21 +308,21 @@ def _run_example(client):
 
     rel_metadata = client.tables.get_relationship("new_Department_Employee")
     if rel_metadata:
-        print(f"[OK] Found relationship: {rel_metadata.get('SchemaName')}")
-        print(f"  Type: {rel_metadata.get('@odata.type')}")
-        print(f"  Referenced Entity: {rel_metadata.get('ReferencedEntity')}")
-        print(f"  Referencing Entity: {rel_metadata.get('ReferencingEntity')}")
+        print(f"[OK] Found relationship: {rel_metadata.relationship_schema_name}")
+        print(f"  Type: {rel_metadata.relationship_type}")
+        print(f"  Referenced Entity: {rel_metadata.referenced_entity}")
+        print(f"  Referencing Entity: {rel_metadata.referencing_entity}")
     else:
         print("  Relationship not found")
 
     log_call("Retrieving M:N relationship by schema name")
 
     m2m_metadata = client.tables.get_relationship("new_employee_project")
     if m2m_metadata:
-        print(f"[OK] Found relationship: {m2m_metadata.get('SchemaName')}")
-        print(f"  Type: {m2m_metadata.get('@odata.type')}")
-        print(f"  Entity 1: {m2m_metadata.get('Entity1LogicalName')}")
-        print(f"  Entity 2: {m2m_metadata.get('Entity2LogicalName')}")
+        print(f"[OK] Found relationship: {m2m_metadata.relationship_schema_name}")
+        print(f"  Type: {m2m_metadata.relationship_type}")
+        print(f"  Entity 1: {m2m_metadata.entity1_logical_name}")
+        print(f"  Entity 2: {m2m_metadata.entity2_logical_name}")
     else:
         print("  Relationship not found")