欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

sql练习

程序员文章站 2024-03-15 18:10:54
...

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;

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

相关标签: mysql