Tutorial: Using Amazon Q generative SQL capability with the TICKIT data

Example conversation with Amazon Q generative SQL

1. In the Editor, connect to a cluster or workgroup that contains the TICKIT sample data.

2. Create an empty notebook and choose the Generative SQL icon to open the chat panel.

3. Enter the following prompt to generate SQL to verify the number of records in the VENUE table:

   How many venues are there?

   SELECT
     COUNT(*) AS num_venues
   FROM
     tickit.venue

   Choose Add to notebook to add two cells to the open notebook. One Markdown cell "How many venues are there?" and one containing the generated SQL.

   In the SQL cell, choose Run to receive the result:

   count
   ------
   202	
   

4. To ask for another version of SQL, choose Regenerate SQL and receive the following answer:

   SELECT
     venuestate,
     COUNT(*) AS num_venues
   FROM
     tickit.venue
   GROUP BY
     venuestate

   Choose Add to notebook to add two more cells to the open notebook. One Markdown cell "How many venues are there?" and one containing the generated SQL.

   In the SQL cell, choose Run to receive the result listing venue state's by number:

   
   venuestate num_venues
   ----------------------
   MA         4	
   OH         8	
   MI         5	
   ...
   

5. The generative SQL assistant can make assumptions about what you're asking. Although there are no widgets in the database, you can still ask. In this case, it gives you a message that it was not able to generate SQL.

   How many widgets are there?

   I was not able to generate any SQL

6. As you learn more about your database, you can ask more specific questions to generate SQL. For example, you're aware that the database contains sales data for several months in 2008. Enter the following prompt to generate SQL to find the number of sales in November 2008:

   How many sales were there in November 2008?

   SELECT
     COUNT(*) AS num_sales
   FROM
     tickit.sales s
     JOIN tickit.date d ON s.dateid = d.dateid
   WHERE
     d.month = 'November'
     AND d.year = 2008

   Choose Add to notebook to add two cells to the open notebook. One Markdown cell "How many sales were there in November 2008?" and one containing the generated SQL.

   In the SQL cell, choose Run to receive the result:

   num_sales
   -----
   0	
   

   You realize this is not the result you were expecting.

7. You notice that the predicate date.month='November' expects the month in the DATE table to be represented by an abbreviation of month name. You change the predicate to d.month='NOV' and rerun the SQL.

   SELECT
     COUNT(*)
   FROM
     sales
     JOIN date ON sales.dateid = date.dateid
   WHERE
     date.month = 'NOV'
     AND date.year = 2008

   In the SQL cell, choose Run to get new results.

   count
   -----
   14261	
   

8. If you ask a question that tries to change the connected database, a warning message is returned along with any recommended SQL. Enter the following prompt to generate SQL to inset data into a table:

   Insert 1 into the venue table.

   INSERT
   ,
   UPDATE
     OR delete data
   FROM
     the database AS that could potentially change the data.Please provide a query that ONLY selects data

   
   I was not able to generate the correct SQL code. I generated SQL, but you'll have to edit it to work with your database.

   If you choose Add to notebook to add two cells to the open notebook and run the SQL, then the SQL fails.

   
   ERROR: syntax error at or near "," Position: 132 [ErrorId: 1-6546764a-011df2691778846219ce6ec2]