SQL: Use GitHub Copilot to write SQL Automatically!

hello let's take a quick look at using GitHub copilot with SQL let's start by finding out what databases are in the current instance show the database names in the current instance select name it's given me a query here we execute that query it shows me that there is a single user database because it's automatically put this where database ID is greater than four in SQL Server the first um for zero index database names are system databases so this is this is the right query so next I want to see what tables are in the city bike database show the database names in the current show the table names in the City Bank database that looks pretty good okay I've got four four tables one of them is called trip data that's the one I'm interested in but I wanted to use information schema for the next query so using information schema show the column names in the trip data table looks pretty good okay I've got my column names but actually I want to show the data type as well just modify it quickly with natural language okay so these are columns in the trip data table I've got start station and end station with latitude and longitude I'd like to write a query that calculates the distance between these two stations let's see if copilot can do it for us so let's write a query to return the following it's better if we're specific with this kind of thing so let's say start station name and in station name and Geographic distance between start and end stations I've learned that it's important to put the word Geographic if you're using this kind of calculation otherwise it will try to use more complex math which isn't necessarily that that efficient so I want the geographic distance between start and end station let me show you what happens if I just do it that way it gives me this but you can see that it has tried to guess where the latitude and longitude should be taken from that's actually not right so let's be more specific and say the locations of the stations are in start lat and you can see that it's Auto completed but it's used l-o-n instead of LNG so if I just change that let's see what we get now okay that's much better you can see that the calculation is using start that and start long and it's used direct the correct functions to calculate the distance let's uh execute this and see what we get we've got the right calculations but we've also got this error geography Point failed because parameter one is not allowed to be null so probably means that start that or over here end lat there's a row with a null um in the column so we don't really want that as we can't calculate that distance anyway so let's try exclude rows where the permanence is on null delete that stuff and try again press tab tab oh look at that it has actually built the where clause for me I'm just going to format that so you can see it better you can see that it's built the where clause and the calculation is the same it's using the correct columns and now if I execute this is taking a lot longer and it's taking a lot longer because I forgot to put the limiting Clause so let's stop that using t-sql syntax return the top 100 rows only right now let's put top 100 which is which is much better again format I really should learn the keyboard shortcut um that's the right query it's got top 100 because it's SQL Server let's execute that and it has worked that is the top 100 distances by trip between these two stations and that is an example of using GitHub co-pilot to write SQL enjoy

Channel: Flex Your Data

Views: 3,139

Keywords:

Id: 2ZGaBXhaHBQ

Length: 4min 51sec (291 seconds)

Published: Sun Jul 09 2023