15-2-2018

ENO
ENAME
JOB_TYPE
MANAGER
HIRE_DATE
DNO
COMMISSION
SALARY
783
King
President
- 
17/NOV/81
10
0
2950
769
Blake
Manager
783
01/MAY/81
30
0
2870
756
Jones
Manager
783
02/APR/81
20
0
2300
792
Jones
Analyst
756
03/DEC/81
20
0
2600
788
Scott
Analyst
756
09/DEC/81
20
0
2850
793
Miller
Clerk
788
23/JAN/82
40
0
1300
765
Martin
Sales_man
769
22/APR/81
30
1400
1250
778
Clark
Manager
783
09/JUN/81
10
0
2900
790
James
Clerk
769
03/DEC/81
30
0
950
736
Smith
Clerk
790
17/DEC/80
20
0
1000
787
Adams
Clerk
778
12/JAN/83
20
0
1150
752
Ward
Sales_man
769
22/FEB/81
30
500
1300
749
Allan
Sales_man
769
20/FEB/81
30
300
2000
784
Turner
Sales_man
769
08/SEP/81
30
0
1450


1. query to display employee name, job, hire date, employee number for each employee with employee number appearing first 

select eno "EMPLOYEE NUMBER", ename "EMPLOYEE NAME", job_type "JOB", hire_date "HIRE DATE" from emp33; 

EMPLOYEE NUMBER
EMPLOYEE NAME
JOB
HIRE DATE
783
King
President
17/NOV/81
769
Blake
Manager
01/MAY/81
756
Jones
Manager
02/APR/81
792
Jones
Analyst
03/DEC/81
788
Scott
Analyst
09/DEC/81
793
Miller
Clerk
23/JAN/82
765
Martin
Sales_man
22/APR/81
778
Clark
Manager
09/JUN/81
790
James
Clerk
03/DEC/81
736
Smith
Clerk
17/DEC/80
787
Adams
Clerk
12/JAN/83
752
Ward
Sales_man
22/FEB/81
749
Allan
Sales_man
20/FEB/81
784
Turner
Sales_man
08/SEP/81


2. query to display unique jobs from employee table 

select unique(job_type) from emp33; 

JOB_TYPE
Manager
Analyst
Clerk
President
Sales_man


 
3. query to display employee name concatenated by job seperated by comma 

select ename||', '||job_type "EMPLOYEE NAME CONCATE JOB" from emp33; 

EMPLOYEE NAME CONCATE JOB
King, President
Blake, Manager
Jones, Manager
Jones, Analyst
Scott, Analyst
Miller, Clerk
Martin, Sales_man
Clark, ManagerENAMEDNO Allan30 Blake30 Clark10 James30 King10 Martin30 Turner30 Ward30
James, Clerk
Smith, Clerk
Adams, Clerk
Ward, Sales_man
Allan, Sales_man



4. query to display all data from employee table, seperate each coloumn by a comma and name the said column as THE_OUTPUT 

select eno||','||ename||','||job_type||','||manager||','||hire_date||','||dno||','||commission||','||salary "THE_OUTPUT" from emp33; 

THE_OUTPUT
783,King,President,,17/NOV/81,10,0,2950
769,Blake,Manager,783,01/MAY/81,30,0,2870
756,Jones,Manager,783,02/APR/81,20,0,2300
792,Jones,Analyst,756,03/DEC/81,20,0,2600
788,Scott,Analyst,756,09/DEC/81,20,0,2850
793,Miller,Clerk,788,23/JAN/82,40,0,1300
765,Martin,Sales_man,769,22/APR/81,30,1400,1250
778,Clark,Manager,783,09/JUN/81,10,0,2900
790,James,Clerk,769,03/DEC/81,30,0,950
736,Smith,Clerk,790,17/DEC/80,20,0,1000
787,Adams,Clerk,778,12/JAN/83,20,0,1150
752,Ward,Sales_man,769,22/FEB/81,30,500,1300
749,Allan,Sales_man,769,20/FEB/81,30,300,2000
784,Turner,Sales_man,769,08/SEP/81,30,0,145








5. query to display employee name and salary of all the employees earning more than 2850 

select ename, salary from emp33 where salary>2850; 

ENAME
SALARY
King
2950
Blake
2870
Clark
2900

6. query to display employee name and department number for employee number 790

select ename, dno from emp33 where eno>790;

ENAME
DNO
Jones
20
Miller
40


7. query to display employee name and salary of all employees whose salary is not in range of 1500 and 2850

select ename, salary from emp33 where salary not between 1500 and 2850;

ENAME
SALARY
King
2950
Blake
2870
Miller
1300
Martin
1250
Clark
2900
James
950
Smith
1000
Adams
1150
Ward
1300
Turner
1450


8. query to display employee name and department number of all employees in dpeartment 10 and 30 in alphabetical order by name

select ename, dno from emp33 where dno in(10,30) order by ename;

ENAME
DNO
Allan
30
Blake
30
Clark
10
James
30
King
10
Martin
30
Turner
30
Ward
30


9. query ro display name and hire date of every employee who was hired in 1981

select ename, hire_date from emp33 where hire_date like '%81';

ENAME
HIRE_DATE
King
17/NOV/81
Blake
01/MAY/81
Jones
02/APR/81
Jones
03/DEC/81
Scott
09/DEC/81
Martin
22/APR/81
Clark
09/JUN/81
James
03/DEC/81
Ward
22/FEB/81
Allan
20/FEB/81
Turner
08/SEP/81


10. query to display name and job of all employees who do not have a current manager

select ename, job_type from emp33 where manager is null;

ENAME
JOB_TYPE
King
President


11. query to display name, salary and commision of all the employees who earn commision

select ename, salary, commission from emp33 where commission>0

ENAME
SALARY
COMMISSION
Martin
1250
1400
Ward
1300
500
Allan
2000
300


12. sort the data in decending order of salary and commision

select * from emp33 order by salary desc, commission desc

ENO
ENAME
JOB_TYPE
MANAGER
HIRE_DATE
DNO
COMMISSION
SALARY
783
King
President
- 
17/NOV/81
10
0
2950
778
Clark
Manager
783
09/JUN/81
10
0
2900
769
Blake
Manager
783
01/MAY/81
30
0
2870
788
Scott
Analyst
756
09/DEC/81
20
0
2850
792
Jones
Analyst
756
03/DEC/81
20
0
2600
756
Jones
Manager
783
02/APR/81
20
0
2300
749
Allan
Sales_man
769
20/FEB/81
30
300
2000
784
Turner
Sales_man
769
08/SEP/81
30
0
1450
752
Ward
Sales_man
769
22/FEB/81
30
500
1300
793
Miller
Clerk
788
23/JAN/82
40
0
1300

13. query to display name of all the employees where third letter of their name is a

select ename from emp33 where ename like '__a%'

ENAME
Blake
Clark
Adams


14. query to display name of all employees either have two r's or two a's in their name and are either in department number 30 or their manager's employee number is 778

select ename from emp33 where (lower(ename) like '%a%a%' or lower(ename) like '%r%r%') and (dno=30 or manager=778)

ENAME
Adams
Allan
Turner