EXTERNAL TABLE

-----------------------------------------------------------

. EXTERNAL TABLE .

-----------------------------------------------------------

(SOME FEAUTRE---->READ ONLY,STORE IN OUTSIDE OF DATABASE FILE,STORE IN FLAT FILE,NO DML STATEMENT WILL WORK, NO IN DEX CAN BE CREATED)

Create a directory in any path

c:/first is my directory

Creating an directory object amed first

create directory first as 'c:/first';

Create a file "emp1.txt" inside the directory and in sert the following data into it

10,jones,11-Dec-1934

20,smith,12-Jun-1972

Create an external table

CREATE TABLE oldemp

(empno NUMBER(8), empname CHAR(20), birthdate DATE)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY first

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY ','

)

LOCATION ('emp1.txt')

)

PARALLEL 5

REJECT LIMIT 200;

--->(ORACLE_LOADER)oracle driver from its external representation into an equivalent Oracle native representation

--->(ACCESS PARAMETER)data identificaton from the flat file(emp1.txt)

--->LOCATION identification of flat file

--->PARALLEL enables five parallel execution servers to simultaneously scan the external data sources (files) when executing the INSERT INTO TABLE statement

--->The REJECT LIMIT clause specifies that if more than 200 conversion errors occur during a query

of the external data, the query is aborted and an error returned*/

close the session and after starting another session query the table. It will remain as it is

Querying extenal table

select * from oldemp;

Open the "emp1.txt" and add the following data

30,arka,13-jun-1999

40,gora,12-jun-2004

Again query the data you will see the data added to the table

Now remove the "emp1.txt"

And query it again you will see the error

Take it to the old place and query again. it will run clearly

CONSTRAINT

<------CONSTRAINT ----->

WHICH CAN RESTRICT SOMETHING

~~~~~~~~~~~~ ABOUT DIFFERENT TYPES OF CONSTRAINTS~~~~~~~~~

NOT NULL

this ensures that NULL values are not permitted for the columns, since they serve as keys for operation on this table. Columns without the NOT NULL constraint allow NULL values. NOT NULL is one of several intigrity constraints thatmay be defined for table.

UNIQUE

this designates a column or combination of column as a unique key. No two rows in the table can have the same value for this key. Nulls are allowed if the UNIQUE key is used on a single column.

PRIMARY key

As with UNIQUE keys a primary key enforces uniqueness of the column or column combinations involved and a uique index is created to manage this. There may however be only one primary key in a table and this is known as definitive key ehrough which rows in the table are individually identifier. NULLS are not allowed in a primary key clumns. the long datatypes cannot be included in a primary key. a primary key can contain 16 columns at most. A UNIQUE index is creted on the column contained in a the primary key.

FOREIGHN KEY

foreign key provide referential intigrity rule either within a table or between tables. A foreign key is used in relation ship with either a primary or unique key elsewhere.

CHECK constraints

the check constraints explicitly defines a condition that each row must satisfy.

DEFINING CONSTRAINTS (ALSO EXAMPLE OF DEFINING PRIMARY KEY CONSTRAINT)

CREATE TABLE COUNTRY(ID NUMBER(4),NAME VARCHAR2(30), RANK NUMBER(4), CONTINENT VARCHAR2(30),CONSTRAINT ID_PK PRIMARY KEY(ID));

INSERT INTO COUNTRY VALUES(1,'INDIA',1,'ASIA');

VIEW CONSTRAINT FROM DATA DICTIONARY

SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME='ID_PK';

NOT NULL

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30));

INSERT INTO STATE VALUES( NULL,'WEST BENGAL',1,'INDIA');

UNIQUE

CREATE TABLE STATE(ID NUMBER(4) ,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_UNI UNIQUE(ID));

INSERT INTO STATE VALUES( NULL,'WEST BENGAL',1,'INDIA');

FOREIGN KEY

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_FK FOREIGN KEY(ID) REFERENCES COUNTRY(ID),CONSTRAINT ID_UNI UNIQUE(ID));

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_FK FOREIGN KEY(ID) REFERENCES COUNTRY(ID) ON DELETE CASCADE,CONSTRAINT ID_UNI UNIQUE(ID));

CREATE TABLE STATE(ID NUMBER(4) CONSTRAINT ID_NNUL NOT NULL,NAME VARCHAR2(30),RANK NUMBER(4),COUNTRY VARCHAR2(30),CONSTRAINT ID_FK FOREIGN KEY(ID) REFERENCES COUNTRY(ID) ON UPDATE CASCADE,CONSTRAINT ID_UNI UNIQUE(ID));

INSERT INTO STATE VALUES( 1,'WEST BENGAL',12,'INDIA');

PROOF OF GIVING MULTIPLE CONSTRAINT TO ONE TABLE

SELECT CONSTRAINT_NAME,TABLE_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='STATE';

CHECK CONSTRAINT

CREATE TABLE CHK(NAME VARCHAR2(10),SAL NUMBER(7),CONSTRAINT SAL_CHK CHECK(SAL>2000));

INSERT INTO CHK VALUES('ARKA',100);

INSERT INTO CHK VALUES('ARKA',2904);

ADDING A CONSTRAINT

ALTER TABLE COUNTRY MODIFY(ID CONSTRAINT ID_PK NOT NULL);

DROPPING A CONSTRAINT

ALTER TABLE COUNTRY DROP CONSTRAINT ID_PK CASCADE;

DISABLE A CONSTRAINT

ALTER TABLE COUNTRY DISABLE CONSTRAINT ID_PK CASCADE;

ENABLE A CONSTRAINT

ALTER TABLE COUNTRY ENABLE CONSTRAINT ID_PK CASCADE;

DDL EXTENSION

. DDL EXTENSION .

******TWO TABLE USED ARE IN THESE EXAMPLE***********

create table stud(name varchar2(10),roll number(5),DOB date);

CREATE TABLE JOB_STUD(NAME VARCHAR2(30),MANAGER NUMBER(10),DUTY VARCHAR2(30));

-------------------------

INSERT ALL (UNCONDITIONAL)

INSERT ALL

INTO STUD VALUES(NAME,ROLL,DOB)

INTO JOB_STUD VALUES(NAME,MANAGER,DUTY)

SELECT ENAME NAME,EMPNO ROLL,HIREDATE DOB,MGR MANAGER,JOB DUTY FROM EMP

WHERE SAL>2000;

-------------------------

SELECT * FROM STUD;

SELECT * FROM JOB_STUD;

-------------------------

TRUNCATE TABLE STUD;

TRUNCATE TABLE JOB_STUD;

-------------------------

INSERT ALL (NCONDITIONAL)

INSERT ALL

WHEN DEPTNO=10 THEN

INTO STUD VALUES(NAME,ROLL,DOB)

WHEN DEPTNO =20 THEN

INTO JOB_STUD VALUES(NAME,MANAGER,DUTY)

SELECT ENAME NAME,EMPNO ROLL,HIREDATE DOB,MGR MANAGER,JOB DUTY,DEPTNO FROM EMP

WHERE SAL>2000;

-------------------------

SELECT * FROM STUD;

SELECT * FROM JOB_STUD;

-------------------------

TRUNCATE TABLE STUD;

TRUNCATE TABLE JOB_STUD;

-------------------------

INSERT FIRST (ONLY THE FIRST VALID EXPRESSION UNDER WHEN CLAUSE WILL WORK)

INSERT FIRST

WHEN MANAGER IS NULL THEN

INTO STUD VALUES(NAME,ROLL,DOB)

WHEN DUTY ='PRESIDENT' THEN

INTO JOB_STUD VALUES(NAME,MANAGER,DUTY)

SELECT ENAME NAME,EMPNO ROLL,HIREDATE DOB,MGR MANAGER,JOB DUTY FROM EMP

WHERE SAL>2000;

-------------------------

SELECT * FROM STUD;

SELECT * FROM JOB_STUD;

-------------------------

TRUNCATE TABLE STUD;

TRUNCATE TABLE JOB_STUD;

-------------------------

Group function Enhanced

. Group function Enhanced .

NORMAL GROUP BY

SELECT AVG(SAL),MAX(SAL), COUNT(*) FROM EMP WHERE JOB LIKE 'CL%' GROUP BY SAL;

NORMAL HAVING CLAUSE

SELECT deptNO, AVG(sal) FROM emp GROUP BY deptNO HAVING AVG(sal) >2200;

ROLLUP OPERATOR(DISPLAYS GROUP FUNCTIONS SOLUTION ON PERTICULAR WHERE CLAUSE)

SELECT deptNO, JOB, SUM(sal) FROM emp WHERE deptNO <>

CUBE OPERATOR

SELECT deptNO, JOB, SUM(sal) FROM emp WHERE deptNO <>

GROUPING (USING CUBE)

SELECT deptNO, JOB, SUM(sal),GROUPING(DEPTNO) DEPAT,GROUPING(JOB) JOB FROM emp WHERE deptNO <>

GROUPING OPERATOR(USIING ROLLUP)

SELECT DEPTNO DEPTID, job JOB,SUM(SAL),GROUPING(deptNO) GRP_DEPT,GROUPING(job) GRP_JOB FROM emp WHERE deptNO <>

GROUPING SETS

SELECT DEPTNO,JOB,MGR,AVG(SAL) FROM EMP GROUP BY GROUPING SETS((DEPTNO,JOB),(JOB,MGR));

COMPOSIT COLUMN

SELECT DEPTNO,JOB,MGR,AVG(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,(JOB,MGR));

set operator

Set Operator

UNION Returns All distinct rows selected by either query

UNION ALL Returns All rows selected by either query, including all duplicates

INTERSECT Returns All distinct rows selected by both queries

MINUS Returns All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement

----------------------------------------------------------------------------

UNION

select job from emp where deptno=10

union

select job from emp where deptno=30

------------------------------

UNION ALL

select job from emp where deptno=10

union ALL

select job from emp where deptno=30

------------------------------

INTERSECT

select job from emp where deptno=10

INTERSECT

select job from emp where deptno=30

------------------------------

MINUS

select job from emp where deptno=10

MINUS

select job from emp where deptno=30

------------------------------

Unlock the Teaching account

Unlock the Teaching account

The HR user is for accout for teaching. IN any stydy material from oracle is containning the example of the built-in table of this user.

------By default password------

username:-HR

password:-6399F3B38EDF3288

------Above is collected from this command------

sql>select * from dba_user where username='hr';

-----------------------------------------------------------------------------------------------

the user is generally locked and the account is set to expired.

to unlock this account give the command.

sql>alter user hr identified by arka account unlock;

-----------------------------------------------------------------------------------------------

Now you can see all the tables in the account like employee table

sql>Select * from tab;

sql>select * from employee;

JOINING

CARTESIAN PRODUCT

SELECT * FROM EMP,DEPT;

------------------------------------------------------------------------------

ORACLE 8I AND PRIOR

------------------------------------------------------------------------------

EQUI JOIN

SQL>SELECT EMP.ENAME,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

SQL>SELECT EMP.ENAME,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

AND ENAME='ALLEN'

-------------

USING TABLE ALIASES

SELECT E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO

AND ENAME='ALLEN'

------------

NON-EQUIJOIN

SELECT E.ENAME,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

------------

OUTERJOIN (Place the outer join symbol following the name of the column in the table without the matching rows)

SQL>SELECT e.ENAME, D.DNAME

FROM emp e, dept d

WHERE e.deptNO(+) = d.deptNO

SELECT e.ENAME, D.DNAME

FROM emp e, dept d

WHERE e.deptNO= d.deptNO(+)

-------------------

SELFJOIN

SQL>SELECT WORKER.ENAME ||' WORKS FOR '|| MANAGER.ENAME FROM EMP WORKER,EMP MANAGER WHERE WORKER.EMPNO=MANAGER.EMPNO

------------------------------------------------------------

SQL-1999 COMPLIENT JOIN

------------------------------------------------------------

CROSS JOIN(SIMILER TO CARTESIAN JOIN)

SELECT ENAME,DNAME FROM EMP CROSS JOIN DEPT;

------------

NATURAL JOIN(SIMILAR AS EQUI JOIN)

SELECT ENAME,DNAME FROM EMP NATURAL JOIN DEPT

------------

USING CLAUSE(WHEN TWO OR MORE COLUMN IS SIMILER THEN "USING" CLAUSE POINTS TO THE PERTICULAR)

SELECT E.ENAME,D.DNAME FROM EMP E JOIN DEPT D USING(DEPTNO);

------------

ON CLAUSE

SELECT E.ENAME,D.DNAME

FROM EMP E JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------

THREE WAY JOIN USING "ON" CLAUSE

SELECT employee_id, city, department_name

FROM employees e

JOIN departments d

ON d.department_id = e.department_id

JOIN locations l

ON d.location_id = l.location_id;

------------

LEFT OUTER JOIN

SELECT E.ENAME,D.DNAME

FROM EMP E

LEFT OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------

RIGHT OUTER JOIN

SELECT E.ENAME,D.DNAME

FROM EMP E

RIGHT OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------

FULL OUTER JOIN

SELECT E.ENAME,D.DNAME

FROM EMP E

FULL OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------

DDL and DML

DDL(CREATE,ALTER,DROP,RENAME,TRUNCATE)

DML(INSERT,UPDATE,DELETE,MERGE)

Create table

sql>create table stud(name varchar2(10),roll number(5),DOB date);

---------------------------------------------------------------------------

Insert

---------------------------------------------------------------------------

NORMAL INSERT

insert into stud(name,roll,DOB) values('ARKA',1,'12-DEC-1985');

ANOTHER METHOD

insert into stud values('DIBYENDU',2,'16-DEC-1983');

INSERT WITH NULL VALUE

insert into stud values('DIBYENDU',2,NULL);

INSERT ROWS FROM ANOTHER TABLE

INSERT INTO STUD(NAME,ROLL,DOB) SELECT ENAME,EMPNO,HIREDATE FROM EMP WHERE DEPTNO=10;

INSERTING SPECIAL CHARECTER

INSERT INTO STUD VALUES('RONIT',56,SYSDATE);

---------------------------------------------------------------------------

UPDATING

---------------------------------------------------------------------------

UPDATING SINGLE ROW

UPDATE STUD SET ROLL=10 WHERE NAME='ARKA';

UPDATING ALL ROW IN A TABLE

UPDATE STUD SET ROLL=10;

UPDATING THROUGH SUBQUERY

UPDATE STUD

SET ROLL=(SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO=10),

DOB=(SELECT MAX(HIREDATE) FROM EMP WHERE DEPTNO=10)

WHERE NAME='DIBYENDU';

delete ROWS FROM table

delete from stud WHERE DOB='09-JUN-81';

DELETE ALL ROWS FROM A TABLE

DELETE FROM STUD;

DEFAULT CLAUSE

INSERT INTO STUD VALUES('SAUMYA',DEFAULT,DEFAULT);

UPDATE STUD SET ROLL=DEFAULT WHERE NAME='RONIT';

PREPARATION BEFORE WATCH THE

MERGE COMMAND

ALTER TABLE STUD ADD (EMPNO NUMBER(5));

UPDATE STUD SET EMPNO=(SELECT EMPNO FROM EMP WHERE ENAME='KING') WHERE NAME='KING';

UPDATE STUD SET EMPNO=(SELECT EMPNO FROM EMP WHERE ENAME='MILLER') WHERE NAME='MILLER';

UPDATE STUD SET EMPNO=(SELECT EMPNO FROM EMP WHERE ENAME='ALLEN') WHERE NAME='RONIT';

*********************************************************************

MERGE ROWS

MERGE INTO STUD C

USING EMP E

ON (C.EMPNO=E.EMPNO)

WHEN MATCHED THEN

UPDATE SET

C.NAME=E.ENAME,

C.ROLL=E.EMPNO,

C.DOB=E.HIREDATE

WHEN NOT MATCHED THEN

INSERT VALUES(E.ENAME,E.EMPNO,E.HIREDATE);

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------

ALTER

---------------------------------------------------------------------------

alter table

ADDING A TABLE

alter table stud add(gender char);

MODIFY A TABLE

alter table stud modify (gender varchar2(1));

alter table stud add(class number(2));

SETTNG A COLUMN UNUSED

alter table stud set unused(class);

insert into stud(class) values(12);

alter table stud drop unused columns;

RENAME A OBJECT(TABLE)

rename stud to student;

rename STUDENT to stud;

TRUNCATE A TABLE

TRUNCATE TABLE STUD;

-------A faulty condition--------

after "delete from stud" command

insert into stud values('DIBYENDU',2,'16-DEC-1983');

above command will not run. But this comand run

insert into stud values('DIBYENDU',2,'16-DEC-1983','m');

system checking

It is a checking system that it can create an user and give privilege to it

CONNECT SYSTEM/MANAGER;

DROP USER ARKA CASCADE;

CREATE USER ARKA

IDENTIFIED BY STG;

GRANT create session, alter session, create table,

create view,

create sequence, UNLIMITED TABLESPACE to ARKA with admin option;

CONNECT ARKA/STG;

CREATE TABLE AR(NAME VARCHAR2(30));

Function in oracle

SINGLE ROW FUNCTION

1. Act on each row return in the query

2.Return one result per row

3. Expects one or more user argument

4. May be nested

Character function

LOWER

Select LOWER ('DNAME'), LOWER ('SQL COURSE') FROM DEPT;

------

UPPER

SELECT ENAME FROM EMP WHERE ENAME= UPPER('&ENAME');

------

INITCAP

SELECT INITCAP(DNAME), INITCAP(LOC) FROM DEPT

------

CONCAT

SELECT CONCAT(ENAME,JOB) "JOB FROM EMP WHERE EMPNO=7900;

------

LPAD

SELECT LPAD(DNAME,20,'*'),LPAD(DNAME,20), LPAD(DNAME,20,'.') FROM DEPT;

------

RPAD

SELECT RPAD(DNAME,20,'*') FROM DEPT;

------

SUBSTR

SELECT SUBSTR('ORACLE',2,4),SUBSTR("DNAME",2,4),SUBSTR(DNAME,3) FROM DEPT;

------

INSTR

SELECT DNAME,INSTR(DNAME,'A'),INSTR(DNAME,'ES'),INSTR(DNAME,'C',1,2) FROM DEPT;//3RD ONE 2ND C POSITION

-----

LTRIM

SELECT DNAME,LTRIM(DNAME,'A'),LTRIM(DNAME,'ARS') FROM DEPT;

------

RTRIM

SELECT DNAME,RTRIM(DNAME,'H'),RTRIM(DNAME,'GHS') FROM DEPT;

------

SOUNDEX

SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX('FRED');

------

LENGTH

SELECT LENGTH('SQL COURSE'),LNGTH(DNAME), LENGTH(DEPTNO),LENGTH(DNAME) FROM DEPT;

------

TRANSLATE

SELECT ENAME,TRANSLATE(ENAME,'C','P'),JOB,TRANSLATE(JOB,'AR','IT') FROM EMP WHERE DEPTNO=10;

REPLACE

SELECT JOB,REPLACE(JOB,'SALESMAN','SALESPERSON') jobr,REPLACE(ENAME,'CO','PX') FROM EMP;

Number function

ROUND

SELECT ROUND(45.923,1),ROUND(45.923),ROUND(45.323)ROUND(SAL/32,2)

-------

CEIL

SELECT CEIL(SAL),CEIL(101.76) FROM EMP WHERE SAL BETWEEN 3000 AND 5000;

-------

FLOOR

SELECT FLOOR(15.7) "Floor" FROM DUAL;

-------

POWER

SELECT SAL,POWER(SAL,2),POWER(50,5) FROM EMP WHERE DEPTNO=10;

-------

SQRT

SELECT SAL,SQRT(SAL),SQRT(40) FROM EMP WHERE SAL>2000;

-------

SIGN

SELECT SAL-COMM,SIGN(SAL-COMM) FROM EMP WHERE DEPTNO=30;

SELECT ENAME,SAL,COMM FROM EMP WHERE SIGN(SAL-COMM)=1;

-------

ABS

SELECT SAL-COMM,ABS(SAL-COMM) FROM EMP WHERE DEPTNO=30;

-------

MOD

SELECT SAL,COMM,MOD(SAL,COMM),MOD(100,40) FROM EMP WHERE DEPTNO=30 ORDER BY COMM;

-------

EXP

SELECT EXP(4) "e to the 4th power" FROM DUAL;

-------

TRUNC

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

-------

LOG(M,N),SIN(N),SINH(N),TAN(N),TANH(N),COS(N),COS(H)

DATE FUNCTION

GENERAL FORMAT-----> 16-JUN-1985

SYSDATE

SELECT SYSDATE

FROM SYS.DUAL;

---------

ARITHMETIC OPERATION

SELECT HIREDATE,HIREDATE+7 FROM EMP WHERE HIREDATE LIKE '%JUN%';

---------

MONTHS_BETWEEN

SELECT MONTHS_BETWEEN

(TO_DATE('02-02-1995','MM-DD-YYYY'),

TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"

FROM DUAL;

---------

ADD_MONTHS

SELECT TO_CHAR(

ADD_MONTHS(hiredate,1),

'DD-MON-YYYY') "Next month"

FROM emp

WHERE ename = 'SMITH';

---------

LAST_DAY

SELECT SYSDATE,

LAST_DAY(SYSDATE) "Last",

LAST_DAY(SYSDATE) - SYSDATE "Days Left"

FROM DUAL;

---------

NEXT_DAY

SELECT NEXT_DAY('15-MAR-98','TUESDAY') "NEXT DAY"

FROM DUAL;

---------

TO_CHAR

SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')

"New date format" FROM emp

WHERE ename = 'BLAKE';

---------

TO_DATE

SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.')

FROM DUAL;

---------

RR DATE FORMAT

select empno,ename, to_char(hiredate,'dd-mon-yyyy')

from emp

where hiredate

---------

AGREGATE FUNTION OR GROUP FUNCTIONOR MULTIPLE ROW FUNCTION

AVG

SELECT AVG(sal) "Average"

FROM emp;

---------

MAX

SELECT MAX(sal) "Maximum" FROM emp;

---------

SUM

SELECT SUM(sal) "Total"

FROM emp;

---------

COUNT

SELECT COUNT(*) "Total",COUNT(job),COUNT(DISTINCT job) FROM emp;

---------

MIN

SELECT MIN(hiredate) "Earliest" FROM emp;

---------

GROUP BY

SELECT JOB,AVG(SAL)

FROM EMP

GROUP BY JOB;

---------

GROUP UNDER GROUPS

SELECT DEPTNO,JOB,AVG(SAL)

FROM EMP

GROUP BY DEPTNO,JOB;

---------

ERRONEOUS STATEMENT

SELECT DEPTNO,MIN(SAL)

FROM EMP;

---------

HAVING

SELECT DEPTNO,AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING COUNT(*)>3

**************************SOME EXTRA FUNCTION**********************

NVL2 FUNCTION (IF FIRST EXPRESSION IS NULL IT RETURNS SCOND EXPR OTHERWISE 3RD)

SQL>SELECT ENAME,COMM,NVL2(COMM,0,1) FROM EMP;

---------

NULLIF(IF TWO EXPRESSION IS EQUAL IT RETURNS NULL)

SQL>SELECT ENAME, LENGTH(ENAME) EXPR1,LENGTH(JOB) EXPR2,NULLIF(LENGTH(ENAME),LENGTH(JOB)) RESULT FROM EMP;

---------

CASE(IF ELSE IN SQL)

SELECT ENAME, SAL,JOB,

CASE ENAME WHEN 'ALLEN' THEN SAL*10

WHEN 'SMITH' THEN SAL*100

WHEN 'JAMES' THEN SAL*1000

ELSE SAL END "REVISED SALARY"

FROM EMP

USER administration

USER administration



CRETING A USER
CREATE USER ARKA
IDENTIFIED BY STG

CREATE USER ANIS
IDENTIFIED BY STG
-------------------------------------------------------------------------------

GRANT USER PRIVILEGE
GRANT create session, alter session, create table,
create view, create procedure, create trigger, create library,
create tablespace, alter tablespace, drop tablespace,
execute any procedure, UNLIMITED TABLESPACE, create public synonym,
create sequence to ARKA with admin option;
-------------------------------------------------------------------------------

CREATE ROLE
CREATE ROLE MANAGER;
-------------------------------------------------------------------------------

GRANTING PRIVILEGE TO ROLe
GRANT create session, alter session, create ANY table,
create view, create procedure, create trigger, create library,
create tablespace, alter tablespace, drop tablespace,
execute any procedure, UNLIMITED TABLESPACE, create public synonym,
create sequence to MANAGER;
-------------------------------------------------------------------------------

granting role to user
GRANT MANAGER TO ARKA,ANIS;
-------------------------------------------------------------------------------

CHANGING PASSWORD
ALTER USER ARKA
IDENTIFIED BY STGORA
-------------------------------------------------------------------------------

expiring password
alter user arka password expire;
-------------------------------------------------------------------------------

DELETE A USER
DROP USER ARKA CASCADE;
DROP USER ANIS;
-------------------------------------------------------------------------------

CREATE TABLE AR(NAME VARCHAR2(30));

Oracle basics and opearators

Oracle basics and opearators
selecting all column
select * from emp;
------------------------------------------------------------
sql*plus command

sql>SET AUTOCOMMIT on
sql>SET AUTOCOMMIT 10
sql>set linesize 120
----------------------------------------------------
sql>COLUMN ENAME HEADING 'EMPLOYEE |NAME'
sql>COLUMN SAL FORMAT $9,999,990.99
----------------------------------------------------
****ACCOMODATING SQL COMMAND TO A FILE********

SQL>select empno,ename,sal from emp where job='&1'
SQL>save c:/job1.sql
SQL>START c:/job1.sql
----------------------------------------------------
*****define a variable ************
sql>define rem='sal*12+nvl(comm,0)';
sql>select ename,job,&rem from emp order by &amp;rem;
sql>undefine rem
----------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!operators!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ARITHMETIC
select 12+3,13*4,14-5,16/4 from dual;
----------------------------------------------------
CONCATENATION
select empno||'_'||ename employee, 'works in department' from emp;

----------------------------------------------------------------

****null value******
select ename,sal*12+comm annual_sal from emp;

select ename,sal*12+nvl(comm,0) annual_sal
from emp;

------------------------------------------------------------
*****where clause and operators*******

select * from emp
where ename='SMITH'
-----
where deptno<20>sal
-----
where sal>=comm
-----
where sal between 1000 and 2000
-----
where mgr in(7902,7566,7788)
-----
where ename like 'S%'
-----
where comm is null
-----
where sal between 1000 and 2000 and job='CLERK'
-----
where sal between 1000 and 2000 or job='CLERK'
---------------------------------------------------------------

*****distinct clause*****

select distinct deptno,job
from emp;

-------------------------------------------------------------
****order by*******
select deptno,job,ename
from emp
order by deptno,sal desc;
------------------------------------------------------------
*******USER INPUT********
select * from emp where deptno=&Department_no

-----------------------------------------------------------

creation and manage of sequence

create and manage sequence

+++++++creation of a simple tabl to show sequence+++++++++++
create table depart(did number(6),dname varchar2(30),locid number(10));

++++++++sequence creation++++++++++++++++
***SEQUENCE1****
create sequence depart_s
increment by 10
start with 120
maxvalue 9999
nocache
nocycle;

***SEQUENCE2****
create sequence depart_s1
increment by 1
start with 0
maxvalue 99
MINVALUE 0
nocache
nocycle

++++++++++confirming sequence++++++++++++
select * from DBA_SEQUENCES WHERE SEQUENCE_OWNER='SCOTT';

++++++++++SHOWING THE VALUE GENERATED BY SEQUENCE++++++++++++
SELECT DEPART_S.CURRVAL FROM DUAL;
SELECT DEPART_S.NEXTVAL FROM DUAL;

+++++++++USING A SEQUENCE++++++++++++++
INSERT INTO DEPART(DID,DNAME,LOCID)
VALUES(DEPART_S.NEXTVAL,'DEPARTMENT',DEPART_S1.NEXTVAL);

SELECT * FROM DEPART;

creating and updating view

here I explain how to create and manage view read it and practise it

+++++++Creating empcheck view+++++++
create view empcheck
as select empno,ename,job
from emp

++++++++++CHECK THE VIEW CREATED UNDER DATA ICTIONARY+++++++++++
SELECT OWNER,VIEW_NAME,TEXT FROM DBA_VIEWS WHERE OWNER='SCOTT';

+++++++replacing empcheck view+++++++++++++++++++
create or replace view empcheck
as select empno,ename,job
from emp
where empno>6000


+++++++++++checking+++++++++++++
desc empcheck

select * from empcheck;

+++++++++++Column Aliasing+++++++++
create or replace view empcheck
as select empno Employee_no,ename Employee_name,job Job
from emp
where empno>6000

++++++++Another type using column aliases+++++++++++++
create or replace view empcheck( Employee_no, Employee_name,Job )
as select empno,ename,job
from emp
where empno>6000

++++++++++++Creatng a Complex view++++++++++
create OR REPLACE view emp2
(name,minsal,maxsal,avgsal)
AS SELECT D.DNAME,MIN(E.SAL),MAX(E.SAL),AVG(E.SAL)
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
GROUP BY D.DNAME;

++++++++++++WITH CHECK CONSTRAINTS+++++++++++++
CREATE OR REPLACE VIEW EMP3
AS SELECT *
FROM EMP
WHERE ENAME='ALLEN'
WITH CHECK OPTION CONSTRAINT EMP3_CK;

SELECT * FROM EMP3;

UPDATE EMP3
SET ENAME='ARKA'
WHERE ENAME='ALLEN'

++++++++++++WITH READ ONLY++++++++++++++++++++
CREATE VIEW EMP3 (name, NO, JOB)
AS SELECT ENAME, EMPNO,JOB
FROM EMP
WITH READ ONLY;

CHECKING
DELETE FROM EMP3
WHERE NAME='SMITH'

++++++++++++TOP N ANANLYSYS++++++++++++++++
SELECT ENAME,SAL,ROWNUM AS RANK
FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM<=3