AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

SQL Functions: How to write a Function in SQL?

Last updated on Nov 04,2019 107 Views
13 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

Structured Query Language aka SQL is used to handle data in databases. It provides various in-built functions and commands to access and manage databases according to our requirements. In this article on SQL Functions, I will discuss the various in-built functions to perform different types of calculation on the data.

The following topics will be covered in this article:

    1. LCASE()
    2. UCASE() 
    3. LEN()
    4. MID() 
    5. ROUND() 
    6. NOW()
    7. FORMAT()

Before we delve into the different types of functions offered by SQL, let us understand what are functions.

What are functions?

Functions are methods used to perform data operations. SQL has many in-built functions used to perform string concatenations, mathematical calculations etc.

SQL functions are categorized into the following two categories:

  1. Aggregate Functions
  2. Scalar Functions

Let us look into each one of them, one by one.

Aggregate SQL Functions

The Aggregate Functions in SQL perform calculations on a group of values and then 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() Returns the number of rows either based on a condition, or without a condition.
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.

Let us look into each one of the above functions in depth. For your better understanding, I will be considering the following table to explain to you all the examples.

StudentIDStudentNameMarks
1Sanjay64
2Varun72
3Akash45
4Rohit86
5Anjali92

SUM()

Used to return a total sum of numeric column which you choose.

Syntax:

SELECT SUM(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the sum of marks of all students from the Students table.

SELECT SUM(Marks)
FROM Students;

Output:

359

COUNT() 

Returns the number of rows present in the table either based on some condition or without any condition.

Syntax:

SELECT COUNT(ColumnName)
FROM TableName
WHERE Condition;

Example:

Write a query to count the number of students from the Students table.

SELECT COUNT(StudentID)
FROM Students;

Output:

5

Example:

Write a query to count the number of students scoring marks > 75 from the Students table.

SELECT COUNT(StudentID)
FROM Students
WHERE Marks >75;

Output:

2

 AVG() 

This function is used to return the average value of a numeric column.

Syntax:

SELECT AVG(ColumnName)
FROM TableName;

Example:

Write a query to calculate the average marks of all students from the Students table.

SELECT AVG(Marks)
FROM Students;

Output:

71.8

MIN()

Used to return the minimum value of a numeric column.

Syntax:

SELECT MIN(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the minimum marks out of all students from the Students table.

SELECT MIN(Marks)
FROM Students;

Output:

45

MAX()

Returns the maximum value of a numeric column.

Syntax:

SELECT MAX(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the maximum marks out of all students from the Students table.

SELECT MAX(Marks)
FROM Students;

Output:

92

FIRST()

This function returns the first value of the column which you choose.

Syntax:

SELECT FIRST(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the marks of the first student.

SELECT FIRST(Marks)
FROM Students;

Output:

64

LAST() 

Used to return the last value of the column which you choose.

Syntax:

SELECT LAST(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the marks of the last student.

SELECT LAST(Marks)
FROM Students;

Output: 92

Well, with that we come to an end to SQL Aggregate Functions. Next in this article on SQL Functions, let us understand the various Scalar Functions.

Scalar SQL Functions

The Scalar Functions in SQL are used to return a single value from the given input value.  Following are a few of the most commonly used Aggregate Functions:

 Let us look into each one of the above functions in depth. 

FunctionDescription

LCASE()

Used to convert string column values to lowercase

UCASE()

This function is used to convert a string column values to Uppercase.

LEN()

Returns the length of the text values in the column.

MID()

Extracts substrings in SQL from column values having String data type.

ROUND()

Rounds off a numeric value to the nearest integer.

NOW()

This function is used to return the current system date and time.

FORMAT()

Used to format how a field must be displayed.

LCASE()

Used to convert values of a string column to lowercase characters.

Syntax:

SELECT LCASE(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the names of all students in lowercase.

SELECT LCASE(StudentName)
FROM Students;

Output:

sanjay
varun
akash
rohit
anjali

UCASE()

Used to convert values of a string column to uppercase characters.

Syntax:

SELECT UCASE(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the names of all students in lowercase.

SELECT UCASE(StudentName)
FROM Students;

Output:

 
SANJAY
VARUN
AKASH
ROHIT
ANJALI

LEN()

Used to retrieve the length of the input string.

Syntax:

SELECT LENGTH(String) AS SampleColumn;

Example:

Write a query to extract the length of the student name “Sanjay”.

SELECT LENGTH(“Sanjay”) AS StudentNameLen;

Output:

6

MID()

This function is used to extract substrings from columns having string data type.

Syntax:

SELECT MID(ColumnName, Start, Length)
FROM TableName;

Example:

Write a query to extract substrings from the StudentName column.

SELECT MID(StudentName, 2, 3)
FROM Students;

Output:

anj
aru
kas
ohi
nja

ROUND()

This function is used to round off a numeric value to the nearest integer.

Syntax:

SELECT ROUND(ColumnName, Decimals)
FROM TableName;

Example:

For this example, let us consider the following Marks table in the Students table.

StudentIDStudentNameMarks
1Sanjay90.76
2Varun80.45
3Akash54.32
4Rohit72.89
5Anjali67.66

Write a query to round the marks to the integer value. 

SELECT ROUND(Marks)
FROM Students;

Output:

91
80
54
73
68

NOW()

Used to return the current date and time. The date and time are returned in the “YYYY-MM-DD HH-MM-SS” format.

Syntax:

SELECT NOW();

Example:

Write a query to retrieve the current date and time.

SELECT NOW();

Output:

NOW() 
2019-10-14 09:16:36

 

FORMAT()

This function formats the way a field must be displayed.

Syntax:

FORMAT(InputValue, Format)

Example:

Write a query to display the numbers “123456789” in the format “###-###-###”

SELECT FORMAT(123456789, “###-###-###”);

Output:

123-456-789

With this, we come to an end to this article on SQL Functions. I hope you understood how to use the various types of functions in SQL. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”SQL Functions” and I will get back to you.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.