Common basic problem sets and notes of SQL database

--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

Programming VIP