Transcript part 1.

ORACLE 10G
SQL
INTRODUCTION OF ORACLE 10G
BASIC POINTS
• What is Database Actually?
– Database is a plat form where we can place the
data for the future references.
• Or database is a collection of interrelated
data.
• (I.e. database always stores data along with
it’s relationships)
• The main objective of the db is store the
interrelated data and maintains the data.
• What is DBMS(Data base management
system)
• A DBMS is essentially a collection of
interrelated data and a set of pgms to access
this data.
– Data model
•
The relationship among the data
items, which shows how they are related is
called data model.
• The following are the database models:
•
•
•
•
File management system(FMS)
Hierarchical database system(HDS)
Network database system(NDS)
Relational database system(RDBMS) EX:oracle
• RDBMS: collection of relations or two dimensional
tables
• I)it has only data is represented by logical area called
as table.
• ii)intersection of rows and columns (cell) gives single
value.
• iii) no data redundancy (data redundancy can prevent
by using integrity constraints and triggers)
• iv) support the null values.
• v)support the integrity constraints.
• vi)provide the high security for the data at the time of
sharing by concurrent users(using DCL)
• vii)data retrive is very fast.
• viii) supports unlimited size
• Diff b/n DBMS and RDBMS:
• It having no relationship concepts
• it having the relationship concept using
database object tables.
• Supports single user
• Supports multi users
• It treats data as files internally
• It treats data as tables internally
• It supports 3 rules of e.f codd out of 12.
• It supports 6 rules minimum
• Eg:foxfro
• Ex:oracle,db2,pl/sql.
• E-R MODEL:
• E-R model provides an easy understood
pictorial map for the db design.
• It is built during the analysis phase of the
system development life cycle.
• Key components in E-R model:
•
Entity:
•
Attributes:
• Relation database Terminology:
•
Row or Tuple:
•
Column or Attribute:
•
Field or Cell:
• History of Oracle
• Oracle corporation was founded in 1977 in
california.
• Oracle main competitors in the Database
business are:
• Eg: IBM with DB2, informix,MSAccess and SQL
server,MYSQL.
• Oracle main competitors in the Application
business are:
• Eg:SAP and People Soft.
• Introduced the first RDBMS based on the IBM
System/Relation model (SQL) Technology.
• Communication With RDBMS:
– The SQL is used to communicate with RDBMS.
• Versions in Oracle
– Oracle 6.0(1988)
» Oracle Financial Applications Built on Relational database.
» It will not support the Client &server Technology.
– Oracle 7.X(1992):
» It support the client &server Technology
» Support for Unix operating System.
• Oracle 8.0(1997)
– Support more users, more data and Object Relational Features.
– It support oops concept.
– New data types Lobs are introduced.
• Oracle 8i(internet)(2000):
– Supports the Web based Applications
– SQLJ(Structured query lang java) introduced.
– Java is in built.
• Oracle 9i(2001):
– RAC introduced and some built in functions are like NVL2,
Nullif…etc are introduced.
– New Time stamp Data types are introduced.
– Merge command introduced.
• Oracle 10g(grid computing Technology)(2004):
– Flash back table, Recycle bin introduced.
– New data types binary load, binary float are introduced
– New table space SysAvx introduced.
• Insert All Command introduced
• Oracle 11g (11.1)
–
–
–
–
Simple-integer, simple-float, and simple double data type
Continue stmt
Compound triggers
Sequences in PL/sql Expressions
• Database objects :
• The different Database objects in Oracle are:
• Table: Used to store data.
• View: Logically represent subsets of data from one or
more tables
• Sequences: used to generate primary key values.
• Index: it is used to improve the performance of some
queries.
• Synonym: used to give alternative names to objects
– Data types in oracle
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Each value in oracle is manipulated by a data type.
Scalar data types in oracle are:
i)character data type
ii) number
iii) long and raw
iv)Lob data types
v)Date time
Character data type:
They store character data
The diff character data types are
CHAR
NCHAR VARCHAR2
NVARCHAR2
Char: it specifies fixed length character string
i) Default length is 1 byte
ii)Maximum 2000 bytes.
Varchar2: this data type used for storing text data.
i) Minimum size is 1 byte
ii) Maximum size is 4000 bytes
• VARCHAR vs. VARCHAR2
• VARCHAR can store up to 2000 bytes of
characters while VARCHAR2 can store up to
4000 bytes of characters.
• If we declare datatype as VARCHAR then it will
occupy space for NULL values, In case of
VARCHAR2 datatype it will not occupy any
space.
• Memory allocation by varchar is static whereas
that using varchar2 is dynamic.There is no
wastage of memory in varchar2 if unused
•
•
•
Char: it specifies fixed length character string
i) Default length is 1 byte
ii)Maximum 2000 bytes.
•
•
•
Varchar2: this data type used for storing text data.
i) Minimum size is 1 byte
ii) Maximum size is 4000 bytes
•
•
•
Number: store the number values
i) Syntax number (precision, scale)
ii) The precision is the total no of digits required and scale stands for the rounding of
decimal
•
•
Long: used to store characters or numbers
i) Maximum size is 2 GB
•
•
Raw: it stores binary information like photos signatures, thumb impressions …etc.
i) Maximum length is 2000 bytes.
•
•
•
Long raw:
i) It stores the binary data similar to raw but can store more bytes than raw
ii) Maximum length is 2gb
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Lob :( large object)
The built in lob data types are
BLOB ii) CLOB iii) NCLOB iv) Bfile (identified location of the image)
Those images can store into the database by using BLOB data type
ii) These data types are stored internally
iii) The lob data type can store large and unstructured data like text,
image, video and special data.
iv) The maximum size is 4 GB
Date time:
It is used to store dates and time information
i) The default date format in oracle is dd-mon-yy
ii) The default time accepted by oracle data is 12:00 AM (midnight)
iii) The default date accepted by oracle data is the first day of the current
month.
iv) Date range provide
January 1, 4712 BC to December 31, 9999 AD
Timestamp:
i) It is an extension of the date data type
ii) It stores day month year hour minute second
Syntax: timestamp (fractional seconds-precision)
iii) It can be a number in the range of 0-9 with default as 6
• SQL (Structure query language):
• i) Sql commonly pronounced as “SEQUEL”(structured
English query language)
• ii) SEQUEL later become SQL
• iii) It allows the user to communicate as the server
• iv) Use to define retrive and manipulate the data.
• SQL*PLUS Buffer
• i) it is an environmental tool
• ii) we can use directly sql&pl/sql stmts in this
environment tool
• iii) only one sql stmt is managed in the sql buffer
• iv) the current sql stmt replaces the previous sql stmt
in
•
Components of SQL:
•
•
•
Data retrieval/query language (DRL/DQL):
It is used to retrieve the information from database objects for read only purpose.
i)Select
•
•
•
Data manipulation language (DML)
It used to manipulate the data in database objects
i)Insert (new content) ii)insert all(10g) iii) update(modify) iv) merge(9i) v)delete
•
•
•
•
Data definition language (DDL)
Use to define database objects
Creation, modification, removing
i) Create ii) alter iii) drop iv) truncate v) rename
•
•
•
Data control language (DCL)
It used to share the information between users
i)Grants ii) Revoke
•
•
•
Transaction control language (TCL)
It used to save or cancel DML operations
i) Commit ii) Rollback iii) Save point
•
•
•
•
•
•
•
•
•
•
•
•
•
SQL DEVELOPMENT ENVIRONMENTS
sql developer and sql*plus
Client server technology in oracle
Table:
It is a basic unit or database object to store the
data.
Some rules followed before creating the table
i) Specify the table name and column name and data
type
ii) Table name must be unique (a-z,_)
iii) Table names are not case sensitive
iv) Table name should 1-30 characters long
v) Column name should begin with a letter and can be
1-30 character long
vi) Column name is unique
vii) Maximum 1000 columns are there
LAB PRACTICLES OF SQL
• How to start the SQL*PLUS environment
• steps:
• start-> programs->oracle 10g express edition>Run SQL command Line
• connect to the database using default
user/password
• syntax: conn <username>/<password>;
• or conn <username>
• enater password:<******>;
• eg: conn hr/hr;
•
•
•
•
•
•
•
•
•
•
•
•
to see the existing tables in hr user
syntax: select *from tab;
to see the structure of the table
syntax: desc <table name>;
creation of user (own):
Steps:
i) SQL> conn sys/sys as sysdba;
ii) SQL> create user <username>identified by
<password>;
eg: create user times identified by times;
iii) SQL>grant connect, resource to <username>;
eg: SQL>grant connect, resource to times;
iv) SQL> conn times/times;
•
•
•
•
•
•
•
•
•
How to lock the currant user
Steps: 1)conn sys/sys as sysdba;
2)alter user <username> account lock;
Eg:alter user hr account lock;
How to unlock the currant user
Steps:
1)conn sys/sys as sysdba;
2)alter user<username>account unlock;
Eg: alter user hr account unlock;
• Table:
•
It is a basic unit or database object to store the
data.
• Some rules followed before creating the table
• i) Specify the table name and column name and data
type
• ii) Table name must be unique (a-z,_)
• iii) Table names are not case sensitive
• iv) Table name should 1-30 characters long
• v) Column name should begin with a letter and can be
1-30 character long
• vi) Column name is unique
• vii) Maximum 1000 columns are there.
creation of table
• using crate command u will create the new
table
• syntax: create table <table name>
• <(column1 datatype,column2 data type)>-----);
• eg: create table emp
• (empno number(10),ename varchar2(10),sal
number(10),job varchar2(10),mgr
number(10),hiredate date,comm
number(7,2),deptno number(10));
• Saving SQL Files:
•
The List file is used to save all information which performed in
SQL*PLUS
• Navigations: 1)in SQL*PLUS
• File->spool->spool file-> spool on
• 2)give the file name
• 3) performed all tasks
• 4) now spool off
• Creating and Running SQL Script file
• The Save command is used to store the current contents of the SQL buffer
• EX: SAVE E:\nitdir\suma.sql
• Save e:\nitdir\suma.sql replace
• To get Script
• Used to display the script file
• Get e:\nitdir\suma.sql
• To Run the script:
• Run is used to display the script and run the file
• Run e:\nitdir\suma.sql
• To open the script file in editor:
• Ed e:\nitdir\suma.sql
insert data into the emp table:
• three methods are there:
• i) insert into tablename values(------------------);
• eg insert into emp values(1001,'smith',5000,'clerk', 7566,'10may-88',null,10);
• ii) insert into
emp(empno,ename,sal,deptno)values(7369,'scott',7000,10);
• iii)substitution variables:
• it will store the data temporarly
• strore the data through :&(single ampresend)
•
:&&(double ampresend)
• eg of &:SQL> insert into emp
values(&empno,'&ename',&sal,'&job',&mgr,'&hiredate',&comm,
&deptno);
• eg of &&: SQL>insert into emp(empno,ename,sal)
values(&empno,'&ename','&&fees);
• insert the null values and special values
into the table
• eg:insert into
emp(empno,ename,hiredate)values(null,user,s
ysdate);
• Query: it is an operation to retrive the data
from one table or more than one tables.
• Retrieve the data or view the data from existing
table:
• using select command u can retrive or view the data
easily
• capabilities of sql select stmts:
• select: chooses the rows
• projection: chooses the columns
• join : it chooses the data from more than one table
• syntax: select * <[distinct/unique
columns/expressions/alias]> from table;
select ->identifies what columns
* ->select all column
from ->identifies which table
distinct->avoide duplicates
•
•
•
•
•
Retrieving data from all columns:
eg:
select *from emp;
select *from dept;
select *from salgrade;
•
•
•
•
•
Retrieving data from specific columns:
eg:
select empno,ename,job,sal from emp;
select deptno,dname,loc from emp
select lo sal from salgrade;
• Applying arithmatic operations in select stmt:
• arithmatic operators: used to create expressions on
number and date data.
• the arithmatic operators are:
• +(addition)
• -(subtraction)
• *(multiplication)
• /(division)
•
•
•
•
•
eg: select empno,ename,sal,sal+500 from emp;
select empno,ename,sal,12*sal+500 from emp
select empno,sal,12*(sal+500);
operator precedence:
(*/+-)
• 'Dual' table
• dual is a small oracle worktable,which consists
of only one row and one column,aaan
• and contains the value x in that column.
• we can use dual table with select stmts, where
clause and for retrieving the function results
• EG: select 2*2 from dual
• select ename from dual;
• select 1 from emp;
• using select stmt handling the null values:
•
•
•
•
•
Null:
unknown value
undefined value
not equal to zero (0) or blank space.
RDBMS support null values
• NVL function:
•
the NVL function is used to convert a null value to an actual
value.
• syntax: NVL(expr1,expr2)
• eg: select NVL(null,100)from dual;
• select NVL(100,200)from dual
• select ename,sal,comm,sal+nvl(comm,0)from emp;
• select ename,sal,comm,(sal*12)+nvl(comm,0)from emp;
•
•
•
•
•
•
NVL2 function:
syntax:NVL2(expr1,expr2,expr3);
eg: select NVL2(100,NULL,300)from dual;
select NVL2(null,100,300)from dual;
note: if expr1 is not null,nvl2 returns expr2,if expr1 is null nvl2 returns expr3.
• NULLIF function:
• compares two expressions and returns null if
they are equal,or the first if there are not equal.
• syntax:NULLIF(expr1,expr2)
• eg: select nullif(100,100)from dual;
• select nullif(100,200)from dual;
• COALESCE:
• it returns first non_null expression in the
expression list.
• Syntax: COALESCE(expr1,expr2,expr3)
• eg: select coalesce(null,300,350) from dual;
• select coalesce (100,300,null)from dual;
•
•
•
•
•
•
•
•
Defining column alias:
An alias is an alternative name given for any oracle object
aliases in oracle
1)column alias
column alias:
rename a column heading.
how to defrine column alias in select stmt:
specify the alias after the column in the select stmt list using a space as a
separator
• using As keyword
• enclose the alias in double quotation marks "“
•
•
•
•
Examples:
eg: select empno empnumber
ename empname
job as "designation" from emp;
• 2) select empno "empnumber",sal "basic",sal*0.25 hra,sal*0.20 da,sal*0.15
"pf",sal+sal*0.25+sal*0.20-sal*0.15 "gross“ from emp;
• Concatenation operator:
• the concatenation operator concatenate
columns or character strings or expressions.
• represented by two vertical bars ||
• eg:
SQL>select empno||ename from emp
SQL> select 'the basic salary of'||ename|| 'is RS'
||sal employee from emp;
• avoide duplicate rows
• distinct/unique: to eliminate duplicate rows in the
Result.
• The result will gives default Ascending order.
• eg: select distinct deptno,job from emp;
• select distinct sal from emp;
• select unique deptno from emp;
• filter of record using select stmt:
• using where clause u will returns limited records only.
• syntax of where:
select *{[distinct]column|expression[alias],----} fromm
table [where condition];
• Relational operators:
• <,>,<=,>=,=,<>or!
• eg :select ename from emp where sal>4000;
• select ename from emp where sal=5000;
• select ename,deptno from emp where deptno<>30;
• logical operators:
• combines the result of two component conditions to produce a
single result.
• three logical operators :
• AND OR NOT
• truth tables of And (both true means true otherwise gives
false)
• truth table of or (any one true it returns true value,both false
means it will return false value)
• and: the and operator allows creating stmts based on two or
more conditions being met.
• it returns false if either is false.
• eg:
• select ename,sal,job emp where (sal>=1500 and sal<=5000)
and job='manager';
• select ename ,comm from emp where sal=5000 and sal<4000;
• OR:
• it returns true if either component conditions is true.
• it returns false if both are false.
• eg:
• select empno,ename,sal,deptno from emp where sal>2000 or
deptno=20;
• select empno,ename from emp where job='manager' or
deptno=30;
• not:
• it returns true if the following condition is false
• it returns false if the
• eg;
• select ename from emp where not ename='smith';
• select empno,ename,job,sal from emp where not
sal<=5000;
• combination of and or operators
• select ename,sal from emp
• where (job='clerk' or job='president' or
job='analyst')and sal>3000;
•
•
•
•
•
•
•
Rules of precedence default:
the following order:
All comparision operators
not
and
or
note: override rules of precedence by using
parenthesws.
• Miscellaneous operators: (SQL*PLUS
OPERATORS)
• Between ----And
• Not Between----And
• IN
• Not IN
• Like
• Not Like
• Is null
• Is not null.
• Between:
• Between is used to display rows based on range of values
• The lower limit should be declared first.
• eg:
• select empno,ename,comm from emp where sal between 2000
and 5000;
• select empno,ename ,hiredate from emp where hiredate not
between '17-may-1988' and '12-mar-1990'
• Like: use the Like condition to perform wildcard (*,[],[-])
• searches of valid search string values.
• The following wild cards are:
• % it reprsents any sequnxce of zero or more characters
• _ represent any single character, only at that position only.
• select empno,ename fromemp where ename like 'M%'
• select empno,ename from emp where ename not like
'm%'
• select empno,ename from emp where ename like
'_o%';
• select empno,ename,job from emp where job like '
';
• select ename,hiredate from emp where hiredate like
'%-feb-1981';
• Select ename,hiredate from emp where hiredate like
'%jan%';
• Select ename from emp where ename like
'%c';
• IN operator:
• The operator is used to test for values in a
specified list.
• The operator can be used upon any
datatype.
• Eg:
• Select empno,job from emp where deptno
in(10,30);
• Select empno,ename from emp where
ename in(‘ford’,’smith’)
• Select ename,hiredate from emp where
hiredate in(’20-feb-1981’,’09-jun-1981’)
• Order By clause:
• The order by clause can be used to sort the rows.
• the order by clause must be the last clause of the sql
stmt.
• Default ordering of data is sorted: (Ascending)
• Number 1-999
• dates Earlist-latest
• string A-Z;nulls:last
• Syntax:
• select from table [where condition(s) ]
• [order by {column,expr}[ASC|DESC]];
• Examples:
• select ename,job sal,deptno from emp order
by sal;
• select ename,job,sal,deptno from emp order
by sal DESC;
• select ename,job,sal,deptno from emp order
by 2 desc;
• select *from emp order by 5desc;
• select ename,job,sal,sal*12 annsal from emp
order by annsal;
• Select ename,sal,deptno from emp where
deptno=10 order by sal desc;
•
•
•
•
Functions in oracle: (Functions perform an action)
Two types of functions
1)single row functions
2)multiple Row functions
• def of 1):Return one result per row.
• def of2): these function manipulate group of rows to
give one result per group of rows
• Syntax: func_name(colum/expr,[arg1,arg2,-----])
• note: can be used in select, where,and order by
clause.
• can be nested.
•
•
•
•
•
Why u using Functions in SQL.
Perform calculations on data.
Modify individual data items
Convert column data types
Display the dates and numbers format.
•
•
•
•
•
•
single row function again five types:
i)character functions
ii)number functions
iii) Date functions
iv)conversion functions
v)general functions
• Character functions: Accept character input and can return
both character and number values.
• Two Types:
• Case conversion functions.
• Character_manipulation functions.
• Lower: it converts alpha character value to
lowercase
• syntax :lower(column|expression)
• eg: select Lower('TIMES TECHNOLOGIES') from
dual;
• select ename,job,lower(ename)from emp where
deptno=10;
• upper: it converts the alpha character values to
upper case
• syntax: upper(column|expression)
• eg: select upper('ramakrishna')from dual;
• select ename,job,upper(ename),upper(job) from
emp where deptno=20;
• Initcap: it returns a string with the first letter of each word in
upper case,keeping all
• Syntax: initcap (co0lumn|expression)
• eg:
• select initcap(Ename)from emp;
• select ename,upper(ename),lower(ename),initcap(ename)from
emp;
• Concat: it concatenates the first characters value to the second
character value.
• note: only two mparameters accept.
• syntax:concat(column1|expr1,column2|expr2)
• eg:concat('oracle','naresh technologies')from dual;
• select ename,job,concat(ename,job)from emp where
deptno=20;
• select concat(concat(ename,job),sal) from emp;
•
•
•
•
•
Length: Returns the number of characters in a value.
if the char is null,if return null.
Syntax: Length(column|expression)
eg: select length('times tech') from dual;
select initcap(ename),job from emp where
length(ename)=5;
• Lpad function:
• Syntax:Lpad(column|expression,n,[c])
• fill extra spaces with char 'c' up to 'n' position on left
side.
• default padding is space.
• eg: select Lpad('page1',20,'*') from dual
• select Lpad('page1',20) from dual;
•
•
•
•
•
Rpad function:
syntax: Rpad(column|expression,'n','c')
Eg:
select Rpad('oracle10g',20,'@')from dual;
select Rpad('timestech',27)from dual;
• Ltrim:
•
all the left most characters that appear in the
set are removed.
•
•
•
•
syntax: Ltrim(char,set)
Eg:
select Ltrim('xyxyxyORACLE 10g','xy')from dual;
select Ltrim('MMM KRISHNA','M')from dual;
•
•
•
•
•
•
Trim: Both side character sets are removed.
Eg: select trim( 's' from 'ssmithss') from dual;
select trim(both 's' from 'ssmithss')from dual;
Leading: trim only for leftside
Trailing :trim only for rightside
Eg: select Trim(Leading ‘s’ from
‘ssmithss’)from dual;
• Select Trim(Trailing ‘s’ from ‘ssmithss’)from
dual;
•
Replace function:
• it returns the every occurrence of search string replace by the replacement string.
• it substitutes one string for another as well as to remove character string.
• note: if the replacement string is omitted or null, all occurrence of search string are
removed.
•
Syntax: Replace (text, search_string, [Replacement_string])
•
•
•
eg: >select replace('led','l','r')from dual;
>select replace('led','l','ra')from dual;
>select job,replace(job,'p')from emp where job='president';
•
•
•
Translate:
used to translate character by character in a string.
note:the argument from can contain more characters than to.
•
syntax: Translate(char,from,to)
•
•
•
eg:>select translate(job,'p',' ') from emp where job='president';
>select translate('led','le','r')from dual;
>select translate(job,'mn','dm')from emp where job='manager');
•
Substring:
•
Returns specified characters from character value,string from a specified position
•
•
'm' to 'n' characters long.
to extract the portion of the string it is mainly used.
•
points to Remember:
•
•
•
•
if m is 0,it is treated as 1.
if m is positive, oracle counts from the begining of char to find the first character.
If m is negative oracle counts from end of the character.
if n is <1 or 0, a null is returned.
•
•
•
syntax: Substr(col/expr,m,[n])
m->starting position
n->occurence number.
•
•
•
•
•
eg:>select substr('siva rama krishna',1,4)from dual;
>select substr('siva rama krishna',6,4) from dual;
select substr('siva rama krishna',7) from dual;
select substr('siva rama krishna',-12,4) from dual;
>select ename,job from emp where substr(job,6)=upper('man');
•
•
•
•
•
•
Instring :
it returns the numaric position of a named character.
syntax: Instr(column|expr,'c',[,m],[n])
note: the default value of m and n are 1.
if search is unsuccessful,the return value is zero.
the value of n should be positive.
•
•
•
•
•
eg: select instr('siva ramakrishna','a',1,1)from dual;
>select instr('siva ramakrishna','a',7,1)from dual;
select instr(job,'a',1,2)from emp where job='manager';
select instr(job,'a',2)from emp where job='manager';
select instr(job,'a')from emp where job='manager';
• CHR Funtion:
• It returns a character having the binary
equivalent to 'n'.
• syntax: CHR(N)
• eg: select chr(65) from dual;
• select chr(87)||chr(90)from dual;
• Ascii function:
• it will returns the value the format of binary
number.
• syntax: ASCII('A')from dual;
• select ename,ascii(ename)from emp;
• Number function:
• These function accept number input and return
numaric values.
•
•
•
•
•
•
•
•
•
Round function
Truncate
ceil
floor
modulus
power
square
Absolute
Sign
• Round function:
• syntax: Round(m,n)
• it returns 'm' round to 'n' places right of the decimal
point.
• if 'n' omitted , n is Rounded to 0 places
• 'n' can be negative, and rounds off the digits from left
of the decimal point.
• 'n' must be integer.
• eg:
• Select round(19.637)from dual;
• select round(19.637,1)from dual;
•
(19.637,-1)
•
(7843.637,-3)
•
(
,4)from dual;
• Truncate
• syntax:Trunc(m,n)
• eg:select truncate(7843.637,2) from dual;
•
(7843.637,-4)from dual
• Ceil :
• syntax:ceil(n)
• The adjustment is done to the highest nearest
decimal value.
• eg: select ceil(19.32)from dual;
• select ceil(19)from dual;
• Floor:
• syntax: floor(n)
• The adjustment is done to the lowest nearest decimal
value.
• eg:select floor(18.34)from dual;
• select filoor(18.9)from dual;
•
•
•
•
Modulus:
syntax: Mod (m,n)
it returns remainder of 'm' divided by 'n'.
it returns 'm' if 'n' is 0.
• eg: select Mod(100,10)from dual;
• select Mod(17,4)from dual;
•
•
•
•
•
•
•
•
•
Power:
it returns 'm' raised to the 'n' th power.
syntax:power(m,n)
eg:select power(5,2)from dual;
select power(-3,4)from dual;
Square:
syntax:sqrt(n)
It returns square Root of 'n' as Real value.
Select sqrt(25)from dual;
•
•
•
•
•
•
•
•
•
•
•
•
•
•
ABsolute:
Syntax:ABS(n)
eg:select abs(-398)from dual;
select sa,comm,comm-sal,abs(comm-sal)from emp;
Sign:
syntax:Sign(n)
it returns the sign,specification of a number
if n<0,returns -1
n=0,
0
n>0
1
eg:select sign(-10)from dual;
select sign(100)from dual;
select sal,sign(sal-comm)from dual;
•
•
•
•
Working With Dates in Oracle:
Points:
Oracle stores dates in an internal numaric format.
Dates in oracle range from jan 1,4712 BC to Dec
31,9999 AD.
• The default date format is DD-MON-YY.
• The numeric format represents
• Century Year Month Day Hours Minutes Seconds
Date functions:
• Sysdate:
• Date arithmatic
• Add_months
• Months_between
• next_day
• Last_day
•
•
•
•
•
•
•
•
•
•
•
•
Sysdate: It returns Current date and time
Eg:>select sysdate from dual;
Date arithmatic:
Perform arithmatic operators on dates (+,-,*,/)
Date+number (no of days)
Date-number(sub no of days)
Date-date(sub one date from another date)
Date+number/24 (add no of hours to a date)
eg:
>select sysdate,sysdate+10 from dual
>select sysdate,sysdate+48/24 from dual
>select ename,hiredate,sysdate-hiredate "exofemps"
from emp;
•
((sysdate-hiredate)/7)
•
((sysdate-hiredate)/30)
•
((sysdate-hiredate)/365)
• Add_Months:
•
•
•
•
syntax: Add_months(D,+(or)-N)
N is add no of Calender months
eg:select sysdate,add_months(sysdate,3)from dual;
>select sysdate,sal,hiredate,add_months(hiredate,1) from emp where
deptno=30;
• Monts_between:
•
•
•
•
It gives diff b/n dates d1 and d2 in months
if d1 is later than d2, the result is positive,else negative.
The Result is always an integer.
Syntax :months_between(D1,D2)
•
•
•
•
Eg: select months_between(sysdate,hiredate) from dual
>
((sysdate,hiredate)/12) "ex in yers from emp
>select months_between(sysdate,hiredate) from emp
where months_between(sysdate,hiredate)>200;
• Next_Day
• It returns the date of the week day of next week same day
• Syntax:Next_day(D,char)
• >select sysdate,next_day(sysdate,'friday') from dual;
• Last_day:
• it returns the date of the last day of the month.
• Mostly used how many days left in the current month.
• eg: select sysdate,last_day(sysdate) from dual;
• select sysdate,last_day(sysdate) last,last_day(sysdate)-sysdate
daysleft from dual;
• >select add_months(last_day(sysdate),-1)+1 from dual;
•
•
•
•
•
•
•
Rounding of dates:
Date is rounded to the nearest day.
syntax:Round(date,'format')
eg:
>select Round(sysdate,'Day')from dual;
(sysdate,'Month')
(sysdate,'Year')
•
•
•
•
•
•
•
Truncating Dates:
Date is truncated to the nearest day.
syntax:Trunc(sysdate,'format')
Eg:
Select Trunc(sysdate,'Day')from dual;
(
(
• Conversion Function:
• using this function convert a value from one data type
to another.
• Two types:
• i)implicit data type conversion
• ii)explicit data type conversion
• i)eg: varchar2--------->Number
•
varchar---------->date
•
date-------------->varchar2
• ii)explicit conversion function are
•
•
To_char ---> to character conversion
•
To_date----->to date
•
To_Number--->to number
• To_char conversion:
• this function can be used in two ways.
• to_char(number conversion)
• to_char(date conversion)
• To_char(date conversion):
• converts date of data type to a value of varchar2 data
type .
• AD OR A.D/BC OR B.C Indicator:
• select sysdate,to_char(sysdate,'B.C')from dual;
• Meridian indicator:(A.M OR AM)
• eg:select sysdate,to_char(sysdate,'A.M')from dual
• Century indicator:CC
• idicates the century.
• select to_char(sysdate,'cc-ad')from dual
• Numaric Weekday (1-7)
• Returns the week day number
• select sysdate,to_char(sysdate,'D')from dual;
• Weekday spelling:
• returns the weekday with speling.
• select sysdate,to_char(sysdate,'Day') from dual
•
(sysdate,'dy')from dual;
• Monthday :DD
• it indicates the day of the month(1-31)
•
(sysdate,'DD')from dual;
•
(sysdate,'DD-Day')from dual;
• Year day: DDD
• it indicates the day of the year(1-366)
•
to_char(sysdate,'DDD')from dual;
• Select ename,hiredate,to_char(hiredate,'ddd')from
emp
• where to_char(hiredate,'Dy')='Mon';
• Year Week :IW or WW
• indicates the week of the year(1-52 or 1-53)
•
to_char(sysdate,'IW')from dual;
• Four digit year YYYY or SYYY 0r IYYY
• select sysdate,to_char(sysdate,'YYYY')from dual;
• Spelled Year:YEAR Or SYEAR
• Returns the numaric year in spelling.
• select to_char(sysdate,'year')from dual;
• week of the month :W
• specifies the week of the month(1-5)
• note:week starts on the first day of the month and ends on the
seventh day.
• eg:
to_char(sysdate,'w')
•
•
•
•
•
Quarter of the year:Q
Returns the quarter of the year
eg:
to_char(sysdate,'Q') from dual;
to_char(hiredate,'Q')from emp
where to_char(hiredate,'Q')=3;
• number month:MM
• Returns the numaric value of the month
• select to_char(sysdate,'MM')from dual;
• Abbrevation of month(MON or Month)
• to_char(sysdate,'MON')from dual;
• Roman month:RM
• returns the roman numeral month(I-XII).
• Julian day indicator: J
• It returns the number of day since january 1,4712 BC.
• select sysdate,to_char(sysdate,'j')from dual;
•
•
•
•
Twele hour clock :HH OR HH12
it is default clock mode.
Returns the hour of the day.
syntax: to_char(D,'HH')
• Twenty four hour:(HH24)
• Returns the hour of the day in HH24 clock
mode
• syntax:to_char(D,'HH24')
• Minuted Indicator:(MI)
• Returns the minutes from the given date.
• Syntax:to_char(D,'MI")
•
to_char(sysdate,'HH:MI')from emp where
deptno =10;
•
•
•
•
•
seconds indicator:SS
Returns seconds from the given date(0-59)
to_char(sysdate,'ss')
to_char(sysdate,'hh24:mi:ss')
to_char(sysdate,'da
• Date format punctuators:
• '_' '/' '!' '.' ',' ':' ';'
• spellformat of fullDate:
• select
sysdate,to_char(sysdate,'DDspth,month,yyyysp') from
dual;
• Fill mode :FM
• it suppress blank padding in the return value.
• to_char(sysdate,'FMddspth month yyyy')from dual
• To_date function:
• converts the char or varchar2 data type to a
value of date data type.
• syntax: to_date(char,'fmt')
• select to_char(to_date('12-jan1980'),'ddsp')fromk dual;
• select to_date('1980-December-17','yyyymonth-dd')from dual;
• note:
• specific month,year hiring candidates display.
• select ename,hiredate from emp where
(to_char(hiredate,'mon'))='jan'
(to_char(hiredate,'yy'))=83
•
•
•
•
•
•
How to insert specific date and Time values
create table datetime
(ename varchar2(10),time timestamp)
>insert into datetime values(sysdate)
>select *from datetime.
insert into datetime values('suma',to_date('12-jan2009 11:30:15 P.M.','dd-mon-yyyy hh:mi:ss p
• To_char(number conversion)
• converts number of number data type to a value of
varchar2 data type.
• syntax:to_char(number,fmt)
• Decimal indicator:D->99d99
• returns the specified position of the decimal
character.
• default decimal delimiter is period'.'
• select to_char(123434,'9999d99')from dual;
• Group separator:G->9G999
• Returns the specified position of the group separator.
• select sal,to_char(sal,'9G999')from dual;
• Trailing Minus:MI->9999MI
• Returns a positive value with a trailing blank.
• to_char(-10000,'L99g99D99MI')
• Roman number:RN
• to_char(1000,'RN')
• Dollar indicator:$->$9999
• Return value with a leading dollor value.
• to_char(200000,'$99,999.99)D
• Digit place Marker:9999
• select to_char(20.55-20,'99999')
•
(20.25-20,'99999')
• ISO current indicator:C->9999
• Returns specified position of the ISO currency
symbol.
• select 1000,to_char(1000,'c9999.99')from dual;
• hexadecimal:X->XXXX
•
to_char(2000,'XX
• Aggregating Or Group functions:
• Returns a summary information for each group.
• Group functions can appear in only i)select lists
ii)order by and having clause
•
•
•
•
but we cannot use in where clause,group by clause.
The following seven are:
i)AVG ii)Sum iii)Maximum iv)Minimum
v)Count Vi)Standard deviation vii)variance
• AVG:
• It returns the avg value of column.
• it ignores null values.
• syntax:AVg(distinct/allcolumn)
• Eg: select AVG(sal) from emp;
•
AVG(distinct sal)
• Sum:
• it returns the sum value of column.
• syntax:sum(
)
•
•
•
•
Maximum:
it returns the maximum value of column.
syntax:Max(
)
select max(ename)from emp;
•
•
•
Minimum:
it returns the minimum value of column.
syntax:MIN(
)
•
select min(comm)from dual;
•
Count:
•
•
it gives the no of rows in the query.
it * used to returns all including duplicated and nulls.
•
syntax:count(*/distinct/all coulmn)
•
•
•
select count(*)from emp;
select count(ename)from emp;
select count(distinct job)from emp;
• Group By Clause:
• The Group by clause is used to decide the rows in table into
groups.
• Syntax:select columname1,colmname2,....
•
from table
•
[where condition(s)]
•
[Group by column name(s)]
•
[order by column(s)];
• note:
• All select clause stmt columns must be in group by clause.
• but all group by clause columns list may or maynot used in
select stme
• column alias are not used in group by
• Default Rows are sorted by ascending order of the columns
included in the
• group by clause.
• Having clause:
• u may not want all the summary rows returned by a group by
clause.
• using Having clause u can eliminate summary rows.
• The clause is used to filter the data that is associated with
group function.
• syntax:
•
•
•
•
•
•
Select column,group_function(column)...
From table
[where condition]
[Group by]
[having expression];
[order by column/alias];
• note:Group by clause can used ,w/o a group function
in the select stmt.
•
Having clause can used,w/o a Group By clause in
the select stmt.
• EG:
• select deptno,count(deptno) from emp
Group by deptno having count(deptno)>3;
• select AVG(sal) from emp having avg(sal)>2500;
• select job,sum(sal) payroll from emp
where job='clerk'
group by deptno
having min(sal)<1000;
• Nesting of Group function:
• Group functions can be nested to depth of two
levels.
• select max(avg(sal)from emp
• group by deptno;
• select max(sum(sal),min(avg(sal) from emp
group by deptno;
•
•
•
•
Miscellaneous functions:
Greatest:
syntax:Greatest(expr1,expr2,.....)
it is used to pick highest values from list of values.
•
•
•
•
•
•
•
select greatest('sibel','codd','oraclecorpo')from dual;
(5000,40000,30)
('10-jul-05','20-jul-05')
('allen',100,300,5000,'oracle')
Least:
it used to pic lowest value from list of values.
select lowest('c','m','n','b')from dual;
•
•
•
•
•
•
•
user:
syntax:user
returns the current oracle users name.
select user from dual;
select *from all_user;
uid:
it gives the number that identified the uyser
since oracle is used in multi-user environment.
• select userenv('isdba')
•
('language') (lang)
•
('terminal')
• Vsize:
• it returns the number of bytes in the internal
representation of expr.
• if expr is null,function returns null.
• select ename,vsize(ename)from dual
•
Components of SQL:
•
•
•
•
DRL/DQL:(Data Retrival/Query Language:
Select:
it is used to retrive the informatio from database objects for read onl purpose.
Eg: select *from emp;
•
DML:(Data Manipulation Language:
•
•
it is a core part of SQL
when u want to Add,Update or Delete data in the Database,u execute DML.
•
•
•
•
•
•
•
•
•
Insert:
Insert All:
By using Insert All we can insert the data in multiple tables at a time.
Eg:
create table D1 as
select *from dept where "ram"='krishna';
>insert all
into d1 valures(deptno,dname,loc)
into d2 values(deptno,dname,loc)
into d3 values(deptno,dname,loc)
select *from dept;
•
• Update:
• update is used to modify the existing values in table or in the base table of
view.
• Syntax:Update Tablename
• set coulumn=value,[column=value]
• [where condition];
•
•
•
•
eg:Update emp set sal=20000 where sal=10000;
Update emp set sal=4000,comm=null where job='salesman';
Update emp set hiredate=default
where empno=7788
• Delete:
• Remove existing rows from a table.
• Temporarly removed.
•
•
•
•
Sytax:
Delete [from] <tablename> [where condition];
eg:Delete from emp
Delete from emp where empno in(7788,7902);
• Merge:
• Merge stmt is used to select rows from one
or more sources for update
• or insert into one or more tables Data.
• DDL:(Data Definition Language)
•
Alter to modify the struture of the table
• (Add new columns,remove existing columns)
• Syntax:
• Alter table <tablename>
• add (column name datatype,colmn datatype).....;
• default it willl come last only and contains null values.
• modify:
• it is used to modify struture of existing columns.
• syntax: alter table<tablename>
• modify(column datatype)------);
• EG: alter table faculty modify city varchar2(20);
• note: we can increase width or precision of a numaric value.
• we can decrease the width of column when the columns only
only null values.
• we can change the datatype if the column contains nulls
• char->varchar2
•
•
•
•
Drop:
it is used to Remove columns perminently.
we can drop more than one column at a time.
once a column is dropped it cannot be revoked.
• Syntax: Alter table <tablename>
Drop column <name>;
• eg: alter table temp drop column empno;
• alter table temp drop(job,mgr);
• Rename:
• syntax: Alter table <tablename>
• Rename <old column>to <newcolumn>
• Alter table emp
• rename emp to employee.
• Truncate:
• syntax:Truncate <tablename>
• using this data is gone but table struture is
there.
• Transaction Control language:TCL
• Transaction changes can be made permenent to a database only are
comitted.
• Commit:
• used to save changes made by DML stmts.
• Types of commits:
• Implicit: DDL,DCL Stmts is automatically committed by implicitly commit.
• Explicit: it is given by user,valid for only DML operations.
• Syntax: Commit;
• Rollback:
• previous state of the date is returned or Restored
• Syntax:
• Rollback;
• Savepoint:
• save point is a point with in particular
Transaction to which you may rollback w/o
rollback the entire transaction.
•
•
•
•
>savepoint s0;
insert into deptvalues(
savepoint s1;
rollback to savepoint s1;
);
• DCL:(Data Control Language)
• Share the information between users.
• Grant: give the permission to the user
• Grant Dba to usera
• grant insert ,update on emp to usera;
• grant all on emp to usera; (insert,update,delete,select)
• grant all on en•mp to bpr,sys,sss;
• grant all on emp to public;
• REVOKE:
• It is used to remove the access allowed by
grant.
• Revoke can assign to
tables,views,synonyms,sequences.
• Revoke insert,update on emp from usera;
• Revoke all on emp from public;
• Diff b/n Truncate And Drop
• Diff b/n Truncate and delete.