MSSQL

In this lab, we will practice working with the server-side programming features of Microsoft SQL Server.

Pre-requisites and preparation

Required tools to complete the tasks:

• Windows, Linux, or macOS: All tools are platform-independent, or a platform-independent alternative is available.
• Microsoft SQL Server
  • The free Express version is sufficient, or you may also use localdb installed with Visual Studio
  • A Linux version is also available.
  • On macOS, you can use Docker.
• SQL Server Management Studio, or you may also use the platform-independent Azure Data Studio is
• Database initialization script: mssql.sql
• GitHub account and a git client

Materials for preparing for this laboratory:

• Using Microsoft SQL Server: description and video
• The schema of the database
• Microsoft SQL Server server-side programming and the SQL language
  • Check the materials of Data-driven systems including the seminars

Initial steps

Keep in mind that you are expected to follow the submission process.

Create and check out your Git repository

1. Create your git repository using the invitation link in Moodle. Each lab has a different URL; make sure to use the right one!

2. Wait for the repository creation to complete, then check out the repository.

   If you are not asked for credentials to log in to GitHub in university computer laboratories when checking out the repository, the operation may fail. This is likely due to the machine using someone else's GitHub credentials. Delete these credentials first (see here), then retry the checkout.

3. Create a new branch with the name solution and work on this branch.

4. Open the checked-out folder and type your Neptun code into the neptun.txt file. There should be a single line with the 6 characters of your Neptun code and nothing else in this file.

Create the database

1. Connect to Microsoft SQL Server using SQL Server Management Studio. Start Management Studio and use the following connection details:

   • Server name: (localdb)\mssqllocaldb or .\sqlexpress (which is short for: localhost\sqlexpress)
   • Authentication: Windows authentication

2. Create a new database (if it does not exist yet). The name should be your Neptun code: in Object Explorer right-click Databases and choose Create Database.

   IMPORTANT

   The name of the database must be your Neptun code. You will need to submit screenshots that display the database name this way!

3. Create the sample database by executing the initializer script Open a new Query window, paste the script into the window, then execute it. Make sure to select the correct database in the toolbar dropdown.

   

4. Verify that the tables are created. If the Tables folder was open before, you need to refresh it.

   .