Thursday, August 1, 2013

SQL Queries: with solutings



TABLES
CASE STUDY

Create the following Tables:

LOCATION
Location_ID
Regional_Group
122
NEW YORK
123
DALLAS
124
CHICAGO
167
BOSTON

DEPARTMENT
Department_ID
Name
Location_ID
10
ACCOUNTING
122
20
RESEARCH
124
30
SALES
123
40
OPERATIONS
167


JOB
Job_ID
Function
667
CLERK
668
STAFF
669
ANALYST
670
SALESPERSON
671
MANAGER
672
PRESIDENT



EMPLOYEE

EMPLOYEE_ID
LAST_NAME
FIRST_NAME
MIDDLE_NAME
JOB_ID
MANAGER_ID
HIREDATE
SALARY
COMM
DEPARTMENT_ID
7369
SMITH
JOHN
Q
667
7902
17-DEC-84
800
NULL
20
7499
ALLEN
KEVIN
J
670
7698
20-FEB-85
1600
300
30
7505
DOYLE
JEAN
K
671
7839
04-APR-85
2850
NULL
30
7506
DENNIS
LYNN
S
671
7839
15-MAY-85
2750
NULL
30
7507
BAKER
LESLIE
D
671
7839
10-JUN-85
2200
NULL
40
7521
WARK
CYNTHIA
D
670
7698
22-FEB-85
1250
500
30
SALARY_GRADE
GRADE
LOWER_BOUND
UPPER_BOUND
I
4000.00
5000.00
II
3999.00
3000.00
III
2999.00
2000.00
IV
1999.00
1000.00
V
999.00
300.00


Queries based on the above tables:

Simple Queries:

  1. List all the employee details
SELECT * FROM EMPLOYEE
  1. List all the department details
 SELECT * FROM DEPARTMENT
  1. List all job details
SELECT * FROM JOB
  1. List all the locations
SELECT * FROM LOCATION
  1. List out first name,last name,salary, commission for all employees
SELECT LAST_NAME,FIRST_NAME,COMM FROM EMPLOYEE
  1. List out employee_id,last name,department id for all  employees and rename employee id as “ID  of the employee”, last name as “Name of the employee”, department id as  “department  ID”
 SELECT     EMPLOYEE_ID AS "ID OF EMPLOYEE",
                  LAST_NAME AS "EMPLOYEE NAME",
            DEPARTMENT_ID AS "DEPARTMENT ID"
 FROM EMPLOYEE

  1. List out the employees anuual salary with their names only.
SELECT  LAST_NAME AS "EMPLOYEE NAME",
        SALARY*12 AS "ANUAL SALARY"
FROM EMPLOYEE

Where Conditions:
  1. List the details about “SMITH”
          SELECT * FROM EMPLOYEE
WHERE LAST_NAME='SMITH'
  1. List out the employees who are working in department 20
          SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID=20
  1. List out the employees who are earning salary between 3000 and 4500
 SELECT * FROM EMPLOYEE
 WHERE SALARY BETWEEN 3000 AND 4500
  1. List out the employees who are working in department 10 or 20
          SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID IN(20,30)
  1. Find out the employees who are not working in department 10 or 30
          SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID NOT IN(10,30)


  1. List out the employees whose name starts with “S”

          SELECT * FROM EMPLOYEE
WHERE LAST_NAME LIKE 'S%'
  1. List out the employees whose name start with “S” and end with “H”
SELECT * FROM EMPLOYEE
WHERE LAST_NAME LIKE 'S%H'
  1. List out the employees whose name length is 4 and start with “S”
          SELECT * FROM EMPLOYEE
WHERE LAST_NAME LIKE 'S____'
  1. List out the employees who are working in department 10 and draw the salaries more than 3500
          SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID=10
      AND SALARY >3500
  1. list out the employees who are not receiving commission.
SELECT * FROM EMPLOYEE
WHERE COMM IS NULL

Order By Clause:
  1. List out the employee id, last name in ascending order based on the employee id.
          SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEE
ORDER BY EMPLOYEE_ID
  1. List out the employee id, name in descending order based on salary column
          SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEE
ORDER BY SALARY DESC
  1. list out the employee details according to their last_name in ascending order and salaries in descending order
          SELECT * FROM EMPLOYEE
ORDER BY LAST_NAME,SALARY DESC
  1. list out the employee details according to their last_name in ascending order and then on department_id in descending order.
          SELECT * FROM EMPLOYEE
ORDER BY LAST_NAME,DEPARTMENT_ID DESC

Group By & Having Clause:

  1. How many employees who are working in different departments wise in the organization
          SELECT DEPARTMENT_ID,COUNT(*) AS "NUMBER OF EMPLOYEE"
FROM EMPLOYEE
GROUP BY DEPARTMENT_ID
  1. List out the department wise maximum salary, minimum salary, average salary of the employees
          SELECT  DEPARTMENT_ID,
            MAX(SALARY) MAXUM_SALARY,
            MIN(SALARY) MINMUM_SALARY,
            AVG(SALARY) AVERAGE_SALARY
FROM EMPLOYEE
GROUP BY DEPARTMENT_ID
  1. List out the job wise maximum salary, minimum salary, average salaries of the employees.
          SELECT  JOB_ID,
            MAX(SALARY) MAXUM_SALARY,
            MIN(SALARY) MINMUM_SALARY,
            AVG(SALARY) AVERAGE_SALARY
FROM EMPLOYEE
GROUP BY JOB_ID
  1. List out the no.of employees joined in every month in ascending order.
          SELECT DATENAME(MM,HIREDATE) MONTH,COUNT(*) NoOfEmployee
FROM EMPLOYEE
GROUP BY DATENAME(MM,HIREDATE)

  1. List out the no.of employees for each month and year, in the ascending order based on the year, month.
          SELECT  DATEPART(YYYY,HIREDATE) YEAR,
              DATENAME(MM,HIREDATE) MONTH,
              COUNT(*) NoOfEmployee
FROM EMPLOYEE
GROUP BY DATEPART(YYYY,HIREDATE),DATENAME(MM,HIREDATE)
  1. List out the department id having at least four employees.
          SELECT DEPARTMENT_ID,COUNT(*) "NUMBER OF EMPLOYEES"
FROM EMPLOYEE
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>4
  1. How many employees in January month.
          SELECT DATENAME(MM,HIREDATE) MONTH,COUNT(*) "NUMBER OF EMPLOYEES"
FROM EMPLOYEE
WHERE DATENAME(MM,HIREDATE)='FEBRUARY'
GROUP BY DATENAME(MM,HIREDATE)
  1. How many employees who are joined in January or September month.
SELECT DATENAME(MM,HIREDATE) MONTH,COUNT(*) "NUMBER OF EMPLOYEES"
FROM EMPLOYEE
WHERE DATENAME(MM,HIREDATE)IN ('FEBRUARY','MARCH')
GROUP BY DATENAME(MM,HIREDATE)
  1. How many employees who are joined in 1985.
          SELECT DATEPART(YY,HIREDATE) YEAR,COUNT(*) "NUMBER OF EMPLOYEES"
FROM EMPLOYEE
WHERE DATEPART(YY,HIREDATE)=1985
GROUP BY DATEPART(YY,HIREDATE)


  1. How many employees joined each month in 1985.

          SELECT  DATEPART(YY,HIREDATE) YEAR,
              DATENAME(MM,HIREDATE) MONTH,
              COUNT(*) "NUMBER OF EMPLOYEES"
FROM EMPLOYEE
WHERE DATEPART(YY,HIREDATE)=1985
GROUP BY DATEPART(YY,HIREDATE),DATENAME(MM,HIREDATE)
  1. How many employees who are joined in March 1985.

SELECT      DATEPART(YY,HIREDATE) YEAR,
                  DATENAME(MM,HIREDATE) MONTH,
                  COUNT(*) "NUMBER OF EMPLOYEES"
FROM  EMPLOYEE
WHERE DATEPART(YY,HIREDATE)=1985
            AND
            DATENAME(MM,HIREDATE)='MARCH'
GROUP BY DATEPART(YY,HIREDATE),DATENAME(MM,HIREDATE)
  1. Which is the department id, having greater than or equal to 3 employees joined in April 1985.
          SELECT  DEPARTMENT_ID,
            COUNT(*) "NUMBER OF EMPLOYEES"
FROM  EMPLOYEE
WHERE DATEPART(YY,HIREDATE)=1985
            AND
            DATENAME(MM,HIREDATE)='APRIL'
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>=3

Sub-Queries


  1. Display the employee who got the maximum salary.
          SELECT * FROM EMPLOYEE
WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE
                 WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE))
  1. Display the employees who are working in Sales department
          SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENT
                        WHERE NAME='SALES')
  1. Display the employees who are working as “Clerk”.
 SELECT * FROM EMPLOYEE
 WHERE JOB_ID IN (SELECT JOB_ID FROM JOB
                  WHERE FUNCTIONALITY='CLEARK')
  1. Display the employees who are working in “New York”
          SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM DEPARTMENT
            WHERE LOCATION_ID IN (SELECT LOCATION_ID FROM LOCATION
                            WHERE REGIONAL_GROUP='NEW YORK'))


  1. Find out no.of employees working in “Sales” department.

          SELECT * FROM EMPLOYEE
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENT
                                    WHERE NAME='SALES')
  1. Update the employees salaries, who are working as Clerk on the basis of 10%.
          UPDATE EMPLOYEE SET SALARY=(SALARY+SALARY*10/100)
WHERE JOB_ID IN(SELECT JOB_ID FROM JOB
                WHERE FUNCTIONALITY='CLERK')
  1. Delete the employees who are working in accounting department.
          DELETE FROM EMPLOYEE
WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM DEPARTMENT
                       WHERE NAME='ACCOUNTING')
  1. Display the second highest salary drawing employee details.
          SELECT * FROM EMPLOYEE
WHERE SALARY IN(SELECT MAX(SALARY) FROM EMPLOYEE
                WHERE SALARY <(SELECT MAX(SALARY) FROM EMPLOYEE))
  1. Display the Nth highest salary drawing employee details
          SELECT A.SALARY FROM EMPLOYEE A
WHERE N-1=(SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
             WHERE A.SALARY<B.SALARY)
2ND,3RD AND 5TH MAX SALARYS
SELECT A.SALARY FROM EMPLOYEE A
WHERE (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
             WHERE A.SALARY<B.SALARY)IN(1,2,4)

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

  1. List out the employees who earn more than every employee in department 30.
          SELECT * FROM EMPLOYEE
WHERE SALARY>ALL(SELECT SALARY FROM EMPLOYEE
                 WHERE DEPARTMENT_ID=30)
(OR)
SELECT * FROM EMPLOYEE
WHERE SALARY>ALL(SELECT MAX(SALARY) FROM EMPLOYEE
                 WHERE DEPARTMENT_ID=30)
  1. List out the employees who earn more than the lowest salary in department 30.
 SELECT * FROM EMPLOYEE
WHERE SALARY>ANY(SELECT MIN(SALARY) FROM EMPLOYEE
                 WHERE DEPARTMENT_ID=30)
  1. Find out which department does not have any employees.
          SELECT DEPARTMENT_ID,NAME FROM DEPARTMENT
WHERE DEPARTMENT_ID NOT IN(SELECT DEPARTMENT_ID FROM EMPLOYEE)



Co-Related Sub Queries:

46. Find out the employees who earn greater than the average salary for their department.

SELECT * FROM EMPLOYEE E
WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEE
                    WHERE DEPARTMENT_ID=E.DEPARTMENT_ID
              GROUP BY DEPARTMENT_ID)

Joins

Simple join


48.List our employees with their department names
           SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,NAME
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
49.Display employees with their designations (jobs)
          SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,FUNCTIONALITY
FROM EMPLOYEE E
INNER JOIN JOB J
ON E.JOB_ID=J.JOB_ID
     50.Display the employees with their department name and regional groups.
          SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,NAME,REGIONAL_GROUP
FROM EMPLOYEE E,DEPARTMENT D,LOCATION L
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
            AND
            D.LOCATION_ID=L.LOCATION_ID
                        (OR)

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,E.DEPARTMENT_ID,NAME,REGIONAL_GROUP
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
INNER JOIN LOCATION L ON D.LOCATION_ID=L.LOCATION_ID

51.How many employees who are working in different departments and display with department name.
            SELECT NAME AS "DEPARTMENT NAME",COUNT(*) AS "NUMBER OF EMPLOYEES"
      FROM DEPARTMENT D
      INNER JOIN EMPLOYEE E
      ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
   GROUP BY NAME
52.How many employees who are working in sales department.
           SELECT NAME,COUNT(*) AS "NUMBER OF EMPLOYEES"
      FROM DEPARTMENT D
      INNER JOIN EMPLOYEE E
      ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
      GROUP BY NAME
      HAVING NAME='SALES'

53.Which is the department having greater than or equal to 5 employees and display the department names in ascending order.
          SELECT NAME AS "DEPARTMENT NAME",COUNT(*) AS "NUMBER OF EMPLOYEES"
      FROM DEPARTMENT D
      INNER JOIN EMPLOYEE E
      ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
      GROUP BY NAME
   HAVING COUNT(*)>=5
54.How many jobs in the organization with designations.
      SELECT FUNCTIONALITY,COUNT(*) AS "NO OF JOBS"
      FROM JOB J
      INNER JOIN EMPLOYEE E
      ON E.JOB_ID=J.JOB_ID
   GROUP BY FUNCTIONALITY
55.How many employees working in “New York”.
          SELECT REGIONAL_GROUP,COUNT(*) "NUMBER OF EMPLOYEES"
      FROM EMPLOYEE E
      INNER JOIN DEPARTMENT D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
      INNER JOIN LOCATION L ON D.LOCATION_ID=L.LOCATION_ID
      GROUP BY REGIONAL_GROUP
      HAVING REGIONAL_GROUP='DALLAS'
      (OR)
      SELECT REGIONAL_GROUP,COUNT(*) "NUMBER OF EMPLOYEES"
      FROM EMPLOYEE E, DEPARTMENT D, LOCATION L
      WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
      AND D.LOCATION_ID=L.LOCATION_ID
      AND L.REGIONAL_GROUP='DALLAS'
      GROUP BY REGIONAL_GROUP
Non – Equi Join:

56.Display employee details with salary grades.
           SELECT LAST_NAME,SALARY,GRADE
      FROM EMPLOYEE E,SALARY_GRADE S
   WHERE SALARY BETWEEN LOWER_BOUND AND UPPER_BOUND
57.List out the no. of employees on grade wise.
          SELECT GRADE,COUNT(*)
      FROM SALARY_GRADE S,EMPLOYEE E
      WHERE SALARY BETWEEN LOWER_BOUND AND UPPER_BOUND
      GROUP BY GRADE
   ORDER BY GRADE DESC
58.Display the employ salary grades and no. of employees between 2000 to 5000 range of salary.
             SELECT GRADE,COUNT(*)
      FROM SALARY_GRADE S,EMPLOYEE E
      WHERE SALARY BETWEEN LOWER_BOUND AND UPPER_BOUND
      AND
      LOWER_BOUND>=2000 AND UPPER_BOUND<=5000
      GROUP BY GRADE
   ORDER BY GRADE

Self Join:

59.Display the employee details with their manager names.
          SELECT      E.EMPLOYEE_ID "EMPLOYEE ID",
                  E.LAST_NAME "EMPLOYEE NAME",
                  M.LAST_NAME "MANAGER NAME"
      FROM       EMPLOYEE E,EMPLOYEE M
      WHERE       M.EMPLOYEE_ID=E.MANAGER_ID
      (OR)
      SELECT      E.EMPLOYEE_ID "EMPLOYEE ID",
                  E.LAST_NAME "EMPLOYEE NAME",
                  M.LAST_NAME "MANAGER NAME"
      FROM        EMPLOYEE E
                  INNER JOIN EMPLOYEE M
   ON          E.MANAGER_ID=M.EMPLOYEE_ID
      
60.Display the employee details who earn more than their managers salaries.

          SELECT      E.LAST_NAME "EMPLOYEE NAME",
                  E.SALARY "EMPLOYEE SALARY",
                  M.LAST_NAME "MANAGER NAME",
                  M.SALARY "MANAGER SALARY"
      FROM        EMPLOYEE E,EMPLOYEE M
      WHERE       M.EMPLOYEE_ID=E.MANAGER_ID
                  AND
                  E.SALARY>M.SALARY
      (OR)
      SELECT      E.LAST_NAME "EMPLOYEE NAME",
                  E.SALARY "EMPLOYEE SALARY",
                  M.LAST_NAME "MANAGER NAME",
                  M.SALARY "MANAGER SALARY"
      FROM        EMPLOYEE E
                  INNER JOIN EMPLOYEE M
      ON          E.MANAGER_ID=M.EMPLOYEE_ID
                  AND
               E.SALARY>M.SALARY
61.Show the no. of employees working under every manager.
           SELECT M.LAST_NAME,COUNT(*) "NUMBER OF EMPLOYEES"
      FROM EMPLOYEE E,EMPLOYEE M
      WHERE E.MANAGER_ID=M.EMPLOYEE_ID
      GROUP BY M.LAST_NAME
      (OR)
      SELECT M.LAST_NAME,COUNT(*) "NUMBER OF EMPLOYEES"
      FROM EMPLOYEE E
      INNER JOIN EMPLOYEE M
      ON M.EMPLOYEE_ID=E.MANAGER_ID
      GROUP BY M.LAST_NAME
     
(ABOVE TWO SOLUTIONS ARE BASED ON MANAGER NAME)


(BELOW SOLUTIONS IS BASED ON MANAGER ID)
      SELECT M.MANAGER_ID,COUNT(*) "NUMBER OF EMPLOYEES"
      FROM EMPLOYEE E
      INNER JOIN EMPLOYEE M
      ON M.EMPLOYEE_ID=E.MANAGER_ID
      GROUP BY M.MANAGER_ID
Outer Join:
61.Display employee details with all departments.
          SELECT EMPLOYEE_ID,LAST_NAME,HIREDATE,SALARY,E.DEPARTMENT_ID,NAME
      FROM EMPLOYEE E
      LEFT OUTER JOIN DEPARTMENT D
   ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
62.Display all employees in sales or operation departments.
          SELECT EMPLOYEE_ID,LAST_NAME,E.DEPARTMENT_ID,NAME
      FROM EMPLOYEE E
      LEFT OUTER JOIN DEPARTMENT D
      ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
      AND D.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENT
                              WHERE NAME IN('SALES','OPERATIONS'))
Set Operators:

63.List out the distinct jobs in Sales and Accounting Departments.
          SELECT FUNCTIONALITY
      FROM JOB WHERE JOB_ID IN(SELECT JOB_ID FROM EMPLOYEE
      WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENT                                          WHERE NAME='SALES'))
      UNION
      SELECT FUNCTIONALITY
      FROM JOB WHERE JOB_ID IN(SELECT JOB_ID FROM EMPLOYEE
      WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENT                                          WHERE NAME='ACCOUNTING'))
64.List out the ALL jobs in Sales and Accounting Departments.
          SELECT FUNCTIONALITY
      FROM JOB WHERE JOB_ID IN(SELECT JOB_ID FROM EMPLOYEE
      WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENT                                          WHERE NAME='SALES'))
      UNION ALL
      SELECT FUNCTIONALITY
      FROM JOB WHERE JOB_ID IN(SELECT JOB_ID FROM EMPLOYEE
      WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENT                                          WHERE NAME='ACCOUNTING'))
65.List out the common jobs in Research and Accounting Departments in ascending order.
      SELECT FUNCTIONALITY
      FROM JOB WHERE JOB_ID IN(SELECT JOB_ID FROM EMPLOYEE
      WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENT                                          WHERE NAME='SALES'))
      INTERSECT
      SELECT FUNCTIONALITY
      FROM JOB WHERE JOB_ID IN(SELECT JOB_ID FROM EMPLOYEE
      WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM DEPARTMENT                                          WHERE NAME='ACCOUNTING'))
----------------------------------------------------------------------------

  1. Starting 4 rows from a table

In oracle

select * from department where rownum<=3;

In SQL Server

Select top 3* from department

  1. 4th row record value

In Oracle

SELECT * FROM DEPARTMENT WHERE ROWNUM <=4
MINUS
SELECT * FROM DEPARTMENT WHERE ROWNUM <4 nbsp="">

(OR)

SELECT * FROM (SELECT ROWNUM R,E.* FROM DEPARTMENT E)
 WHERE R=3;

In SQL Server

SELECT TOP 3* FROM DEPARTMENT
EXCEPT
SELECT TOP 2* FROM DEPARTMENT

(OR)

WITH NthRowCTE AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY department_id) AS RNum
        , *
    FROM department
)
SELECT * FROM NthRowCTE WHERE RNum = 4

Tutorials on SAP-ABAP

Adobe Interactive Forms Tutorials

Business Server Pages (BSP)

Userexits/BADIs

Web Dynpro for ABAP (Step by step procedure for web dynpro,Tutorials on Web Dynpro,)

ALV Tutorials

goodsites