Using SQL to Retrieve Data

1 hour
  • 7 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 you achieve greater success in your current role and possibly even set you up for a move to another.

In this hands-on lab we are going to work with various methods of selecting data. This will help you get an understanding of how to extract useful information from databases.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log In to the Azure Portal
  1. Log in to the Azure Portal using the provided credentials.
Create a SQL Database
  1. Click on the three-line menu button at the top left of the page, and click Create a resource.

  2. Click on the Databases category on the left, and click SQL Database.

  3. Under Resource group, do not click Create new and name it because the lab already provides one for us to select.

  4. Assign a Database name e.g. sql_lab.

  5. Under Server click Create new.

  6. Assign a Server name.

  7. Assign an Admin login.

  8. Assign a Password, following the requirements.

  9. Under Location, choose (US) West US.

  10. Click OK.

  11. Leave Elastic Pool at No.

  12. Click Configure database.

  13. Click the area for Basic.

  14. Click Apply.

  15. Click Next: Additional Settings.

  16. Under Use existing data click Sample.

  17. Click Review + create.

  18. Double-check everything and click Create.

    After a short time, we will have a fully functional SQL database preloaded with data that’s ready to go!

    Note: If we will be connecting from our client machine rather than the Azure Query Editor, take these additional steps:

  19. Click Go to resource.

  20. Click Set server firewall at the top.

  21. Click Add client IP.

  22. Add the public IP address (it may autofill).

  23. Click Save.

Connect Your Client
  1. Go to the Overview page for the SQL database.

    Note: We can return to the page also by clicking SQL databases in the far left pane, and then on the database name. Or we can use the breadcrumb menu at the top.

  2. If we want to avoid potential connection issues and use a simple editor, click Query editor which is currently in preview.

  3. 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.)

    1. Download and install Visual Studio Code.(https://code.visualstudio.com/)
    2. Install the MSSQL extension.
      1. Click on the Extensions icon on the far left-hand side.
      2. Search for MSSQL, click on it, and then click Install.
  4. Search for MSSQL, click on it, and then click Install.

  5. Open a new window, and change the type by clicking on Plain Text at the bottom right and choosing SQL.

  6. In the same area, click Disconnected.

  7. In the top pane that pops up choose Create Connection Profile.

  8. Copy/paste the Server name from the database Overview page and hit Enter.

  9. Type or copy/paste the Database name and hit Enter.

  10. Choose SQL Login and hit Enter.

  11. Enter the Admin login that was specified earlier and hit Enter.

  12. Enter the Password that was specified earlier and hit Enter.

  13. Choose whether or not to save the password and hit Enter.

  14. Type a Profile Name and hit Enter.

    Note: In the bottom right, we should see a message saying that the profile has been created and indicating our connected status.

List the Database Tables
  1. Let’s start by finding out what tables are in our database to work with. Use the query below to list those tables from a system view:

    SELECT name FROM sys.tables
Use a Join to Show the Tables’ Schema
  1. Without knowledge of which schema a table belongs to we cannot properly query it. Use the query below to associate the table name with its schema name:

    SELECT t.name as TableName, s.name as SchemaName
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
List Silver Bicycles
  1. Looking at the tables we can see that there is an associated ProductCategory table. Use the query below to pull a list of products that filters via the correct category ID for bicycles and the color Silver:

    SELECT *
    FROM SalesLT.Product p
    LEFT JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID
    WHERE c.ProductCategoryID = 5
    AND p.Color = 'Silver'
Narrow Down the List to Items Under $1000 and Sort by Least to Most Expensive
  1. We will expand on the last query by adding additional WHERE conditions and an ORDER BY. Use the following:

    SELECT *
    FROM SalesLT.Product p
    LEFT JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID
    WHERE c.ProductCategoryID = 5
    AND p.Color = 'Silver'
    AND p.ListPrice < 1000
    ORDER BY p.ListPrice ASC

Additional Resources

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

To begin working with our sample dataset, you are going to log into the Azure portal and create a SQL database. You will then use Visual Studio Code, Azure Query Editor, or another tool to connect to the database and begin working with it.

In our scenario, you will be producing a list of bicycles that will be used for an upcoming promotion. In this sale, we are featuring silver bicycles that are under $1,000. Use SQL to produce a list of these, sorted from least to most expensive.

For detailed instructions on how to complete these tasks, expand each learning objective below, or click the Guide tab above the video player. Feel free to follow along there or jump in on your own to begin retrieving data via SQL!

Note: Be sure to check out our interactive diagram for hidden knowledge! https://www.lucidchart.com/documents/view/42f19722-ec69-419c-a5b0-63aa26c8f62c

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?