Solved Practical Problems on Oracle

  

                                                                               

       

1) Display the details of all employees

SQL> select * from emp;

2) Display the department information from department table

SQL> select * from dept;

3) Display the name and job for all the employees

SQL> select ename,job from emp;

4) Display the name and salary for all the employees

SQL> select ename,sal from emp;

5) Display the employee no and total salary for all the employees

SQL> select empno,sal+nvl(comm,0) from emp;

6) Display the employee name and annual salary for all employees.

SQL> select ename,(sal+nvl(comm,0))*12 ANNUALSAL from emp;

7) Display the names of all the employees who are working in depart number 10.

SQL> select * from emp where deptno=10;

8) Display the names of all the employees who are working as clerks and drawing a salary more than 3000.

SQL> select * from emp where job='CLERK' and sal>3000;

9) Display the employee number and name who are earning comm.

SQL> select empno,ename from emp where comm is not null;

10) Display the employee number and name who do not earn any comm.

SQL> select empno,ename from emp where comm is null;

11) Display the names of employees who are working as clerks,salesman or analyst and drawing a salary more than 3000.

SQL> select * from emp where job in('CLERK','ANALYST','SALESMAN') and sal>3000;

12) Display the names of the employees who are working in the company for the past 5 years;

SQL> select ename from emp where to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')>=5;

13) Display the list of employees who have joined the company before 30-JUN-81 or after 31-DEC-81.

SQL> select * from emp where hiredate<'30-JUN-81' or hiredate>'31-DEC-81';

14) Display current Date and Time.

SQL> select to_char(sysdate,'dd mon yy hh:mm:ss pm') from dual;

15) Display the list of all users in your database(use catalog table).

SQL> select username from all_users;

16) Display the names of all tables from current user;

SQL> select tname from tab;

17) Display the name of the current user.

SQL> show user;

18) Display the names of employees working in depart number 10 or 20 or 40 or employees working as CLERKS,SALESMAN or ANALYST.

SQL> select * from emp where deptno in(10,20,40) or job in('CLERK','SALESMAN','ANALYST');

19) Display the names of employees whose name starts with alaphabet S.

SQL> select ename from emp where ename like 'S%';

20) Display the Employee names for employees whose name ends with alaphabet S.

SQL> select ename from emp where ename like '%S';

21) Display the names of employees whose names have second alphabet A in their names.

SQL> select ename from emp where ename like '_A%';

22) select the names of the employee whose names is exactly five characters in length.

SQL> select ename from emp where length(ename)=5;

23) Display the names of the employee who are not working as MANAGERS.

SQL> select ename from emp where job not in ('MANAGER');

24) Display the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST.

SQL> select ename from emp where job not in ('SALESMAN','CLERK','ANALYST');

24) Display the names of the employee who are not working in deptno 10,20 and not as a CLERK.

SQL> select ename from emp where deptno not in(10,20) and job not in('CLERK');

26) Display the total number of employee working in the company.

SQL> select count(*) from emp;

27) Display the total salary being paid to all employees.

SQL> select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL> select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL> select min(sal) from emp;

30) Display the average salary from emp table.

SQL> select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL> select max(sal) from emp where job='CLERK';

32) Display the maximum salary being paid to depart number 20.

SQL> select max(sal) from emp where deptno=20;

33) Display the minimum salary being paid to any SALESMAN.

SQL> select min(sal) from emp where job='SALESMAN';

34) Display the average salary drawn by MANAGERS.

SQL> select avg(sal) from emp where job='MANAGER';

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL> select sum(sal) from emp where job='ANALYST' and deptno=40;

36) Display the names of the employee in order of salary i.e the name of the employee earning lowest salary should salary appear first.

SQL> select ename,sal from emp order by sal;

37) Display the names of the employee in descending order of salary.

SQL> select ename,sal from emp order by sal desc;

38) Display the names of the employee in order of employee name.

SQL> select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and within name by deptno and with in deptno by sal.

SQL> select empno,ename,deptno,sal from emp order by ename,deptno,sal;

40) Display the name of the employee along with their annual salary(sal*12) including comm.The name of the employee earning highest annual salary should appear first.

SQL> select ename,(sal+nvl(comm,0))*12 ANNUAL_SAL from emp order by (sal+nvl(comm,0))*12 desc;

41) Display name,salary,hra,pf,da,total salary for each employee. The output should be in the order of total salary,hra 15% of salary,da 10% of salary,pf 5% salary,total salary will be(salary+hra+da)-pf.

SQL> select ename,sal,(15/100)*sal HRA,(5/100)*sal PF, (10/100)*sal DA, (sal+(15/100)*sal+(10/100)*sal)-(5/100)*sal TOTAL_SAL from emp;

42) Display department numbers and total number of employees working in each department.

SQL> select deptno,count(empno) from emp group by deptno;

43) Display the various jobs and total number of employees within each job group.

SQL> select job,count(empno) from emp group by job;

44) Display the depart numbers and total salary for each department.

SQL> select deptno,sum(sal) from emp group by deptno;

45) Display the depart numbers and max salary for each department.

SQL> select deptno,max(sal) from emp group by deptno;

46) Display the various jobs and total salary for each job

SQL> select job,sum(sal) from emp group by job;

47) Display the various jobs and minimum salary for each job.

SQL> select job,min(sal) from emp group by job;

48) Display the depart numbers with more than three employees in each dept.

SQL> select deptno,count(*) from emp group by deptno having count(*)>3;

49) Display the various jobs along with total salary for each of the jobs where total salary is greater than 8000.

SQL> select job,sum(sal) from emp group by job having sum(sal)>8000;

50) Display the various jobs along with total number of employees in each job.The output should contain only those jobs with more than three employees.

SQL> select job,count(*) from emp group by job having count(*)>3;

51) Display the name of the employee who earns highest salary.

SQL> select ename from emp where sal=(select max(sal) from emp);

52) Display the employee number and name for employee working as clerk and earning highest salary among clerks.

SQL> select empno,ename from emp where sal=(select max(sal) from emp where job='CLERK');

53) Display the names of salesman who earns a salary more than the highest salary of any clerk.

SQL> select ename from emp where sal>(select max(sal) from emp where job='CLERK') and job='SALESMAN';

54) Display the names of clerks who earn a salary more than the lowest salary of any salesman.

SQL> select ename from emp where sal>(select min(sal) from emp where job='SALESMAN') and job='CLERK';

55) Display the names of the employees who earn highest salary in their respective departments.

SQL> select ename,deptno,sal from emp where sal in(select max(sal) from emp group by deptno);

56) Display the names of the employees who earn highest salaries in their respective job groups.

SQL> select ename,job from emp where sal in(select max(sal) from emp group by job);

57) Display the employee names who are working in accounting department.

SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';

58) Display the employee names who are working in Chicago.

SQL> select e.ename from emp e,dept d where e.deptno=d.deptno and d.loc='CHICAGO';

59) Display the Job groups having total salary greater than the maximum salary for managers.

SQL> select job,sum(sal) from emp group by job having sum(sal)>(select max(sal) from emp where job='MANAGER');

60) Display the names of employees from department number 10 with salary greater than that of any employee working in other department.

SQL> select * from emp where deptno=10 and sal>any(select sal from emp where deptno not in(10));

61) Display the names of the employees from department number 10 with salary greater than that of all employee working in other departments.

SQL> select * from emp where deptno=10 and sal>all(select sal from emp where deptno not in(10));

62) Display the names of the employees in Uppercase.

SQL> select upper(ename) from emp;

63) Display the names of the employees in Lower case.

SQL> select lower(ename) from emp;

64) Display the names of the employees in Proper case.

SQL> select initcap(ename) from emp;

65) Display the length of Your name using appropriate function.

SQL> select length('rakesh') from dual;

66) Display the length of all the employee names.

SQL> select length(ename) from emp;

67) select name of the employee concatenate with employee number.

SQL> select ename||empno from emp;

68) User appropriate function and extract 2 characters starting from 3 characters from the following string 'Oracle'. i.e the out put should be 'ac'.

SQL> select substr('Oracle',3,2) from dual;

69) Find the First occurrence of character 'a' from the following string i.e 'Computer Maintenance Corporation'.

SQL> select instr( 'Computer Maintenance Corporation','a',1,1) from dual;

70) Replace every occurrence of alphabet A with B in the string Allens(use translate function).

SQL> select translate('Allens','A','B') from dual;

71) Display the information from emp table.Where job manager is found it should be displayed as boos(Use replace function).

SQL> select replace(job,'MANAGER','BOSS') from emp;

72) Display empno,ename,deptno from emp table.Instead of display department numbers display the related department name(Use decode function).

SQL> select empno,ename,decode(deptno,10,(select dname from dept where deptno=10),20,(select dname from dept where deptno=20),30,(select dname from dept where deptno=30),'N/A') from emp;

73) Display your age in days.

SQL> select to_date(sysdate)-to_date('27-OCT-91')from dual;

74) Display your age in months.

SQL> select months_between(sysdate,'27-OCT-91') from dual;

75) Display the current date as 15th August Friday Nineteen Ninety Seven.

SQL> select initcap(to_char(sysdate,'ddth month day year')) from dual;

76) Display the following output for each row from emp table. scott has joined the company on Wednesday 13th August nineteen ninety.

SQL> select ename||' has joined the company on '||initcap(to_char(sysdate,'day ddth month year')) from emp;

77) Find the date for nearest saturday after current date.

SQL> select next_day(sysdate,'saturday') from dual;

78) Display current time.

SQL> select to_char(sysdate,'hh:mm:ss am') from dual;

79) Display the date three months Before the current date.

SQL> select add_months(sysdate,-3) from dual;

80) Display the common jobs from department number 10 and 20.

SQL> select job from emp where deptno=10 and job in(select job from emp where deptno=20);

81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.

SQL> select distinct(job) from emp where deptno in(10,20);

82) Display the jobs which are unique to department 10.

SQL> select distinct(job) from emp where deptno=10;

83) Display the details of those who do not have any person working under them.

SQL> select empno,ename from emp minus select m.empno,m.ename from emp e,emp m where e.mgr=m.empno group by m.ename,m.empno;

84) Display the details of those employees who are in sales department and grade is 3.

SQL> select e.* from emp e,dept d,salgrade s where e.deptno=d.deptno and d.dname='SALES' and e.sal between s.losal and s.hisal and s.grade=3;

85) Display those who are not managers and who are managers any one.

i)display the managers names

SQL> select e.ename from emp e where ename in(select distinct(m.ename) from emp e,emp m where e.mgr=m.empno);

ii)display the who are not managers

SQL> select e.ename from emp e where ename not in(select distinct(m.ename) from emp e,emp m where e.mgr=m.empno);

86) Display those employee whose name contains not less than 4 characters.

SQL> select * from emp where length(ename)>=4;

87) Display those department whose name start with "S" while the location name ends with "K".

SQL> select * from dept where dname like 'S%' and loc like '%K';

88) Display those employees whose manager name is JONES.

SQL> select e.* from emp e,emp m where e.mgr=m.empno and m.ename='JONES';

89) Display those employees whose salary is more than 3000 after giving 20% increment.

SQL> select * from emp where (sal+(20/100)*sal)>3000;

90) Display all employees with their dept names;

SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

91) Display ename who are working in sales dept.

SQL> select e.ename from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';

92) Display employee name,deptname,salary and comm for those sal in between 2000 to 5000 while location is chicago.

SQL> select e.ename,d.dname,e.sal,nvl(e.comm,0) from emp e,dept d where e.sal between 2000 and 5000 and e.deptno=d.deptno and d.loc='CHICAGO';

93)Display those employees whose salary greater than his manager salary.

SQL> select e.* from emp e,emp m where e.mgr=m.empno and e.sal>m.sal;

94) Display those employees who are working in the same dept where his manager is work.

SQL> select e.* from emp e,emp m where e.mgr=m.empno and e.deptno=m.deptno;

95) Display those employees who are not working under any manager.

SQL> select * from emp where mgr is null;

96) Display grade and employees name for the dept no 10 or 30, but grade is not 4, while joined the company before 31-dec-82.

SQL> select s.grade,e.ename from emp e,salgrade s where e.sal between s.losal and s.hisal and deptno in(10,20) and s.grade<>4 and e.hiredate<'31-DEC-82';

97) Update the salary of each employee by 10% increment who are not eligible for commission.

SQL> update emp set sal=sal+((10/100)*sal) where comm is null;

98) SELECT those employee who joined the company before 31-dec-82 while their dept location is newyork or Chicago.

SQL> select e.* from emp e,dept d where e.deptno=d.deptno and e.hiredate<'31-dec-82' and d.loc in('NEWYORK','CHICAGO');

99) DISPLAY EMPLOYEE NAME, JOB, DEPARTMENT, LOCATION FOR ALL WHO ARE WORKING AS MANAGER?

SQL> select e.ename,e.job,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';

100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? --[AND ALSO DISPLAY JONES MANAGER NAME]?

SQL> select e.ename,m.ename from emp e,emp m where e.mgr=m.empno and m.ename='JONES'

  union

  select e.ename,m.ename from emp e,emp m where e.mgr=m.empno and e.ename='JONES';

101) Display name and salary of ford if his salary is equal to hisal of his grade.

SQL> select e.ename,e.sal from emp e,salgrade s where e.sal between s.losal and s.hisal and e.ename='FORD' and e.sal=s.hisal;

102) Display employee name,job,depart name ,manager name,his grade and make out an under department wise?

SQL> select e.ename,e.job,d.dname,m.ename,s.grade from emp e,emp m,dept d,salgrade s where e.mgr=m.empno and e.deptno=d.deptno and e.sal between s.losal and s.hisal order by e.deptno;

103) List out all employees name,job,salary,grade and depart name for every one in the company except 'CLERK'.Sort on salary display the highest salary?

SQL> select e.ename,e.job,e.sal,s.grade,d.dname from emp e,salgrade s,dept d where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.job not in('CLERK') order by e.sal desc;

104) Display the employee name,job and his manager.Display also employee who are without manager?

SQL> select e.ename,e.job,m.ename as manager from emp e,emp m where e.mgr = m.empno(+);

105) Find out the top 5 earners of company?

SQL> select * from (select ename,sal,dense_rank()over(order by sal desc)rank from emp) where rank<6;

106) Display name of those employee who are getting the highest salary?

SQL> select ename from emp where sal=(select max(sal) from emp);

107) Display those employee whose salary is equal to average of maximum and minimum?

SQL> select * from emp where sal=(select (max(sal)+min(sal))/2 from emp);

108) Select count of employee in each department where count greater than 3?

SQL> select deptno,count(*) as "no of employee" from emp group by deptno having count(*)>3;

109) Display dname where at least 3 are working and display only department name?

SQL> select d.dname from emp e,dept d where d.deptno=e.deptno group by d.dname having count(*)>2;

110) Display those managers name whose salary is more than average salary of his employee?

SQL> select m.ename from emp e,emp m where e.mgr=m.empno and m.sal>(select avg(sal) from emp) group by m.ename;

111) Display ename and managers name where emp  salary is more than average salary of his company?

SQL> select e.ename as "manager",m.ename as "manager's manager" from emp e,emp m where e.mgr=m.empno and e.sal>(select a

vg(sal) from emp);

112) Display employee name,sal,comm and net pay for those employee whose net pay is greater than or equal to any other employee salary of the company?

SQL> select e.ename,e.sal,e.comm,e.sal+nvl(e.comm,0) from emp e where (e.sal+nvl(e.comm,0))>=any(select sal from emp);

113) Display all employees names with total sal of company with each employee name?

SQL> select ename,(select sum(sal) from emp) from emp;

114) Find out last 5(least)earners of the company.?

SQL> select * from (select ename,dense_rank()over(order by sal)rank from emp) where rank<=5;

115) Find out the number of employees whose salary is greater than their manager salary?

SQL> select count(*) from emp e,emp m where e.mgr=m.empno and e.sal>m.sal;

116) Display those department name where no employee working?

SQL> select distinct(d.dname) from emp e,dept d where d.deptno not in(select deptno from emp group by deptno);

117) Display those employee whose salary is ODD value?

SQL> select * from emp where mod(sal,2)>0;

118) Display those employee whose salary contains at least 3 digits excluding decimal value?

SQL> select ename,sal,(case when length(substr(sal,1,instr(sal,'.',1)-1))>3 then substr(sal,1,instr(sal,'.',1)-1) else substr(sal,1,length(sal)) end) as msal from emp;

119) Display those employee who joined in the company in the month of Dec?

SQL> select * from emp where to_char(hiredate,'mon')='dec';

120) Display those employees whose name contains "A"?

SQL> select ename from emp where instr(ename,'A')>0;

121) Display those employee whose deptno is available in salary?

SQL> select e.* from emp e,emp d where e.deptno=d.sal;

122) Display the employee name with first 2 characters from hiredate and last 2 characters of salary?

SQL> select substr(hiredate,1,2)||ename||substr(sal,-2,2) from emp;

123) Display those employee whose 10% of salary is equal to the year of joining?

SQL> select * from emp where sal*(10/100)=to_char(hiredate,'yy');

124) Display those employee who are working in sales or research?

SQL> select e.*,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname in('SALES','RESEARCH');

125) Display the grade of jones?

SQL> select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal and e.ename='JONES';

126) Display those employees who joined the company before 15 of the month?

SQL> select * from emp where to_char(hiredate,'dd')<15;

127) Display those employee who has joined between January and April.

SQL> select * from emp where to_char(hiredate,'mm') between 1 and 4;

128) Delete those records where no of employees in a particular department is less than 3.

SQL> delete from emp where deptno=(select deptno from emp group by deptno having count(*)<3);

129) Display the name of the department where no employee working.

SQL> select distinct(d.dname) from emp e,dept d where d.deptno not in(select deptno from emp group by deptno);

130) Display those employees who are working as manager.

SQL> select m.ename from emp e,emp m where e.mgr=m.empno group by m.ename;

131) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?

SQL> select e.ename,s.grade,e.sal,s.losal,s.hisal from emp e,salgrade s where e.sal between s.losal and s.hisal and s.grade<>substr(sal,0,1);

132) Print the details of all the employees who are Sub-ordinate to BLAKE?

SQL> select e.ename from emp e,emp m where e.mgr=m.empno and m.ename='BLAKE';

133) Display employee name and his salary whose salary is greater than highest average of department number?

SQL> select e.ename,e.sal from emp e where e.sal>(select max(avg(sal)) from emp group by deptno);

134) Display the 10th record of emp table(without using rowid)

SQL> select * from emp where rownum<11

  minus

  select * from emp where rownum<10;

135) Display the half of the ename's in upper case and remaining lowercase?

SQL> select upper(substr(ename,1,ceil(length(ename)/2))) || lower(substr(ename,ceil(length(ename)/2)+1,length(ename))) from emp;

136) Delete the 10th record of emp table.

SQL> delete from empbak where empno=(select empno from emp where rownum<11

  minus

  select empno from emp where rownum<10);

137) Create a copy of emp table;

SQL> create table empbak as select * from emp;

138) Select ename if ename exists more than once.

SQL> select ename from emp group by ename having count(*)>1;

139) Display all enames in reverse order?(SMITH:HTIMS).

SQL> select ename||':'||reverse(ename) from emp;

140) Display those employee whose joining of month and grade is equal.

SQL> select e.ename,e.hiredate,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal and to_char(e.hiredate,'mm')=s.grade;

141) Display those employee whose joining DATE is available in deptno.

SQL> select e.ename,e.hiredate,e.deptno from emp e where to_char(hiredate,'dd')=deptno;

142) Display those employees name as follows

A ALLEN

B BLAKE

SQL> select substr(ename,1,1)||' '||ename from emp;

143) List out the employees ename,sal,PF(20% OF SAL) from emp;

SQL> select ename,sal,(20/100)*sal PF from emp;

144) Create table emp with only one column empno;

SQL> create table empbak as select empno from emp;

145) Add this column to empbak table ename varchar2(20).

SQL> alter table empbak add(ename varchar2(20));

146) Oops I forgot give the primary key constraint. Add in now.

SQL> alter table empbak add primary key(empno);

147) Now increase the length of ename column to 30 characters.

SQL> alter table empbak modify(ename varchar2(30));

148) Add salary column to empbak table.

SQL> alter table empbak add(sal number(10,2));

149) I want to give a validation saying that salary cannot be greater 10,000 (note give a name to this constraint)

SQL> alter table empbak add constraint c1 check(sal<10000);

150) For the time being I have decided that I will not impose this validation.My boss has agreed to pay more than 10,000.

SQL> alter table empbak modify constraint c1 disable;

151) My boss has changed his mind. Now he doesn't want to pay more than 10,000.so revoke that salary constraint.

SQL> alter table empbak modify constraint c1 enable;

152) Add column called as mgr to your empbak table;

SQL> alter table empbak add(mgr number(5));

153) Oh! This column should be related to empno. Give a command to add this constraint.

SQL> alter table empbak add constraint c2 foreign key(mgr) references empbak(empno);

154) Add dept column to your empbak table;

SQL> alter table empbak add(dept number(5));

155) This dept column should be related to deptno column of dept table;

SQL> alter table empbak add constraint c3 foreign key(dept) references dept(deptno);

156) Give the command to add the constraint.

SQL>alter table <table_name) add constraint <constraint_name> <constraint type>

157) Create table called as newemp. Using single command create this table as well as get data into this table(use create table as);

SQL>create table newemp as select * from emp;

158) Delete the rows of employees who are working in the company for more than 2 years.

SQL>delete from empbak where (sysdate-hiredate)/365>2;

159) Provide a commission(10% Comm Of Sal) to employees who are not earning any commission.

SQL> update emp set sal=sal+sal*.1 where comm is null;

160) If any employee has commission his commission should be incremented by 10% of his salary.

SQL> update emp set sal=sal+sal*.1 where comm is not null;

161) Display employee name and department name for each employee.

SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

162)Display employee number,name and location of the department in which he is working.

SQL> select e.empno,e.ename,d.loc from emp e,dept d where e.deptno=d.deptno;

163) Display ename,dname even if there are no employees working in a particular department.

SQL> select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;

164) Display employee name and his manager name.

SQL> select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;

165) Display the department name and total number of employees in each department.

SQL> select d.dname,count(*) from emp e,dept d where e.deptno=d.deptno group by d.dname;

166)Display the department name along with total salary in each department.

SQL> select d.dname,sum(e.sal) from emp e,dept d where e.deptno=d.deptno group by d.dname;

167) Display itemname and total sales amount for each item.

SQL>select itemname,sum(amount) from item group by itemname;

168) Write a Query To Delete The Repeated Rows from emp table.

SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename);

169) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL> select * from emp where rownum<8

  minus

  select * from emp where rownum<5;

170) DISPLAY TOP 10 ROWS FROM TABLE?

SQL> select * from emp where rownum<11;

171) DISPLAY TOP 3 SALARIES FROM EMP;

SQL> select * from (select empno,ename,sal,dense_rank()over(order by sal desc)rank from emp) where rank<=3;

172) DISPLAY 9th record FROM THE EMP TABLE?

SQL> select * from (select rownum rr,a.* from emp a ) where rr=9;

173) select second max salary from emp;

SQL> select max(sal) from emp where sal<(select max(sal) from emp);

174) Display employee records whose name contains exactly one 'A';

SQL> select * from emp where length(ename) - length(replace(ename,'A','')) = 1;

175) Display employee records joined in first 15 days in the month APR,DEC in the year between 1980 and 1987.

SQL> select * from emp where to_char(hiredate,'dd')<=15 and to_char(hiredate,'mm') in(4,12) and to_char(hiredate,'yyyy') between 1980 and 1987;

176) Display ename,sal,hiredate and date of retirement, assume that date of retirement is 30 years after date of join.

SQL> select ename,sal,hiredate,add_months(hiredate,30*12) Retirement from emp;

177) Display first day of the current month.

SQL> select add_months(last_day(sysdate)+1,-1) from dual;

178) If deptno = 10 ,    if job='CLERK' then increment sal by 10%

                                   if job='MANAGER' then increment by 15%

                                   Others increment sal by 5%

       If deptno = 20,     if job='CLERK' then increment sal by 15%

                                  if job='MANAGER' then increment sal by 20%

                                  Others increment sal by 10%

       Others dept set to same value.

SQL> update emp set sal=decode(deptno,10,decode(job,'CLERK',sal*1.1,'MANAGER',sal*1.15,sal*1.05), 20, decode(job,'CLERK',sal*1.15,'MANAGER',sal*1.2,sal*1.1),sal);

179) Display number of employees joined as follows ?

            1980         1981        1982

               ?              ?             ?

SQL> select count(decode(to_char(hiredate,'yyyy'),1980,empno)) Y1980, count(decode(to_char(hiredate,'yyyy'),1981,empno))Y1981, count(decode(to_char(hiredate,'yyyy'),1982,empno))Y1982 from emp;