EXERCISES. 1     SIMPLE COMMANDS

* List all information about the employees. Select * from emp2019274072;
​​​​​​

2.List all information about the departments
Select * from dept2019274072;

3.List only the following information from the EMP table ( Employee
name, employee number, salary, department number)
Select empno,ename,sal,deptno from emp2019274072;

4..List details of employees in departments 10 and 30.
Select * from emp2019274072 where deptno between 10 and 30;

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;

8.Find all employees whose job is either Clerk or Salesman.
Select ename,job from emp2019274072 where job not in(‘CLERK’,’SALESMAN’);

9.Find any Clerk who is not in department 10.
Select ename,job,deptno from emp2019274072 where job=’CLERK’ and deptno!=10;

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;

(从这里开始第一个练习由于数据库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;

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;

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;

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;

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;