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;

关注下面微信公众号获得更多学习资源！