MySQL Learn the third day exercise (multi-table connection)

--Multi-table connection--write a query to query employee name, department name, work placeSelectEname,dname,loc fromemp,deptwhereEmp.deptno=Dept.deptno--Exercise 1--1, write a query, display all employee name, department number, department nameSelectEname,dept.deptno,dname fromemp,deptwhereEmp.deptno=Dept.deptno--2, write a query, show all work in Chicago and bonuses are not empty employees--name, work place, bonusSelectEname,loc,comm fromemp,deptwhereEmp.deptno=Dept.deptno andLoc= 'CHICAGO' andComm is  not NULL--3. Write a query that shows all names of employees with a character in the name, work placeSelectEname,loc fromemp,deptwhereEmp.deptno=Dept.deptno andEname like '%a%'--query Each employee's name, salary, salary levelSelectEname,sal,grade fromEmp,salgradewhereSal>=Losal andSal<=HisalSelectEname,sal,grade fromEmp,salgradewhereSalbetweenLosal andHisal--Exercise 2--1, to inquire each employee's number, the name, the salary grade, according to the salary grade enters--Row Ascending orderSelectEmpno,ename,grade,loc fromEmp,dept,salgradewhereEmp.deptno=Dept.deptno andSal>=Losal andSal<=HisalOrder  byGrade--2. Self-Connection--query each employee's name and direct superior nameSelectT1.ename employee name, T2.ename Direct Superior name fromEMP t1,emp T2whereT1.mgr=T2.empno--Exercise 3--query All jobs in York and Chicago for employee names, employee numbers, and--their manager's name, manager numberSelectt1.ename,t1.empno,t2.ename Manager Name, T2.empno Manager number fromEMP t1,emp t2,deptwhereT1.mgr=T2.empno andT1.deptno=Dept.deptno andLocinch('NEW YORK','CHICAGO')--3. Cross-connectSelect * fromEMP Cross JoinDept--4. Natural ConnectionSelect * fromempnaturalJoinDept--5. Using clauseSelect * fromEMPJoindeptusing (DEPTNO)--6. ON clauseSelect * fromEMPJoinDept onEmp.deptno=Dept.deptnoJoinSalgrade onSalbetweenLosal andHisalwhereEmp.deptno= Ten--Check the information of employees and departments, and ask to show the departments that have no employees.Select * fromEMPJoinDept onEmp.deptno=Dept.deptnoSelect * fromemp,deptwhereEmp.deptno=Dept.deptno--7, inquires the staff and the department information, requests the Department which does not have the staff also displaysSelect * fromDept Left JoinEMP onDept.deptno=Emp.deptnoSelect * fromEMP Right JoinDept onEmp.deptno=Dept.deptno--Check the name of the employee and the name of the Superior, and ask for the name of the employee without a superior.Selectt1.ename name, T2.ename Manager name fromEMP T1 Left JoinEMP T2 onT1.mgr=T2.empno--Exercise 4--use sql-99 notation to complete the following exercises--1. Create a cross-connection between an employee table and a departmental tableSelect * fromEMP Cross JoinDept--2, using natural connection, showing the date of entry after May 1, 80--Employee Name, department name, entry dateSelectename,dname,hiredate fromempnaturalJoinDeptwhereHireDate>= '1980-05-01'--3, using the USE clause, display the employee's name, department name, work place in ChicagoSelectEname,dname,loc fromEMPJoindeptusing (DEPTNO)whereLoc= 'CHICAGO'--4. Use the ON clause to display the employee's name, department name, work place, salary level in ChicagoSelectEname,dname,loc,grade fromEMPJoinDept onEmp.deptno=Dept.deptnoJoinSalgrade onSalbetweenLosal andHisalwhereLoc= 'CHICAGO'--5, use left connection, query each employee name, manager name, no experience of King also to show outSelectE.ename,m.ename fromEMP E Left JoinEMP M onE.mgr=M.empno

MySQL Learn the third day exercise (multi-table connection)