mysqll练习题一、二、单多表练习

微 . 观

已于 2023-01-12 16:58:55 修改

阅读量812

点赞数

文章标签：数据库 Powered by 金山文档

于 2023-01-08 19:19:44 首次发布

本文链接：https://blog.csdn.net/qq_67449285/article/details/128592950

版权

SQL语句练习题一

选择部门30中的所有员工；

列出所有办事员(CLERK)的姓名，编号和部门编号；

找出奖金高于工资的员工；

找出奖金高于工资的60%的员工；

找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；

找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料；

7、找出收取奖金的员工的不同工作；

8、找出不收取奖金或者收取的奖金低于100的员工；

9、找出各月倒数第三天受雇的所有员工；

10、找出早于12年前受雇的员工；

11、以首字母大写的方式显示所有员工的姓名；

12、显示正好为5个字符的员工的姓名；

13、显示不带有“R”的员工姓名；

14、显示所有员工姓名的前三个字符；

15、显示所有员工的姓名，用“a”替换所有的“A”；

16、显示满10年服务年限的员工的姓名和受雇日期；

17、显示员工的详细资料，按姓名排序；

18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；

19、显示所有员工的姓名、工作和工资，按工作的降序排序，若工作相同则按工资排序；

20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；

21、显示在一个月为30天的情况所有员工的日工资，忽略余数；

22、找出(任何年份的)2月受聘的所有员工；

23、对于每个员工，显示其加入公司的天数；

24、显示姓名字段的任何位置包含“A”的所有员工的姓名；

25、以年月日的方式显示所有员工的服务年限；

SQL练习题二

列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资

2、列出薪金比SMITH或者ALLEN多的所有员工的编号、姓名、部门名称、其领导姓名

3、列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列

4、列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数

5、列出部门名称和这些部门的员工信息(数量、平均工资)，同时列出那些没有员工的部门

6、列出所有CLERK的姓名及其部门名称，部门的人数，工资等级

7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资

8、列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道该部门的部门编号

9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

10、列出与SCOTT从事相同工作的所有员工及部门名称，部门人数

11、列出公司各个工资等级雇员的数量、平均工资

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

13、列出在每个部门工作的员工数量、平均工资和平均服务期限

14、列出所有员工的姓名、部门名称和工资

15、列出所有部门的详细信息和部门人数

16、列出各种工作的最低工资及从事此工作的雇员姓名

17、列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数

18、列出所有员工的年工资，所在部门名称，按年薪从低到高排序

19、列出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000

20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数

21、给任职日期超过30年或者87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，以此类推

1
select e.DEPTNO,AVG(SAL),MIN(SAL),MAX(SAL) 
FROM EMP e    
LEFT JOIN DEPT d   
ON e.deptno = d.deptno
GROUP BY e.deptno;

2
select e.empno,e.ename,m.ename,d.dname
from EMP e left join EMP m on e.mgr=m.empno left join DEPT d on e.deptno=d.deptno
where e.sal>some(select sal from EMP where ename in ('SMITH','ALLEN'));

3
select e.ENAME,e.EMPNO,e.MGR,m.ENAME as bossName from EMP e left join EMP m on e.MGR=m.EMPNO order by e.SAL DESC;

4
select e.empno,e.ename,d.dname,d.loc,temp.empcount
from EMP e,DEPT d,(select deptno dno,count(empno) empcount
from EMP
where deptno is not null
group by deptno) temp
where e.deptno=d.deptno and d.deptno=temp.dno

5
select *,AVG(e.SAL) ,(select count(e.ENAME) from EMP group by e.DEPTNO) from EMP e right join DEPT d on e.DEPTNO=d.DEPTNO group by e.EMPNO,d.DEPTNO;
6
select e.ENAME,d.DNAME,count(*) ,g.GRADE from SALGRADE g, EMP e join DEPT d on e.DEPTNO=d.DEPTNO where JOB='CLERK' group by e.ENAME,d.DNAME,g.GRADE;

7
select *,d.DNAME,d.LOC,AVG(SAL) from EMP,DEPT d where SAL>=1500 group by EMPNO,d.DEPTNO;

8
select e.ENAME,e.SAL,e.HIREDATE ,d.DNAME from EMP e,DEPT d where d.DNAME='SALES';

9
select distinct e.ENAME,m.ENAME as boosName ,d.DNAME as 部门,g.GRADE as 工资等级 from EMP e,EMP m,DEPT d,SALGRADE g where e.SAL>(select AVG(SAL) from EMP) and e.MGR=m.EMPNO;


10
 select ENAME ,DNAME,count(ENAME) from EMP inner join DEPT on EMP.DEPTNO=DEPT.DEPTNO where JOB=(select JOB from EMP where ENAME=‘SCOTT’) group by EMPNO;

11
select e.ENAME,AVG(e.SAL),count(*) FROM EMP e join EMP m on
e.DEPTNO=m.DEPTNO group by e.ENAME;

12
select e.SAL ,e.ENAME,d.DNAME from EMP e ,DEPT d where SAL>(select MAX(SAL) from EMP where DEPTNO=30) and e.DEPTNO=d.DEPTNO;
13
 select count(ENAME),AVG(SAL),AVG(DATEDIFF(now(),HIREDATE)) from EMP
group by DEPTNO;
14
 select e.ENAME,e.SAL,d.DNAME from EMP e ,DEPT d where e.DEPTNO=d.DEPTNO;
15
select d.* ,e.ENAME ,count(1) from DEPT d,EMP e where e.DEPTNO=d.DEPTNO group by d.DEPTNO,e.EMPNO;



17
 select MIN(e.SAL),e.ENAME,d.DNAME,count(*) from EMP e join DEPT d on e.DEPTNO=d.DEPTNO where JOB='MANAGER' group by e.ENAME,d.DNAME;



18
select e.ENAME,e.SAL*12,d.DNAME from EMP e,DEPT d where e.DEPTNO=d.DEPTNO group by
ENAME,SAL,DNAME;

19
select SAL from EMP where SAL > 3000 and ENAME='( select m.ENAME as BossNAME from
EMP e left join EMP m on m.EMPNO=e.MGR where e.EN)';

20
select SUM(e.SAL),d.DNAME ,count(*) from EMP e join DEPT d on e.DEPTNO=d.DEPTNO where d.DNAME like '%S%' group by d.DNAME;

单多表查询

练习1：SELECT语句的基本使用

1. 查询每个雇员的所有记录；

2. 查询前5个会员的所有记录；

3. 查询每个雇员的地址和电话；

4. 查询num为001的雇员地址和电话；

5. 查询表Employee表中女雇员的地址和电话，使用AS子句将结果列中各列的标题分别指定为地址、电话；

6. 计算每个雇员的实际收入；

7. 找出所有性王的雇员的部门号（部门号不能重复显示）；

8. 找出所有收入在2000－3000元之间的雇员编号

练习2：子查询的使用（答案可以不唯一）

1. 查找在财务部工作的雇员情况；

2. 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名；

3. 查找比所有财务部雇员收入都高的雇员的姓名；

练习3：连接查询的使用

1. 查找每个雇员的情况及薪水情况；

2. 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况；

练习4：数据汇总

1. 求财务部雇员的平均实际收入；

2. 求财务部雇员的总人数；

练习5：GROUP BY 、ORDER BY 子句的使用

1. 求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）；

2. 求部门的平均薪水大于2500的部门信息（要求显示，部门号、部门名称和平均工资）

练习一：
1
select * from Employee;
2
select * from Employee where num <= 5;
3
select name,tel,addr from Employee;
4
select tel,addr from Employee where num=001;
5
select tel as "地址"，tel as "电话" from Employee where sex="女";/错

SELECT addr AS 地址,tel AS 电话 FROM employee WHERE sex="女";
6
SELECT num,income-outcome from salay;
7
SELECT  DISTINCT depno  from Employee WHERE name LIKE"王%";
8
SELECT num FROM salay WHERE income BETWEEN 2000 AND 3000;

练习二：
1
select * from Employee where depno=1;
2
SELECT name from Employee WHERE depno=
(SELECT depno from department WHERE depName="财务部") AND birth<ALL
(SELECT birth FROM Employee WHERE depno=(SELECT depno FROM department WHERE depName="研发部"));
3
SELECT DISTINCT income,name from Employee,salay,department WHERE Employee.num=
salay.num AND income>all
(SELECT income FROM Employee,salay,department WHERE Employee.num=salay.num AND Employee.depno=department.depno and depName="财务部");
练习三：
1
SELECT * FROM employee ,salay WHERE Employee.num=salay.num;
SELECT e.*, s.* FROM Employee e
INNER JOIN salay s
ON e.Num=s.Num;
2
SELECT name,salay.* FROM Employee,salay,department WHERE Employee.num=salay.num AND Employee.depno=department.depno AND depName="财务部"AND income>2200;
-- 内连接
SELECT e.name, s.* FROM salay s
INNER JOIN Employee e
ON s.Num =e.Num
INNER JOIN department d
ON e.depno=d.depno
WHERE s.income>2200 AND d.depname="财务部";
练习四：
1
SELECT avg(income-outcome) FROM Employee,salay,department WHERE Employee.num=salay.num AND Employee.depno=department.depno and depName="财务部";
2
SELECT count(*) FROM Employee,salay,department WHERE Employee.num=salay.num AND Employee.depno=department.depno and depName="财务部";
练习五：
1
SELECT e.depno AS 部门号,d.depname AS 部门名称,count(*)AS 部门雇员数 FROM Employee e INNER JOIN department d on e.depno=d.depno  GROUP BY  d.depno ;
2
SELECT e.depno AS 部门号,d.depname AS 部门名称,avg(income)AS 部门平均工资 FROM Employee e,department d,salay s  GROUP BY  d.depno,e.num ;

附：表数据

附：
SQL1练习一、二表数据：
CREATE TABLE DEPT
(DEPTNO int PRIMARY KEY,
DNAME VARCHAR(14) ,
LOC VARCHAR(13) ) ;

CREATE TABLE EMP
(EMPNO int  PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int,
HIREDATE DATE,
SAL int,
COMM int,
DEPTNO int  REFERENCES DEPT);
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');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-4-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-4-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-4-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-12-23',1300,NULL,10);
CREATE TABLE BONUS
(
ENAME VARCHAR(10),
JOB VARCHAR(9)  ,
SAL int,
COMM int
) ;
CREATE TABLE SALGRADE
( GRADE int,
LOSAL int,
HISAL int);
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);




单多表数据：
create table Employee(
  num int primary key auto_increment,
  name varchar(30) not null,
  addr varchar(30) not null unique,
  zip varchar(30) not null,
  tel varchar(30) not null,
  email varchar(30) unique,
  depno int not null,
  birth date not null,
  sex set ("男","女")
)



create table department(
  depno int primary key auto_increment,
  depName varchar(30) unique not null,
  remark varchar(50)
)



create table salay(
  num int primary key auto_increment,
  inCome double not null,
  outCome double not null
)

alter table Employee add constraint fk_Employee_department foreign key(depno) references department(depno);



insert into Employee VALUES 
(1,"王林","武汉大学","430074","87598405",null,2,"1985-2-1","男"),
(2,"王芳 ","华中科大","430073","62534231",null,1,"1966-3-28","男"),
(3,"张晓","武汉理工大","430072 ","87596985",Null,1,"1972-12-9","男"),
(4,"王小燕","武汉交大","430071","85743261","lili@sina.com",1 ,"1950-7-30","女"),
(5,"李华"," 华中农大","430070","87569865",Null,5,"1962-10-18","男"),
(6,"李明","华中师大","430075","85362143","zhujun@sina.com ",5,"1955-09-28","男"),
(7,"田丽","中南财大","430076","85693265","zgming@sohu.com",3,"1968-08-10","女"),
(8,"吴天","武汉电力","430077","36985612 ","zjamg@china.com",5,"1964-10-01","男"),
(9,"刘备"," 武汉邮科院","430078","69865231",Null,3,"1967-04-02","男"),
(10,"赵云","学府家园","430071","68592312 ",Null,4,"1968-11-18","男"),
(11,"貂禅","湖北工大"," 430074","65987654", null,4,"1959-09-03","女");

insert INTO department(depName,remark) values ("财务部",Null),("人力资源部",Null),("经理办公室",Null),
("研发部",Null),("市场部",Null);

INSERT INTO salay(inCome,outCome) values (2100.7,123.09),(1582.62,88.03),(2569.88,185.65),(1987.01 ,79.58),
(2066.15 ,108.0),( 2980.7, 210.2),(3259.98 ,281.52),(2860.0,198),(2347.68,180),(2531.98,199.08),(2240.0,121.0);