员工部门表综合查询SQL

--数据库的表设计如下：--部门：部门编号，部门名称，地址；--员工：员工编号，员工名字，职务，管理编号，入职日期，薪资，奖金，部门编号；--创建部门表：CREATE TABLE dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(20) );

--创建员工表：CREATE TABLE emp(empno INT PRIMARY KEY,ename VARCHAR(20) NOT NULL,  job VARCHAR(20) CHECK (job IN('CLERK','SALESMAN','MANAGER','ANALYST')),mgp INT,hiredate DATETIME ,sal DECIMAL(10,2),comm DECIMAL(10,2),deptno INT,CONSTRAINT pk_we FOREIGN KEY (deptno) 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 empvalues(7369,'SMITH','CLERK',7902,'1980-12-17',1640,NULL,20);

insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',11400,300,30);

insert into empvalues(7521,'WARD','SALESMAN',7698,'1981-2-22',5200,500,30);

insert into empvalues(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 empvalues(7698,'BLAKE','MANAGER',7839,'1981-5-1',5900,NULL,30);

insert into empvalues(7782,'CLARK','MANAGER',7839,'1981-6-9',2470,NULL,10);

insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3040,NULL,20);（1） 查询奖金高于工资的20%的员工信息select * from emp where isnull(comm,0)>sal*0.2;

（2） 查询10号部门中工种为MANAGER和20部门中工种为CLERK的员工的信息select * from emp where job='MANAGER' and deptno=10 unionselect * from emp where job='CLERK' and deptno=20;--select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);

（3） 查询所有员工工资与奖金的和select ename,sal+isnull(comm,0) 实发工资 from emp;

（4） 查询没有奖金或奖金低于100的员工信息select * from empwhere comm is null or comm<100;

（5） 查询各月倒数第3天(倒数第2天)入职的员工信息select * from emp where DATENAME(day,hiredate+3)=1;

（6） 查询工龄大于或等于25年的员工信息。select ename 姓名,hiredate 雇用日期,datediff(year,hiredate,getdate()) 工龄from empwhere datediff(year,hiredate,getdate())>=25;

（7） 查询员工信息，要求以首字母大写的方式显示所有员工的姓名select upper(SUBSTRING(ename,1,1))+lower(substring(ename,2,(len(ename)-1)))from emp;

（8） 查询员工名正好为6个字符的员工的信息select ename from emp where len(ename)=6;

（9） 查询员工名字中不包含字母“Ｓ”的员工select ename from emp where ename not like '%Ｓ%';

（10） 查询员工姓名的第二字母为“M”的员工信息。select ename from emp where ename like '_M%';

（11） 查询所有员工姓名的前三个字符select ename 员工姓名,substring(ename,1,3)员工姓名的前三个字符 from emp;

（12） 查询所有员工的姓名，如果包含字母“S”，则用“s”替换--返回被替换了指定子串的字符串--REPLACE (<string_expression1>，<string_expression2>，<string_expression3>)--用string_expression3替换在string_expression1 中的子串string_expression2。select replace(ename,'S','s') from emp;

（13） 查询在2月份入职的所有员工信息select * from emp where datename(mm,hiredate)=2;

（14） 查询所有员工入职以来的工作期限，用“XX年XX月XX日”的形式表示。select ename,datename(yy,hiredate)+'年'+datename(mm,hiredate)+'月'+datename(dd,hiredate)+'日' 工作期限 from emp;

（15） 查询至少有一个员工的部门信息。select d.dname,count(empno) 部门人数from emp eright join dept d on d.deptno=e.deptnogroup by d.dname,e.deptnohaving count(empno)>=1;

（16） 查询所有员工的姓名及其直接上级的姓名。select ename 员工的姓名,(select ename from emp e2 where e2.empno=e1.mgp) 直接上级from emp e1;

（17） 查询入职日期早于其直接上级领导的所有员工信息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.hiredate<(select hiredatefrom emp e2 where e2.empno=e1.mgp);

（18） 查询所有部门及其员工信息，包括那些没有员工的部门select dept.dname,emp.enamefrom deptleft outer join emp on emp.deptno=dept.deptno;（19） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工select dept.dname,emp.enamefrom empleft outer join dept on emp.deptno=dept.deptno;

（20） 查询所有工种为CLERK的员工的姓名及其部门名称select dept.dname,emp.ename,emp.jobfrom empleft outer join dept on emp.deptno=dept.deptnowhere job='CLERK';