PRIMARY CONSTRAINTS : PRIMARY,UNIQUE,CHECK,REFERENCES)
SECONDARY CONSTRAINTS : NOT NULL,DEFAULT)
CONSTRAINTS BASED ON 2 LEVELS COLUMN LEVEL and TABLE 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 UNIEMP
UNIQUE,ENAME VARCHAR2(10));
PRIMARY KEY
CREATE TABLE EMP9( ENO NUMBER(3) CONSTRAINT PKEMP9 PRIMARY KEY,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'
2. Display the details of all employees who are joined before '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
4. Display all the employees working in chicago
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’);
2. DISPLAY THE EMPLOYEES BELONGING TO MILLER'S DEPARTMENT
Select * from emp where dname = (select dname from emp
Where ename = ‘milers’);
3. DISPLAY ALL THE EMPLOYEES REPORTING TO KING
Select * from emp where rname = (select rname from emp
Where ename = ‘king’);
4. DISPLAY ALL THE EMPLOYEES WHO ARE GETTING MAX SALARY
Select * from emp where basal = (select max(basal) from emp);
5. 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’);
6. 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
7. 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%ROWCOUNT display 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
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;
2. 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
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.
1).Public synonym
2).Private synonym
CREATE SYNONYM Z FOR EMP;
CREATE PUBLIC SYNONYM VV FOR EMP;
DROP SYNONYM Z;
SELECT * FROM USER_SYNONYMS WHERE TABLE_NAME='EMP';
INDEXES
An index is a database object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and
provides direct, fast access to rows.
PRIMARY KEY AUTOMATICALLY INDEXED HAS ITS EFFECT ON LARGER TABLES
CREATE INDEX AB ON EMP(EMPNO);
SELECT * FROM USER_INDEXES WHERE TABLE_NAME='EMP';
IF YOU WANT TO INDEX A PRIMARY KEY COLUMN FIRST JUST DISABLE IT
DROP INDEX AB;
SEQUENCES
A sequence is a database object from which multiple users may generate unique integers.You can use sequences to automatically generate primary key values.
1.CREATE SEQUENCE S1 INCREMENT BY 10 START WITH 10 MAXVALUE 50
2.CREATE SEQUENCE S5 INCREMENT BY 10 START WITH 10 MAXVALUE 50
MINVALUE 5 CYCLE CACHE 2;
3.CREATE SEQUENCE S5 INCREMENT BY –2 START WITH 10 MAXVALUE 20
MINVALUE –5 CYCLE CACHE 2;
4.CREATE SEQUENCE S6 INCREMENT BY –2 START WITH 6 MAXVALUE 20
MINVALUE –4 CYCLE CACHE 2;
SELECT * FROM USER_SEQUENCES WHERE TABLE_NAME='EMP';
DROP SEQUENCE S6;
Awesome Thanks for the valuable material
ReplyDelete