Relational Data in Azure

Question 1

Type of cloud service:
SQL Server on Azure VMs ?
Azure SQL Managed Instance ?
Azure SQL Database ?

Answer 1

SQL Server on Azure VMs - IaaS
Azure SQL Managed Instance - PaaS
Azure SQL Database - PaaS

Question 2

Required user management:
SQL Server on Azure VMs ?
Azure SQL Managed Instance ?
Azure SQL Database ?

Answer 2

SQL Server on Azure VMs - User must manage all aspects of the server (OS, SQL server updates/ configuration/backups/maintenance etc.)

Azure SQL Managed Instance - Not required to manage SQL updates, backups, recovery etc.

Azure SQL Database - Not required to manage SQL updates, backups, recovery etc.

Question 3

Use cases:
SQL Server on Azure VMs ?
Azure SQL Managed Instance ?
Azure SQL Database ?

Answer 3

SQL Server on Azure VMs - migrate or extend an on-premises SQL Server solution and retain full control over all aspects of server and database configuration.

Azure SQL Managed Instance - most cloud migration scenarios, particularly when you need minimal changes to existing applications.

Azure SQL Database - new cloud solutions, or to migrate applications that have minimal instance-level dependencies.

Question 4

Azure SQL Database - Elastic Pool

Answer 4

Elastic pool - multiple databases share the same resources (memory, storage, processing power) via multiple-tenancy. These resources are known as the pool.
Useful if databases have resource requirements that vary over time.

Question 5

Azure SQL Database - Single database

Answer 5

Single database allows a user to quickly set up and run a single SQL Server database. A user can scale the database if more storage space, memory or processing is required.

Question 6

The primary use case for Azure database for MySQL, Azure database for MariaDB and Azure database for PostgreSQL

Answer 6

enable organizations that use them in on-premises apps to move to Azure quickly, without making significant changes to their applications.

Question 6

Primary use case for Azure database for MySQL, Azure database for MariaDB and Azure database for PostgreSQL

Answer 6

The primary reason for these services is to enable organizations that use them in on-premises apps to move to Azure quickly, without making significant changes to their applications.

Question 7

Azure Database for MySQL.

Answer 7

Azure Database for MySQL is a PaaS implementation of MySQL in the Azure cloud, based on the MySQL Community Edition.

Question 8

Azure Database for MariaDB

Answer 8

Azure Database for MariaDB is an implementation of the MariaDB database management system adapted to run in Azure. It’s based on the MariaDB Community Edition.

Question 9

Azure Database for PostgreSQL

Answer 9

is a PaaS implementation of PostgreSQL in the Azure Cloud. This service provides the same availability, performance, scaling, security, and administrative benefits as the MySQL service.

Question 10

SQL-DDL

Answer 10

Data Definition Language - SQL commands to create, modify and remove tables and other objects in a database.

CREATE - create a new object
ALTER - Modify the structure of an object (e.g., alter a table to add a new column)
DROP - Remove an object from the database
RENAME - rename object

Question 11

SQL - DCL

Answer 11

Data Control Language use to manage permissions for specific users or groups

GRANT - grant permission to perform a specific action(s)
DENY - deny permission to perform specific action(s)
REVOKE - remove an existing permission

Question 12

SQL - DML

Answer 12

Data Manipulation Language are commands that manipulate rows in tables.

SELECT - Read rows from table
INSERT - Insert rows into a table
UPDATE - Modify data in existing rows
DELETE - Delete existing rows

Question 13

SQL - WHERE

Answer 13

A WHERE clause is an important clause for DML statements.

SELECT, UPDATE and DELETE statements apply to EVERY ROW in the table unless a WHERE clause is provided.

SELECT * FROM customer; #return all rows
SELECT * FROM customer WHERE city = ‘Perth’; #return customers from the city of perth.

Question 14

What is a view?

Answer 14

A view is a virtual table based on the results of a SELECT query.

CREATE VIEW Deliveries
AS
SELECT o.OrderNo, o.OrderDate,
       c.FirstName, c.LastName, c.Address, c.City
FROM Order AS o JOIN Customer AS c
ON o.Customer = c.ID;

A view can be queried and filtered much the same way as a table.

SELECT OrderNo, OrderDate, LastName, Address
FROM Deliveries
WHERE City = ‘Seattle’;

Question 15

SQL - What is a stored procedure?

Answer 15

SQL statements that can be run on command. Stored procedures are used to encapsulate programmatic logic in a database for actions that applications need to perform when working with data.

CREATE PROCEDURE RenameProduct
	@ProductID INT,
	@NewName VARCHAR(20)
AS
UPDATE Product
SET Name = @NewName
WHERE ID = @ProductID;

EXEC RenameProduct 201, ‘Spanner’;

Question 16

SQL - What is an index?

Answer 16

An index helps you search for data in a table. When you create an index in a database, you specify a column from the table, and the index contains a copy of this data in a sorted order, with pointers to the corresponding rows in the table.

CREATE INDEX idx_ProductName
ON Product(Name);