Scribd
Upload
6 views6 pages

CH 1 Querying and Functions in SQL

The document outlines SQL functions, categorizing them into single row functions (scalar functions) and multi-row functions (aggregate functions). It details various single row functions including numeric, string, and date functions, providing examples and their outputs. Each function performs specific tasks such as calculations, string manipulations, and date retrievals, returning single values based on the input provided.

Uploaded by

sirisrujanika23
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views6 pages

CH 1 Querying and Functions in SQL

The document outlines SQL functions, categorizing them into single row functions (scalar functions) and multi-row functions (aggregate functions). It details various single row functions including numeric, string, and date functions, providing examples and their outputs. Each function performs specific tasks such as calculations, string manipulations, and date retrievals, returning single values based on the input provided.

Uploaded by

sirisrujanika23
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

Querying and SQL functions

Function
•A function is a prede ned set of instructions that performs a speci c task and returns a
single value.

Types of functions
1.Single Row function/Scalar function
- Single row functions are applied on a single value and return a single value.

2.Multi-row functions/Aggregate function


- functions work on a set of records as a whole, and return a single value for each column of the records
on which the function is applied
fi
1. Single row functions
i)Numeric functions
• POWER(X,Y) - Calculates X to the power Y.
SELECT POWER(2,3);
Output: 8

• ROUND(N,D) - Rounds o number N to D number of decimal places


SELECT ROUND(2912.564, 1);
Output: 2912.6

SELECT ROUND(283.2);
Output: 283

• MOD(A,B) - Returns the remainder after dividing number A by number B.


SELECT MOD(21, 2);
Output: 1 ff
ii) String functions
•UCASE(string) OR UPPER(string) - Converts string into uppercase.
SELECT UCASE(“informatics practices”)
Output - INFORMATICS PRACTICES
•LOWER(string) OR LCASE(string) - Converts string into lowercase.
SELECT LCASE(“INFORMATICS PRACTICES”)
Output - informatics practices
•MID(string, pos, n) OR SUBSTRING(string, pos, n) OR SUBSTR(string, pos, n) -
- Returns a substring of size n starting from the specified position of the string. If n is
not specified, it returns the substring from the position pos till end of the string.

SELECT MID(“INFORMATICS, 3, 4)
Output - FORM
SELECT MID(“INFORMATICS, 7)
Output - ATICS
•LENGTH(string) - Return the number of characters in the specified string.
SELECT LENGTH(“informatics”)
Output - 11
•LEFT(string, N) - Returns N number of characters from the left side of the string.
SELECT LEFT(“computer, 4)
Output - comp
•RIGHT(string, N) - Returns N number of characters from the right side of the string.
SELECT RIGHT(“computer, 4)
Output - user
iii) Date functions
•NOW() - It returns the current system date and time.
SELECT NOW()
Output - 2025-06-26 19:41:17
•DATE() - It returns the date part from the given date.
SELECT DATE(NOW())
Output - 2025-06-26
•MONTH(date) - It returns the month in numeric form from the date.
SELECT MONTH(NOW())
Output - 6
•MONTHNAME(date) - It returns the month name from the specified date.
SELECT MONTHNAME(“2025-06-26”)
Output - June
•YEAR(date) - It returns the year from the date.
SELECT YEAR(“2025-06-26”)
Output - 2025
•DAY(date) - It returns the day part from the date.
SELECT DAY(“2025-06-26”)
Output - 26
•DAYNAME(date) - It returns the name of the day from the date.
SELECT DAY(“2003-03-24”);
Output - 24

You might also like