The first version of common SQL (MySQL)

Time:2020-1-13

Table structure

The first version of common SQL (MySQL)

The first version of common SQL (MySQL)

1. List all departments with at least one employee no

  1. routine

select dname from dept where deptno in(select deptno from emp);

2. How to use exists?

select dname from dept where exists(select NULL from emp where emp.deptno=dept.deptno);

3. Use grouping

select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno)>=1);

2. List all employees with more salary than “Smith”

select * from emp where sal > (select sal from emp where ename=’SMITH’)

Using exises

select * from emp A where exists (select null from emp B where A.SAL>B.SAL and Ename =’SMITH’)

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

1.
select m.ename,n.ename from emp m left join emp n on m.mgr=n.empno
2.
select m.ename ,(select n.ename from emp n where n.empno=m.mgr )as bossname from emp m

4. List employees whose employment date is earlier than that of their immediate supervisor

select m.empno,m.ename,m.mgr,m.hiredate,n.empno,n.ename,n.hiredate from emp m join emp n on m.mgr=n.empno where m.hiredate<n.hiredate

5. List the Department name and employee information of these departments, as well as those departments without employees

1.
select dept.dname,emp.* from dept left join emp on dept.deptno=emp.deptno order by dept.dname
2.
select dept.dname,emp.* from dept left join emp Using(deptno) order by dept.dname

6. List the names of all clerks and their departments

select ENAME,DNAME,job from emp JOIN dept on emp.deptno=dept.deptno where emp.job =’CLERK’;

7. List all kinds of jobs with minimum salary more than 1500

select job from emp group by job having min(sal)>1500

select * from emp where job in(select job from emp group by job having min(sal)>1500);

8. List the name of the staff in the Department ‘sales’, assuming they do not know the department number of the sales department

select * from emp join (select deptno from dept where dname=’sales’) tmp using (deptno);

  1. routine

select dname from dept where deptno in(select deptno from emp);

2. How to use exists?

select dname from dept where exists(select NULL from emp where emp.deptno=dept.deptno);

3. Use grouping

select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno)>=1);

9. List all employees whose salary is higher than the average salary of the company

select ename from emp where sal>(select avg(sal) from emp );

10. List all employees who do the same job as “Scott”

select * from emp A where exists(select job from emp B where ename=’SCOTT’ AND A.JOB=B.JOB) AND eNAME !=’scott’;

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

select ename,sal,deptno from emp where sal in(select sal from emp where deptno=’30’) and deptno <>’30’;

12. List the names and salaries of all employees whose salaries are higher than those of employees in department 30

select ename,sal,deptno from emp where sal >ALL(select SAL from emp where deptno=’30’) and deptno <>’30’;
select ename,sal,deptno from emp where sal >(select MAX(SAL) from emp where deptno=’30’) and deptno <>’30’;

13. List the number of employees working in each department, average salary and average service period

select count(empno),avg(sal),deptno from emp right join dept using(deptno) group by deptno

14. List all employees’ names, Department names and salaries (salary + commission)

select empno,dname,sal+comm as wage from emp join dept using(deptno);

15. List details of all departments and number of departments

select dept.* from dept join emp using(deptno);

16. List the minimum wage for various jobs

select job ,min(sal) from emp group by job

17. List the minimum salary of the manager of each department

select min(sal) as sal,deptno from emp where job=’manager’ group by deptno

18. List the annual salary (salary + commission) of all employees, sorted from the lowest to the highest

select (sal+comm)*12 as money,ename from emp order by money

19. Use an SQL statement to query the data of the first three salaries of each department in all EMP tables, as shown below: sal1 has the highest salary, and then backward

deptno sal1 sal2 sal3

select sal from emp