Test Essay

626 WordsJul 6, 20113 Pages
1 )))) SQL> select avg(sal) from emp group by job; AVG(SAL) ---------- 1037.5 1400 5000 2758.33333 3000 2 )))) SQL> select deptno,job,avg(sal) from emp group by deptno,job; DEPTNO JOB AVG(SAL) ---------- --------- ---------- 20 CLERK 950 30 SALESMAN 1400 20 MANAGER 2975 30 CLERK 950 10 PRESIDENT 5000 30 MANAGER 2850 10 CLERK 1300 10 MANAGER 2450 20 ANALYST 3000 3 )))) SQL> select avg(sal),deptno,count(*) from emp group by deptno having count(*)>3; AVG(SAL) DEPTNO COUNT(*) ---------- ---------- ---------- 1566.66667 30 6 2175 20 5 4 )))) SQL> select sal from emp group by sal having count(sal)>1; SAL ---------- 1250 3000 SQL> select empno from emp group by empno having count(empno)>1; no rows selected 5 )))) SQL> select deptno from emp where job='ANALYST' group by deptno having count(job)>=2; DEPTNO ---------- 20 6 )))) SQL> select dname,length(dname)-length(replace(dname,'S')) from dept; DNAME LENGTH(DNAME)-LENGTH(REPLACE(DNAME,'S')) -------------- ---------------------------------------- ACCOUNTING 0 RESEARCH 1 SALES 2 OPERATIONS 1 7 )))) SQL> select rpad(ename,7)||lpad(job,9) as employee_and_job from emp; EMPLOYEE_AND_JOB ---------------- SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES

More about Test Essay

Open Document