SQL Clauses
Learning Objectives:
By the end of this lesson, learners should be able to:
Describe the Order By, Group By, Having, and Limit clauses.
Explain the Like and Between Operators.
Demonstrate the Order By, Group By, Having, and Limit clauses.
SQL Clauses
Table of Contents
Overview of ORDER BY Clause
GROUP BY Clause.
HAVING Clause.
Important Facts About the Where and Having Clauses.
Subqueries in the HAVING Clause.
LIMIT Operator.
LIKE Operator.
BETWEEN Operator.
Prerequisite
Note: We will utilize the classicmodels database for demonstrations and examples in this presentation.
Overview of ORDER BY Clause
The ORDER BY clause is used to sort the records in your result set. When executing the SELECT statement with an ORDER BY clause, SQL always evaluates the ORDER BY clause after the FROM and SELECT. The syntax is as follows:
ASC: Optional. ASC sorts the result set in ascending order by expression (default, if no modifier is provider).
DESC: Optional. DESC sorts the result set in descending order by expression.
Example 1: ORDER BY Clause
In this example, we will use the customers table from the classicmodels database for the demonstration. The following query uses the ORDER BY clause to sort the customers by their last names in ascending order.
If you want to sort customers by the last name in descending order, you can use the DESC attribute contactLastname column in the ORDER BY clause as shown in the following query:
Example 2: ORDER BY Clause
Sort the result by multiple columns. If you want to sort the customers by the last name in descending order, and then by the first name in ascending order, you will specify both DESC and ASC attributes in these respective columns as follows:
Example 3: ORDER BY Clause
Sorting by relative position. You can also use the ORDER BY Clause to sort by relative position in the result set, where the first field in the result set is 1, and the next field is 2, and so on:
Hands-On LAB
Complete the lab GLab - 304.4.1 - ORDER BY Clause, you can find this Lab on Canva under the Assignment section. If you have any technical questions while performing the lab activity, ask your instructors for assistance.
GROUP BY Clause
The GROUP BY clause groups a set of rows into a set of summary rows by values of columns. The GROUP BY clause returns one row for each group.
We often use the GROUP BY clause with aggregate functions such as SUM(), AVG(), MAX(), MIN(), COUNT(), and etc.
The GROUP BY clause is an optional clause of the SELECT statement.
GROUP BY clause syntax:
Example: GROUP BY Without Aggregate Function
The following queries will return the same result. When we use the GROUP BY clause in the SELECT statement without using aggregate functions, it would behave like the DISTINCT clause.
Example: GROUP BY Clause
Suppose you want to group values of the order’s status into subgroups. To do so, use the GROUP BY clause with the status column as the following query:
If you want to know the number of orders in each status, you can use the COUNT() function with the GROUP BY clause as follows:
HAVING Clause
The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
SQL evaluates the HAVING clause after the FROM, WHERE, SELECT, and GROUP BY clauses, and before ORDER BY and LIMIT clauses.
Syntax of the HAVING clause:
The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.
Notice that the HAVING clause applies a filter condition to each group of rows while the WHERE clause applies the filter condition to each individual row.
Example: Choosing Groups - HAVING
The following query uses the GROUP BY clause to get order numbers, the number of items sold per order, and total sales for each from the orderdetails table:
Now you can find which order has a total sale greater than 20,000 by using the HAVING clause as follows:
Example: Choosing Groups - HAVING (continued)
You can construct a complex condition in the HAVING clause by using logical operators such as OR and AND.
Important Facts About the Where and Having Clauses
We cannot use aggregate functions within the WHERE clause. The query below will generate an error:
The WHERE clause filters individual rows and cannot be used with multi-row functions. Instead, we must use the HAVING clause, as shown in the query below:
This gives us the expected single row.
Subqueries in the HAVING Clause
The HAVING clause supports the use of subqueries. For example, our previous query is a bit fragile – as more amounts are added to the payment table, the average amount will change and our query will be invalid due to the hard-coded value. We can make it dynamic by using a subquery.
LIMIT Operator
The LIMIT operator is used in the SELECT statement to constrain the number of rows to return. The LIMIT operator accepts one or two arguments. The values of both arguments must be zero or positive integers.
Syntax: LIMIT Operator
In this syntax:
The
offset
specifies the offset of the first row to return. The offset of the first row is 0, not 1.The
row_count
specifies the maximum number of rows to return.
The following picture illustrates the LIMIT clause:
21
Example: LIMIT Operator
Output:
22
LIMIT Operator and ORDER BY Clause
By default, the SELECT statement returns rows in an unspecified order. When you add the LIMIT operator to the SELECT statement, the returned rows are unpredictable.
Therefore, to ensure that the LIMIT operator returns an expected output, you should always use it with an ORDER BY clause.
Example
In this example:
First, the ORDER BY clause sorts the customers by credits in high to low.
Then, the LIMIT operator returns the first 5 rows.
23
Example: The LIMIT and ORDER BY Clause
The following query finds the customer who has the second-highest credit limit.
Output
24
LIKE Operator
The LIKE operator is used in the WHERE clause with SELECT, DELETE, and UPDATE statements to filter data based on patterns or searches for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:
The percent sign
%
represents zero, one, or multiple characters.The underscore
_
wildcard matches any single character.
For example,
s%
matches any string starts with the character “s” (e.g., sun or six).The
se_
matches any string that starts with “se” and is followed by any character (e.g., see or sea).
Syntax
LIKE Operator Wildcards
Like operator | Description |
---|---|
% | Represents zero, one, or multiple characters. |
_ | Matches any single character. |
Example: LIKE Operator Wildcards
The following SQL statement selects all customers with a CustomerName
starting with "a":
The following SQL statement selects all customers with a CustomerName
that have “or" in any position:
27
Example: LIKE Operator Wildcards (continued)
The following SQL statement selects all customers with a contactFirstName
that starts with "a" and ends with "o":
In the below query, we used the LIKE clause to find employees whose last names end with “on” (e.g., Patterson, Thompson)
Practice Assignment
This assignment will be administered through HackerRank. Make sure to select the MySQL database in the drop-down box above where you enter your code.
Note: Use your office hours to complete this assignment. If you have any technical questions while performing the practice assignment activity, ask your instructors for assistance.
BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN clause Syntax
Example 1: BETWEEN Operator
To find the products whose buy prices are NOT between $20 and $100, you can use the NOT BETWEEN operator as follows:
The following example uses the BETWEEN operator to find products whose buy prices are between $90 and $100.
Example 2: BETWEEN Operator
The following SQL statement selects all products with a price BETWEEN $10 and $20, and no products with a productLine of 'S10_1678' or 'S10_1949':
Output:
Example 3: Operator
Output:
Practice Assignment
Complete the assignment PA 304.4.1 - Practice Assignment - Simple Queries. You can find this assignment on Canva under the Assignment section. Note: Use your office hours to complete this assignment. If you have any technical questions while performing the assignment activity, ask your instructors for assistance.
Knowledge Check
How does the ORDER BY Clause work in SQL?
When do we use a HAVING clause?
Which is the default order of Sort in the ORDER BY Clause?
What is the meaning of the GROUP BY Clause in Mysql?
What SQL clause is used to restrict the rows returned by a query?
What is the difference between percent sign (%) and the underscore (_) for pattern matching (e.g. in the LIKE operator)?
Summary
The MySQL ORDER BY clause is used to sort the records in a result set.
The MySQL WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.
The MySQL GROUP BY clause is used in a SELECT statements to collect data across multiple records and group the results by one or more columns.
The MySQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.
The MySQL LIMIT clause is used to retrieve records from one or more tables in MySQL and limit the number of records returned based on a limit value.
The MySQL BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.
The MySQL LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.
MySQL system clauses are keywords or statements to handle information. It helps to operate a group of the data and apply it to require conditions. The clauses apply conditions or select patterns to get information. MySQL clauses are not used to insert new data. You retrieve data using several clauses. The table uses either single or multiple clauses.
References
Questions?
Last updated