Old Du Mysql's 34 homework questions

MySQL34 homework questions

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

mysql> select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t 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 is paid above the average salary of the Department

mysql> select e.ename,t.* from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t
    -> on e.deptno = t.deptno and e.sal > t.avgsal;
+-------+--------+-------------+
| ename | deptno | avgsal      |
+-------+--------+-------------+
| ALLEN |     30 | 1566.666667 |
| JONES |     20 | 2175.000000 |
| BLAKE |     30 | 1566.666667 |
| SCOTT |     20 | 2175.000000 |
| KING  |     10 | 2916.666667 |
| FORD  |     20 | 2175.000000 |
+-------+--------+-------------+

3. Get the average salary grade (of everyone) in the Department

The average value of the Department salary level grade is required, not the grade of the average value of the salary level

mysql> select t .*, avg(t.grade) as avggrade from (select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno
    -> ;
+-------+---------+--------+-------+----------+
| ename | sal     | deptno | grade | avggrade |
+-------+---------+--------+-------+----------+
| CLARK | 2450.00 |     10 |     4 |   3.6667 |
| SMITH |  800.00 |     20 |     1 |   2.8000 |
| ALLEN | 1600.00 |     30 |     3 |   2.5000 |
+-------+---------+--------+-------+----------+
3 rows in set (0.00 sec)

4. Do not use group function (Max) to get the highest salary

mysql> select ename,sal from emp order by sal desc limit 0,1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)

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.00 sec)

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

mysql> select t.deptno,max(t.avgsal) from ( select avg(sal) as avgsal,deptno from emp group by deptno) t;
+--------+---------------+
| deptno | max(t.avgsal) |
+--------+---------------+
|     10 |   2916.666667 |
+--------+---------------+
1 row in set (0.00 sec)

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

mysql> select d.dname,max(t.avgsal) from dept d join (select avg(sal) as avgsal,deptno from emp group by deptno) t
    -> on d.deptno = t.deptno;
+------------+---------------+
| dname      | max(t.avgsal) |
+------------+---------------+
| ACCOUNTING |   2916.666667 |
+------------+---------------+
1 row in set (0.00 sec)

7. The Department name of the Department with the lowest level of average salary

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) t
    -> join
    -> salgrade s
    -> on
    -> t.avgsal between s.losal and s.hisal
    -> where
    -> s.grade = (select grade from salgrade where (select avg(sal) as 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)

mysql> select d.*,t.deptno from dept d join (select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) t on d.deptno = t. deptno;
+--------+-------+---------+--------+
| DEPTNO | DNAME | LOC     | deptno |
+--------+-------+---------+--------+
|     30 | SALES | CHICAGO |     30 |
+--------+-------+---------+--------+
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)

mysql> select ename from emp where sal >( select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| SCOTT |
| KING  |
| FORD  |
+-------+
5 rows in set (0.00 sec)

9. Top five highest paid employees

mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 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     |
+--------+---------+
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
+--------+---------+
5 rows in set (0.00 sec)

11. 5 employees who have obtained the last entry

Dates can also be in descending or ascending order.

mysql> select ename,hiredate from emp order by hiredate desc limit 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)slq

12. How many employees get each salary grade

mysql> select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        5 |
|     2 |        2 |
|     3 |        2 |
|     4 |        4 |
|     5 |        1 |
+-------+----------+
5 rows in set (0.00 sec)

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)
Question:
1. Find out the names of all students who have not chosen "dawn" teacher.
2. List the names and average scores of students who fail more than 2 (including 2).
3. Names of all students who have studied both course 1 and course 2.

14. List the names of all employees and leaders

mysql> select e.ename,b.ename from emp e join emp b on e.empno = b.empno;
+--------+--------+
| ename  | ename  |
+--------+--------+
| ADAMS  | ADAMS  |
| ALLEN  | ALLEN  |
| BLAKE  | BLAKE  |
| CLARK  | CLARK  |
| FORD   | FORD   |
| JAMES  | JAMES  |
| JONES  | JONES  |
| KING   | KING   |
| MARTIN | MARTIN |
| MILLER | MILLER |
| SCOTT  | SCOTT  |
| SMITH  | SMITH  |
| TURNER | TURNER |
| WARD   | WARD   |
+--------+--------+
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

mysql>  select e.ename 'employess' ,e.hiredate,b.ename 'employers',b.hiredate,d.dname from emp e join emp b on e.mgr = b.empno join dept d on e.deptno = d.deptno where e.hiredate < b.hiredate;
+-----------+------------+-----------+------------+------------+
| employess | hiredate   | employers | hiredate   | dname      |
+-----------+------------+-----------+------------+------------+
| CLARK     | 1981-06-09 | KING      | 1981-11-17 | ACCOUNTING |
| SMITH     | 1980-12-17 | FORD      | 1981-12-03 | RESEARCH   |
| JONES     | 1981-04-02 | KING      | 1981-11-17 | RESEARCH   |
| ALLEN     | 1981-02-20 | BLAKE     | 1981-05-01 | SALES      |
| WARD      | 1981-02-22 | BLAKE     | 1981-05-01 | SALES      |
| BLAKE     | 1981-05-01 | KING      | 1981-11-17 | SALES      |
+-----------+------------+-------

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

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 | 1000.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.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)

17. List all departments with at least 5 employees

mysql> select t.SUMCOUNT,t.dname from ( select count(*) as 'SUMCOUNT',d.dname from emp e join dept d on e.deptno = d.deptno group by d.deptno) t where t.SUMCOUNT >4;
+----------+----------+
| SUMCOUNT | dname    |
+----------+----------+
|        5 | RESEARCH |
|        6 | SALES    |
+----------+----------+

mysql> select count(*) from emp group by deptno having count(*) >= 5;
+----------+
| count(*) |
+----------+
|        5 |
|        6 |
+----------+
2 rows in set (0.00 sec)

18. List all employee information with more salary than "SMITH"

mysql> select e.ename,e.sal from emp e join (select ename,sal from emp where ename = 'SMITH') t on e.sal > t.sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 1000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1000.00 |
+--------+---------+
13 rows in set (0.00 sec)

19. List the names of all clerks and their departments, and the number of people in the departments

mysql> select
    -> t1.*,t2.deptcount
    -> from
    -> (select
    -> e.ename,e.job,d.dname,d.deptno
    -> from
    -> emp e
    -> join
    -> dept d
    -> on
    -> e.deptno = d.deptno
    -> where
    -> e.job = 'CLERK') t1
    -> join
    -> (select deptno, count(*) as deptcount from emp group by deptno) t2
    -> on
    -> t1.deptno = t2.deptno;
+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
+--------+-------+------------+--------+-----------+
4 rows in set (0.00 sec)

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

mysql> select job,count(*) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| PRESIDENT |        1 |
+-----------+----------+
2 rows in set (0.00 sec)

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

mysql>  select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

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

mysql> 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);
+-------+------------+-------+-------+
| staff     | dname      | leader    | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH   | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| SCOTT | RESEARCH   | JONES |     4 |
| KING  | ACCOUNTING | NULL  |     5 |
| FORD  | RESEARCH   | JONES |     4 |
+-------+------------+-------+-------+
5 rows in set (0.00 sec)

23. List the names of all employees and departments engaged in the same work as "SCOTT"

mysql> 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';
+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+
1 row in set (0.00 sec)

mysql>  select t.* from ( select a.job, a.ename,d.dname from emp a join dept d where a.deptno = d.deptno) t where t.job = (select job from emp where ename = 'SCOTT');
+---------+-------+----------+
| job     | ename | dname    |
+---------+-------+----------+
| ANALYST | SCOTT | RESEARCH |
| ANALYST | FORD  | RESEARCH |
+---------+-------+----------+

24. List the names and salaries of other employees whose salary is equal to that of employees in department 30

mysql> select ename ,sal from emp where sal in (select distinct 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

mysql> select e.ename,d.dname,e.sal from emp e join dept d on e.deptno = d.deptno where e.sal >(select max(sal) from emp where deptno = 30);
+-------+------------+---------+
| ename | dname      | sal     |
+-------+------------+---------+
| KING  | ACCOUNTING | 5000.00 |
| JONES | RESEARCH   | 2975.00 |
| SCOTT | RESEARCH   | 3000.00 |
| FORD  | RESEARCH   | 3000.00 |
+-------+------------+---------+
4 rows in set (0.00 sec)

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

select 
	d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;

+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | avgservicetime |
+--------+--------+-------------+----------------+
|     10 |      3 | 2333.333333 |        39.3333 |
|     20 |      5 | 2175.000000 |        37.4000 |
|     30 |      6 | 1566.666667 |        39.6667 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+
4 rows in set (0.00 sec)

stay mysql How to calculate the "year difference" between the two dates and how many years?
	TimeStampDiff(Interval type, Previous date, Next 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.deptno,count(ename) count,ifnull(avg(sal),0) avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) hiredate from emp e right join dept d on e.deptno = d.deptno group by d.deptno;
+--------+-------+-------------+----------+
| deptno | count | avgsal      | hiredate |
+--------+-------+-------------+----------+
|     10 |     3 | 2333.333333 |  39.3333 |
|     20 |     5 | 2175.000000 |  37.4000 |
|     30 |     6 | 1566.666667 |  39.6667 |
|     40 |     0 |    0.000000 |   0.0000 |
+--------+-------+-------------+----------+
4 rows in set (0.00 sec)

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

mysql> select e.ename,d.dname,e.sal from emp e left 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 | 1000.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 | 1000.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)

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

mysql> select count(e.ename) Cemployee, d.* from emp e right join dept d on e.deptno = d.deptno group by d.deptno;
+-----------+--------+------------+----------+
| Cemployee | DEPTNO | DNAME      | LOC      |
+-----------+--------+------------+----------+
|         3 |     10 | ACCOUNTING | NEW YORK |
|         5 |     20 | RESEARCH   | DALLAS   |
|         6 |     30 | SALES      | CHICAGO  |
|         0 |     40 | OPERATIONS | BOSTON   |
+-----------+--------+------------+----------+
4 rows in set (0.00 sec)

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

mysql> select e.sal, e.ename,e.job from emp e join  (select job, min(sal) minsal from emp group by job) t on e.sal = t.minsal and e.job = t.job;
+---------+--------+-----------+
| sal     | ename  | job       |
+---------+--------+-----------+
| 3000.00 | SCOTT  | ANALYST   |
| 3000.00 | FORD   | ANALYST   |
|  800.00 | SMITH  | CLERK     |
| 1000.00 | CLARK  | MANAGER   |
| 5000.00 | KING   | PRESIDENT |
| 1250.00 | WARD   | SALESMAN  |
| 1250.00 | MARTIN | SALESMAN  |
+---------+--------+-----------+
7 rows in set (0.00 sec)

30. List the minimum salary of manager of each department

mysql> select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  1000.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 + ifnull(comm,0))*12 as yearsal from emp order by yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| MILLER | 12000.00 |
| CLARK  | 12000.00 |
| ADAMS  | 13200.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.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 name and leader of the employee whose salary exceeds 3000

mysql> select a.ename employees, b.ename employer from emp a join emp b on a.mgr = b.empno where b.sal >3000;
+-----------+----------+
| employees | employer |
+-----------+----------+
| JONES     | KING     |
| BLAKE     | KING     |
| CLARK     | KING     |
+-----------+----------+
3 rows in set (0.00 sec)

33. Calculate the total salary and number of department employees with'S' characters in the Department name

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

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

mysql> update emp set sal = sal*1.1 where timestampdiff(YEAR,hiredate,now())> 30;
Query OK, 14 rows affected (0.01 sec)
Rows matched: 14  Changed: 14  Warnings: 0

Posted by leena86 on Wed, 03 Nov 2021 18:07:17 -0700

Programmer Group