SQL Advanced

Setup

Overview

Time: 0 min
Objectives

  • Open the W3schools SQL Tryit Editor v.1.6

  • Explore the sample database included with the SQL Tryit Editor

Workshop Setup

There is no setup required for this workshop. This workshop will utilize W3schools “SQL Try it Editor v1.6” for all exercises practicing SQL queries. Please follow the link below to access the SQL Tryit Editor in your browser.

SQL Tryit Editor v1.6

About the Data Used in this Workshop:

The database and tables used in the examples are from the W3Schools “SQL Editor”. These databases are present to all the users and can bee used to implement the concepts that are discussed in this workshop.

W3Schools Tryit Editor Database

Key Points

Selection Queries

Overview

Time: 15 min
Objectives

  • Review of basic SQL queries and introduction to advanced queries and keywords

Quick Review

NULL

The NULL function can be used in the WHERE clause to identify rows which have null values. For example, consider the below “person” table.

Syntax

SELECT column
FROM table_name
WHERE column IS NOT NULL

Example

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

SQ1

BETWEEN

The BETWEEN function is used to specify a range of values. Both the start and end values are inclusive in the range. It can be used along with numbers, text, or dates.

Syntax

SELECT column(s)...................
FROM table_name
WHERE column_to_filter_on BETWEENs tart_range AND end_range

Example

SELECT ProductID, ProductName, Price
FROM Products
WHERE Price BETWEEN 10 AND 14
ORDER BY 3

SQ2

LIKE

The LIKE function is used to search the columns using the specified pattern. • % - Multiple characters • _ - Single Character Syntax

Syntax

SELECT column(s)...................
FROM table_name
WHERE column_to_filter_on LIKE pattern

Example

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName LIKE 'a%'

SQ3 SQ4

SUBQUERY

A subquery is also a query, but the output of the subquery is used as input to another query to fetch the necessary results. A subquery is always enclosed inside a parenthesis ().

For example, we can fetch the maximum value in a column using he below query

SELECT MAX(price) FROM Products

SQ5

The result from the above query can be used as input to another query to filter out the particular rows matching the above result.

SELECT ProductID, ProductName, Unit, Price
From Products
WHERE price = (SELECT MAX(price) FROM Products)

SQ6

IN

When the results of a subquery has a single value we can use the equal to (=) sign to match the values. When the subquery has more than one value in it’s result we can use the IN function. The IN functions checks if the results of the primary query match any one of the results of the subquery.

Consider the below two tables: PRODUCTS – Contains details about the various products in a store ORDERS – Contains details about the various orders, one line per product per order

SQ7

In order to select all the OrderID which contains a product whose price is greater than 50:

Step 1 : Select the ProductID from PRODUCTS table whose price is greater than 50

SELECT ProductID FROM Products
Where Price > 50

SQ8

Step 2 : Select all records from ORDERS table where the ProductID contains any one of the values present above

SELECT *
From OrderDetails
WHERE ProductID IN (SELECT ProductID FROM Products Where Price > 50)

SQ9

Key Points

  • Introduction to keywords like NULL, BETWEEN, LIKE, SUBQUERY and IN

Aggregate Function

Overview

Time: 10 min
Objectives

  • Introduction to Aggregate Functions

GROUP BY & COUNT

The GROUP BY function is used to group (or) combine similar entries in one column into a single record. This function helps identify all the unique values under a column and helps provides statistical results based on the combined aggregation function (discussed next). The GROUP BY function is generally combined with an aggregate function and one such function is COUNT. The COUNT function when used in combination with GROUP BY provides the count of number of times each vale appears in a column.

Syntax

SELECT column_to_group_by, AGGREGATE_FUNCTION(column),...................
FROM table_name
GROUP BY column_to_group_by

Example

Consider the below CUSTOMERS table with a few sample records

AF1

SELECT Country, Count(CustomerID) AS CT
FROM Customers
GROUP BY Country
ORDER BY 2 DESC

AF2

AGGREGATE FUNCTIONS

The aggregate functions are generally used in combination with the GROUP BY function to perform the required aggregation function on the required column. The various aggregation functions are:

Syntax

SELECT column_to_group_by, AGGREGATE_FUNCTION(column),...................
FROM table_name
GROUP BY column_to_group_by

Example

Consider the below PRODUCTS table with a few sample records AF3

SELECT CategoryID, SUM(Price) AS Sum_Price, MAX(Price) AS Max_Price, COUNT(Price) as CT
FROM Products
GROUP BY 1

AF4

Key Points

  • Familiarize with the aggregate function along with examples

Conditional Functions

Overview

Time: 15 min
Objectives

  • Introduction to Conditional Functions

FIXME

HAVING

The HAVING function performs a similar function as WHERE function and helps to filter out data in the result. The AGGREGATE functions cannot be used in WHERE clause and can only be used in the HAVING clause. Thus HAVING clause is generally preceded by the GROUP BY function.

CF1

Syntax

SELECT column
FROM table_name
GROUP BY column
HAVING condition

Example

SELECT CategoryID, SUM(Price) AS Sum_Price
FROM Products
GROUP BY 1

CF2

SELECT CategoryID, SUM(Price) AS Sum_Price
FROM Products
GROUP BY 1
HAVING SUM(Price) > 300

CF3

CASE

The CASE function/clause is used to perform an action based on a condition(s).It is similar to SWITCH CASE statement in programming languages

Syntax

SELECT column1, column2,
CASE WHEN condition1 THEN action1
WHEN condition2 THEN action2
ELSE action3 END
FROM table_name

Example

SELECT CategoryID,
CASE WHEN CategoryID < 4 THEN "Less than 4"
WHEN CategoryID = 4 THEN "Equal to 4"
ELSE "Greater than 4" END AS ID_Text
FROM Categories

CF4

IF (MySQL)

The IF functions is similar to the CASE function but performs only one of two actions based on a condition. If the condition is true then one action is performed else it automatically performs the other action.

Note: The IF function is available in MySQL and not the standard SQL language

Syntax

SELECT IF(condition, action_if_condition_true, action_if_condition_false)
FROM table_name

Example

SELECT DISTINCT Quantity, IF(Quantity>4, "MORE", "LESS") AS Quantity_String
FROM OrderDetails

CF5

Key Points

  • Familiarize with the Conditional functions like HAVING, CASE, IF along with examples

Window Function

Overview

Time: 10 min
Objectives

  • Introduction to Window Functions

FIXME

WINDOW FUNCTIONS

The window function is similar to an aggregate function, but it splits (partitions) the data into multiple groups based on the query and then performs an aggregation (or) function on these segments and returns a value for each row in the table.

WF1

The window function is used mostly in the select statement, partitions the data and perform some function across each partition. Some common window functions are: •OVER •ROW_NUMBER() •RANK() •DENSE_RANK()

Syntax

SELECT column(s), FUNCTION(column) OVER(PARTITION BY column ORDER BY column) FROM table_name

For example, consider a table q1 sales which has quarter1 sales details of a company. To find the average sales of the entire quarter:

WF2

OVER

The over function helps to print the result of the aggregate function for each record in the final output. Thus, the final output is not just one row which gives the aggregation result, but rather the entire table selected along with a new column where the aggregation result is printed once for each row.

WF3

Thus, the overall average of the sales column has been calculated and the result is presented in a new column called “avgsales”. All the records in this column comprise of the same value which is the average sales seen in the previous query.

PARTITIONBY

The PARTITIONBY function is used to split the data into multiple partitions and then perform the aggregation function on each of these partitions.

WF4

In the above example, the dataset is partitioned based on dealer_id.

WF5

ORDER BY

The ORDER BY clause helps to order the results in ascending or descending order inside each partition.

WF6

Key Points

  • Understanding the concepts of Window Functions like OVER, PARTITION, ORDER BY