EXERCISES. 1     SIMPLE COMMANDS

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

chart 15 topic 1.1

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

chart 16 topic 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;

chart 17 topic 1.3

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

chart 18 topic 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;

chart 19 topic 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’);

chart 20 topic 1.8

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

chart 21 topic 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;

chart 23 topic 1.11

( Here is the first exercise due to the database emp Table last 3 Unexpected loss of rows leads to different results for some questions , But the query statement is OK )

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;

chart 24 topic 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;

chart 25 topic 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;

chart 26 topic 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;

chart 27 topic 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;

chart 28 topic 1.21

Technology
Daily Recommendation