SQL Query and Answers - Part6

126. Display ename,sal and deptno for each employee who earn a sal greater than the avg of their department order by deptno

select ename,sal,deptno from emp e where sal >(select avg(sal) from emp where deptno=e.deptno) order by deptno;;

127. Display the department where there are no employees;

select deptno,dname from dept where deptno not in(select distinct(deptno) from emp);

128. Display the dept no with highes tannual remuneration bill as compensation.

select deptno,sum(sal) from emp group by deptno having sum(sal) = (select mac(sum(sal)) from emp group by deptno);

129. In which year did most people join the company. Diaplay the year and number of employees(hey try out)

select max(aa) from (select count(*) aa, to _char(hiredate,'yyyy') dd from emp group by to_char(hiredate,'yyyy'))

130. Display avg sal figure for the dept

select deptno,avg(sal) from emp group byu deptno;

131. write a query of display against the row of the most recently hired employee. Display ename hire date and column

max date showing.

select empno, hiredate from emp where hiredate=(select max (hiredate) from emp);

132. Display employees who can earn more than lowest sal in dept no 30

select * from emp where sal>(select min(sal) from deptno=30);

133. Find employees who can earn more than every employees in dept no 30

select * from emp where sal>(select max(sal) from emp where deptno=30);

select * from emp where sal>all(select sal from emp where deptn0=30);

134. select dept name dept no and sum of sal

break on deptno on dname;

select e.deptno,d.dname,sal fdrom emp e, dept d

where e.deptno=d.deptno

order by e.deptno;

135. find out avg sal and ave total remainders for each job type

136. find all dept's which have more than 3 employees.

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

137. Display the half of the enames in upper case and remaining lowe case.

select concat(upper(substr(ename,0,lengh(ename)/2)), lower (substr(ename,lengh(ename)/2+1,lenght(ename)))) from emp;

138. select ename if ename exists more than once.

select distinct(ename) from emp e where ename in(select ename from emp where e.empno<>empno);

139. Display all enames in reverse order

select ename from emp order by ename desc

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

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

141. Display those employee whose joining date is avaiable in dept no

select * from emp where to_char(hiredate,'dd')=deptno;

142. Display those employee name as follows A allen, B black

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

143. List out the employees ename,sal pf from emp

select ename,sal,sal*15/100 pf from emp

144. Display RSPS from emp without using updating,inserting

Create table emp with only one column empno

create table emp(empno number(5));

145. Add this column to emp table ename varchar(20)

alter table emp add ename varchar2(20) not null;

146. opps I forgot to give the primary Key constrains. Add it now.

Alter table emp add constrains emp_empno primary key(empno);

147.  now increase the length of ename column to 30 characters,

alter table emp modify ename varchar2(30);

148. Add salary column to emp table.

alter table emp add sal number(7,2);

149. I wan tto give a validation saying that sal cannot be greater 10,000(note give a name to this comumn)

Alter table emp add constraints emp_sal_check check(sal<10000);

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

Alter table emp disable constrainsts emp_sal_check;

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

Alter table emp enable constraints emp_sal_check;

152. Add column called a s mgr to your emp table.

Alter table emp add mgr number(5);

153. Oh ths column should be related to empno. give a command to add this constrains

Alter table emp add constraint emp_mgr foreign key(empno);

154. Add dept no column to your emp table

alter table emp add deptno number(3);

155. This dept no column should be related to deptno column to dept table

alter table emp1 add constraints emp1_deptno foreign key(deptno) referemces dept(deptno);

156. Create table called as new emp. using single command create this table as welll as to get data into this table (use create table as)

create table newemp as select * from emp;

157. create table called as newemp. The table should contains only empno,ename,dname

create table newemp as select empno,ename,dname from emp e, dept d where e,deptno=d.deptno;

158. Delete the rows of employees who are workingin the company for more than 3 years

delete from emp where floor(susdate-hiredaye)>2*365;

159. Provide a commission to employees who are not earning any commission.

update emo set comm=300 where comm is null;

160. If any employees has commsision his commsision should be incremented by 10% of his salary

update emp set comm=comm*10/100 where comm is not null;

161. Display employees name and department name for each employee

select ename,dename 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

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

163. display ename, dname even if the employees in a particular department(use outer join)

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

164. Display employee name and his manager name

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

165. Display the department name along with total salary in each department.

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

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

select deptno,count(*) from emp group by deptno;

167. Alter table emp1 add constrains emp1_deptno foreign key(deptno) references dept(deptno)

delete from emp where job name in clerk