MySQL练习题

-- mysql练习sql脚本

 

-- ----------------------------

-- ----------------------------
create database db10;            -- 创建db10数据库
use db10;                               -- 切换到db10数据库
create table dept(                   -- 创建部门表
    deptno int primary key,        -- 部门编号
    deptname varchar(50),        -- 部门名称
    loc varchar(50)                    -- 部门位置
);
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)
);
-- ----------------------------
-- Records of dept 部门表数据
-- ----------------------------
INSERT INTO `dept` VALUES ('10', '会计部', '北京');
INSERT INTO `dept` VALUES ('20', '调查部', '杭州');
INSERT INTO `dept` VALUES ('30', '销售部', '上海');
INSERT INTO `dept` VALUES ('40', '营销部', '深圳');

-- ----------------------------
-- Records of emp 员工表数据
-- ----------------------------
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.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

-- 2.列出薪资比关羽高的所有员工。

-- 3.列出所有员工的姓名及其直接上级的姓名。

-- 4.列出最低薪资大于1500的各种职位及从事此职位的员工人数。

-- 5.列出在销售部职位的员工的姓名，假定不知道销售部的部门编号。

-- 6.列出与曹操从事相同职位的所有员工及部门名称。

-- 7.列出薪资高于在销售部(已知部门编号为30)就职的所有员工的薪资的员工姓名和薪资、部门名称。

-- 8.列出在每个部门职位的员工数量、平均工资。

-- 9.查出至少有一个员工的部门。显示部门人数、部门编号、部门名称、部门位置。

-- 10.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

-- 11.列出所有职员的姓名及其部门名称，部门的人数。

 

 

答案：

-- 1.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
    select * from dept left join emp on dept.deptno=emp.deptno;
-- 2.列出薪资比关羽高的所有员工。
    select * from dept,emp where dept.deptno=emp.deptno;
-- 3.列出所有员工的姓名及其直接上级的姓名。x
    select a.empname as 员工姓名,b.empname as 上级姓名 from emp as a,emp as b where a.mgr=b.empno;
-- 4.列出最低薪资大于1500的各种职位及从事此职位的员工人数。x
    select job,min(sal) as 最低薪资,count(*) as 员工人数 from emp group by job having min(sal)>1500;
-- 5.列出在销售部职位的员工的姓名，假定不知道销售部的部门编号。
    select empname from emp,dept where deptname="销售部";

-- 6.列出与曹操从事相同职位的所有员工及部门名称。x
    select e.empname,p.deptname from emp e,dept p where e.job="推销员" and e.deptno=p.deptno;

-- 7.列出薪资高于在销售部(已知部门编号为30)就职的所有员工的薪资的员工姓名和薪资、部门名称。
        select e.empname,e.sal,d.deptname 
        from emp e,dept d
        where e.deptno=d.deptno and e.sal>
            (select max(sal) 
            from emp
            where deptno=30 
            );
    
-- 8.列出在每个部门职位的员工数量、平均工资。
    select job,count(*),avg(sal)
    from emp 
    group by job;

-- 9.查出至少有一个员工的部门。显示部门人数、部门编号、部门名称、部门位置。
    select count(e.deptno),d.deptno,d.deptname,d.loc
    from emp e,dept d
    where e.deptno=d.deptno 
    
group by e.deptno;

-- 10.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
    select e.empno as 编号,e.empname as 姓名 ,d.deptname as 部门名称
    from emp e,dept d,emp a
    where e.deptno=d.deptno and a.empno=e.mgr and e.hiredate<a.hiredate;

-- 11.列出所有职员的姓名及其部门名称，部门的人数。

员工和所在部门
select e.deptno,e.empname,d.deptname
from emp e,dept d
where e.deptno=d.deptno;

部门名称，部门的人数。
select count(*),job
from emp e
group by e.deptno;

 

 

子模式DDL用来描述数据库的局部逻辑结构

SELECT IF（条件，表达式1，表达式2）条件成立，执行表达式1，否则执行表达式2