Use Azure Data Studio to Perform 10 Fundamental SQL Queries in Azure

45 minutes
  • 4 Learning Objectives

About this Hands-on Lab

Learn the key features of Azure Data Studio to securely connect to an Azure SQL Database and cement your SQL querying skills through guided practice. These basic statements cover expectations for the Azure DP-900 Azure Data Fundamentals certification exam.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Download and Install Azure Data Studio

There are alternative ways to query the lab database, but to get the most out of the lab, you will want to download and install Azure Data Studio.

  1. Navigate to Microsoft’s download page for Azure Data Studio to download and install the right version for your operating system.
  2. Follow OS-specific instructions for your installation.
  3. Confirm you can open Azure Data Studio.
  4. If you have problems with this process, refer back to the download page, where there will be instructions and links to more resources.
Enable Your Client IP Address on the Azure SQL Database

In order to connect to the database, the firewall needs to allow you access, which means you need to provide it with your current IP address. This is true, regardless of whether you choose to use Azure Data Studio or some other tool to perform the queries in the last objective of this lab.

  1. Log in to the lab Azure subscription using the credentials provided with this lab.
  2. Navigate to the Azure SQL Database called SQL_Lab (there’s only 1 database).
    • Be sure you select the database and not the database server that backs it.
  3. Near the top of the Overview page for the resource, find and select Set server firewall.
  4. On the Firewall settings page, find and select Add client IP.
    • This should populate the form under Rule name with your IP address.
  5. Save the change.
Connect to Azure Subscription from Azure Data Studio

Interacting with databases in Azure requires you to set up a connection to the subscription. From there, all databases you have rights to see (and use, if you have permissions) will be available to you. Keep in mind that each of those assets might have their own login, but the first step of connecting to the cloud environment is covered here.

Add Linked Account

  1. Open Azure Data Studio, if it is not already open.

  2. In the left Connections menu, choose the Azure blade and select the + to add a new connection.

  3. A Linked accounts window will display. There may be a link to add your first account, but if not, find the icon in the upper right with a little + on it and select that.

    Important: The ADS dialog will "spin" on "Adding account…". At the same time, there is a dialog that will have opened up on your default browser. This is not an in-private window, and that’s okay.

  4. If the dialog lists possible accounts, ignore them and select Use another account.

    • You will then be directed to a sign-in window.
  5. At the sign-in window, enter your credentials for this lab.

  6. When complete, you will see the account back in the Linked accounts dialog in Azure Data Studio. Close that dialog.

  7. In the left Connections pane, under the Azure blade, you should see your lab subscription. Drill down on the tree under SQL database until you find the SQL_Lab database.

    Troubleshooting:

    If you do not see the SQL_Lab database, ensure that you have properly firewall correctly in the previous objective, that the linked connection is the one for this lab and not your personal or work linked connection, and also ensure that you are drilling down on the lab subscription.

Connect to the Specific Database

  1. Drill down on the SQL_Lab to the list of tables. Right-click on any table and choose Select Top 1000. At that point, or before, you’ll get an alert telling you that your login failed, and a dialog will open for you to enter a username and password.

  2. In the dialog, enter LabAdmin as the username and Lab!Pwd9 as the password.

    Important: Be sure to tick the box to remember the password for the most convenient experience.

  3. Confirm that SQL_Lab shows at the top of the query editor window as the database you are working in.

  4. Optionally, run the query that’s in the window for full confirmation that you have a good connection.

  5. Delete the Select Top 1000 query, and you’re ready to write your own SQL!

Run 10 Fundamental SQL Statements

If you are experienced in writing at least some SQL, we encourage you to use the general instructions below to author your own code. If you are new to SQL or just need a hint, go to the Resources section of this lab for the code solutions, which you can copy/paste into the query editor.

  1. In Azure Data Studio, be sure that the query editor pane is showing SQL_Lab at the top of the pane to indicate the database you are using.
  2. Author code for the following 10 tasks, or copy/paste from the Resources section of this lab.
--DML TASK #1: Query a view called vGetAllCategories, returning the product category, the category ID, and then the parent product category, in no particular order
--#1 DML: Simple SELECT query

--OUTCOME #1: This query on a view (rather than a table, just for fun) should return 37 rows, in no particular order
--DML TASK #2: Query a view called vGetAllCategories, returning the product category, the category ID, and then the parent product category, ordered by the parent product category, then product category

--OUTCOME #2: This query on a view should return 37 rows, ordered by the parent category, then product category
--DML TASK #3: Query a view called vGetAllCategories, returning all columns, where the ParentProductCategoryName is Clothing, in no particular order

--OUTCOME #3: This query on a view should return 8 rows, in no particular order (note that the column names are sequenced based on the order defined in the table versus the sequence we specified in previous queries)
--DML TASK #4: Query the Address and CustomerAddress tables to return CustomerID, City, and StateProvince, where the address is located in Idaho

--OUTCOME #4: This query that joins 2 tables should return 3 rows, in no particular order. The customer ID comes from the Customer Address table and the city and state came from the Address table
--DML TASK #5: Query SalesOrderHeader to return the highest TotalDue, the lowest totalDue, the average TotalDue and the Sum of all orders

--OUTCOME #5: Approximate totals . . .  Max: 119,960, Min: 43, Average: 29,884, All Orders: 956,303
--DDL TASK #6: Create a table in the SalesLT schema called SpecialWidgets, with 2 columns: WidgetID as the primary key and WidgetName as a varchar(50)

--OUTCOME #6: Assuming the Messages pane shows a successful execution, verify by refreshing the tree view in the explorer blade to the left, drilling down on the table name to view the columns, or run this query:
SELECT * FROM [SalesLT].[SpecialWidgets]
--This won't return any rows, but it also shouldn't error and say that the table doesn't exist
;
--DDL TASK #7: Alter the SpecialWidgets table you just created by adding another varchar(50) column called WidgetCategory; this column is allowed to be null

--OUTCOME #7: Assuming the Messages pane shows a successful execution, verify by refreshing the tree view in the explorer blade to the left, drilling down on the table name to view the columns, or run this query:
SELECT * FROM [SalesLT].[SpecialWidgets]
--This won't return any rows, but it should show the additional column just added
;
--DML TASK #8: Insert 2 rows into SpecialWidgets, each with a unique WidgetID, whatever WidgetName you want for each row, leaving WidgetCategory

--OUTCOME #8: Assuming the Messages pane shows a successful execution, verify by running this query, which should return 2 rows, with 2 columns populated and the third showing as NULL (empty)
SELECT * FROM [SalesLT].[SpecialWidgets]
;
--DML TASK #9: Update SpecialWidgets so that just 1 of the rows you just inserted is assigned to a WidgetCategory of "Extra Special"

--OUTCOME #9: Assuming the Messages pane shows a successful execution, verify by running this query, which should return 2 rows, where 1 row has all 3 columns populated, and the other is still NULL for WidgetCategory
SELECT * FROM [SalesLT].[SpecialWidgets]
;
--DML TASK #10: Delete the row in SpecialWidgets where the WidgetCategory is not populated
--#10 DML: Delete the row in the SpecialWidgets table where the WidgetCategory is not populated
--At least 2 options. First, we can base it on the column being null. This one would delete ALL rows where this is true; it just so happens we have only 1 row.

--OUTCOME #10 Assuming the Messages pane shows a successful execution, verify by running this query, which should now return just 1 row, with all columns populated
SELECT * FROM [SalesLT].[SpecialWidgets]
;

Additional Resources

Spoiler Alert If you are attempting to author the SQL statements on your own, following the fourth lab objective, what follows are the solution scripts. Proceed only if you need some hints.

For each of the 10 tasks, copy and paste the code below. Note that the code that is commented out in each code block (preceded by 2 dashes like this: --THIS IS A COMMENT) can be safely copied to the query editor window to help you study the code, understand what it is doing, and verify the executed results. You do not have to remove it before you run a query.

--DML TASK #1: Query a view called vGetAllCategories, returning the product category, the category ID, and then the parent product category, in no particular order
--#1 DML: Simple SELECT query
SELECT [ParentProductCategoryName]
      ,[ProductCategoryName]
      ,[ProductCategoryID]
  FROM [SalesLT].[vGetAllCategories]
  ;
 --OUTCOME #1: This query on a view (rather than a table, just for fun) should return 37 rows, in no particular order
--DML TASK #2: Query a view called vGetAllCategories, returning the product category, the category ID, and then the parent product category, ordered by the parent product category, then product category
--#2 DML: Simple SELECT query with an ORDER BY clause
SELECT [ProductCategoryName]
      ,[ProductCategoryID]
      ,[ParentProductCategoryName]
  FROM [SalesLT].[vGetAllCategories]
ORDER BY ParentProductCategoryName, ProductCategoryName
;
--OUTCOME #2: This query on a view should return 37 rows, ordered by the parent category, then product category
--DML TASK #3: Query a view called vGetAllCategories, returning all columns, where the ParentProductCategoryName is Clothing, in no particular order
--#3 DML: Simple SELECT query to return all columns, filtered by the ParentProductCategoryName
SELECT *
  FROM [SalesLT].[vGetAllCategories]
WHERE ParentProductCategoryName='Clothing'
;
--OUTCOME #3: This query on a view should return 8 rows, in no particular order (note that the column names are sequenced based on the order defined in the table versus the sequence we specified in previous queries)
--DML TASK #4: Query the Address and CustomerAddress tables to return CustomerID, City, and StateProvince, where the address is located in Idaho
--#4 DML: SELECT query that joins 2 tables and returns 3 columns (1 column from 1 table, and 2 columns from the other table), where the StateProvince is Idaho
SELECT [CustomerID]
    ,[City]
    ,[StateProvince]     
FROM [SalesLT].[Address]
JOIN [SalesLT].[CustomerAddress]
ON [SalesLT].[Address].[AddressID]=[SalesLT].[CustomerAddress].[AddressID]
WHERE StateProvince='Idaho'
;
--OUTCOME #4: This query that joins 2 tables should return 3 rows, in no particular order. The customer ID comes from the Customer Address table and the city and state came from the Address table.
--DML TASK #5: Query SalesOrderHeader to return the highest TotalDue, the lowest totalDue, the average TotalDue, and the Sum of all orders
--#5 DML: SELECT query with aggregation examples
SELECT MAX(TotalDue) AS MaximumOrderTotal
    ,MIN(TotalDue) AS LowestOrderTotal
    ,AVG(TotalDue) AS AverageOrderTotal
    ,SUM(TotalDue) AS GrandTotalOrders
FROM [SalesLT].[SalesOrderHeader]
;
--OUTCOME #5: Approximate totals . . .  Max: 119,960, Min: 43, Average: 29,884, All Orders: 956,303
--DDL TASK #6: Create a table in the SalesLT schema called SpecialWidgets, with 2 columns: WidgetID as the primary key and WidgetName as a varchar(50)
--#6 DDL: Create a table in the same schema as our other tables (SalesLT), call it SpecialWidgets, with 2 columns WidgetID and WidgetName

--Drop the table, first, if it already exists (note that your query editor may show the "IF EXISTS" syntax to be a typo; execute it anyway. It should work on Azure SQL Database and any SQL Server instances 2016+)
DROP TABLE IF EXISTS [SalesLT].[SpecialWidgets];

-- Create the table in the specified schema
CREATE TABLE [SalesLT].[SpecialWidgets]
(
    [WidgetID] INT NOT NULL PRIMARY KEY, -- Primary Key column
    [WidgetName] VARCHAR(50) NOT NULL
);
--OUTCOME #6: Assuming the Messages pane shows a successful execution, verify by refreshing the tree view in the explorer blade to the left, drilling down on the table name to view the columns, or run this query:
SELECT * FROM [SalesLT].[SpecialWidgets]
--This won't return any rows, but it also shouldn't error and say that the table doesn't exist
;
--DDL TASK #7: Alter the SpecialWidgets table you just created by adding another varchar(50) column called WidgetCategory; this column is allowed to be null
--#7 DDL: Alter the SpecialWidgets table by adding an additional column; this new column is allowed to be empty
ALTER TABLE [SalesLT].[SpecialWidgets] ADD [WidgetCategory] VARCHAR(50) NULL
;
--OUTCOME #7: Assuming the Messages pane shows a successful execution, verify by refreshing the tree view in the explorer blade to the left, drilling down on the table name to view the columns, or run this query:
SELECT * FROM [SalesLT].[SpecialWidgets]
--This won't return any rows, but it should show the additional column just added
;
--DML TASK #8: Insert 2 rows into SpecialWidgets, each with a unique WidgetID, whatever WidgetName you want for each row, leaving WidgetCategory
--#8 DML: Insert into the SpecialWidgets table, populating only the columns that contain data (NOT NULL)
INSERT INTO [SalesLT].[SpecialWidgets] (WidgetID, WidgetName)
VALUES (22,'Big Yellow Widget'),
(45, 'Tiny Red Widget')
;
--OUTCOME #8: Assuming the Messages pane shows a successful execution, verify by running this query, which should return 2 rows, with 2 columns populated and the third showing as NULL (empty)
SELECT * FROM [SalesLT].[SpecialWidgets]
;
--DML TASK #9: Update SpecialWidgets so that just 1 of the rows you just inserted is assigned to a WidgetCategory of "Extra Special"
--#9 DML: Update the SpecialWidgets table to populate the WidgetCategory column for the row where the WidgetName ID is 45
UPDATE [SalesLT].[SpecialWidgets]
SET WidgetCategory='Extra Special'
WHERE WidgetID=45
;
--OUTCOME #9: Assuming the Messages pane shows a successful execution, verify by running this query, which should return 2 rows, where 1 row has all 3 columns populated, and the other is still NULL for WidgetCategory
SELECT * FROM [SalesLT].[SpecialWidgets]
;
--DML TASK #10: Delete the row in SpecialWidgets where the WidgetCategory is not populated
--#10 DML: Delete the row in the SpecialWidgets table where the WidgetCategory is not populated
--At least 2 options. First, we can base it on the column being null. This one would delete ALL rows where this is true; it just so happens we have only 1 row:
DELETE FROM [SalesLT].[SpecialWidgets] WHERE WidgetCategory IS NULL;
--Or this statement works, too, and is more precise to remove the single row (and is faster on a well-designed database):
DELETE FROM [SalesLT].[SpecialWidgets] WHERE WidgetID = 22;
--OUTCOME #10 Assuming the Messages pane shows a successful execution, verify by running this query, which should now return just 1 row, with all columns populated
SELECT * FROM [SalesLT].[SpecialWidgets]
;

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?