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.
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.
- Navigate to Microsoft’s download page for Azure Data Studio to download and install the right version for your operating system.
- Follow OS-specific instructions for your installation.
- Confirm you can open Azure Data Studio.
- 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.
- Log in to the lab Azure subscription using the credentials provided with this lab.
- 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.
- Near the top of the Overview page for the resource, find and select Set server firewall.
- On the Firewall settings page, find and select Add client IP.
- This should populate the form under Rule name with your IP address.
- 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
Open Azure Data Studio, if it is not already open.
In the left Connections menu, choose the Azure blade and select the + to add a new connection.
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.
If the dialog lists possible accounts, ignore them and select Use another account.
- You will then be directed to a sign-in window.
At the sign-in window, enter your credentials for this lab.
When complete, you will see the account back in the Linked accounts dialog in Azure Data Studio. Close that dialog.
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
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
Drill down on the
SQL_Labto 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.
In the dialog, enter
LabAdminas the username and
Lab!Pwd9as the password.
Important: Be sure to tick the box to remember the password for the most convenient experience.
SQL_Labshows at the top of the query editor window as the database you are working in.
Optionally, run the query that’s in the window for full confirmation that you have a good connection.
Select Top 1000query, 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.
- In Azure Data Studio, be sure that the query editor pane is showing
SQL_Labat the top of the pane to indicate the database you are using.
- 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] ;
- In Azure Data Studio, be sure that the query editor pane is showing