Skip to content

Instantly share code, notes, and snippets.

@sambhav2612
Created February 15, 2018 07:32
Show Gist options
  • Save sambhav2612/fa0def2e8756e89d3db62e23d08c73f5 to your computer and use it in GitHub Desktop.
Save sambhav2612/fa0def2e8756e89d3db62e23d08c73f5 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment