Group by clause:

• The GROUP BY statement groups rows that

have the same values into summary rows, like
"find the number of customers in each
country".
• The GROUP BY statement is often used with
aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to
group the result-set by one or more columns.
syntax
• SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Having clause
• The HAVING clause was added to SQL because
the WHERE keyword cannot be used with
aggregate functions.
syntax
• SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
 Like operator
• The 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:
• The percent sign (%) represents zero, one, or
multiple characters
• The underscore sign (_) represents one, single
character
syntax
• SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
• Example:
• SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
Mysql update
• The UPDATE statement is used to modify the
existing records in a table.
• UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
syntax
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City
= 'Frankfurt'
WHERE CustomerID = 1;
 SQL Function
• SQL provides many built-in functions to
perform operations on data. These functions
are useful while performing mathematical
calculations, string concatenations, sub-strings
etc. SQL functions are divided into two
categories,
• Aggregate Functions
• Scalar Functions
 Aggregate Functions
• These functions return a single value after
performing calculations on a group of values.
Following are some of the frequently used
Aggregrate functions.
1. AVG() Function
Average returns average value after calculating it
from values in a numeric column.
• Its general syntax is,
• SELECT AVG(column_name) FROM table_name
Eg:SELECT avg(salary) from Emp;
2. COUNT() Function
Count returns the number of rows present in the
table either based on some condition or without
condition.
• Its general syntax is,
• SELECT COUNT(column_name) FROM table-
name.
• Eg:
• SELECT COUNT(name) FROM Emp WHERE salary
= 8000;
• SELECT COUNT(DISTINCT salary) FROM emp;
3.FIRST() Function
• First function returns first value of a selected
column
• Syntax for FIRST function is,
• SELECT FIRST(column_name) FROM table-name;
• Eg:SELECT FIRST(salary) FROM Emp;
4.LAST() Function
• LAST function returns the return last value of the
selected column.
• Syntax of LAST function is,
• SELECT LAST(column_name) FROM table-name;
• SELECT LAST(salary) FROM emp;
5.MAX() Function
• MAX function returns maximum value from
selected column of the table.
• Syntax of MAX function is,
• SELECT MAX(column_name) from table-name;
• Eg:SELECT MAX(salary) FROM emp;
6.MIN() Function
• MIN function returns minimum value from a
selected column of the table.
• Syntax for MIN function is,
• SELECT MIN(column_name) from table-name;
• SELECT MIN(salary) FROM emp;
7.SUM() Function
• SUM function returns total sum of a selected
columns numeric values.
• Syntax for SUM is,
• SELECT SUM(column_name) from table-name;
Eg:SELECT SUM(salary) FROM emp;
 Scalar Functions
• Scalar functions return a single value from an
input value. Following are some frequently
used Scalar Functions in SQL.
1.UCASE() Function
• UCASE function is used to convert value of
string column to Uppercase characters.
• Syntax of UCASE:
• SELECT UCASE(column_name) from table-
name;
Eg:SELECT UCASE(name) FROM emp;
2.LCASE() Function
• LCASE function is used to convert value of string
columns to Lowecase characters.
• Syntax for LCASE is,
• SELECT LCASE(column_name) FROM table-
name;
Eg:SELECT LCASE(name) FROM emp;
3.MID() Function
• MID function is used to extract substrings
from column values of string type in a table.
• Syntax for MID function is,
• SELECT MID(column_name, start, length) from
table-name;
• Eg:SELECT MID(name,2,2) FROM emp;
4.ROUND() Function
• ROUND function is used to round a numeric
field to number of nearest integer. It is used
on Decimal point values.
• Syntax of Round function is,
• SELECT ROUND(column_name, decimals) from
table-name;
• Eg:SELECT ROUND(salary) from emp;
Aggregate functions
• Count function:The COUNT() function returns
the number of rows that matches a specified
criterion.
• SELECT COUNT(column_name)
FROM table_name
WHERE condition;
 Sum function
• The SUM() function returns the total sum of a
numeric column
• SELECT SUM(column_name)
FROM table_name
WHERE condition;
 Average function
• The AVG() function returns the average value
of a numeric column.
• SELECT AVG(column_name)
FROM table_name
WHERE condition;
 Min and max function
• The MIN() function returns the smallest value
of the selected column.
• The MAX() function returns the largest value of
the selected column.
• MIN() Syntax:SELECT MIN(column_name)
FROM table_name
WHERE condition;
• Max syntax:SELECT MAX(column_name)
FROM table_name
WHERE condition;
Task