VIEW= THE SUBSET OF THE TABLE. USE FOR COMPLICATED COMPLEX QUERY.
SQL> SELECT ENAME,JOB,SAL FROM EMP
2 WHERE DEPTNO IN (10,20)
3 OR JOB = 'SALESMAN' OR
4 SAL <= 2500;
ENAME JOB SAL
---------- --------- ----------
AHMAD CLERK 40000
MARIA OFFICER 7000
ALI CLERK 5000
SAHIR CLERK 2000
ARIF CLERK 2500
ASIF DBA 77788
KIRAN MANAGER 2000
SANA MANAGER 2200
KASHIF OFFICER 1400
IRFAN CLERK 1200
EMRAN CLERK 1000
ENAME JOB SAL
---------- --------- ----------
SIDRA ASSISTANT 900
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
ENAME JOB SAL
---------- --------- ----------
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
25 rows selected.
SQL> CREATE VIEW ABCVIEW
2 AS
3 SELECT ENAME,JOB,SAL FROM EMP
4 WHERE DEPTNO IN (10,20)
5 OR JOB = 'SALESMAN' OR
6 SAL <= 2500;
View created.
SQL> SELECT * FROM ABCVIEW;
ENAME JOB SAL
---------- --------- ----------
AHMAD CLERK 40000
MARIA OFFICER 7000
ALI CLERK 5000
SAHIR CLERK 2000
ARIF CLERK 2500
ASIF DBA 77788
KIRAN MANAGER 2000
SANA MANAGER 2200
KASHIF OFFICER 1400
IRFAN CLERK 1200
EMRAN CLERK 1000
ENAME JOB SAL
---------- --------- ----------
SIDRA ASSISTANT 900
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
ENAME JOB SAL
---------- --------- ----------
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
25 rows selected.
SQL> DROP VIEW ABCVIEW;
SEQUENCE= TO GENERATE AUTO NO
SQL> CREATE SEQUENCE ABCSQ
2 START WITH 10
3 INCREMENT BY 5;
Sequence created.
SQL> CREATE TABLE ABCDEF
2 (RNO NUMBER(10),
3 NAME VARCHAR2(20));
Table created.
SQL> INSERT INTO ABCDEF
2 (RNO,NAME)
3 VALUES
4 (ABCSQ.NEXTVAL,'USAMA');
1 row created.
SQL> ED
Wrote file afiedt.buf
1 INSERT INTO ABCDEF
2 (RNO,NAME)
3 VALUES
4* (ABCSQ.NEXTVAL,'SAMI')
SQL> /
1 row created.
SQL> ED
Wrote file afiedt.buf
1 INSERT INTO ABCDEF
2 (RNO,NAME)
3 VALUES
4* (ABCSQ.NEXTVAL,'EJ')
SQL> /
1 row created.
SQL> ED
Wrote file afiedt.buf
1 INSERT INTO ABCDEF
2 (RNO,NAME)
3 VALUES
4* (ABCSQ.NEXTVAL,'HA')
SQL> /
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM ABCDEF;
RNO NAME
---------- --------------------
10 USAMA
15 SAMI
20 EJ
25 HA
SQL> SELECT * FROM USER_SEQUENCES;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
EMPSEQ 1 1.0000E+27 10 N N 20
8336
DBOBJECTID_SEQUENCE 1 1.0000E+24 50 N N 50
1
ABCSQ 1 1.0000E+27 5 N N 20
110
SQL> SET LINES 300 PAGES 300
SQL> /
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
EMPSEQ 1 1.0000E+27 10 N N 20 8336
DBOBJECTID_SEQUENCE 1 1.0000E+24 50 N N 50 1
ABCSQ 1 1.0000E+27 5 N N 20 110
SQL> DROP SEQUENCE ABCSEQ;
DROP SEQUENCE ABCSEQ
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL> ED
Wrote file afiedt.buf
1* DROP SEQUENCE ABCSQ
SQL> /
Sequence dropped.
INDEX= TO IMPROVE THE PERFORMANCE OF THE TABLE.WHEN UR DATA > 200 IN A TABLE THEN U APPLY INDEX.
PK UK IN COLUMNS AUTOMATIC CREATE INDEX.
SQL> CREATE INDEX ABCIDX
2 ON EMP(JOB);
ON EMP(JOB)
*
ERROR at line 2:
ORA-01408: such column list already indexed
SQL> ED
Wrote file afiedt.buf
1 CREATE INDEX ABCIDX
2* ON EMP(SAL)
SQL> /
ON EMP(SAL)
*
ERROR at line 2:
ORA-01408: such column list already indexed
SQL> ED
Wrote file afiedt.buf
1 CREATE INDEX ABCIDX
2* ON ABCDEF(NAME)
SQL> /
Index created.
SQL> SELECT * FROM ABCDEF;
RNO NAME
---------- --------------------
10 USAMA
15 SAMI
20 EJ
25 HA
SQL> DROP INDEX ABCIDX;
Index dropped.
No comments:
Post a Comment