The Data Manipulation Language (DML) category of SQL commands are the ones most used when querying, modifying, and managing data. DML operations are typically executed on rows, columns, and tables. The Data Manipulation Language (DML) consists of the most well‐known SQL commands, like INSERT, UPDATE, DELETE, SELECT, WHERE, SET, and FROM. There are many DML commands, so not all of them are covered in depth in this chapter. Note that you are expected to know the basic DML commands already; therefore, we won’t discuss them. But here is a list just so you know what the basic DML commands are: AND, AS, BETWEEN, COMMIT, DELETE, FROM, GROUP BY, IN, INSERT, INTO, LIKE, MERGE, OR, ORDER BY, PARSE, ROLLBACK, SELECT, SET, UPDATE, and WHERE.
You do need to know both basic and advanced DML commands and SQL functions for the DP‐203 exam. Some of the more complicated and relevant DML commands are mentioned in this book. The command statements and functions covered here are enough to know for the exam and enough to get you skilled up to a level where you can create and perform some very sophisticated data analysis. Read on to learn many of the more complex DML command statements and functions.
Command Statements and Functions
There are different groupings of SQL syntax, some of which you have already read about, like DBCC, DDL, and DML. When you directly query data, the syntax you use would typically fall into the DML category of commands—for example, BULK INSERT, CASE, and CROSS APPLY. When you want to perform some calculation or transformation during the execution of the query, you would then use a function—for example, AVG, COALESCE, CAST, DATEDIFF, MAX, OPENJSON, and COUNT. There are numerous groupings of functions; two of the most common are aggregate functions and Windows functions. Aggregate functions are those you are probably most familiar with, such as AVG, MAX, MIN, and SUM. You can use them along with GROUP BY, as in the following example:
SELECT CONVERT(VARCHAR(50), [READING_DATETIME], 23) AS READINGDATE,
AVG([VALUE]) AS Average, MAX([VALUE]) AS Maximum,
MIN([VALUE]) AS Minimum, SUM([VALUE]) AS Sum, COUNT(*) AS COUNT
FROM [dbo].[READING]
WHERE [ELECTRODE_ID] = 1 AND [FREQUENCY_ID] = 1
GROUP BY [SESSION_ID], CONVERT(VARCHAR(50), [READING_DATETIME], 23)
ORDER BY CONVERT(VARCHAR(50), [READING_DATETIME], 23)
That query calculates the average, maximum, minimum, and total brainwave values per session, by reading date, for electrode AF3 and frequency THETA. CONVERT is discussed in more detail later. The following table illustrates the output of the aggregate function query.
Converting the aggregate function into a Windows function requires the implementation of the OVER clause. The following query illustrates how this is achieved, followed by the results: SELECT CONVERT(VARCHAR(50), [READING_DATETIME], 23) AS READINGDATE, [VALUE],
AVG([VALUE]) OVER (PARTITION BY [SESSION_ID], CONVERT(VARCHAR(50),
[READING_DATETIME], 23)) AS Average,
MAX([VALUE]) OVER (PARTITION BY [SESSION_ID], CONVERT(VARCHAR(50),
[READING_DATETIME], 23)) AS Maximum,
MIN([VALUE]) OVER (PARTITION BY [SESSION_ID], CONVERT(VARCHAR(50),
[READING_DATETIME], 23)) AS Minimum,
SUM([VALUE]) OVER (PARTITION BY [SESSION_ID], CONVERT(VARCHAR(50),
[READING_DATETIME], 23)) AS Total
FROM [dbo].[READING]
WHERE [ELECTRODE_ID] = 1 AND [FREQUENCY_ID] = 1
ORDER BY CONVERT(VARCHAR(50), [READING_DATETIME], 23)
As you can see in the first table, the aggregate function performed the calculations using values based on the GROUP BY clause. A Windows function does not employ a GROUP BY—it instead calculates the values for each row based on the parameters used with OVER and PARTITION BY. Notice that the parameters that come after PARTITION BY for the Windows function are the same as the GROUP BY clause used in the aggregate function. Windows functions employ what is called a window frame. Each row rendered using a Windows function has an associated frame that provides the capacity to perform ranking, aggregation, and analytics on each row value based on a group of rows. The remaining content in this section provides some information about many of the common mid‐ to advanced level SQL command statements and SQL functions. You’ll learn more about the OVER clause and Windows functions in the section “Data Manipulation Language (DML),” later in this chapter.
