1* select ename,to_char(hiredate,'Month, DD YYYY') from empsql>/Tue Dec 05 Hi!!!!!!ENAME TO_CHAR(HIREDATE,'---------- ------------------ALLEN February , 20 1981WARD February , 22 1981MARTIN September, 28 1981BLAKE May , 01 1981CLARK June , 09 1981KING November , 17 1981TURNER September, 08 1981JAMES December , 03 1981MILLER January , 23 1982NAKEN October , 15 1985JOSEPH October , 20 1998LIBIN November , 25 1983ja February , 20 2006JAMES February , 20 1981suresh October , 16 2006HARRIS April , 08 1985SEN October , 10 2006rose February , 10 200518 rows selected./2. 1* select ename, hiredate, to_char(next_day(Add_months(hiredate,6),'Monday'),'day ",the" Ddspth "of" month yyyy') as Review from empsql>/Tue Dec 05 page 1 Hi!!!!!!ENAME HIREDATE REVIEW---------- --------- ------------------------------------------------ALLEN 20-FEB-81 monday ,the Twenty-Fourth of august 1981WARD 22-FEB-81 monday ,the Twenty-Fourth of august 1981MARTIN 28-SEP-81 monday ,the Twenty-Ninth of march 1982BLAKE 01-MAY-81 monday ,the Second of november 1981CLARK 09-JUN-81 monday ,the Fourteenth of december 1981KING 17-NOV-81 monday ,the Twenty-Fourth of may 1982TURNER 08-SEP-81 monday ,the Fifteenth of march 1982JAMES 03-DEC-81 monday ,the Seventh of june 1982MILLER 23-JAN-82 monday ,the Twenty-Sixth of july 1982NAKEN 15-OCT-85 monday ,the Twenty-First of april 1986JOSEPH 20-OCT-98 monday ,the Twenty-Sixth of april 1999LIBIN 25-NOV-83 monday ,the Twenty-Eighth of may 1984ja 20-FEB-06 monday ,the Twenty-First of august 2006JAMES 20-FEB-81 monday ,the Twenty-Fourth of august 1981suresh 16-OCT-06 monday ,the Twenty-Third of april 2007HARRIS 08-APR-85 monday ,the Fourteenth of october 1985SEN 10-OCT-06 monday ,the Sixteenth of april 2007rose 10-FEB-05 monday ,the Fifteenth of august 200518 rows selected.sql>3. 1* select ename ,round( months_between(sysdate,hiredate)) as "monthsworked" from emp order by 2sql>/Tue Dec 05 page 1 Hi!!!!!!ENAME monthsworked---------- ------------suresh 2SEN 2ja 10rose 22JOSEPH 98NAKEN 254HARRIS 260LIBIN 276MILLER 298JAMES 300KING 301MARTIN 302TURNER 303CLARK 306BLAKE 307WARD 309ALLEN 310JAMES 31018 rows selected.5. 1* select ename, lpad(sal,15,'$') from empsql>/Tue Dec 05 Hi!!!!!!ENAME LPAD(SAL,15,'$'---------- ---------------ALLEN $$$$$$$$$$$5000WARD $$$$$$$$$$$7500MARTIN $$$$$$$$$$$7500BLAKE $$$$$$$$$$$8000CLARK $$$$$$$$$$$4500KING $$$$$$$$$$$4000TURNER $$$$$$$$$$$7500JAMES $$$$$$$$$$$7500MILLER $$$$$$$$$$$8000NAKEN $$$$$$$$$$$8000JOSEPH $$$$$$$$$$$4938LIBIN $$$$$$$$$$$1789ja $$$$$$$$$$$5000JAMES $$$$$$$$$$$2000suresh $$$$$$$$$$$5000HARRIS $$$$$$$$$$$6000SEN $$$$$$$$$$12000rose $$$$$$$$$$$150018 rows selected.6. 1* select ename,hiredate,to_char(hiredate,'d') as DAY from emp order by 3sql>/Tue Dec 05 Hi!!!!!!ENAME HIREDATE D---------- --------- -WARD 22-FEB-81 1MARTIN 28-SEP-81 2suresh 16-OCT-06 2ja 20-FEB-06 2HARRIS 08-APR-85 2CLARK 09-JUN-81 3KING 17-NOV-81 3TURNER 08-SEP-81 3NAKEN 15-OCT-85 3SEN 10-OCT-06 3JOSEPH 20-OCT-98 3JAMES 03-DEC-81 5rose 10-FEB-05 5ALLEN 20-FEB-81 6LIBIN 25-NOV-83 6JAMES 20-FEB-81 6BLAKE 01-MAY-81 6MILLER 23-JAN-82 718 rows selected.7. 1 select ename,job,sal, case 2 job when 'MANAGER' then 'B' 3 when 'PRESIDENT' THEN 'A' 4 when 'ANALYST' then 'C' 5 else 'D' 6* END as grade FROM EMPsql>/Tue Dec 05 Hi!!!!!!ENAME JOB SAL G---------- --------- ---------- -ALLEN SALESMAN 5000 DWARD SALESMAN 7500 DMARTIN SALESMAN 7500 DBLAKE MANAGER 8000 BCLARK MANAGER 4500 BKING PRESIDENT 4000 ATURNER SALESMAN 7500 DJAMES CLERK 7500 DMILLER MANAGER 8000 BNAKEN PGMR 8000 DJOSEPH SALESMAN 4938 DLIBIN CLERK 1789 Dja sad 5000 DJAMES SALESMAN 2000 Dsuresh MANAGER 5000 BHARRIS ANALYST 6000 CSEN SE 12000 Drose salesman 1500 D18 rows selected.8.sql>SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),'Friday') FROM DUAL;Tue Dec 05 Hi!!!!!!NEXT_DAY(---------29-DEC-069. 1* select ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptnosql>/Tue Dec 05 Hi!!!!!!ENAME DEPTNO DNAME---------- ---------- --------------NAKEN 40 QUALITYCLARK 10 ACCOUNTINGKING 10 ACCOUNTINGMILLER 10 ACCOUNTINGALLEN 20 RESEARCHja 20 RESEARCHsuresh 20 RESEARCHHARRIS 20 RESEARCHSEN 20 RESEARCHWARD 30 SALESMARTIN 30 SALESBLAKE 30 SALESTURNER 30 SALESJAMES 30 SALESJOSEPH 30 SALESJAMES 30 SALESrose 30 SALES17 rows selected.11. 1* select ename,dname,loc from emp e, dept d where d.deptno=e.deptno and e.comm is not nullsql>/Tue Dec 05 page 1 Hi!!!!!!ENAME DNAME LOC---------- -------------- -------------NAKEN QUALITY CHENNAICLARK ACCOUNTING CHENNAIKING ACCOUNTING CHENNAIMILLER ACCOUNTING CHENNAIALLEN RESEARCH BANGALOREja RESEARCH BANGALOREsuresh RESEARCH BANGALOREHARRIS RESEARCH BANGALORESEN RESEARCH BANGALOREWARD SALES BANGALOREMARTIN SALES BANGALOREBLAKE SALES BANGALORETURNER SALES BANGALOREJAMES SALES BANGALOREJAMES SALES BANGALORErose SALES BANGALORE16 rows selected.12.sql>select ename, dname from emp e, dept d where d.deptno=e.deptno and e.ename like '%A%';Tue Dec 05 p Hi!!!!!!ENAME DNAME---------- --------------NAKEN QUALITYCLARK ACCOUNTINGALLEN RESEARCHHARRIS RESEARCHWARD SALESMARTIN SALESBLAKE SALESJAMES SALESJAMES SALES9 rows selected.13. 1* SELECT * FROM DEPTsql>SELECT ENAME, DNAME, d.deptno from emp e,dept d where e.deptno=d.deptno and loc='CHENNAI';Tue Dec 05 page 1 Hi!!!!!!ENAME DNAME DEPTNO---------- -------------- ----------NAKEN QUALITY 40CLARK ACCOUNTING 10KING ACCOUNTING 10MILLER ACCOUNTING 1014.sql>SELECT e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;Tue Dec 05 Hi!!!!!! EMPNO ENAME EMPNO ENAME---------- ---------- ---------- ---------- 9687 rose 7499 ALLEN 9687 rose 7499 ja 7499 ALLEN 7698 BLAKE 7521 WARD 7698 BLAKE 7844 TURNER 7698 BLAKE 7654 MARTIN 7698 BLAKE 7489 JAMES 7698 BLAKE 7499 ja 7698 BLAKE 7900 JAMES 7698 BLAKE 7934 MILLER 7782 CLARK 7474 LIBIN 7782 CLARK 7679 NAKEN 7782 CLARK 7698 BLAKE 7839 KING 7782 CLARK 7839 KING 7869 JOSEPH 7900 JAMES15 rows selected.15.sql>@ C:\SUBHASAKTHI\E2Q13.SQL;Tue Dec 05 Hi!!!!!!ENAME----------ALLENWARDMARTINBLAKECLARKKINGTURNERJAMESMILLERNAKENJOSEPHLIBINjaJAMESsureshHARRISSENrose18 rows selected.sql>SAVE E2Q15Created file E2Q15.sqlsql>EDIT E2Q1516.17.sql>DESC SALGRADE; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ GRADE NUMBER LOSAL NUMBER HISAL NUMBER ENAME VARCHAR2(25) JOB VARCHAR2(25) SALARY NUMBER(5) DNAME VARCHAR2(25)sql>SELECT ENAME,JOB,SALARY,DNAME,GRADE FROM SALGRADE;Tue Dec 05 page 1 Hi!!!!!!ENAME JOB SALARY DNAME GRADE------------------------- ------------------------- ---------- ------------------------- ---------- 1 2 3 4 5ALLEN SALESMAN 5000 RESEARCH 1MARTIN SALESMAN 5000 SALES 27 rows selected.18. 1* SELECT * FROM EMP WHERE HIREDATE >(SELECT HIREDATE FROM EMP WHERE ENAME='BLAKE')sql>/Tue Dec 05 page Hi!!!!!! EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7654 MARTIN SALESMAN 7698 28-SEP-81 7500 345 30 7782 CLARK MANAGER 7839 09-JUN-81 4500 345 10 7839 KING PRESIDENT 17-NOV-81 4000 345 10 7844 TURNER SALESMAN 7698 08-SEP-81 7500 345 30 7900 JAMES CLERK 7698 03-DEC-81 7500 345 30 7934 MILLER MANAGER 7782 23-JAN-82 8000 345 10 7679 NAKEN PGMR 7782 15-OCT-85 8000 455 40 7869 JOSEPH SALESMAN 7900 20-OCT-98 4938 30 7474 LIBIN CLERK 7782 25-NOV-83 1789 526 75 7499 ja sad 7698 20-FEB-06 5000 45 20 7660 suresh MANAGER 7723 16-OCT-06 5000 500 20 8888 HARRIS ANALYST 7566 08-APR-85 6000 300 20 7042 SEN SE 7466 10-OCT-06 12000 500 20 9687 rose salesman 7499 10-FEB-05 1500 111 3014 rows selected.19.sql>select e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.hiredateselect d.deptno,d.dname,ename,nvl(ename,'no employee') as "no EMP" from emp e,dept d where e.deptno(+)=d.deptno;Tue Dec 05 page 1 Hi!!!!!! DEPTNO DNAME ENAME no EMP---------- -------------- ---------- ----------- 40 QUALITY NAKEN NAKEN 12 sdf no employee 10 ACCOUNTING CLARK CLARK 10 ACCOUNTING KING KING 10 ACCOUNTING MILLER MILLER 20 RESEARCH ALLEN ALLEN 20 RESEARCH ja ja 20 RESEARCH suresh suresh 20 RESEARCH HARRIS HARRIS 20 RESEARCH SEN SEN 30 SALES WARD WARD 30 SALES MARTIN MARTIN 30 SALES BLAKE BLAKE 30 SALES TURNER TURNER 30 SALES JAMES JAMES 30 SALES JOSEPH JOSEPH 30 SALES JAMES JAMES 30 SALES rose rose18 rows selected.21.paper22 1* select round(max(sal)) as MAXIMUM,round(min(sal)) as MINIMUM,round(avg(sal)) as AVERAGE,round(sum(sal))as TOTAL from empql>/ue Dec 05 page 1 Hi!!!!!! MAXIMUM MINIMUM AVERAGE TOTAL--------- ---------- ---------- ---------- 12000 1500 5874 10572723. 1* select job, round(max(sal)) as MAXIMUM,round(min(sal)) as MINIMUM,round(avg(sal)) as AVERAGE,round(sum(sal))as TOTAL from emp group by jobsql>/Tue Dec 05 page 1 Hi!!!!!!JOB MAXIMUM MINIMUM AVERAGE TOTAL--------- ---------- ---------- ---------- ----------ANALYST 6000 6000 6000 6000CLERK 7500 1789 4645 9289MANAGER 8000 4500 6375 25500PGMR 8000 8000 8000 8000PRESIDENT 4000 4000 4000 4000SALESMAN 7500 2000 5740 34438SE 12000 12000 12000 12000sad 5000 5000 5000 5000salesman 1500 1500 1500 150024. 1* select dname,count(dname) from emp e,dept d where e.deptno=d.deptno group by dnamesql>/Tue Dec 05 Hi!!!!!!DNAME COUNT(DNAME)-------------- ------------ACCOUNTING 3QUALITY 1RESEARCH 5SALES 825.sql>select count(*) from emp where job='MANAGER';Tue Dec 05 page 1 Hi!!!!!! COUNT(*)---------- 426.sql>select max(sal)-min(sal) as DIFFRENCE from emp;Tue Dec 05 Hi!!!!!! DIFFRENCE---------- 1050027. 1* select count(*) ,mgr from emp where mgr is not null and sal>(select min(sal) from emp ) group by mgrsql>/Tue Dec 05 page 1 Hi!!!!!! COUNT(*) MGR---------- ---------- 1 7466 1 7566 7 7698 1 7723 3 7782 2 7839 1 79007 rows selected.28. 1* select count(ename) , round( avg(sal),2),loc from emp e , dept d where e.deptno=d.deptno group by locsql>/Tue Dec 05 page 1 Hi!!!!!!COUNT(ENAME) ROUND(AVG(SAL),2) LOC------------ ----------------- ------------- 13 6110.62 BANGALORE 4 6125 CHENNAI29. 1* select count(*) , deptno from emp where to_char(hiredate,'YYYY') between 1980 and 1983 and deptno in (10,20,30)group by deptnosql>/Tue Dec 05 page 1 Hi!!!!!! COUNT(*) DEPTNO---------- ---------- 3 10 1 20 6 3030. 1* select nvl(avg(comm),0) from empsql>/Tue Dec 05 NVL(AVG(COMM),0)---------------- 355.867647