員工部門表綜合查詢題

1、(1) 查詢 20 部門的所有員工信息。(2) 查詢所有工種為CLERK勺員工的員工號、員工名和部門號。(3) 查詢獎金(COM)高于工資(SAL的員工信息。( 4) 查詢獎金高于工資的 20%的員工信息。(5) 查詢10號部門中工種為MANAGEIR20部門中工種為CLERK勺員工的信息。(6) 查詢所有工種不是 MANAGEIR CLERI，且工資大于或等于2000的員工的詳 細信息。( 7) 查詢有獎金的員工的不同工種。( 8) 查詢所有員工工資與獎金的和。(9) 查詢沒有獎金或獎金低于 100的員工信息。( 10) 查詢各月倒數(shù)第 3 天入職的員工信息。( 11) 查詢工齡大于或等于

2、25 年的員工信息。( 12) 查詢員工信息，要求以首字母大寫的方式顯示所有員工的姓名。(13) 查詢員工名正好為 6個字符的員工的信息。(14) 查詢員工名字中不包含字母“S”的員工。(15) 查詢員工姓名的第二字母為“ M”的員工信息。( 16) 查詢所有員工姓名的前三個字符。(17)查詢所有員工的姓名，如果包含字母“ S,則用“ s”替換。( 18) 查詢員工的姓名和入職日期，并按入職日期從先到后進行排序。( 19) 顯示所有員工的姓名、工種、工資和獎金，按工種降序排序，若工種相同 則按工資升序排序。( 20) 顯示所有員工的姓名、入職的年份和月份，按入職日期所在的月份排序， 若月份相同

3、則按入職的年份排序。(21) 查詢在 2 月份入職的所有員工信息。(22)查詢所有員工入職以來的工作期限，用“ XX年XX月XX日”的形式表示。( 23.1) 查詢至少有一個員工的部門信息。(23.2) 查詢至少有兩個員工的部門信息。(24)查詢工資比SMITH員工工資高的所有員工信息。( 25) 查詢所有員工的姓名及其直接上級的姓名。( 26) 查詢?nèi)肼毴掌谠缬谄渲苯由霞夘I(lǐng)導(dǎo)的所有員工信息。( 27) 查詢所有部門及其員工信息，包括那些沒有員工的部門。( 28) 查詢所有員工及其部門信息，包括那些還不屬于任何部門的員工。(29)查詢所有工種為CLERK勺員工的姓名及其部門名稱。( 30) 查

4、詢最低工資大于 2500 的各種工作。(31) 查詢平均工資低于 2000的部門及其員工信息。(32) 查詢在SALES部門工作的員工的姓名信息。( 33) 查詢工資高于公司平均工資的所有員工信息。(34) 查詢出與SMITH員工從事相同工作的所有員工信息。(35) 列出工資等于 30 部門中某個員工的工資的所有員工的姓名和工資。(36) 查詢工資高于 30 部門工作的所有員工的工資的員工姓名和工資。(37) 查詢每個部門中的員工數(shù)量、平均工資和平均工作年限。(38) 查詢從事同一種工作但不屬于同一部門的員工信息。（ 39） 查詢各個部門的詳細信息以及部門人數(shù)、部門平均工資。（ 40） 查詢各

5、種工作的最低工資。（ 41） 查詢各個部門中不同工種的最高工資。（42） 查詢 10 號部門員工及其領(lǐng)導(dǎo)的信息。（ 43） 查詢各個部門的人數(shù)及平均工資。（ 44） 查詢工資為某個部門平均工資的員工的信息。（ 45） 查詢工資高于本部門平均工資的員工的信息。（ 46） 查詢工資高于本部門平均工資的員工的信息及其部門的平均工資。（47） 查詢工資高于 20 號部門某個員工工資的員工的信息。（ 48） 統(tǒng)計各個工種的員工人數(shù)與平均工資。（ 49） 統(tǒng)計每個部門中各工種的人數(shù)與平均工資。（50）查詢其他部門中工資、獎金與 30 號部門某員工工資、獎金都相同的員工的 信息。沒有查詢結(jié)果（51）查詢部門

6、人數(shù)大于 5 的部門的員工信息。（52）查詢所有員工工資都大于 1000 的部門的信息。（53）查詢所有員工工資都大于 1000 的部門的信息及其員工信息。（54）查詢所有員工工資都在9003000之間的部門的信息。（55）查詢有工資在9003000之間的員工所在部門的員工信息。（56）查詢每個員工的領(lǐng)導(dǎo)所在部門的信息。（57）查詢?nèi)藬?shù)最多的部門信息。（58）查詢 30號部門中工資排序前 3名的員工信息。59) 查詢所有員工中工資排序在 5 到 10 名之間的員工信息60) 查詢指定年份之間入職的員工信息。答案：員工部門表綜合查詢 60 題( 上)CREATE DATABASE oa;USE

7、oa;CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(20), loc VARCHAR(20) )DROP TABLE emp CREATE TABLE emp( empno INT PRIMARY KEY, ename VARCHAR(20) NOT NULL, job VARCHAR(20) CHECK (job IN (CLERK,SALESMAN,MANAGER,SALESMAN,ANALYST), mgp INT , hiredate DATETIME , sal DECIMAL(10,2), comm DECIMAL

8、(10,2),DEPTNO INT FOREIGN KEY REFERENCES dept(deptno) )INSERT INTO dept VALUES (10,ACCOUNTING,NEWTORK) INSERT INTO dept VALUES (20,RESEARCH,DALLAS) INSERT INTO dept VALUES (30,SALES,CHICAGO) INSERT INTO dept VALUES (40,OPERATIONS,BOSTON)insert into emp values(7369,SMITH,CLERK,7902,1980-12- 17,1640,N

9、ULL,20);insert into emp values(7499,ALLEN,SALESMAN,7698,1981-2- 20,11400,300,30);insert into emp values(7521,WARD,SALESMAN,7698,1981-2- 22,5200,500,30);insert into emp values(7566,JOENS,MANAGER,7839,1981-4- 2,7015,NULL,20);insert into emp values(7654,MARTIN,SALESMAN,7698,1981-9- 28,5200,1400,30);ins

10、ert into emp values(7698,BLAKE,MANAGER,7839,1981-5- 1,5900,NULL,30);insert into emp values(7782,CLARK,MANAGER,7839,1981-6- 9,2470,NULL,10);insert into emp values(7788,SCOTT,ANALYST,7566,1987-4- 19,3040,NULL,20);insert into emp values(7844,TURNER,SALESMAN,7698,1980-12- 17,6200,0,30);insert into emp v

11、alues(7876,ADAMS,CLERK,7788,1981-9- 8,2240,NULL,20);insert into emp values(7900,JAMES,CLERK,7698,1987-5- 23,4000,NULL,30);insert into emp values(7902,FORD,ANALYST,7566,1981-12- 3,3040,NULL,20);insert into emp values(7934,MILLER,CLERK,7782,1982-12- 3,2620,NULL,10);SELECT * FROM emp;select * from dept

12、;員工部門表綜合查詢 60 題(1) 查詢 20 部門的所有員工信息。select * from emp where deptno=20;(2) 查詢所有工種為CLERK勺員工的員工號、員工名和部門號。 select empno,ename,deptno from emp where job=CLERK(3) 查詢獎金(COM)高于工資(SAL的員工信息。select * from emp where isnull(comm,0)sal( 4) 查詢獎金高于工資的 20%的員工信息。select * from emp where isnull(comm,0)sal*0.2(5) 查詢10號部門

13、中工種為MANAGE和20部門中工種為CLERK勺員工的信息 select * from empwhere job=MANAGER and deptno=10unionselect * from empwhere job=CLERK and deptno=20select * from empwhere (job=MANAGER and deptno=10)or (job=CLERK and deptno=20)(6) 查詢所有工種不是 MANAGEIR CLERK- 且工資大于或等于 2000 的員工的詳細信息。 select * from empwhere job not in(MANAG

14、ER,CLERK)and sal=2000( 7) 查詢有獎金的員工的不同工種。select distinct job from emp where comm is not null(8) 查詢所有員工工資與獎金的和。select ename,sal isnull(comm,0)實發(fā)工資from emp(9) 查詢沒有獎金或獎金低于 100的員工信息。select *from empwhere comm is null or comm=25( 12) 查詢員工信息，要求以首字母大寫的方式顯示所有員工的姓名。select upper(SUBSTRING(ename,1,1) lower(subs

15、tring(ename,2,(len(ename)- 1)from emp(13) 查詢員工名正好為 6 個字符的員工的信息。 select ename from emp where len(ename)=6(14) 查詢員工名字中不包含字母“S”的員工。select ename from emp where ename not like % S %(15) 查詢員工姓名的第二字母為“ M的員工信息。select ename from empwhere ename like _M%( 16) 查詢所有員工姓名的前三個字符。select ename 員工姓名 ,substring(ename,1

16、,3) 員工姓名的前三個字符 from emp(17)查詢所有員工的姓名，如果包含字母“ S,則用“ s”替換。- 返回被替換了指定子串的字符串。-REPLACE ( ， ， )- 用 string_expression3 替換在 string_expression1 中的子串 string_expression2 。select replace(ename,S,s)from emp( 18) 查詢員工的姓名和入職日期，并按入職日期從先到后進行排序。select ename,hiredatefrom emporder by hiredate( 19) 顯示所有員工的姓名、工種、工資和獎金，按工

17、種降序排序， - 若工種相同則按工資升序排序。select ename,job,sal,commfrom emp order by job desc( 20) 顯示所有員工的姓名、入職的年份和月份，- 按入職日期所在的月份排序，若月份相同則按入職的年份排序。select ename,hiredate 入職日期 ,DATENAME(yy,hiredate) 入職的年份 ,datename(mm,hiredate) 入職的月份from emp(21) 查詢在 2 月份入職的所有員工信息。select * from empwhere datename(mm,hiredate)=2(22)查詢所有員工

18、入職以來的工作期限，用“ XX年XX月XX日”的形式表示。 select ename,datename(yy,hiredate) 年 datename(mm,hiredate) 月datename(dd,hiredate) 日 工作期限from emp( 23.1) 查詢至少有一個員工的部門信息。select d.dname,count(empno) 部門人數(shù)from emp eright join dept d on d.deptno=e.deptnogroup by d.dname,e.deptnohaving count(empno)=1(23.2) 查詢至少有兩個員工的部門信息。sel

19、ect d.dname,count(empno) 部門人數(shù)from emp eright join dept d on d.deptno=e.deptnogroup by d.dname,e.deptnohaving count(empno)1(24)查詢工資比SMITH員工工資高的所有員工信息。select *from empwhere sal(select sal from emp where ename=SMITH)( 25) 查詢所有員工的姓名及其直接上級的姓名 select ename 員工的姓名 ,(select ename from emp e2 where e2.empno=e

20、1.mgp ) 直接上級from emp e1( 26) 查詢?nèi)肼毴掌谠缬谄渲苯由霞夘I(lǐng)導(dǎo)的所有員工信息。 select ename 員工的姓名 ,hiredate 入職日期 ,( select ename from emp e2 where e2.empno=e1.mgp ) 直接上級 ,(select hiredate from emp e2 where e2.empno=e1.mgp) 直接上級入職日期 from emp e1 where e1.hiredate2500員工部門表綜合查詢 60 題（下）(31) 查詢平均工資低于 2000 的部門及其員工信息。 select *from d

21、ept left outer join emp on dept.deptno=emp.deptno where dept.deptno in ( select deptno from emp group by deptno having avg(sal)( select avg(sal) from emp)(34) 查詢出與SMITH員工從事相同工作的所有員工信息。select * from emp where job = ( select job from emp where ename=SMITH)(35) 列出工資等于 30 部門中某個員工的工資的所有員工的姓名和工資 select *f

22、rom emp where sal in ( select sal from emp where deptno=30) and deptno!=3036) 查詢工資高于 30 部門工作的所有員工的工資的員工姓名和工資 select * from emp where sal all(select sal from empwhere deptno=30)( 37) 查詢每個部門中的員工數(shù)量、平均工資和平均工作年限。 select dname 部門 ,count(ename) 員工數(shù)量 ,isnull(avg(sal),0)平均工資 ,isnull(avg(datediff(yy,hiredate,

23、getdate(),0) 平均工作年限 from dept dleft outer join emp e on d.deptno=e.deptno group by d.dname( 38) 查詢從事同一種工作但不屬于同一部門的員工信息 select *from emp e1 where e1.job in ( select distinct e2.job from emp e2 where e2.deptno != e1.deptno )( 39) 查詢各個部門的詳細信息以及部門人數(shù)、部門平均工資。select d.dname 部門名稱 ,d.deptno 部門編號 ,count(e.emp

24、no) 人數(shù) ,avg(e.sal) 平均工資from dept dleft outer join emp e on d.deptno=e.deptnogroup by d.deptno,d.dname( 40) 查詢各種工作的最低工資。 select job 工種 ,min(sal) 最低工資 from empgroup by job最高工資( 41) 查詢各個部門中不同工種的最高工資。 select dname 部門名稱 ,job 工種 ,max(isnull(sal,0) from dept d left join emp e on d.deptno=e.deptno group by

25、job,dname(42) 查詢 10 號部門員工及其領(lǐng)導(dǎo)的信息。select deptno 部門 ,ename 姓名 ,(select e2.ename from emp e2 where e2.mgp=e1.empno) 上級領(lǐng)導(dǎo)from emp e1 where deptno=10( 43) 查詢各個部門的人數(shù)及平均工資。select dname 部門名稱 ,count(ename) 部門人數(shù) ,avg(isnull(sal,0) from dept d left outer join emp e on d.deptno=e.deptno group by d.dname( 44) 查詢

26、工資為某個部門平均工資的員工的信息。 select * from emp where sal in( select avg(sal) from emp group by deptno)( 45) 查詢工資高于本部門平均工資的員工的信息。 select * from emp e1 where sal( select avg(sal)from emp e2where e2.deptno=e1.deptno)( 46) 查詢工資高于本部門平均工資的員工的信息及其部門的平均工資。 select *,(select avg(sal) from emp e2 where e2.deptno=e1.dept

27、no) 均工資 from emp e1 where sal(select avg(sal) from emp e2 where e2.deptno=e1.deptno)(47) 查詢工資高于 20 號部門某個員工工資的員工的信息。 select *from emp e1where sal any(select sal from emp e where deptno=20平均工資部門平)(48)統(tǒng)計各個工種的員工人數(shù)與平均工資。select job 工種 ,count(empno) 員工人數(shù) ,avg(sal) 平均工資 from empgroup by job( 49) 統(tǒng)計每個部門中各工種的

28、人數(shù)與平均工資。select dname 部門 ,job 工種 ,count(empno) 人數(shù) ,avg(isnull(sal,0)平均工資from dept d left outer join emp e on d.deptno=e.deptnogroup by job,dname(50) 查詢其他部門中工資、獎金與 30 號部門某員工工資、- 獎金都相同的員工的信息。沒有查詢結(jié)果select *from emp ewhere isnull(sal,0) isnull(comm,0) in (select isnull(sal,0) isnull(comm,0)from emp e1where e1.deptno=30 and e.sal=e1.sal and m=m and e.deptno!=30)(51) 查詢部門人數(shù)大于 5 的部門的員工信息。 select * from emp where deptno in(select deptnofrom empgroup by deptnohaving count(empno)5)(52) 查詢所有員工工資都大于 1000 的部門的信息。select *from dept dwhere deptno in (select deptno from emp egroup by deptnohavi