3月1号两联表作业
本帖最后由 上海25期-文武飞 于 2022-3-1 22:37 编辑CREATE TABLE dept(dept1 int(20),dept_name varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';
INSERT INTO dept VALUES(101,'财务');
INSERT into dept VALUES(102,'销售');
INSERT into dept VALUES(103,'IT技术');
INSERT into dept VALUES(104,'行政');
create table emp(sid int(10) PRIMARY key,name VARCHAR(20),
age int(20),worktime date,incoming int(20),dept2 int(20))ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';
desc emp;
SELECT * from emp;
INSERT INTO emp VALUES(1789,'张三',35,"1980-01-01",4000,101);
INSERT into emp VALUES(1674,'李四',32,"1983-04-01",3500,101);
INSERT into emp VALUES(1776,'王五',24,"1990-07-01",2000,101);
INSERT into emp VALUES(1568,'赵六',57,"1970-10-11",7500,102);
INSERT into emp VALUES(1564,'荣七',64,"1963-10-11",8500,102);
INSERT into emp VALUES(1879,'牛八',55,"1971-10-20",7300,103);
1、财务部门的收入总和；
select sum(incoming),dept_namefrom (select*from dept left join empon dept.dept1=emp.dept2)t where t.dept_name='财务'; （显示收入总和财务部门）
select sum(incoming),dept_namefrom (select*from emp right join dept on dept.dept1=emp.dept2)t where t.dept_name='财务'; （显示收入总和财务部门）
select sum(incoming) from emp where dept2 =(select dept1 from dept where dept_name='财务'); （只显示收入总和）
select sum(incoming) from emp where dept2 in(select dept1 from dept where dept_name='财务');(只显示收入总和）
select sum(incoming) from emp where dept2 not in(select dept1 from dept where dept_name!='财务'); （只显示收入总和）
2、It技术部入职员工的员工号
select sid,name,dept_name from (select*from emp left join dept on dept.dept1=emp.dept2)t where t.dept_name='IT技术';
select sid,name,dept_name from (select*from dept right join emp on dept.dept1=emp.dept2)t where t.dept_name='IT技术';
select sid,name from emp where dept2 =(select dept1 from dept where dept_name='IT技术'); （只显示员工号和姓名）
select sid,name from emp where dept2 in(select dept1 from dept where dept_name='IT技术');（只显示员工号和姓名）
select sid,name from emp where dept2 not in(select dept1 from dept where dept_name!='IT技术'); （只显示员工号和姓名）
3、列出每一个部门中年纪最大的员工姓名，部门名称；
select max(age),name,dept_name from (select*from dept left join emp on dept.dept1=emp.dept2)t group by t.dept_name;
select max(age),name,dept_name from (select*from emp right join dept on dept.dept1=emp.dept2)t group by t.dept_name;
4、列出每一个部门的员工总收入及部门名称；
select sum(incoming),dept_name from (select*from dept left join emp on dept.dept1=emp.dept2)t group by t.dept_name;
select sum(incoming),dept_name from (select*from emp right join dept on dept.dept1=emp.dept2)t group by t.dept_name;
5、列出部门员工收入大于7000的部门编号，部门名称；
select name,incoming,dept_name,dept2 from (select*from dept left join emp on dept.dept1=emp.dept2)t having t.incoming>7000;
select name,incoming,dept_name,dept2 from (select*from emp right join dept on dept.dept1=emp.dept2)thaving t.incoming>7000;
6、找出哪个部门还没有员工入职；
select name,worktime,dept_name from (select*from dept left join emp on dept.dept1=emp.dept2)t where t.worktime>'2022-03-01';
select name,worktime,dept_name from (select*from emp right join dept on dept.dept1=emp.dept2)t where t.worktime>'2022-03-01';
666666666666666666666666666666666666666666666666666666666
页:
[1]