Day3

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

DDL CONT... ------------------ALTER ADD : COLUMNS MODIFY: COLUMN DATATYPE CONTRAINT DROP:COLUMN CONSTRAINT RENAME: COLUMN ALTER+ADD ----------------ALTER TABLE

ALTER+MODIFY ------------------------ALTER TABLE MODIFY (EXIST_COLNMAE NEW_DATATYPE(SIZE)); ALTER TABLE EMPLOY ADD (MGR NUMBER(4)); ALTER+ DROP --------------------ALTER TABLE DROP COLUMN ; ALTER TABLE EMPLOY DROP COLUMN MGR; ALTER TABLE EMPLOY DROP COLUMN (REMARKS,PROGRAM) ALTER+RENAME ----------------------ALTER TABLE RENAME COLUMN <EXIST_COLNAME> TO ; ALTER TABLE EMPLOY RENAME COLUMN SALARY TO EMP_SAL; TRUNCATE : REMOVES ALL RECS FROM DB PERMANENTLY BUT TABLE STRUCTURE REMAINS ---------------DROP -> REMOVES ALL RECS AND TABLE FROM THE DATABASE PERMANENTLY. -------TRUNCATE TABLE ;

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

NEW FEATURE IN 10G TABLE DROP CAN BE GOT BACK- WILL BE STORED IN THE RECYCLE BIN FLASHBACK IS USED TO RESTORE PURGE -> WHEN USED ALONG WITH DROP THEN IT WILL NOT BE STORED IN THE RECYCLE DROP TABLE PURGE PURGE RECYCLEBIN -- TO EMPTY RECYCLE TRUNCATE TABLE EMPLOY; DESC EMPLOY SELECT * FROM EMPLOY; DROP TABLE EMPLOY; DESC EMPLOY SELECT * FROM EMPLOY; SELECT * FROM TAB; DESC RECYCLEBIN SELECT OBJECT _NAME.ORIGINAL_NAME FROM RECYCLEBIN

DELETE IS BASED ON THE RECORD BUT DROP IS BASED ON THE TABLE

FLASHBACK TABLE EMPLOY TO BEFORE DROP RENAME --------------RENAME <EXIST-TABNAME> TO ; DESC RENAME EMPLOY TO EMP_DETAIL ^ completes ddl commands | ---------------------------------------CONSTRAINTS -------------------------are restrictions for user, applied on columns. when user enters somethings it should check for condition and allow only that are permitted TYPES: NOT NULL : NO NULL UNIQUE : NO DUPLICATES, MULTIPLE NULL ARE ALLOWED PRIMARY KEY: NO NULL AND NO DUPLICATES ONLY ONE PRIMARY KEY PER TABLE

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

CHECK : CHECKS FOR PREDEFINED VALUE FOREIGN KEY REFERENTIAL INTEGRITY CONSTRAINT SELF REFERENTIAL INTEGRITY CONSTRAINT constraints can be applied at two levels ----------------------COLUMN TABLE NAMED: -------------USER : CONSTRAINT CON_TYPE SYSTEM(DEFAULT): CON_TYPE :SYS_COO4859 ENABLE/DESABLE: ALTER TABLE DISABLE/ENABLE CONSTRAINT ; DROP : ALTER TABLE DROP CONSTRAINT < CONSTR_NAME>

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 NULLUNIQUE); CONSTRAINT TABLES CAN NOT BE DUPLICATED USER_CONSTRAINTS --------------------------------DESC_USER_CONSTRAINTS SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME ='EMP_DET'; not null and check both are represented as c

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

if not specified system names the constrains by itself INSERT INTO EMP_DET VALUES (10001,'ANIL',99999,0001); INSERT INTO EMP_DET VALUES (1001,'ANCHAN',88888,0002);

DISABLE/ENABLE: ---------------------------ALTER TABLE EMP_DET DISABLE CONTRAINT SYS_C005310 ; DELETE EMP_DET WHERE EMPID IN 1002; ALTER TABLE EMP_DET ENABLE CONSTRAINT SYS_COO5310; TO APPLY CONSTRAINT WITH ALTER: -------------------------------------------------------ALTER TABLE EMP_DET DROP CONSTRAINT NN_NAME; ALTER+ADD : TO DEFINE A NOT NULL ON ENAME CAN NOT BE USED TO ADD A NOT NULL CONSTRAINT NOTE: USE ATER+MODIFY ALTER TABLE ADD CONSTRAINT < CONTR_NAME> CONTR_TYPE(COLNAME); ALTER+MODIFY ----------------------ALTER TABLE MODIFY (COLNAME CONSTRAINT CON_TYPE); EXERCISE ALTER TABLE EMP_DET ADD CONSTRAINT NN_NAME NOT NULL(ENAME); -> ERROR ALTER TABLE EMP_DET MODIFY (ENAME CONSTRAINT NN_NAME NOT NULL) it wont remove the existing the constraint but appends the new constraint to the existing constraint if you don't want the existing then remove and add the new constraint we can not use add for the not null constraint

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

T ABLE LEVEL : ---------------------CRETE TABLE T A NUMBER (4) CONSTRAINT........ CONSTRAINT ...., now i don't define the constraints now B NUMBER(5) , when I want to apply one constraint for both columns we use table level COMPOSITE PRIMARY KEY: --------------------------------------MULTIPLE COLUMNS ACT AS A SINGLE PRIMARY KEY. CONSTRAINT C_PK PRIMARY KEY(A,B) dont forget to put comma otherwise it becomes column level this is used only for primary and unique; cuz foreign key refers to only primary key or unique key this constraint is checked on row basis FOREIGN KEY REFERENTIAL INTEGRITY CONSTRAINT SELF REFERRAL INTEGRITY CONSTRAINT PRIMARY KEY -------------------NO NULL,NO DUPL 1 PK FOR TABLE NA COMPOSITE PK

FOREIGN KEY --------------------NULL,DUPL ALLOWED MULTIPLE FK'S ARE ALLOWED REFERS TO PK/UNIQUE OF SAME TBL OR ANOTHER TBL NOT APPLICABLE HERE ALSO KNOW AS A COMMON COLUMN BETN 2 TABLES common column : their name can be same/different but their data types must be same TWO LAWS OF RDBMS: 1> NO INSERTION OF A DETAIL RECORD IS ALLOWED IF CORRESPONDING MASTER RECORDIS PRESENT 2> NO DELETION OF MASTER ALLOWED IS WHEN DEPENDENCIES EXIST IN THE DETAIL ON DELETE CASCADE: ----------------------------------IT ALLOWS TO DELETE THE MASTER RECORD, SUBSEQUENTLY THE DEPENDENCIES WILL ALSO BE DELETED it deletes all the dependencies in the referred table SELF REFERENTIAL INTEGRITY CONSTRAINT: --------------------------------------------------------------------PRIMARY KEY AND FOREIGN KEY RESIDE WITH IN THE SAME TABLE i.e THE FOREIGN KEY REFERS TO PRIMARY KEY WITH IN THE SAME TABLE.

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

APPLY THE CONSTRAINTS: CREATE/ALTER -------------------------CREATE TABLE DEPARTMENT ( DNO NUMBER2 CONSTRAINT PK_DNO PRIMARY KEY, DNAME VARCHAR2(20), LOC VARCHAR(20) ); CREATE TABLE EMPLOYEES ( EMPNO NUMBER(4) CONSTRAINT PK_NO PRIMARY KEY, ENAME CHAR(10), SAL NUMBER(10), MGR NUMBER(4), DEPTNO NUMBER(2) CONTRAINT FK_DEPTNO REFERRENCES DEPARTMENT(DNO) ); INSERT INTO DEPARTMENT VALUES (10,'SALES','BLORE'); INSERT INTO EMPS VALUES (1001,'ANIL',99999,1001,10); INSERT INTO EMPS VALUES WHEN THERE ARE ENTRIES IN THE OTHER TABLE WE CAN NOT DELETE ENTRIES IN THE MASTER RECORD IT SAYS integrity constraint violated now drop the constraint ALTER TABLE EMPS DROP CONSTRAINT FK_DEPTNO; TO APPLY A REFERRENTIAL INTEGRITY CONSTRAINT FOR AN EXISTING TBL FOR AB EXISTING TBL; -----------------------------------ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DNO);

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

SELF REFERRENTIAL INTEGRITY CONSTRAINT: -------------------------------------------------------------------ALTER TABLE EMPLOYEES ADD CONTRAINT FK_KEY_MGR FOREIGN KEY(MGR) REFERENCES EMPLOYEES(EMPNO);

For Evaluation Only.

ON DELETE CASCADE: ---------------------------------ALTER TABLE EMPS ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DNO) ON DELETE CASCADE; ALTER TABLE EMPS ADD CONSTRAINT FK_MGR FOREIGN KEY(MGR) REFERENCES EMPLOYEES(EMPNO) ON DELETE CASCADE;

DELETE EMPS WHERE MGR IS NULL;

QUERYING THE DATABASE WRT TO OR MORE TABLES -------------------------------------------------------------------------------------1>SET OPERATORS 2>JOINS 3> SUB QUERIES SET OPERATORS --------------------------UNION ALL : INCL DUPL+INCL NULL UNION : EXCLUDES THE DUPLICATES+INLUDES NULL INTERSECT: COMMON DATA FROM TABLE1 AND TABLE2 EXCL NULL MINUS: T1-T2 DATA PRESENT IN T1 AND NOT IN T2 VICE VERSA

BOTH UNION AND INTERSECT REMOVES DUPLICATES BUT INTERSECT EXCLUDE NULL

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

LIMITATIONS: -----------------------NO OF COLUMNS SELECTED FROM T1 MUST BE EQ TO NUMBER OF COLUMNS IN TABLE2 AND ALSO COL DATA TYPES SHOULD BE SAME OUTPUT WILL BE DISPLAYED FROM THE BOTH THE TABLES ONE BELLOW THE TABLE SELECT DEPTNO FROM EMP UNION ALL SELECT DEPTNO FROM DEPT; SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP; SELECT DEPTNO FROM DEPT UNION ALL SELECT DEPTNO FROM EMP; SELECT DEPTNO FROM EMP INTERSECT ALL SELECT DEPTNO FROM DEPT;

JOINS ----------

SELECT * FROM EMP,DEPT; gives the cartesian product JOIN IS USED FOR 1> TO ELIMINATE CARTESIAN PRODUCT 2> TO RETRIEVE DATA FROM MORE THAN ONE TABLE AT A TIME TABLE1.COMMONCOLUMN=TABLE2.COMMONCOLUMN it gives the columns for which these two are equal TYPES: ------------EQUI NON EQUI SELF JOIN OUTER FULL OTER JOIN EQUI JOIN ------------------SELECT EMPNO ,ENAME,SAL,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

Generated by Foxit Edited PDF Creator © Foxit Software by Foxit Reader http://www.foxitsoftware.com For by evaluation only. Copyright(C) Foxit Software Company,2005-2007

For Evaluation Only.

WHEN DEPTNO IS CALLED (ITS THERE IN BOTH THE TABLES) IT THROWS AN ERROR SOL1; SPECIFY THE TABLE FROM WHICH IT HAS TO BE PICKED LIKE :TABLE1.DEPTNAME SOL2: TEMP ALIAS SELECT EMPNO,ENAME,SAL,E.DEPTNO,DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO -------------------------------------------------------------------------------------------------------------------------------------------------------------------WRITE A QUERY TO DISPLAY ALL THE EMP DETAILS AND THEIR DEPT NAMES JOINING EMP AND DEPT TABS SELECT E.*,DNAME FORM EMP E,DEPT WHERE E.DEPTNO=DEPT.DEPTNO; ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------WRITE A QUERY TO DISPLAY THE DETAILS OF THE EMPLOYEES AND THEIR DEPARTMENTS WHERE DNAME IS ACCOUNTING AND SALARY IS GREATER THAN 2000; SELECT E.*,DNAME FROM EMP E,DEPT WHERE SAL>2000 AND DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='ACCOUNTING') AND E.DEPTNO=DEPT.DEPTNO; -------------------------------------------------------------------------------------------------------------------------------------------WRITE A QUERY TO DISPLAY THE EMPLOY DETAILS AND THEIR RESPECTIVE SALARY GRADES -------------------------------------------------------------------------------------------------------------------------------------------SELECT ENAME,GRADE FROM EMP, SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL; SELF JOIN -------------------JOINING A TABLE TO ITSELF. WRITE A QUERY TO DISPLAY THE EMPNAME AND THEIR RESPECTIVE MANGER NAMES REPORTING --------------------------------------------------------------------------------------------------------------------------------------------------SELECT A.ENAME,B.ENAME FROM EMP A,EMP B WHERE A.MGR=B.EMPNO; THIS PRODUCES THE RESULT WITH OUT THE KING SO

Generated by Foxit PDF Creator © Foxit Software http://www.foxitsoftware.com For evaluation only.