Purpose: In this lab, we’ll see some examples of how to have Copilot help with writing SQL
- Create a new file named dev.sql. You can create it via entering the line below in the terminal.
code dev.sql
- Afterwards this file should be open in a tab in the editor. Assume we want to work with a database or database definition that defines a dataset for students, staff, curriculums, courses, schools of study, locations, and registrations for a university system. Let's see what Copilot would generate for a query to get all students in a course - without any other context.
Enter the following comment below and press Tab to accept suggestions. Remember that you may have to hit Enter multiple times to get Copilot to prompt. Or if you don't get a suggestion or only get a comment, try "nudging" Copilot via typing "select".
-- define a select statement to get all students enrolled in a course
- Go ahead and save this file as part of the project. You can do this from the "3-line" menu under File->Save, or just click on the X next to the file's name in it's tab and select to Save it.
- If the file is no longer open in the tabs, you can select the "Explorer" icon at the top of the sidebar and select the file in the list to open it back up.
- Let's see if we get any different results if we provide Copilot additional context. Open the file create-tables.sql in the editor from the GitHub repository. (You can either select and open it from the file list or use the command below from the terminal.) Scroll through it and take a quick look at the contents.
code create-tables.sql
-
Now with that file open, go back up to the top of the dev.sql file. Highlight and delete the comment and resulting query from step 2.
-
Enter the same comment as before to request the query. (Basically, repeat step 2.) See what Copilot suggests this time. You can accept the suggestions or cycle through options. (If you first get a duplicate line as the query, just hit Enter and Copilot should start making more meaningful suggestions.)
-- define a select statement to get all students enrolled in a course
- If all goes well, this second pass should generate a query with many more specific references to the names and identifiers used in create-tables.sql. (If not, delete the result and try again.) Take a look at the query and then compare the names/identifiers used to the ones in the create-tables.sql file. This will show that Copilot picks up on context from other files available to it to make better suggestions. This is one of the key principles of Prompt Engineering - providing the right context to Copilot to get the best results. Remember that Copilot is context-aware and will provide better results with more context. Take some time now to learn more about Prompt Engineering, check out Prompt engineering for GitHub Copilot from the GitHub documentation.
- In some cases, we might be able to use a separate index to speed up operations. Let's ask Copilot to create a new index based on the last query. Add the following line after the current query in the file dev.sql.
-- write an index to improve the performance of the query
- Let's suppose we also want to have a table to capture student attendance. We can ask Copilot to create the table definition for us.
-- define a table for student attendance to capture attendance by class
(Here again, if you don't get a meaningful response from Copilot, you may need to nudge it by typing CREATE.) In the definition Copilot provided, it may have added a comment for the status in the same format as the comment in the courses.registration table definition in the create-tables.sql file.
- Copilot can also create stored procedures. Let's ask it to create a new stored procedure for getting a list of enrolled students at a particular location. Let's use the CMD+I shortcut. Go to the bottom of the dev.sql file, invoke Copilot Chat via the shortcut and then enter the line below in the dialog. You can choose to Accept or Discard the result.
define a stored procedure to get course enrollment by location
- We can be more prescriptive with our stored procedure definition. Let's add a more complex request. Go to the Chat interface extension via clicking on the icon on the tool bar (if its not already opened). In the Chat window, enter the prompt below.
define a stored procedure to get instructor details associated with a location
include instructor details, location details, and courses associated with the instructor
use instructor_id as the input parameter
- Finally, let's see Copilot optimize a query for us. Suppose we want to get all the course registrations for September, 2023. Enter the following query in the file.
select * from courses.registrations where year(registration_date) = 2023 and month(registration_date) = 9;
- Ask Copilot to optimize the previous query. You can do this via highlighting the query (make sure to highlight the entire query), switch to the separate chat interface and entering "optimize" in the dialog. You can Accept or Discard the suggested optimization after that.
optimize
