Introduction
One of the best ways to up a developer’s skills is to get a subscription for GitHub Copilot which can assist with writing better code and to ask difficult questions.
Copilot pricing is affordable at $10 USD per month is a low cost tool for assisting in many facets of coding which will pay for itself usually in less than a day of coding.
✔️ GitHub Copilot can also assist with writing SQL.
✔️ Best done with known databases like Microsoft NorthWind
Example 1
A novice developer learn to work with SQL is using Microsoft NorthWind database and wants to create a stored procedure for getting all products for a specific order.
Consider using my version of NorthWind database.
In Visual Studio, create a new file named test.sql, press ALT + / to bring up Copilot window and enter.
using microsoft NorthWind database create a select statement to get all products for an order as a stored procedure
Click the paper airplane and Copilot returns the following, click Accept.
Well that did not work as there is no OrderId in the Product table. Let's try again by indicating that we are getting information from OrderDetails table.
using microsoft NorthWind database create a select statement to get all products for an order in the OrderDetails table as a stored procedure
CREATE PROCEDURE GetProductsForOrder
@OrderID INT
AS
BEGIN
-- Select all products for the given order
SELECT p.ProductName, od.Quantity, od.UnitPrice
FROM OrderDetail od
INNER JOIN Products p ON od.ProductID = p.ProductID
WHERE od.OrderID = @OrderID
END
Open SSMS (SQL-Server Management Studio) or Azure Data Studio to the NorthWind database, create a new query, drop the SQL from Copilot in and execute.
Or better, copy the SQL between the BEGIN and END into a new query window as shown below to test prior to creating the stored procedure to ensure it works.
- I added a parameter which would in the stored procedure would be passed in.
- Adjust columns needed
DECLARE @OrderID AS INT = 10248;
SELECT p.ProductName,
od.Quantity,
od.UnitPrice
FROM dbo.OrderDetails od
INNER JOIN dbo.Products p
ON od.ProductID = p.ProductID
WHERE od.OrderID = @OrderID;
Execute and this data is returned.
Since in this case the results are good, go ahead and run the create SQL statement above and if not adjust as needed.
Note
When performing the above steps Copilot retrieved information from the web and if asking about a unknown database you created more likely than not will get the question wrong. Remember this is for learning so use known databases such as NorthWind and Chinook for instance.
Example 2
🛑 This example was a random pick from Stackoverflow
A great method to learn is traversing Stackoverflow but a developer when finding a question with an answer the developer must take time to create and populate a database which takes time.
Using the following question
How to concatenate text from multiple rows into a single text string in SQL Server
And a developer wants to try the accepted solution.
In Visual Studio, create a new file named test.sql and press ALT + /.
Enter the following:
Create a database for the following (add a space than paste in the following)
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
Click the paper airplane and Copilot produces the following.
CREATE DATABASE YourDatabaseName;
GO
USE YourDatabaseName;
GO
CREATE TABLE dbo.Students (
StudentID INT PRIMARY KEY,
StudentName NVARCHAR(50),
SubjectID INT
);
GO
INSERT INTO dbo.Students (StudentID, StudentName, SubjectID)
VALUES (1, 'John', 1),
(2, 'Jane', 1),
(3, 'Mike', 2),
(4, 'Emily', 2),
(5, 'David', 3),
(6, 'Sarah', 3);
GO
SELECT Main.SubjectID,
LEFT(Main.Students, LEN(Main.Students)-1) AS "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main];
The above will work but let's clean it up, get rid of the create database, instead use an existing database. Note the SQL has been reformatted from the original and at the top a statement has been added to delete the table if it exists which allows for experimentation.
IF OBJECT_ID(N'dbo.Students', N'U') IS NOT NULL
DROP TABLE [dbo].Students;
GO
CREATE TABLE dbo.Students (StudentID INT PRIMARY KEY,
StudentName NVARCHAR(50) NOT NULL,
SubjectID INT NOT NULL);
GO
INSERT INTO dbo.Students (StudentID,
StudentName,
SubjectID)
VALUES (1, 'John', 1),
(2, 'Jane', 1),
(3, 'Mike', 2),
(4, 'Emily', 2),
(5, 'David', 3),
(6, 'Sarah', 3);
GO
SELECT Main.SubjectID,
LEFT(Main.Students, LEN(Main.Students) - 1) AS "Students"
FROM ( SELECT DISTINCT ST2.SubjectID,
( SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)') [Students]
FROM dbo.Students ST2) [Main];
Results done in Azure Data Studio
The above is now part of your box of tricks with minimum effort using Copilot to assist.
Example 3
A developer has the following SQL statement and wants to understand the statement.
SELECT
s.name AS SchemaName,
t.name AS TableName,
p.Rows
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
AND p.rows > 0
GROUP BY
t.name, s.name, p.rows
ORDER BY
t.name
Sure they can run it but they want details.
In Visual Studio, create a new file named test.sql and paste the above statement in. Next press ALT + / then /, options appear, select explain and click the paper airplane and Copilot provides details of the statement.
Next, press ALT + / then /, options appear, select doc to have Copilot create documentation and we get the following.
/*
This SQL script retrieves information about tables in a database.
It selects the schema name, table name, and number of rows for each table.
The script joins multiple system tables to gather the required information.
It also applies filters to exclude system tables and tables with no rows.
The result is grouped by table name, schema name, and number of rows.
The final result is ordered by table name.
*/
SELECT
s.name AS SchemaName,
t.name AS TableName,
p.Rows
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
AND p.rows > 0
GROUP BY
t.name, s.name, p.rows
ORDER BY
t.name
Tips
- When learning in regard to SQL, use well known databases such as Adventure works, NorthWind and Chinook.
- Asking questions for Copilot, write the question, copy the question text to say notepad then ask the question. If the result is not what a developer wants, go back to notepad and rephrase the question again, repeat as needed.
Summary
Copilot can be used to assist with more than helping with writing code, Copilot also can be used to assist with writing SQL statements and explain and document SQL.
Top comments (0)