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

Download and install Azure Data Studio for your OS.

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.

Configure this access for the Azure SQL Database by adding your client IP the Networking page, where the IP firewall settings are managed. Test this access in the Query editor. Use Lab!Pwd9 as the password when connecting to the Query editor.

From the Azure portal Overview page, obtain the Server name (FQDN). In Azure Data Studio, add a new linked Azure account using the same credentials you used to log in to the Azure portal. Once the account is linked, connect to the server and database in Azure Data Studio.

Use the previously copied FQDN as the Server, LabAdmin as the User name, and Lab!Pwd9 as the Password.

In Azure Data Studio, open the Query Editor. At the top of the pane, ensure it is connected to the SQL_Lab to indicate the database you are using. If you are experienced in writing at least some SQL, we encourage you to use the hints provided below to construct each query. If you are new to SQL, refer to the Lab Guide for the code solutions, which you can copy-paste into the Query Editor.

Note that the following tasks include hints that are commented out (preceded by two dashes like this: --THIS IS A COMMENT) and 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 these comments before you run a query. Enter your code before the --OUTCOME comment in each task. The --OUTCOME will provide information on the expected results.

DML Task #1

--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

--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

--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 eight 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

--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 two tables should return three rows, in no particular order. The customer ID comes from the CustomerAddress table and the city and state come from the Address table.

DML Task #5

--DML TASK #5: Query SalesOrderHeader table 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

--DDL TASK #6: Create a table in the SalesLT schema called SpecialWidgets, with two 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

--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

--DML TASK #8: Insert two rows into SpecialWidgets, each with a unique WidgetID, whatever WidgetName you want for each row, leaving WidgetCategory empty.


--OUTCOME #8: Assuming the Messages pane shows a successful execution, verify by running this query, which should return two rows, with two columns populated and the third showing as NULL (empty).
SELECT * FROM [SalesLT].[SpecialWidgets];

DML Task #9

--DML TASK #9: Update SpecialWidgets so that just one 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 two rows, where one row has all three columns populated, and the other is still NULL for WidgetCategory.
SELECT * FROM [SalesLT].[SpecialWidgets];

DML Task #10

--DML TASK #10: Delete the row in SpecialWidgets where the WidgetCategory is not populated.
--At least two 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 one row.


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