Microsoft Azure SQL

Connecting to the database

From the Microsoft Azure portal (https://portal.azure.com/), we have access to the list of available databases:

Clicking on the database name (in this case, ccDatabase), we can see the general database information (at the centre of the page) and a menu on the left, from where we can access the Query editor:

Accessing the database requires providing the access credentials, even having previously logged into the portal.

After clicking on the Query editor option (in the menu on the left), we need to enter the login data and the password to access the database. Then we click on the Ok blue button:

The Query editor main page has three sections:

1. This section shows the tables, views and stores procedures for our database.
2. The second one is the place where we will write and run the SQL statements.
3. The Results section shows the output for the SQL executed instructions.

Let’s write and run a SELECT query to see some information from the Product and ProductCategory tables:

Here is the code from Query 1 area:

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

After clicking the Run button, the Results section shows the data retrieved from the database:

Code analysis

Let us analyse below the meaning of the lines of code applied:

We use the SELECT TOP clause in line 1 to specify the number of records to return. Then, in the same line, we inform the field names (Name and name) to get and the alias assigned to each one.

The FROM clause in line 2 establish the source table (SalesLT.ProductCategory) for the first field (pc.Name) and then join (JOIN clause in line 3) a second table from where get the second field (p.name). Finally, line 4 defines the fields that have to match from both tables.

Adding a record to the database

Running the following INSERT T-SQL statement we will add a new product in the SalesLT.Product table:

INSERT INTO [SalesLT].[Product]
       ( [Name]
       , [ProductNumber]
       , [Color]
       , [ProductCategoryID]
       , [StandardCost]
       , [ListPrice]
       , [SellStartDate]
       )
VALUES
       ('myNewProduct'
       ,123456789
       ,'NewColor'
       ,1
       ,100
       ,100
       ,GETDATE() );

The result is a new row added to the SalesLT.Product table:

Updating a record to the database

Running the following UPDATE T-SQL statement we can modify a specific record data, for example, the value for the record ListPrice added previously:

UPDATE [SalesLT].[Product]
SET [ListPrice] = 125
WHERE Name = 'myNewProduct';

A SELECT statement let us check that the change was successful:

Deleting a record from the database

Running the following DELETE T-SQL statement, we will remove the new product added before

DELETE FROM [SalesLT].[Product]
WHERE Name = 'myNewProduct';

Once again, a SELECT statement let us check that the DELETE statement successfully deleted the record: