員工部門表綜合查詢題

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

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

3、職的年份排序。（ 21） 查詢在 2 月份入職的所有員工信息。(22)查詢所有員工入職以來的工作期限，用“ XX年XX月XX日”的形式表示。23.1 ) 查詢至少有一個員工的部門信息。23.2 ) 查詢至少有兩個員工的部門信息。(24)查詢工資比SMITH員工工資高的所有員工信息。25) 查詢所有員工的姓名及其直接上級的姓名。26) 查詢入職日期早于其直接上級領導的所有員工信息。27) 查詢所有部門及其員工信息，包括那些沒有員工的部門。28) 查詢所有員工及其部門信息，包括那些還不屬于任何部門的員工。(29)查詢所有工種為CLERK勺員工的姓名及其部門名稱。30) 查詢最低工資大于2500 的

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

5、的信息。（ 43） 查詢各個部門的人數及平均工資。（ 44） 查詢工資為某個部門平均工資的員工的信息。（ 45） 查詢工資高于本部門平均工資的員工的信息。（ 46） 查詢工資高于本部門平均工資的員工的信息及其部門的平均工資。（ 47） 查詢工資高于20 號部門某個員工工資的員工的信息。（ 48） 統(tǒng)計各個工種的員工人數與平均工資。（ 49） 統(tǒng)計每個部門中各工種的人數與平均工資。（ 50） 查詢其他部門中工資、獎金與 30 號部門某員工工資、獎金都相同的員工的信息。沒有查詢結果（ 51） 查詢部門人數大于5 的部門的員工信息。（ 52） 查詢所有員工工資都大于1000 的部門的信息。（ 53）

6、 查詢所有員工工資都大于1000 的部門的信息及其員工信息。（54）查詢所有員工工資都在9003000之間的部門的信息。（55）查詢有工資在9003000之間的員工所在部門的員工信息。（ 56） 查詢每個員工的領導所在部門的信息。（ 57） 查詢人數最多的部門信息。（ 58） 查詢 30 號部門中工資排序前3 名的員工信息。59) 查詢所有員工中工資排序在5 到 10 名之間的員工信息。60) 查詢指定年份之間入職的員工信息。答案：員工部門表綜合查詢 60 題 ( 上 )CREATE DATABASE oa;USE oa;CREATE TABLE dept(deptno INT PRIMARY

7、 KEY,dname VARCHAR(20), loc VARCHAR(20) )DROP TABLE empCREATE 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(10,2),DEPTNO INT FOREIGN KEY REFERENCES dept(de

8、ptno) )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,NULL,20);insert into emp values(7499,ALLEN,SALESMAN

9、,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);insert into emp values(7698,BLAKE,MANAGER,7839,1981-5

10、- 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 values(7876,ADAMS,CLERK,7788,1981-9- 8,2240,NULL,20

11、);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;員工部門表綜合查詢 60 題( 1) 查詢 20 部門的所有員工信息。select * from em

12、p where deptno=20;(2)查詢所有工種為CLERK勺員工的員工號、員工名和部門號。select empno,ename,deptno from emp where job=CLERK(3) 查詢獎金(COMM高于工資(SALL的員工信息。select * from emp where isnull(comm,0)sal( 4) 查詢獎金高于工資的20%的員工信息。select * from emp where isnull(comm,0)sal*0.2(5)查詢10號部門中工種為MANAGER 20部門中工種為CLERK勺員工的信息 select * from empwhere

13、 job=MANAGER and deptno=10unionselect * from empwhere job=CLERK and deptno=20-select * from empwhere (job=MANAGER and deptno=10)or (job=CLERK and deptno=20)( 6) 查詢所有工種不是 MANAGER CLERK- 且工資大于或等于2000 的員工的詳細信息。select * from empwhere job not in(MANAGER,CLERK)and sal=2000( 7) 查詢有獎金的員工的不同工種。select distinc

14、t job from empwhere 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(substring(ename,2,(len(ename)- 1)from emp( 13) 查詢員工名正好為

15、 6 個字符的員工的信息。select ename from emp where len(ename)=6(14)查詢員工名字中不包含字母“S”的員工。select ename from emp where ename not like % S %(15)查詢員工姓名的第二字母為“ MT的員工信息。select ename from empwhere ename like _M%( 16) 查詢所有員工姓名的前三個字符。select ename 員工姓名 ,substring(ename,1,3) 員工姓名的前三個字符from emp(17)查詢所有員工的姓名，如果包含字母 S,則用“s”替換

16、。- - 返回被替換了指定子串的字符串。- -REPLACE ( ， ，)- - 用 string_expression3 替換在 string_expression1 中的子串string_expression2 。select replace(ename,S,s)from emp( 18) 查詢員工的姓名和入職日期，并按入職日期從先到后進行排序。select ename,hiredatefrom emporder by hiredate( 19) 顯示所有員工的姓名、工種、工資和獎金，按工種降序排序，- 若工種相同則按工資升序排序。select ename,job,sal,commfrom

17、 emporder 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)查詢所有員工入職以來的工作期限，用“ XX年XX月XX日”的形式表示。 select ename,datename

18、(yy,hiredate) 年 datename(mm,hiredate) 月 datename(dd,hiredate) 日 工作期限from emp( 23.1 ) 查詢至少有一個員工的部門信息。select d.dname,count(empno) 部門人數from emp eright join dept d on d.deptno=e.deptnogroup by d.dname,e.deptnohaving count(empno)=1( 23.2 ) 查詢至少有兩個員工的部門信息。select d.dname,count(empno) 部門人數from emp eright jo

19、in 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=e1.mgp） 直接上級from emp e1（ 26） 查詢入職日期早于其直接上級領導的所

20、有員工信息。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 e1where e1.hiredate2500員工部門表綜合查詢 60 題 （ 下 ） 31） 查詢平均工資低于2000 的部門及其員工信息。select *from dept left outer join emp on dept.deptno=emp.deptnow

21、here dept.deptno in (select deptno from empgroup by deptnohaving avg(sal)(select avg(sal)from emp)(34)查詢出與SMITH員工從事相同工作的所有員工信息。 select * from emp where job = (select jobfrom empwhere ename=SMITH)( 35) 列出工資等于30 部門中某個員工的工資的所有員工的姓名和工資。select *from empwhere sal in (select salfrom empwhere deptno=30) and

22、 deptno!=30( 36) 查詢工資高于30 部門工作的所有員工的工資的員工姓名和工資。select *from empwhere sal all(select salfrom empwhere deptno=30)( 37) 查詢每個部門中的員工數量、平均工資和平均工作年限。select dname 部門 ,count(ename) 員工數量 ,isnull(avg(sal),0) 平均工資 ,isnull(avg(datediff(yy,hiredate,getdate(),0)平均工作年限from dept dleft outer join emp e on d.deptno=e.

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

24、ptnogroup 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.deptnogroup by job,dname( 42) 查詢 10 號部門員工及其領導的信息。select deptno 部門 ,ename 姓名 ,(select e2.ena

25、me from emp e2 wheree2.mgp=e1.empno) 上級領導from emp e1where deptno=10( 43) 查詢各個部門的人數及平均工資。select dname 部門名稱 ,count(ename) 部門人數 ,avg(isnull(sal,0) 平均工資from dept d left outer join emp e on d.deptno=e.deptnogroup by d.dname( 44) 查詢工資為某個部門平均工資的員工的信息。select * from empwhere sal in(select avg(sal)from emp gr

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

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

28、t outer join emp e on d.deptno=e.deptnogroup by job,dname( 50) 查詢其他部門中工資、獎金與 30 號部門某員工工資、- 獎金都相同的員工的信息。沒有查詢結果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) 查詢部門人數大于5 的部門的員工信息。select * from empwhere deptno in(select deptnofrom empgroup by deptnohaving count(empno)5)( 52) 查詢所有員工工資都大于 1000 的部門的信息。select *from dept dwhere deptno in (select deptno from emp egroup by deptnoh