Day2

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. SQL FUNCTIONS ARE BUILT IN FUNCTIONS, THEY CAN BE CALLED FROM A USER DEFINED TBL OR SYSTEM DEFINED VIEW(DUAL) SQL>DESC DUAL DESC IS A SQL COMMAND SELECT * FROM DUAL; collection of data placed on a horizontal line is a record record or tuple or row all are same column/atribute table/entity table consists of row and column SYSDATE() --------------built in function can be called form any table and system defined table SELECT SYSDATE FROM DUAL; 02-SEP-08 (DEFSULT DATE FORMAT-->DD-MM-YY) there must be atleast a record to call this sydate function TYPES: ---------------NUMBER CHARECTER DATE CONVERSION MISC NUMBER: -------------------ABS() SQRT() MOD() ROUND() FLOOR() CIEL() ---SELECT ABS(-1),SQRT(9),MOD(3,2), ROUND(25.432) FROM DUAL; CHARECTER FUNCTIONS -----------------------------------LENGTH() CONCAT() SUBSTRING() INSTR() LPAD()

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. RPAD() ---SELECT LENGTH ('INDUKURI RAMA RAJU') FROM DUAL; SELECT ENAME AS NAME,LENGTH(ENAME) FROM EMP; SELECT CONCAT(ENAME,JOB) WORKS_AS FROM EMP; SELECT ENAME||' WORKS AS'|| JOB FROM EMP; SUBSTR() -----------------SELECT ENAME,SUBSTR(ENAME,1,3) " START_1_3" FROM EMP; INSTR() -----------------SELECT ENAME,INSTR(ENAME,'A') " POS A" FROM EMP; LPAD() -----------pad something to the left side SELECT LPAD(EMPNO,10,'ORACLE') AS EMPID FROM EMP; similarly RPAD() CONVERSION(): ------------------------------TO_CAHR() TO_DATE() --TO_CAHR(): DATE TO CAHR FORMAT ----------------02-SEP-08 ->SYSDATE ->DD-MM-YY INPUT -------------DD DDTH YY YYYY DAY DY Dy

OUTPUT --------------------02 02ND 08 2008 TUESDAY TUE Tue

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.

day tuesday MON SEP MONTH SEPTEMBER YEAR TWO THOUSAND EIGHT HH:MI:SS 10:50:07 HH24:MI:SS HH:MM:SS MM WILL PICK UP MONTH WRITE A QUERY TO DISPLAY TODAYS DAY: SELECT TO_CHAR(SYSDATE,'DAY') "TODAY IS " FROM DUAL; SELECT TO_CHAR(TO_DATE('1947-15-08','YYYY-DD-MM'),'DAY') "DAY OF INDEPENDENCE " FROM DUAL; be carefull when working with the dates DATE FUNCTIONS: --------------------------------ADD_MONTHS() MONTHS_BETWEEN() NEXT_DAY() :SYSDATE,'FRI' LAST_DAY():SYSDATE MISC: -------------NVL() NOVALUE NVL2() NEW ONE WITH OUT LIMITATIONS OF DECODE() ---SEL SAL,COMM,SAL+COMM AS GROSS_SAL FROM EMP;when there is one null value sum is null so NVL() : NVL(COLNAME,VAL) NVL(COMM,0) SELECT SAL,COMM,SAL+NVL(COMM,0) AS GROSS_SAL FROM EMP; if the value is null then take the value instead and NVL2() -------VALUE NEED NOT BE GIVEN SELECT SAL,COMM,NVL2(COMM,SAL+COMM,SAL) AS GROSS FROM EMP; if commision is not null then take next arg otherwise take next to next arg here it is 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. DECODE() -----------------we can put if then else statements here SELECT JOB,SAL, DECODE(JOB,'MANAGER',SAL+SAL*.10, 'CLERK',SAL+SAL*.05, SAL) AS RAISE_SAL FROM EMP; IF NO ELSE IE LAST SENTENCE IS NOT THERE THEN OTHER ENTRIES WILL NOT BE PRINTED; ROWID -------------is a unique id generated for every record stored in the database ROWNUM: ---------------sequnce of numbers for the records SELECT ROWNUM,ROWID FROM EMP; CLASS ASSGINMENT: -----------------------------WRITE A QUERY TO DISPLAY THE ALTERNATE RECORDS FROM EMP TABLE WRITE A QUERY TO DISPLAY THE LAST 5 RECORDS FROM EMP TABLE HINT : USE MODE AND DECODE FUNCTIONS HINT: USE ORDER BY AND LESS THAN SELECT * DECODE (MOD(ROWNUM,2),0,*) 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.

✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍✍

SQL COMMANDS DDL: DATA DEFINITION LANGUAGE ->PERMANENT CAN NOT BE ROLLED BACK AUTO COMMIT FALLOWS CREATE this is w/r structure of the table ALTER DROP TRUNCATE RENAME DML : DATA MANIPULATION LANGUAGE -> CAN BE ROLLED BACK INSERT UPDATE DELETE these operations are on records but not on the tables like DDL. minute we execute DDL, auto commit immedialtely fallows but f

TCL NOTE: select is a command which can be used for querying the database 2) with create command 3) with dml commands DATATYPES SUPPORTED BY SQL: WRT 10G ------------------------------------------------------NUMBER : 38 CHAR: 4000 VARCHAR/VARCHAR2 4000 DATE 9B LONG 2000 LONG RAW 2000 TIMESTAMP DATE+TIME DD-MON-YY HH:MI:SS ----

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.

LARGE OBJECT DATA TYPES LOB -----------------------------------------------BLOB BINARY LOB :4GB ->MOVIES AND PICTURES CLOB CHARECTER LARGE OBJECTS :4GB HUGE CHARECTED DATA NCLOB: NATIONAL CHARECTER LARGE OBJECT: FORIEGN LANGUAGE BFILE: BINARY EXTERNAL LARGE OBJECT: SYSTEM DEPENDENT ALTER SESSION SET NLS_LANGUAGE ='FRENCH';' CREATE: CREATE TABLE COL1 , COL2 , ---, ---, COLN VALUES except numbers everything should be put in quotes INSERT INTO EMPLOY VALUES (1001,'ANIL',9999,'ORACLE','TRAINING'); INSERT INTO (COL1,COL5) VALUES (COL1VAL,COL2VAL); INSERT INTO EMPLOY(EMPNO,REMARKS) VALUES(1004,'YET TO JOIN); this is used to insert into required columns and remaining are set to 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.

THIRD METHOD ALSO CALLED AS & METHOD INSERT INTO VALUES (&COLNAME,'&COL2NAME'---); it asks for the column names INSERT INTO EMPLOY VALUES (&EMPNO,'&ENAME',&SALARY,'&DOJ','&PROGRAM',&A); UPDATE SET COLNAME=; FOR ALL RECORDS UPDATE SET COLNAME = CLASS ASSIGNMENT ----------------------------------WRITE A QUERY TO DISPLAY THE N NUMBER OF RECORDS AT RANDOM FROM EMP WHEREN IS INPUT FROM USER

DELETE: ---------------DELETE [FROM] , -> APPLICABLE ON ALL RECODS DELETE[FROM] WHERE COLNAME =; one row is deleted TCL ------------COMMIT : MAKES DML PERMANENT UPTO PREV COMMIT UPTO PREV DDL UPTO NORMAL DISCONNECT SQL>DISCONNECT/SQL>EXIT ROLLBACK: UPTO PREV COMMIT UPTO PREV DDL UPTO UBNORMAL DISC->POWER FAILURE CLOSE SQL * PLUS SAVEPOINT: basically a bench mark so that it roll back to the specified bench mark or save point specified the moment commit is specified all the save points are removed. when committed or drop table is done save points are removed

DCL =========== GRANT REVOKE USER1 USER2 USER3 ---------- GRANT ----------------- GRANT -------------> USER1.T1-----------> USER1.T1 ALL(I,U,D,S)

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.

USER1 ------------T1 ---->5 RECORDS USER2> SELECT * FROM USER1.T1; ->5 RECORDS USER3 > SELECT * FROM USER1.T1 -> 5 RECORDS ---------------------------------------------------------USER2> DELETE USER1.T1 WHERE EMPNO IN(1001,1002); 2 records deleted user2 sees 3 records user1 sees 5 records user3 sees 5 records othr transactions by the users are made to wait untill user2 is eithr commits or rolls back REVOKE: -------------------USER 1 CAN REVOKE FROM USER 2 USER1 <------------------ USER2 <-------------------USER3 USER 3 AUTOMATICALLY LOOSES THE LINK WHERE AS USER3 CAN REVOKE IT FROM USER 2 BUT WHAT IS NOT POSSIBLE IS USER3 CAN NOT DIRECTLY REVOKE USER1 REVOKE ALL ON FROM

GRANT ALL ON EMP TO [USER] WITH GRANT OPTION grants rights of table emp to the new user

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

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