Thursday 29 March 2012

Oracle EMP,DEPT tables with data


CREATE TABLE EMP
    (
        EMPNO        INT            NOT NULL    ,
        ENAME        VARCHAR(10)                ,
        JOB            VARCHAR(9)                ,
        MGR            INT                        ,
        SAL            NUMERIC(7,2)                ,
        DEPTNO        INT
    );
   
CREATE TABLE DEPT
        (
        DEPTNO    INT,
        DNAME    VARCHAR(14),
        LOC        VARCHAR(13)
        )
   

CREATE TABLE BONUS
(
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    SAL NUMERIC(8,2),
    COMM NUMERIC(8,2)
)

CREATE TABLE SALGRADE
(
    GRADE INT,
    LOSAL NUMERIC(8,2),
    HISAL NUMERIC(8,2)
)


        INSERT INTO emp VALUES
            (7369, 'SMITH', 'CLERK', 7902, 800, 20);
        INSERT INTO EMP VALUES
            (7499, 'ALLEN', 'SALESMAN', 7698, 1600, 30);
        INSERT INTO EMP VALUES
            (7521, 'WARD', 'SALESMAN', 7698, 1250, 30);
        INSERT INTO EMP VALUES
            (7566, 'JONES', 'MANAGER', 7839, 2975, 20);
        INSERT INTO EMP VALUES
            (7654, 'MARTIN', 'SALESMAN', 7698, 1250, 30);
        INSERT INTO EMP VALUES
            (7698, 'BLAKE', 'MANAGER', 7839, 2850, 30);
        INSERT INTO EMP VALUES
            (7782, 'CLARK', 'MANAGER', 7839, 2450, 10);
        INSERT INTO EMP VALUES
            (7788, 'SCOTT', 'ANALYST', 7566, 3000, 20);
        INSERT INTO EMP VALUES
            (7839, 'KING', 'PRESIDENT', NULL, 5000, 10);
        INSERT INTO EMP VALUES
            (7844, 'TURNER', 'SALESMAN', 7698, 1500, 30);
        INSERT INTO EMP VALUES
            (7876, 'ADAMS', 'CLERK', 7788, 1100, 20);
        INSERT INTO EMP VALUES
            (7900, 'JAMES', 'CLERK', 7698, 950, 30);
        INSERT INTO EMP VALUES
            (7902, 'FORD', 'ANALYST', 7566, 3000, 60);
        INSERT INTO EMP VALUES
            (7934, 'MILLER', 'CLERK', 7782, 1300, 10);
           




        INSERT INTO DEPT
            VALUES (10, 'ACCOUNTING', 'NEW YORK')
        INSERT INTO DEPT
            VALUES (20, 'RESEARCH', 'DALLAS')
        INSERT INTO DEPT
            VALUES (30, 'SALES', 'CHICAGO')
        INSERT INTO DEPT
            VALUES (40, 'OPERATIONS', 'BOSTON')




        INSERT INTO SALGRADE
                VALUES (1, 700,  1200)
        INSERT INTO SALGRADE
                VALUES (2, 1201, 1400)
        INSERT INTO SALGRADE
                VALUES (3, 1401, 2000)
        INSERT INTO SALGRADE
                VALUES (4, 2001, 3000)
        INSERT INTO SALGRADE
                VALUES (5, 3001, 9999)




1.List out all details of employees
2.List out all details of employees who earn more than 2000
3.List out employee names and their manager ids of all employees
4.List out all the employee ids and their department nos who are working in department 20
5.List out all details of ALLEN
6.List out all details of MARTIN and LUTHER
List out all employee details who"s name starts with S
List out employee details who"s name ends with G
List out employee details who"s name contains LL
List out employee details who"s name starts with A and ends with N
7.List out department 10 and depatment 20 employee details
8.List out all the employees who work in SCOTT"s department
9.List out all details of employees who earn more than MARTIN
10.Details of Employees Under KING"S manager
11.List out all details of employees and theire managers
12.List out all the employee details who earn more than his manager
13.List out departments and their employee count
14.List out each departments avg salary
15.List out Designation wise employee count
16.List out managers and total employees under each manager
17.List out departments and their maximum salaries
18.List out department details which is giving the maximum salary
19.List out the department details where there are more employees than all
20.List out department details where there are no employees in it
21.List out all employees and thier salgrades
22.List out Grade wise total employees


6 comments:

  1. 8.List out all the employees who work in SCOTT"s department

    what is the answer for the above question?
    is it by using multiple sql queries or using plsql block;

    ReplyDelete
  2. 8.List out all the employees who work in SCOTT"s department

    what is the answer for the above question?
    is it by using multiple sql queries or using plsql block;

    ReplyDelete
    Replies
    1. select * from EMP where DEPTNO=(select DEPTNO from EMP where ENAME='SCOTT');

      Delete
  3. Nice you guys are checking out my blog. If you have any questions on SQL & TSQL you can post over here so that I can respond.

    ReplyDelete
  4. THank you Very much for your interest.

    ReplyDelete