Mysql练习(2)

Z.959

于 2023-01-05 17:06:19 发布

阅读量113

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_56751692/article/details/128566763

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

素材

DROP DATABASE IF EXISTS `emp`;
CREATE DATABASE `emp`;

USE emp;

 CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL, 
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE bonus 
( 
ENAME VARCHAR(10) , 
JOB VARCHAR(9) , 
SAL BIGINT, 
COMM BIGINT 
) ; 
CREATE TABLE `emp` (
    `empno` int(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` int(4),  
    `hiredate` DATE,  
    `sal` float(7,2),  
    `comm` float(7,2),  
    `deptno` int(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE `salgrade` (  
    `grade` int, 
    `losal` int,  
    `hisal` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (5,3001,9999);

题目

SQL语句练习题一
1、选择部门30中的所有员工；
SELECT * FROM emp WHERE deptno=30;
2、列出所有办事员(CLERK)的姓名，编号和部门编号；
SELECT ename,empno,deptno FROM emp WHERE job='CLERK';
3、找出奖金高于工资的员工；
SELECT * FROM emp WHERE comm>sal;
4、找出奖金高于工资的60%的员工；
SELECT * FROM emp WHERE comm>sal*0.1;
5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料；
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or (job not in ('MANAGER','CLERK') and sal >=2000);
7、找出收取奖金的员工的不同工作；
select job from emp where comm is not null;
8、找出不收取奖金或者收取的奖金低于100的员工；
select * from emp where comm is null or comm <100;
9、找出各月倒数第三天受雇的所有员工；
SELECT * FROM emp WHERE DAY(last_day(hiredate))-DAY(hiredate)=2;
SELECT * FROM emp WHERE LAST_DAY(HIREDATE)-2=HIREDATE;
10、找出早于12年前受雇的员工；
select * from emp where YEAR(NOW())-YEAR(hiredate)> 13 ;
11、以首字母大写的方式显示所有员工的姓名；
SELECT REPLACE(LOWER(ename),LEFT(LOWER(ename),1),UPPER(LEFT(LOWER(ename),1))) FROM emp;
12、显示正好为5个字符的员工的姓名；
SELECT ename FROM emp WHERE  LENGTH(ename)=5;
13、显示不带有“R”的员工姓名；
SELECT ename FROM emp WHERE ename NOT LIKE '%R%';
14、显示所有员工姓名的前三个字符；
SELECT LEFT(ename,3) FROM emp;
15、显示所有员工的姓名，用“a”替换所有的“A”；
SELECT REPLACE(ename,'A','a') FROM emp;
16、显示满10年服务年限的员工的姓名和受雇日期；
select ename,hiredate from emp WHERE YEAR(NOW())-YEAR(hiredate) >10;
17、显示员工的详细资料，按姓名排序；
select * from emp order by ename;
18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；
select ename,hiredate from emp order by hiredate asc;
19、显示所有员工的姓名、工作和工资，按工作的降序排序，若工作相同则按工资排序；
select ename,job,sal from emp ORDER BY job DESC,sal;
20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；
select ename,YEAR(hiredate),MONTH(hiredate) from emp ORDER BY MONTH(hiredate),hiredate;
21、显示在一个月为30天的情况所有员工的日工资，忽略余数；
select ename,FLOOR((sal/30)) from emp;
22、找出(任何年份的)2月受聘的所有员工；
select ename from emp WHERE MONTH(hiredate)=2;
23、对于每个员工，显示其加入公司的天数；
select ename,DATEDIFF(NOW(),hiredate) from emp;
24、显示姓名字段的任何位置包含“A”的所有员工的姓名；
select ename from emp where ename like '%A%';
25、以年月日的方式显示所有员工的服务年限；
select ename,
FLOOR(DATEDIFF(NOW(),hiredate)/365) AS YEARS,
CEILING(((DATEDIFF(NOW(),hiredate)-FLOOR(DATEDIFF(NOW(),hiredate)/365)*365)/30)) AS MONTH,
DATEDIFF(NOW(),hiredate)-FLOOR(DATEDIFF(NOW(),hiredate)/365)*365 AS DAY
from emp;

题目

SQL练习题二
1、列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资
SELECT emp.deptno,dname,AVG(sal),MIN(sal),MAX(sal) FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno HAVING COUNT(*)>=1;
2、列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名
select e.empno,e.ename,d.dname,m.ename
from emp e,dept d,emp m
where e.sal>any(
		select sal
		from emp
		where ename in ('SMITH','ALLEN'))
and e.deptno=d.deptno
and e.mgr=m.empno;
3、列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列
select e.empno,e.ename,m.empno,m.ename,(m.sal+IFNULL(m.comm,0))*12 income from emp e,emp m where e.mgr=m.empno order by income desc;
4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
select e.empno,e.ename,d.dname,d.loc,temp.count
	from emp e,emp m,dept d,(
		select deptno dno,count(*) count
		from emp
		group by deptno) temp
	where e.mgr=m.empno
	and e.hiredate<m.hiredate
	and e.deptno=d.deptno
	and e.deptno=temp.dno;
5、列出部门名称和这些部门的员工信息(数量、平均工资)，同时列出那些没有员工的部门
select d.dname,count(e.empno),round(IFNULL(avg(e.sal),0))
	from emp e,dept d
	where e.deptno=d.deptno
	group by d.dname;
6、列出所有CLERK的姓名及其部门名称，部门的人数，工资等级
select e.ename,d.dname,temp.count,s.grade
	from emp e,dept d,(
		select deptno dno,count(empno) count
		from emp
		group by deptno) temp,salgrade s
	where job='CLERK'
		and e.deptno=d.deptno
		and d.deptno=temp.dno
		and e.sal between s.losal and s.hisal;
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资
select temp.job,temp.count,d.dname,d.loc,res.avg
	from dept d,(
		select e.job job,count(e.empno) count
		from emp e
		group by e.job
		having min(e.sal)>1500) temp,
		emp e,(
			select deptno,avg(sal) avg
			from emp
			group by deptno) res
	where e.deptno=d.deptno
	and e.job=temp.job
	and e.deptno=res.deptno;
8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道该部门的部门编号
select e.ename,e.sal,e.hiredate,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';
9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
select e.empno,e.ename,e.job,e.sal,d.dname,m.ename,s.grade
	from emp e,dept d,emp m,salgrade s
	where e.sal>(
		select avg(sal) from emp)
	and e.deptno=d.deptno
	and e.mgr=m.empno
	and e.sal between s.losal and s.hisal;
10、列出与SCOTT从事相同工作的所有员工及部门名称，部门人数
select e.empno,e.ename,e.job,d.dname,temp.count
from emp e,dept d,(
		select deptno,count(empno) count
		from emp
		group by deptno) temp
where e.job=(select job from emp where ename='SCOTT')
and e.deptno=d.deptno 
and temp.deptno=e.deptno;
11、列出公司各个工资等级雇员的数量、平均工资
select s.grade,count(e.empno),avg(e.sal)
	from emp e,salgrade s
	where e.sal between s.losal and s.hisal
	group by s.grade,s.losal,s.hisal
	order by s.grade;
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
select e.empno,e.ename,e.sal,d.dname
	from emp e,dept d
	where sal>all(
		select sal from emp where deptno=30)
	and e.deptno=d.deptno;
13、列出在每个部门工作的员工数量、平均工资和平均服务期限
select d.deptno,d.dname,count(e.empno),avg(e.sal),
		avg(months_between(sysdate,hiredate)/12) year
	from emp e,dept d
	where e.deptno=d.deptno
	group by d.deptno,d.dname;
14、列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno;
15、列出所有部门的详细信息和部门人数
select d.deptno,d.dname,d.loc,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname,d.loc;
16、列出各种工作的最低工资及从事此工作的雇员姓名
select e.ename,e.job,e.sal
	from emp e,(
		select job,min(sal) min
		from emp
		group by job) temp
	where e.job=temp.job 
	and e.sal=temp.min;
17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数
	select e.ename,e.sal,d.dname,res.count
	from emp e,(
		select deptno dno,min(sal) sal
		from emp
		where job='MANAGER'
		group by deptno) temp,dept d,(
			select deptno dno,count(empno) count
			from emp
			group by deptno) res
	where e.deptno=temp.dno 
	and e.sal=temp.sal 
	and e.job='MANAGER'
	and e.deptno=d.deptno
	and res.dno=d.deptno;
18、列出所有员工的年工资，所在部门名称，按年薪从低到高排序
	select e.ename,sal*12 income,d.dname
	from emp e,dept d
	where e.deptno=d.deptno
	order by income;
19、列出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
	select distinct m.ename,d.dname,m.sal
	from emp e,emp m,dept d
	where e.mgr=m.empno 
	and m.deptno=d.deptno
	and m.sal>3000;
20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
	select d.dname,sum(e.sal),count(e.empno)
	from emp e,dept d
	where e.deptno=d.deptno 
	and d.dname like '%S%'
	group by d.dname;
21、给任职日期超过30年或者87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，以此类推
	UPDATE emp SET 
		SAL=(1+DEPTNO/100)*SAL
	WHERE DATEDIFF(SYSDATE(),hiredate)/30/12>30
	OR YEAR(hiredate)=1987