Database (mysql) query statement exercise (–)

Pre observation tips

The database of this query exercise comes from the Internet, you can baidu yourself!!!!

database

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int(11) NOT NULL COMMENT 'Department number',
  `DNAME` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Department name',
  `LOC` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Department address',
  PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL COMMENT 'number',
  `ENAME` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'full name',
  `JOB` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Type of work',
  `MGR` double DEFAULT NULL COMMENT 'Superior number',
  `HIREDATE` date DEFAULT NULL COMMENT 'birthday',
  `SAL` double DEFAULT NULL COMMENT 'salary',
  `COMM` double DEFAULT NULL COMMENT 'bonus',
  `DEPTNO` int(11) DEFAULT NULL COMMENT 'Department number',
  PRIMARY KEY (`EMPNO`) USING BTREE,
  KEY `DEPTNO` (`DEPTNO`) USING BTREE,
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('2222', 'Dfds', 'dsfcds', '56454', '1988-02-21', '546464', '888', '40');
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-07-13', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-07-13', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `GRADE` int(11) DEFAULT NULL COMMENT 'Grade number',
  `LOSAL` double DEFAULT NULL COMMENT 'minimum wages',
  `HISAL` double DEFAULT NULL COMMENT 'ceiling on wages'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

subject

– 1. Query the names, employee numbers and department names of all employees of the Department in NEW YORK.

– 3. Query the employee name ename, salary sal, department number, department name and salary level whose manager is black from the personnel table.

– 6. Query the Department name of each department and the lowest salary of employees in each department.

– 7. Query the names, wages and department names of all employees whose wages are greater than 1800 in each department, and arrange them in ascending order according to the names of employees.

– 8. Query the information of employees who are employed in the same year and month as black, their department information and their salary level information

– 10. Count the total wages of employees in each department, and display the Department name and total wages (including employees without departments).

– 11. Count how many departments have been put into operation, and display the department number, name, location and number of departments (i.e. departments with employees)

– 12. Query the name and number of departments with more than 3 people in each department.

– 13. Query the name and average salary of the Department not located in 'NEW YORK'.

– 14. Query all the employee information of the Department where employees joined on December 3, 1981

– 15. Query the number of people engaged in each position in each department, display the Department name, position name (excluding departments without employees) and the number of people engaged in the position, and sort in ascending order by department name, position name and the number of people engaged in the position.

– 16. Query all the information of the employee with the longest employment time in each department.

– 17. Query the name of the Department with the highest average salary in each department (except those without Department).

– 18. List the names and salaries of employees whose salaries are higher than those of all employees working in department 30, and the name of the Department.

– 19. Display the names of the first two departments with the largest number of departments and the number of employees.

– 20. List the names and department numbers of employees whose salary is higher than the average salary of their department.

answer

– 1. Query the names, employee numbers and department names of all employees of the Department in NEW YORK.
select e.ename,e.empno,d.loc from emp e join dept d on e.DEPTNO=d.DEPTNO where d.LOC='NEW YORK';

– 2. Find the name and average salary of the Department whose average salary is higher than 2000. (employees with salary less than 1000 are not included in the statistical range, and the results are sorted in descending order of average salary)
select dept.dname,avg(sal) from emp join dept on emp.deptno=dept.deptno where empno in
(select empno from emp where sal >1000)
group by emp.DEPTNO having avg(sal) >2000 order by avg(sal) desc;

– 3. Query the employee name ename, salary sal, department number, department name and salary level whose manager is black from the personnel table.
select DISTINCT e.ename,e.sal,e.deptno,d.dname,s.GRADE from dept d,emp e,salgrade s where d.deptno=e.deptno and e.mgr=(select empno from emp where ename='BLAKE')
AND e.sal BETWEEN s.LOSAL and s.HISAL;

– 4. Query the names of employees whose wages are more than 2000 yuan in each department, their wages and the names of their departments, and sort them in ascending order. If the wages are equal, sort them in descending order by employee names
select e.ename,e.sal,d.dname from emp e join dept d on e.DEPTNO=d.DEPTNO where sal>2000 order by sal asc,e.ename desc;

– 5. Query the employee number, employee name, employment time, department number and supervisor number, supervisor name, supervisor employment time and supervisor department number of the employee and supervisor who have joined in the same year and whose salary is lower than the supervisor's salary.
select e.empno,e.ename,e.hiredate,e.deptno,e1.empno,e1.ename,e1.hiredate,e1.deptno from emp e join emp e1 on e.mgr = e1.empno
where YEAR(e.hiredate) = YEAR(e1.hiredate) and e.sal < e1.sal

– 6. Query the Department name of each department and the lowest salary of employees in each department.
select d.dname,min(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno;

– 7. Query the names, wages and department names of all employees whose wages are greater than 1800 in each department, and arrange them in ascending order according to the names of employees.
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where sal>1800 order by e.ename;

– 8. Query the information of employees who are employed in the same year and month as black, their department information and their salary level information
select emp.*,dept.dname,salgrade.GRADE from emp,dept,salgrade where emp.deptno=dept.deptno and YEAR(hiredate)=(select YEAR(hiredate) from emp where ename='BLAKE')
and month(hiredate)=(select month(hiredate) from emp where ename='BLAKE')
and emp.sal BETWEEN salgrade.LOSAL and salgrade.HISAL;

– 9. Query the names, entry time and department names (including employees without departments) of all employees whose entry date is between 'May 1, 1981' and 'May 1, 1985', and sort them in ascending order by employee names.
select e.ename,e.hiredate,d.dname from emp e join dept d on e.deptno=d.deptno where e.hiredate between '1981-5-1' and '1985-5-1' order by e.ename asc;

– 10. Count the total wages of employees in each department, and display the Department name and total wages (including employees without departments).
select d.dname,sum(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno;

– 11. Count how many departments have been put into operation, and display the department number, name, location and number of departments (i.e. departments with employees)
select dept.*,count(emp.empno) from emp,dept where dept.deptno=emp.deptno GROUP BY emp.deptno

– 12. Query the name and number of departments with more than 3 people in each department.
select d.dname,count(e.empno) 'number of people' from emp e join dept d on e.deptno=d.deptno group by e.deptno having > 3;

– 13. Query the name and average salary of the Department not located in 'NEW YORK'.
select d.dname,avg(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno having e.DEPTNO not in(select deptno from dept where loc='NEW YORK');

– 14. Query all the employee information of the Department where employees joined on December 3, 1981
select * from emp where deptno in (select deptno from emp where hiredate='1981-12-03');

– 16. Query all the information of the employee with the longest employment time in each department.
select *from emp e join dept d on e.deptno=d.deptno group by e.deptno having max(datediff(now(),hiredate));

– 17. Query the name of the Department with the highest average salary in each department (except those without Department).
select avg(e.sal),d.dname from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avg(e.sal) desc limit 1,1;

– 18. List the names and salaries of employees whose salaries are higher than those of all employees working in department 30, and the name of the Department.
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno
where e.sal>all (select sal from emp where deptno=30);

– 19. Display the names of the first two departments with the largest number of departments and the number of employees.
select d.dname,count(e.deptno) 'number of employees' from emp e join dept d on e.deptno=d.deptno group by e.deptno order by number of employees desc limit 0,2;

– 20. List the names and department numbers of employees whose salary is higher than the average salary of their department.
select e.ename,e.deptno from emp e join (select deptno,avg(sal) avgsal from emp group by deptno) tab on e.deptno = tab.deptno where e.sal > tab.avgsal

Keywords: MySQL

Added by Revlet on Thu, 10 Feb 2022 02:34:12 +0200

Programming VIP

Database (mysql) query statement exercise (–)

Pre observation tips

database

subject

answer

Popular Keywords