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.
Successfully complete this lab by achieving the following learning objectives:
- Log In To The Azure Portal
Log in to the Azure Portal using the provided credentials.
- Create a SQL Database
- Click on the three-line menu button at the top left of the page, and click Create a resource.
- Click on the Databases category on the left, and click SQL Database.
- Under Resource group choose the one created by the lab.
- Assign a Database name.
- Under Server click Create new.
- Assign a Server name.
- Assign an Admin login.
- Assign a Password, following the requirements.
- Under Location, choose (US) West US.
- Click OK.
- Leave Elastic Pool at No.
- Click Configure database.
- Click the area for Basic.
- Click Apply.
- ClickNext: Additional Settings.
- Under Use existing data click Sample.
- Click Review + create.
- Double-check everything and click Create.
After a short time, we will have a fully-functional SQL database preloaded with data and ready to go!
Note: To connect from the client machine rather than the
Azure Query Editor, take these additional steps.
- Click Go to resource.
- Click Set server firewall at the top.
- Click Add client IP.
- Add the public IP address (it may autofill for you).
- Click Save.
- Connect Our Client
- Go to the Overview page for our SQL database. (Clicking SQL databases in the far left pane, then on our database name gets us back. Or we can use the breadcrumb menu at the top.)
- To avoid potential connection issues and use a simple editor, click Query editor which is currently in preview.
- 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.)
- Download and install Visual Studio Code. (https://code.visualstudio.com/)
- Install the MSSQL extension.
- Click on the
Extensionsicon on the far-left side.
- Search for "MSSQL", click on it, and click Install.
- Click on the
- Open a new window, and change the type by clicking Plain Text at the bottom-right and choosing SQL.
- In the same area, click on Disconnected.
- In the top pane that pops up, choose Create Connection Profile.
- Copy and paste the
Server namefrom your database overview page and hit
- Type or copy and paste our
Database nameand hit Enter.
- Choose SQL Login and hit Enter.
- Enter the Admin login specified earlier and hit Enter.
- Enter the Password specified earlier and hit Enter.
- Choose whether or not you’d like to save the password and hit Enter.
- Type a Profile Name and hit Enter.
In the bottom-right, we should see a message saying the profile has been created and we are connected. Now let’s have some fun!
- Create an Index for Product Name and Color
An index will improve performance for oft-used queries against specific fields. Use the following command to create one for the
Colorcolumns on the
CREATE INDEX IX_Product_NameAndColor ON SalesLT.Product (Name,Color)
- Update the Product Description View
In order to provide our analytics workers with more detail, update the view
vProductAndDescriptionto include the
ProductNumbercolumn from the
Producttable. (Hint: Use the current definition and simply change
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
- Delete the Unused ErrorLog Table
The owners of the table
ErrorLoghave confirmed it’s no longer in use, and they wish to delete it. Use the following command to drop that table.
DROP TABLE dbo.ErrorLog