<>– Department table

create table dept(
deptno int primary key auto_increment, – Department number
dname varchar(14) , – Department name
loc varchar(13) – address
) ;

<>– Employee table

create table emp(
empno int primary key auto_increment,-- Employee number
ename varchar(10), – Employee name -
job varchar(9), – post
mgr int, – Direct leader No
hiredate date, – Date of employment , Entry date
sal int, – salary
comm int, – Commission
deptno int not null, – Department number
foreign key (deptno) references dept(deptno)
);
insert into dept values(10,‘ Finance Department ’,‘ Beijing ’);
insert into dept values(20,‘ R & D department ’,‘ Shanghai ’);
insert into dept values(30,‘ Sales Department ’,‘ Guangzhou ’);
insert into dept values(40,‘ Administration Department ’,‘ Shenzhen ’);
insert into emp values(7369,‘ Liu Yi ’,‘ staff member ’,7902,‘1980-12-17’,800,null,20);
insert into emp values(7499,‘ Chen er ’,‘ salesman ’,7698,‘1981-02-20’,1600,300,30);
insert into emp values(7521,‘ Zhang San ’,‘ salesman ’,7698,‘1981-02-22’,1250,500,30);
insert into emp values(7566,‘ Li Si ’,‘ manager ’,7839,‘1981-04-02’,2975,null,20);
insert into emp values(7654,‘ Wang Wu ’,‘ salesman ’,7698,‘1981-09-28’,1250,1400,30);
insert into emp values(7698,‘ Zhao Liu ’,‘ manager ’,7839,‘1981-05-01’,2850,null,30);
insert into emp values(7782,‘ Sun Qi ’,‘ manager ’,7839,‘1981-06-09’,2450,null,10);
insert into emp values(7788,‘ Zhou Ba ’,‘ analyst ’,7566,‘1987-06-13’,3000,null,20);
insert into emp values(7839,‘ Wu Jiu ’,‘ CEO ’,null,‘1981-11-17’,5000,null,10);
insert into emp values(7844,‘ Zheng Shi ’,‘ salesman ’,7698,‘1981-09-08’,1500,0,30);
insert into emp values(7876,‘ Guo Jing ’,‘ staff member ’,7788,‘1987-06-13’,1100,null,20);
insert into emp values(7900,‘ linghu chong ’,‘ staff member ’,7698,‘1981-12-03’,950,null,30);
insert into emp values(7902,‘ zhang wuji ’,‘ analyst ’,7566,‘1981-12-03’,3000,null,20);
insert into emp values(7934,‘ Guo Yang ’,‘ staff member ’,7782,‘1983-01-23’,1300,null,10);

– 1. List all departments with at least one employee .
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(1)>1;
– 2. List salary ratio " Liu Yi " All employees of the company .
– SELECT * FROM emp WHERE ename =‘ Liu Yi ’ ; – sal 800
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename =' Liu Yi ') ORDER BY
sal;
– 3. List the names of all employees and their immediate supervisors .
SELECT e1.ename,(SELECT ename FROM emp b WHERE b.empno =e1.mgr) FROM emp e1
select e.ename,m.ename from emp e left join emp m on e.mgr=m.empno;

– 4. List all employees whose employment date is earlier than their direct supervisor .
select e.ename,e.hiredate,m.ename, m.hiredate from emp e left join emp m on e.
mgr=m.empno WHERE e.hiredate<m.hiredate;
– 5. List Department names and employee information for these departments , Also list those departments that do not have employees .
SELECT dname,emp.* FROM emp LEFT JOIN dept ON emp.deptno =dept.deptno;
– 6. List all job by “ staff member ” Name of and department .
SELECT dname,ename FROM emp LEFT JOIN dept ON emp.deptno =dept.deptno WHERE job
=' staff member ';
– 7. List minimum salary greater than 1500 All kinds of work .
SELECT * FROM emp WHERE sal>1500;
– 8. List in Department “ Sales Department ” Name of employee working , Suppose you don't know the department number of the sales department .
SELECT dname,emp.* FROM emp LEFT JOIN dept ON emp.deptno =dept.deptno WHERE emp
.deptno=(SELECT deptno FROM dept WHERE dept.dname=' Sales Department ');
– 9. List all employees whose salary is higher than the average salary of the company .
SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp);
– 10. List and " Zhou Ba " All employees engaged in the same work .
SELECT * FROM emp WHERE emp.job =(SELECT job FROM emp e WHERE e.ename = ' Zhou Ba ');
– 11. List salary equal to Department 30 Name and salary of all employees in the .
select ename ,sal,deptno from emp where sal in(select sal from emp where deptno
=30) AND emp.deptno !=30;
– 12. List departments with higher salaries 30 Name and salary of all employees working .
select ename ,sal,deptno from emp where sal > (select max(sal) from emp where
deptno=30) AND emp.deptno !=30;
– 13. List the number of employees working in each department , average wage .

SELECT deptno,COUNT(ename) Number of employees ,ROUND(AVG(sal),2) average wage FROM emp GROUP BY deptno

– 14. List the names of all employees , Department name and salary .

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

– 15. List the details and number of people in all departments .
SELECT dept.*,COUNT(1) FROM emp LEFT JOIN dept ON emp.deptno =dept.deptno GROUP
BY emp.deptno;
– 16. List the minimum wage for various jobs .
SELECT job,MIN(sal) FROM emp GROUP BY job;
– 17. List the of each department manager Minimum salary .
SELECT deptno,job ,MIN(sal) FROM emp e WHERE e.job=' manager ' GROUP BY deptno;
– 18. List the annual salary of all employees , Sort by annual salary from low to high .
SELECT sal*12 Annual salary , emp.* FROM emp ORDER BY Annual salary ;
– 19. Find out emp The salary in the table is 3000 above ( include 3000) Employee number of all employees , full name , salary .
SELECT empno,ename,sal FROM emp WHERE sal>=3000;
– 20. Find out all salaries in ’ Chen er ’ All personnel information above .
SELECT * FROM emp WHERE sal> (SELECT e.sal FROM emp e WHERE e.ename = ' Chen er ' );
– 21. Find out emp The department number in the table is 20, Salary in 2000 above ( barring 2000) All employees , Show their employee number , Name and salary , Display with the following name : Employee number Employee name
salary
SELECT deptno,ename,sal FROM emp WHERE sal>2000 AND deptno =20;
– 22. Find out emp All types of work in the table ( No repetition )
SELECT job FROM emp GROUP BY job HAVING count(1)=1;
– 23. Find all bonuses (comm) All information of personnel whose field is not empty .
SELECT * FROM emp WHERE comm NOT NULL;
– 24. Find out the salary in 800 reach 2500 between ( Closed interval ) Information of all employees .( notes : It is implemented in two ways and as well as between and)
SELECT * FROM emp WHERE sal>=800 AND sal<= 2500; SELECT * FROM emp WHERE sal
BETWEEN 800 AND 2500;
– 25. The employee number found is 7521,7900,7782 Information of all employees .( notes : It is implemented in two ways ,or as well as in)
SELECT * FROM emp WHERE empno =7521 OR empno =7900 OR empno =7782; SELECT *
FROM emp WHERE empno IN ( 7521, 7900,7782 );
– 26. Find out there are in the name “ Zhang ” character , And the salary is 1000 above ( barring 1000) All employee information .
SELECT * FROM emp WHERE ename LIKE '% Zhang %' AND sal >1000;
– 27. The third character of the name is “ Avoid ” All employee information .
SELECT * FROM emp WHERE ename LIKE '__ Avoid ' ;
– 28. Sort all employees in ascending salary order , Those with the same salary shall be sorted in descending order according to the employment time .
SELECT * FROM emp ORDER BY sal,hiredate DESC;
– 29. Sort all employees in ascending alphabetical order , Those with the same initial are sorted in descending order of salary . order by convert(name using gbk) asc;
select * from emp order by convert(substring(ename,1,1) using gbk) asc, sal
desc;
– 30. Find out the name of the person who worked first , Entry time and salary .
SELECT ename,hiredate,sal FROM emp e WHERE hiredate = (select min(hiredate)
from emp);
– 31. Displays the names of all employees , salary , bonus , If there is no bonus , Temporary display 100.
– ifnull Function in an expression is NULL Gets the value from another expression
select ename,sal, ifnull(comm,100) from emp
– 32. A position that shows the highest salary .
SELECT job FROM emp WHERE sal = (select max(sal) from emp);
– 33. Find out emp Maximum and minimum salaries for all departments in the table , Department number is 10 Department not displayed .
SELECT deptno Department number , MAX(sal) ceiling on wages , min(sal) minimum wages FROM emp WHERE deptno !=10
GROUP BY deptno;
– 34. delete 10 The highest paid employee in department No .
delete from emp where empno = (select empno from (SELECT empno FROM emp WHERE
deptno=10 AND job != ' CEO ' and sal>=all(select sal from emp where deptno=10 AND
job!= ' CEO ')) as temp);
SELECT * FROM emp WHERE deptno=10;5
– 35. Reduce the salary of the highest paid employee 30%.
UPDATE emp SET sal = sal*0.7 WHERE empno =(select empno from (SELECT empno FROM
empWHERE job != ' CEO ' and sal>=all(select sal from emp where job != ' CEO ') LIMIT
1) as temp);
– 36. Query employee name , Wages and Wage scale ( wages >=3000 by 3 level , wages >2000 by 2 level , wages <=2000 by 1 level )
select ename, sal, case when sal>3000 then '3 level ' when sal>2000 then '2 level ' else
'1 level ' end from emp ORDER BY sal DESC;

Technology
©2019-2020 Toolsou All rights reserved,
【 Explain in detail 】 Interview must ask :SpringBoot Automatic configuration principle 【 Hyper detail 】Java Realization of student information management system 2020 The 11th National Blue Bridge Cup Python group JavaScript note ( five )---- Branch statement MYSQL database SQL Sentence practice experiment EXERCISES. 1 SIMPLE COMMANDSMySQL Basics Commonly used sentence ( Add / delete / modify query )C++ of string of compare usage vue limit input Only positive numbers can be entered C Language of a Gobang game implementation Programmer refused due to low salary offer,HR become shame , Netizens instantly blew up ..