Mysql-34 classic query exercises (with answers and analysis)

Time:2021-12-6

First, you need to create and initialize the data of employee table, department table and salary grade table:

DROP TABLE IF EXISTS EMP;

DROP TABLE IF EXISTS DEPT;

DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT (
	DEPTNO int(2) NOT NULL,
	DNAME VARCHAR(14),
	LOC VARCHAR(13),
	PRIMARY KEY (DEPTNO)
);

CREATE TABLE EMP (
	EMPNO int(4) NOT NULL,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE DEFAULT NULL,
	SAL DOUBLE(7, 2),
	COMM DOUBLE(7, 2),
	PRIMARY KEY (EMPNO),
	DEPTNO INT(2)
);

CREATE TABLE SALGRADE (
	GRADE INT,
	LOSAL INT,
	HISAL INT
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (40, 'OPERATIONS', 'BOSTON');

COMMIT;

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17'
	, 800, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20'
	, 1600, 300, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22'
	, 1250, 500, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02'
	, 2975, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28'
	, 1250, 1400, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01'
	, 2850, NULL, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09'
	, 2450, NULL, 10);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19'
	, 3000, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17'
	, 5000, NULL, 10);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08'
	, 1500, 0, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23'
	, 1100, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03'
	, 950, NULL, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03'
	, 3000, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE
	, SAL, COMM, DEPTNO)
VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23'
	, 1300, NULL, 10);

COMMIT;

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (1, 700, 1200);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (2, 1201, 1400);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (3, 1401, 2000);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (4, 2001, 3000);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (5, 3001, 9999);

COMMIT;

SELECT *
FROM emp;

SELECT *
FROM dept;

SELECT *
FROM salgrade;
Employee table:

Department table:

Salary grade table:

1. Name of the person who gets the highest salary in each department

Get the highest salary of the Department first:
  select deptno,max(sal) from emp group  by deptno;    

  Final query statement and result:                                                                                                
  select e.ename,t.* from (select deptno,max(sal) maxsal from emp group by deptno) t
  join emp e on t.deptno = e.deptno and t.maxsal = e.sal;
  +-------+--------+---------+
  | ename | deptno | maxsal  |
  +-------+--------+---------+
  | BLAKE |     30 | 2850.00 |
  | SCOTT |     20 | 3000.00 |
  | KING  |     10 | 5000.00 |
  | FORD  |     20 | 3000.00 |
  +-------+--------+---------+

2. Who’s salary is above the average salary of the Department

Get the average salary of the Department first:
  select deptno,avg(sal) avgsal from emp group by deptno;

  Final query statement and result:   
  select e1.ename,e1.sal,e1.deptno from emp e1 join
  (select deptno,avg(sal) avgsal from emp group by deptno) e2 on e1. deptno = e2.deptno
  where e1.sal > e2.avgsal order by deptno; 
  +-------+---------+--------+
  | ename | sal     | deptno |
  +-------+---------+--------+
  | KING  | 5000.00 |     10 |
  | JONES | 2975.00 |     20 |
  | SCOTT | 3000.00 |     20 |
  | FORD  | 3000.00 |     20 |
  | ALLEN | 1600.00 |     30 |
  | BLAKE | 2850.00 |     30 |
  +-------+---------+--------+

3. Obtain the average salary grade of the Department (everyone)

Get everyone's salary grade first:
  select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;

  Final query statement and result:   
  select t.deptno,avg(t.grade) avgsalgrade from
  (select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) t
  group by t.deptno;
  +--------+-------------+
  | DEPTNO | avgsalgrade |
  +--------+-------------+
  |     10 |      3.6667 |
  |     20 |      2.8000 |
  |     30 |      2.5000 |
  +--------+-------------+

4. Do not use group function (max) to get the highest salary (give two solutions)

The first solution:
  Take the first record in descending order:                                                                                                                                                                          
  select ename,sal from emp order by sal desc limit 1;

  The second solution:
  Self connection: one of the values is not less than any one. After distinct de duplication, use not in to filter the query results, which is the maximum value:
  select ename,sal from emp where sal not in
  (select distinct a.sal from emp a join emp b on a.sal < b.sal);
  +-------+---------+
  | ename | sal     |
  +-------+---------+
  | KING  | 5000.00 |
  +-------+---------+

5. Department number of the Department with the highest average salary

The first scheme:
  Get the average salary of the Department first, and then sort in descending order to get the first data:
  select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
  +--------+-------------+
  | deptno | avgsal      |
  +--------+-------------+
  |     10 | 2916.666667 |
  +--------+-------------+

  The second scheme:
  Get the average salary of each department first:
  select avg(sal) avgsal from emp group by deptno;

  Then take the above query results as a temporary table and use the max grouping function to obtain the maximum value and department number:
  select t.deptno, max(t.avgsal) maxavgsal from
  (select deptno,avg(sal) avgsal from emp group by deptno) t;
  +--------+-------------+
  | deptno | maxavgsal   |
  +--------+-------------+
  |     10 | 2916.666667 |
  +--------+-------------+

6. Department name of the Department with the highest average salary

Get the average salary of each department first:
  select avg(sal) avgsal from emp group by deptno;

  Then take the above query results as a temporary table and use the max grouping function to obtain the maximum value and department number:
  select t.deptno, max(t.avgsal) maxavgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t;

  Finally, connect the table with the Department table and query the Department Name:
  select t.deptno,d.dname, max(t.avgsal) maxavgsal from 
  (select deptno,avg(sal) avgsal from emp group by deptno) t join dept d on t.deptno = d.deptno;
  +--------+------------+-------------+
  | deptno | dname      | maxavgsal   |
  +--------+------------+-------------+
  |     10 | ACCOUNTING | 2916.666667 |
  +--------+------------+-------------+

7. Department name of the Department with the lowest grade for average salary

Calculate the average salary of each department first:
  select deptno,avg(sal) avgsal from emp group by deptno;

  Then calculate the average salary level of each department:
  select t.deptno,s.grade  from (select deptno,avg(sal) avgsal from emp group by deptno) t
  join salgrade s on t.avgsal between s.losal and hisal;

  Finally, use the queried data as a temporary table and a department table to query the Department Name:
  select t1.deptno,d.dname,t1.grade from (select t.deptno,s.grade  from 
  (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s
  on t.avgsal between s.losal and hisal) t1 join dept d on t1.deptno = d.deptno;
  +--------+------------+-------+
  | deptno | dname      | grade |
  +--------+------------+-------+
  |     10 | ACCOUNTING |     4 |
  |     20 | RESEARCH   |     4 |
  |     30 | SALES      |     3 |
  +--------+------------+-------+

8. Get a manager’s name that is higher than the maximum salary of an ordinary employee (the employee code does not appear on Mgr)

First get the maximum salary of all ordinary employees (8 in total):
  select max(e.sal) maxsal from emp e where ename not in
  (select e.ename from emp e join emp e1 on e.empno = e1.mgr);

  Final query statement and result:
  select ename from emp where sal > (select max(e.sal) maxsal from emp e
  where ename not in (select e.ename from emp e join emp e1 on e.empno = e1.mgr));
  +-------+
  | ename |
  +-------+
  | JONES |
  | BLAKE |
  | CLARK |
  | SCOTT |
  | KING  |
  | FORD  |
  +-------+

9. Top five employees with the highest salaries

select * from emp order by sal desc limit 5;
  +-------+-------+-----------+------+------------+---------+------+--------+
  | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
  +-------+-------+-----------+------+------------+---------+------+--------+
  |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
  |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
  |  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
  |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
  |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
  +-------+-------+-----------+------+------------+---------+------+--------+

10. The sixth to tenth highest paid employees

select * from emp order by sal desc limit 5,5;
  +-------+--------+----------+------+------------+---------+--------+--------+
  | EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
  +-------+--------+----------+------+------------+---------+--------+--------+
  |  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |   NULL |     10 |
  |  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
  |  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |   0.00 |     30 |
  |  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |   NULL |     10 |
  |  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
  +-------+--------+----------+------+------------+---------+--------+--------+

11. 5 employees who have obtained the final entry

select * from emp order by hiredate desc limit 5;
  +-------+--------+---------+------+------------+---------+------+--------+
  | EMPNO | ENAME  | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
  +-------+--------+---------+------+------------+---------+------+--------+
  |  7876 | ADAMS  | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
  |  7788 | SCOTT  | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
  |  7934 | MILLER | CLERK   | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
  |  7900 | JAMES  | CLERK   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
  |  7902 | FORD   | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
  +-------+--------+---------+------+------------+---------+------+--------+

12. How many employees get each salary grade

select s.grade,count(1) count from emp e join salgrade s
  on e.sal between s.losal and s.hisal group by grade;
  +-------+-------+
  | grade | count |
  +-------+-------+
  |     1 |     3 |
  |     2 |     3 |
  |     3 |     2 |
  |     4 |     5 |
  |     5 |     1 |
  +-------+-------+

13. Interview questions

There are 3 tables s (student table), C (course table) and SC (student course selection table)
  S (SnO, sname) represents (student number, name) 
  C (CNO, CNAME, cteacher) representative (class number, class name, teacher)
  SC (SnO, CNO, scgrade) representative (student number, class number, grade)
First, you need to create and initialize the data of student table, curriculum table and student course selection table:
DROP TABLE IF EXISTS S;
  create table s(
        sno int(10) primary key auto_increment,
        sname varchar(32)
        );

  DROP TABLE IF EXISTS C;
  create table c(
        cno int(10) primary key auto_increment,
        cname varchar(32),
        cteacher varchar(32)
        );

  //SnO + CNO is a composite primary key, which is related to one
  //SnO is also a foreign key, and CNO is also a foreign key. There are two foreign keys
  DROP TABLE IF EXISTS SC;
  create table sc(
        sno int(10),
    cno int(10),
        scgrade double(3,1),
        constraint sc_sno_cno_pk primary key(sno,cno), 
        constraint sc_sno_fk foreign key(sno) references s(sno),
        constraint sc_cno_fk foreign key(cno) references c(cno)
        );

  Insert into S (SnO, sname) values ('1 ',' student 1 '); 
  Insert into S (SnO, sname) values ('2 ',' student 2 '); 
  Insert into S (SnO, sname) values ('3 ',' student 3 '); 
  Insert into S (SnO, sname) values ('4 ',' student 4 '); 
  commit;

  Insert into C (CNO, CNAME, cteacher) values ('1 ',' Java ',' Teacher Wang '); 
  Insert into C (CNO, CNAME, cteacher) values ('2 ',' C + + ',' Teacher Zhang '); 
  Insert into C (CNO, CNAME, cteacher) values ('3 ',' c# ',' Teacher Li '); 
  Insert into C (CNO, CNAME, cteacher) values ('4 ',' MySQL ',' Teacher Zhao '); 
  Insert into C (CNO, CNAME, cteacher) values ('5 ',' Oracle ',' dawn '); 
  commit;

  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '50'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '50'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '50'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '80');
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '70');  
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '59'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '2', '61'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '99'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '4', '100'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '5', '52'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '4', '3', '82'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '4', '4', '99'); 
  INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '4', '5', '40'); 
  commit;

  SELECT * FROM S;
  SELECT * FROM C;
  SELECT * FROM SC;
S student form:

C curriculum:

SC student course selection table:

13-1. Find out the names of all students who have not chosen “dawn” teacher?

Find out teacher Liming's course number first:
  Select CNO from C where cteacher = 'dawn';

  Then find out the student number of the elective course through the course number:
  Select SnO from SC where CNO = (select CNO from C where cteacher = 'dawn');

  Finally, filter out the students who have selected Liming teacher's course:
  select sname from s where sno not in
  (select SnO from SC where CNO = (select CNO from C where cteacher = 'dawn');
  +---------+
  | sname   |
  +---------+
  |Student 1|
  |Student 2|
  +---------+

13-2. List the names and average scores of students who fail more than 2 (including 2)

First, obtain the student number and average score of more than 2 failed students through student number grouping:
  select sno,avg(scgrade) avggrade,count(1) count from sc where scgrade < 60 group by sno having count >=2;

  Then, the student name and average score can be found through the student number list:
  select  s.sname,t.avggrade from s s join
  (select sno,avg(scgrade) avggrade,count(1) count from sc where scgrade < 60 group by sno having count >=2) t
  on s.sno = t.sno;
  +---------+----------+
  | sname   | avggrade |
  +---------+----------+
  |Student 1 | 50|
  +---------+----------+

13-3. Names of all students who have studied both course 1 and course 2

First obtain the student number of courses 1 and 2:
  select sno from sc where cno = 1 and 2;

  Final query statement and result:
  select s.sname from s s join (select sno from sc where cno = 1 and 2) t on s.sno = t.sno;
  +---------+
  | sname   |
  +---------+
  |Student 1|
  |Student 3|
  +---------+

14. List the names of all employees and leaders

select e.ename,e1.ename leadername from emp e left join emp e1 on e.mgr = e1.empno;
  +--------+------------+
  | ename  | leadername |
  +--------+------------+
  | SMITH  | FORD       |
  | ALLEN  | BLAKE      |
  | WARD   | BLAKE      |
  | JONES  | KING       |
  | MARTIN | BLAKE      |
  | BLAKE  | KING       |
  | CLARK  | KING       |
  | SCOTT  | JONES      |
  | KING   | NULL       |
  | TURNER | BLAKE      |
  | ADAMS  | SCOTT      |
  | JAMES  | BLAKE      |
  | FORD   | JONES      |
  | MILLER | CLARK      |
  +--------+------------+

15. List the number, name and department name of all employees whose employment date is earlier than their direct supervisor

First list the employee number, name and department number of his / her direct supervisor on the date of employment:
  select e.empno, e.ename,e.deptno,e.hiredate,e1.ename leadername,e1.hiredate from emp e
  left join emp e1 on e.mgr = e1.empno where e.hiredate < e1.hiredate;
  +-------+-------+--------+------------+------------+------------+
  | empno | ename | deptno | hiredate   | leadername | hiredate   |
  +-------+-------+--------+------------+------------+------------+
  |  7369 | SMITH |     20 | 1980-12-17 | FORD       | 1981-12-03 |
  |  7499 | ALLEN |     30 | 1981-02-20 | BLAKE      | 1981-05-01 |
  |  7521 | WARD  |     30 | 1981-02-22 | BLAKE      | 1981-05-01 |
  |  7566 | JONES |     20 | 1981-04-02 | KING       | 1981-11-17 |
  |  7698 | BLAKE |     30 | 1981-05-01 | KING       | 1981-11-17 |
  |  7782 | CLARK |     10 | 1981-06-09 | KING       | 1981-11-17 |
  +-------+-------+--------+------------+------------+------------+

  Final query statement and result:
  select t.empno,t.ename,d.dname from (select e.empno, e.ename,e.deptno,e1.ename leadername from emp e
  left join emp e1 on e.mgr = e1.empno where e.hiredate < e1.hiredate) t join dept d on t.deptno = d.deptno;
  +-------+-------+------------+
  | empno | ename | dname      |
  +-------+-------+------------+
  |  7369 | SMITH | RESEARCH   |
  |  7499 | ALLEN | SALES      |
  |  7521 | WARD  | SALES      |
  |  7566 | JONES | RESEARCH   |
  |  7698 | BLAKE | SALES      |
  |  7782 | CLARK | ACCOUNTING |
  +-------+-------+------------+

16. List Department names and employee information of these departments, as well as those departments without employees

select d.deptno,d.dname,e.* from emp e right join dept d on e.deptno = d.deptno order by d.deptno;
  +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
  | deptno | dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
  +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
  |     10 | ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
  |     10 | ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
  |     10 | ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
  |     20 | RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
  |     20 | RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
  |     20 | RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
  |     20 | RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
  |     20 | RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
  |     30 | SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
  |     30 | SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
  |     30 | SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
  |     30 | SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
  |     30 | SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
  |     30 | SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
  |     40 | OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
  +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+

17. List all departments with at least 5 employees

select deptno, count(1) counter from emp group by deptno having counter >= 5;
  +--------+---------+
  | deptno | counter |
  +--------+---------+
  |     20 |       5 |
  |     30 |       6 |
  +--------+---------+

18. List all employees whose salary is more than “Smith”

First find out the salary of 'smth':
  select sal from emp where ename = 'SMITH';

  Final query statement and result:
  select * from emp where sal > (select sal from emp where ename = 'SMITH');
  +-------+--------+-----------+------+------------+---------+---------+--------+
  | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
  +-------+--------+-----------+------+------------+---------+---------+--------+
  |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
  |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
  |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
  |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
  |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
  |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
  |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
  |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
  |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
  |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
  |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
  |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
  |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
  +-------+--------+-----------+------+------------+---------+---------+--------+

19. List the names of all “clerks” (clerks) and their department names and the number of departments

Find out the names of employees and departments whose work is "clerk" according to department grouping:
  select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK';

  Then find out the number of people in each department:
  select count(*) number from emp group by deptno;

  Finally, the above two tables are jointly queried:
  select d1.ename,d1.dname,d2.number from
  (select e.deptno,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK') d1
  join (select deptno,count(*) number from emp group by deptno) d2 on d1.deptno = d2.deptno;
  +--------+------------+--------+
  | ename  | dname      | number |
  +--------+------------+--------+
  | SMITH  | RESEARCH   |      5 |
  | ADAMS  | RESEARCH   |      5 |
  | JAMES  | SALES      |      6 |
  | MILLER | ACCOUNTING |      3 |
  +--------+------------+--------+

20. List various jobs with a minimum salary of more than 1500 and the number of all employees engaged in this job

select job,count(*) peoNumber from emp group by job having min(sal) > 1500;
  +-----------+-----------+
  | job       | peoNumber |
  +-----------+-----------+
  | ANALYST   |         2 |
  | MANAGER   |         3 |
  | PRESIDENT |         1 |
  +-----------+-----------+

21. List the names of employees working in the Department “sales” < Sales Department >, assuming that they do not know the department number of the sales department

First find out the department number of "sales" < Sales Department >:
  select deptno from dept where dname = 'SALES';

  Finally, directly list the names of employees working in the "sales" department through the department number:
  select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
  +--------+
  | ename  |
  +--------+
  | ALLEN  |
  | WARD   |
  | MARTIN |
  | BLAKE  |
  | TURNER |
  | JAMES  |
  +--------+

22. List the salary levels of all employees whose salary is higher than the average salary of the company, their departments, superior leaders and employees

First find out all employees whose salary is higher than the average salary of the company:
  select ename from emp where sal > (select avg(sal) avgsal from emp);

  Then find out all employees whose salary is higher than the average salary of the company. Their department:
  select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno
  where sal > (select avg(sal) avgsal from emp);

  Then find out all employees whose salary is higher than the average salary of the company, their departments and superior leaders:
  select e1.ename,d.dname,e2.ename from emp e1 join dept d on e1.deptno = d.deptno
  left join emp e2 on e1.mgr = e2.empno where e1.sal > (select avg(sal) avgsal from emp);

  Finally, list all employees whose salary is higher than the average salary of the company, their departments, superior leaders and salary grades:
  select e1.ename,d.dname,e2.ename,s.grade from emp e1 join dept d on e1.deptno = d.deptno
  left join emp e2 on e1.mgr = e2.empno join salgrade s on e1.sal between s.losal and s.hisal
  where e1.sal > (select avg(sal) avgsal from emp);
  +-------+------------+-------+-------+
  | ename | dname      | ename | grade |
  +-------+------------+-------+-------+
  | JONES | RESEARCH   | KING  |     4 |
  | BLAKE | SALES      | KING  |     4 |
  | CLARK | ACCOUNTING | KING  |     4 |
  | SCOTT | RESEARCH   | JONES |     4 |
  | KING  | ACCOUNTING | NULL  |     5 |
  | FORD  | RESEARCH   | JONES |     4 |
  +-------+------------+-------+-------+

23. List the names of all employees and departments engaged in the same work as “Scott”

First find out all employees who do the same work as "Scott":
  select ename from emp where job = (select job from emp where ename = 'SCOTT');

  Then combine the Department table to find out the names of all employees and departments engaged in the same work as "Scott":
  select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno
  where job = (select job from emp where ename = 'SCOTT') and ename <> 'SCOTT';
  +-------+----------+
  | ename | dname    |
  +-------+----------+
  | FORD  | RESEARCH |
  +-------+----------+

24. List the names and salaries of other employees whose salaries are equal to those of employees in department 30

Find out the salary of employees in 30 departments first:
  select sal from emp where deptno = 30;

  Final query statement and result:
  select ename,sal from emp where sal in (select sal from emp where deptno = 30) and deptno <> 30;
  Empty set (0.00 sec)

25. List the names, salaries and department names of employees whose salaries are higher than those of all employees working in department 30

First find out the highest salary of all employees working in department 30:
  select max(sal) maxsal from emp where deptno = 30;

  Then find out the name and salary of the employee whose salary is higher than the above salary:
  select ename,sal from emp where sal > (select max(sal) maxsal from emp where deptno = 30) and deptno <> 30;

  Finally, find out the employee's name, salary and department name whose salary is higher than that of all employees working in department 30:
  select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno
  where e.sal > (select max(sal) maxsal from emp where deptno = 30) and e.deptno <> 30;
  +-------+---------+------------+
  | ename | sal     | dname      |
  +-------+---------+------------+
  | JONES | 2975.00 | RESEARCH   |
  | SCOTT | 3000.00 | RESEARCH   |
  | KING  | 5000.00 | ACCOUNTING |
  | FORD  | 3000.00 | RESEARCH   |
  +-------+---------+------------+

26. List the number of employees working in each department, average salary and average service period (*)

//The functions involved include: ifnull (field, 0) DateDiff (now()), date) right join
  select d.deptno,ifnull(count(e.ename),0) peonumber,ifnull(avg(e.sal),0) avgsal,
  ifnull(avg(datediff(now(),e.hiredate)/365),0) servertime from emp e right join dept d
  on e.deptno = d.deptno group by e.deptno order by deptno;
  +--------+-----------+-------------+-------------+
  | deptno | peonumber | avgsal      | servertime  |
  +--------+-----------+-------------+-------------+
  |     10 |         3 | 2916.666667 | 39.21186667 |
  |     20 |         5 | 2175.000000 | 37.23890000 |
  |     30 |         6 | 1566.666667 | 39.51051667 |
  |     40 |         0 |    0.000000 |  0.00000000 |
  +--------+-----------+-------------+-------------+

27. List the names, Department names and salaries of all employees

select e.ename,d.dname,e.sal from emp e join dept d on e.deptno = d.deptno;
  +--------+------------+---------+
  | ename  | dname      | sal     |
  +--------+------------+---------+
  | SMITH  | RESEARCH   |  800.00 |
  | ALLEN  | SALES      | 1600.00 |
  | WARD   | SALES      | 1250.00 |
  | JONES  | RESEARCH   | 2975.00 |
  | MARTIN | SALES      | 1250.00 |
  | BLAKE  | SALES      | 2850.00 |
  | CLARK  | ACCOUNTING | 2450.00 |
  | SCOTT  | RESEARCH   | 3000.00 |
  | KING   | ACCOUNTING | 5000.00 |
  | TURNER | SALES      | 1500.00 |
  | ADAMS  | RESEARCH   | 1100.00 |
  | JAMES  | SALES      |  950.00 |
  | FORD   | RESEARCH   | 3000.00 |
  | MILLER | ACCOUNTING | 1300.00 |
  +--------+------------+---------+

28. List the details and number of people in all departments

select d.*,count(e.ename) peoNumber from dept d left join emp e 
  on d.deptno = e.deptno group by e.deptno order by d.deptno;
  +--------+------------+----------+-----------+
  | DEPTNO | DNAME      | LOC      | peoNumber |
  +--------+------------+----------+-----------+
  |     10 | ACCOUNTING | NEW YORK |         3 |
  |     20 | RESEARCH   | DALLAS   |         5 |
  |     30 | SALES      | CHICAGO  |         6 |
  |     40 | OPERATIONS | BOSTON   |         0 |
  +--------+------------+----------+-----------+

29. List the minimum wage for various jobs and the names of employees engaged in such jobs

First find out the minimum wage for various jobs:
  select job,min(sal) minSal from emp group by job;

  Final query statement and result:
  select ename,job,sal from emp where sal in (select min(sal) minSal from emp group by job);
  +--------+-----------+---------+
  | ename  | job       | sal     |
  +--------+-----------+---------+
  | SMITH  | CLERK     |  800.00 |
  | WARD   | SALESMAN  | 1250.00 |
  | MARTIN | SALESMAN  | 1250.00 |
  | CLARK  | MANAGER   | 2450.00 |
  | SCOTT  | ANALYST   | 3000.00 |
  | KING   | PRESIDENT | 5000.00 |
  | FORD   | ANALYST   | 3000.00 |
  +--------+-----------+---------+

30. List the minimum salary of managers in each department

select deptno,min(sal) minSal from emp where job = 'MANAGER' group by deptno;
  +--------+---------+
  | deptno | minSal  |
  +--------+---------+
  |     10 | 2450.00 |
  |     20 | 2975.00 |
  |     30 | 2850.00 |
  +--------+---------+

31. List the annual salary of all employees, in order of annual salary from low to high

select ename,ifnull(sal,0) * 12 annualSal from emp order by annualSal;
  +--------+-----------+
  | ename  | annualSal |
  +--------+-----------+
  | SMITH  |   9600.00 |
  | JAMES  |  11400.00 |
  | ADAMS  |  13200.00 |
  | WARD   |  15000.00 |
  | MARTIN |  15000.00 |
  | MILLER |  15600.00 |
  | TURNER |  18000.00 |
  | ALLEN  |  19200.00 |
  | CLARK  |  29400.00 |
  | BLAKE  |  34200.00 |
  | JONES  |  35700.00 |
  | SCOTT  |  36000.00 |
  | FORD   |  36000.00 |
  | KING   |  60000.00 |
  +--------+-----------+

32. Find out the names of employees and leaders whose salary exceeds 3000

select e1.ename,e2.ename,e2.sal from emp e1 join emp e2 on e1.mgr = e2.empno where e2.sal > 3000;
  +-------+-------+---------+
  | ename | ename | sal     |
  +-------+-------+---------+
  | JONES | KING  | 5000.00 |
  | BLAKE | KING  | 5000.00 |
  | CLARK | KING  | 5000.00 |
  +-------+-------+---------+

33. Calculate the total salary and number of department employees with “s” character in the Department name

select d.dname,ifnull(sum(e.sal),0) sumSal,count(e.ename) peoNumber from emp e right join dept d
  on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno;
  +------------+----------+-----------+
  | dname      | sumSal   | peoNumber |
  +------------+----------+-----------+
  | RESEARCH   | 10875.00 |         5 |
  | SALES      |  9400.00 |         6 |
  | OPERATIONS |     0.00 |         0 |
  +------------+----------+-----------+

34. Give 10% salary increase to employees who have served for more than 30 years

Identify employees who have served for more than 30 years and get a 10% raise:
  update emp set sal = sal * 1.1 where datediff(now(),hiredate)/365 > 30;

The answers of the test questions have been verified, but the solution may not be the best because of my work. If there is a better tuning scheme, please comment and exchange. Thank you(╹▽╹)!

Recommended Today

Swift FAQ

1、 Foundation 1. What is the difference between class and struct? a. Struct will automatically generate the required constructor. The constructor with which attribute is not assigned an initial value will be generated with which attribute as the parameter. But class doesn’t. write it yourself struct StructTest { var name:String var age:Int } class ClassTest […]