UploadLanguage (EN)Support
BusinessMobileSocial MediaMarketingTechnologyArt & PhotosCareerDesignEducationPresentations & Public SpeakingGovernment & NonprofitHealthcareInternetLawLeadership & ManagementAutomotiveEngineeringSoftwareRecruiting & HRRetailSalesServicesScienceSmall Business & EntrepreneurshipFoodEnvironmentEconomy & FinanceData & AnalyticsInvestor RelationsSportsSpiritualNews & PoliticsTravelSelf ImprovementReal EstateEntertainment & HumorHealth & MedicineDevices & HardwareLifestyle
Upload
Sign in
Uploaded byammarbrohi
PPTX, PDF7,684 views

SQL Functions

This document discusses different types of SQL functions including string, numeric, conversion, group, date/time, and user-defined functions. It provides examples of common string functions like UPPER, LENGTH, SUBSTR. Numeric functions covered include ABS, ROUND, POWER. Group functions include AVG, COUNT, MAX, MIN, SUM. Date functions allow conversion and calculation involving dates. The document demonstrates how to create scalar and table-valued user-defined functions in SQL.

Data & Analytics◦
Downloaded 338 times
1 / 48
2 / 48
3 / 48
4 / 48
5 / 48
6 / 48
7 / 48
8 / 48
Most read
9 / 48
10 / 48
11 / 48
12 / 48
13 / 48
14 / 48
15 / 48
16 / 48
17 / 48
18 / 48
19 / 48
20 / 48
SQL Function FreakDeveloper
Function? • Stored piece of program that Manipulates submitted data and return some value. FreakDeveloper
Where use SQL Functions? • SELECT Statement SELECT Ucase(name) FROM students • WHERE Statement SELECT * FROM students WHERE substr(class,1,1) FreakDeveloper
Types of SQL Functions? • String Functions • Numeric Functions • String / Number Conversion Functions • Group Functions • Date and Time Functions • Date Conversion Functions FreakDeveloper
String Functions • LOWER( string ) • All letters are changed to lowercase. • AMMAR ammar FreakDeveloper LOWER ( s )
String Functions • UPPER( string ) • All letters are changed to uppercase • ammar AMMAR FreakDeveloper LOWER ( s )
String Functions • INSTR( string, find, pos, occ) • Finds string within string. • INSTR(‘Ammar’, ‘ar’)  4 • INSTR(‘Ammar’, ‘a’,1,2)  4 FreakDeveloper
String Functions • LENGTH( string) • Tells length of string • LENGTH(‘Ammar’)  5 FreakDeveloper
String Functions • LPAD( string, length, pad-string) • Adds string to its left. • LPAD(‘Ammar’,10)  ‘ Ammar’ • LPAD(‘Ammar’,10, ‘-’)  ‘-----Ammar’ FreakDeveloper
String Functions • RPAD( string, length, pad-string) • Adds string to its right. • RPAD(‘Ammar’,10)  ‘Ammar ’ • RPAD(‘Ammar’,10, ‘-’)  ‘Ammar-----’ FreakDeveloper
String Functions • LTRIM( string, characters) • Trims out string from left. • LTRIM(‘ Ammar’)  ‘Ammar’ • LTRIM(‘Ammar’, ‘m’)  ‘Aar’ FreakDeveloper
String Functions • RTRIM( string, characters) • Trims out string from right. • RTRIM(‘Ammar ’)  ‘Ammar’ • RTRIM(‘Ammar’, ‘r’)  ‘Amm’ FreakDeveloper
String Functions • SUBSTR( string, pos, number) • Takes out string from string. • SUBSTR(‘Ammar’, 3)  ‘mar’ • SUBTR(‘Ammar’, 3, 2)  ‘ma’ FreakDeveloper
Types of SQL Functions? • String Functions • Numeric Functions • String / Number Conversion Functions • Group Functions • Date and Time Functions • Date Conversion Functions FreakDeveloper
Numeric Functions • ABS ( value) • Returns absolute value • ABS(-13)  13 FreakDeveloper
Numeric Functions • MOD( value, divisor) • Returns remainder of value divided by divisor. • MOD(4,2)  0 FreakDeveloper
Numeric Functions • POWER( value, exp) • Raises power of value to its exp • POWER(2,2)  4 FreakDeveloper
Numeric Functions • ROUND ( value, decimal) • Rounds value to decimal places. • ROUND (20.6, 0)  21 FreakDeveloper
Numeric Functions • CEIL ( value) • Rounds value upwards. • CEIL(20.6, 0)  21 • CEIL(-20.6, 0)  20 FreakDeveloper
Numeric Functions • FLOOR ( value) • Rounds value downwards. • FLOOR(20.6, 0)  20 • FLOOR(-20.6, 0)  21 FreakDeveloper
Numeric Functions • SQRT ( value) • Returns Square root of value. • SQRT(36)  6 • SQRT(81)  9 FreakDeveloper
Numeric Functions • SQRT ( value) • Returns Square root of value. • SQRT(36)  6 • SQRT(81)  9 FreakDeveloper
Types of SQL Functions? • String Functions • Numeric Functions • String / Number Conversion Functions • Group Functions • Date and Time Functions • Date Conversion Functions FreakDeveloper
Numeric Functions • NANVL ( value, format) • If value NaN, it will show replace_with • NANVL(‘Ammar’, 15)  15 • NANVL(10, 15)  10 FreakDeveloper
Numeric Functions • TO_CHAR ( value, format) • Converts value into string using format. • TO_CHAR(100.11, $999.9)  ‘$100.1’ • TO_CHAR(100.11, 999)  ‘100’ FreakDeveloper
Numeric Functions • TO_NUMBER ( string, format) • Converts string into number using format. • TO_NUMBER(‘100.11’, 999.9)  100.1 FreakDeveloper
Types of SQL Functions? • String Functions • Numeric Functions • String / Number Conversion Functions • Group Functions • Date and Time Functions • Date Conversion Functions FreakDeveloper
Group Functions • AVG ( column) • Finds average of column • AVG (rent)  $1300 FreakDeveloper
Group Functions • Count ( column) • Returns number of rows. • Count (*) from orders  140 • Count (customer_id) from orders • WHERE customer_id=4  9 FreakDeveloper
Group Functions • MAX ( column) • Returns maximum value in column. • MAX(marks) from students 99 • SELECT student_name from students • WHERE marks = MAX(marks) FreakDeveloper
Group Functions • MIN ( column) • Returns minimum value in column. • MIN (marks) from students 10 • SELECT student_name from students • WHERE marks = MIN(marks) FreakDeveloper
Group Functions • SUM ( column) • Returns sum of column. • SUM (quanitity) from orders 900 • SELECT SUM(quantity) from orders • WHERE cus_id= 7 FreakDeveloper
Types of SQL Functions? • String Functions • Numeric Functions • String / Number Conversion Functions • Group Functions • Date and Time Functions • Date Conversion Functions FreakDeveloper
Group Functions • CURRDATE() • CURRTIME() • SYSDATE() • NOW() • Returns the current date or time. FreakDeveloper
Group Functions • ADDMONTHS() • ADDYEARS() • Adds months and year respectively. • SYSDATE() + 1 FreakDeveloper
Group Functions • MONTHS_BETWEEN(date1, date2) • Tells the number of months between date1 and date2. • MONTHS_BETWEEN( • TO_DATE ('2003/08/02', 'yyyy/mm/dd'), TO_DATE ('2003/06/02', 'yyyy/mm/dd') • )  2 FreakDeveloper
Group Functions • SYSTIMESTAMP() • CURRTIMESTAMP() • TIMESTAMP () • Returns current date and time as a timestamp. FreakDeveloper
Types of SQL Functions? • String Functions • Numeric Functions • String / Number Conversion Functions • Group Functions • Date and Time Functions • Date Conversion Functions FreakDeveloper
Group Functions • TO_CHAR ( date, format) • Converts date into string using format. • TO_CHAR(now(), ‘dd-mm-yyyy’)  16-8- 2014 FreakDeveloper
Group Functions • TO_DATE ( string, format) • Converts string into dateusing format. • TO_DATE ('2014/08/16', 'yyyy/mm/dd'), FreakDeveloper
I wanna create my Function  FreakDeveloper
User Defined Function Types • Scalar Function: Returns single value as a result of actions perform by function. • Inline Table-Valued Function: Returns a table variable as a result of actions perform by function. • Multi-Statement Table-Valued Function: Same as ITVF, differ in performance. FreakDeveloper
Creating Scalar Function • CREATE FUNCTION function_name ( @parameter_name parameter_data_type ) RETURNS return_data_type AS BEGIN function_body RETURN scalar_expression END FreakDeveloper
Creating Scalar Function • CREATE FUNCTION function_name ( • @parameter_name parameter_data_type • RETURNS TABLE • AS • BEGIN • RETURN (Select Statement) • END FreakDeveloper
Questions? FreakDeveloper
Reference • http://msdn.microsoft.com/en-us/library/ms186755.aspx • http://www.dotnet- tricks.com/Tutorial/sqlserver/KY3T010412-Different-Types-of- SQL-Server-Functions.html • http://www.cs.utexas.edu/~mitra/csSpring2012/cs327/lectur es/sqlFunc.html FreakDeveloper
SlideShare • http://msdn.microsoft.com/en-us/library/ms186755.aspx • http://www.dotnet- tricks.com/Tutorial/sqlserver/KY3T010412-Different-Types-of- SQL-Server-Functions.html • http://www.cs.utexas.edu/~mitra/csSpring2012/cs327/lectur es/sqlFunc.html FreakDeveloper
Thank you FreakDeveloper