测试面试 上海 - 面试公司- 伯俊科技
create table t_emp( empno int(10), ename char(10), job char(10), mar int(10), hiredate date, sal int(10), comm int(10), deptno int(10)); /* empno:员工编号 ename:员工姓名 job:职能 mar:直属上级 hiredate：入职日期 sal:薪水 comm:提成 deptno:部门编号 */insert into t_emp values(7369,"SMITH","CLERK",7902,"1980-12-17",800,null,20); insert into t_emp values(7499,"ALLEN","SALESMAN",7698,"1981-02-20",1600,300,30); insert into t_emp values(7521,"WARD","SALESMAN",7698,"1981-02-22",1250,500,30); insert into t_emp values(7566,"JONES","MANAER",7839,"1981-04-02",2975,null,20); insert into t_emp values(7654,"MARTIN","SALESMAN",7698,"1981-09-28",1250,1400,30); insert into t_emp values(7698,"BLAKE","MANAGER",7839,"1981-05-01",2850,null,30); insert into t_emp values(7782,"CLARK","MANAGER",7839,"1981-06-09",2450,null,10); insert into t_emp values(7788,"SCOTT","ANALYST",7566,"1982-12-09",3000,null,20); insert into t_emp values(7839,"KING","PRESIDENT",NULL,"1981-11-17",5000,null,10); insert into t_emp values(7844,"TURNER","SALESMAN",7698,"1981-09-08",1500,0,30); insert into t_emp values(7876,"ADAMS","CLERK",7788,"1983-01-12",1100,null,20); insert into t_emp values(7900,"JAMES","CLERK",7698,"1981-12-03",950,null,30); insert into t_emp values(7902,"FORD","ANALYST",7566,"1981-12-03",3000,null,20); insert into t_emp values(7934,"MILLER","CLERK",7782,"1982-01-23",1300,null,10);create table t_dept( deptno int(10), dname char(10), loc char(10) ); /* deptno:部门编号 dname:部门名称 loc:部门归属地 */insert into t_dept values(10,"ACCOUNTING","NEWYORK"); insert into t_dept values(20,"RESEARCH","DALLAS"); insert into t_dept values(30,"SALES","CHICAGO"); insert into t_dept values(40,"A 部门","北京");1.查询 10 和 20 部门中月收入最高的员工 (收入=薪水 + 提成) 2.查询员工名字最长的是几个字符 3.查询 10 和 20 部门中底薪超过 2000 并且工龄超过 15 年的员工人数 4.查询每个部门中，1982 年以后入职的员工超过 2 个人的部门编号 5.查询与 SCOTT 相同部门的员工都有谁 6 查询每个部门的名称和部门人数第 1 题 select ename from t_emp where IFNULL(sal,0)+IFNULL(comm,0) in( select max(IFNULL(sal,0)+IFNULL(comm,0)) as shouru from t_emp where deptno=10 or deptno=20 group by deptno)第 2 题 select max(length(ename)) from t_emp第 3 题 select count(*) from t_emp where (deptno=10 or deptno=20) and sal>2000 and hiredate<= DATE_SUB(now(),INTERVAL 15 year)第 4 题答案 select deptno from t_emp where hiredate>"1982-01-01" group by deptno having count(deptno)>=2 第 5 题答案 select ename from t_emp where deptno=(select deptno from t_emp where ename="SCOTT")第 6 题答案 select * from t_emp; select * from t_dept; select dname,count(*) from t_emp inner join t_dept on t_emp.deptno=t_dept.deptno group by t_dept.dname
页:
[1]