Database

DB Functional

1. Build-in Functions - Group functions, Scalar functions

Group functions -

AVG

SUM

MAX

MIN

COUNT

select SysDate from Dual

Scalar functions -

Character Functions - Upper, Lower, InitCap

Length,

LPAD - Pads the Character on Left Side-
Example: Select LPAD('ORACLE', 10, 'XXX) FROM DUAL

RPAD - Pads the Character on Right Side -
Example: Select RPAD('ORACLE', 10, 'XXX) FROM DUAL

TRIM() - Removes the specified characters from both sides

LTRIM() - Removes the specified characters from left side

RTRIM() - Removes the specified characters from right side

INSTR() - Returns the position of the character with-in the string

SUBSTR() - Returns the sub-string of the string

CONCAT() - Join the two strings

Number Functions-

abs() - Returns the Obsolute Value

sqrt() - Returns the Square Root of the Provided value

mod() - Returns the Remainder value

power() - Returns the Power value

Trunc() - Removes the Decimal Point

Greatest() - Returns the Largest

Least() - Returns the Smallest

Date Functions-

ADD_MONTHS() - Adds Number of Months to the Provided Date

MONTHS_BETWEEN() - Returns the Number of Months between two given Dates

NEXT_DAY() - Returns the Next Date of the Provided Date

LAST_DAY() - Returns the Last Date of the Provided Date

Conversion functions-

TO_NUMBER() -

TO_DATE() -

TO_CHAR() - Converts the Date to specified format

Group by Clause - Is used to divide rows into different groups

Having Clause - Is used to filter the output from the group by clause

Order by Clause - Is used to arrange rows in the table (ascending or descending order)

Order of Execution of the Clauses Group By > Having > Order By

Constraints - At the time of defining the tables, we specify

Not Null - Will not accept null values in the column

Unique - Does not allow duplicates

Primary Key - Combination of Not Null and Unique. Primary Key does not allow duplicate values and also null values. This constraints can be created both at the column level as well as table level. We can create a Primary Key by combining two columns which is also called as composite key

Foreign Key or Referential Integrity

Check

Types of Tables - User Defined Tables and System Tables Set Operators - Can be used only if the column types have same data type

Union

Union All

Intersect

Minus