Sql Commands

<==> raju.ch88@gmail/yahoo.com <--> +918019574321 <==>



set the page size
-->SET PAGESIZE 100
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80 // set line 100
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO OFF

create a user
-->create user identified by
-->grant connect,resource to ;

see the wich user is we r using
-->show

connect with another user
-->conn /

save the procedure
-->commit

describe the table
-->desc

-->spool on
-->spool ofF

DEFINING COLUMN ALIAS
-->SELECT ENAME EMPLOYEENAME FROM EMP;
CONCATINATION
-->SELECT ENAME||DEPTNO FROM EMP;
-->select 'The Basic Salary of '||ename||' is Rs: ' ||sal "designation" from emp;
To stop feedback
-->set feedback stop;

To copy all the one table to another table using -->AS
Ex:- create table temp AS select * from emp;

-- COMBINING TWO TABLES
select emp.deptno,ename,empno,job,loc,dname from emp,dept where emp.deptno=dept.deptno;

HANDLING NULL VALUES

NULL
--> SELECT * FRON EMP;
-->SELECT ENAME,EMPNO,COMM FROM EMP;
General Function

NVL function
-->select ename,deptno,nvl(ename,'abc') from emp;

To generate sequence no without using SEQUENCE,LOOP
-->create table test(no number(4),name varchar2(15));
-->insert into test values(nvl((select max(no) from test),100)+1,'&name');

NVL2 Function

-->select ename,deptno,comm,nvl2(comm,'abc','xyz')from emp;
-->select ename,deptno,comm,nvl2(comm,comm+500,100)from emp;
Nullif
-->select nullif(100,200)from emp;
coalesce
-->select coalesce(comm,mgr,9999,....) from emp;

Distinct/Unique
-->select distinct job from emp;
-->select unique deptno from emp;

Relational operators
<>,!=,^= -->Not Equal
= -->Equal
:= --> Asign to
--> select ename,empno, sal,job from emp where job>='M' and ename<='M';

LOGICAL OPERATERS
AND
-->SELECT * FROM EMP WHERE(DEPTNO=20 AND JOB='SALESMAN');
OR
-->SELECT * FROM EMP WHERE(DEPTNO=20 OR JOB='SALESMAN');
NOT
-->SELECT * FROM EMP WHERE NOT DEPTNO=20 AND JOB='SALESMAN'

-------> select ename,job,mgr,sal from emp where not sal!=800 or not job^='SALESMAN' and not mgr<>7839;

SQL*PLUS OPERATER

IN
-->SELECT * FROM EMP WHERE SAL IN(2000,3000);
NOT IN
-->SELECT * FROM EMP WHERE JOB IN('MANAGER','SALESMAN');

BETWEEN
-->select ename,hiredate from emp where hiredate between '17-dec-80' and '17-jan-82';
-->SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
NOT BETWEEN
-->SELECT * FROM EMP WHERE SAL NOT BETWEEN 1000 AND 3000;
LIKE
--> select ename from emp where ename not like '%A%';
IS NULL / IS Not Null
-->select ename,mgr,comm from emp where comm is null;
--> select ename,mgr,comm from emp where comm>=0
-->select ename,mgr,comm from emp where comm is not null;
OREDR BY
-->select ename,job,sal,empno from emp order by 4;
-->select ename,job,sal,empno from emp where job in('CLERK','MANAGER') order by 4;
-->select ename,job,sal,empno from emp where job in('CLERK','MANAGER') order by 4 desc;

SINGLE ROW FUNCTIONS

SYNTAX:- Func_name(column/expr,[argument1,arg2,....]);

CHARACTER FUNCTION

Lower
-->SELECT lower('Raju.chinthapatla') "My Name Is" from duel;
Upper
-->SELECT upper('Raju.chinthapatla') "My Name Is" from duel;
Initcap
-->SELECT initcap('RaJu chInTHaPaTla') "My Name Is" from duel;

/* ENVIRONMENT VARIABLE */
SYNTAX:- var [size] by default size is 1 byte
-->exec
is a command to excute
-->print :
To print the bind variable value

Ex: --> exec : vname := 'SMITH';

--> var name varchar2(20)
--> exec:name:='raju.chinthapatla'
--> print:exec
--> print:name
--> select initcap(:name) "My Name Is" from duel;
Concat
-->select concat('Raju','.Chinthapatla') "My Name Is" from duel;
-->select concat(concat(concat('Raju','.Chinthapatla'),' From'),'Warangal') "My Name Is" from duel;
-->select concat(concat('Raju','.Chinthapatla'),concat('From','Warangal')) "My Name Is" from duel;