# Common basic problem sets and notes of SQL database

catalogue

object

Problem set:

--01 list the names of all departments with at least 3 employees

--02 list all employees whose salary is more than 'SMITH'. A sub query is used here (that is, the sub query of SMITH's salary)

--03 list the names of all employees and their immediate supervisors

--04 list employees whose employment date is earlier than their direct supervisor

--05 list Department names and employee information of these departments, including those without employees

--06 list the names of all employees whose job s are clerk and the names of their departments

--07 list all kinds of jobs with a minimum salary of more than 1500

--08 list the names of employees working in the SALES department, assuming they do not know the department number of the SALES department

--09 list all employees whose salary is higher than the average salary of the company

--10 list people who do the same work as employee 'SCOTT'

--11 list the names and salaries of all employees whose salary is equal to the salary of employees in 30 departments,

--12 list employees whose salary is higher than that of all employees in department 30 and their salary

--13 list the number of employees working in each department, the average salary and the average length of service

--14 list the names, Department names and wages of all employees

--15 list a combination of employees who work in the same job but belong to different departments,

--16 list details of all departments

--17 list the minimum wage for various jobs

--18 list the minimum salary of managers in each department

--19 list the annual salary (including allowance) of all employees, which is ranked from low to high,

--20 list the average salary of employees whose position is clerk

--21 list the average salary of clerk, grouped by department

--22 list the average salary of clerk, grouped by department, with department number in(20,30) and the number of department employees > = 2, in descending order according to the average salary,

## object

scott.sql file - other document resources - CSDN Library

This problem set is based on the table

dept table:

 DNAME LOC DEPTNO ACCOUNTING NEW YORK 10 RESEARCH DALLAS 20 SALES CHICAGO 30 OPERATIONS BOSTON 40

emp table:

## Problem set:

### List the names of all departments with at least 3 employees

--Analysis: there are department numbers and detailed data in emp table and department numbers and names in dept table. Therefore, they need to be connected through Department numbers first; Then they need to be divided into groups; Finally, count the data divided into groups and compare with 3. (Note: after grouping, use having for group level filtering)

`select d.dname from emp e join dept d on(e.deptno = d.deptno) group by d.dname having count(d.dname) >= 3;`

### List all employees whose salary is more than 'SMITH'. A sub query is used here (that is, the sub query of SMITH's salary)

`select * from emp e where sal > (select sal from emp where ename = 'SMITH');`

### List the names of all employees and their immediate supervisors

`select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;`

### List employees whose employment date is earlier than their immediate supervisor

`select e.ename staff,m.ename superior,e.hiredate Employee entry date,m.hiredate Entry date of its superior from emp e,emp m where e.mgr = m.empno and m.hiredate > e.hiredate;`

### List Department names and employee information of these departments, including those without employees

`select d.dname,e.ename from emp e right join dept d using(deptno);`

### List the names of all employees whose job s are clerk and the names of their departments

--using(deptno) is synonymous with where(e.deptno=d.deptno)

```select d.dname,e.ename from emp e join dept d using(deptno) where e.job = 'CLERK';

select d.dname,e.ename from emp e join dept d where(e.deptno=d.deptno) and e.job like 'CLERK';```

### List jobs with a minimum salary of more than 1500

`select e.job from emp e group by e.job having min(sal) > 1500;`

### List the names of employees working in the SALES department, assuming they do not know the department number of the SALES department

`select e.ename from emp e join dept d using(deptno) where d.dname = 'sales';`

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

`select * from emp e where sal > (select avg(sal) from emp);`

### List people who do the same work as employee 'SCOTT'

`select * from emp e where job = (select job from emp where ename = 'scott') and ename<>'scott';`

### List the names and salaries of all employees whose salary is equal to the salary of employees in 30 departments,

When there are multiple targets, you can't only use "=", but use "= any" and the latter "in"

```select * from emp where sal = any(select sal from emp where deptno = 30) and deptno <> 30;

select * from emp where sal in (select sal from emp where deptno = 30) and deptno <> 30;```

### List employees whose salary is higher than that of all employees in department 30 and their salary

`select * from emp where sal > all(select sal from emp where deptno = 30) and deptno <> 30;`

### List the number of employees working in each department, average salary and average length of service

select deptno,count(ename),avg(sal),avg(TO_DAYS(now()-hiredate)) from emp group by deptno;

--In fact, there is a lack of 40 departments without employees. In order to count to 40 departments, connect the dept table

`select deptno,count(ename),avg(sal),avg(TO_DAYS(now()-hiredate)) from emp right join dept d using(deptno) group by deptno;`

### List the names, Department names and salaries of all employees

`select ename full name,deptno Department name,sal wages from emp right join dept using(deptno);`

### List a combination of employees who work in the same job but belong to different departments,

select e.empno,e.ename,m.empno,m.ename from emp e ,emp m where e.job = m.job and e.deptno<>m.deptno;

In this case, there are repeated calculations, such as Miller & Smith and Smith & Miller. To avoid this situation, just change e.deptno < > m.deptno to e.deptno > m.deptno or e.deptno < m.deptno, so as to avoid the problem of repeated statistics

`select e.empno,e.ename,m.empno,m.ename from emp e ,emp m where e.job = m.job and e.deptno<m.deptno;`

### List details of all departments

`select * from emp e join dept d using(deptno) group by d.dname;`

### List the minimum wage for various jobs

`select job,min(sal) minimum wage from emp group by deptno;`

### List the minimum salary of managers in each department

`select min(sal) from emp where job = 'manager' group by deptno;`

### List the annual salary (including allowance) of all employees, arranged from low to high,

order by sal/order by sal desc,

To avoid comm being empty, an ifnull(comm,0) can be added,

ifnull(a,b) usage: if it is not empty, return a; if it is empty, return B

Here, the second generation refers to (sal+ifnull(comm,0))*12; If 1 means ename

`select ename,(sal+ifnull(comm,0))*12 from emp e order by 2;`

### List the average salary of employees whose position is clerk

`select avg(sal) from emp e where  e.job = 'clerk';`

### List the average salary of clerk, grouped by department

`select deptno,avg(sal) from emp e where e.job = 'clerk' group by deptno;`

### List the average salary of clerk, grouped by department, with department number in(20,30) and number of department employees > = 2, in descending order according to the average salary,

in(10,30) means only 10,30

`select deptno,avg(sal) from emp e where e.job = 'clerk' and deptno in (20,30) group by deptno having count(empno) >=2 order by avg(sal) desc;`

Keywords: Database SQL linq

Added by swatisonee on Fri, 25 Feb 2022 11:30:32 +0200