chapter 2 SELECT * FROM TAB; select * from tab; select * from tab; SELECT * FROM emp; SELECT empno,ename,sal,deptno

FROM emp; SELECT ename Employee, deptno Department_No, SAL "Employee Compensation" FROM emp; SELECT empno,ename,deptno FROM emp WHERE deptno = 30; DD-MON-YY is Oracle's default date format. SELECT empno, ename, deptno, sal FROM emp WHERE sal BETWEEN 3000 and 5000; SELECT empno, ename, comm FROM emp WHERE comm IS NULL; The ' = ' operator cannot be used here. SELECT ename,comm FROM emp WHERE NVL(comm,0)= 0; SELECT empno, ename, deptno FROM emp WHERE deptno IN (20,30,40); The ' = ' operator can take only one value. To provide multiple values, in the place of ' = ', IN is used. IN takes more than one value as its input. Hence it is called a list operator. SELECT empno,ename,sal FROM emp WHERE sal > ANY (1000,2850,3000); Thus the selected salaries would be greater than 1000. SELECT empno,ename,sal FROM emp WHERE sal > ALL (800,2850,3000); Thus the selected salaries would be greater than 3000. =ANY is equivalent to IN operator. !=ALL is equivalent to NOT IN.

SELECT empno, ename FROM emp WHERE ename like'S%'; SELECT empno, ename FROM emp WHERE ename like 'AL_ _N'; SELECT ename FROM emp WHERE SOUNDEX(ename) = SOUNDEX('skot'); SOUNDEX() is a phonetic function. SELECT empno, ename, sal, sal*.1 PF FROM emp; PF is a column alias. SELECT empno, ename, hiredate, sal FROM emp ORDER BY ename; ORDER BY ename sorts the rows in the alphabetical order. chapter 3 SELECT empno,ename,mgr,deptno FROM emp ORDER BY empno L 2 INPUT WHERE deptno =10 LIST L 2 - lists the second line of the query. FROM emp is the current line. INPUT WHERE deptno = 10 inserts a new line after the current line. LIST lists the modified query. DEL - deletes the current line. Commands LIST (L) R CHANGE (C) APPEND (A) DEL INPUT (I) Definition Lists the current / specified line Displays & executes the latest command Replaces the old text with the new text Adds text at the end of the current line Deletes the current / specified line Inserts a new line after the current / specified line

SPOOL c:\student\Testfile.txt SPOOL OFF Query results and the feedback after the query execution can be written to files for later reference,to produce reports etc. This is called as Spooling.

Spooling can be done using the SPOOL command. SAVE c:\student\QueryFile creates a file SQL *PLUS command file, QueryFile with an extension .SQL and stores it in the specified directory. GET c:\student\QueryFile loads the contents of the specified file into SQL buffer. They are also termed as SET command variables. There are 68 system variables. Changing the values of the system variables, change the way SQL *Plus behaves,when executing the commands and displaying the results. SHOW UNDERLINE SHOW ALL SET UNDERLINE '=' SET FEEDBACK OFF COLSEP -ECHO {OFF / ON} - EDITFILE - FEEDBACK {OFF / ON} HEADING {OFF / ON} - LINESIZE -SERVEROUTPUT {OFF / ON} -SQLPROMPT SQLTERMINATOR - TIME {OFF / ON} chapter 4 A join is a query that combines rows from two or more tables, views, etc. (Eg) emp.deptno = dept.deptno Equi Join Non-Equi Join Outer Join Self Join SELECT empno, ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno; SELECT ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND sal > 3000; An Equi join with a non-equal condition is termed as Non-Equi Join. SELECT empno, ename, dept.deptno, dname FROM emp, dept WHERE emp.deptno(+) = dept.deptno; SELECT X.ename,Y.ename FROM X,Y WHERE X.mgr = Y.empno; SELECT empno, ename, deptno FROM emp UNION SELECT empno, ename, deptno FROM un_emp;

UNION selects distinct rows from either of the queries. Any records that are identical in both the tables are returned only once. i.e., it avoids duplication of rows. SELECT empno, ename, deptno FROM emp UNION ALL SELECT empno, ename, deptno FROM un_emp; UNION ALL selects all rows from emp and un_emp tables. Duplicate rows are also included. All the rows selected by either query including duplicates are displayed. SELECT empno, ename, deptno FROM emp INTERSECT SELECT empno, ename, deptno FROM un_emp; INTERSECT selects the rows present in common within the emp and un_emp tables. Thus the operator selects only those rows returned by both the queries. SELECT empno, ename, deptno FROM emp MINUS SELECT empno, ename, deptno FROM un_emp; SELECT empno, ename, deptno FROM un_emp MINUS SELECT empno, ename, deptno FROM emp; SELECT ... FROM emp MINUS SELECT ... FROM un_emp displays the rows of emp which are not present in un_emp. SELECT ... FROM un_emp MINUS SELECT ... FROM emp displays the rows of un_emp which are not present in emp. chapter 5 SELECT ROUND(17.678,2) R2, ROUND(17.678,1) R1, ROUND(17.678,0) R0, ROUND(17.678,-1) RA, ROUND(179.678,-2) RB FROM DUAL; SELECT TRUNC(17.678,2) T2, TRUNC(17.678,1) T1, TRUNC(17.678,0) T0, TRUNC(17.678,-1) TA, TRUNC(179.678,-2) FROM DUAL;

TB

SELECT sal, comm, GREATEST(sal, comm) G1, GREATEST(sal, NVL(comm,0)) G2, LEAST(sal,NVL(comm,0))Least FROM emp; SELECT SQRT(25) sq, LOG(10,2) lg,

POWER(4,3) pw, MOD(10,3) mod FROM DUAL; SELECT ename, VSIZE (ename) FROM emp; VSIZE(ename) tells how many bytes ORACLE needs to store the value of ename in the database. SELECT ename,INITCAP(ename)IC,UPPER(ename) UP, LOWER(ename)LW FROM emp; SELECT LPAD('Suresh',10,'*')lpad, RPAD('Suresh',10,'*') rpad, LTRIM('*****Suresh*****','*') lt, RTRIM('*****Suresh*****','*')rt FROM DUAL; SELECT REPLACE('JACK and JUE','J','BL') "After Repl" FROM DUAL; SELECT 'ABCXYZ123' BEFORE, TRANSLATE ('ABCXYZ123','ABCDEF12345','ghijkl') AFTER FROM DUAL; SELECT ename,deptno, DECODE(deptno,10,'SALES',20,'ACC','others') dname FROM emp; SELECT ename , LENGTH(ename)len, SUBSTR(ename,2,3)substr, INSTR(ename,'S')instr from emp WHERE deptno =20; SELECT CONCAT('WELCOME','HOME')"Using Fn", 'WELCOME' 'HOME' "Using op" FROM dual; SELECT empno, hiredate, TO_CHAR(hiredate,'MM/DD/YY HH:MI:SS')Date_Time FROM emp; SELECT hiredate, hiredate + 2 after, hiredate - 2 before,SYSDATE - hiredate FROM emp WHERE deptno=20; SELECT hiredate hdt, ADD_MONTHS(hiredate,3) ad_m, ADD_MONTHS(hiredate,-3) sub_m FROM emp WHERE deptno = 30; SELECT hiredate,MONTHS_BETWEEN(sysdate,hiredate)Mbet FROM emp WHERE deptno =10 ; SELECT hiredate, LAST_DAY(hiredate), NEXT_DAY(hiredate,'SUN') FROM emp WHERE deptno = 10;

SELECT NEW_TIME(SYSDATE,'GMT','AST') NewDate, TO_CHAR(NEW_TIME(SYSDATE,'GMT','AST'),'hh:mi:ss PM') NewTime FROM DUAL; chapter 6 SELECT deptno, SUM(sal) FROM emp GROUP BY deptno; SELECT job, deptno, FROM emp WHERE deptno !=10 GROUP BY job, deptno HAVING SUM(sal) > 3000 ORDER BY deptno, job; SUM(sal)

SELECT empno, ename, sal, deptno FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'JONES'); SELECT empno, ename, job, sal FROM emp WHERE job IN ( SELECT job FROM emp WHERE deptno = 20); The IN list operator in the WHERE clause of the main query handles multiple values. chapter 7 CHAR VARCHAR2 NCHAR NVARCHAR2 DDL DML DCL CREATE TABLE CREATE INDEX ALTER TABLE GRANT etc., SELECT INSERT UPDATE DELETE COMMIT ROLLBACK SAVEPOINT etc. CREATE TABLE account_code( acc_code NUMBER(15) PRIMARY KEY, description VARCHAR2(30) NOT NULL, br_code VARCHAR2(3), section_Code VARCHAR2(3),

ref_date DATE, detailed_remarks LONG); CREATE INDEX br_index ON account_code(br_code); CREATE UNIQUE INDEX br_sec ON account_code(br_code,section_code); ALTER TABLE account_code ADD city VARCHAR2(6); ALTER TABLE account_code MODIFY br_code NUMBER(5) NOT NULL ADD new_col number(4); CREATE TABLE empback AS SELECT * FROM emp; CREATE TABLE new_emp(empno, ename, deptno) AS SELECT empno, ename, deptno FROM emp WHERE empno IS NULL; No rows satisfy the condition empno IS NULL. Thus only the structure is copied on to new_emp. Data is not copied. DROP INDEX br_sec; DROP TABLE account_code; TRUNCATE TABLE emp; TRUNCATE command is used to quickly remove all rows from a table. RENAME emp1 to emp3 changes of the name of emp1 to emp3. The new name emp3 must not already be used by another object. INSERT INTO dept VALUES(50, 'SECURITY', 'HO'); UPDATE dept SET dname = 'PRODUCTS' WHERE deptno = 60; UPDATE emp SET sal = sal+nvl(comm,0) WHERE empno = 7788; DELETE FROM emp WHERE empno =7900; ROLLBACK; ROLLBACK undoes the effect of the DML commands. COMMIT; COMMIT makes the changes permanent in the database. chapter 8 A view is a customized presentation of the data from one or more tables. CREATE VIEW dept20

AS SELECT * FROM emp WHERE deptno = 20; CREATE OR REPLACE VIEW dept20A (employee_number,empname,department,salary) AS SELECT empno,ename,deptno,sal FROM emp WHERE deptno =20; CREATE VIEW dept20c AS SELECT * FROM emp WHERE deptno = 20 WITH CHECK OPTION; INSERT INTO dept20c (empno,deptno) VALUES (7800,10); UPDATE dept20c SET deptno = 30; The WITH CHECK OPTION clause restricts the users from updating or inserting data into the deptno column that may violate the view's query. CREATE OR REPLACE view EMPVIEW AS SELECT empno,ename,job FROM emp WITH READ ONLY; WITH READ ONLY clause specifies that we will only be able to select records from the view. Thus modifications to the base table through the view are prevented. CREATE FORCE VIEW dependent_details AS SELECT empno,dependent_name,dep_dob FROM dependents WHERE dep_dob > '31-dec-99'; CREATE FORCE VIEW dependent_details creates a view without any table named dependent_details. The view creation is enabled by FORCE option. The view will be created with error. ALTER VIEW dependent_details COMPILE; chapter 9 Column level Constraints CREATE TABLE account_master( account_code VARCHAR2(10) CONSTRAINT acode_pk PRIMARY KEY, description VARCHAR2(30) NOT NULL, op_balance NUMBER(12,2) CONSTRAINT not_null_constraint CHECK(op_balance IS NOT NULL) CONSTRAINT zero_opbal CHECK(op_balance > 0), br_code NUMBER(3)CONSTRAINT brcode_uk UNIQUE, company_name VARCHAR2(10) DEFAULT 'ABC CO');

Table Level constraints. CREATE TABLE account_master1( account_code VARCHAR2(10), description VARCHAR2(30) CONSTRAINT des_notnull NOT NULL, op_balance NUMBER(12,2) NOT NULL CONSTRAINT zero_opbal1 CHECK(op_balance > 0), cl_balance NUMBER(12,2), br_code NUMBER(3), company_name VARCHAR2(10) DEFAULT 'ABC Co', CONSTRAINT br_accd_pk PRIMARY KEY(account_code, br_code), CONSTRAINT Obal_Cbal_chk CHECK(op_balance > cl_balance)); ALTER TABLE emp1 ADD CONSTRAINT ename_uk UNIQUE(ename); ALTER TABLE emp1 ADD CONSTRAINT ename_uk UNIQUE(ename) DISABLE; ALTER TABLE emp1 ENABLE CONSTRAINT ename_uk ; ALTER TABLE emp1 DISABLE CONSTRAINT ename_uk; chapter 10 chapter 11 A PL/SQL program is written in units called blocks. A PL/SQL program can include the DML and DCL statements. But direct use of DDL statements is illegal. DECLARE -- Salary increment Program v_ename VARCHAR2(10); v_sal NUMBER(7,2); BEGIN SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno = 7369; v_sal := v_sal +100; UPDATE emp set sal= v_sal WHERE empno= 7369; END; DECLARE v_empno emp.empno%TYPE; v_sal emp.sal%TYPE; v_bonus v_sal%TYPE; BEGIN SELECT empno, sal INTO v_empno, v_sal FROM emp WHERE empno = &eno; v_bonus := v_sal * .10; DBMS_OUTPUT.PUT_LINE( 'Bonus Amount= Rs.' INSERT INTO BONUS(empno,bonus)

v_bonus);

VALUES(v_empno,v_bonus); END; eno is called as substitution variable, which is identified by the preceding character &. It is used to get input from the user, during the execution of the program. chapter 12 IF-END IF statement IF-ELSE-END IF statement Nested IF-ELSE-IF statement IF-ELSIF-END IF statement.

DECLARE v_empno NUMBER(4); v_sal NUMBER(7,2); BEGIN SELECT empno, sal INTO v_empno, v_sal FROM emp WHERE empno =7839; IF v_sal > 0 THEN v_sal := v_sal +100; ELSE IF v_sal = 0 THEN v_sal := v_sal + 200; ELSE v_sal := v_sal + 1000; END IF; END IF; UPDATE emp SET sal = v_sal WHERE empno = v_empno; END; LOOP FOR-LOOP. WHILE-LOOP BEGIN DBMS_OUTPUT.PUT_LINE('Even Numbers between 1 and 15..' ); FOR ctr IN 1..15 LOOP IF MOD(ctr,2)= 0 THEN DBMS_OUTPUT.PUT_LINE(ctr); END IF; END LOOP; END; CREATE TABLE TEMP( name VARCHAR2(15), mgrno NUMBER(4), Salary NUMBER(7,2)); DECLARE emprec emp%ROWTYPE; starting_empno CONSTANT NUMBER := 7902;

BEGIN SELECT * INTO emprec FROM emp WHERE empno = starting_empno; WHILE emprec.sal < 4000 LOOP INSERT INTO temp VALUES(emprec.ename, emprec.mgr, emprec.sal); SELECT * INTO emprec FROM emp WHERE empno = emprec.mgr; END LOOP; END; DECLARE v_comm emp.comm%TYPE; v_job emp.job%TYPE; BEGIN SELECT comm,job INTO v_comm,v_job FROM emp WHERE empno = 7449; IF v_job = 'SALESMAN' THEN v_comm := v_comm * 2; GOTO upd_comm; ELSIF v_job = 'CLERK' THEN v_comm := v_comm * 3; GOTO upd_comm; ELSE GOTO no_upd; END IF; <<NO_UPD>> DBMS_OUTPUT.PUT_LINE('Comm was not updated'); GOTO end_pro; <<UPD_COMM>> UPDATE emp SET comm = v_comm WHERE empno=7499; DBMS_OUTPUT.PUT_LINE('Comm was updated'); <<END_PRO>> DBMS_OUTPUT.PUT_LINE('End of the program'); END; chapter 13 In order to process SQL statements and store processing information Oracle allocates memory. This memory (work) area is termed as Cursor. DECLARE CURSOR emp_cur IS SELECT empno,sal FROM emp; v_sal emp.sal%TYPE; v_empno emp.empno%TYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_empno,v_sal; EXIT when emp_cur%NOTFOUND;

IF v_sal < 1000 THEN v_sal := v_sal +100.33; END IF; UPDATE emp SET sal = v_sal WHERE empno = v_empno; END LOOP; CLOSE emp_cur; END; Open the cursor. Fetch from the cursor. Close the cursor. chapter 14

But the errors which occur during the program execution (runtime) such as division by zero , invalid values etc. may cause the abnormal termination of the program. These run-time errors are called exceptions. To avoid the abnormal termination of the program exceptions need to be handled. User-defined or External Exceptions. Pre-defined or Internal Exceptions User-defined exceptions are declared by the programmer which are specific to a particular program. Pre-defined exceptions are common SQL errors which are automatically recognized by Oracle. e.g. Dividing by Zero DECLARE v_empno emp.empno%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%type; CURSOR emp_cur IS SELECT empno, sal, comm FROM emp; zero_sal EXCEPTION; null_comm EXCEPTION; BEGIN OPEN emp_cur; LOOP BEGIN BEGIN FETCH emp_cur INTO v_empno, v_sal,v_comm; EXIT WHEN emp_cur%NOTFOUND; IF v_sal= 0 THEN RAISE zero_sal; END IF; EXCEPTION WHEN zero_sal THEN INSERT INTO error_info VALUES(TO_CHAR(v_empno) 'zero salary'); END; IF v_comm IS NULL THEN RAISE null_comm; END IF; EXCEPTION

WHEN null_comm THEN INSERT INTO error_info VALUES(TO_CHAR(v_empno) END; END LOOP; CLOSE emp_cur; END;

'

null comm');

Whenever our program includes a SELECT statement, we need to include the NO_DATA_FOUND exception handler. The SELECT INTO statement also can cause another predefined exception TOO_MANY_ROWS. DECLARE BEGIN UPDATE dept SET dname = 'Quality' WHERE deptno = 99; IF SQL%NOTFOUND THEN INSERT INTO error_info VALUES( ' Dept update failed'); END IF; END; DECLARE v_err_num NUMBER; v_err_message VARCHAR2(100); BEGIN UPDATE dept set deptno = 11111111111; EXCEPTION WHEN OTHERS THEN v_err_num := SQLCODE; v_err_message := SUBSTR(SQLERRM,1,100); INSERT INTO error_info VALUES(v_err_num ' ' END; v_err_message);

DECLARE MISSING_NOT_NULL EXCEPTION; PRAGMA EXCEPTION_INIT(MISSING_NOT_NULL, -1400); BEGIN INSERT INTO emp (empno,ename,job,sal) VALUES(001,'HARSHA','MANAGER',5000); EXCEPTION WHEN MISSING_NOT_NULL THEN INSERT INTO error_info VALUES('Value not provided for a NOT NULL column'); END; DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT(my_exception, -20002); DECLARE v_dname VARCHAR2(30); CURSOR emp_cur IS SELECT deptno FROM emp; BEGIN FOR emprec in emp_cur LOOP BEGIN SELECT dname INTO v_dname FROM dept

WHERE deptno = emprec.deptno; DBMS_OUTPUT.PUT_LINE('dname is ' v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'invalid dept found ..terminating the program' ); END; END LOOP; END; RAISE_APPLICATION_ERROR terminates the program issuing the error message provided. Re-RAISE DECLARE Null_dept EXCEPTION; v_deptno NUMBER; BEGIN BEGIN SELECT deptno INTO v_deptno FROM emp WHERE empno=7566; IF v_deptno IS NULL THEN RAISE Null_dept; END IF; EXCEPTION WHEN Null_dept THEN INSERT INTO error_info VALUES('Null dept found'); RAISE; END; EXCEPTION WHEN Null_dept THEN UPDATE emp SET deptno =10 WHERE empno=7566; END; chapter 15 Illustrates the FOR UPDATE and WHERE CURRENT OF clauses. Deletes the records of those employees whose salary is 0 or null. Program Code DECLARE CURSOR del_empcur IS SELECT empno,sal FROM emp WHERE NVL(sal,0) =0 FOR UPDATE; BEGIN FOR emprec IN del_empcur LOOP DELETE FROM emp WHERE CURRENT OF del_empcur; END LOOP; COMMIT; END; chapter 16 chapter 17