sql查询语句总结练习

首先创建表格‘dept’和‘emp’并插入数据记录。

代码：

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50527
Source Host           : localhost:3306
Source Database       : wr_day01_mdb1

Target Server Type    : MYSQL
Target Server Version : 50527
File Encoding         : 65001

Date: 2019-07-24 08:00:31
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` varchar(255) NOT NULL DEFAULT '',
  `dname` varchar(255) DEFAULT NULL,
  `loc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`deptno`),
  KEY `deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', '教学部', '济南');
INSERT INTO `dept` VALUES ('20', '实训中心', '莱芜');
INSERT INTO `dept` VALUES ('30', '就业部', '济南');
INSERT INTO `dept` VALUES ('40', '市场部', '济南');
INSERT INTO `dept` VALUES ('50', '总经理办公室', '北京');

-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` varchar(20) NOT NULL,
  `ename` varchar(20) NOT NULL,
  `job` varchar(20) DEFAULT NULL,
  `mgr` varchar(20) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `comm` int(11) DEFAULT NULL,
  `deptno` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1001', '李军', '总经理', null, '2018-07-01', '15000', '2000', '50');
INSERT INTO `emp` VALUES ('1002', '赵旭', '班主任', '1003', '2018-07-02', '3000', '2000', '20');
INSERT INTO `emp` VALUES ('1003', '李萌', '实训中心主任', '1001', '2018-06-22', '4000', '2000', '20');
INSERT INTO `emp` VALUES ('1004', '刘枫', '教学总监', '1001', '2018-07-10', '12000', '2000', '10');
INSERT INTO `emp` VALUES ('1005', '王涛', '嵌入式讲师', '1004', '2018-07-10', '5000', '2000', '10');

 

 接下来时sql查询语句的练习题目：

注：读者可根据具体题目修改数据库数据内容，以便查询出相应的结果集。

1.列出至少有一个员工的所有部门。

SELECT dname FROM dept where deptno IN (SELECT DISTINCT deptno FROM emp)

 

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

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = '王涛')

 

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

SELECT e.ename '员工',IFNULL(b.ename,'boss') '上司' FROM  emp e LEFT JOIN emp b ON e.mgr = b.empno

 

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

SELECT e.* FROM emp e LEFT JOIN emp b ON e.mgr = b.empno WHERE e.hiredate < b.hiredate

 

5.列出部门名称和这些部门的员工信息，包括那些没有员工的部门。

SELECT dept.dname,emp.* FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno

 

6.列出所有job为“CLERK”（办事员）的姓名及其部门名称。

SELECT ename,dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = 'CLERK'

 

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

SELECT job FROM emp GROUP BY job HAVING MIN(sal)>1500

 

8.列出在部门“教学部”工作的员工的姓名，假定不知道教学部的部门编号。

SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = '教学部')

 

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

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)

 

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

SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = '刘枫')

 

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

SELECT ename,sal FROM emp WHERE sal = ANY(SELECT sal FROM emp WHERE deptno = '10')

 

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

SELECT ename,sal FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = '30')

 

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

SELECT dname,COUNT(empno),AVG(sal),AVG( TIMESTAMPDIFF(DAY,hiredate,NOW())) FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno      

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

SELECT ename,dname,sal FROM emp INNER JOIN dept ON emp.deptno = dept.deptno

 

15.列出从事同一种工作但属于不同部门的员工的一种组合。

SELECT e1.ename,e1.job,e1.deptno,e2.ename,e2.job,e2.deptno FROM emp e1 CROSS JOIN emp e2 WHERE e1.job = e2.job AND e1.deptno != e2.deptno LIMIT 1

 

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

SELECT DISTINCT dept.deptno,dname,loc,COUNT(empno)'部门人数' FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno

 

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

SELECT job,MIN(sal) FROM emp GROUP BY job

 

18.列出各个部门的MANAGER（经理）的最低薪金（job为MANAGER）。

SELECT dept.dname,MIN(sal)'该部门经理的最低薪 资'  FROM dept LEFT JOIN (SELECT * FROM emp WHERE job = '经理') m on dept.deptno = m.deptno GROUP BY dept.deptno

 

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

SELECT ename,(sal*12) FROM emp ORDER BY (sal*12) DESC

sql语句如果有误误，望读者指出，谢谢。