課堂筆記課件

1、-以scott用戶登錄-1. 查詢每個(gè)部門各多少員工select dname,count(*) from emp,dept where emp.deptno=dept.deptno group by dname;-2. 查詢每個(gè)職位的平均工資select job,avg(sal) from emp group by job;-3. 查詢?cè)滦酱笥赟COTT月薪的員工select * from emp where sal( select sal from emp where ename=SCOTT);-4. 查詢?cè)滦酱笥贏CCOUNTING部門所有員工月薪的員工- 注意KING 是5000,所有人

2、都沒他高,故意弄個(gè)高的再試試 update emp set sal =6000 where empno=7365; select * from empwhere salall( select sal from empwhere deptno=(select deptno from deptwhere dname=ACCOUNTING);-5. 查詢所有員工的名字，以及員工所在的部門名字select ename,dname from emp,dept where emp.deptno= dept.deptno;-6. 查詢所有員工的名字，以及員工的工資級(jí)別select ename,grade,l

3、osal,hisal from emp,salgrade where emp.sal betn losal and hisal;-7. 查詢獎(jiǎng)金為NULL的員工select * from emp where comm is null;-8. 查詢?nèi)肼毴掌诔^5年的員工select * from empwhere to_char(sysdate,yyyy)-to_char(hiredate,yyyy)5;-9. 查詢所有員工的名字，且名字的第一個(gè)字母要大寫，其余字母小寫.select upper(substr(ename,1,1)| lower(substr(ename,2) from emp;

4、-10.查詢每個(gè)部門，每個(gè)職位的平均工資是多少 select deptno,job,avg(sal) from emp group by deptno,joborder by deptno;-11.查詢?nèi)肼毘^10年的員工當(dāng)中，每個(gè)部門的最低月薪-如果只顯示部門,則如下select deptno,min(sal) from empwhere to_char(sysdate,yyyy)-to_char(hiredate,yyyy)10 group by deptno;-如果需要顯示部門名稱,則如下select dname,min(sal) from emp,deptwhere to_char(s

5、ysdate,yyyy)-to_char(hiredate,yyyy)10 and emp.deptno= dept.deptnogroup by dname;-12.查詢平均月薪高于3000的部門沒有,降低下,查詢高于2000的select dname,avg(sal) from emp,dept where emp.deptno= dept.deptno group by dname ing avg(sal)2000;-13.查詢名字中第二個(gè)字母是A的員工select * from emp where ename like _A%;-14.查詢名字中包含M的員工 select * from

6、 emp where ename like %M%;-15.查詢員工都有哪些職位-方法一:使用distinct 關(guān)鍵字select distinct job from emp;-方法二:對(duì)職位分組即到同樣的結(jié)果集select job from emp group by job;-16.把ACCOUNTING部門的員工調(diào)到RESEARCH部門update emp set deptno=( select deptno from dept where dname=RESEARCH)where deptno=( select deptno from dept where dname=ACCOUNTIN

7、G);-17.把RESEARCH部門員工的月薪改為SALES部門最高月薪的1.5倍update emp set sal =(select max(sal)*1.5 from empwhere deptno =(select deptno from deptwhere dname = SALES )where deptno = (select deptno from deptwhere dname = RESEARCH);-查看下結(jié)果select * from emp order by deptno desc;-18.查詢每個(gè)員工的名字，以及員工上司的名字。-自連接select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;-19.查詢?cè)诋?dāng)月倒數(shù)第8天入職的員工(暫時(shí)沒有,先添加一個(gè))- 知識(shí)點(diǎn) last_day 返回指定日期所在月份的最后一天- 先改變當(dāng)前會(huì)話日期格式alter ses set nls_date_format=yyyy-mm-dd; inserto emp values(7364,Lilei,CLERK,7839,2014-1-23,3200,null,10);select * from empwhere last_day(hiredate)-8=hired