Skip to main content

Using SQL to Manage Database Objects

Hands-On Lab

 

Photo of Landon Fowler

Landon Fowler

Database Training Architect II

Length

00:30:00

Difficulty

Intermediate

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Using SQL to Manage Database Objects

Introduction

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.

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!

Connecting to the Lab

  1. Log in to the Azure Portal using the credentials provided on the lab instructions page.

Create a SQL Database

  1. Click SQL databases.

  2. Click Create SQL database.

  3. Click the combo box next to Resource group and select the available resource group.

  4. In the box next to Database name, enter "manage_lab".

  5. Click Create new under the text box next to Server.

  6. In the frame that appears, enter "la-sql-lab-manage" in the box under Server name.

  7. Provide a login in the box under Server admin login.

  8. Provide a password in the box under Password and enter the same password in the box under Confirm password.

  9. Under Location, click the combo box and pick a nearby region.

  1. Check the box next to Allow Azure services to access server.

  2. Click OK.

  3. Back in the main window, click Configure database.

  4. Click the window at the top that says Looking for basic, standard, premium?.

  5. Click Apply.

  6. Click Next: Additional settings.

  7. Under Data source, make sure Use existing data is set to Sample.

  8. Click Review + create.

  9. Click Create.

Connect Our Client

  1. Click Go to resource.

  2. Click Set server firewall at the top of the frame.

  3. Click Add client IP at the top of the pop-up window.

  4. Click Save.

  5. Back in the main window, copy the Server name to the clipboard.

    The next steps in this lab go through specifically using Visual Studio Code. Other programs can be used but might result in some slight differences in the instructions.

  6. Create a new document.

  7. Click Plain Text at the bottom of the window.

  8. Change the text type to SQL.

  9. Click the window at the top and select Create Connection Profile.

  1. Paste the server name copied in a previous step and press Enter.

  2. For the database name, enter "manage_lab" without quotes and press Enter.

  3. Select SQL Login.

  4. Log in using the credentials provided in the previous task.

  5. Choose Yes to save the password.

  6. Name the profile "manage_lab" without quotes and press Enter.

Create an Index for Product Name and Color

  1. Create the index.

    CREATE INDEX IX_Product_NameAndColor
    ON SalesLT.Product (Name, Color)
  2. Verify the index.

    select * from sys.indexes
    where name = 'IX_Product_NameAndColor'

Update the Product Description View

  1. Check the current view.

    select * from SalesLT.vProductAndDescription
  2. Alter the view for the product description.

    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
  3. Verify the changes.

    select * from SalesLT.vProductAndDescription

Delete the Unused ErrorLog Table

  1. Drop the ErrorLog table.

    DROP TABLE dbo.ErrorLog
  2. Verify the table no longer exists.

    select * from sys.tables

Conclusion

Congratulations, you've completed this hands-on lab!