How To Create, Query And Delete Database In Azure SQL

In this article, we’ll learn to create a database in Azure SQL, experiment with some Query, and then when one is done with using the resources, learn to delete it. This is a hands-on tutorial following up on the previous article, Azure SQL Database.

Creating Database in Azure SQL

Step 1

First of all, learn to create and Manage Azure SQL Resources through the Azure Portal. Create an Azure SQL resource following up the step-by-step from this previous tutorial.

Step 2

Now, let us create a Single Database. Under Azure SQL in Dashboard, Select the SQL Database – Single Database and Click on Create.

Step 3

You’ll be prompted with the Create SQL Database page.

Fill in the details for your Resource group, Select the Database name and server. Switch the SQL elastic pool to No and Locally-redundant backup storage-Preview.

Note
If in name, the database server isn’t there, create one yourself by Clicking in Create new in the Server option.

Simply, fill in the Server Name, Location, and Authentication method of User SQL Authentication, Server admin login, and password.

We also learned about SQL Authentication and its substitute in the previous article, Azure Active Directory.

Step 4

Click on Next: Networking.

Select the Public Endpoint in the Network connectivity and Choose No for Firewall rules as of now (we’ll see how this affects later on). Choose the Default for the Connection Policy.

Step 5 

Choose Next: Security

Choose Not Now on the Azure Defender for SQL and then choose Next: Additional Settings.

Step 6 

Under the Use existing data, select on Sample.

And then, Select Review + Create.

Step 7 

We can see the product details and the estimated cost for a month which would result in around 380 USD.

Now, Click on Create.

Step 8 

As we are creating the database, deployment submission notifications pop up.

The Deployment in Process page is updated as new resources are created.

Step 9

Finally, the Deployment is Complete text pops up as the database is deployed.

Click on the Go to Resource.

Step 10 

The SQL Database page look will something like this.

How to QUERY a database in Azure SQL? 

Step 11 

Now, Click on Query Editor 

We’ll see the welcome page as follows. 

We can see, on the left-hand side we have the SQL Server Authentication with Login and Password option and on the left-hand side, we have the Active Directory Authentication. This has been discussed in detail in the prior article, Azure Active Directory.

To learn more about Azure SQL, you can watch this video.

Step 12 

We are using SQL server authentication for this demo. So, click on Ok. 

We can see the prompt, Cannot open server ‘ojash’. Remember in Step 4, we chose not to set the firewall. If we had chosen to setup the firewall back then, this error wouldn’t have popped up.

To resolve this, click on Whitelist IP 2X.XX.XX.X on server ojash.

Once done, click on OK.

Once, all works fine, we can see it through the notification too.

Step 13 

Now, the Query Editor can be viewed. See in Query1, we can use our SQL commands to operate our queries and the output will pop up in the Results. Very similar to our MS SQL Server right.

Step 14 

Let us expand our Tables, Views, and Stored Procedures. We can see numerous tables on Sales here.

Step 15 

Let us practice some Query now.

SELECT TOP 30 pc.Name as CategoryName, p.name as ProductName
FROM SalesLT.ProductCategory pc
JOIN SalesLT.Product p
ON pc.productcategoryid = p.productcategoryid;

Now, Click on Run

Step 16 

We can see the results in the Result section below, with the CategoryName and RoductName listed.

In the Messages, we can see, the affected rows as 30 as we selected the Top 30 rows in our Query.

This is how Query can be done in Azure SQL Database 

Cleaning Resources 

Step 17 

Click on the button on the top left corner. 

Choose, the Resource Groups.

Step 18 

Now, click on the resource name itself. 

You’ll be taken to the resource page with all services that are in operation listed.

Step 19 

Select the services you want to delete i.e., Clean-up to free up resources which would save cost once the work is completed as the costing would be there if the resources are still in operation once the required task is completed.

Azure will pop you up with the Delete Resources Confirmation.

Type in yes in the Confirm Delete segment.

Now, Click on Delete.

With this, the resources will be deleted. 

Conclusion

In this article, we learned through a step-by-step process to create a single database in Azure SQL, learned how to write a query, and check the results. Moreover, we also learned to clean up the resources by deleting them once we no longer need them.