员工部门表综合查询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 emp

where 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 e

right join dept d on d.deptno=e.deptno

group by d.dname,e.deptno

having 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 e1

where e1.hiredate<(select hiredate

from emp e2 where e2.empno=e1.mgp

);

（18） 查询所有部门及其员工信息，包括那些没有员工的部门

select dept.dname,emp.ename

from dept

left outer join emp on emp.deptno=dept.deptno;

（19） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工

select dept.dname,emp.ename

from emp

left outer join dept on emp.deptno=dept.deptno;

（20） 查询所有工种为CLERK的员工的姓名及其部门名称

select dept.dname,emp.ename,emp.job

from emp

left outer join dept on emp.deptno=dept.deptno

where job='CLERK';

巴特西

员工部门表综合查询SQL

最新文章

热门文章