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:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY
   column1 [ASC|DESC],
   column2 [ASC|DESC],
   ...;

  • 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.

SELECT
    contactLastname,
    contactFirstname
FROM
    customers
ORDER BY
    contactLastname;

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:

SELECT
    contactLastname,
    contactFirstname
FROM
    customers
ORDER BY
    contactLastname DESC;

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:

SELECT
    contactLastname,
    contactFirstname
FROM
    customers
ORDER BY
    contactLastname DESC ,
    contactFirstname ASC;

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:

SELECT
    contactLastname,
    contactFirstname,
    city
FROM
    customers
ORDER BY
    2 DESC;

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:

SELECT c1, c2,..., cn, aggregate_function(ci)
FROM table
WHERE where_conditions
GROUP BY c1 , c2,...,cn;

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.

SELECT Country
FROM customers
GROUP BY country;

SELECT DISTINCT(Country)
FROM customers;

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:

SELECT status FROM orders GROUP BY status;

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:

SELECT status, COUNT(*) FROM orders GROUP BY status;

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.

SELECT select_list FROM table_name WHERE search_condition
GROUP BY group_by_expression HAVING group_condition;

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:

SELECT ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY
    ordernumber;

Now you can find which order has a total sale greater than 20,000 by using the HAVING clause as follows:

SELECT ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY
    ordernumber
HAVING
    total > 20000;

Example: Choosing Groups - HAVING (continued)

You can construct a complex condition in the HAVING clause by using logical operators such as OR and AND.

SELECT ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY
    ordernumber
HAVING
    total > 1000 AND itemsCount > 600;

Important Facts About the Where and Having Clauses

We cannot use aggregate functions within the WHERE clause. The query below will generate an error:

ERROR CODE 1111. Invalid use of group function.
Select customerNumber, avg(amount) as avgAmount from payments p
Where avg(amount)>1000
group by customerNumber;

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:

Select customerNumber, avg(amount) as avgAmount
from payments p
group by customerNumber
having avg(amount)>1000;

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.

Select customerNumber, avg(amount)  as avgAmount
from payments p1
group by customerNumber
having avg(amount) > (
    Select avg(amount) from payments p2
) order by 2 desc;

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

SELECT COLUMN_NAME FROM table_name
LIMIT [offset,] row_count;

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

SELECT customerNumber, customerName, creditLimit
FROM customers
ORDER BY creditLimit DESC
LIMIT 5,2;

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

SELECT
    customerNumber,
    customerName,
    creditLimit
FROM   customers
ORDER BY creditLimit DESC
LIMIT 5;

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.

SELECT
    customerName,
    creditLimit
FROM    customers
ORDER BY     creditLimit DESC
LIMIT 1,1;

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

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

LIKE Operator Wildcards

Like operatorDescription

%

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":

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

The following SQL statement selects all customers with a CustomerName that have “or" in any position:

SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

27

Example: LIKE Operator Wildcards (continued)

The following SQL statement selects all customers with a contactFirstName that starts with "a" and ends with "o":

SELECT * FROM Customers
WHERE contactFirstName  LIKE 'a%o';

In the below query, we used the LIKE clause to find employees whose last names end with “on” (e.g., Patterson, Thompson)

SELECT employeeNumber, lastName, firstName FROM employees
WHERE lastName LIKE '%on';

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

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

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:

SELECT productCode, productName, buyPrice
FROM products
WHERE buyPrice NOT BETWEEN 20 AND 100;

The following example uses the BETWEEN operator to find products whose buy prices are between $90 and $100.

SELECT productCode, productName, buyPrice
FROM products
WHERE buyPrice 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':

SELECT * FROM Products WHERE (buyPrice BETWEEN 10 AND 20)
AND NOT productLine IN ('S10_1678','S10_1949');

Output:

Example 3: Operator

SELECT * FROM Products
WHERE ProductName
BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

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?

PreviousDDL and Data IntegrityNextAggregate Functions and Operators

Last updated