Create a Database with GitHub Copilot

Objective

The objective of this activity is to give students the skills to use GitHub Copilot for creating, refining, and populating a SQL Server database by leveraging Copilot's smart suggestions and real-time guidance throughout the development process.

Prerequisites

• Visual Studio Code with GitHub Copilot enabled
• GitHub Copilot extension(s) installed
• Active GitHub Copilot subscription

Steps

Step 1: Create the Initial Script

• Create a new file named Bookstore.sql
• Ask GitHub Copilot to get you started:

Create a SQL Server 2022 database to track authors, books, and sales

• Accept the code

Step 2: Improve the Script

• Ask GitHub Copilot to improve the script:

Make sure I'm in the Master database first

Add DROP statement in case the database already exists

• Accept the code changes

Step 3: Track Additional Fields

• Ask GitHub Copilot to track additional fields:

Track author email and website

• Accept the code changes

Step 4: Generate Sample Data

• Ask GitHub Copilot to generate sample data

Generate 20 sample authors, 20 sample books, and 15 sample sales

• Accept the code changes

Step 5: Make the Data More Interesting

• Ask GitHub Copilot to make the data more interesting:

Use names of real authors of technical books

Create titles based on AI and GitHub Copilot

• Accept the code changes

Step 6: Create a View

• Ask GitHub Copilot to create a comprehensive view:

Create a view showing authors and related book sales

Select all rows from the new view

• Accept the code changes
• (Optional) Run the script to generate the database and sample data

Summary

In this activity, you learned how to use GitHub Copilot to efficiently create and refine a SQL script for a Bookstore database with AI-generated suggestions and real-time guidance. You explored Copilot's capabilities to scaffold a database, add safeguards, expand schema fields, generate and improve sample data, and create comprehensive views, making the database development process smoother and more productive.