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;

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

技术
©2019-2020 Toolsou All rights reserved,
程序员的520,送给女友的几行漂亮的代码(python版)基于stm32控制四轮小车电机驱动(一)linux查看磁盘空间命令实验四 自动化测试工具-软件测试axios拦截器封装与使用C语言——qsort函数opencv-python傅里叶变换以及逆变换在算法研究过程中如何进行算法创新nc的安装和简单操作C语言做一个简易的登陆验证(功能)界面