Azure Data Studio

In the previous articles, we’ve learned about the basics of Azure Data Studio and how to install it and then went through a step-by-step guide to connect Azure Data Studio to Azure SQL Database. Following the last article, this article dives in creating tables, working with queries and delete the data in Azure SQL Database from Azure Data Studio.

Azure Data Studio Article Series

Azure Data Studio is basically a database tool that is cross-platform routinely used by data engineers and professionals for both on-premises and cloud services throughout the operating system spectrum from Windows to macOS and Linux. There are numerous modern editor offerings with the Azure Data Studio from Code Snippets, IntelliSense, Integrated Terminal, Source Control Integration, and more. A great experience with charting of query results, customizable dashboards are supported built-in.

Now, let us get into learning how to create tables, query data and delete them in Azure SQL Database from the Azure Data Studio with this step-by-step tutorial.

Step 1

Connect Azure Data Studio to Azure SQL Database. Once it is done, you can access the Azure SQL DB from Azure Data Studio as following.

Here, we have two databases; i.e., Master and ojashdatabase.

Step 2

Select the ojashdatabase by Clicking on it.

Since this is a newly created database, we can see it is empty.

You can see the compute utilization visualization as of now in Azure.

Step 3

Click on New Query under the database option you created.

Step 4

You’ll be taken to SQL Query Editor.

Creating Database

Step 5

With the following SQL code in the Query Editor, we create a new database TutorialDB. Click on Run to Execute the Code.

IF NOT EXISTS (
 SELECT name
 FROM sys.databases
 WHERE name = N'TutorialDB'
)
CREATE DATABASE [TutorialDB]
GO
ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON
GO

The message will be updated in the section below.

Step 6

We can see, a new Database TutorialDB is created under the Server Section just below ojashdatabase.

Creating Table

Step 7

Now, with the following code, we create a new Table named Customers with the Columns CustomerId, Name, Location, Email with the CustomerId set as the Primary Key.

-- Create a new table called 'Customers' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
DROP TABLE dbo.Customers
GO
-- Create the table in the specified schema
CREATE TABLE dbo.Customers
(
 CustomerId INT NOT NULL PRIMARY KEY, -- primary key column
 Name [NVARCHAR](50) NOT NULL,
 Location [NVARCHAR](50) NOT NULL,
 Email [NVARCHAR](50) NOT NULL
);
GO

Inserting Row in Table

Step 8

With the following code, we add in values such as Ojash, Nepal and so on to the respective columns of CustomerId, Name, Location and Email.

-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
 ([CustomerId],[Name],[Location],[Email])
VALUES
 ( 1, N'Ojash', N'Nepal', N'[email protected]'),
 ( 2, N'Shiva', N'India', N'[email protected]'),
 ( 3, N'Mahesh', N'United States', N'[email protected]'),
 ( 4, N'John', N'United Kingdom', N'[email protected]')
GO

Displaying Data

Step 9

The values updated in the database can now be viewed with the following code. Next, we Click on Run to display the output in Results section.

-- Select rows from table 'Customers'
SELECT * FROM dbo.Customers;

Deleting Data from Table

Step 10

With the following SQL query, we delete the data from the Customers Table.

-- Delete rows from table 'Customers'
DELETE FROM dbo.Customers;

Once, we run the query, we can see that the values ie. Data under each column is now null.

With this we’ve learnt to create Database, create Table and Query from Azure Data Studio on the Azure SQL Database.

Step 11

Now, we visit the Azure Portal can check the metrics. We can see the visualization of the CPU billing as per the resource use.

We can also set the view for the visualization graphs for different metrics and aggregations.

Conclusion

Thus, in this article, we learnt by the step-by-step process to Create Database, Table and Query data and Delete data from Azure Data Studio in Azure SQL Database.