Mysql代码练习题

1.写出dept（部门表）与salgrade（工资等级表）的建表语句	create table dept(	deptno int,	dname varchar(20),	loc varchar(20)	);	create table salgrade(	grade int,	losal int,	hisal int	);
2.在表emp中,查询出工资最高的员工职位	select job,max(sal) from emp;
3.在表emp中，插入一条数据员工号7400、员工姓名TOM、职位CLERK、所属经理7788、入职时间1983/9/5、工资1050、奖金600、部门号20	insert into emp values(7400,'tom','clerk',7788,'1983/9/5',1050,600,20);	
4.在emp中，更新所插入的7400的数据，将工资修改为1300、部门号30.	update emp set sal=1300,deptno=30 where empno=7400;	
5.在emp表中，删除所插入的7400数据。	delete form emp where empno=7400;	
6.在表emp中,查询工资在2000-3000之间的员工姓名和工资	select ename,sal from emp where sal>=2000 and sal<=3000;						  	
7.在表emp中,查询员工奖金,如果是null,输出时以0表示	select ifnull(comm,0) from emp;		
8.在表emp中,查询有奖金的员工总收入	select sal+ifnull(comm,0) from emp;		
9.在表emp中,查询员工姓名第一位是A的员工姓名	select ename from emp where ename like 'A%';		         
10.在表emp中,查询员工姓名中包含A的员工姓名	select ename from emp where ename like '%A%';	      
11.在表emp中,查询员工姓名中第二位是A的员工姓名	select ename from emp where ename like '_A%';
12.在表emp中,部门号由高而低,工资由低到高列出每个员工的姓名,部门号,工资.	select ename,deptno,sal from emp;	
13.在表emp中,统计每年入职的员工个数	select left(hiredate,4),count* from emp group by left( hiredate,4);	
14.在表emp中,查询每一个部门的平均工资.	select avg(sal) from emp group by deptno;		
15.在表emp中,查询每一职位的平均工资,并按平均工资升序排列	select job,avg(sal) from emp group by job order by avg(sal) asc;		
16.在表emp中,查询不同部门中秘书(clerk)职位的平均工资,并按平均工资降序排列	select avg(sal) from emp where job='clerk' group by deptno order by avg(sal) desc;	
17.在表emp中,列出工资最小值小于等于2000的职位.	select job from emp where not(sal)>2000 group by job;		
18查询员工号为7369的员工所在的部门名称	select dept.dname from emp,dept where emp.deptno=dept.deptno and emp.empno=7369;	
19.根据工资的值,列出员工姓名和等级	select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;	
20.列出员工姓名和所在的部门名称	select e.ename,d.dname from emp e,dept d where e.dept = d.deptno;	
21.列出员工姓名和所在部门名称	select e.ename,d.dname from emp e,dept d where e.dept = d.deptno;	
22.在表emp中,列出每个员工及其经理的姓名	select ename,ename from emp e,(select empno,ename from emp) s where e.mgr=s.empno;			
23.在表emp中,查询工资高于员工WARD工资的员工姓名和工资	select ename,sal from emp where sal>(select sal from emp where ename='ward');		
24.在表emp中,查询工资相同的员工(员工姓名和工资)	select ename,sal from emp group by sal;	
25.在表emp中,列出所有WARD或者SMITH所在部门的员工姓名和部门名称	select e.ename,d.dname from emp e,dept d where deptno in(select deptno from emp where ename='ward') or deptno in(select deptno from emp where ename = 'smith');		
26.在表emp中,列出工资低于销售人员(SALESMAN)的非经理员工姓名,职位和工资	select * from(select ename,job,sal from emp where sal<(select min(sal) from emp where job = 'salesman')) m where not(manager);	
27.在表emp中,列出工资高于部门经理(MANAGER)的非经理员工姓名,职位和工资	select * from(select ename,job,sal form emp where sal>(select max(sal) from emp where job = 'manager')) m where not(manager);	
28.在表emp中,列出所有至少有一名下级职员的员工姓名和职位	select ename,job from emp group mgr;		
29.在表emp中,列出工资高于2000或为部门经理(MANAGER)的员工姓名/职位和工资	select ename,job,sal from emp where sal>2000 or job = 'manager';		
30.在表emp中,查询工资高于平均工资水平的员工姓名和工资	select ename,sal from emp where sal>(select avg(sal)from emp);	
31.在表emp中,插入一条记录,员工号为7999,员工姓名为Tom,其他数据为空	insert into emp  values(7999,'tom',null,null,null,null,null,null);		
32.在表emp中,把员工号为7369的员工工资上浮8%,奖金增加200	update emp set sal *= 1.08,comm += 200 where empno=7369;		
33,在表emp中,删除入职时间在1981年以前的所有员工	delete from emp where left(hiredate,4)<1981;
![Orcal数据库表](https://img-blog.csdnimg.cn/20200305160729189.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDA2MDQ4NA==,size_16,color_FFFFFF,t_70)