sql function(ppt)

"FUNCTIONS IN SQL"
CREATED BY: ANKIT DUBEY

"TYPES OF
FUNCTION IN SQL"
STRING
Function
Aggregate
Function
Date
Function
Time
Function

1) STRING FUNCTION
SQL | String functions
String function are used to perform an operation on input string and return an output
string.
Following are the string functions defined in SQL:
1)ASCII(): This function is used to find the ASCII value of a character.
Syntax: SELECT ascii('t');
Output: 116

2)CHAR_LENGTH(): This function is used to find the
length of a word.
Syntax: SELECT char_length('Hello!');
Output: 6
3)CHARACTER_LENGTH(): This function is used to
find the length of a line.
Syntax: SELECT CHARACTER_LENGTH('ANKIT
DUBEY');
Output: 11

4)CONCAT(): This function is used to add two words
or strings.
Syntax: SELECT 'Ankit' || ' ' || 'Dubey' FROM dual;
Output: ‘AnkitDubey’
5)LCASE(): This function is used to convert the given
string into lower case.
Syntax: LCASE ("ANKIT DUBEY");
Output: Ankit dubey

6)LEFT(): This function is used to SELECT a
sub string from the left of given size or characters.
Syntax: SELECT LEFT('Ankit S. Dubey', 5);
Output: Ankit
7)LENGTH(): This function is used to find the
length of a word.
Syntax: LENGTH('AnkitDubey');
Output: 10
&many more.............................................

2)AGGREGATE FUNCTION:
Aggregate functions in SQL:
In database management an aggregate function is a function where the
values of multiple rows are grouped together as input on certain criteria
to form a single value of more significant meaning.
Various Aggregate Functions Are:
1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()

• Now let us understand each Aggregate function with a example:
Id Name Salary
-----------------------
1 A 80
2 B 40
3 C 60
4 D 70
5 E 60
6 F Null
1)Count():
Count(*): Returns total number of records. i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the column salary
.i.e 4

Sum():
sum(salary): Sum all
Non Null values of
Column salary i.e.,
310
sum(Distinct salary):
Sum of all distinct
Non-Null values i.e.,
250.
Avg():
Avg(salary) =
Sum(salary) /
count(salary) = 310/5
Avg(Distinct salary) =
sum(Distinct salary) /
Count(Distinct Salary)
= 250/4
Min():
Min(salary):
Minimum value in
the salary column
except NULL i.e., 40.
Max(salary):
Maximum value in
the salary i.e., 80.

EXAMPLE OF AGGREGATE FUNCTION(INPUT):
USE ankit;
CREATE TABLE employee(id INT, fnam VARCHAR(10),snam VARCHAR(10),address VARCHAR(10),salary INT,age INT);
INSERT INTO employee(id,fnam,snam,address,salary,age
VALUES(1,'ANKIT','DUBEY','NALASOPARA',50000,25);
SELECT *FROM employee;
SELECT SUM(salary)FROM employee;
SELECT COUNT(salary)FROM employee;
SELECT AVG (salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT MAX(salary) FROM employee;

EXAMPLE OF AGGREGATE FUNCTION{OUTPUT}:

3.) "DATE FUNCION"
• It returns the name of the day and month of a
given date expression. This function is used to add a number of days,
months, years to the given date expression.
By the use of Date Function we show the
• the current date.
•Date functions in SQL with examples
•SQL | Date functions
• NOW(): Returns the current date and time. ...
• CURDATE(): Returns the current date. ...
• CURTIME(): Returns the current time. ...
• DATE(): Extracts the date part of a date or date/time expression. ...
• EXTRACT(): Returns a single part of a date/time. ...
• DATE_ADD() : Adds a specified time interval to a date

4) "TIME FUNCTION "
The time() function extracts the time part from
a given time/datetime.
Note: This function returns "00:00:00" if expression is not a
datetime/time, or NULL if expression is NULL.
Syntax:
TIME(expression)
MySQL TIME_FORMAT() Function:
The TIME_FORMAT() function formats a
time by a specified format.
Syntax:
TIME_FORMAT(time, format)
Parameter Description
time Required. The time to be formatted
format Required. The format to use. Can be one or a combination of
the following:

sql function(ppt)

More Related Content

What's hot

Similar to sql function(ppt)

More from Ankit Dubey

Recently uploaded

sql function(ppt)