Day3 Sql
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Day3 Sql as PDF for free.
More details
- Words: 1,516
- Pages: 12
DDL CONTD.. ------ALTER ADD : COLUMN/'S,CONSTRAINT MODIFY : COLUMN DATATYPE(SIZE),CONSTRAINT DROP : COLUMN/'S,CONSTRAINT RENAME : COLUMN ALTER + ADD: ---------------------ALTER TABLE ADD(NEW_COL DATATYPE(SIZE),.....); SQL> ALTER TABLE EMPLOY ADD(MGR NUMBER(4)); SQL> DESC EMPLOY ALTER + MODIFY -------------------------ALTER TABLE MODIFY(EXST_COLNAME NEW_DATATYPE(SIZE)); ALTER TABLE EMPLOY MODIFY(MGR VARCHAR2(20)); DESC EMPLOY ALTER + DROP ---------------------ALTER TABLE DROP COLUMN ; ALTER TABLE DROP(COL3,COL7,COL9,....); SQL> ALTER TABLE EMPLOY DROP COLUMN MGR; SQL> ALTER TABLE EMPLOY DROP(PROGRAM,REMARKS); ALTER + RENAME: ---------------------------ALTER TABLE RENAME COLUMN <EXST_COLNAME> TO ; SQL> ALTER TABLE EMPLOY RENAME COLUMN SALARY TO EMP_SAL; DESC EMPLOY TRUNCATE: -> REMOVES ALL RECS FROM DB PERMAN--------------------ENTLY. TABLE STRUCT REMAINS. TRUNCATE TABLE ; SQL> TRUNCATE TABLE EMPLOY; DROP -> REMOVES ALL RECS AND TABLE FROM --------DB PERMANENTLY. DROP TABLE [PURGE]; NOTE: ORACLE10g
-> TABLE DROPPED CAN BE GOT BACK. IT WILL BE STORED IN RECYCLE BIN. FLASHBACK IS USED TO RESTORE.
PURGE
-> WHEN USED ALONG WITH DROP THEN IT WILL NOT BE STORED IN RECYCLE. FLASHBACK CANNOT BE USED.
SQL> TRUNCATE TABLE EMPLOY; SQL> DESC EMPLOY SQL> SELECT * FROM EMPLOY; SQL> DROP TABLE EMPLOY [PURGE]; SQL> DESC EMPLOY SQL> SELECT * FROM EMPLOY; SQL> SELECT * FROM TAB;
BIN$.......
SQL> DESC RECYCLEBIN SQL> SELECT OBJECT_NAME,ORIGINAL_NAME FROM RECYCLEBIN; SQL> FLASHBACK TABLE EMPLOY TO BEFORE DROP; Flashback Complete. SQL> DESC EMPLOY SQL> SELECT * FROM EMPLOY; SQL> PURGE RECYCLEBIN; ---> EMPTY RECYCLEBIN PERMANENTLY. RENAME: -------------RENAME <EXST_TBLNAME> TO; DESC ; SELECT * FROM ; SQL> RENAME EMPLOY TO EMP_DETAILS; SQL> DESC EMP_DETAILS SQL> SELECT * FROM EMP_DETAILS; SQL> CONN SYS/ORACLE AS SYSDBA CONSTRAINTS ---------------------ARE RESTRICTIONS FOR USER, APPLIED ON COLUMNS. TYPES: ------------NOT NULL UNIQUE PRIMARY KEY CHECK
: NO NULL : NO DUPL, MULTIPLE NULL ALLO -WED. : NO NULL,NO DUPL ONLY ONE PRIMARY KEY PER TABLE. : CHECKS FOR PREDEFINED VAL.
FOREIGN KEY REFERENTIAL INTEGRITY CONSTRAINT SELF REFERENTIAL INTEGRITY CONSTRAINT 2 LEVELS: --------------COLUMN TABLE NAMED: ------------USER : CONSTRAINT CON_TYPE SYSTEM(DEFAULT): CON_TYPE : SYS_C004859 ENABLE/DISABLE DROP
: ALTER TABLE DISABLE/ENABLE CONSTRAINT ;
: ALTER TABLE DROP CONSTRAINT ;
2 STAGES: -------------------CREATE ALTER CREATE: ------------CREATE TABLE EMPLY_DETAILS (EMPNO NUMBER(4) CONSTRAINT PK_EMPNO PRIMARY KEY, ENAME CHAR(10) CONSTRAINT NN_ENAME NOT NULL, SAL NUMBER(10) CHECK(SAL>10000), EPFNO NUMBER(4) NOT NULL UNIQUE); CONSTRAINT NAMES CANNOT BE DUPLICATED ACROSS TBLS WITHIN THE USER. ERROR at line 3: ORA-02264: name already used by an existing constraint USER_CONSTRAINTS ---------------------------------DESC USER_CONSTRAINTS SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLY_DETAILS'; CONSTRAINT_NAME C STATUS -----------------------------------------NN_NAME C ENABLED SYS_C005595 C ENABLED SYS_C005596 C ENABLED PK_NO P ENABLED SYS_C005598 U ENABLED INSERT INTO EMPLY_DETAILS VALUES (1001,'ANIL',99999,0001);
INSERT INTO EMPLY_DETAILS VALUES (1001,'ANCHAN',88888,0002); ERROR at line 1: ORA-00001: unique constraint (ANIL.PK_NO) violated INSERT INTO EMPLY_DETAILS VALUES (NULL,'ANCHAN',88888,0002); ERROR at line 2: ORA-01400: cannot insert NULL into ("ANIL"."EMPLY_DETAILS"."EMPNO") ------------------------------ PRIMARY KEY --------------------------------------INSERT INTO EMPLY_DETAILS VALUES (1002,'',88888,0002); ERROR at line 2: ORA-01400: cannot insert NULL into ("ANIL"."EMPLY_DETAILS"."ENAME") ------------------------------ NOT NULL --------------------------------------------INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',888,0002); ERROR at line 1: ORA-02290: check constraint (ANIL.SYS_C005596) violated ------------------------------ CHECK -------------------------------------------------INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',88888,0001); ERROR at line 1: ORA-00001: unique constraint (ANIL.SYS_C005598) violated INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',88888,NULL); ERROR at line 2: ORA-01400: cannot insert NULL into ("ANIL"."EMPLY_DETAILS"."EPFNO" ------------------------------ 2 CONSTRAINTS --> NOT NULL, UNIQUE --DISABLE/ENABLE: --------------------------ALTER TABLE EMPLY_DETAILS DISABLE CONSTRAINT SYS_C005598; INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',88888,1); SQL> ALTER TABLE EMPLY_DETAILS ENABLE CONSTRAINT SYS_C005598; * ERROR at line 1: ORA-02299: cannot validate (ANIL.SYS_C005598) - duplicate keys found SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLY_DETAILS'; SQL> DELETE EMPLY_DETAILS WHERE EMPNO = 1002; SQL> COMMIT; SQL> ALTER TABLE EMPLY_DETAILS ENABLE CONSTRAINT SYS_C005598;
TO APPLY CONSTRAINTS WITH ALTER: ----------------------------------------------------------ALTER TABLE EMPLY_DETAILS DROP CONSTRAINT NN_NAME; SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLY_DETAILS'; ALTER+ADD : TO DEFINE A NOT NULL ON ENAME: --------------------------------------------CANNOT BE USED TO ADD A NOT NULL CONSTRAINT. NOTE: USE ALTER+MODIFY ALTER TABLE ADD CONSTRAINT CONSTR_TYPE(COLNAME); ALTER+MODIFY ----------------------ALTER TABLE MODIFY(COLNAME CONSTRAINT CON_TYPE); EXERCISE: ALTER TABLE EMPLY_DETAILS ADD CONSTRAINT NN_NAME NOT NULL(ENAME);
-> ERROR
ALTER TABLE EMPLY_DETAILS MODIFY(ENAME CONSTRAINT NN_NAME NOT NULL); ALTER TABLE EMPLY_DETAILS MODIFY(ENAME CONSTRAINT UNQ_NAME UNIQUE); NOTE: ALTER+MODIFY
-> APPENDS THE NEW EXISTING CONSTRAINT.
TABLE LEVEL: ---------------------COMPOSITE PRIMARY KEY: CHECKS ON ROW BASIS ----------------------------------------MULTIPLE COLUMNS ACT AS A SINGLE PRIMARY KEY. CREATE TABLE T (A NUMBER(4), B NUMBER(4), CONSTRAINT C_PK PRIMARY KEY(A,B) ); A -1 1 2 2 2 3 3
B -1 2 1 2 3 2 3
CONSTRAINT TO THE
FOREIGN KEY REFERENTIAL INTEGRITY CONSTRAINT SELF REFERENTIAL INTEGRITY CONSTRAINT PRIMARY KEY FOREIGN KEY ---------------------------------------NO NULL,NO DUPL NULL,DUPL ALLOWED. 1 PK PER TABLE MULTIPLE FK'S PER TABLE NA REFERS TO PK / UNIQUE OF TBL. COMPOSITE PK NA ALSO KNOWN AS A COMMON COLUMN BETN 2 TABLES. COMMONCOLUMN
SAME TBL OR ANOTHER
: THEIR NAMES CAN BE DIFFERENT / SAME BUT THEIR DATATYPES MUST BE SAME.
2 LAWS: ------------1> NO INSERTION OF A DETAIL RECORD ALLOWED IF IS NOT PRESENT. 2> NO DELETION OF MASTER ALLOWED WHEN DEPENDENCIES EXIST IN THE DETAIL.
CORRESPONDING MASTER RECORD
ON DELETE CASCADE: ---------------------------------IT ALLOWS TO DELETE THE MASTER RECORD,SUBSEQUENTLY THE DEPENDENCIES WILL ALSO BE DELETED. SELF REFERENTIAL INTEGRITY CONSTRAINT: -------------------------------------------------------------------PRIMARY KEY AND FOREIGN KEY RESIDE WITHIN THE SAME TABLE. i.e THE FOREIGN KEY REFERS TO PRIMARY KEY WITHIN THE SAME TBL. APPLY THE CONSTRAINTS : CREATE / ALTER: -------------------------CREATE TABLE DEPARTMENT (DNO NUMBER(2) CONSTRAINT PK_DNO PRIMARY KEY, DNAME VARCHAR2(20), LOC VARCHAR2(20) ); CREATE TABLE EMPLOYEES (EMPNO NUMBER(4) CONSTRAINT PK_NO PRIMARY KEY, ENAME CHAR(10), SAL NUMBER(10), MGR NUMBER(4), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPARTMENT(DNO) -- COLUMN LEVEL ); OR CREATE TABLE EMPLOYEES (EMPNO NUMBER(4) CONSTRAINT PK_NO PRIMARY KEY, ENAME CHAR(10), SAL NUMBER(10), MGR NUMBER(4),
DEPTNO NUMBER(2), CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO)
REFERENCES DEPARTMENT(DNO)
); SQL> INSERT INTO EMPLOYEES VALUES (1001,'ANIL',99999,1001,10); ERROR at line 1: ORA-02291: integrity constraint (ANIL.FK_DEPTNO) violated - parent key not found BEGIN INSERT INTO DEPARTMENT VALUES (10,'SALES','BLORE'); INSERT INTO EMPLOYEES VALUES (1001,'ANIL',99999,1001,10); INSERT INTO EMPLOYEES VALUES (1002,'ANCHAN',99999,1001,10); INSERT INTO EMPLOYEES VALUES (1003,'COLLINS',99999,1001,10); INSERT INTO EMPLOYEES VALUES (1004,'RAJ',99999,1002,10); END; / DELETE DEPARTMENT WHERE DNO = 10; ERROR at line 1: ORA-02292: integrity constraint (ANIL.FK_DEPTNO) violated - child record found ALTER TABLE EMPLOYEES DROP CONSTRAINT FK_DEPTNO; TO APPLY A REFERENTIAL INTEGRITY CONSTRAINT FOR AN EXISTING TBL: --------------------------------------------------------------------------------------------ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DNO); SELF REFERENTIAL INTEGRITY CONSTRAINT: -------------------------------------------------------------------ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_KEY_MGR FOREIGN KEY(MGR) REFERENCES EMPLOYEES(EMPNO); ON DELETE CASCADE: --------------------------------ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) DEPARTMENT(DNO) ON DELETE CASCADE;
REFERENCES
ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_KEY_MGR FOREIGN KEY(MGR) REFERENCES EMPLOYEES(EMPNO) ON DELETE CASCADE; QUERYING THE DATABASE WRT 2 OR MORE TBLS: --------------------------------------------------------------------------1> SET OPERATORS 2> JOINS
3> SUB QUERIES SET OPERATORS -------------------------UNION ALL : INCL DUPL + INCL NULL UNION : EXCL DUPL + INCL NULL INTERSECT : EXCL DUPL - EXCL NULL MINUS : T1-T2 : DATA PRESENT IN T1 NOT IN T2 VICEVERSA. SELECT ..COL.FROM OPERATOR SELECT..COL..FROM; LIMITATIONS -------------------NO OF COLS SELECTED FROM T1 MUST BE EQUAL TO NO OF COLS FROM T2. THESE COL DATATYPES MUST ALSO BE THE SAME. THE OUTPUT WILL BE DISPLAYED FROM BOTH THE TBLS ONE BELOW THE OTHER. SQL> SELECT DEPTNO FROM UNION ALL SELECT DEPTNO SQL> SELECT DEPTNO FROM UNION SELECT DEPTNO SQL> SELECT DEPTNO FROM INTERSECT SELECT DEPTNO SQL> SELECT DEPTNO FROM MINUS SELECT DEPTNO SQL> SELECT DEPTNO FROM MINUS SELECT DEPTNO
EMP FROM DEPT; EMP FROM DEPT; EMP FROM DEPT; EMP FROM DEPT; DEPT FROM EMP;
JOINS -------------------------------------------SELECT * FROM EMP,DEPT; EMP -> 14 DEPT -> 4 CARTESIAN PRODUCT =========> 14*4 = 56 IS USED: 1> TO ELIMINATE CARTESIAN PRODUCT 2> TO RETRIEVE DATA FROM MORE THAN 1 TBL AT A TIME. JOIN CONCEPT: ------------------------TABLE1.COMMONCOLUMN = TABLE2.COMMONCOLUMN TYPES: ----------EQUI NON EQUI SELF JOIN OUTER FULL OUTER JOIN EQUI JOIN:
----------------SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT EMPNO,ENAME,SAL,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT EMPNO,ENAME,SAL,DEPTNO,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; ERROR at line 1: ORA-00918: column ambiguously defined SOL1: --------SELECT EMPNO,ENAME,SAL,EMP.DEPTNO,DNAME FROM EMP.DEPTNO = DEPT.DEPTNO; OR SELECT EMPNO,ENAME,SAL,DEPT.DEPTNO,DNAME FROM EMP.DEPTNO = DEPT.DEPTNO; SOL2: TABLE TEMPORARY ALIAS --------SELECT EMPNO,ENAME,SAL,D.DEPTNO,DNAME FROM WHERE E.DEPTNO = D.DEPTNO; OR SELECT EMPNO,ENAME,SAL,E.DEPTNO,DNAME FROM WHERE E.DEPTNO = D.DEPTNO;
EMP,DEPT WHERE EMP,DEPT WHERE
EMP
E , DEPT
D
EMP
E , DEPT
D
WRITE A QUERY TO DISPLAY ALL THE EMP DETAILS AND THEIR DEPT NAMES JOINING EMP AND DEPT TABLS. SELECT E.*,DNAME FROM EMP E, DEPT WHERE E.DEPTNO = DEPT.DEPTNO; ---------------------------------------------------------------------------------------------WRITE A QUERY TO DISPLAY THE DETAILS OF EMPLOYEES AND THEIR DEPARTMENTS WHERE DNAME IS ACCOUNTING AND SAL IS GREATER THAN 2000: SELECT E.*,DNAME FROM EMP E, DEPT WHERE SAL > 2000 AND E.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING') AND E.DEPTNO = DEPT.DEPTNO; OR SELECT E.*,DNAME FROM EMP E, DEPT WHERE SAL > 2000 AND DNAME = 'ACCOUNTING' AND E.DEPTNO = DEPT.DEPTNO; ---------------------------------------------------------------------------------------------NON EQUI JOIN: ----------------------WRITE A QUERY TO DISPLAY THE EMPLOYEE DETAILS AND THEIR RESPECTIVE GRADE JOINING EMP AND SALGRADE TBLS ---------------------------------------------------------------------------------------------SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL; OR SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE WHERE SAL >= LOSAL AND SAL <= HISAL; SELF JOIN: ----------------JOINING A TABLE TO ITSELF. WRITE A QUERY TO DISPLAY EMPLOYEE NAMES AND THEIR RESPECTIVE MANAGER NAMES REPORTING TO: ---------------------------------------------------------------------------------------------SELECT A.ENAME AS EMPLOYEES, B.ENAME AS MANAGERS FROM EMP A,EMP B WHERE A.MGR = B.EMPNO; 13 rows selected. -----> 14 ---------------------------------------------------------------------------------------------
-> TABLE DROPPED CAN BE GOT BACK. IT WILL BE STORED IN RECYCLE BIN. FLASHBACK IS USED TO RESTORE.
PURGE
-> WHEN USED ALONG WITH DROP THEN IT WILL NOT BE STORED IN RECYCLE. FLASHBACK CANNOT BE USED.
SQL> TRUNCATE TABLE EMPLOY; SQL> DESC EMPLOY SQL> SELECT * FROM EMPLOY; SQL> DROP TABLE EMPLOY [PURGE]; SQL> DESC EMPLOY SQL> SELECT * FROM EMPLOY; SQL> SELECT * FROM TAB;
BIN$.......
SQL> DESC RECYCLEBIN SQL> SELECT OBJECT_NAME,ORIGINAL_NAME FROM RECYCLEBIN; SQL> FLASHBACK TABLE EMPLOY TO BEFORE DROP; Flashback Complete. SQL> DESC EMPLOY SQL> SELECT * FROM EMPLOY; SQL> PURGE RECYCLEBIN; ---> EMPTY RECYCLEBIN PERMANENTLY. RENAME: -------------RENAME <EXST_TBLNAME> TO
: NO NULL : NO DUPL, MULTIPLE NULL ALLO -WED. : NO NULL,NO DUPL ONLY ONE PRIMARY KEY PER TABLE. : CHECKS FOR PREDEFINED VAL.
FOREIGN KEY REFERENTIAL INTEGRITY CONSTRAINT SELF REFERENTIAL INTEGRITY CONSTRAINT 2 LEVELS: --------------COLUMN TABLE NAMED: ------------USER : CONSTRAINT
: ALTER TABLE
: ALTER TABLE
2 STAGES: -------------------CREATE ALTER CREATE: ------------CREATE TABLE EMPLY_DETAILS (EMPNO NUMBER(4) CONSTRAINT PK_EMPNO PRIMARY KEY, ENAME CHAR(10) CONSTRAINT NN_ENAME NOT NULL, SAL NUMBER(10) CHECK(SAL>10000), EPFNO NUMBER(4) NOT NULL UNIQUE); CONSTRAINT NAMES CANNOT BE DUPLICATED ACROSS TBLS WITHIN THE USER. ERROR at line 3: ORA-02264: name already used by an existing constraint USER_CONSTRAINTS ---------------------------------DESC USER_CONSTRAINTS SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLY_DETAILS'; CONSTRAINT_NAME C STATUS -----------------------------------------NN_NAME C ENABLED SYS_C005595 C ENABLED SYS_C005596 C ENABLED PK_NO P ENABLED SYS_C005598 U ENABLED INSERT INTO EMPLY_DETAILS VALUES (1001,'ANIL',99999,0001);
INSERT INTO EMPLY_DETAILS VALUES (1001,'ANCHAN',88888,0002); ERROR at line 1: ORA-00001: unique constraint (ANIL.PK_NO) violated INSERT INTO EMPLY_DETAILS VALUES (NULL,'ANCHAN',88888,0002); ERROR at line 2: ORA-01400: cannot insert NULL into ("ANIL"."EMPLY_DETAILS"."EMPNO") ------------------------------ PRIMARY KEY --------------------------------------INSERT INTO EMPLY_DETAILS VALUES (1002,'',88888,0002); ERROR at line 2: ORA-01400: cannot insert NULL into ("ANIL"."EMPLY_DETAILS"."ENAME") ------------------------------ NOT NULL --------------------------------------------INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',888,0002); ERROR at line 1: ORA-02290: check constraint (ANIL.SYS_C005596) violated ------------------------------ CHECK -------------------------------------------------INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',88888,0001); ERROR at line 1: ORA-00001: unique constraint (ANIL.SYS_C005598) violated INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',88888,NULL); ERROR at line 2: ORA-01400: cannot insert NULL into ("ANIL"."EMPLY_DETAILS"."EPFNO" ------------------------------ 2 CONSTRAINTS --> NOT NULL, UNIQUE --DISABLE/ENABLE: --------------------------ALTER TABLE EMPLY_DETAILS DISABLE CONSTRAINT SYS_C005598; INSERT INTO EMPLY_DETAILS VALUES (1002,'ANCHAN',88888,1); SQL> ALTER TABLE EMPLY_DETAILS ENABLE CONSTRAINT SYS_C005598; * ERROR at line 1: ORA-02299: cannot validate (ANIL.SYS_C005598) - duplicate keys found SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLY_DETAILS'; SQL> DELETE EMPLY_DETAILS WHERE EMPNO = 1002; SQL> COMMIT; SQL> ALTER TABLE EMPLY_DETAILS ENABLE CONSTRAINT SYS_C005598;
TO APPLY CONSTRAINTS WITH ALTER: ----------------------------------------------------------ALTER TABLE EMPLY_DETAILS DROP CONSTRAINT NN_NAME; SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLY_DETAILS'; ALTER+ADD : TO DEFINE A NOT NULL ON ENAME: --------------------------------------------CANNOT BE USED TO ADD A NOT NULL CONSTRAINT. NOTE: USE ALTER+MODIFY ALTER TABLE
-> ERROR
ALTER TABLE EMPLY_DETAILS MODIFY(ENAME CONSTRAINT NN_NAME NOT NULL); ALTER TABLE EMPLY_DETAILS MODIFY(ENAME CONSTRAINT UNQ_NAME UNIQUE); NOTE: ALTER+MODIFY
-> APPENDS THE NEW EXISTING CONSTRAINT.
TABLE LEVEL: ---------------------COMPOSITE PRIMARY KEY: CHECKS ON ROW BASIS ----------------------------------------MULTIPLE COLUMNS ACT AS A SINGLE PRIMARY KEY. CREATE TABLE T (A NUMBER(4), B NUMBER(4), CONSTRAINT C_PK PRIMARY KEY(A,B) ); A -1 1 2 2 2 3 3
B -1 2 1 2 3 2 3
CONSTRAINT TO THE
FOREIGN KEY REFERENTIAL INTEGRITY CONSTRAINT SELF REFERENTIAL INTEGRITY CONSTRAINT PRIMARY KEY FOREIGN KEY ---------------------------------------NO NULL,NO DUPL NULL,DUPL ALLOWED. 1 PK PER TABLE MULTIPLE FK'S PER TABLE NA REFERS TO PK / UNIQUE OF TBL. COMPOSITE PK NA ALSO KNOWN AS A COMMON COLUMN BETN 2 TABLES. COMMONCOLUMN
SAME TBL OR ANOTHER
: THEIR NAMES CAN BE DIFFERENT / SAME BUT THEIR DATATYPES MUST BE SAME.
2 LAWS: ------------1> NO INSERTION OF A DETAIL RECORD ALLOWED IF IS NOT PRESENT. 2> NO DELETION OF MASTER ALLOWED WHEN DEPENDENCIES EXIST IN THE DETAIL.
CORRESPONDING MASTER RECORD
ON DELETE CASCADE: ---------------------------------IT ALLOWS TO DELETE THE MASTER RECORD,SUBSEQUENTLY THE DEPENDENCIES WILL ALSO BE DELETED. SELF REFERENTIAL INTEGRITY CONSTRAINT: -------------------------------------------------------------------PRIMARY KEY AND FOREIGN KEY RESIDE WITHIN THE SAME TABLE. i.e THE FOREIGN KEY REFERS TO PRIMARY KEY WITHIN THE SAME TBL. APPLY THE CONSTRAINTS : CREATE / ALTER: -------------------------CREATE TABLE DEPARTMENT (DNO NUMBER(2) CONSTRAINT PK_DNO PRIMARY KEY, DNAME VARCHAR2(20), LOC VARCHAR2(20) ); CREATE TABLE EMPLOYEES (EMPNO NUMBER(4) CONSTRAINT PK_NO PRIMARY KEY, ENAME CHAR(10), SAL NUMBER(10), MGR NUMBER(4), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPARTMENT(DNO) -- COLUMN LEVEL ); OR CREATE TABLE EMPLOYEES (EMPNO NUMBER(4) CONSTRAINT PK_NO PRIMARY KEY, ENAME CHAR(10), SAL NUMBER(10), MGR NUMBER(4),
DEPTNO NUMBER(2), CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO)
REFERENCES DEPARTMENT(DNO)
); SQL> INSERT INTO EMPLOYEES VALUES (1001,'ANIL',99999,1001,10); ERROR at line 1: ORA-02291: integrity constraint (ANIL.FK_DEPTNO) violated - parent key not found BEGIN INSERT INTO DEPARTMENT VALUES (10,'SALES','BLORE'); INSERT INTO EMPLOYEES VALUES (1001,'ANIL',99999,1001,10); INSERT INTO EMPLOYEES VALUES (1002,'ANCHAN',99999,1001,10); INSERT INTO EMPLOYEES VALUES (1003,'COLLINS',99999,1001,10); INSERT INTO EMPLOYEES VALUES (1004,'RAJ',99999,1002,10); END; / DELETE DEPARTMENT WHERE DNO = 10; ERROR at line 1: ORA-02292: integrity constraint (ANIL.FK_DEPTNO) violated - child record found ALTER TABLE EMPLOYEES DROP CONSTRAINT FK_DEPTNO; TO APPLY A REFERENTIAL INTEGRITY CONSTRAINT FOR AN EXISTING TBL: --------------------------------------------------------------------------------------------ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DNO); SELF REFERENTIAL INTEGRITY CONSTRAINT: -------------------------------------------------------------------ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_KEY_MGR FOREIGN KEY(MGR) REFERENCES EMPLOYEES(EMPNO); ON DELETE CASCADE: --------------------------------ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) DEPARTMENT(DNO) ON DELETE CASCADE;
REFERENCES
ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_KEY_MGR FOREIGN KEY(MGR) REFERENCES EMPLOYEES(EMPNO) ON DELETE CASCADE; QUERYING THE DATABASE WRT 2 OR MORE TBLS: --------------------------------------------------------------------------1> SET OPERATORS 2> JOINS
3> SUB QUERIES SET OPERATORS -------------------------UNION ALL : INCL DUPL + INCL NULL UNION : EXCL DUPL + INCL NULL INTERSECT : EXCL DUPL - EXCL NULL MINUS : T1-T2 : DATA PRESENT IN T1 NOT IN T2 VICEVERSA. SELECT ..COL.FROM
EMP FROM DEPT; EMP FROM DEPT; EMP FROM DEPT; EMP FROM DEPT; DEPT FROM EMP;
JOINS -------------------------------------------SELECT * FROM EMP,DEPT; EMP -> 14 DEPT -> 4 CARTESIAN PRODUCT =========> 14*4 = 56 IS USED: 1> TO ELIMINATE CARTESIAN PRODUCT 2> TO RETRIEVE DATA FROM MORE THAN 1 TBL AT A TIME. JOIN CONCEPT: ------------------------TABLE1.COMMONCOLUMN = TABLE2.COMMONCOLUMN TYPES: ----------EQUI NON EQUI SELF JOIN OUTER FULL OUTER JOIN EQUI JOIN:
----------------SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT EMPNO,ENAME,SAL,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT EMPNO,ENAME,SAL,DEPTNO,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; ERROR at line 1: ORA-00918: column ambiguously defined SOL1: --------SELECT EMPNO,ENAME,SAL,EMP.DEPTNO,DNAME FROM EMP.DEPTNO = DEPT.DEPTNO; OR SELECT EMPNO,ENAME,SAL,DEPT.DEPTNO,DNAME FROM EMP.DEPTNO = DEPT.DEPTNO; SOL2: TABLE TEMPORARY ALIAS --------SELECT EMPNO,ENAME,SAL,D.DEPTNO,DNAME FROM WHERE E.DEPTNO = D.DEPTNO; OR SELECT EMPNO,ENAME,SAL,E.DEPTNO,DNAME FROM WHERE E.DEPTNO = D.DEPTNO;
EMP,DEPT WHERE EMP,DEPT WHERE
EMP
E , DEPT
D
EMP
E , DEPT
D
WRITE A QUERY TO DISPLAY ALL THE EMP DETAILS AND THEIR DEPT NAMES JOINING EMP AND DEPT TABLS. SELECT E.*,DNAME FROM EMP E, DEPT WHERE E.DEPTNO = DEPT.DEPTNO; ---------------------------------------------------------------------------------------------WRITE A QUERY TO DISPLAY THE DETAILS OF EMPLOYEES AND THEIR DEPARTMENTS WHERE DNAME IS ACCOUNTING AND SAL IS GREATER THAN 2000: SELECT E.*,DNAME FROM EMP E, DEPT WHERE SAL > 2000 AND E.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING') AND E.DEPTNO = DEPT.DEPTNO; OR SELECT E.*,DNAME FROM EMP E, DEPT WHERE SAL > 2000 AND DNAME = 'ACCOUNTING' AND E.DEPTNO = DEPT.DEPTNO; ---------------------------------------------------------------------------------------------NON EQUI JOIN: ----------------------WRITE A QUERY TO DISPLAY THE EMPLOYEE DETAILS AND THEIR RESPECTIVE GRADE JOINING EMP AND SALGRADE TBLS ---------------------------------------------------------------------------------------------SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL; OR SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE WHERE SAL >= LOSAL AND SAL <= HISAL; SELF JOIN: ----------------JOINING A TABLE TO ITSELF. WRITE A QUERY TO DISPLAY EMPLOYEE NAMES AND THEIR RESPECTIVE MANAGER NAMES REPORTING TO: ---------------------------------------------------------------------------------------------SELECT A.ENAME AS EMPLOYEES, B.ENAME AS MANAGERS FROM EMP A,EMP B WHERE A.MGR = B.EMPNO; 13 rows selected. -----> 14 ---------------------------------------------------------------------------------------------
Related Documents
Day3 Sql
October 2019 18
Day3
November 2019 25
Day3
October 2019 25
Training Day3
November 2019 8
Struts - Day3
July 2020 12
