Functions - Subqueries
Abdallah EL Asmar
Scalar functions
➢ Operate on a single value and then return a single value. Scalar functions can be
used wherever an expression is valid.
▪ String Functions :
▪ Mathematical Functions
▪ Date Functions
▪ Other Functions
Abdallah EL Asmar 2
Scalar functions
▪ String Functions
LEN (string_expression) : Returns the number of characters of the specified string
expression, excluding trailing spaces.
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) :
This function searches for one character expression inside a second character
expression, returning the starting position of the first expression if found
CONCAT ( argument1 , argument2 [ , argumentN ] ... ) : returns a string resulting
from the concatenation, or joining, of two or more string
SUBSTRING ( expression, start, length ) : Returns part of a character expression
SELECT employee_id, concat (SUBSTRING (first_name, 1, 2),
SUBSTRING(job_id, 4, 2) , LEN(last_name)) AS [password]
FROM employees;
Abdallah EL Asmar 3
Scalar functions
▪ Mathematical Functions
ROUND ( numeric_expression , length ) : Returns a numeric value, rounded to the
specified length.
FLOOR ( numeric_expression) : Returns the largest integer less than or equal to
the specified numeric expression.
CEILING ( numeric_expression ) : returns the smallest integer greater than, or
equal to, the specified numeric expression.
SELECT 145.5654 AS Nbr,ROUND(145.5654, 2) AS round_Nbr,
CEILING(145.5654) AS celling_Nbr,FLOOR(145.5654) AS floor_Nbr;
Abdallah EL Asmar 4
➢Scalar functions
▪ Date Functions
GETDATE ( ) : Returns a datetime value containing the date and time of the
computer.
DAY( date ) , MONTH ( date ), YEAR ( date ) : Returns an integer representing
respectively the day, the month, the year part of the specified date.
SELECT employee_id, MONTH(hire_date) AS hire_month
FROM employees
WHERE YEAR(hire_date) = 2018;
▪ Other Functions
ISNULL ( check_expression , replacement_value ) : Replaces NULL values of
check_expression (any type) with the specified replacement value.
SELECT employee_id, salary, salary + (salary *
(ISNULL(commission_pct, 0))) 'Salary + Bonus'
FROM employees;
Abdallah EL Asmar 5
➢Fonctions scalaires
▪ Autres fonctions
CONVERT (data_type(length), expression [, style]) : The CONVERT() function
converts a value (of any type) into a specified datatype.
SELECT CONVERT (INT, 234.89) real_to_int,
CONVERT (INT, '234') char_to_int,
CONVERT (DECIMAL(5,2), 234 ) int_to_real,
CONVERT (DECIMAL(5,2), '234.3' ) char_to_real,
CONVERT (CHAR(10), 356.7) + 'A' real_to_char,
CONVERT (VARCHAR(10), 356.7) + 'A' real_to_varchar
SELECT CONVERT (DATE, '2024-04-10') char_to_date,
CONVERT (DATETIME, '2024-04-10') char_to_datetime,
CONVERT (DATE, GETDATE()) datetime_to_date,
CONVERT (VARCHAR, GETDATE(), 103) datetime_to_char_s,
CONVERT (TIME, GETDATE()) datetime_to_time
Abdallah EL Asmar 6
Aggregate functions
➢ Aggregate functions
➢ The GROUP BY clause
➢ The HAVING clause
Abdallah EL Asmar 7
Aggregate functions
▪ An aggregate function performs a calculation on a set of values and returns a
single value.
▪ The most commonly used SQL aggregate functions are:
Function Description
COUNT ( * ) Number of rows
COUNT ( [DISTINCT] expression) Number of non-NULL values
AVG ( [DISTINCT] expression) Average value
SUM ( [DISTINCT] expression) Sum of values
MAX (expression) Highest value
MIN (expression) Smallest value
Abdallah EL Asmar 8
Aggregate functions
▪ Aggregate functions are often used with the GROUP BY clause of the SELECT
statement. The GROUP BY clause splits the result into groups of values and the
aggregate function can be used to return a single value for each group.
▪ Except for COUNT(*), aggregate functions ignore null values.
▪ Aggregate functions can be used only in the following situations:
o The select list of a SELECT statement (either a subquery or an outer query).
o A HAVING clause.
o An ORDER BY clause
Abdallah EL Asmar 9
Aggregate functions
▪ COUNT ( * ) : Returns the number of rows in a table (or a group), and it
preserves duplicate rows. It counts each row separately. This includes rows that
contain null values.
▪ COUNT ( [DISTINCT] expression) : Returns the number of non-null
values of the expression.
▪ expression of any type, except image, or text. COUNT doesn't support
aggregate functions or subqueries in an expression.
▪ DISTINCT : Specifies that COUNT returns the number of unique nonnull
values.
SELECT COUNT(*) 'Nbr of employees',
COUNT(manager_id) 'Nbr of employees having managers',
COUNT(DISTINCT manager_id) 'Nbr of managers'
FROM employees;
Abdallah EL Asmar 10
Aggregate functions
▪ MAX ( expression ) : Returns the maximum value in the expression.
▪ MIN (expression) : Returns the minimum value in the expression.
▪ expression : Is a constant, column name, or function, and any
combination of arithmetic, and string operators. Aggregate functions and
subqueries are not permitted
▪ MAX and MIN can be used with numeric, character, and datetime
columns.
SELECT MAX(salary) 'Max salary',
MIN(salary) 'Min salary',
MAX(2024 - YEAR(hire_date)) 'Max nbr. of years'
FROM employees;
Abdallah EL Asmar 11
Aggregate functions
▪ AVG ( [DISTINCT] expression ) : Returns the average of the values in the
expression.
▪ SUM ([DISTINCT] expression) : Returns the sum of all the values in the
expression.
▪ expression : An expression of the numeric data type category. Aggregate
functions and subqueries are not permitted.
SELECT AVG(salary) 'Average salary',
AVG(DISTINCT salary) 'Average without duplicate values',
SUM(salary) 'Total sum of salaries',
AVG(salary * commission_pct) 'Average commission',
AVG(salary * ISNULL(commission_pct, 0)) 'Average commission for all'
FROM employees;
Abdallah EL Asmar 12
The GROUP BY clause
▪ This clause divides the query result into groups of rows according to the values of
one or more columns, usually by performing one or more aggregations on each
group. The SELECT statement returns one row per group.
GROUP BY column1 [, column2…]
▪ Each column in any nonaggregate expression in the <select> list must be included
in the GROUP BY list.
▪ Columns used with GROUP BY do not have to appear in the <select> list.
▪ You can't use an alias of a column with GROUP BY.
SELECT job_id, MAX(salary) AS Max_salary
FROM employees
WHERE department_id = 50
GROUP BY job_id
ORDER BY 2 DESC;
Abdallah EL Asmar 13
The HAVING clause
▪ HAVING is typically used with a GROUP BY clause.
▪ Specifies a search condition for a group or an aggregate.
HAVING search_condition
▪ Typically, search condition is an aggregate function comparison with a value.
▪ HAVING can be used only with the SELECT statement. When GROUP BY is not
used, there is an implicit single, aggregated group.
SELECT job_id, MAX(salary) AS Max_salary
FROM employees
WHERE department_id = 50
GROUP BY job_id
HAVING COUNT(*) > 5
ORDER BY 2 DESC;
Abdallah EL Asmar 14
Subquery
➢ What is a subquery
➢ Single-row subqueries
➢ Multiple-row subqueries
➢ Subqueries with DML statements
Abdallah EL Asmar 15
➢ Problem: Suppose we want to write a query to find out who earns a salary greater
than Karen Partners' salary.
➢ Such query can be written :
SELECT e1.first_name, e1.last_name, [Link]
FROM employees e1, employees e2
WHERE [Link] > [Link]
AND e2.first_name = 'Karen' AND e2.last_name = 'Partners’
➢ Other solution, consists to:
▪ Write one query to find how much Karen Partners earns.
▪ Write a second query to find who earns more than the result of the first query,
placing the first query inside the second query.
➢ The inner query (or subquery) returns a value that is used by the outer query (or
main query).
Abdallah EL Asmar 16
Main query (Outer query)
? Which employees have salaries greater than
Karim’s salary?
Subquery (inner query)
? What is Karim’s salary?
Main query (Outer query)
SELECT first_name, last_name
FROM employees
WHERE salary > (SELECT salary Subquery (Inner query)
FROM employees
WHERE first_name = 'Karen’
AND last_name = 'Partners')
Abdallah EL Asmar 17
➢ A subquery is a SELECT statement that is embedded in the clause of another
SELECT statement.
➢ A subquery can be embedded in many clauses, including:
o SELECT clause
o WHERE clause
o HAVING clause
o FROM clause
SELECT select_list
FROM table, (SELECT select_list
FROM table…..) table_alias
WHERE expr operator (SELECT select_list
FROM table…..)
GROUP BY column
HAVING expr operator (SELECT select_list
FROM table…..)
➢ The subquery (inner query) executes before the main query (outer query). The
result of the subquery is used by the main query.
Abdallah EL Asmar 18
Subquery types
➢ Single-row subqueries
▪ Queries that return only one row from the inner SELECT statement
▪ Use single-row comparison operators : < <= > >= = <>
❑ Employees who do the same job as the employee 180 but earn more salary
than him.
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees
WHERE employee_id = 180)
AND salary > (SELECT salary FROM employees
WHERE employee_id = 180);
❑ Employees who earn a salary greater than the average salary.
SELECT last_name, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);
Abdallah EL Asmar 19
Subquery types
➢ Multiple-row subqueries
▪ Queries that return more than one row from the inner SELECT statement
▪ Use multiple-row comparison operators
Operator Meaning
IN Equal to any member in the list
ANY Must be preceded by =, !=, >, <, <=, >=. Returns TRUE if at least
one element exists in the result-set of the Subquery for which the
relation is TRUE.
ALL Must be preceded by =, !=, >, <, <=, >=. Returns TRUE if the
relation is TRUE for all elements in the result set of the Subquery.
❑ Employees who earn more than all the salaries of employees in the
department 80.
SELECT last_name, first_name, salary
FROM employees
WHERE salary >ALL (SELECT salary
FROM employees WHERE department_id = 80);
Abdallah EL Asmar 20
➢ Employees who earn the same salary as the minimum salary for any department.
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
▪ > ANY : Greater than the minimum of the Subquery
▪ < ANY : Smaller than the maximum of the Subquery
▪ = ANY : equivalent to IN
▪ > ALL : Greater than the maximum of the Subquery
▪ < ALL : Smaller than the minimum of the Subquery
▪ <> ALL : equivalent to NOT IN
Abdallah EL Asmar 21
Subqueries and null values
➢ Single-row subquery : If a subquery returns a null value, then the entire query
returns no results because a comparison with NULL returns NULL.
➢ Multiple-row subquery : If a subquery result contains a NULL value, do not use an
operator (NOT IN, >ALL, <ALL) that compares with all values in that subquery,
because the result of the entire query will be NULL.
▪ Logically, the following SQL statement should have returned many rows.
However, the SQL statement does not return any rows. One of the values
returned by the inner query is a null value and, therefore, the entire query
returns no rows.
SELECT last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees);
Abdallah EL Asmar 22
Subqueries used in place of an expression
➢ In Transact-SQL, a subquery can be substituted anywhere an expression can be
used in SELECT, UPDATE, INSERT, and DELETE statements, except in an
ORDER BY list and GROUP BY.
➢ Create a table based on a query
SELECT select_list INTO new_table
[WHERE ……. ];
For example, create a table named Person as a copy of the Employees table:
SELECT * INTO person
FROM employees;
➢ Using a subquery within the DELETE statement
DELETE FROM person
WHERE department_id = 80
AND salary < (SELECT AVG(salary)
FROM person
WHERE department_id = 80);
Abdallah EL Asmar 23
Subqueries used in place of an expression
➢ Using a subquery within the INSERT statement
INSERT INTO table_name subquery;
Insert into Person the query result:
INSERT INTO person
SELECT * FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM person);
➢ Using a subquery within the UPDATE statement
UPDATE person
SET salary = (SELECT AVG(salary) FROM person)
WHERE salary < (SELECT AVG(salary) FROM person);
➢ Using a subquery within the SELECT clause
SELECT employee_id, last_name, salary,
(salary - (SELECT AVG(salary) FROM employees)) difference
FROM employees;
Abdallah EL Asmar 24