# SQL Query Interview Questions

## 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;```

Translate »