This function returns the coefficient of correlation when passed a pair of numbers. CORR will determine if a relationship exists between the pair of values it receives. The result is a range from −1 to 1 where either ±1 means there is a correlation between the two numbers, and a 0 means there is no […]
This function is used in combination with OPENROWSET and instructs the runtime about the format of the content being retrieved. In addition to OPENJSON, we can use OPENXML and OPENQUERY, which are used in the same context but on different file formats or to run SQL queries directly against a database. The following query illustrates […]
When you write code or a query, you typically do so on a workstation on your desk or via a browser. If the code needs to make a connection to a database to retrieve and parse some data, it is important to decide where the parsing should take place. There are two places where the […]
These are some of the most common aggregate SQL functions. You saw them in the previous section. You can use these functions to calculate average, maximum, minimum, and total of numeric column values on one or more tables. The COUNT function returns the number of rows that match the SQL statement criteria. Note that after […]
CONVERT and CAST are essentially the same—there is no difference between their capabilities or performance. They both exist solely for historical reasons, not for any functional ones. As long as you understand that both of these SQL functions are used to change the data type of data stored in a table, you have this one […]
This is a relational structure‐oriented concept that has to do with querying data that exists in two or more tables using a single query. It is possible to use JOINs on NoSQL data, but the nature of nonstructured or semi‐structured means it won’t be a very performant experience. If the JOIN on non‐ or unstructured […]
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 […]
A Cartesian JOIN, or a CROSS JOIN, renders a Cartesian product, which is a record set of two or more joined tables. The following snippet is an example of a CROSS JOIN: SELECT [ELECTRODE], [FREQUENCY]FROM [ELECTRODE], [FREQUENCY]ORDER BY ELECTRODE Notice that there is no JOIN condition, which results in each row in the ELECTRODE table […]
The UNION command combines the data from two or more tables without adding any additional rows. This is best understood visually, so consider the following SQL statement: SELECT SESSION_ID AS ID, CONVERT(VARCHAR(50),SESSION_DATETIME, 127) AS DATE_SCENARIOFROM [SESSION]UNIONSELECT SCENARIO_ID, SCENARIOFROM SCENARIO The result would be something similar to the following. Notice that the rendered output is a […]
This command will display the current optimization statistics for a table or view. Before you can show the statistics, you first need to create them. The following snippet illustrates how this is done. Provide a statistics name, the table (e.g., READING), and the column (e.g., VALUE) for which you want to capture statistics: CREATE STATISTICS […]
