MYSQL数据库SQL语句练习实验 EXERCISES. 1 SIMPLE COMMANDS
2021/12/11 2:17:47
本文主要是介绍MYSQL数据库SQL语句练习实验 EXERCISES. 1 SIMPLE COMMANDS,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
EXERCISES. 1 SIMPLE COMMANDS
- List all information about the employees.
Select * from emp2019274072;
图 15 题1.1
2.List all information about the departments
Select * from dept2019274072;
图 16 题1.2
3.List only the following information from the EMP table ( Employee name, employee number, salary, department number)
Select empno,ename,sal,deptno from emp2019274072;
图 17 题1.3
4..List details of employees in departments 10 and 30.
Select * from emp2019274072 where deptno between 10 and 30;
图 18 题1.4
5.List all the jobs in the EMP table eliminating duplicates.
Select ename from emp2019274072 where sal<20000;
6.What are the names of the employees who earn less than £20,000?
Select ename from emp2019274072 where sal<20000;
7.What is the name, job title and employee number of the person in department 20 who earns more than £25000?
Select ename,job,empno from emp2019274072 where deptno=20;
图 19 题1.5 1.6 1.7
8.Find all employees whose job is either Clerk or Salesman.
Select ename,job from emp2019274072 where job not in(‘CLERK’,’SALESMAN’);
图 20 题1.8
9.Find any Clerk who is not in department 10.
Select ename,job,deptno from emp2019274072 where job=’CLERK’ and deptno!=10;
图 21 题1.9
10.Find everyone whose job is Salesman and all the Analysts in department 20.
Select ename,job,deptno from emp2019274072 where job=‘salesman’ or job=’analysts’ and deptno=20;
图 22 题1.10
11.Find all the employees who earn between £15,000 and £20,000. Show the employee name, department and salary.
Select ename,deptno,sal from emp2019274072 where sal between 15000 and 20000;
图 23 题1.11
(从这里开始第一个练习由于数据库emp表最后3行意外丢失导致部分题目结果不完全一样,但查询语句是没问题的)
12.Find the name of the President.
Select ename,job from emp2019274072 where job=’ PRESIDENT’;
13.Find all the employees whose last names end with S
Select ename from emp2019274072 where ename like ‘%s’;
14.List the employees whose names have TH or LL in them
Select ename from emp2019274072 where ename like ‘%th%’ or ename like ‘%ll%’;
15.List only those employees who receive commission.
Select ename,comm from emp2019274072 where comm is not null;
图 24 题1.12 1.13 1.14 1.15
16.Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.
Select ename, job, sal, hiredate, deptno from emp2019274072 order by ename;
17.Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.
Select ename, job, sal, hiredate, deptno from emp2019274072 order by sal;
18.List all salesmen in descending order by commission divided by their salary.
Select ename,job,comm from emp2019274072 where job=’ SALESMAN’ order by comm desc;
图 25 题1.16 1.17 1.18
19.Order employees in department 30 who receive commision, in ascending order by commission
Select ename,deptno,comm from emp2019274072 where deptno= 30 and comm is not null order by comm desc;
图 26 题1.19
20.Find the names, jobs, salaries and commissions of all employees who do not have managers.
Select ename, job, sal, comm,mgr from emp2019274072 where mgr is null;
图 27 题1.20
21.Find all the salesmen in department 30 who have a salary greater than or equal to £18000.
Select ename,job,deptno,sal from emp2019274072 where job=’SALESMAN’ and deptno=30 and sal >=18000;
图 28 题1.21
这篇关于MYSQL数据库SQL语句练习实验 EXERCISES. 1 SIMPLE COMMANDS的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-04部署MySQL集群项目实战:新手入门教程
- 2024-11-04如何部署MySQL集群资料:新手入门指南
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程
- 2024-11-01部署MySQL集群学习:新手入门教程
- 2024-11-01部署MySQL集群入门:新手必读指南
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南