DROP TABLE EMP; DROP TABLE DEPT; DROP TABLE BONUS; DROP TABLE SALGRADE; DROP TABLE DUMMY; CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2)); INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); CREATE TABLE BONUS (ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER, COMM NUMBER); CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999);
Day 22-10-2011 – to be executed queries Group by clause: Using this we can get values by grouping similar values: 1. select job,avg(sal) from emp group by job; 2. select job avg(sal) from emp where job!=’MANAGER’ group by job; Having clause: Using this we can give the conditions to group values 1. select deptno,avg(sal) from emp group by deptno having count(*) > 3 2. select job, max(sal) from emp having max(sal) >3000 group by job; JOINS: Here we are going to learn how to join two distinct tables: Category 1 : Equi joins In this we compare two columns which are present in both tables 1. select ename,job,dname from emp,dept where emp.deptno=dept.deptno explanation: in order to distinguish two deptno’s here we used table name before the column name with ‘.’ Operator. 2. select dept.deptno, ename, job, dname from emp,dept where emp.deptno=dept.deptno order by dept.deptno category 2: non-equi join in this we join two tables which doesn’t have same column name 1.select e.ename,e.sal,s,grade from emp e,salgrade s where e.sal between s.losal and s.hisal Other joins:-OUTER JOIN Here we use ‘(+)’ operator for outer join using this join we can get the rows which doesn’t satisfies the condition while accessing data from two tables 1. select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno and d.deptno in (30,40) explanation: in EMP table we don’t have record with deptno 40 but we have a record in DEPT table with deptno 40, so in order to get that record we are using outer join with employee deptno. SET Operations:
UNION UNION ALL INTERSECT MINUS 1.select job from emp where deptno=10 UNION select job from emp where deptno=30 Apply all other set operations in the above said way and check the results VIEW’s A view is a virtual table which can prepare by combining several columns from different tables 1.create view d10view as select empno,ename,sal from emp where deptno=10 Explanation : Here we are creating a view with name d10-view it consists only the employee details who are working in deptno 10 By this way we can restrict the users from accessing entire table Now user can get the data from this view by writing the command 1.Select * from d10view
