Course reference

Ali Cloud developer community – database learning route

Database and SQL/MySQL basic exercises

Select * from all employees; select * from all employees;

Query the employee whose bonus is 60% higher than salary

mysql> select * from emp where comm>sal*0.6;
Copy the code

3. Details of all the managers in section 10 and all the salesmen in section 20

mysql> select * from emp where (deptno=10 and job='managers') or (deptno=20 and job='Salesman');
Copy the code

4. Not a manager or a salesman

mysql> select * from emp where (deptno=10 and job='managers') or (deptno=20 and job='Salesman') or (job not in ('managers'.'Salesman') and sal>=20000);
Copy the code

5. No bonus

mysql> select * from emp where comm is null or comm<1000;
Copy the code

The name is three words

mysql> select * from emp where ename like '_';
Copy the code

7. Employees who entered the company in 2000 (fuzzy query)

mysql> select * from emp where hiredate like '2000 - %';
Copy the code

8, number ascending sort

mysql> select * from emp order by empno asc;
Copy the code

9. Use descending salary order. If the salary is the same, use ascending date order

mysql> select * from emp order by sal desc, hiredate asc;
Copy the code

Query the average salary for each department

Select deptno, avg(sal) from mysql> select deptno, avg(salfrom emp group by deptno;
Copy the code

11. Query the number of employees in each department

mysql> select deptno, count(*) from emp group by deptno;
Copy the code

The highest minimum wage for each job, the number of people

mysql> select job, max(sal), min(sal), count(*) from emp group by job;
Copy the code

SQL/MySQL advanced and query exercises

1, find out the department with at least one employee, display the department number, department name, department location, department number.

  • Columns: D.deptno, D.name, D.oc, number of department
  • Dept D, emp e
  • Condition: e.d eptno = d.d eptno

Create a new table with the number of people in each department, then use the inner join to merge the table with the department table and remove the Cartesian product, then display the required columns.

mysql> select d.*, z1.cnt from dept d inner join (select deptno, count(*) cnt from emp group by deptno) z1 where d.deptno=z1.deptno;
Copy the code
deptno dname loc cnt
10 Actuarial-oriented) Beijing 3
20 Studies ministry Shanghai 5
30 The sales department Guangzhou 6

List the names of all employees and their immediate supervisors

  • Column: name of employee, name of superior
  • Table: EMp E, EMP M
  • Condition: Employee’s MGR = superior’s EMPno

Because the employee may not have a superior, but also want to show it, so use the left outer link

mysql> select e.ename, m.ename from emp e left outer join emp m on e.mgr=m.empno;
Copy the code
ename ename
Gan ning Pang tong
Diane yee silk Guan yu
Yin was Guan yu
Liu bei Once pig
Thank sun Guan yu
Guan yu Once pig
Zhang fei Once pig
The various ge is bright Liu bei
Once pig NULL
Ha way Guan yu
Zhou tai The various ge is bright
Cheng pu Guan yu
Pang tong Liu bei
Huang gai Zhang fei
Zhang SAN Gan ning

List the numbers, names, and department names of all employees who were employed prior to their immediate supervisor

  • Columns: empno, ename, d.name
  • Table emp E, Dept D, EMp m
  • Condition: e.h iredata < m.h iredata

(1) Do not check the department name, only check the department number

  • Columns: E.epno, E.name, e.deptno
  • Table: EMp E, EMP M
  • Condition: e.mgr= m.emno, e.hiredata
mysql> select e.empno, e.ename, e.deptno from emp e, emp m where e.mgr=m.empno and e.hiredate<m.hiredate;
Copy the code

(2) Add the corresponding department to the department table

mysql> select e.empno, e.ename, d.dname from emp e, emp m, dept d where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
Copy the code
empno ename dname
1002 Diane yee silk The sales department
1003 Yin was The sales department
1004 Liu bei Studies ministry
1006 Guan yu The sales department
1007 Zhang fei Actuarial-oriented)
1001 Gan ning Studies ministry

4. List the names of the departments and their employees, as well as the departments with no employees

  • Column: d. name, e.*
  • Table emp e, DEPT D
  • Condition: e.d eptno = d.d eptno

(1) Forget the departments with no employees

mysql> select * from emp e, dept d where e.deptno=d.deptno;
Copy the code

(2) Use the right outer link to display departments without employees

mysql> select * from emp e right outer join dept d on e.deptno=d.deptno;
Copy the code

List all jobs with minimum salary above 15000 and the number of employees doing these jobs

  • Column: e.j ob, count (*)
  • Table: emp e
  • Condition: min (sal) > 15000
  • Grouping: job
mysql> select job, count(*) from emp e group by job having min(sal)>15000;
Copy the code
job count(*)
The manager 3
analysts 2
Chairman of the board of directors 1
cleaner 1

List the names of all the people who work in the sales department. Assume you don’t know the department number

  • Column: e.e name
  • Table emp e, DEPT D
  • E.deptno =d.deptno and d.name = ‘Sales’

(1) Use the subquery, first find the department number of the sales department

mysql> select e.ename from emp e where e.deptno=(select deptno from dept where dname='Sales Department');
Copy the code

(2) Or first join two tables, and then extract the department name as sales department employees

mysql> select e.ename from emp e, dept d where e.deptno=d.deptno and d.dname='Sales Department';
Copy the code
ename
Diane yee silk
Yin was
Thank sun
Guan yu
Ha way
Cheng pu

List all employees who earn more than the company’s average salary, the name of their department, their supervisor, and their salary bracket

(1) Don’t check the salary grade

  • Columns: E.name, D.name, m.name
  • Table: EMp e, EMp M, DEPT D
  • E.sal >(select avg(e.sal) from emp) and e.mgr= m.epno and e.detno = d.detno
mysql> select e.ename, d.dname, m.ename from emp e, emp m, dept d where e.sal>(select avg(sal) from emp) and e.mgr=m.empno and e.deptno=d.deptno;
Copy the code
ename dname ename
The various ge is bright Studies ministry Liu bei
Pang tong Studies ministry Liu bei
Liu bei Studies ministry Once pig
Guan yu The sales department Once pig

(2) Connect the salary scale again

  • Columns: E.name, D.name, m.name, S.glade
  • Table: EMp E, EMP M, Dept D, Salgrade S
  • E.sal >(select AVg (e.sal) from EMp) and E.m gr= m.epno and E.dial = D.dial and E.sal between S.l osal and S.
mysql> select e.ename, d.dname, m.ename, s.grade from emp e, emp m, dept d, salgrade s where e.sal>(select avg(sal) from emp) and e.mgr=m.empno and e.deptno=d.deptno and e.sal between s.losal and s.hisal;
Copy the code
ename dname ename grade
The various ge is bright Studies ministry Liu bei 4
Pang tong Studies ministry Liu bei 4
Liu bei Studies ministry Once pig 4
Guan yu The sales department Once pig 4

(3) Finally eliminate the situation that employees have no superiors and employee departments do not exist

Mysql > select e.name, d.name, m.name, s.glade -- >from-- > emp e left DEPtno d on e.deptno= D.deptno -- > left deptno emp m on e.mgr= m.depno -- > left deptno Select sal from the osal of the growing number of anglosal sal at all timesfrom emp)
Copy the code

or

mysql> select e.ename, d.dname, m.ename, s.grade from emp e left outer join dept d on e.deptno=d.deptno left outer join emp m on e.mgr=m.empno left outer join salgrade s on e.sal between s.losal and s.hisal where e.sal>(select avg(sal) from emp)
Copy the code
ename dname ename grade
Liu bei Studies ministry Once pig 4
Guan yu The sales department Once pig 4
The various ge is bright Studies ministry Liu bei 4
Once pig Actuarial-oriented) NULL 5
Pang tong Studies ministry Liu bei 4
Zhang SAN NULL Gan ning 5

10. List all employees and departments engaged in the same work as Pang Tong

  • Column: e. *, d.d name
  • Table emp e, DEPT D
  • E.deptno =d.deptno and e.ob =(select ename from emp where ename= ‘ename ‘)
mysql> select e.*, d.dname from emp e left outer join dept d on e.deptno=d.deptno where e.job=(select job from emp where ename='pang');
Copy the code
empno ename job mgr hiredate sal comm deptno dname
1008 The various ge is bright analysts 1004 2007-04-19 30000 NULL 20 Studies ministry
1013 Pang tong analysts 1004 2001-12-03 30000 NULL 20 Studies ministry

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

  • Columns: E.name, E.sal, D.name
  • Table emp e, DEPT D
  • Select sal from emp group by deptno having deptno=30 and e.deptno=d.deptno
mysql> select e.ename, e.sal, d.dname from emp e left outer join dept d on e.deptno=d.deptno where e.sal>(select max(sal) from emp group by deptno having deptno=30);
Copy the code
ename sal dname
Liu bei 29750 Studies ministry
The various ge is bright 30000 Studies ministry
Once pig 50000 Actuarial-oriented)
Pang tong 30000 Studies ministry
Zhang SAN 80000 NULL

or

mysql> select e.ename, e.sal, d.dname from emp e left outer join dept d on e.deptno=d.deptno where e.sal > all (select sal from emp where deptno=30);
Copy the code

12, find the year, profit, annual growth ratio (that is, ask for one more column to show the growth ratio)

mysql> select * from tb_year;
Copy the code
year zz
2010 100
2011 150
2012 250
2013 800
2014 1000

Ideas :(1) first put the profits of adjacent two years on a line

mysql> select * from tb_year y1, tb_year y2;
Copy the code
year zz year zz
2010 100 2010 100
2011 150 2010 100
2012 250 2010 100
2013 800 2010 100
2014 1000 2010 100
2010 100 2011 150
2011 150 2011 150
2012 250 2011 150
2013 800 2011 150
2014 1000 2011 150
2010 100 2012 250
2011 150 2012 250
2012 250 2012 250
2013 800 2012 250
2014 1000 2012 250
2010 100 2013 800
2011 150 2013 800
2012 250 2013 800
2013 800 2013 800
2014 1000 2013 800
2010 100 2014 1000
2011 150 2014 1000
2012 250 2014 1000
2013 800 2014 1000
2014 1000 2014 1000 
mysql> select * from tb_year y1, tb_year y2 where y1.year=y2.year+1;
Copy the code
year zz year zz
2011 150 2010 100
2012 250 2011 150
2013 800 2012 250
2014 1000 2013 800

Note the left outer connection

mysql> select * from tb_year y1 left outer join tb_year y2 on y1.year=y2.year+1;
Copy the code
year zz year zz
2010 100 NULL NULL
2011 150 2010 100
2012 250 2011 150
2013 800 2012 250
2014 1000 2013 800

(2) Calculate the growth ratio

mysql> select y1.*, (y1.zz-y2.zz)/y1.zz*100 from tb_year y1 left outer join tb_year y2 on y1.year=y2.year+1;
Copy the code
year zz (y1.zz-y2.zz)/y1.zz*100
2010 100 NULL
2011 150 33.33333333333333
2012 250 40
2013 800 68.75
2014 1000 20

(3) Modify according to display requirements

mysql> select y1.*, ifnull(concat((y1.zz-y2.zz)/y2.zz*100.The '%'), 0Growth thanfrom tb_year y1 left outer join tb_year y2 on y1.year=y2.year+1;
Copy the code
year zz Growth than
2010 100 0
2011 150 50%
2012 250 66.66666666666666%
2013 800 220.00000000000003%
2014 1000 25%

Y1.year =y2.year+1 y1.year=y2.year+1 y1.year=y2.year+1

My blog