Day4

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. DAY4 ------------

OUTER JOIN: ------------------------IT SATISFIES EQUI CONDITION AND ALSO UNMATCHED DATA FROM ONE OF THE TBL; represented by a (+)sign SELECT A.ENAME AS EMPLOYEES, B.ENAME AS MANAGERS FROM EMP A,EMP B WHERE A.MGR=B.EMPNO(+); ->LEFT J SELECT A.ENAME AS EMPLOYEES, B.ENAME AS MANAGERS FROM EMP A,EMP B WHERE B.EMPNO(+)=A.MGR; FULL OUTER JOIN: ---------------------------SELECT * FROM EMP FULL OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; satisfies equi condition and also unmarked data from both the tables REPLACE , WITH KEYWORD FULL OUTER JOIN REPLACE WHERE WITH ON if we want left/right outer join we use SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; SUBQUERIES ============= <----------------------------------------------------------------MQ----------------------------> (<-------------------------SQ------------------------------------>) IS PART OF MAIN QUERY IS ENCLOSED WITHIN BARACKETS GETS EXEC FRIST,RESULT IS FED TO THE MAIN AND THERBY THE MQ GETS EXEC. CAN B E INDEPENDENTLY EXEC AT TEH SQL PROMPT. MQ DEPENDS ON THE SQ. SELECT ENAME FORM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAMA='JONES') CLASS ASSIGNMENT ---------------------------------WRITE A QUERY TO DISPLAY THE II MAX SAL FROM EMP; SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP);

PUT THE +SIGN IN THE OPP SIDE OTHERWISE IT GIVES THE CARTESIAN PRODUCT ie. put + sign on where there are mo entries

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

DISPLAY THE EMPLOPYNAME WHO IS GETTING THE SECOND MAX SALARY ------------------------------------------------------

For Evaluation Only.

SELECT ENAME,MAX(SAL) FROM EMP WHERE SAL NOT IN(SELECT MAX(SAL) FROM EMP); OR SELECT ENAME FROM EMP WHER SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP)); SELECT DISTINCT SAL FROM EMP X WHERE &N=(SELECT COUNT(DISTINCT SAL) FROM EMP Y WHERE X.SAL<=Y.SAL); TYPES: --------------RETURNING A SINGLE VALUE RETURNING MULTIPLE VALUES MULTIPLE SQ NESTED SQ CORRELATED SQ RETURNING A SINGLE VAUE ----------------------------------------SELECT ENAME FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP); RETURNING MULTIPLE VALUES: ---------------------------------------------SELECT ENAME FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE DEPTNO=10); here subquery returns the multiple values so we use two kinds of solutions for this SOLN: ANY,ALL SELECT ENAME FROM EMP WHERE SAL>ANY (SELECT SAL FROM EMP WHERE DEPTNO=10); ANY ACTS LIKE A "OR " AND ALLACTS LIKE "AND" SELECT ENAME,SAL FROM EMP WHERE SAL>ANY (SELECT SAL FROM EMP WHERE DEPTNO=10); MULTIPLE SUBQUERY -----------------------------------<----------------------------MQ------------------------------------------------> <--------------SQ1------------------> AND/OR <----------------------------------------> NESTED SUBQUERY: ----------------------------------<---------------------------------------------------MQ------------------------------> AND MANY SUB QUERIES FALLOW IT NESTED IN

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.

WRITE A QUERY TO DISPLAY EMPLOY DETAILS WHOS SALARIES ARE GREATER THAN AVG SALARY AND DEPTNAME ACCOUNTING USING SUBQUERIES SELECT * FROM EMP WHERE(SELECT SAL FROM EMP WHERE SAL>(SELECT AVG(SAL) EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME='ACCOUNTING')); SOLUTION SELECT E.*,DNAME FROM EMP E,DEPT WHERE SAL>(SELECT AVG(SAL) FROM EMP) AND DNAME='ACCOUNTING' AND E.DEPTNO=DEPT.DEPTNO; OR IT CAN BE WRITTEN LIKE THIS SELECT E.*,(SELECT DNAME FROM DEPT WHERE DNAME='ACCOUNTING') FROM EMP E WHRE SAL>(SELECT AVG(SAL) FROM EMP) AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE DNAME='ACCOUNTING'); SELECT ENAME FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP))); CORRELATED SQ -------------------------------IS PART OF MAIN MQ IS ENCLOSED WITHIN BRACKETS SQ AND MQ GETS EXECUTED CORRELATEDLY SQ CAN NOT BE INDEPENDENTLY EXEC AT THE SQL PROMPT SQ DEPENDS ON THE MQ WRITE A QUERY TO DISPALY THE NTH MAX SAL FROM EMP WHERE N IS AN INPUT FROM THE USER

A 2.97 mm

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

D ATABASE OBJECTS =================== OBJECTS ---------------

For Evaluation Only. DATA DICTIONARY VIEWS -------------------------------

TABLE SYNONYM SEQUENCE INDEX

TABS,USER_TABLES USER_VIEWS USER_SYNONIMS

TYPE AS OBJECT TO CREATE -----------------------------------------------CREATE OBJECT ....... TO_DROP -----------------DROP OBJECT ....... VIEW: view is imaginary of virtual object. cuz does not exist physically any space and stored in the form of a query It can be created/dropped . it can be created with req number of columns based on base table can also be created based on more than one table ---------->known as complex views views are updatables. view ------------> base TBL <------------changes (i,u,d) view -------------> base tbl <----------drop genarates error MATERIALIZED VIEWS PREV KNOWN AS SNAP SHOTS ---------------------------------MAT VIEW ------> BASE TBL NOT BE REFLECTED <------ CAN NOT BE MANIPULATED changes made on the base tables do not reflect(DML)

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. CAN ALSO BE CREATED BASED ON NON-EXISTING TABLE -------> KNOWN AS FORCE VIEW CREATE VIEW AS SELECT COL1,COL2,COL5,... FROM ; CREATE VIEW CMP_DEPT_EMP AS SELECT EMPNO,ENAME,SAL,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; UPDATE CMP_DEPT_EMP SET SAL=9999 WHERE DNAME='ACCOUNTING'; DROP TABLE EMP; DESC VW_EMP; SELECT * FROM VW_EMP; SIMILARLY FORCE VIEW --------------------------------------CREATE FORCE VIEW F_VW_EMP AS SELECT * FROM XYZ;

COMPLEX MATERILAZED VIEW IS POSSIBLW ONLY IF IT INCLUDES PRIMARY KEY PRIAMARY TBL CAN BE UPDATED BUT NOT THE VIEW

MATERIALIZED VIEWS: ----------------------------------CONN SYS/ORALCE GRANT CREATE MATERIALIZED VIEW TO CREATE MATERIALIZED VIEW M_VW_EMP AS SELECT EMPNO,ENAME,SAL FROM EMP;

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.

DESC USER_VIEWS SELECT VIEW_NAME FROM USER_VIEWS; SYNONYM --------------------IS A PERMANENT ALIAS NAME FOR AN OBJECT MUST BE CREATED WITH ALL COLS FOR SECURITY REASONS SYNONYM -------------> BASE TABLE <------------(DML) TRANSLATION IS NO LONGER VALID GIVES object doesn't exist CREATE SYNONYM SYN_EMP FOR EMP; UPDATE SYN_EMP SET SAL=8778 WHERE ENAME='SMITH'; USER1> GRANT ALL ON T1 TO RAMA CREATE SYNONYM T1 FOR RAMA.T1 FOR USER1.T1; SELECT * FROM T1; SELECT SYNONYM_NAME FROM USER _SYNONYM; SEQUENCE ----------------------GENERATION OF PRIMARY KEY VALUES. 2 STEPS: ------------CREATE GENERATE 2 ATTRIBUTES --------------------CURVAL NEXTVAL DUAL <-- SELECT SEQUENCE NAME.CURVAL FROM DUAL; SELECT SEQNAME.NEXTVAL FROM DUAL

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

CREATE SEQUENCE <SEQ_NAME> INCREMENT BY START WITH MINVALUE MAXVALUE CYCLE/NOCYCLE CACHE;

For Evaluation Only.

SQL> CREATE SEQUENCE SEQ_NO INCREMENT BY 1 START WITH 3 MINVALUE 2 MAXVALUE 10 CYCLE CACHE 2; DEFAULT 20 BYTES INDEX -----------------FOR FASTER RETRIEVAL OF DATA FROM THE DB. IMPLICIT(SYSTEM) EXPLICIT(USER) IMPLICIT: WHEN USER DEFINES PRIMARY KEY,UNIQUE OR COMPOSITE PRIMARY KEY. SELECT INDEX_NAME FROM USER_INEDEXES; EXPLICIT: CREATE INDEX ON (COL1,COL2,.....,COL9); CREATE INDEX IND_EMP ON EMP(EMPNO,ENAME,SAL);

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.

ORDBMS -------------

CREATE TYPE ADDR_TY AS OBJECT (ADDR1 VARCHAR2(20), ADDR2 VARCHAR2(20), CITY VARCHAR(10), STATE VARCHAR(20), PINCODE CHAR(6)); CREATE TABLE TEST_TBL ( EMPNO NUMBER(4) ENAME CHAR(10) ADDRESS ADDR_TY); INSERT INTO TEST_TBL VALUES (1001,'ANIL',ADDR_TY('ORACLE','WF','BLORE','KAR',560029)); SELECT * FROM TEST_TBL; SELECT X.ADDRESS.CITY FROM TEST_TBL

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.

CALL HER REGD CERTIFICATION

41084657

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