sql练习
sql练习
创建数据库 create database dbexecise;
切换到dbexecise数据库 use dbexecise;
创建部门表
create table dept(
#部门编号
deptno int primary key,
#部门名称
deptname varchar(50),
# 部门位置
loc varchar(50)
);
部门表插入语句
INSERT INTO `dept` VALUES ('10', '会计部', '北京');
INSERT INTO `dept` VALUES ('20', '调查部', '杭州');
INSERT INTO `dept` VALUES ('30', '销售部', '上海');
INSERT INTO `dept` VALUES ('40', '营销部', '深圳');
创建员工表
create table emp(
#员工编号
empno int primary key,
#员工姓名
empname varchar(50),
#职位
job varchar(50),
#直属上级
mgr int,
#受雇日期
hiredate date,
#薪资
sal int,
#奖金
comm int,
#所在部门编号
deptno int,
#外键
foreign key(deptno) references dept(deptno)
);
员工表插入数据
INSERT INTO `emp` VALUES ('7369', '张无忌', '办事员', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', '曹操', '推销员', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', '杨志', '推销员', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', '朱元璋', '经理', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', '殷天正', '推销员', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', '张三丰', '经理', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', '关羽', '经理', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', '宋江', '分析员', '7566', '1987-04-19', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', '韩少云', '董事长', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', '孙二娘', '推销员', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', '张飞', '办事员', '7788', '1987-05-23', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', '赵云', '办事员', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', '诸葛亮', '分析员', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', '夏侯惇', '办事员', '7782', '1982-01-23', '1300', null, '10');
查询语句
1.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 select deptname, empname, empno, emp.deptno from dept left join emp on emp.deptno=dept.deptno;
2.列出薪资比关羽高的所有员工.
1).查询出关羽的薪资 select sal from emp where empname='关羽'
2).查询出比关羽薪资高的员工 select empno,empname,sal from emp where sal>( SELECT sal from emp where empname='关羽');
3.列出所有员工的姓名及其直接上级的姓名
1).查询员工 select empno,mgr from emp e1;
2).查询员工的上级 select empno,empname from emp e2;
3).关联查询,查询出员工及员工对应的上级 select e1.empname,e2.empname from emp e1,emp e2 where e1.mgr = e2.empno;
4.列出最低薪资大于1500的各种职位及其从事此职位的员工人数
1).查询出各种职位的最低薪资 select job, min(sal) 最低薪资 from emp group by job having min(sal);
2).查询出最低薪资>1500的职位 select job, min(sal) 最低薪资 from emp group by job having min(sal)>1500;
3).查询出每个职位对应的员工人数 select job, min(sal) 最低薪资, count(*) 员工人数 from emp group by job having min(sal)>1500;
5.列出在销售部职位的员工姓名,假定不知道销售部的部门编号
1).查询出销售部的部门编号 select deptno from dept where deptname='销售部';
2).查询出在销售部职位的员工姓名 select empname from emp where deptno=(select deptno from dept where deptname='销售部');
6.列出与曹操从事相同职位的所有员工及部门名称
1).查询出曹操从事的职位及部门编号 select job,deptno from emp where empname='曹操';
2).查询出与曹操从事相同职位的所有员工 select empname,deptno from emp where job=(select job from emp where empname='曹操');
3).查询出与曹操从事相同职位的所有员工及部门名称 select deptname,empname from dept,(select empname,deptno from emp where job=(select job from emp where empname='曹操'))e2 where dept.deptno=e2.deptno;
7.列出薪资高于销售部(已知编号为30)就职的所有员工的薪资的员工姓名和薪资,部门名称
1).查出在销售部就职的所有员工最高薪资 select max(sal) from emp where deptno=30;
2).关联查询, 查询员工的姓名,薪资, 部门名称 select e.empname,e.sal,d.deptname from emp e,dept d where e.deptno=d.deptno and sal>(select max(sal) from emp where deptno=30);
8.列出在每个部门职位的员工数量、平均工资。 select count(*),avg(sal) from emp group by deptno;
9.查出至少有一个员工的部门。显示部门人数、部门编号、部门名称、部门位置.
1).查询出每个部门员工数量 select count(*),deptno from emp group by deptno;
2).关联查询,查询出部门编号、部门名称、部门位置 select i.c,d.deptname,d.loc,d.deptno from dept d,(select count(*) c,deptno from emp group by deptno)i where i.deptno=d.deptno and i.c>=1;
10.列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称 select e1.empname, e1.empno, d.deptname from emp e1, emp e2, dept d where e1.deptno=d.deptno and e1.mgr=e2.empno and e1.hiredate < e2.hiredate;
11.列出所有职员的姓名及其部门名称,部门的人数。 select t.empname, t.deptname, e.cnt 员工数量 from
(
select e.empname, e.deptno, d.deptname from emp e, dept d where e.deptno=d.deptno
) t, (
select deptno, count(*) cnt from emp group by deptno
) e
where t.deptno = e.deptno;
关注下面微信公众号获得更多学习资源!
上一篇: 第四周作业(基础题)