SQL Classic Statement

15 Questions need re-analysis. Didn't understand. Use the EMP table and Dept table under the Scott/tiger user to complete the following exercises,

The structure of the table is described below for the EMP employee table (EMPNO employee number/ename Employee Name/job work/mgr Superior number/hiredate Employment Date/sal Salary/comm Commission/deptno Department number) Dept Department Table (DEPTNO department number/ Dname department name/loc location) Salary = salary + commission

1. List all departments that have at least one employee.

2. List all employees who pay more than "SMITH".

3. Lists the names of all employees and their immediate superiors.

4. List all employees whose employment date is earlier than their immediate superiors.

5. Lists the department names and employee information for those departments, and lists the departments that have no employees.

6. Lists the names of all "clerk" (clerks) and their department names.

7. Lists various jobs with a minimum salary of more than 1500.

8. Lists the names of employees who work in the department "sales", assuming they do not know the department number of the sales department.

9. List all employees who pay more than the company's average salary. 10. List all employees who work in the same way as "SCOTT".

11. Lists the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30.

12. Lists the employee's name and salary for salaries higher than the salary of all employees who work in department 30.

13. Lists the number of employees, average wages, and average service duration for each department.

14. Lists the names, department names, and wages of all employees.

15. Lists detailed information and number of departments for all departments.

16. List the minimum wage for each job.

17. Lists the manager (manager) minimum salary for each department.

18. Lists the annual salary of all employees, sorted from lowest to highest annual salary.

Solution: 1. List all departments that have at least one employee. (Two-table union query, and the use of group by...having)

Select Dname from dept where Deptno in (select Deptno from EMP GROUP by DEPTNO have Count (*) >1);

2. List all employees who pay more than "SMITH". (Classic self-connect query)

Select ename from emp where sal> (select Sal from emp where ename like ' SMITH ');

3. Lists the names of all employees and their immediate superiors. (Querying yourself multiple times for a table, an internal query can refer to the field of an external object like an object, where the reference is similar to the scope in programming, i.e., with the {} analogy)

Select Ename, (select ename from emp where empno in (a.mgr)) from EMP A;

4. List all employees whose employment date is earlier than their immediate superiors. (Ibid., dates can be compared directly)

Select ename from emp a where hiredate< (select HireDate from emp where empno in (a.mgr));

5. Lists the department name and employee information for those departments, and lists the departments that do not have an employee (with the EMP table as the main, left connection query)

Select dname,emp.* from dept left join EMP on DEPT.DEPTNO=EMP.DEPTNO;

6. Lists the names of all "clerk" (clerks) and their department names. (Domain, note ())

Select Ename, (select Dname from dept where Deptno in (A.deptno)) as dname from EMP A where JOB like ' clerk ';

7. Lists various jobs with a minimum salary of more than 1500. Select Job from EMP where sal>1500;

8. Lists the names of employees who work in the department "sales", assuming they do not know the department number of the sales department. (Classic two-table connection)

Select ename from emp where deptno= (select Deptno from dept where dname like ' SALES ');

9. List all employees who pay more than the company's average salary. (Check yourself repeatedly) select ename from emp where sal> (select AVG (SAL) from EMP);

10. List all employees who work in the same way as "SCOTT". (Exclude yourself)

Select ename from emp where job in (the Select Job from emp where ename like ' Scott ') and ename!= ' Scott ';

11. Lists the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30. (Any usage, and crowding out)

Select Ename,sal from emp where Sal=any (select Sal from emp wheredeptno=30) and deptno!=30;

12. Lists the employee's name and salary for salaries higher than the salary of all employees who work in department 30. (Usage of Max)

Select Sal,ename from emp where sal> (select Max (SAL) from EMP where deptno=30);

13. Lists the number of employees, average wages, and average service duration for each (keyword, group by) department. (Classic group by usage)

Select Deptno,count (*), AVG (a.sal), AVG (sysdate-hiredate) from the EMP a group by DEPTNO;

14. Lists the names, department names, and wages of all employees. (The classic two-table connection query, replace the ID of the primary key in a table with a specific name (solve the problem that many people will encounter in the actual application can not bind multiple columns), can also be used where to query, compared with the title 5)

Select Ename,sal, (select Dname from dept a where A.deptno=b.deptno) as dname from EMP B;

15. Lists detailed information and number of departments for all departments. (AS is *, all fields of dept and subsequent temporary table B will be displayed (note: Not just dept fields, Note *))

SELECT * FROM Dept a LEFT join (select Deptno,count (*) from EMP Group by Deptno) b on A.deptno=b.deptno;

16. List the various (with each synonymous (see title 13)) work minimum wage. Select Job,min (SAL) from the EMP group by job;

17. Lists the minimum salary for each department manager (managers, managers only, without group by).

Select min (sal) from EMP where job like ' manager ';(because the manager is a value not a field, it cannot be lowercase)

18. Lists the annual salary of all employees, sorted from lowest to highest annual salary. (NVL: null conversion function)

Select ENAME,SAL+NVL (comm,0) as Sal from emp order by Sal

Comment: null conversion function, Oracel:nvl;mysql is: ifnull (EXP1,EXP2)

19. Query the maximum wage and minimum wage for each department, and export the department number, department name

Select Deptno, (select Dname from dept where deptno = E.deptno) as Dname, Max (sal) as ' maximum wage ', min (sal) as ' minimum wage ' from EMP E GROUP by Deptno