Master Oracle SQL with Quick Revision Tips

Basics

Horizontal ones are called as rows, records, tuples

Vertical ones are called as columns, field , attribute

what is row?

row is a collection of field values.

what is column ?

column is a collection of one field values

Based on operation SQL is categorized into sublanguage they are:

DDL :

create, alter,, truncate ,drop ,flashback ,rename

DML:

update, delete, insert , merge, insert all

• DQL

Select

• TCL

commit, rollback ,save point

• DCL

grand , revoke

Data Types

char : it allows fixed number of characters. it always uses the length even if the data is sorter

varchar : it allows variable length uses the needed space.

Numeric:

number(p): it allows the integer without any decimals

number(p, s) : it allows the integer with decimal where p is for precision which says how many digits are allowed actually.

s is scale it means number of digits allowed after decimal point .

example number(7,2) here 7 is precision and 2 is scale

it allows total of 7 digits 2 digits after the decimal that means five digits is for the before one.

Date

allows date and time

time is optional if we don’t enter then time is default taken as 12:00 am.

DD-MON-YYYY

Timestamp

allows data time and milliseconds

TO_CHAR

it is used to converts number to a string based on the given Format-model.

9

represents the digits shows the space if not filled

0

represents the digits fills with the 0 if they are not used

to_date

to convert a string into date format

examples:using to_char and to_date

select to_char(to_date('22/09/1999','dd/mm/yyyy'),'mm/dd/yyyy') from dual;

09/22/1999

NVL function

It is used to replace null values with default values.

NVL(arg1,arg2)

if arg1 is null then replace arg1 with arg2 value

select NVL(null,1000) from dual;

1000

Binary data type is used to store multimedia objects like audio, images, video.