String Related SQL Query Help

2.STRING RELATED SQL QUERY Help

CREATE EMPLOYEE TABLE

1. Get all employee detail from EMPLOYEE table whose " FIRST_NAME" not start with any single character between 'A-N'

Query:

SELECT * FROM EMPLOYEE WHERE FIRST_NAME like '[^A-N]%'

2. Get all employee detail from EMPLOYEE table whose "Gender" end with 'le' and contain 4 letters. The Underscore(_) Wildcard Character represents any single character.

Query:

SELECT * FROM EMPLOYEE WHERE Gender like '__le' --there are two "_"

3. Get all employee detail from EMPLOYEE table whose " FIRST_NAME" start with 'A' and contain 5 letters.

Query:

SELECT * FROM EMPLOYEE WHERE FIRST_NAME like 'S_____' --there are FIVE"_"

4. Get all employee detail from EMPLOYEE table whose "FIRST_NAME" containing '%'.

Query:

SELECT * FROM EMPLOYEE WHERE FIRST_NAME like '%[%]%'

-- no name containg '%'

5. Get all unique "DEPARTMENT" from EMPLOYEE table.

Query:

SELECT DISTINCT DEPARTMENT FROM EMPLOYEE ;

6. Get the highest "SALARY" from EMPLOYEE table.

Query:

SELECT MAX(SALARY) FROM EMPLOYEE ;

7. Get the lowest "SALARY" from EMPLOYEE table.

Query:

SELECT MIN(SALARY) FROM EMPLOYEE ;

8. Show "JOINING_DATE" in "dd mmm yyyy" format, ex- "10 Feb 2014"

Query:

SELECT CONVERT(VARCHAR(20),JOINING_DATE,106) FROM EMPLOYEE ;

9. Show "JOINING_DATE" in "yyyy/mm/dd" format, ex- "2014/02/10"

Query:

SELECT CONVERT(VARCHAR(20),JOINING_DATE,111) FROM EMPLOYEE ;

10. Show only time part of the "JOINING_DATE".

Query:

SELECT CONVERT(VARCHAR(20),JOINING_DATE,108) FROM EMPLOYEE ;

3. Group by related SQL Query -

EMPLOYEE TABLE

ASSIGNMENT TABLE

1. query to get the department and department wise total salary from EMPLOYEE table.

SELECT DEPARTMENT, SUM(SALARY) AS TOTAL_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT

2. query to get the department and department wise total salary, display it in ascending order according to salary.

SELECT DEPARTMENT, SUM(SALARY) AS TOTAL_SALARY FROM EMPLOYEE
GROUP BY DEPARTMENT
ORDER BY SUM(SALARY) ASC

3. query to get the department and department wise total salary, display it in descending order according to salary.

SELECT DEPARTMENT, SUM(SALARY) AS TOTAL_SALARY FROM EMPLOYEE
GROUP BY DEPARTMENT
ORDER BY SUM(SALARY) desc

4. query to get the department, total number of department, total salary with respect to department from EMPLOYEE table.

SELECT DEPARTMENT, COUNT(*) AS TOTAL, SUM(SALARY) FROM EMPLOYEE
GROUP BY DEPARTMENT

5. query to get the department wise average salary from EMPLOYEE table order by salary ascending.

SELECT DEPARTMENT, AVG(SALARY) AS TOTAL FROM EMPLOYEE
GROUP BY DEPARTMENT
ORDER BY AVG(SALARY)ASC

6. query to get the department wise maximum salary from EMPLOYEE table order by salary ascending.

SELECT DEPARTMENT, MAX(SALARY) AS TOTAL FROM EMPLOYEE
GROUP BY DEPARTMENT
ORDER BY MAX(SALARY)ASC

7. query to get the department wise minimum salary from EMPLOYEE table order by salary ascending.

SELECT DEPARTMENT, MIN(SALARY) AS TOTAL FROM EMPLOYEE
GROUP BY DEPARTMENT
ORDER BY MIN(SALARY)ASC

8. query to fetch ASSIGNMENT_NAME assign to more than one employee.

SELECT ASSIGNMENT_NAME, COUNT(*) AS NO_EMP FROM ASSIGNMENT
GROUP BY ASSIGNMENT_NAME
HAVING COUNT(*) > 1