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 values1030，8000，1) insert into TbEmo values1030，6000，2) insert into TbEmo values1030，15000，3) insert into TbEmo values1030，9000，4) insert into TbEmo values1030，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