2.STRING RELATED SQL QUERY Help
CREATE EMPLOYEE TABLE
EMP_ID |
FIRST_NAME |
LAST_NAME |
GENDER |
DEPARTMENT |
JOINING_DATE |
SALARY |
1001 |
ANDREW |
CENCINI |
MALE |
IT |
08-01-2014 |
300000 |
1002 |
STEVEN |
THROPE |
MALE |
HR |
10-02-2014 |
400000 |
1003 |
ANNE |
HELLUNG-LARSEN |
FEMALE |
FINANCE |
20-03-2014 |
500000 |
1004 |
DENE |
SENA |
FEMALE |
ACCOUNT |
30-04-2014 |
200000 |
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
EMP_ID |
FIRST_NAME |
LAST_NAME |
GENDER |
DEPARTMENT |
JOINING_DATE |
SALARY |
1001 |
ANDREW |
CENCINI |
MALE |
IT |
08-01-2014 |
300000 |
1002 |
STEVEN |
THROPE |
MALE |
HR |
10-02-2014 |
400000 |
1003 |
ANNE |
HELLUNG-LARSEN |
FEMALE |
FINANCE |
20-03-2014 |
500000 |
1004 |
DENE |
SENA |
FEMALE |
ACCOUNT |
30-04-2014 |
200000 |
ASSIGNMENT TABLE
ASSIGN_ID |
EMP_ID |
ASSIGNMENT_NAME |
ASSIGN_DATE |
DUE_DATE |
11 |
1001 |
MENAGEMENT |
01-02-2014 |
01-03-2014 |
22 |
1001 |
HR SYSTEM |
11-02-2014 |
11-04-2014 |
33 |
1002 |
MENAGEMENT |
01-03-2014 |
01-04-2014 |
44 |
1003 |
FINANCE |
01-03-2014 |
01-04-2014 |
55 |
1003 |
DBMS |
11-04-2014 |
11-05-2014 |
66 |
1004 |
DB |
10-04-2014 |
10-06-2014 |
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
Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More
Follow Us