MySQL homework questions
Three tables
mysql> select * from emp; // Query all data from the emp table* On behalf of all +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 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 | +-------+--------+-----------+------+------------+---------+---------+--------+ mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ mysql> select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
1. Name of the person who gets the highest salary in each department
I didn't expect to write the first one for a long time. It's harmful!
I wrote it myself
First find the maximum salary of each part, form a table, and alias it as n (no alias error)
Join employee table and table n
Find out these four people according to the equal salary
After watching the video:
How come I don't have the on statement of table connection... The answer should be lucky
mysql> select e.ename,d.* from emp e
-> join (select deptno,max(sal) maxsal from emp group by deptno) d
-> on e.deptno = d.deptno and e.sal = d.maxsal;
+-------+--------+---------+
| ename | deptno | maxsal |
+-------+--------+---------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.01 sec)
2. Who is paid above the average salary of the Department
According to the idea of the previous question, first find the average salary of the Department and form a table n
mysql> select avg(sal),deptno
-> from emp
-> group by deptno;
+-------------+--------+
| avg(sal) | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.000000 | 20 |
| 1566.666667 | 30 |
+-------------+--------+
3 rows in set (0.02 sec)
Then, in table e, find the people who have the same department but whose salary is greater than the average salary, and they will go up soon
mysql> select e.ename,e.sal -> from emp e -> join (select avg(sal) sal,deptno from emp group by deptno) n -> on e.sal > n.sal and e.deptno = n.deptno; +-------+---------+ | ename | sal | +-------+---------+ | ALLEN | 1600.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ 6 rows in set (0.03 sec)
3. Get the average salary grade (of everyone) in the Department
The first step is to get everyone's salary grade
mysql> select e.ename,e.deptno,sg.grade -> from emp e -> join salgrade sg -> on e.sal between sg.losal and sg.hisal; +--------+--------+-------+ | ename | deptno | grade | +--------+--------+-------+ | SMITH | 20 | 1 | | ALLEN | 30 | 3 | | WARD | 30 | 2 | | JONES | 20 | 4 | | MARTIN | 30 | 2 | | BLAKE | 30 | 4 | | CLARK | 10 | 4 | | SCOTT | 20 | 4 | | KING | 10 | 5 | | TURNER | 30 | 3 | | ADAMS | 20 | 1 | | JAMES | 30 | 1 | | FORD | 20 | 4 | | MILLER | 10 | 2 | +--------+--------+-------+ 14 rows in set (0.00 sec)
The second step is to average the grades in this table by department
mysql> select deptno, avg(grade)
-> from (select e.ename, e.deptno, sg.grade
-> from emp e
-> join salgrade sg
-> on e.sal between sg.losal and sg.hisal) as n
-> group by
-> deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+------------+
3 rows in set (0.00 sec)
After watching the video:
After the table connection, it is not necessary to treat it as a temporary table, but directly group it later
mysql> select e.deptno, avg(s.grade)
-> from emp e
-> join salgrade s
-> on e.sal between s.losal and s.hisal
-> group by deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)
4. Do not use group function (Max) to get the highest salary (give two solutions)
Method 1:
The first reaction is to form a table after sorting, and then take the first row
Or in ascending order, count the quantity, and then find the last one
mysql> select sal from emp order by sal desc -> limit 0,1; +---------+ | sal | +---------+ | 5000.00 | +---------+ 1 row in set (0.00 sec)
Method 2:
I can't think of it. Look at the answer
God's operation a.sal < b.sal, how to take it, that is, for a value in B, get all smaller than it in a,
Then the maximum value will not be displayed in this table
mysql> select a.sal
-> from emp a
-> join emp b
-> on a.sal < b.sal;
((shown below)
+---------+
| sal |
+---------+
| 800.00 | //When b=800, no value meets the conditions; b= 1600, a total of 6 values meet the conditions
| 1250.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 1300.00 |
| 800.00 | //At this time, b = 1250, and a total of 3 values meet the conditions
| 1100.00 |
| 950.00 |
| 800.00 | //b = 2975....
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
......
+---------+
89 rows in set (0.00 sec)
Then use dinsinct to remove the weight
mysql> select distinct a.sal -> from emp a -> join emp b -> on a.sal < b.sal; +---------+ | sal | +---------+ | 800.00 | | 1250.00 | | 1500.00 | | 1100.00 | | 950.00 | | 1300.00 | | 1600.00 | | 2850.00 | | 2450.00 | | 2975.00 | | 3000.00 | +---------+ 11 rows in set (0.00 sec)
Finally, find the value that is not equal to the value in the above table
mysql> select sal from emp
-> where sal not in (
-> select distinct a.sal
-> from emp a
-> join emp b
-> on a.sal < b.sal);
+---------+
| sal |
+---------+
| 5000.00 |
+---------+
1 row in set (0.01 sec)
After reading this answer, I found that the questions I wrote above should be troublesome. Pay attention to them later
5. Department number of the Department with the highest average salary (give at least two solutions)
The first step is the average salary of each department
mysql> select deptno, avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
Step 2: get the corresponding number of the highest salary part
Sort with max or
mysql> select deptno, max(avgsal) -> from (select deptno, avg(sal) avgsal from emp group by deptno) new; +--------+-------------+ | deptno | max(avgsal) | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+ 1 row in set (0.00 sec) Note: is this correct? It seems that the output department number is correct In use select deptno, avg(sal) from emp group by deptno;After generating a table, query the maximum value in the table, which has nothing to do with the department number Therefore, the number of 10 output is only the number output sequentially because 10 is on the first line, which is actually incorrect If the maximum value is changed to the minimum value, the output is obviously wrong mysql> select deptno, min(avgsal) -> from (select deptno, avg(sal) avgsal from emp group by deptno) new; +--------+-------------+ | deptno | min(avgsal) | +--------+-------------+ | 10 | 1566.666667 | +--------+-------------+ 1 row in set (0.00 sec) Therefore, the maximum value should be taken here, and then the corresponding department number should be matched in the table mysql> select max(avgsal) -> from (select deptno, avg(sal) avgsal from emp group by deptno) new; Then it is matched with the average value grouped by department mysql> select deptno, avg(sal) as avgsal -> from emp -> group by -> deptno -> having avgsal = (select max(new.avgsal) from (select deptno, avg(sal) avgsal from emp group by deptno) new); +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+ 1 row in set (0.00 sec)
Or (sort)
mysql> select deptno,avg(sal) avgsal -> from emp -> group by deptno -> order by avgsal desc -> limit 0,1; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+ 1 row in set (0.00 sec)
6. Department name of the Department with the highest average salary
Connect to the previous question. The previous question is looking for the part number. To find the name of this question, you need to connect another table and then query
mysql> select d.dname
-> from dept d
-> join (select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 0,1) as t
-> on d.deptno = t.deptno;
+------------+
| dname |
+------------+
| ACCOUNTING |
+------------+
1 row in set (0.00 sec)
I feel how stupid I am. When I look at the same, I foolishly set the above results. Alas
You can connect the two tables first, then group them according to the department number, calculate the average value, and then sort to take the first one
mysql> select d.dname, avg(e.sal) as avgsal -> from emp e -> join dept d -> on d.deptno = e.deptno -> group by e.deptno -> order by avgsal desc -> limit 0,1; +------------+-------------+ | dname | avgsal | +------------+-------------+ | ACCOUNTING | 2916.666667 | +------------+-------------+ 1 row in set (0.01 sec)
The Department name of the Department with the lowest level of average salary
Average salary first
mysql> select deptno, avg(sal) -> from emp -> group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
The Department with the lowest level of average salary
mysql> select n.deptno, n.avgsal,s.grade -> from salgrade s -> join (select deptno, avg(sal) avgsal from emp group by deptno) as n -> on n.avgsal between losal and hisal -> order by grade asc -> limit 0,1 -> ; +--------+-------------+-------+ | deptno | avgsal | grade | +--------+-------------+-------+ | 30 | 1566.666667 | 3 | +--------+-------------+-------+ 1 row in set (0.00 sec)
Then connect the Department table to get the Department name
mysql> select d.dname
-> from dept d
-> join (
-> select n.deptno, n.avgsal,s.grade
-> from salgrade s
-> join (select deptno, avg(sal) avgsal from emp group by deptno) as n
-> on n.avgsal between losal and hisal
-> order by grade asc
-> limit 0,1) as t
-> on d.deptno = t.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+
1 row in set (0.00 sec)
I think it's cumbersome. I'll watch the video later to learn how to improve. I'll finish it myself first
After watching the video, I found that I can't use limit to get the lowest level, because there may be multiple departments with the lowest level, so I need to find the lowest level first
Because the minimum salary level must be the lowest, take the lowest level according to this
Minimum average salary
mysql> select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1
-> ;
+-------------+
| avgsal |
+-------------+
| 1566.666667 |
+-------------+
1 row in set (0.00 sec)
Grade of minimum average salary
mysql> select grade from salgrade where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal; +-------+ | grade | +-------+ | 3 | +-------+ 1 row in set (0.00 sec)
Name of the average salary grade of the Department
mysql> select t.*, s.grade
-> from (select d.dname, avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t
-> join salgrade s
-> on t.avgsal between s.losal and s.hisal;
+------------+-------------+-------+
| dname | avgsal | grade |
+------------+-------------+-------+
| SALES | 1566.666667 | 3 |
| ACCOUNTING | 2916.666667 | 4 |
| RESEARCH | 2175.000000 | 4 |
+------------+-------------+-------+
3 rows in set (0.00 sec)
Department name of the lowest average salary grade (condition: the grade in the above table is equal to the lowest draw salary grade)
mysql> select t.*, s.grade
-> from (select d.dname, avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t
-> join salgrade s
-> on t.avgsal between s.losal and s.hisal
-> where s.grade =
-> (select grade from salgrade where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
+-------+-------------+-------+
| dname | avgsal | grade |
+-------+-------------+-------+
| SALES | 1566.666667 | 3 |
+-------+-------------+-------+
1 row in set (0.00 sec)
8. Get a leader's name that is higher than the maximum salary of an ordinary employee (the employee code does not appear in the mgr field)
What is the meaning of this question? It is the maximum salary of employees (ordinary employees) who are not properly led, and then find the name of leaders who are higher than this maximum salary
So it must be self connected
First ask for two tables, one is the salary table of ordinary employees, and the other is the salary table of leaders
First of all, I tried to report an error
mysql> select empeno,ename,sal
-> from emp
-> where empno not in mgr;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mgr' at line 3
Then we'll behave ourselves and find out the numbers of all the leaders first
mysql> select distinct mgr
-> from emp
-> where mgr is not null;
±-----+
| mgr |
±-----+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
±-----+
6 rows in set (0.00 sec)
Then find out the number and salary of all ordinary employees and sort them
Note: when not in is used, remember to exclude NULL in the following parentheses.
mysql> select ename,sal,empno
-> from emp
-> where empno not in(
-> select distinct mgr
-> from emp
-> where mgr is not null)
-> order by sal desc
-> ;
+--------+---------+-------+
| ename | sal | empno |
+--------+---------+-------+
| ALLEN | 1600.00 | 7499 |
| TURNER | 1500.00 | 7844 |
| MILLER | 1300.00 | 7934 |
| WARD | 1250.00 | 7521 |
| MARTIN | 1250.00 | 7654 |
| ADAMS | 1100.00 | 7876 |
| JAMES | 950.00 | 7900 |
| SMITH | 800.00 | 7369 |
+--------+---------+-------+
8 rows in set (0.00 sec)
At this time, the highest salary of ordinary employees has been found, and then the name of leaders with higher salary will be found
Find all leaders
mysql> select ename, sal, empno
-> from emp
-> where empno in(
-> select distinct mgr
-> from emp
-> where mgr is not null);
+-------+---------+-------+
| ename | sal | empno |
+-------+---------+-------+
| JONES | 2975.00 | 7566 |
| BLAKE | 2850.00 | 7698 |
| CLARK | 2450.00 | 7782 |
| SCOTT | 3000.00 | 7788 |
| KING | 5000.00 | 7839 |
| FORD | 3000.00 | 7902 |
+-------+---------+-------+
6 rows in set (0.00 sec)
Find the name of the leader whose salary is greater than the maximum salary
mysql> select l.ename,l.sal
-> from (select ename, sal from emp where empno in(select distinct mgr from emp where mgr is not null)) as l
-> where l.sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
The answer given by the teacher should be wrong, although the answer is the same
It's the leader's name, not more than 1600 among all employees
After watching the video:
The teacher is right. I am wrong. The leader must be higher than the maximum salary of ordinary employees!!!!!
So instead of selecting leaders, just look for them in all employees
select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
9. Top five highest paid employees
Suddenly simple, not suitable
mysql> select ename, sal
-> from emp
-> order by sal desc
-> limit 0,5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
10. The sixth to tenth highest paid employees
mysql> select ename, sal -> from emp -> order by sal desc -> limit 5,5; +--------+---------+ | ename | sal | +--------+---------+ | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | +--------+---------+ 5 rows in set (0.00 sec)
11. 5 employees who have obtained the last entry
The knowledge points given in this question can be sorted by date, and are in ascending order from morning to night and descending order from late to early
mysql> select ename, hiredate
-> from emp
-> order by hiredate desc
-> limit 0,5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD | 1981-12-03 |
| JAMES | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
12. How many employees get each salary grade
First calculate the salary grade, and then count
mysql> select e.ename, s.grade
-> from emp e
-> join salgrade s
-> on e.sal between losal and hisal;
+--------+-------+
| ename | grade |
+--------+-------+
| SMITH | 1 |
| ALLEN | 3 |
| WARD | 2 |
| JONES | 4 |
| MARTIN | 2 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| KING | 5 |
| TURNER | 3 |
| ADAMS | 1 |
| JAMES | 1 |
| FORD | 4 |
| MILLER | 2 |
+--------+-------+
14 rows in set (0.00 sec)
count
mysql> select grade, count(*)
-> from (
-> select e.ename, s.grade
-> from emp e
-> join salgrade s
-> on e.sal between losal and hisal) as n
-> group by n.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+----------+
5 rows in set (0.00 sec)
After watching the video:
There is also no need to turn the query result of the first step into a table. Just group it directly
mysql> select s.grade,count(*)
-> from emp e
-> join salgrade s
-> on e.sal between losal and hisal
-> group by s.grade;
13. Interview questions
Question 1 Find out the names of all the students who have not chosen "dawn" teacher.
First find out the number of Liming teacher, then check the number of students who chose Liming teacher in the course selection table, and then check the names of students who did not choose Liming teacher in the student table
First find out the number of Mr. Liming
mysql> select cno from c where cteacher = 'liming'; +------+ | cno | +------+ | 5 | +------+ 1 row in set (0.08 sec)
Then check the number of the students who chose Mr. liming in the course selection table,
mysql> select sno from sc
-> where cno = (select cno from c where cteacher = 'liming');
+------+
| sno |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.03 sec)
Check the names of students who did not choose Mr. liming in the student table
mysql> select sname from s
-> where sno not in (
-> select sno from sc
-> where cno = (select cno from c where cteacher = 'liming'));
+-----------+
| sname |
+-----------+
| xuesheng3 |
| xuesheng4 |
+-----------+
2 rows in set (0.03 sec)
Question 2 List the names and average scores of students who fail more than 2 (including 2).
Find out all the people and courses who failed first
mysql> select sno,cno,scgrade
-> from sc where scgrade < 60;
+------+------+---------+
| sno | cno | scgrade |
+------+------+---------+
| 1 | 1 | 40 |
| 1 | 2 | 30 |
| 1 | 3 | 20 |
| 2 | 5 | 40 |
+------+------+---------+
4 rows in set (0.01 sec)
Then count the number of failed courses for each person and select the number of students with more than 2 courses
mysql> select sno,count(sno) from (select sno,cno,scgrade from sc where scgrade < 60) as n
-> group by sno having count(sno) >= 2;
+------+------------+
| sno | count(sno) |
+------+------------+
| 1 | 3 |
+------+------------+
1 row in set (0.12 sec)
Calculate the average grade of the student in the course selection table
mysql> select sno,avg(scgrade) from sc where sno = ( -> select sno from (select sno,cno,scgrade from sc where scgrade < 60) as n -> group by sno having count(sno) >= 2) -> group by sno; +------+--------------+ | sno | avg(scgrade) | +------+--------------+ | 1 | 46 | +------+--------------+ 1 row in set (0.07 sec)
Find the student's name in the student table and connect the two tables according to the student number
mysql> select a.sname, b.avggrade
-> from s as a
-> join (
-> select sno,avg(scgrade) avggrade from sc where sno = (
-> select sno from (select sno,cno,scgrade from sc where scgrade < 60) as n
-> group by sno having count(sno) >= 2)
-> group by sno) as b
-> on a.sno = b.sno;
+-----------+----------+
| sname | avggrade |
+-----------+----------+
| xuesheng1 | 46 |
+-----------+----------+
1 row in set (0.01 sec)
Question 3: names of all students who have studied both course 1 and course 2.
Students who have studied No. 1 and No. 2
mysql> select sno,cno from sc where cno = '1'; +------+------+ | sno | cno | +------+------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +------+------+ 3 rows in set (0.00 sec) mysql> select sno,cno from sc where cno = '2'; +------+------+ | sno | cno | +------+------+ | 1 | 2 | | 2 | 2 | +------+------+ 2 rows in set (0.00 sec)
Students who have taken two courses at the same time
mysql> select a.sno from (select sno,cno from sc where cno = '1') as a
-> where a.sno in (select b.sno from
-> (select sno,cno from sc where cno = '2') as b);
+------+
| sno |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
Find the name in the student form
mysql> select s.sname from s
-> where sno in (
-> select a.sno from (select sno,cno from sc where cno = '1') as a
-> where a.sno in (select b.sno from
-> (select sno,cno from sc where cno = '2') as b));
+-----------+
| sname |
+-----------+
| xuesheng1 |
| xuesheng2 |
+-----------+
2 rows in set (0.00 sec)
14. List the names of all employees and leaders
It is equivalent to two tables, one for all employees and one for relationship. Then check the corresponding leaders through the relationship table. Note that since the boss has no leaders, external connection is used. It is processed when it is NULL
mysql> select a.ename, ifnull(b.ename, 'I\'m boss') from emp a
-> left join emp b
-> on a.mgr = b.empno;
+--------+------------------------------+
| ename | ifnull(b.ename, 'I\'m boss') |
+--------+------------------------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | I'm boss |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+------------------------------+
14 rows in set (0.00 sec)
15. List the number, name and department name of all employees whose employment date is earlier than their direct supervisor
I found the leader in the previous question. Here I judge whether his employment date is earlier than his leader
mysql> select a.empno, a.ename, b.ename,a.deptno
-> from emp a
-> join emp b
-> on a.mgr = b.empno
-> where a.hiredate < b.hiredate;
+-------+-------+-------+--------+
| empno | ename | ename | deptno |
+-------+-------+-------+--------+
| 7369 | SMITH | FORD | 20 |
| 7499 | ALLEN | BLAKE | 30 |
| 7521 | WARD | BLAKE | 30 |
| 7566 | JONES | KING | 20 |
| 7698 | BLAKE | KING | 30 |
| 7782 | CLARK | KING | 10 |
+-------+-------+-------+--------+
6 rows in set (0.02 sec)
Then connect the Department table to find the Department name
mysql> select t.empno, t.ename, d.dname
-> from (
-> select a.empno, a.ename, a.deptno
-> from emp a
-> join emp b
-> on a.mgr = b.empno
-> where a.hiredate < b.hiredate) as t
-> join dept d
-> on t.deptno = d.deptno;
+-------+-------+------------+
| empno | ename | dname |
+-------+-------+------------+
| 7782 | CLARK | ACCOUNTING |
| 7369 | SMITH | RESEARCH |
| 7566 | JONES | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7698 | BLAKE | SALES |
+-------+-------+------------+
After watching the video:
It's the same problem. You don't need nesting and it's cumbersome. Just connect it later
mysql> select a.ename, b.ename,a.deptno,d.dname
-> from emp a
-> join emp b
-> on a.mgr = b.empno
-> join dept d
-> a.deptno = d.deptno
-> where a.hiredate < b.hiredate;
16. List Department names and employee information of these departments, as well as those departments without employees
Left outer link
mysql> select d.dname,e.* from dept d left join emp e on d.deptno = e.deptno; +------------+-------+--------+-----------+------+------------+---------+---------+--------+ | dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------------+-------+--------+-----------+------+------------+---------+---------+--------+ | ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-------+--------+-----------+------+------------+---------+---------+--------+ 15 rows in set (0.00 sec)
17. List all departments with at least 5 employees
Next question, group statistics is finished
mysql> select d.dname,count(dname) from dept d left join emp e on d.deptno = e.deptno
-> group by dname
-> having count(dname) >= 5;
+----------+--------------+
| dname | count(dname) |
+----------+--------------+
| RESEARCH | 5 |
| SALES | 6 |
+----------+--------------+
2 rows in set (0.00 sec)
18. List all employee information with more salary than "SMITH"
mysql> 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 | +-------+--------+-----------+------+------------+---------+---------+--------+ 13 rows in set (0.02 sec)
19. List the names of all clerks and their departments, and the number of people in the Department
First find out the clerk's name and department number
mysql> select ename,job, deptno from emp where job = 'clerk'; +--------+-------+--------+ | ename | job | deptno | +--------+-------+--------+ | SMITH | CLERK | 20 | | ADAMS | CLERK | 20 | | JAMES | CLERK | 30 | | MILLER | CLERK | 10 | +--------+-------+--------+ 4 rows in set (0.00 sec)
Find out the number and number of people corresponding to each department
mysql> select deptno,count(deptno) from emp group by deptno; +--------+---------------+ | deptno | count(deptno) | +--------+---------------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+---------------+ 3 rows in set (0.00 sec)
Then find the corresponding name
mysql> select e.deptno,count(e.deptno), d.dname from emp e join dept d on e.deptno = d.deptno group by deptno; +--------+-----------------+------------+ | deptno | count(e.deptno) | dname | +--------+-----------------+------------+ | 10 | 3 | ACCOUNTING | | 20 | 5 | RESEARCH | | 30 | 6 | SALES | +--------+-----------------+------------+ 3 rows in set (0.00 sec)
Then connect the two tables
mysql> select a.ename, b.dname, b.cc
-> from(
-> select ename,job, deptno from emp where job = 'clerk') as a
-> join(
-> select e.deptno,count(e.deptno) as cc, d.dname from emp e join dept d on e.deptno = d.deptno group by deptno) as b
-> on a.deptno = b.deptno;
+--------+------------+----+
| ename | dname | cc |
+--------+------------+----+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+------------+----+
4 rows in set (0.03 sec)
20. List all kinds of jobs with a minimum salary of more than 1500 and the number of all employees engaged in this job
Find out the minimum salary for each job first
mysql> select job, min(sal) from emp group by job; +-----------+----------+ | job | min(sal) | +-----------+----------+ | ANALYST | 3000.00 | | CLERK | 800.00 | | MANAGER | 2450.00 | | PRESIDENT | 5000.00 | | SALESMAN | 1250.00 | +-----------+----------+ 5 rows in set (0.03 sec)
Select jobs with a minimum salary of more than 1500
mysql> select job, min(sal) from emp group by job having min(sal) > 1500; +-----------+----------+ | job | min(sal) | +-----------+----------+ | ANALYST | 3000.00 | | MANAGER | 2450.00 | | PRESIDENT | 5000.00 | +-----------+----------+ 3 rows in set (0.00 sec)
Number of people engaged in this job
mysql> select job,count(job) from emp where job in (
-> select job from emp group by job having min(sal) > 1500)
-> group by job;
+-----------+------------+
| job | count(job) |
+-----------+------------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
+-----------+------------+
3 rows in set (0.00 sec)
It's troublesome to write again. Count directly while selecting a job
mysql> select job, count(*) from emp group by job having min(sal) > 1500;
21. List the names of employees working in the Department "sales" < Sales Department ". It is assumed that they do not know the department number of the sales department
Connect the two tables first, and then query, and you're done
mysql> select e.*, d.dname from emp e join dept d on e.deptno = d.deptno; +-------+--------+-----------+------+------------+---------+---------+--------+------------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | dname | +-------+--------+-----------+------+------------+---------+---------+--------+------------+ | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | ACCOUNTING | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | ACCOUNTING | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ACCOUNTING | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | RESEARCH | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | RESEARCH | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | RESEARCH | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | RESEARCH | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | RESEARCH | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | SALES | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | SALES | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | SALES | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | SALES | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | SALES | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | SALES | +-------+--------+-----------+------+------------+---------+---------+--------+------------+ 14 rows in set (0.00 sec) mysql> select n.ename from (select e.*, d.dname from emp e join dept d on e.deptno = d.deptno) as n where dname = 'sales'; +--------+ | ename | +--------+ | ALLEN | | WARD | | MARTIN | | BLAKE | | TURNER | | JAMES | +--------+ 6 rows in set (0.00 sec)
After watching the video:
First find the department number according to the Department name, and then find the employee with the same department number
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
22. List the salary grades of all employees whose salary is higher than the average salary of the company, their departments, superior leaders and employees
Find all employees whose salary is higher than the average salary first
mysql> select ename,deptno, mgr,sal from emp where sal > (select avg(sal) from emp); +-------+--------+------+---------+ | ename | deptno | mgr | sal | +-------+--------+------+---------+ | JONES | 20 | 7839 | 2975.00 | | BLAKE | 30 | 7839 | 2850.00 | | CLARK | 10 | 7839 | 2450.00 | | SCOTT | 20 | 7566 | 3000.00 | | KING | 10 | NULL | 5000.00 | | FORD | 20 | 7566 | 3000.00 | +-------+--------+------+---------+ 6 rows in set (0.00 sec)
Then connect three tables and query them respectively
mysql> select e.ename, d.dname,l.ename, s.grade from ( -> select ename,deptno, mgr,sal from emp where sal > (select avg(sal) from emp)) as e -> join dept d -> on e.deptno = d.deptno -> left join emp l -> on e.mgr = l.empno -> join salgrade s -> on e.sal between losal and hisal; +-------+------------+-------+-------+ | 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 | +-------+------------+-------+-------+ 6 rows in set (0.00 sec)
After watching the video:
It's cumbersome again
select e.ename 'staff',d.dname,l.ename 'leader',s.grade from emp e join dept d on e.deptno = d.deptno left join emp l on e.mgr = l.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp);
23. List the names of all employees and departments engaged in the same work as "SCOTT"
Find out what scott does
mysql> select job from emp where ename = 'scott'; +---------+ | job | +---------+ | ANALYST | +---------+ 1 row in set (0.00 sec)
Find the name and department number of the employee who does the same work as him
mysql> select ename,deptno from emp where job = (select job from emp where ename = 'scott') and ename != 'scott'; +-------+--------+ | ename | deptno | +-------+--------+ | FORD | 20 | +-------+--------+ 1 row in set (0.00 sec)
Query department name in connection table
mysql> select e.ename, d.dname from ( -> select ename,deptno from emp where job = (select job from emp where ename = 'scott') and ename != 'scott') as e -> join dept d -> on e.deptno = d.deptno; +-------+----------+ | ename | dname | +-------+----------+ | FORD | RESEARCH | +-------+----------+
After watching the video:
I found that what I did before was step by step, so I can easily regard each step as a table, and then connect another table for the next step. In some places, I don't have to be so troublesome
For example, here, after I find the name and department number of the employee who is engaged in the same work as him, I can directly connect the Department table to find out the Department name
select e.ename,e.job,d.dname from emp e join dept d on e.deptno = d.deptno where e.job = (select job from emp where ename = 'SCOTT') and e.ename <> 'SCOTT';
24. List the names and salaries of other employees whose salary is equal to that of employees in department 30
Why is this sentence so awkward to read
First find the salary and name of the employees in the Department 30
mysql> select ename, sal from emp where deptno = 30; +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | TURNER | 1500.00 | | JAMES | 950.00 | +--------+---------+ 6 rows in set (0.00 sec)
Find out the name and salary of the employee whose salary is in this table but whose name is not in this table,
It's rigid again. I've been writing like this
mysql> select e.ename,e.sal from emp e
-> join (
-> select ename, sal from emp where deptno = 30) as b
-> on e.sal in b.sal and e.ename not in b.ename;
But this will not work. The bottom line will directly report errors one by one
For employees whose names are not in the above table, the Department is not 30, and the direct screening is finished
mysql> 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 and salaries of employees whose salaries are higher than those of all employees working in department 30 Department name
Similarly, first find the maximum salary of employees in department 30
mysql> select max(sal) from emp where deptno = 30; +----------+ | max(sal) | +----------+ | 2850.00 | +----------+ 1 row in set (0.00 sec)
Then find the employee's name and department number greater than this salary
mysql> select ename,sal,deptno from emp -> where sal > (select max(sal) from emp where deptno = 30) and deptno != 30; +-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | JONES | 2975.00 | 20 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | +-------+---------+--------+ 4 rows in set (0.00 sec)
Connect the table and change the department number to name
mysql> select e.ename, e.sal ,d.dname from (
-> select ename,sal,deptno from emp
-> where sal > (select max(sal) from emp where deptno = 30) and deptno != 30) e
-> join dept d
-> on e.deptno = d.deptno;
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
4 rows in set (0.00 sec)
After watching the video:
I'll look at it as a table again. Just connect it directly. There is no need to add department number, which is not equal to 30
mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno
-> where sal > (select max(sal) from emp where deptno = 30);
mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno where sal > (select max(sal) from emp where deptno = 30); +-------+---------+------------+ | ename | sal | dname | +-------+---------+------------+ | KING | 5000.00 | ACCOUNTING | | JONES | 2975.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | FORD | 3000.00 | RESEARCH | +-------+---------+------------+ 4 rows in set (0.00 sec)
26. List the number of employees working in each department, average salary and average service period
I don't quite understand what the average service period is
First write down the other two items
mysql> select d.dname, count(e.ename),avg(e.sal) from emp e -> join dept d -> on e.deptno = d.deptno -> group by e.deptno; +------------+----------------+-------------+ | dname | count(e.ename) | avg(e.sal) | +------------+----------------+-------------+ | ACCOUNTING | 3 | 2916.666667 | | RESEARCH | 5 | 2175.000000 | | SALES | 6 | 1566.666667 | +------------+----------------+-------------+ 3 rows in set (0.00 sec)
Because there is a department without employees, it is wrong here. It needs external connection and empty assignment
mysql> select d.dname, count(e.ename),ifnull(avg(e.sal),0) from emp e
-> right join dept d
-> on e.deptno = d.deptno
-> group by e.deptno;
+------------+----------------+----------------------+
| dname | count(e.ename) | ifnull(avg(e.sal),0) |
+------------+----------------+----------------------+
| OPERATIONS | 0 | 0.000000 |
| ACCOUNTING | 3 | 2916.666667 |
| RESEARCH | 5 | 2175.000000 |
| SALES | 6 | 1566.666667 |
+------------+----------------+----------------------+
4 rows in set (0.04 sec)
Service period, I guess it's the current time minus the entry time
Calculate the difference between two dates in MySQL
How to calculate the "year difference" between two dates in mysql? How many years is the difference?
Timestampdiff (interval type, previous date, later date)
timestampdiff(YEAR, hiredate, now())
Interval type:
SECOND Seconds,
MINUTE minute,
HOUR Hours,
DAY God,
WEEK week
MONTH Month,
QUARTER Quarter,
YEAR year
mysql> select d.dname, count(e.ename) ecount,ifnull(avg(e.sal),0) avgsal,
-> ifnull(Timestampdiff(YEAR,hiredate, now()),0) avgservetime from emp e
-> right join dept d
-> on e.deptno = d.deptno
-> group by e.deptno;
+------------+--------+-------------+--------------+
| dname | ecount | avgsal | avgservetime |
+------------+--------+-------------+--------------+
| OPERATIONS | 0 | 0.000000 | 0 |
| ACCOUNTING | 3 | 2916.666667 | 39 |
| RESEARCH | 5 | 2175.000000 | 40 |
| SALES | 6 | 1566.666667 | 40 |
+------------+--------+-------------+--------------+
4 rows in set (0.02 sec)
27. List the names, Department names and wages of all employees
Why does it feel simpler and simpler
mysql> select e.ename, d.dname, e.sal
-> from emp e
-> join dept d
-> on e.deptno = d.deptno;
+--------+------------+---------+
| ename | dname | sal |
+--------+------------+---------+
| CLARK | ACCOUNTING | 2450.00 |
| KING | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH | RESEARCH | 800.00 |
| JONES | RESEARCH | 2975.00 |
| SCOTT | RESEARCH | 3000.00 |
| ADAMS | RESEARCH | 1100.00 |
| FORD | RESEARCH | 3000.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| TURNER | SALES | 1500.00 |
| JAMES | SALES | 950.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)
28. List the details and number of people in all departments
mysql> select d.*,ifnull(e.ecount,0) from (
-> select deptno,count(ename) ecount from emp group by deptno) as e
-> right join dept d
-> on e.deptno = d.deptno;
+--------+------------+----------+--------------------+
| DEPTNO | DNAME | LOC | ifnull(e.ecount,0) |
+--------+------------+----------+--------------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+--------------------+
4 rows in set (0.00 sec)
It's complicated again
select d.deptno,d.dname,d.loc,count(e.ename) from emp e right join dept d on e.deptno = d.deptno group by d.deptno,d.dname,d.loc;
29. List the minimum wage for various jobs and the names of employees engaged in this job
First, the minimum wage and name of various jobs
mysql> select job,min(sal) from emp group by job; +-----------+----------+ | job | min(sal) | +-----------+----------+ | ANALYST | 3000.00 | | CLERK | 800.00 | | MANAGER | 2450.00 | | PRESIDENT | 5000.00 | | SALESMAN | 1250.00 | +-----------+----------+ 5 rows in set (0.00 sec)
According to the answers given, the title should be the name of the employee who is engaged in the job and has the lowest salary
mysql> select e.ename,j.job,j.minsal from emp e
-> join (
-> select job,min(sal) minsal from emp group by job) as j
-> on e.job = j.job and e.sal = j.minsal;
+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| 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 |
+--------+-----------+---------+
7 rows in set (0.00 sec)
30. List the minimum salary of the manager of each department
Pay special attention here. First judge the leader, and then group, you can get the answer; However, if you group first and then judge, the column without job will be displayed
Therefore, it can be concluded that after grouping, only the fields and grouping functions used for grouping are left in the remaining fields in the table, and then having will be executed
mysql> select deptno, min(sal) from emp group by deptno having job = 'manager'; ERROR 1054 (42S22): Unknown column 'job' in 'having clause' mysql> select deptno, min(sal) from emp where job= 'manager' group by deptno; +--------+----------+ | deptno | min(sal) | +--------+----------+ | 10 | 2450.00 | | 20 | 2975.00 | | 30 | 2850.00 | +--------+----------+ 3 rows in set (0.00 sec)
31. List the annual salary of all employees, ranking from low to high
mysql> select ename,(sal * 12 + ifnull(comm, 0) * 12) yearsal from emp order by yearsal asc; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | JAMES | 11400.00 | | ADAMS | 13200.00 | | MILLER | 15600.00 | | TURNER | 18000.00 | | WARD | 21000.00 | | ALLEN | 22800.00 | | CLARK | 29400.00 | | MARTIN | 31800.00 | | BLAKE | 34200.00 | | JONES | 35700.00 | | FORD | 36000.00 | | SCOTT | 36000.00 | | KING | 60000.00 | +--------+----------+ 14 rows in set (0.00 sec)
32. Find out the names of employees and leaders whose salary exceeds 3000
mysql> select e.ename, l.ename,l.sal
-> from emp e
-> join emp l
-> on e.mgr = l.empno
-> where l.sal > 3000;
+-------+-------+---------+
| ename | ename | sal |
+-------+-------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+-------+---------+
3 rows in set (0.00 sec)
33. Calculate the total salary and number of department employees with'S' characters in the Department name
Use fuzzy query to find the Department name with S character
mysql> select deptno,dname from dept where dname like '%s%'; +--------+------------+ | deptno | dname | +--------+------------+ | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+ 3 rows in set (0.00 sec)
Then connect the table to check the total salary and the number of people in the Department
mysql> select d.deptno, d.dname, sum(e.sal), count(e.ename)
-> from emp e
-> right join (
-> select deptno,dname from dept where dname like '%s%') as d
-> on e.deptno = d.deptno
-> group by deptno;
+--------+------------+------------+----------------+
| deptno | dname | sum(e.sal) | count(e.ename) |
+--------+------------+------------+----------------+
| 20 | RESEARCH | 10875.00 | 5 |
| 30 | SALES | 9400.00 | 6 |
| 40 | OPERATIONS | NULL | 0 |
+--------+------------+------------+----------------+
3 rows in set (0.00 sec)
After watching the video:
Without nesting,
select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal from emp e right join dept d on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno,d.dname,d.loc;
34. Give 10% salary increase to employees who have served for more than 30 years
It's been more than 30 years, ha ha
mysql> select ename, sal * 1.1 from emp where timestampdiff(year, hiredate, now()) > 30; +--------+-----------+ | ename | sal * 1.1 | +--------+-----------+ | SMITH | 880.00 | | ALLEN | 1760.00 | | WARD | 1375.00 | | JONES | 3272.50 | | MARTIN | 1375.00 | | BLAKE | 3135.00 | | CLARK | 2695.00 | | SCOTT | 3300.00 | | KING | 5500.00 | | TURNER | 1650.00 | | ADAMS | 1210.00 | | JAMES | 1045.00 | | FORD | 3300.00 | | MILLER | 1430.00 | +--------+-----------+ 14 rows in set (0.03 sec)
summary
It took two or three days to finish these questions. At the beginning, I was a little confused. After I knew the routine, I did it faster and faster. Of course, it has something to do with the simplification of the later questions. Of course, I also found some small problems. I feel that the database is still relatively simple. Continue to refuel
After watching the video, I found that many of my questions come step by step. Although this is very organized and not easy to make mistakes, there are many processes in which I turn the results of the previous step into a table and then nest them. In fact, it is not necessary, so pay attention here