coderz.py

What are SQL Functions?

SQL functions are predefined routines that can be invoked by SQL queries to perform specific operations on database data. These functions are used to manipulate data and return results based on the parameters passed to them.

SQL functions are categorized into the following two categories:

1. Aggregate Functions
2. Scalar Functions

Aggregate Functions:

These functions are used to calculate a set of values and return a single value.

Following are a few of the most commonly used Aggregate Functions:

Function	Description
SUM()	Used to return the sum of a group of values.
COUNT()	This function returns the number of rows in a table or the number of non-null values in a column.
AVG()	Used to calculate the average value of a numeric column.
MIN()	This function returns the minimum value of a column.
MAX()	Returns a maximum value of a column.
FIRST()	Used to return the first value of the column.
LAST()	This function returns the last value of the column.

Syntax & Examples for aggregate functions:

• COUNT():

Syntax for counting all rows in a table:

SELECT COUNT(*)
FROM table_name;

Syntax for counting non-null values in a specific column:

SELECT COUNT(column_name)
FROM table_name;

Consider a Student Table:

StudentID	StudentName	Marks
1	John	64
2	Sam	72
3	Alan	45
4	Rabecca	86
5	Anjali	92

Query to count the number of students from the Student table.

SELECT COUNT(StudentID)FROM Student;

Output: 5

• SUM():

Syntax for summing values in a specific column:

SELECT SUM(column_name)
FROM table_name;

Query to retrieve the sum of marks of all students from the Student table.

SELECT SUM(Marks)FROM Student;

Output: 359

• AVG():

Syntax for calculating the average of values in a specific column:

SELECT AVG(column_name)
FROM table_name;

Query to calculate the average marks of all students from the Student table.

SELECT AVG(Marks)FROM Student;

Output: 71.8

• MIN():

Syntax for finding the minimum value in a specific column:

SELECT MIN(column_name)
FROM table_name;

Query to retrieve the minimum marks out of all students from the Student table.

SELECT MIN(Marks)FROM Student;

Output: 45

• MAX():

Syntax for finding the maximum value in a specific column:

SELECT MAX(column_name)
FROM table_name;

Query to retrieve the maximum marks out of all students from the Student table.

SELECT MAX(Marks)FROM Student;

Output: 92

• FIRST():

Syntax for finding the first value in a specific column:

SELECT FIRST(column_name)
FROM table_name;

Query to retrieve the marks of the first student.

SELECT FIRST(Marks)
FROM Student;

Output: 64

• LAST():

Syntax for finding the last value in a specific column:

SELECT LAST(column_name)
FROM table_name;

Query to retrieve the marks of the last student.

SELECT LAST(Marks)
FROM Student;

Output: 92

Note: also read about SQL Constraints

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py