SQL Functions

SQL Functions : These functions can be used to perform calculation,comparison,modification of data

The two types of functions in sql are
1.Single-row functions: Operates on single row and returns one result for row
2.Multiple-row functions :operates on group of rows

The functions are
1.character function :accepts character data as input and return both character and numeric value
a.case manipulation
b.character manipulation

2.Number function: accepts numeric input and return numeric value

3.Date function :stores date in internal numeric format default display as DD-MON-YY.

4.Conversion function: convert the data from char to varchar, varchar to char, number to date, date to varchar.


5.General function :These function work with any data type
NVL(expr1, expr2) : Converts nall value to actual value.
NVL2(expr1, expr2, expr3) : If expr1 is null return expr2,not null return expr3.
NULLIF(expr1, expr2) : Compare two expressions and return null if they are equal or the
first if they are not equal.
COALESCE(expr1, expr2,........ exprn):returns the first not null expr.

6.Aggregate function : SUM,AVG,MAX,MIN,COUNT(*),COUNT(column name). these all are the aggregate functions.

GROUP BY Clause : group by clause is used to categorize the query result
HAVING CLAUSE : Used for restricting groups.

Elements of SQL

The basic elements of SQL are DATATYPES different types of datatypes are..

















A data type is an attribute that specifies the type of data that the object can hold

Char Data type :This specify the character string and has a length specified by size.if you insert a shorter value the blank space will be counted and if you insert a longer value then it returns a error.Maximum length allowed is 2000 Bytes.

Varchar2 : this specify a variable length character string.maximum of 4000 bytes.

Numeric Data type : This number data type stores +ve & -ve numbers including zero.

Date :The date data type stores Date and time information.It can be represented in both character and number data type.

Time Stamp : This is extension of date data type it stores year,month,day,hour,minute, and second value it also stores fractional seconds.

Blob Data type : It stores unstructured binary data it can store upto 8terabytes of binary data.

Clob Data types : It stores character data it can store upto 8terabytes of character data

LITERALS : These are fixed data values similarly like constants.

Text Literals : Text,character and string these are always surrounded by single quotations mark.can have maximum length of 4000 bytes.

Numeric Literals : These are used to specify fixed and floating point number stores maximum of 38 digits of precision.

Date Literals : we can specify date as string literals or can convert char or num. value to date value.

Null Value : If a column in a table has no value then it is said to be Null. A column can have many null values unless its restricted by primary key or not null.in a column no value is equal to null or no two null values are same

Comments : comments are used to understand table clearly.this is optional.
begin with /* and end the statement with */.