SQL Server 数据库简单查询和关联查询练习

-- 创建数据库
use master 

if(exists(select *from sys.databases where name='HR'))
  drop database HR

create database HR

go

use HR

create table TbDept
(
  deptno int identity(1,1) primary key,
  dname varchar(20),
  dloc text
)

insert into TbDept values('销售部','武汉市')
insert into TbDept values('后勤部','赣州市')
insert into TbDept values('培训部','黄冈市')
insert into TbDept values('管理部','西安市')
insert into TbDept values('策略部','武汉市')


create table TbEmo
(
  empno int identity(1,1) primary key,
  ename varchar(20),
  job varchar(20),
  mgr int,
  sal int,
  dno int references TbDept(deptno)
)


insert into TbEmo values('张三','部员',1030,8000,1)
insert into TbEmo values('李四','部员',1030,6000,2)
insert into TbEmo values('陈总','总裁',1030,15000,3)
insert into TbEmo values('王五','经理',1030,9000,4)
insert into TbEmo values('吴广','部员',1030,7000,5)




--练习:
-- 1.查询薪水最高的员工姓名和工资
select top 1 ename,sal from TbEmo order by sal desc
-- 2.查询员工的姓名和年薪
select ename,sal*12 as 年薪 from TbEmo
-- 3.查询有员工的部门的编号和人数
select dno as 编号,COUNT(*) as 人数 from TbEmo where job='部员' group by dno
-- 4.查询所有部门的名称和人数
select dname as 名称,COUNT(*) as 人数 from TbDept left join TbEmo on TbDept.deptno=TbEmo.dno group by dno,dname
-- 5.查询薪水最高的员工(boss除外)的姓名和工资
select top 1 ename,sal from TbEmo where job='部员' order by sal desc 
-- 6.查询薪水超过平均薪水的员工的姓名和工资
select ename,sal from TbEmo where sal>(select  SUM(sal)/COUNT(sal) from TbEmo) and job='员工'
-- 7.查询薪水超过其所在部门的平均薪水的员工的姓名，部门编号和工资
select ename,deptno,sal from (select * from TbEmo where sal>(select  SUM(sal)/COUNT(sal) from TbEmo)) as a inner join TbDept on a.dno=TbDept.deptno
-- 8.查询部门中薪水最高的人的姓名,工资和所在部门名称
select  top 1 ename,sal,dname from TbEmo inner join TbDept on TbEmo.dno=TbDept.deptno order by sal desc
-- 9.查询·主管的姓名和职位
select  top 1 ename,sal,dname from TbEmo inner join TbDept on TbEmo.dno=TbDept.deptno where job='总裁'
-- 10.查询薪水排名的前三的员工的姓名和工资
select top 3 ename,sal from TbEmo where job='部员' order by sal desc