MySQL single-table query && multi-table query (staff table 14+9)

Dept (deptno,dname,loc) EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) Salgrade (grade,losal,hisal) Stu (SID, Sname,age,gander,province,tuition)

Single-Table query questions ====================================================

Dept (deptno,dname,loc) EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) Salgrade (grade,losal,hisal) Stu (SID, Sname,age,gander,province,tuition)

1. Find all employees with department number 30SELECTStu.sname fromStuWHEREStu.sid=  -2name, number, and department number of all sales staff. SELECTEmp.empno,emp.ename,emp.deptno fromEMPWHEREEmp.job= 'Sales Clerk'3find employees with bonuses above their salaries. SELECTEmp.ename fromEMPWHEREEmp.comm>Emp.sal4. Find out bonuses above salary 60%of employees. SELECTEmp.ename fromEMPWHEREEmp.comm>Emp.sal*0.65identify all the managers in the department Number 10, and the department number 20 for all sales staff details. SELECTEmp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,emp.deptno fromEMPWHERE(Emp.job= 'Manager'  andEmp.deptno=  -)OR(Emp.job= 'Sales Clerk'  andEmp.deptno= Ten)6Find all the managers in the department Number 10, the department number 20 all salespeople, and the details of all employees who are not managers and are not salespeople but whose wages are large or equal to 20000. SELECTEmp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,emp.deptno fromEMPWHERE(Emp.job= 'Manager'  andEmp.deptno=  -)OR(Emp.job= 'Sales Clerk'  andEmp.deptno= Ten)OR(Emp.job!= 'Manager'  andEmp.job!= 'Sales Clerk'  andEmp.sal>=20000)7employees with no bonuses or bonuses below 1000. SELECTEmp.empno,emp.ename,emp.job fromEMPWHEREEmp.comm<  + OREmp.comm is NULL8query an employee whose name consists of three words. SELECTEmp.ename fromEMPWHEREEmp.ename like '___'9the employee who entered the job in 2000. SELECTEmp.ename fromEMPWHEREEmp.hiredate like '2000-__-__'#'2000%'Ten. Query all employee details, sorted in ascending order of numbersSELECTEmp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno fromEMPORDER  byEmp.empnoASC One. Query all employee details, sorted in descending order if wages are the same using the entry date in ascending orderSELECTEmp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno fromEMPORDER  byEmp.salDESC, Emp.hiredateASC A. Query the average salary for each departmentSELECTEmp.deptno,AVG(SAL) fromEMPGROUP  byEmp.deptno -. Query the number of employees in each department. SELECTEmp.deptno,COUNT(1) fromEMPGROUP  byEmp.deptno -. Query the maximum wage, minimum wage, number of persons for each jobSELECTEmp.job,MAX(SAL),MIN(SAL),COUNT(1) asnumber fromEMPGROUP  byEmp.job

Multi-table Query topics====================================================

Dept (deptno,dname,loc) EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) Salgrade (grade,losal,hisal) Stu (SID, Sname,age,gander,province,tuition)

1identify the department with at least one employee. Show department number, department name, department location, number of departments. SELECTDept.deptno,dept.dname,dept.loc,COUNT(Emp.deptno) asNumber of Departments fromdept,empWHEREDept.deptno=Emp.deptnoGROUP  byDept.deptno2. Lists the names of all employees and their immediate superiors. SELECTE.ename, Ifnull (M.ename,'BOSS') as Lead fromEMP E Left JOINEMP M onE.mgr=M.empno;3lists the number, name, and department name of all employees whose employment date is earlier than the direct ancestor. SELECTE.empno,e.ename,d.dname fromEMP aseINNER JOINEmp asM onE.mgr=M.empno Left JOINDept asD onE.deptno=D.deptnoWHEREe.hiredate<m.hiredate4lists the department names and employee information for those departments, and lists the departments that have no employees. SELECTE.*, D.dname fromEMP E Right JOINDept D onE.deptno=D.deptno;5List of jobs with minimum salary greater than 15000 and number of employees engaged in the work. SELECTE.job,COUNT(*) asNumber of employees fromEMP EGROUP  byJob having MIN(SAL)>150006lists the names of employees who work in the sales department, assuming they do not know the department number of the sales department. SELECTE.ename fromEMP EWHERE(SELECTE.deptno fromDeptWHEREDname='Sales Department'        )7List all employee information for salary higher than company average salary, department name, supervisor, salary level. SELECTE.*, D.dname,s.grade fromemp e NATURAL Left JOINDept D Left JOINEMP m onM.empno=E.mgr Left JOINSalgrade s onE.salbetweenS.losal andS.hisalWHEREE.sal>(SELECT AVG(SAL) fromEMP)8List all employees and department names that are in the same job as Pang Tong. SELECTE.*, D.dname fromEMP E, dept DWHEREE.deptno=D.deptno andE.job=(SELECTJob fromEmpWHEREEname='Pang Tong');9The name and salary of the employee who paid the salary higher than the salaries of all the employees working in department 30. SELECTE.ename, e.sal, D.dname fromEMP E, dept DWHEREE.deptno=D.deptno andSal>  All(SELECTSal fromEmpWHEREDeptno= -) 

MySQL single-table query && multi-table query (staff table 14+9)