Using SQL to Manage Database Objects

30 minutes
  • 6 Learning Objectives

About this Hands-on Lab

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

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

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log In To The Azure Portal

Log in to the Azure Portal using the provided credentials.

Create a SQL Database
  • Click on the three-line menu button at the top left of the page, and click Create a resource.
  • Click on the Databases category on the left, and click SQL Database.
  • Under Resource group choose the one created by the lab.
  • Assign a Database name.
  • Under Server click Create new.
  • Assign a Server name.
  • Assign an Admin login.
  • Assign a Password, following the requirements.
  • Under Location, choose (US) West US.
  • Click OK.
  • Leave Elastic Pool at No.
  • Click Configure database.
  • Click the area for Basic.
  • Click Apply.
  • ClickNext: Additional Settings.
  • Under Use existing data click Sample.
  • Click Review + create.
  • Double-check everything and click Create.

After a short time, we will have a fully-functional SQL database preloaded with data and ready to go!

Note: To connect from the client machine rather than the Azure Query Editor, take these additional steps.

  • Click Go to resource.
  • Click Set server firewall at the top.
  • Click Add client IP.
  • Add the public IP address (it may autofill for you).
  • Click Save.
Connect Our Client
  • Go to the Overview page for our SQL database. (Clicking SQL databases in the far left pane, then on our database name gets us back. Or we can use the breadcrumb menu at the top.)
  • To avoid potential connection issues and use a simple editor, click Query editor which is currently in preview.
  • If you’d rather use a local client, follow these steps (for the purposes of this example, we are going to assume Visual Studio Code is being used.)
    • Download and install Visual Studio Code. (https://code.visualstudio.com/)
    • Install the MSSQL extension.
      • Click on the Extensions icon on the far-left side.
      • Search for "MSSQL", click on it, and click Install.
  • Open a new window, and change the type by clicking Plain Text at the bottom-right and choosing SQL.
  • In the same area, click on Disconnected.
  • In the top pane that pops up, choose Create Connection Profile.
  • Copy and paste the Server name from your database overview page and hit Enter.
  • Type or copy and paste our Database name and hit Enter.
  • Choose SQL Login and hit Enter.
  • Enter the Admin login specified earlier and hit Enter.
  • Enter the Password specified earlier and hit Enter.
  • Choose whether or not you’d like to save the password and hit Enter.
  • Type a Profile Name and hit Enter.

In the bottom-right, we should see a message saying the profile has been created and we are connected. Now let’s have some fun!

Create an Index for Product Name and Color

An index will improve performance for oft-used queries against specific fields. Use the following command to create one for the Name and Color columns on the Product table.

    CREATE INDEX IX_Product_NameAndColor ON SalesLT.Product (Name,Color)
Update the Product Description View

In order to provide our analytics workers with more detail, update the view vProductAndDescription to include the ProductNumber column from the Product table. (Hint: Use the current definition and simply change CREATE to ALTER.)

    ALTER VIEW SalesLT.vProductAndDescription
    AS
    SELECT
        p.ProductID
        ,p.ProductNumber
        ,p.Name
        ,pm.Name AS ProductModel
        ,pmx.Culture
        ,pd.Description
    FROM SalesLT.Product p
        INNER JOIN SalesLT.ProductModel pm
        ON p.ProductModelID = pm.ProductModelID
        INNER JOIN SalesLT.ProductModelProductDescription pmx
        ON pm.ProductModelID = pmx.ProductModelID
        INNER JOIN SalesLT.ProductDescription pd
        ON pmx.ProductDescriptionID = pd.ProductDescriptionID;

    GO
Delete the Unused ErrorLog Table

The owners of the table ErrorLog have confirmed it’s no longer in use, and they wish to delete it. Use the following command to drop that table.

    DROP TABLE dbo.ErrorLog

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 our enterprise data.

To begin working with our sample dataset we must log in to the Azure portal and create a SQL database. We 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 others.

Note for novices:

The solution video is conducted in Visual Studio Code, but if you have no experience with that environment, we suggest you use the Azure Query Editor that the instructor briefly showed you in the solution video.

In this scenario, our 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 that type of query improved. Second, they'd like the view for product descriptions to include the product number information in addition to its current fields. And lastly, they'd like the table ErrorLog dropped as it is no longer in use.

Step-by-step instructions are included in the task list. Feel free to follow along there or jump in to begin managing database objects with SQL!

(Note: Be sure to check out our interactive diagram for hidden knowledge!) https://www.lucidchart.com/documents/view/062a707f-b26d-42b8-9e49-34539785122c

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.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!

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