Querying MySQL, MariaDB, and PostgreSQL
Queries written to interact with MySQL, MariaDB, and PostgreSQL databases are very similar to ones written in T-SQL. The following example is nearly identical to the previous T-SQL query, with one key difference.
SELECT P.EnglishProductName
,SUM(FIS.OrderQuantity) AS TotalQuantity
,SUM(FIS.SalesAmount) AS TotalSales
FROM [dbo].[FactInternetSales] AS FIS
JOIN [dbo].[DimProduct] AS P
ON FIS.ProductKey = P.ProductKey
WHERE ProductStandardCost> 1000
GROUP BY P.EnglishProductName
ORDER BY TotalSales DESC
LIMIT 10
The SQL dialects used by MySQL, MariaDB, and PostgreSQL do not use the TOP(n) command to retrieve the first n number of rows that are returned by a query. Instead, these dialects use the LIMIT command to limit the number of rows returned.
Keep in mind that queries written to retrieve and manipulate data stored in one of these database engines will need to be done from a tool that can connect to them. MySQL Workbench is a graphical tool that is like SSMS that can be used to connect to MySQL and MariaDB databases. Queries developed for PostgreSQL databases can be done using the graphical tool pgAdmin.
Summary
The “relational data on Azure” objective of the DP-900 exam focuses on building a foundational understanding of common relational database workloads and database structures. It focuses on the different types of relational database offerings in Azure, along with deployment, security, and development considerations for them.
This chapter covered the following concepts:
Describe relational data workloads. Relational data workloads can be split between transactional and analytical. Transactional, or OLTP, workloads store interactions that are related to an organization’s activities, such as retail purchases. Databases such as SQL Server and Azure SQL Database include mechanisms for managing concurrent transactions to maintain ACID compliancy. Unlike OLTP workloads that are focused on optimizing database writes, analytical workloads are optimized for read-heavy applications. Analytical databases are flattened for this reason, so that users reading data do not have to write overly complex queries to query data.
Describe relational Azure data services. There are several relational database options on Azure that are designed to meet any organizational need. The Azure SQL portfolio of products include relational database offerings that use the Microsoft SQL Server database engine. These include SQL Server on Azure Virtual Machine, Azure SQL Managed Instance, and Azure SQL Database. Organizations needing horizontal scale for data warehouse and big data analytics workloads can use an Azure Synapse Analytics dedicated SQL pool. Azure Database for PostgreSQL, Azure Database for MariaDB, and Azure Database for MySQL enable organizations to offload infrastructure and management of their on-premises open-source relational database footprint to Azure.
Describe common management tasks for relational databases in Azure. Relational databases hosted in Azure remove tedious activities associated with managing infrastructure, allowing organizations to spend more time on building solutions that provide valuable insights. However, there are still several management activities that need to be maintained by an administrator, such as automating environment deployments and managing security. For this reason, Azure provides various options for organizations to automated database deployments that are both flexible and highly scalable. Security is also provided at multiple layers in both Azure and in the database engine. These can be categorized by network isolation, access management, data encryption and obfuscation, and security management. There are also several tools provided by Microsoft that allow database administrators and developers to easily perform the activities required to maintain a highly performant relational database solution.
Describe common query techniques. The Structured Query Language, or SQL for short, is an ANSI/ISO-compliant development language that is used to interact with relational data. SQL commands can be categorized into four different types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL). Of these, DDL and DML statements are the most important to understand for the DP-900 exam. While ANSI SQL is the standard that all major RDBMSs use, most of them extend the language with some custom functionality. For example, Transact-SQL (T-SQL) is the implementation of SQL that is used by SQL Server–based relational databases.