Table of Contents
SQL Escape Characters
51. Get names of employees from employee table who has ‘%’ in Last_Name. Tip : Escape character for special characters in a query.?
Select FIRST_NAME FROM EMPLOYEE WHERE LAST_NAME LIKE '%\%%';
52. Get Last Name from employee table after replacing special character with white space?
Select translate(LAST_NAME,'%',' ') from employee
SQL Group By Query
53. Get department,total salary with respect to a department from employee table?
SELECT DEPARTMENT,SUM(SALARY) TOTAL_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT;
54. Get department, total salary with respect to a department from employee table order by total salary descending?
SELECT DEPARTMENT, SUM (SALARY) TOTAL_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY TOTAL_SALARY DESC;
SQL Mathematical Operations using Group By
55. Get department, no of employees in a department, total salary with respect to a department from employee table order by total salary descending?
SELECT DEPARTMENT,COUNT(FIRST_NAME),SUM(SALARY) TOTAL_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY TOTAL_SALARY DESC;
56. Get department wise average salary from employee table order by salary ascending?
SELECT DEPARTMENT,AVG(SALARY) AVGSALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY AVGSALARY ASC;
57. Get department wise maximum salary from employee table order by salary ascending?
SELECT DEPARTMENT,MAX(SALARY) MAXSALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY MAXSALARY ASC;
58. Get department wise minimum salary from employee table order by salary ascending?
SELECT DEPARTMENT,MIN(SALARY) MINSALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY MINSALARY ASC;
59. Select no of employees joined with respect to year and month from employee table?
SELECT TO_CHAR (JOINING_DATE,'YYYY') JOIN_YEAR, TO_CHAR (JOINING_DATE,'MM') JOIN_MONTH,COUNT(*) TOTAL_EMP FROM EMPLOYEE GROUP BY TO_CHAR (JOINING_DATE,'YYYY'),TO_CHAR(JOINING_DATE,'MM');
60. Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending?
SELECT DEPARTMENT,SUM(SALARY) TOTAL_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT HAVING SUM(SALARY) >800000 ORDER BY TOTAL_SALARY DESC;
