From the course: Text to SQL: Amazon Redshift Serverless for Generative SQL in Amazon Q

Filtering data

From the course: Text to SQL: Amazon Redshift Serverless for Generative SQL in Amazon Q

Start my 1-month free trial Buy for my team

Filtering data

“

- [Instructor] In this movie, we'll be learning how to design a prompt in order to filter our data and ask the right questions, so we'll start with a first example, so we'll ask a question of Amazon Q. So we'll ask, what are the first name, last name, and age of students with an attendance pattern of less than 75% from all departments, except business. So from my experience in high education as a lecturer, this question really intrigues me from the high education dataset, so I wanted to be able to ask Q to provide the first name, the last name, and the age of students with an attendance pattern of less than 75% from all departments, except business. So Q will take a few seconds to generate our response, and we can see that Q has extracted three columns, it's also retrieved the right information from the correct table, which is student performance, student grading. And we'll add this code to our markdown notebook, and we'll run the code. So you can see that using plain English, Q keeps on learning and improving the response. And it was so quick, so we don't need to generate any code at all. And it's provided a list of names of students with an attendance rate of less than 75% with their last name and their age. And I also want to follow up with more information as well. I also want to include additional column for attendance, so please also include the column attendance percentage in the result set. And so I'm looking for four columns, and providing more context to improve the answer, so we can see that we have additional column attendance. And we will add this to a notebook, and then we can click on run. So I want to be able to see my students, for example, and then also the attendance pattern. So we can see Omar Williams has attendance pattern of 52%, his generation is Z. And also we have students, so Liam Williams, who has a stronger attendance pattern as well, so we can have a look at their age as well, so this would be really useful for teachers in higher education. And we'll also work on another example as well to filter our data, so I'll ask another question. So please give me the location and code of the most prestigious institution in the world. So we're just asking natural language and having a really good conversation with Q, so hopefully Q will understand my question. So it's provided information regarding location and location code. And we'll add this to our notebook and we'll run the SQL code. So that was the error, so I want to have more than one result, so I'll just run this again, so you can see that we have an error. And Q doesn't quite understand my question, so I'll just paste this error back into the chat interface, and I'll click into generate. And I want Q to analyze the error as well, okay? So it's going to improve the code. And this time, we'll try this again, we'll add it to the notebook. And so I'm not looking for one result, so I'm going to regenerate the code because I want more than one result, okay? So I'll provide more context. Please generate the top 20 results, okay? So now, this is a improved response. And we can add this code to our markdown notebook, and then we'll run the code. And we can see from the results set, the most prestigious universities around the world includes locations, such as Europe, United Kingdom, which includes Oxford University, Cambridge, Stanford in the US, and also Singapore; we have National University of Singapore, and we also have Zurich as well, which is based in Switzerland. So now, we can always ask more questions to improve our results set. And let's have a look at another example as well. Let's see if we can filter our data. Let's ask one more question. So what is the average PhD overall pay for computer engineering around the world? So I want to be as specific as possible. Please list the major, and also the university, and also the stipend, so I want to provide as much detail and in descending order. So my question is, I would like Q to find out what is the average PhD overall pay for computer engineering around the world? And I also want to list the major, the university, the stipend in descending order. So once we are ready to generate the response, let's click on return, okay? And we have code, so it's provided three columns to write the answers to our questions, so it's provided the department, the university, and the overall pay as well. And we can add this code to our markdown notebook and then we will run this code. So we can see that Q has provided code to provide a filter using the where clause for computer engineering, and it's provided our results set in descending order for the average stipend. So if we were studying computer engineering in Princeton University, it will be about $50,400. And we also have a stipend from Cornell, which is 44,000. And scroll down to the bottom, for University of California, it's 33,000, and Virginia Tech, 32,000.

Contents