Note: Learn from This URL, U Will Be Master in SQL

Note: Learn from This URL, U Will Be Master in SQL

1

lGuaranteed Popular Links :

Sql url :

Note: Learn from this URL, U will be Master in SQL

IBM Any Document – Reference :-

Gentle Introduction to SQL :

Introduction to Oracle: Tutorial Series :

Oracle PL/SQL Tutorial:

Oracle SQL*Plus Tutorial:

Oracle/SQL Tutorial :

SQL Tutorial :

SQL - A Practical Introduction. (Free Download) :

Oracle9i Database Online Documentation:

Oracle9i Database List of Books (Release 9.0.1)

Administrator Guide – DOC -

PL/SQL REFERENCE Guide:

SQL REFERENCE Guide:

SQL*Plus User's Guide and Reference

CONSTRAINTS

PRIMARY CONSTRAINTS : PRIMARY,UNIQUE,CHECK,REFERENCES)

SECONDARY CONSTRAINTS : NOT NULL,DEFAULT)

CONSTRAINTS BASED ON 2 LEVELS COLUMN LEVEL and TABLE LEVEL

Except Not null all others can be defined as Both table and column level.

But Not null as only column level.

Eg. For SECONDARY CONSTRAINTS

~~~~~~~~~~~~~~~~~~~~~~~

  1. NOT NULL CONSTRAINT

CREATE TABLE EMP9( ENO NUMBER(3) NOT NULL,

ENAME VARCHAR2(10));

2. DEFAULT CONSTRAINT

CREATE TABLE EMP9(ENO NUMBER(3) NOT NULL,

ENAME VARCHAR2(10),DOJ DATE DEFAULT SYSDATE);

3. PRIMARY CONSTRAINT(COLUMN LEVEL)

UNIQUE

CREATE TABLE EMP9(

ENO NUMBER(3) NOT NULL CONSTRAINT UNIEMPUNIQUE,

ENAME VARCHAR2(10));

PRIMARY KEY

CREATE TABLE EMP9(

ENO NUMBER(3)CONSTRAINT PKEMP9 PRIMARYKEY,

ENAME VARCHAR2(10));

4. CHECK CONSTRAINT

CREATE TABLE BANK(

ACNO NUMBER(2) CONSTRAINT PKBANK PRIMARY KEY,

ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK (ACTYPE IN

('SB','CA','RD')),

ACNAME VARCHAR2(10),

AMOUNT NUMBER(4));

5. REFERENCES

CREATE TABLE EMP9(

ENO NUMBER(3) CONSTRAINT PKE9 PRIMARY KEY,

JOB VARCHAR2(10),

ENAME VARCHAR2(10),

MGR NUMBER(4) REFERENCES EMP9(ENO));

6. REFERENCES(REFERING TO DIFFERENT TABLE)

CREATE TABLE DEPT9(DEPTNO NUMBER(2) CONSTRAINT PKDNO PRIMARY KEY, DNAME VARCHAR2(10), LOC VARCHAR2(10));

CREATE TABLE EMP9( EMPNO NUMBER(4),ENAME VARCHAR2(10),

SAL NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FKDNO REFERENCES DEPT9(DEPTNO));

7. TABLE LEVEL CONSTRAINTS

UNIQUE TABLE LEVEL

CREATE TABLE BANK( ACNO NUMBER(3),ACTYPE VARCHAR2(10),

BAL NUMBER(7,2),PLACE VARCHAR2(10),CONSTRAINT UNIBANK UNIQUE(ACNO,ACTYPE));

PRIMARY KEY(TABLE LEVEL)

CREATE TABLE BANK( ACNO NUMBER(2), ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK (ACTYPE IN ('SB','CA','RD')),AMOUNT NUMBER)

1. Display the Details of all employees working in the same Department

as 'FORD'

SELECT * FROM EMPLOYEES EMP, DEPARTMENT DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNAME = ‘FORD’

2. Display the details of all employees who are joined before 'FORD'

SELECT * FROM EMPLOYEES EMP, DEPARTMENT DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.JOINED_DATE<(

SELECT JOINED_DATE FROM EMPLOYEES EMP

WHERE EMP.DEPTNAME=’FORD’)

3. Display the Names of all the employees who are all getting the

salary Greater than the salary of 'SMITH' along with the

difference in salary

select ename,sal-(select sal from TEMP_EMP where ename='SMITH')

"DIFF SAL" from TEMP_EMP where sal>(select sal from TEMP_EMP where ename='SMITH')

4. Display all the employees working in chicago

5. find the two minimum salaries among table

SELECT SAL FROM(SELECT*FROMTEMP_EMPORDERBY SAL)

WHEREROWNUM<3

6. Nth Highest Salary

-- select min(sal)"nth highest sal"fromTEMP_EMP

where sal in( select sal from(selectDISTINCT(sal)fromTEMP_EMPorderby sal desc)whererownum<=&n)

-- SELECTDISTINCT(a.sal)FROMTEMP_EMP A

WHEREN =(SELECT COUNT (DISTINCT(b.sal))FROMTEMP_EMP B WHERE a.sal<=b.sal)

-- SELECT sal FROM(SELECTROWNUM rn, sal FROM(SELECTDISTINCT(sal) FROMtemp_empORDERBY sal desc)) x WHERE x.rn =:n;

7. How do i get Greatest salaray of dept group from employee (we have to tables EMP and DEPT. for exp: dept 10 have 10000,dept 20 have 5000 and dept 30 have 15000 salaries, i need dept 30 group salary(means sum of that group is 15000).)

-- select deptno,sal1

from(select deptno , sum(sal) sal1 fromtemp_empgroupby deptno orderby sal1 desc)

whererownum<=1

Differentiate between TRUNCATE and DELETE

TRUNCATE deletes much faster than DELETE

TRUNCATE / DELETE
It is a DDL statement / It is a DML statement
It is a one way trip,cannot ROLLBACK / One can Rollback
Doesn't have selective features (where clause) / Has
Doesn't fire database triggers / Does
It requires disabling of referential constraints. / Does not require

SUBQUERIES

SINGLE ROW SUBQUERY

MULTIPLE ROW SUBQUERY

SINGLE ROW SUBQUERY

  1. DISPLAY THE EMPLOYEES WHOSE JOB IS SAME AS THAT OF JONES

Select * from emp where job = (select job from emp

Where ename = ‘jones’);

  1. DISPLAY THE EMPLOYEES BELONGING TO MILLER'S DEPARTMENT

Select * from emp where dname = (select dname from emp

Where ename = ‘milers’);

  1. DISPLAY ALL THE EMPLOYEES REPORTING TO KING

Select * from emp where rname = (select rname from emp

Where ename = ‘king’);

  1. DISPLAY ALL THE EMPLOYEES WHO ARE GETTING MAX SALARY

Select * from emp where basal = (select max(basal) from emp);

  1. DISPLAY ALL THE DETAILS WHERE SAL IS GREATER THAN MILLER'S SALARY AND WHO ARE IN SALES DEPARTMENT.

Select * from emp where dname = ‘sales’ and basal > (select basal from emp where ename = ‘milers’);

  1. DISPLAY ALL THE DETAILS WHO HAVE JOINED IN SAME DATE AS THAT OF JAMES.

Select * from emp where doj = (select doj from emp where ename = ‘james’);

MULTIPLE ROW SUBQUERIES

  1. DISPLAY ALL THE DETAILS WHERE DEPT IS EITHER SALES OR RESEARCH

Select * from emp where dname = any(select dname from emp where dname = ‘sales’ or dname = ‘research’);

Select * from emp where dname = any(select dname from emp where

Dname like(‘sales’,’research’));

SUBQUERY RETURNING MULTIPLE COLUMNS

SELECT ENAME,JOB,MGR FROM EMP WHERE (JOB,MGR) IN(SELECT JOB,MGR FROM EMP WHERE EMPNO=7788);

EXPRESSIONS WITH SUBQUERIES

SELECT * FROM EMP WHERE SAL=(SELECT SAL+50 FROM EMP WHERE

ENAME='JONES');

ROWID

For each row in the database, the ROWID pseudocolumn returns a

row's address.ROWID values contain information necessary to locate a

row:

* which data block in the data file

* which row in the data block (first row is 0)

* which data file (first file is 1)

ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM

Of 1, the second has 2, and so on.

CORRELATED SUBQUERY

FIRST TRY USING MAX

5TH HIGHEST SALARY

SELECT * FROM EMP A WHERE 5=(SELECT COUNT(DISTINCT SAL)FROM EMP B WHERE A.SAL<=B.SAL);

DELETE DUPLICATE DEPARTMENT NUMBERS

DELETE FROM DEPT A WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM DEPT B WHERE A.DEPTNO=B.DEPTNO);

DISPLAY ALTERNATE ROWS

SELECT * FROM EMP WHERE ROWID IN

(SELECT DECODE(MOD(ROWNUM,2),0,ROWID) FROM EMP);

TO DISPLAY NTH HIGHEST SALARY

SELECT MAX(SAL) FROM EMP A WHERE &N=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL<=B.SAL);

SELECT * FROM EMP A WHERE &N=(SELECT COUNT(DISTINCT SAL)

FROM EMP B WHERE A.SAL<=B.SAL);

DELETE DUPLICATE OCCURENCES

DELETE FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT

GROUP BY DEPTNO HAVING COUNT(DEPTNO)>1);

DELETE FROM emp A WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM emp WHERE A.DEPTNO=B.DEPTNO);

DISPLAY ALTERNATE ROWS

SELECT * FROM GDEPT WHERE ROWID IN(SELECT DECODE(MOD(ROWNUM,2),0,ROWID) FROM GDEPT);

DISPLAY from NTH ROW

SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT WHERE ROWNUM<=(SELECT COUNT(*)-&N FROM DEPT));

DISPLAY DETAILS OF ALL EMPLOYEES WHOSE SAL IS GREATER THAN AVG(SAL) OF EMPLOYEES IN RESPECTIVE DEPTS

SELECT A.ENAME,A.DEPTNO,A.SAL FROM EMP A,EMP B

WHERE B.SAL>(SELECT AVG(SAL) FROM EMP B GROUP BY DEPTNO)

DISPLAY ALL THE DETAILS WHERE SAL> LOWEST SAL OF EMPLOYEES IN DEPTNO 20

SELECT * FROM EMP WHERE SAL> ANY(SELECT SAL FROM EMP WHERE DEPTNO=20);

SELECT * FROM EMP WHERE SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO=20);

DISPLAY ALL THE DETAILS WHERE SAL> HIGHEST SAL OF EMPLOYEES IN DEPTNO 20

DECLARE

NAME EMP.ENAME%TYPE;

SALARY EMP.SAL%TYPE;

DESIG EMP.JOB%TYPE:='&JOB';

BEGIN

SELECT ENAME,SAL,JOB INTO NAME,SALARY ,DESIG FROM EMP WHERE

JOB=DESIG;

DBMS_OUTPUT.PUT_LINE(NAME||' '||SALARY||' '||DESIG);

END;

1.display name,hiredate of all employees using cursors

2.DISPLAY DETAILS OF ALL EMPLOYEES WHO ARE CLERKS

3.DISPLAY DETAILS OF ALL EMPLOYEES WHEN DEPTNO IS 10.

4. EXAMPLES FOR %FOUND A B C

5.*ASSIGNMENT CLERK 200/-INCREMENT MANAGER INSERT TO TEMP ANALYST DELETE SALESMAN AND ANNSAL>15000 300/- DECREMENT

6. EXAMPLE FOR %ISOPEN

7.EXAMPLE FOR C1%ROWCOUNT (7 A, 7 B)* display first 5 records

8. display details of first 5 highly paid employees

9.EVEN ROWS

10.NTH ROW

11.CURSOR USING FOR LOOP

12.IF ANNSAL>10000 AND JOB=CLERK INCOME TAX IS 10%,IF ANNSAL>15000 AND JOB=SALESMAN TAX IS 20%,IF ANNSAL>10000 AND JOB=MANAGAER INCOME TAX IS 30%,ELSE A MESSAGE <10000.

13 CURSOR PARAMETERS TO PASS EMPLOYEE NUMBER AND GET THE DETAILS

14 PASS JOB & GET THE DETAILS

15 PASS DEPTNO AND JOB AND GET THE DETIALS

16 CURSOR LOCKS

17 EXAMPLE FOR IMPLICIT CURSORS

18 INSERT WITH IMPLICIT CURSORS

19 UPDATE AND SELECT WITH IMPLICIT CURSORS

20 DELETE WITH IMPLICIT CURSORS

21.DISPLAY NAMES OF EMPLOYEES WITH SAL IN DESCENDING ORDER

WITHOUT USING ORDER BY

22.DISPLAY JOB CATEGORIES ,COUNT(JOB) AND SUM(SAL) FROM EMP

Answers:

1. display name,hiredate of all employees using cursors

DECLARE

cursor c1 is select ename,hiredate from emp;

name varchar(20);

hdate date;

begin

open c1;

loop

fetch c1 into name,hdate;

exit when c1%NOTFOUND;

dbms_output.put_line(name||' '||hdate);

end loop;

close c1;

end;

(VERY IMPORTANT POINT TO BE NOTED IS THAT EXIT WHEN CONDITION IS

SATISFIED WHEN THE FETCH RETURNS FALSE AND WE WILL COME OUT OF LOOP

WHEN EXIT CONDITION IS TRUE.)

4. EXAMPLES FOR %FOUND

DECLARE

cursor c1 is select ename,sal,DEPTNO from emp

WHERE DEPTNO=10;

name varchar(20);

sl number;

dno number;

begin

open c1; loop

fetch c1 into name,sl,dno ;

IF c1%notFOUND THEN

DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT FOUND');

exit;

elsif c1%found then

DBMS_OUTPUT.PUT_LINE('FOUND'||' '||C1%ROWCOUNT);

--INSERT INTO TEMP VALUES(NAME,SL,DNO);

END IF;

end loop;

close c1;

end;

4 B

DECLARE

cursor c1 is select * from emp

WHERE DEPTNO=&deptno;

a emp%rowtype;

begin

open c1;

LOOP

fetch c1 into a;

IF c1%FOUND THEN

DBMS_OUTPUT.PUT_LINE(A.ENAME||' '||A.SAL||' '||A.DEPTNO);

else

EXIT;

END IF;

END LOOP;

close c1;

end;

4 C

DECLARE

cursor c1 is select * from emp

WHERE DEPTNO=&deptno;

a emp%rowtype;

begin

open c1;

loop

fetch c1 into a;

IF not c1%FOUND THEN

exit;

else

DBMS_OUTPUT.PUT_LINE(a.ename||' '||a.sal);

END IF;

end loop;

close c1;

end;

5.CLERK 200/-INCREMENT MANAGER INSERT NAME,SAL TO TEMP ANALYST DELETE

SALESMAN AND ANNSAL>15000 300/- INCREMENT

DECLARE

CURSOR C1 IS SELECT * FROM EMP;

A C1%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

IF A.JOB='CLERK' THEN

UPDATE EMP SET SAL=A.SAL+200 WHERE EMPNO=A.EMPNO;

COMMIT;

ELSIF A.JOB='MANAGER' THEN

DBMS_OUTPUT.PUT_LINE('ROW INSERTED');

INSERT INTO TEMP VALUES(A.ENAME,A.JOB,A.SAL);

COMMIT;

ELSIF A.JOB='ANALYST' THEN

DBMS_OUTPUT.PUT_LINE('ROW DELETED');

DELETE FROM EMP WHERE EMPNO=A.EMPNO;

COMMIT;

ELSIF A.JOB='SALESMAN' AND A.SAL*12>5000 THEN

DBMS_OUTPUT.PUT_LINE('ROW SALESMAN UPDATED');

UPDATE EMP SET SAL=SAL+300 WHERE EMPNO=A.EMPNO;

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE('NO CHANGES');

END IF;

END LOOP;

CLOSE C1;

END;

CREATE TABLE TEMP( A VARCHAR2(10),B VARCHAR2(10),C NUMBER(7,2));

6. EXAMPLE FOR %ISOPEN

DECLARE

CURSOR C1 IS SELECT * FROM EMP;

A C1%ROWTYPE;

BEGIN

IF C1%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('CURSOR ALREADY OPEN');

ELSE

DBMS_OUTPUT.PUT_LINE('CURSOR IS YET TO BE OPENED');

OPEN C1;

END IF;

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(A.SAL||' '||A.ENAME||' '||A.JOB);

END LOOP;

CLOSE C1;

END;

6. B

IF NOT C1%ISOPEN

OPEN C1;

ELSE

DBMS_OUTPUT.PUT_LINE('CURSOR IS NOT OPEN');

END IF;

7.EXAMPLE FOR C1%ROWCOUNTdisplay first 5 records

DECLARE

cursor c1 is select * from emp;

a c1%rowtype;

begin

open c1;

loop

fetch c1 into a;

exit when c1%rowcount>6;

dbms_output.put_line(a.ename||' '||a.sal||' '||a.job||'

'||C1%ROWCOUNT);

end loop;

close c1;

end;

7 B other method for above pgm

DECLARE

cursor c1 is select * from emp;

a c1%rowtype;

begin

open c1;

loop

fetch c1 into a;

exit when c1%notfound;

if c1%rowcount<=6 then

dbms_output.put_line(a.ename||' '||a.sal||' '||a.job);

end if;

end loop;

close c1;

end;

8 display details of first 5 highly paid employees

DECLARE

cursor c1 is select * from emp order by sal desc;

a c1%rowtype;

begin

open c1;

loop

fetch c1 into a;

exit when c1%rowcount>6;

dbms_output.put_line(a.ename||' '||a.sal||' '||a.job||'

'||C1%ROWCOUNT);

end loop;

close c1;

end;

9. display only even rows

DECLARE

cursor c1 is select * from emp ;

a c1%rowtype;

begin

open c1;

loop

fetch c1 into a;

exit when c1%notfound;

if mod(c1%rowcount,2)=0 then

dbms_output.put_line(a.ename||' '||a.sal||' '||a.job);

end if;

end loop;

close c1;

end;

(for odd rows change 0 to 1);

10 display nth row

DECLARE

cursor c1 is select * from emp ;

a c1%rowtype;

n number;

begin

n:=&n;

open c1;

loop

fetch c1 into a;

exit when c1%notfound;

if c1%rowcount=n then

dbms_output.put_line(a.ename||' '||a.sal||' '||a.job);

end if;

end loop;

close c1;

end;

11 cursor using for loop

declare

cursor c2 is select * from emp;

begin

for a in c2 loop

dbms_output.put_line(a.ename||' '||a.sal||' '||a.job);

end loop;

end;

12. IF ANNSAL>10000 AND JOB=CLERK INCOME TAX IS 10%,IF ANNSAL>15000 AND JOB=SALESMAN TAX IS 20%,IF ANNSAL>10000 AND JOB=MANAGAER INCOME TAX IS 30%,ELSE A MESSAGE <10000

alter table emp add(it number(3));

declare

cursor c1 is select * from EMP;

a c1%rowtype;

begin

open c1;

loop

fetch c1 into a;

exit when c1%notfound;

if a.sal*12>10000 and a.job='CLERK' THEN

UPDATE EMP SET IT=A.SAL*12*0.1 WHERE EMPNO=A.EMPNO;

ELSIF A.SAL*12>15000 AND A.JOB ='SALESMAN' THEN

UPDATE EMP SET IT=A.SAL*12*0.2 WHERE EMPNO=A.EMPNO;

ELSIF A.SAL*12>10000 AND A.JOB='MANAGER' THEN

UPDATE EMP SET IT=A.SAL*12*0.3 WHERE EMPNO=A.EMPNO;

ELSIF A.SAL*12<10000 THEN

DBMS_OUTPUT.PUT_LINE(A.ENAME||' '||'SAL< 10000');

END IF;

END LOOP;

COMMIT;

CLOSE C1;

END;

13 CURSOR PARAMETERS TO PASS EMPLOYEE NUMBER AND GET THE DETAILS

DECLARE

CURSOR C1( z NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=z;

A C1%ROWTYPE;

BEGIN

OPEN C1(&DEPTNO);

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(A.ENAME||' '||A.DEPTNO||' '||A.SAL);

END LOOP;

CLOSE C1;

END;

14 TO PASS JOB AND GET THE DETAILS

DECLARE

CURSOR C1( A VARCHAR) IS SELECT * FROM EMP WHERE JOB=A;

A C1%ROWTYPE;

BEGIN

OPEN C1('&AJOB');

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(A.JOB||' '||A.ENAME||' '||A.EMPNO||' ||A.SAL);

END LOOP;

CLOSE C1;

END;

NOTE :it will not take job in uppercase so upper(job)=upper(a)

15. TO PASS DESIGNATION AND DEPTNO AND GET THE DETAILS

DECLARE

CURSOR C1(A NUMBER,B VARCHAR) IS SELECT * FROM EMP

WHERE DEPTNO=A AND JOB=B;

A C1%ROWTYPE;

BEGIN

OPEN C1(&ADETNO,'&BJOB');

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(A.ENAME||' '||A.JOB||' '||A.SAL||' ||A.DEPTNO);

END LOOP;

CLOSE C1;

END;

16 CURSOR LOCKING

DECLARE

CURSOR C1 IS SELECT * FROM EMP FOR UPDATE OF SAL;

A C1%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(A.JOB||' '||A.ENAME||' '||A.SAL||' '||A.DEPTNO);

IF A.JOB='CLERK' THEN

UPDATE EMP SET JOB='FDC' WHERE empno=a.empno;

end if;

end loop;

close c1;

end;

17 EXAMPLE FOR IMPLICIT CURSORS

DECLARE

CURSOR C1 IS SELECT * FROM EMP;

A C1%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT||' '||SQL%ROWCOUNT||' '||A.ENAME);

END LOOP;

CLOSE C1;

END;

(YOU WILL NOT GET THE OUTPUT BECAUSE SQL%ROWCOUNT WILL RETURN A VALUE

ONLY WHEN THE PREVIOUS DML OPERATION IS SUCCESSFULLY COMPLETLE)

18. IMPLICIT CURSOR WITH INSERT

CREATE TABLE TEMP(A VARCHAR2(10),B NUMBER(7,2), C VARCHAR2(10));

DECLARE

CURSOR C2 IS SELECT * FROM EMP;

A C2%ROWTYPE;

BEGIN

OPEN C2;

LOOP

FETCH C2 INTO A;

EXIT WHEN C2%NOTFOUND;

INSERT INTO TEMP VALUES(A.ENAME,A.SAL,A.JOB);

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' '||C2%ROWCOUNT||' '||

A.SAL||' '||A.ENAME);

ELSE

EXIT;

END IF ;

END LOOP;

CLOSE C2;

END;

19 DELETE WITH IMPLICIT CURSORS

DECLARE

BEGIN

DELETE FROM EMP WHERE DEPTNO=10;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

END;

20. UPDATE AND SELECT WITH IMPLICIT CURSORS

DECLARE

A EMP%ROWTYPE;

BEGIN

SELECT * INTO A FROM EMP WHERE EMPNO=7788;

DBMS_OUTPUT.PUT_lINE('AFTER SELECT SQLCOUNT'|| ' '||SQL%ROWCOUNT);

UPDATE EMP SET DEPTNO=99 WHERE DEPTNO=10;

DBMS_OUTPUT.PUT_lINE('AFTER UPDATE SQLCOUNT'|| ' '||SQL%ROWCOUNT);

END;

OUTPUT IS

AFTER SELECT SQLCOUNT 1

AFTER UPDATE SQLCOUNT 3

21.DISPLAY NAMES OF EMPLOYEES WITH SAL IN DESCENDING ORDER WITHOUT USING ORDER BY

DECLARE

CURSOR C1 IS SELECT * FROM EMP;

A C1%ROWTYPE;

N NUMBER;

BEGIN

OPEN C1;

SELECT MAX(SAL) INTO N FROM EMP;

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(N);

SELECT MAX(SAL) INTO N FROM EMP WHERE SAL<N;

END LOOP;

CLOSE C1;

END;

22.DISPLAY JOB CATEGORIES ,COUNT(JOB) AND SUM(SAL) FROM EMP

DECLARE

CURSOR C1 IS SELECT JOB,COUNT(JOB) XY ,SUM(SAL) AB FROM EMP

GROUP BY JOB;

A C1%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO A;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('______');

DBMS_OUTPUT.PUT_LINE(' JOB '||' NUMBER '||' SUM OF SAL

');

DBMS_OUTPUT.PUT_LINE(A.JOB||' '|| A.XY ||' '||

A.AB);

END LOOP;

CLOSE C1;

END;

DECLARE

CURSOR T1 IS SELECT * FROM TEST1;

TEST_REC T1%ROWTYPE;

BEGIN

FOR TEST IN T1

LOOP

UPDATE TEST1 SET AREA=400 WHERE RADIUS=50;

IF SQL%ROWCOUNT >0 THEN

DBMS_OUTPUT.PUT_LINE('RECORD IS ALREADY EXISTS');

ELSE

DBMS_OUTPUT.PUT_LINE('THERE IS NO RECORD');

END IF;

END LOOP;

END;

DECLARE

RDA AREAS.RADIUS%TYPE;

BEGIN

SELECT RADIUS INTO RDA FROM AREAS WHERE RADIUS =345;

EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('RECORD NOT FOUND');

END;

Database objects

TABLES,VIEWS,SYNONYM,INDEX,CLUSTER,SEQUENCE,PROCEDURE,FUNCTION

PACKAGE,TRIGGER

VIEWS

Virtual table changes reflected in table & viceversa no data of its own, no redundancy and security. A logical table based on one or more tables or views.

1.CREATE VIEW EV AS SELECT * FROM EMP;

2.CREATE VIEW EV1 AS SELECT ENAME,EMPNO,DEPTNO FROM EMP;

3.CREATE VIEW EV2(EMPNO,SALARY) AS SELECT EMPNO,SAL FROM EMP;

4.CREATE VIEW EM1 AS SELECT ENAME,SAL,DEPTNO,EMPNO,JOB

FROM EMP WHERE DEPTNO=20;

READ ONLY VIEW

VIEWS WITH GROUP BY,ORDER BY ,AGG , PRIMARY KEY AND CHECK CONSTRAINTS. THE VIEW BECOMES A READ ONLY VIEW.

  1. CREATE VIEW EV AS SELECT DEPTNO,COUNT(*) COUNT FROM EMP GROUP BY DEPTNO;
  1. CREATE VIEW EM1 AS SELECT ENAME,SAL,DEPTNO,EMPNO,JOB

FROM EMP WHERE DEPTNO=20 WITH CHECK OPTION CONSTRAINT CK1;

FORCED VIEWS

CREATE FORCE VIEW ABC AS SELECT EMPNO,ENAME,SAL FROM EMPOP;

SEE THE VIEWS

SELECT * FROM <view name>;

DROP VIEW ABC;

SELECT * FROM USER_VIEW;

CREATE VIEW MAG AS SELECT M.MNO,M.NAME,MA.GP,MA.NP FROM MAGI M,MAG MA ;

SYNONYMS

A synonym is an alternative name for a table,view, sequence, procedure, stored function.