Using SQL to Manage Database Objects

30 minutes
  • 5 Learning Objectives

About this Hands-on Lab

SQL is a powerful language for querying, changing, and deleting data. Almost every person in IT will encounter SQL queries at some point in their work. Being familiar with how to use it effectively can help you achieve greater success in your current role and possibly even set you up to move into a different role.

In this hands-on lab, you will work with methods of managing database objects. This includes creating, altering, and dropping items, such as tables and views.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Create a SQL Database

From the Azure portal, create a SQL database named manage_lab using the lab-provided resource group. Create a new server, and name it la-sql-lab-manage. Set the server location to be the same as your lab-provided resource group. Edit your server firewall to add your client IP.

Connect to the Client

In Visual Studio Code, create a new SQL file. Create a connection profile, and connect to your previously created server and database. Use the login credentials you created when configuring the database.

Create an Index for Product Name and Color

An index will improve performance for frequently used queries against specific fields. Create an index for the Name and Color columns on the Product table.

Update the Product Description View

In order to provide the analytics workers with more detail, update the vProductAndDescription view to include the ProductNumber column from the Product table.

Hint: Use the current definition, and change CREATE to ALTER.

Delete the Unused ErrorLog Table

The owners of the ErrorLog table have confirmed it’s no longer in use, and they wish to delete it. Write a statement to drop the table.

Additional Resources

One exciting feature of Azure is the ability to quickly spin up globally accessible databases. This gives a SQL professional great agility in creating database backends or allowing multiple, distributed teams to report off of enterprise data.

To begin working with the sample dataset, log in to the Azure portal, and create a SQL database. You’ll then need to connect to the database and begin working with it using a tool such as Visual Studio Code, Azure Query Editor, or one of several other tools.

Visual Studio Code

The lab solution video and lab guide use Visual Studio Code to connect to the client and run the needed queries. You can also use the Query editor found within in the Azure portal (currently in preview). If you want to use Visual Studio Code, you will need to set up your environment before completing the lab:

  1. If you do not have the program installed, download and install Visual Studio Code.
  2. Once the program is downloaded, open it and install the MSSQL extension:
    • On the left side of the screen, click the icon for Extensions.
    • Search for and select SQL Server (mssql).
    • Click Install.

Scenario

In this scenario, an analytics group has requested a few updates to improve reporting on the Product information. First, a frequently used operation is to search by product Name and Color, so they'd like the performance of this query type improved. Second, they'd like the view for vProductAndDescription to include the ProductNumber information in addition to its current fields. Last, they'd like the ErrorLog table dropped as it is no longer in use.

Note: Be sure to check out the interactive diagram for hidden knowledge!

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?