1 of 47

Processes and standard functions in SQL

Standard and aggregate functions in SQL. Arguments of aggregate functions. Syntax of standard functions in SQL.

2 of 47

Today's Topic: Functions in SQL

Today, we move beyond basic data retrieval.
We will explore the power of Functions in SQL.
Functions allow us to process and analyze data within the database.
Instead of just retrieving raw data, we can manipulate and summarize it directly.

3 of 47

Functions: Processing Data in the Database

Functions are tools that extend the capabilities of SQL.
They enable us to perform operations on data before we retrieve it.
We will focus on two main categories of functions today:

Standard Functions: Operate on individual data values.
Aggregate Functions: Operate on sets of data values.

4 of 47

Standard Functions in SQL

Built-in SQL functions.
Perform operations on individual data values within a column.
Operate on each row individually.

5 of 47

Categories of Standard Functions

String Functions: Manipulate text data.

Examples: UPPER(), LOWER(), SUBSTR(), LENGTH(), CONCAT()

Numeric Functions: Perform mathematical operations.

Examples: ABS(), ROUND(), CEIL(), FLOOR(), MOD()

Date Functions: Work with dates and times.

Examples: NOW(), DATE(), YEAR(), MONTH(), DAY()

6 of 47

Syntax of Standard Functions

General Syntax:

FUNCTION_NAME(column_name)
FUNCTION_NAME(column_name, argument1, argument2, ...)

The number of arguments depends on the specific function.

7 of 47

Example: UPPER() Function

Hypothetical Students table:

Columns: FirstName, LastName, EnrollmentDate

Example Query:

SELECT UPPER(FirstName), LastName

FROM Students;

This query converts all FirstName values to uppercase.
LastName is displayed as is.

8 of 47

SELECT UPPER(FirstName), LastName�FROM Students;

9 of 47

Function Example: SUBSTR()

Students table (same as before).
Example:

SELECT SUBSTR(LastName, 1, 3) AS ShortLastName

FROM Students;

This extracts the first 3 characters of the LastName and displays them as ShortLastName.

10 of 47

SELECT SUBSTR(LastName, 1, 3) AS ShortLastName�FROM Students;

11 of 47

Numeric Function Example: ROUND()

Assume a GradePointAverage column in the Students table.
Example:

SELECT ROUND(GradePointAverage, 1) AS RoundedGPA

FROM Students;

This rounds the GradePointAverage to 1 decimal place.

12 of 47

SELECT ROUND(GradePointAverage, 1) AS RoundedGPA�FROM Students;

13 of 47

Date Function Example: YEAR()

Students table (with EnrollmentDate).
Example:

SELECT YEAR(EnrollmentDate) AS EnrollmentYear

FROM Students;

This extracts the year from the EnrollmentDate.

14 of 47

SELECT YEAR(EnrollmentDate) AS EnrollmentYear�FROM Students;

15 of 47

Aggregate Functions in SQL

Perform calculations on a set of rows.
Return a single value summarizing the set.
Summarize data across multiple rows.

16 of 47

Key Aggregate Functions

COUNT(): Counts the number of rows.
SUM(): Calculates the sum of values in a column.
AVG(): Calculates the average of values in a column.
MIN(): Finds the minimum value in a column.
MAX(): Finds the maximum value in a column.

17 of 47

Arguments of Aggregate Functions

Typically take a column name as an argument:

AVG(Grade)

COUNT(*): Counts all rows in a table or group.
DISTINCT: Counts only unique values:

COUNT(DISTINCT Major) (Counts unique majors)

18 of 47

Syntax of Aggregate Functions

General Syntax:

AGGREGATE_FUNCTION(column_name)
AGGREGATE_FUNCTION(*)

19 of 47

Example: COUNT(*)

Hypothetical Enrollments table:

Columns: CourseID, StudentID, Grade

Example:

SELECT COUNT(*) AS TotalEnrollments

FROM Enrollments;

Counts the total number of rows in the Enrollments table.
TotalEnrollments is an alias for the result.

Example: COUNT(*)

20 of 47

SELECT COUNT(*) AS TotalEnrollments�FROM Enrollments;�

21 of 47

Example: AVG()

Enrollments table (same as before).
Example:

SELECT AVG(Grade) AS AverageGrade

FROM Enrollments

WHERE CourseID = 'CS101';

Calculates the average Grade for the course 'CS101'.

22 of 47

SELECT AVG(Grade) AS AverageGrade�FROM Enrollments�WHERE CourseID = 'CS101';

23 of 47

Example: MAX()

Enrollments table.
Example:

SELECT MAX(Grade) AS HighestGrade

FROM Enrollments;

Finds the highest Grade in the Enrollments table.

24 of 47

SELECT MAX(Grade) AS HighestGrade�FROM Enrollments;

25 of 47

The DISTINCT Keyword

Used to retrieve only unique values from a column.
Eliminates duplicate values in the result set.

26 of 47

DISTINCT Syntax

Syntax:

SELECT DISTINCT column_name

FROM table_name;

27 of 47

DISTINCT Example

Example: Assume a Students table with a Major column.

SELECT DISTINCT Major

FROM Students;

This query will list all unique majors offered, without repeating any.

28 of 47

SELECT DISTINCT Major�FROM Students;�

29 of 47

DISTINCT Comparison

SELECT Major FROM Students;

Might return: CS, Math, CS, Physics, Math, Biology

SELECT DISTINCT Major FROM Students;

Will return: CS, Math, Physics, Biology

The second query removes duplicate major entries.

30 of 47

DISTINCT (Further Context)

Note that DISTINCT can also be used with Aggregate Functions.
Example: Select COUNT(DISTINCT Major) FROM Students;
This would count number of unique majors.

31 of 47

The ORDER BY Clause

Used to sort the results of a query.
Sorts based on one or more columns.

32 of 47

ORDER BY Syntax

Syntax:

SELECT column1, column2, ...

FROM table_name

ORDER BY column_to_sort [ASC | DESC];

33 of 47

ORDER BY Options

ASC: Sorts in ascending order (smallest to largest, A to Z).

This is the default if no order is specified.

DESC: Sorts in descending order (largest to smallest, Z to A).

34 of 47

Sorting by Multiple Columns

You can sort by multiple columns:

ORDER BY LastName ASC, FirstName ASC

This first sorts by LastName (ascending).
Then, within each LastName, it sorts by FirstName (ascending).
This establishes a primary and secondary sort order.

35 of 47

ORDER BY Example 1

Students table:

SELECT FirstName, LastName

FROM Students

ORDER BY LastName ASC;

Sorts students alphabetically by LastName (A-Z).

36 of 47

SELECT FirstName, LastName�FROM Students�ORDER BY LastName ASC;�

37 of 47

ORDER BY Example 2

Enrollments table:

SELECT CourseID, Grade

FROM Enrollments

ORDER BY CourseID ASC, Grade DESC;

Sorts enrollments first by CourseID (ascending).
Then, within each CourseID, sorts by Grade (descending).

38 of 47

SELECT CourseID, Grade�FROM Enrollments�ORDER BY CourseID ASC, Grade DESC;

39 of 47

The GROUP BY Clause

Groups rows that have the same values in specified columns.
Creates "groups" of rows based on shared values.

40 of 47

GROUP BY and Aggregate Functions

GROUP BY is most powerful when used with aggregate functions.
It allows us to perform calculations per group.
Instead of summarizing the entire table, we summarize each group.

41 of 47

GROUP BY Syntax

Syntax:

SELECT column1, AGGREGATE_FUNCTION(column2)

FROM table_name

GROUP BY column1;

column1 is the column you are grouping by.
AGGREGATE_FUNCTION(column2) is the calculation performed on each group.

42 of 47

GROUP BY Example 1

Students table:

SELECT Major, COUNT(*) AS NumberOfStudents

FROM Students

GROUP BY Major;

Groups students by Major.
Counts the number of students (COUNT(*)) within each major.

43 of 47

SELECT Major, COUNT(*) AS NumberOfStudents�FROM Students�GROUP BY Major;

44 of 47

GROUP BY Example 2

Enrollments table:

SELECT CourseID, AVG(Grade) AS AverageCourseGrade

FROM Enrollments

GROUP BY CourseID;

Groups enrollments by CourseID.
Calculates the average grade (AVG(Grade)) for each course.

45 of 47

SELECT CourseID, AVG(Grade) AS AverageCourseGrade�FROM Enrollments�GROUP BY CourseID;

46 of 47

Important Note about GROUP BY

Any column in the SELECT list that is not part of an aggregate function must be included in the GROUP BY clause.
This rule ensures that the results are well-defined. The Database system needs to know which column is used for generating the grouping, and it must be unambiguous.

47 of 47

Conclusion: Summary of Key Concepts

Standard Functions: Operate on individual row values (e.g., UPPER, ROUND, YEAR).
Aggregate Functions: Summarize data across multiple rows (e.g., COUNT, AVG, MAX).
DISTINCT: Retrieves unique values from a column.
ORDER BY: Sorts the result set.
GROUP BY: Groups rows with the same values, used with aggregate functions.