SQL基础语句--数据库--向先先

create database Company;
go

use Company;

create table tb_dept
(
depno int primary key,
dname varchar(20) not null,
loc varchar(50)
);
go

create table tb_emp
(
eno int primary key,
ename varchar(20) not null,
job varchar(10) not null,
mgr int references tb_emp(eno),
sal int not null,
comm int not null,
dno int references tb_dept(depno)
);
go

insert into tb_dept values (10, '会计部', '北京海淀区');
insert into tb_dept values (20, '研发部', '上海浦东区');
insert into tb_dept values (30, '销售部', '重庆江北区');
insert into tb_dept values (40, '运维部', '四川成都');

insert into tb_emp values (7800, '罗浩', '总裁', null, 8000, 3500, 20);
insert into tb_emp values (2056, '王杰', '分析师', 7800, 5000, 1500, 20);
insert into tb_emp values (3088, '鲜朋', '设计师', 2056, 3500, 1200, 20);
insert into tb_emp values (3211, '吴伊娜', '程序员', 2056, 3200, 800, 20);
insert into tb_emp values (5566, '聂贵川', '会计师', 7800, 4000, 300, 10);
insert into tb_emp values (5234, '罗科', '出纳', 5566, 2000, 100, 10);
insert into tb_emp values (3344, '顾秀英', '销售主管', 7800, 3000, 500, 30);
insert into tb_emp values (1359, '魏香玲', '销售员', 3344, 1800, 800, 30);


select * from tb_dept;
select * from tb_emp;

select ename,sal  from tb_emp where sal=(
 select MAX(sal) from tb_emp where mgr is not null)
 
 select ename ,sal from tb_emp where sal >(
 select AVG(sal)from tb_emp)
 
 select ename ,sal ,sal-avgSal as 高出部分,avgSal 平均工资 from tb_emp  as t1,
 (select AVG (sal) as avgSal from tb_emp) as t2 where sal >avgSal ;

--查询每个部门的人数
SELECT dname as 部门名称,人数 from  tb_dept as t1 ,(
select dno,COUNT(dno) as 人数 from tb_emp group by dno) as t2
where t1.depno= t2.dno;
--inner join 内联接
SELECT dname as 部门名称,人数 
from  tb_dept as t1 inner join
(select dno,COUNT(dno) as 人数 from tb_emp 
group by dno)  t2 on t1.depno= t2.dno
--左外联接/全外联接/右外联接(left outer/ full/right outer)join 
SELECT dname as 部门名称,isnull(人数,0) 
from  tb_dept as t1 left outer  join
(select dno,COUNT(dno) as 人数 from tb_emp 
group by dno)  t2 on t1.depno= t2.dno  

--查询每个部门的部门名称和平均工资
select dname as 部门名称,avgsal as 平均工资  from tb_dept as t1 inner join ( 
select dno,AVG (sal) as avgsal from tb_emp group by dno) t2
on t1.depno=t2.dno 


 --查询谁是主管
 select ename   from tb_emp 
 where eno in(select  mgr from tb_emp )