Day2 Sql

SQL FUNCTIONS: -------------------------------------------------------ARE BUILT IN FUNCTIONS., THEY CAN BE CALLED FROM A USER DEFINED TBL OR SYSTEM DEFINED VIEW(DUAL). SQL> DESC DUAL -> DESC -> SQL*PLUS COMMAND 1 COL -> DUMMY -> VARCHAR2(1) SQL> SELECT * FROM DUAL; X SYSDATE() ---------------SELECT SYSDATE FROM DUAL; 02-SEP-08 (DEFAULT DATE FORMAT -> DD-MON-YY) SELECT SYSDATE FROM EMP; SELECT DISTINCT SYSDATE FROM EMP; TYPES: ----------NUMBER CHARACTER DATE CONVERSION MISC NUMBER: ---------------ABS() SQRT() MOD() ROUND() FLOOR() CEIL() ---SELECT ABS(-1),SQRT(9),MOD(3,2), ROUND(15.432), ROUND(15.432,1),ROUND(15.432,-1), FLOOR(15,432),CEIL(15.432) FROM DUAL; CHARACTER ------------------LENGTH() CONCAT() SUBSTR() INSTR() LPAD() RPAD() ---LENGTH() -------------SELECT LENGTH('ANIL ANCHAN') FROM DUAL; SELECT ENAME,LENGTH(ENAME) FROM EMP;

CONCAT() --------------SELECT CONCAT(ENAME,JOB) WORKS_AS FROM EMP; OR SELECT ENAMEII' WORKS AS 'IIJOBII' HIS HIRDATE IS 'IIHIREDATE EMPLOYEE_DETAILS FROM EMP; SUBSTR() -------------SELECT ENAME,SUBSTR(ENAME,1,3) "START_1_3" FROM EMP; TRY -VE NUMBERS SELECT ENAME,SUBSTR(ENAME,3) "START_3" FROM EMP; INSTR() ----------SELECT ENAME,INSTR(ENAME,'A') "POS_A" FROM EMP; LPAD() ----------SELECT LPAD(EMPNO,10,'ORACLE') AS EMPID FROM EMP; SELECT LPAD(EMPNO,9,'ORACLE') AS EMPID FROM EMP; SELECT LPAD(EMPNO,11,'ORACLE') AS EMPID FROM EMP; SELECT LPAD(EMPNO,LENGTH(EMPNO)+6,'ORACLE') AS EMPID FROM EMP; RPAD() -----------SELECT RPAD(EMPNO,LENGTH(EMPNO)+6,'ORACLE') AS EMPID FROM EMP; CONVERSION: --------------------TO_CHAR() : DATE TO CHAR FORMAT TO_DATE() : DATE TO SYSDATE FORMAT --TO_CHAR(): DATE TO CHAR FORMAT ----------------01-SEP-08

-> SYSDATE

-> DD-MON-YY

INPUT --------DD DDTH YY YYYY DAY DY Dy day MON MONTH YEAR HH:MI:SS HH24:MI:SS

OUTPUT ------------02 02ND 08 2008 TUESDAY TUE Tue tuesday SEP SEPTEMBER TWO THOUSAND EIGHT 10:50:07 14:55:06

AS

HH:MM:SS

10:09:07

-- MM -> 09(SEP)

WRITE A QUERY TO DISPLAY TODAY'S DAY: -----------------------------------------------------------------SELECT TO_CHAR(SYSDATE,'DAY') " TODAY IS " FROM DUAL; WRITE A QUERY TO DISPLAY THE DAY OF OUR INDEPENDENCE: ---------------------------------------------------------------------------------------------47-15-08 ---> ???? --> 'YY-DD-MM' -> 15-AUG-47 TO_CHAR('15-AUG-47','DAY') SELECT TO_CHAR(TO_DATE('47-15-08','YY-DD-MM'),'DAY') "DAY OF INDEPENDENCE" FROM DUAL; SELECT TO_CHAR(TO_DATE('1947-15-08','YYYY-DD-MM'),'DAY') "DAY OF INDEPENDENCE" FROM DUAL; SELECT TO_CHAR(TO_DATE('15-AUG-1947','DD-MON-YYYY'),'DAY') "DAY OF INDEPENDENCE" FROM DUAL; --> CHECK SELECT TO_CHAR(TO_DATE('15-AUG-47'),'DAY') "DAY OF INDEPENDENCE" FROM DUAL; --> CHECK

DATE FUNCTIONS: INPUT OUTPUT ----------------------------ADD_MONTHS() : SYSDATE,3 -> 02-DEC-08 MONTHS_BETWEEN() : 'D1' - 'D2' -> MONTHS NEXT_DAY() : SYSDATE,'FRI' -> 05-SEP-08 LAST_DAY() : SYSDATE -> 30-SEP-08 SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL; 02-DEC-08 SELECT ROUND(MONTHS_BETWEEN(SYSDATE,'15-AUG-1947')/12) FROM DUAL; 61 SELECT NEXT_DAY(SYSDATE,'FRI') FROM DUAL; 05-SEP-08 SELECT LAST_DAY(SYSDATE) FROM DUAL; 30-SEP-08 NOTE: ---------SELECT LAST_DAY(SYSDATE)+65 FROM DUAL; 04-DEC-08 SELECT LAST_DAY(SYSDATE+65) FROM DUAL; 30-NOV-08 MISC: --------NVL() : NO VALUE FN NVL2() DECODE()

---SELECT SAL,COMM,SAL+COMM AS GROSS_SAL FROM EMP; NVL() : NVL(COLNAME,VALUE) NVL(COMM,0) SELECT SAL,COMM,SAL+NVL(COMM,0) AS GROSS_SAL FROM EMP; NVL2() ---------SELECT SAL,COMM,NVL2(COMM,SAL+COMM,SAL) AS GROSS_SAL FROM EMP; DECODE() IF .. THEN .. [ELSE] .. --------------SELECT JOB,SAL, DECODE(JOB,'MANAGER',SAL+SAL*.10, 'CLERK', SAL+SAL *.05, SAL) AS RAISE_SAL FROM EMP; OR SELECT JOB,SAL, DECODE(JOB,'MANAGER',SAL+SAL*.10, 'CLERK', SAL+SAL *.05) AS RAISE_SAL FROM EMP; --------------------------------------------------------------------------------------------ROWID: -----------IS A UNIQUE ID GENERATED FOR EVERY RECORD STORED IN THE DB. ROWNUM: --------------IS SEQUENCE OF NOS FOR THE RECORDS. SELECT ROWNUM,ROWID FROM EMP; CLASS ASSIGNMENT: -------------------------------1> WRITE A QUERY TO DISPLAY THE ALTERNATE RECORDS FROM EMP TABLE: HINT : USE MOD(),DECODE(),ROWNUM ---> SELECT * FROM EMP WHERE ROWID IN (SELECT DECODE(MOD(ROWNUM,2),1,ROWID) FROM EMP) 2> WRITE A QUERY TO GET THE NTH RECORD FROM EMP TABLE WHEN N IS THE INPUT: HINT : USE ROWID,DECODE SELECT * FROM EMP WHERE ROWID IN(SELECT DECODE(ROWNUM,&N,ROWID) FROM EMP) 3> WRITE A QUERY TO DISPLAY THE LAST FIVE RECORDS FROM EMP TABLE: HINT : USE ORDER BY,ROWNUM ---> SELECT * FROM (SELECT * FROM EMP ORDER BY ROWID DESC) WHERE ROWNUM < 6; ----------------------------------------------------------------------------------

-----------SQL COMMANDS ------------------------DDL : DATA DEFINITION LANGUAGE -> PERMANENT ------CANNOT BE ROLLED BACK. CREATE AUTO COMMIT FOLLOWS. ALTER DROP TRUNCATE RENAME DML : DATA MANIPULATION LANGUAGE -> TEMPORARY -------CAN BE ROLLED BACK INSERT UPDATE DELETE TCL : TRANSACTION CONTROL LANGUAGE -------COMMIT ROLLBACK SAVEPOINT DCL : DATA CONTROL LANGUAGE --------GRANT REVOKE NOTE: --------SELECT : IS A COMMAND WHICH CAN BE USED -------------1> QUERYING THE DB 2> WITH CREATE COMMAND 3> WITH DML COMMANDS DATATYPES: 10g ----------------------NUMBER : 38 CHAR (FIXED) : 4000 VARCHAR / VARCHAR2 (VARIABLE) : 4000 DATE : 9 BYTES : DDMONYY DDMONYYYY DD-MON-YY DD-MON-YYYY LONG (CHAR) : 2000 LONG RAW (BINARY) : 2000 TIMESTAMP : DATE+TIME DD-MON-YY HH:MI:SS --EX: CHAR(10) : 'AN9L' -> 10 VARCHAR2(10) : 'AN9L' -> 4 LOB : LargeOBjects DATATYPES ---------------------------BLOB : BINARY LOB : 4 GB : MOVIES,VIDEOS,PICS CLOB : CHARACTER LOB : 4 GB : HUGE CHAR DATA

NCLOB : NATIONAL CLOB BFILE : BINARY EXT LOB

: : FOREIGN LANG : SYSTEM DEPENDENT

SQL> ALTER SESSION SET NLS_LANGUAGE = 'FRENCH'; CREATE: -------------CREATE TABLE (COL1 , COL2 , ---, ---, COLN CREATE TABLE EMPLOY (EMPNO NUMBER(4), ENAME CHAR(10), SALARY NUMBER(10), DOJ DATE, PROGRAM VARCHAR2(20), REMARKS VARCHAR2(20) ); SQL> / SQL> ED SQL> /

-> CHANGE THE TBL NAME

SQL> DESC EMPLOY INSERT: CREATING A NEW RECORD ---------------------I CANNOT SKIP ANY COLUMNS -INSERT INTO VALUES (COL1VAL,'COL2VAL','COL3VAL',.....'COLNVAL'); BEGIN INSERT INTO EMPLOY VALUES (1001,'ANIL',99999,'01-SEP-08','ORACLE','TRAINING'); INSERT INTO EMPLOY VALUES (1002,'ANCHAN',NULL,NULL,'','YET TO JOIN'); INSERT INTO EMPLOY VALUES (NULL,'',NULL,NULL,'',''); END; / II USED TO INSERT INTO REQD COLS -----------------INSERT INTO (COL1,COL5) VALUES(COL1VAL,COL5VAL); INSERT INTO EMPLOY(EMPNO,REMARKS) VALUES(1004,'YET TO JOIN'); III ---& METHOD ----------------

INSERT INTO VALUES (&COL1NAME,'&COL2NAME',....'&COLNNAME'); ENTER THE VALUE FOR COL1NAME: : SQL> INSERT INTO EMPLOY VALUES (&AAA,'&ENAME',&SALARY,'&DOJ','&PROGRAM',&A); SQL> SET ESCAPE # UPDATE: MODIFY AN EXISTING RECORD -----------------------UPDATE SET COLNAME = ; ALL RECORD UPDATE SET COLNAME = WHERE = ; 1 RECORD UPDATE EMPLOY SET SALARY = 7777 WHERE EMPNO = 1002; UPDATE EMPLOY SET ENAME = 'RAJ', SALARY = 98989, PROGRAM = 'JAVA' WHERE EMPNO = 1001; CLASS ASSIGNMENT: -------------------------------WRITE A QUERY TO DISPLAY THE N NO OF RECORDS AT RANDOM FROM EMP TABLE WHERE N IS INPUT FROM USER. DELETE: ------------DELETE [FROM] ; -> ALL RECORDS DELETE [FROM] WHERE COLNAME = ; -> 1 RECORD ROLLBACK; TCL: -------------COMMIT : MAKES DML PERMANENT UPTO PREV COMMIT UPTO PREV DDL UPTO NORMAL DISCONNECT -> SQL> DISC OR SQL> EXIT ROLLBACK

SAVEPOINT I 10:00 10:30 11:00 12:00 01:00

: UPTO PREV COMMIT UPTO PREV DDL UPTO UBNORMAL DISC-> POWER : BENCH MARK TO ROLLBACK TO THE

LOGIN INSERT UPDATE DELETE ROLL;

EMP 4 2 3

SELECT 14 18 18 15 14

FAILURE,CLOSE SQL*PLUS SAVEPOINT IF SPECIFIED.

II 10:00 10:30 10:45 11:00 12:00 12:30 01:00

LOGIN INSERT COMMIT; UPDATE DELETE COMMIT; ROLL;

III 10:00 10:30 10:40 11:00 11:30 12:00 01:00

LOGIN EMP INSERT SAVEPOINT A; UPDATE SAVEPOINT B; DELETE ROLL;

IV 10:00 10:30 10:40 11:00 11:30 12:00 01:00

SELECT LOGIN EMP 14 INSERT 4 18 SAVEPOINT A; DELETE 2 16 SAVEPOINT B; DELETE 3 13 ROLLBACK TO SAVEPOINT B;

18

V 10:00 10:30 10:40 11:00 11:15 11:30 12:00 01:00

SELECT LOGIN EMP 14 INSERT 4 18 SAVEPOINT A; DELETE 2 16 DROP TABLE T; SAVEPOINT B; DELETE 3 13 ROLLBACK TO SAVEPOINT A;

18

EMP

4

SELECT 14 18

2 3

18 15 15

4

SELECT 14 18

2

18

3

14

15

SUCH SAVEPOINT NEVER ESTABLISHED. DCL: ---------GRANT REVOKE USER1 USER2 USER3 ----------- GRANT ----------- GRANT ------------T1 -----------> USER1.T1 ---------> USER1.T1 ALL(I,U,D,S) WITH GRANT OPTION GRANT ALL ON TO [WITH GRANT OPTION]; GRANT CONNECT,RESOURCE TO ; 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 ROWS DELETED. USER2> SELECT * FROM USER1.T1; -> 3 RECORDS USER3> SELECT * FROM USER1.T1; -> 5 RECORDS USER1> SELECT * FROM T1; -> 5 RECORDS --------------------------------------------------------------------------------------------USER3> UPDATE USER1.T1 SET SAL = 9999 WHERE EMPNO = 1001; <WAIT> --> RESOURCE BUSY -> IMPLICIT LOCK USER1> DELETE T1 WHERE EMPNO = 1001; <WAIT> --> RESOURCE BUSY -> IMPLICIT LOCK --------------------------------------------------------------------------------------------REVOKE: -------------REVOKE ALL ON FROM ; 1> USER1 <------------------ USER2 <------------------------------------------------------------ USER3 2>

USER3 <---------------------------- USER3

3> USER1 <------------------------------------------------------------ USER3 NOT POSSIBLE