Common basic statements of MySQL 2

Time:2021-1-3

Subquery exercise

db40

List all employees whose salary is higher than Wang Haitao's, and display their name and salary
Select name, Sal from EMP where Sal > (select Sal from EMP where name = "Wang Haitao");
List all employees in the same position as Liu peixia, showing their names and positions.
Select name, job from EMP where job = (select job from EMP where name = "Liu peixia");
List the employees whose salary is higher than that of all employees in the Department of big data (known department number is 30), and display the employee name, salary and department name.
select e.name,e.sal,d.name from emp e join dept d on sal>(select MAX(sal) from emp where dept_id=30);
Plus no department staff
select e.name,e.sal,d.name from emp e left join dept d on e.dept_id=d.id where  sal>(select MAX(sal) from emp where dept_id=30);

Multi table query exercise

. list the employees who work in the training and excellence department. Assume they don't know the department number of the training and excellence department. Display the Department name and employee name.
Select d.name, e.name from dept D join EMP E on d.name = "training department" where D.id = e.dept_ ID;


(self query) list all employees and their direct superiors, display employee name, superior number and superior name
 select c.name,c.id,c.topid,p.name,p.id from emp c join emp p on c.topid=p.id;
 
 
List all kinds of positions with minimum salary greater than 1500, and display the position and the minimum salary of the position
select job,min(sal) from emp group by job having min(sal)>1500;

 //Supplement the difference between where and having
 //1. All can be filtered. 2. Where is to filter data before grouping; having is to filter data after grouping. 3. Where cannot be used with aggregate function, nor with order / group


List the number of employees in each department and the average salary. Display department number, number of employees and average salary.
select count(*),avg(sal),dept_id from emp group by dept_id;


Find out the Department with at least one employee, and display the department number, department name, Department location and department number.
select e.dept_id,d.name,d.loc,count(*) from emp e join dept d on e.dept_id=d.id group by dept_id having count(*)>0;


List all employees whose employment date is earlier than the immediate superior, and display employee number, employee name and department name.
select c.id,c.name,c.dept_id,d.name dept_name from emp c,emp p, dept d where c.topid=p.id and c.dept_id=d.id and c.hdate<p.hdate;


Query the information of the highest paid employee in the employee table
 select * from emp order by sal desc limit 0,1;