MODULE 4
MySQL Aliases, LIMIT, MIN and MAX, COUNT, AVERAGE, SUM, LIKE, Wildcards,
IN and BETWEEN
A. Rationale
MySQL's aliases, LIMIT, MIN and MAX, COUNT, AVERAGE, SUM, LIKE, wildcards,
IN, and BETWEEN functions represent essential elements for efficient database querying and
data manipulation. Aliases allow for the assignment of alternative names to columns or tables,
enhancing readability and simplifying complex queries. The LIMIT clause facilitates result
set restriction, optimizing query performance by limiting the number of returned rows.
Functions such as MIN, MAX, COUNT, AVERAGE, and SUM enable statistical analysis and
aggregation of data within queries, offering insights into dataset characteristics. The LIKE
operator, along with wildcards, enables pattern matching in string comparisons, facilitating
flexible search functionality. Additionally, the IN and BETWEEN operators enhance query
flexibility by specifying multiple value options or range conditions within queries. These
features collectively empower MySQL users to perform sophisticated data retrieval and
analysis tasks with ease and precision.
B. Objectives
1. Recognize the purpose and utility of MySQL aliases, LIMIT clause, and aggregate
functions (MIN, MAX, COUNT, AVERAGE, SUM) in database querying and data
analysis tasks.
2. Implement MySQL aliases, LIMIT clause, and aggregate functions effectively to
refine and optimize database queries for improved performance and accuracy.
3. Evaluate the usage of MySQL LIKE operator with wildcards and the IN and
BETWEEN operators to perform advanced pattern matching and conditional filtering
within queries.
4. Develop MySQL queries utilizing aliases, LIMIT, aggregate functions, LIKE,
wildcards, IN, and BETWEEN operators to extract and manipulate data according to
specified criteria, demonstrating proficiency in database manipulation techniques.
C. Pre-Test
1. How do aliases, LIMIT, and aggregate functions (MIN, MAX, COUNT, AVERAGE,
SUM) contribute to efficient data retrieval and analysis in MySQL databases?
2. What role do MySQL LIKE operator, wildcards, and the IN and BETWEEN operators
play in enhancing search and filtering capabilities within database queries?
3. In what ways can understanding MySQL aliases, LIMIT, aggregate functions, LIKE,
wildcards, IN, and BETWEEN operators improve the precision and performance of
database queries and data manipulation tasks?
D. Learning Activities
MySQL Aliases
Aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
1|Module 4 - MySQL
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax
Alias Table Syntax
Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one
for the CustomerName column:
The following SQL statement creates two aliases, one for the CustomerName column and one
for the ContactName column. Note: Single or double quotation marks are required if the alias
name contains spaces:
The following SQL statement creates an alias named "Address" that combine four columns
(Address, PostalCode, City and Country):
Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4
(Around the Horn). We use the "Customers" and "Orders" tables, and give them the table
aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):
The following SQL statement is the same as above, but without aliases:
2|Module 4 - MySQL
Aliases can be useful when:
1. There are more than one table involved in a query
2. Functions are used in the query
3. Column names are big or not very readable
4. Two or more columns are combined together
MySQL LIMIT Clause
The LIMIT clause is used to specify the number of records to return.
The LIMIT clause is useful on large tables with thousands of records. Returning a large
number of records can impact performance.
LIMIT Syntax
MySQL LIMIT Examples
The following SQL statement selects the first three records from the "Customers" table:
What if we want to select records 4 - 6 (inclusive)?
MySQL provides a way to handle this: by using OFFSET.
The SQL query below says "return only 3 records, start on record 4 (OFFSET 3)":
ADD a WHERE CLAUSE
The following SQL statement selects the first three records from the "Customers" table,
where the country is "Germany":
3|Module 4 - MySQL
MySQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
MIN() Syntax
MAX() Syntax
MIN() Example
The following SQL statement finds the price of the cheapest product:
MAX() Example
The following SQL statement finds the price of the most expensive product:
MySQL COUNT(), AVG() and SUM() Functions
The COUNT() function returns the number of rows that matches a specified criterion.
COUNT() Syntax
The AVG() function returns the average value of a numeric column.
AVG() Syntax
4|Module 4 - MySQL
The SUM() function returns the total sum of a numeric column.
SUM() Syntax
COUNT() Example
The following SQL statement finds the number of products:
Note: NULL values are not counted.
AVG() Example
The following SQL statement finds the average price of all products:
Note: NULL values are ignored.
SUM() Example
The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails"
table:
Note: NULL values are ignored.
MySQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
1. The percent sign (%) represents zero, one, or multiple characters
2. The underscore sign (_) represents one, single character
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
5|Module 4 - MySQL
Tip: You can also combine any number of conditions using AND or OR operators.
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
SQL LIKE Examples
The following SQL statement selects all customers with a CustomerName starting with "a":
The following SQL statement selects all customers with a CustomerName ending with "a":
The following SQL statement selects all customers with a CustomerName that have "or" in
any position:
The following SQL statement selects all customers with a CustomerName that have "r" in the
second position:
The following SQL statement selects all customers with a CustomerName that starts with "a"
and are at least 3 characters in length:
The following SQL statement selects all customers with a ContactName that starts with "a"
and ends with "o":
6|Module 4 - MySQL
The following SQL statement selects all customers with a CustomerName that does NOT
start with "a":
MySQL Wildcard Characters
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a
WHERE clause to search for a specified pattern in a column.
Wildcard Characters in MySQL
The wildcards can also be used in combinations!
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
Using the % Wildcard
The following SQL statement selects all customers with a City starting with "ber":
The following SQL statement selects all customers with a City containing the pattern "es":
7|Module 4 - MySQL
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with any character,
followed by "ondon":
The following SQL statement selects all customers with a City starting with "L", followed by
any character, followed by "n", followed by any character, followed by "on":
MySQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
Or:
IN Operator Examples
The following SQL statement selects all customers that are located in "Germany", "France"
or "UK":
The following SQL statement selects all customers that are NOT located in "Germany",
"France" or "UK":
8|Module 4 - MySQL
The following SQL statement selects all customers that are from the same countries as the
suppliers:
MySQL 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 Syntax
BETWEEN Example
The following SQL statement selects all products with a price between 10 and 20:
NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:
BETWEEN with IN Example
The following SQL statement selects all products with a price between 10 and 20. In addition;
do not show products with a CategoryID of 1,2, or 3:
BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName between "Carnarvon
Tigers" and "Mozzarella di Giovanni":
9|Module 4 - MySQL
The following SQL statement selects all products with a ProductName between "Carnarvon
Tigers" and "Chef Anton's Cajun Seasoning":
NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName not between
"Carnarvon Tigers" and "Mozzarella di Giovanni":
BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate between '01-July-1996'
and '31-July-1996':
E. Formative Test
F. References
1. [Link]
2. [Link]
3. [Link]
4. [Link]
5. [Link]
6. [Link]
7. [Link]
8. [Link]
10 | M o d u l e 4 - M y S Q L