如何免费查询企业信息,长沙seo搜索,官方网站平台有哪些,wordpress 昵称这部分主要是为了帮助大家回忆回忆MySQL的基本语法#xff0c;数据库来自于MySQL的官方简化版#xff0c;题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。 列出部门名称和这些部门的员工信息#xff0c;同时列出没有员工的部门 唯一要注… 这部分主要是为了帮助大家回忆回忆MySQL的基本语法数据库来自于MySQL的官方简化版题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。 列出部门名称和这些部门的员工信息同时列出没有员工的部门 唯一要注意的是有的部门无员工记得使用left join。 mysql select d.dname,e.* from dept d left join emp e on d.deptnoe.deptno;-----------------------------------------------------------------------------------| dname | empno | ename | job | mgr | hiredate | sal | comm | deptno |-----------------------------------------------------------------------------------| RESEARCHING | 7369 | SIMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 || SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 || SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 || RESEARCHING | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 || SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 || SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 || ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 || RESEARCHING | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 || ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 || SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | NULL | 30 || RESEARCHING | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 || SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 || RESEARCHING | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 || ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 || OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |----------------------------------------------------------------------------------- 列出至少有5名员工的所有部门 mysql select d.dname,count(e.empno) as number from dept d join emp e on d.deptnoe.deptno group by d.deptno having number5;---------------------| dname | number |---------------------| RESEARCHING | 5 || SALES | 6 |--------------------- 列出薪水比simith多的所有员工信息 mysql select * from emp where sal(select sal from emp where enamesimith);----------------------------------------------------------------------| empno | ename | job | mgr | hiredate | sal | comm | deptno |----------------------------------------------------------------------| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 || 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 || 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 || 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 || 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 || 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 || 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 || 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 || 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | NULL | 30 || 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 || 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |---------------------------------------------------------------------- 列出所有岗位为clerk的姓名及部门名称部门人数 先取出每个部门的人数然后将上表作为临时表与emp表连接 mysql select d.deptno,d.dname,count(e.empno) as numbers from dept d join emp e on d.deptnoe.deptno group by e.deptno;------------------------------| deptno | dname | numbers |------------------------------| 20 | RESEARCHING | 5 || 30 | SALES | 6 || 10 | ACCOUNTING | 3 |------------------------------ select a.ename, t.dname, t.numbersfrom emp a join (select d.deptno, d.dname, count(e.empno) as numbers from dept d join emp e on d.deptno e.deptno group by e.deptno) t on a.deptno t.deptno and a.job clerk;------------------------------| ename | dname | numbers |------------------------------| SIMITH | RESEARCHING | 5 || ADAMS | RESEARCHING | 5 || JAMES | SALES | 6 || MILLER | ACCOUNTING | 3 |------------------------------ 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数 先取出最低薪水大于1500的工作找出工作为这些职业的职员最后计数 从emp表中获取人上人的岗位。 mysql select job,min(sal) as minsal from emp group by job having minsal1500;--------------------| job | minsal |--------------------| MANAGER | 2450.00 || ANALYST | 3000.00 || PRESIDENT | 5000.00 |-------------------- 获取所有人上人。 mysql select a.*,b.minsal from emp a join (select job,min(sal) as minsal from emp group by job having minsal1500) b where a.jobb.job;---------------------------------------------------------------------------| empno | ename | job | mgr | hiredate | sal | comm | deptno | minsal |---------------------------------------------------------------------------| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 2450.00 || 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 2450.00 || 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 2450.00 || 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 3000.00 || 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 5000.00 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 3000.00 |--------------------------------------------------------------------------- 根据工作岗位计数 mysql select t.job,count(t.empno),t.minsal from (select a.*,b.minsal from emp a join (select job,min(sal) as minsal from emp group by job having minsal1500) b where a.jobb.job) t group by job;------------------------------------| job | count(t.empno) | minsal |------------------------------------| MANAGER | 3 | 2450.00 || ANALYST | 2 | 3000.00 || PRESIDENT | 1 | 5000.00 |------------------------------------
