数据库四(习题练习)

1)员工信息表emp

字段中文名字依次是:工号,姓名,工作岗位,部门经理,受雇日期,薪金,奖金,部门编号
在这里插入图片描述

2)部门信息表dept

字段中文名字依次是:部门编号、部门名称、所在地

在这里插入图片描述

练习题目

1. 列出至少有4个员工的所有部门编号和名称。

  • 方法一:
    select deptno,dname from dept where
    deptno in(select deptno from (select count(*) count,deptno
    from emp group by deptno) dn where dn.count>=4);
  • 方法二:
    select s.deptno,dept.DNAME
    from (select deptno,count(*) as sum from emp group by deptno having sum>=4) as s
    left join dept
    on s.deptno=dept.DEPTNO;
    在这里插入图片描述

2. 列出薪金比“SMITH”多的所有员工。

  • select empno,ename from emp where
    sal>(select sal from emp where ename=“SMITH”);
    在这里插入图片描述

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

  • select emp1.ename,emp.ename mgrname from emp emp1 inner join
    emp on emp1.mgr=emp.empno;
    在这里插入图片描述

4. 列出受雇日期早于其直接上级的所有员工。

  • 方法一:
    select * from (select emp1.ename name,emp1.hiredate time,emp.ename as mgrname,emp.hiredate as mgrtime from emp emp1
    inner join emp
    on emp1.mgr=emp.empno) s where s.time<s.mgrtime;
  • 方法二:
    select e.EMPNO,e.HIREDATE as time,emp.HIREDATE as date from emp as e
    left join
    emp
    on e.MGR=emp.EMPNO having time<date;
    在这里插入图片描述

5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。

  • select * from dept left join emp
    on dept.DEPTNO=emp.deptno;

在这里插入图片描述

6. 列出所有“CLERK”(办事员)的姓名及其部门名称。

  • 方法一:
    select ename,dname from emp,dept where
    emp.deptno=dept.deptno and emp.job=“CLERK”;
  • 方法二:
    select ename,dname from (select * from emp where job=“CLERK”) as emp
    left join dept
    on emp.DEPTNO=dept.DEPTNO;

在这里插入图片描述

7. 列出最低薪金大于1500的各种工作。

  • select s.job from
    (select min(sal) min,job from emp group by job having min>1500) s;
    在这里插入图片描述

8. 列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号

  • select ename from emp
    where emp.deptno=(select deptno from dept where dept.dname=“SALES”);
    在这里插入图片描述

9. 列出薪金高于公司平均薪金的所有员工。

  • select ename from emp where sal>(select avg(sal) from emp);

在这里插入图片描述

10.列出与“SCOTT”从事相同工作的所有员工。

  • select * from emp where job=(select job from emp where ename=“SCOTT”) and ename<>“SCOTT”;
    在这里插入图片描述

11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

  • 方法一:
    select ename,sal from emp where sal in (select sal from emp where deptno=30);
  • 方法二:
    select DISTINCT * from emp
    inner join (select sal from emp where deptno=30) as e
    on emp.sal=e.sal;
    在这里插入图片描述

12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

  • select * from emp where sal>(select max(sal) from emp where deptno=30);
    在这里插入图片描述

13.列出在每个部门工作的员工数量、平均工资和平均服务期限。

  • select count(*),avg(sal),avg(DATEDIFF(CURRENT_DATE,HIREDATE))from emp group by deptno;
    在这里插入图片描述

14.列出所有员工的姓名、部门名称和工资。

  • select ename,dname,sal + case when comm is null then 0 else comm end as money from emp
    left join
    dept
    on emp.deptno=dept.deptno;
    在这里插入图片描述

15.列出所有部门的详细信息和部门人数。

  • select * from dept
    left join
    (select count(*) count,deptno from emp group by deptno) s
    on s.deptno=dept.deptno;
    在这里插入图片描述

16.列出各种工作的最低工资。

  • 方法一:
  • 方法二:

17.列出各个部门的MANAGER(经理)的最低薪金。

  • 方法一:
    select * from
    (select deptno,min(sal) from emp where job=“MANAGER” group by deptno ) as s left join
    dept
    on s.deptno=dept.deptno;
  • 方法二:
    select * from (select * from emp where job=“MANAGER”) as emp
    where sal=(
    select min(sal) from (select * from emp where job=“MANAGER”) as e where emp.DEPTNO=e.deptno);

在这里插入图片描述

18.列出所有员工的年工资,按年薪从低到高排序。

  • 方法一:
    select s.ename ,s.money from
    (select ename,12 *sal + case when comm is null then 0 else comm end money from emp) s order by s.money;
  • 方法二:
    select ename,12*sal+case when comm is null then 0 else comm end as money
    from emp ORDER BY money;
    在这里插入图片描述

思考: 列出每个部门薪水前两名最高的人员名称以及薪水

  • 方法一:
    select emp1.ename,emp1.sal,emp1.deptno from emp emp1 where
    (select count(*) from emp where emp.deptno=emp1.deptno and emp1.sal<emp.sal)<2;

  • 方法二:
    select * from emp
    where 2>(
    select count(*) from emp as e where emp.DEPTNO=e.deptno and emp.sal<e.sal);

在这里插入图片描述

注意:

  • 字段相加有一个为null,所加结果为null
  • 如果是表中原有的字段名称用where,聚合函数求出的,例如count,age重起名后期需要判断用having。
  • 逻辑and和逻辑or表达式,其操作数是从左到右求值的,如果第一个参数能判断结果,后面结果将不会执行。
  • where是对已经搜索出的结果的过滤条件
  • 在on的条件下,and和or与on同时对前面的集合(表)起作用,而存在where的时候,先对集合进行on的抽取连接再根据where进行筛选。

附录代码

-- 建立员工信息表emp
DROP TABLE IF EXISTS  `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` varchar(10) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` int(7) DEFAULT NULL,
  `COMM` int(7) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values
('7369','SMITH','CLERK','7902','1980-12-17','800',null,'20'),
('7499','ALLEN','SALESMAN','7698','1981-02-20','1600','300','30'),
('7521','WARD','SALESMAN','7698','1981-02-22','1250','500','30'),
('7566','JONES','MANAGER','7839','1981-04-02','2975',null,'20'),
('7654','MARTIN','SALESMAN','7698','1981-09-28','1250','1400','30'),
('7698','BLAKE','MANAGER','7839','1981-05-01','2850',null,'30'),
('7782','CLARK','MANAGER','7839','1981-06-09','2450',null,'10'),
('7788','SCOTT','ANALYST','7566','1987-04-19','3000',null,'20'),
('7839','KING','PRESIDENT',null,'1981-11-17','5000',null,'10'),
('7844','TURNER','SALESMAN','7698','1981-09-08','1500','0','30'),
('7876','ADAMS','CLERK','7788','1987-05-23','1100',null,'20'),
('7900','JAMES','CLERK','7698','1981-12-03','950',null,'30'),
('7902','FORD','ANALYST','7566','1981-12-03','3000',null,'20'),
('7934','MILLER','CLERK','7782','1982-01-23','1300',null,'10');

-- 建立部门信息表dept
DROP TABLE IF EXISTS  `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据
insert into `dept`(`DEPTNO`,`DNAME`,`LOC`) values
('10','ACCOUNTING','NEW YORK'),
('20','RESEARCH','DALLAS'),
('30','SALES','CHICAGO'),
('40','OPERATIONS','BOSTON');

-- 查询表
select * from emp;
select * from dept;

-- 1. 列出至少有4个员工的所有部门编号和名称。
select deptno,dname from dept where 
deptno in(select deptno from (select count(*) count,deptno  
from emp group by deptno) dn where dn.count>=4);

select s.deptno,dept.DNAME
from (select deptno,count(*) as sum from emp group by deptno having sum>=4) as s
left join dept
on s.deptno=dept.DEPTNO;

-- 2. 列出薪金比“SMITH”多的所有员工。
select empno,ename from emp where 
sal>(select sal from emp where ename="SMITH");

-- 3. 列出所有员工的姓名及其直接上级的姓名。
select emp1.ename,emp.ename mgrname from emp emp1  inner join 
emp on emp1.mgr=emp.empno;

select e.EMPNO,e.ENAME,emp.ENAME from emp as e
left join
emp
on e.MGR=emp.EMPNO;

– 4. 列出受雇日期早于其直接上级的所有员工。
select * from (select emp1.ename name,emp1.hiredate time,emp.ename as mgrname,emp.hiredate as mgrtime from emp emp1
inner join emp
on emp1.mgr=emp.empno) s where s.time<s.mgrtime;

select e.EMPNO,e.HIREDATE as time,emp.HIREDATE as date from emp as e
left join
emp
on e.MGR=emp.EMPNO having time<date;

-- 5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
select * from dept left join emp 
on dept.DEPTNO=emp.deptno;

-- 6. 列出所有“CLERK”(办事员)的姓名及其部门名称。
select ename,dname from emp,dept where 
emp.deptno=dept.deptno and emp.job="CLERK";

select ename,dname from (select * from emp where job="CLERK") as emp
left join dept
on emp.DEPTNO=dept.DEPTNO;
-- 7. 列出最低薪金大于1500的各种工作。
select s.job from
(select min(sal) min,job from emp group by job having  min>1500) s;
-- 8. 列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号
select ename from emp 
where emp.deptno=(select deptno from dept where dept.dname="SALES");
-- 9. 列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal>(select avg(sal) from emp);
-- 10.列出与“SCOTT”从事相同工作的所有员工。 
select * from emp where job=(select job from emp where ename="SCOTT") and ename<>"SCOTT";
-- 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename,sal from emp where sal in (select sal from emp where deptno=30);

select DISTINCT * from emp
inner join (select sal from emp where deptno=30) as e
on emp.sal=e.sal;
-- 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
select * from emp where sal>(select max(sal) from emp where deptno=30);
-- 13.列出在每个部门工作的员工数量、平均工资和平均服务期限。 
 select count(*),avg(sal),avg(DATEDIFF(CURRENT_DATE,HIREDATE))from emp group by deptno;
-- 14.列出所有员工的姓名、部门名称和工资。
select ename,dname,sal + case when comm is null then 0 else comm end as money from emp
left join
dept
on emp.deptno=dept.deptno;

-- 15.列出所有部门的详细信息和部门人数。 
select * from dept 
left join 
(select count(*) count,deptno from emp group by deptno) s
on s.deptno=dept.deptno;

-- 16.列出各种工作的最低工资。 
select job,min(money) from (
select job,sal+case when comm is null then 0 else comm end money from emp)
s group by s.money;

-- 17.列出各个部门的MANAGER(经理)的最低薪金。 
select * from
(select deptno,min(sal) from emp where job="MANAGER" group by deptno )  as s left join 
dept 
on s.deptno=dept.deptno;

select * from (select * from emp where job="MANAGER") as emp
where sal=(
select min(sal) from (select * from emp where job="MANAGER") as e where emp.DEPTNO=e.deptno);
-- 18.列出所有员工的年工资,按年薪从低到高排序。
select s.ename ,s.money from 
(select ename,12 *sal + case when comm is null then 0 else  comm end money from emp) s order by s.money;

select ename,12*sal+case when comm is null then 0 else comm end as money 
from emp ORDER BY money;

– 思考: 列出每个部门薪水前两名最高的人员名称以及薪水
select emp1.ename,emp1.sal,emp1.deptno from emp emp1 where
(select count(*) from emp where emp.deptno=emp1.deptno and emp1.sal<emp.sal)<2;

select * from emp
where 2>(
select count(*) from emp as e where emp.DEPTNO=e.deptno and emp.sal<e.sal);

java
java入门基础学习(一)
java入门基础学习(二)
java入门基础学习(三)
java入门基础学习(四)
java入门基础学习(五)
java入门基础学习(六)
java入门基础学习(七)
java入门基础学习(八)
java入门基础学习(九)
java入门基础学习(十)
java入门基础学习(十一)
java入门基础学习(十二)
java入门基础学习(十三)
java入门基础学习(十四)Maven Git
java总结,题目+笔记
java进阶之常见对象(一)
java进阶之常见对象(二)
java进阶之冒泡排序
java进阶之选择排序
java进阶之面向对象(封装)
java进阶之面向对象(代码块、继承)
java进阶之面向对象(多态、抽象、接口)
java进阶之匿名内部类、访问修饰符、包
java进阶之io流(字节流,字符流)
Linux
Linux基础一
Linux基础二
Mysql
mysql一
mysql二
mysql三

  • 1
    点赞
  • 26
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库领域,T-SQL(Transact-SQL)是Microsoft SQL Server使用的标准查询语言和过程定义语言。对于T-SQL练习题,你可以从以下几个方面入手: 1. **基础查询**:练习基本的SELECT语句,包括选择、过滤、排序和分组等操作。例如,创建简单的表,然后查询特定列、使用WHERE子句、GROUP BY和ORDER BY。 2. **聚合函数**:学习和应用COUNT、SUM、AVG、MAX和MIN等聚合函数,用于计算数据集中的统计信息。 3. **JOIN操作**:理解INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN,以及如何连接多个表以获取所需数据。 4. **子查询与嵌套查询**:编写子查询来解决问题,比如查找满足条件的最大值或最小值的行。 5. **存储过程与触发器**:了解如何创建和调用存储过程,以及如何使用触发器来自动执行某些操作。 6. **事务与索引**:理解事务的概念,以及如何创建和优化数据库索引以提高查询性能。 7. **参数化查询与动态SQL**:学习如何使用参数化查询防止SQL注入,以及如何使用动态SQL根据输入创建灵活的SQL语句。 8. **视图与临时表**:创建和使用视图来简化复杂的查询,以及临时表在处理一次性数据时的用途。 9. **错误处理与异常处理**:掌握如何使用TRY...CATCH语句处理程序运行时的错误。 10. **SQL Server Management Studio (SSMS) 使用**:熟悉SSMS的各种功能,如查询编辑器、查询计划查看等。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值