Using SQL to Change Data

Log in to the Azure Portal using the provided credentials.

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. The Subscription dropdown is auto-populated, but there's a resource group sitting in the Resource group dropdown that we'll have to choose.
4. Assign a Database name, something unique, like change_data.
5. Under Server, click Create new:
   • Assign a Server name (must be globally unique)
   • Assign an Admin login
   • Assign a Password, following the requirements.
   • Under Location, choose (US) West US.
   • Click OK.
6. Leave Elastic Pool at No.
7. Click Configure database.
   • Click the area for Basic.
   • Click Apply.
8. Click Next: Additional Settings.
   • Under Use existing data click Sample.
9. Click Review + create.
10. Double-check everything and click Create.

After a short time, you will have a fully functional SQL database preloaded with data and ready to go!

Note: If you will be connecting from your client machine rather than using the Azure Query Editor, take these additional steps.

1. Click the Go to resource button.
2. Click Set server firewall at the top.
3. Click Add client IP.
4. Add your public IP address (it may autofill for you).
5. Click Save.

1. Go to the Overview page for your SQL database. (If you've navigated away, return by clicking SQL databases in the far left pane, then on your database name. Or use the breadcrumb menu at the top.):
   • We need the Server name there, and we can click on the little "Copy to clipboard" button to grab it for later use.
   • If you'd like to avoid potential connection issues and use a simple editor, click on Query editor, which is currently in preview.
2. 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 Extensions icon on the far left side.
       • Search for MSSQL, click on it, and click Install.
3. Open a new window, and change the type by clicking on Plain Text at the bottom right, then choosing SQL (by typing it) in the Select Language Mode text area at the top of the screen.
4. On the same screen, down where we clicked Plain Text a second ago, click on Disconnected.
5. In the top pane that pops up, choose Create Connection Profile:

• Paste the Server name that we copied from our database overview page and hit Enter.
• Type or paste (we'd have to copy it from the overview page as well, in order to paste) our Database name and hit Enter.
• Choose SQL Login and hit Enter.
• Enter the Admin login you specified earlier and hit Enter.
• Enter the Password you 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 you should see a message saying that the profile has been created and that you are connected. Now let's have some fun!

Let's start by finding out what tables are in our database to work with. Use the query below to list those tables and their associated schemas from a system view:

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

A new customer's registration failed, so we need to update the Customer table manually. Use the statement below to add the disgruntled customer Delmar Database with a few minimal fields:

INSERT INTO SalesLT.Customer (FirstName, MiddleName, LastName, PasswordHash, PasswordSalt)
VALUES ('Delmar', 'D.', 'Database', 'oMm8YHksV5Ejn8wACj3H', 'zr2at1t3')

Unfortunately, the sales system placed Delmar's order and neglected to record his email address. So he has not been receiving updates on his order. Update his address manually to avoid another issue with the sales system, and hopefully preserve a valued customer:

UPDATE SalesLT.Customer
SET EmailAddress = '[email protected]'
WHERE FirstName = 'Delmar' AND LastName = 'Database'

Poor Delmar. None of his orders have been successful. Now he's had it, and wishes his account to be completely deleted. As our product team works overtime to fix the dastardly sales system, we will happily comply with Delmar's request to remove his account using the statement below:

DELETE FROM SalesLT.Customer
WHERE FirstName = 'Delmar' AND LastName = 'Database'