This preview shows page 17 - 20 out of 21 pages.

selectdeptno,sum(sal)fromempgroupby deptno havingsum(sal)=(selectmax(sum(sal)) fromempgroupbydeptno); 169.In which year did most people join the company. Display the year and numberof employees selectcount(*),to_char(hiredate,'yyyy')fromemp groupbyto_char(hiredate,'yyyy'); 170.display avg sal figure for the dept selectdeptno,avg(sal)fromempgroupby deptno; 171.Write a query of display against the row of the most recently hired employee.display ename hire date and column max date showing. selectempno,hiredatefromempwherehiredate=(selectmax(hiredate)fromemp); 172.display employees who can earn more than lowest Sal in dept no 30 select*fromempwheresal>(selectmin(sal)fromempwheredeptno=30);
Image of page 17
173.find employees who can earn more than every employees in dept no30select*fromempwheresal>(selectmax(sal)fromempwheredeptno=30);select* fromempwheresal>all(selectsal fromempwheredeptno=30); dept name dept no and sum of Salbreakondeptnoondname;selecte.deptno,d.dname,sal fromempe,deptdwheree.deptno=d.deptnoorderbye.deptno;175.find out avg sal and avg total remainders for each job type176.findall dept’s which have more than 3 employeesselectdeptnofromempgroupby deptno having count(*)>3;177.If the pay day is next Friday after 15thand 30thof every month. Whatis thenext pay day from their hire date for employee in emp table178.If an employee is taken by you today in your organization. And is a policyinyour company to have a review after 9 months the joined date (and of 1stof nextmonth after 9 months) how many days from today your employee has to wait for areview179.Display employee name and his sal whose sal is greater than highestavg of dept no180.Display the 10threcord of EMP table. (without using rowid)181.Display the half of the enames in upper case and remaining lowercaseselectconcat(upper(substr(ename,0 ,length (ename)/2)),lower(substr(ename,length(ename)/2+1,length(ename))))fromemp;182.display the 10threcord of emp table without using group by and rowed183.Delete the 10threcord of emp table.184.Create a copy of emp table. 185.Select ename if ename exists more than once.
Image of page 18