查询练习

create table dept(       --创建‘部门’表
deptno int primary key,  --部门编号（主键）
dname nvarchar(30),      --部门名称
loc nvarchar(30)         --部门所在地
)

create table emp(        --创建员工表
empno int primary key,   --员工编号（主键）
ename nvarchar(30),      --员工姓名
job nvarchar(30),        --员工的工作类型
mgr int,                 --员工的上级
hiredate datetime,       --员工的入职时间
sal numeric(10,2),       --员工的每月工资
comm numeric(10,2),      --员工的年终奖金
deptno int foreign key references dept(deptno) 
)                        --创建一个外键指向部门 

insert into dept values (10,'accounting','new york')
insert into dept values (20,'reasarch','dallas')
insert into dept values (30,'sales','chicago')
insert into dept values (40,'operations','boston')

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7369,'michael','clerk',7902,'2010-1-12',675.23,300,20)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
 (7499,'allen','salesman',7698,'2009-1-23',1675.23,322.50,30)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
 (7521,'ward','salesman',7698,'2008-1-3',12675.99,399.50,30)
insert into emp (empno,ename,job,mgr,hiredate,comm,deptno) values
 (7566,'jones','manager',7839,'2000-1-1',8675.99,20)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7654,'martin','salesman',7698,'2007-12-31',1275.99,999.00,30)
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
 (7782,'blake','manager',7839,'2007-12-20',1275.99,30)
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
(7781,'miller','opreator',7566,'2005-10-12',1275.99,40)
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
(7744,'adamc','opreator',7566,'2006-10-30',1244.0,40)
insert into emp (empno,ename,job,hiredate,sal,deptno) values
(7839,'king','president','2000-1-1',100244.0,10)

select * from dept
select * from emp

--1.查询所有列：
select * from emp
--2.查询指定列：
select ename,sal from emp
--3.取消重复列：（查询部门号）
select count(distinct empno) from emp
--4.查询SMITH的薪水，工作，所在部门
select sal,job,deptno from emp where ename='king'
--5。统计总共有多少个部门：
select count(distinct deptno) from emp
select count(distinct (deptno)) from emp
select count(deptno) from dept
--6.显示每个员工的年工资
select isnull (sal,0)*12 +isnull(comm,0) as nianxin from emp
--7.如何显示工资高于3000的员工
select sal from emp where sal>3000
--8.如何查找1982.1.1后入职的员工
select hiredate from emp where hiredate>1982-1-1
--9.如何显示工资在2000到2500之间的员工情况
select * from emp where sal between 2000 and 2500
--10.如何显示首字母为M的员工姓名和工资
select ename,sal from emp where ename like 'm%'
--11.如何显示首第3个字母为’l‘的所有员工的姓名和工资
select ename,sal from emp where ename like '__l%'
--12.如何显示EMPNO为123,345,800...的员工的姓名和工资（ where in）
select empno from emp where empno in('123','345','800','1999','7499')
--13.如何显示没有上级的雇员的情况（is null ）
select * from emp where mgr is null
--14.查询工资高于1000或是岗位为manager的雇员，还要满足他们的姓名首字母为J
select * from emp where (sal>1000 or job='manager') and
 ename like 'j%'
--15.如何按工资的从低到高的顺序显示雇员信息（order by asc, desc)
select * from emp  order by sal
--16.如何按入职的先后顺序显示雇员信雇员的信息
select * from emp order by hiredate
--17.按部门号升序而工资降序排列显示雇员信息
select * from dept order by deptno asc,sal desc
--18.统计每个人的年薪，并从低到高的顺序排列
select isnull (sal,0)*12 +isnull(comm,0) nianxin from emp order by  nianxin
--19.如何显示员工中最高和最低的工资
select max(sal)as zuigao ,min(sal) as zuidi from emp

--20.如何显示员工中最低工资的员工信息
select * from emp where sal=(select min(sal) from emp)

--21.如何显示所有员工每个月的平均工资和工资总和

select avg(sal),sum(sal) from emp
--22.把高于平均工资的员工名字和工资显示出来
select ename,sal from emp
 where sal>(select avg(sal) from emp)
--23.计算有多少员工
select count(empno) from emp
--24.如何显示每个部门的平均工资和最高工
select avg(sal) as 平均工资,max(sal) as 最高工资 from emp group by deptno
--26.如何显示每个部门的平均工资和最高工,并显示部门名称
select avg(sal) as 平均工资,max(sal) as 最高工资,a.dname as 部门名称 from dept a,
emp b where a.deptno=b.deptno group by a.dname
--27.显示每个部门的各个岗位的平均工资和最低工资
select job 岗位,avg(sal) as 平均,min(sal) as 最低 from emp group by job
--28.显示平均工资小于2000及部门号（having 。
select deptno,avg(sal) from emp group by deptno having avg(sal)<2000

--分离和恢复
--备份和恢复

--用查询分析器备份和恢复
--1.备份
backup database mf2011 to disk='c:/mf2011'
--2.删除
drop database mf2011
--3.恢复
restore database mf2011 from disk='c:/mf2011'