SQL Query Set

                                       
Create a database called COMPANY consisting of two tables-EMPLOYEES & DEPARTMENTS.
ANS:
      SQL> Create table departments
               2  (deptno  number(05) primary key,
               3  dname  varchar(10),
               4   loc varchar(10));
              Table created.
      SQL> Create table employees
               2  (empno  number(05) primary key,
               3  ename  varchar(20),
               4  job char(10),
               5  mgr number(10),
               6  hiredate  date,
               7  sal  number(10),
               8  comm   number(05),
               9  deptno  number(05) ,
              10  CONSTRAINT fk_p_ref1 FOREIGN KEY (deptno) REFERENCES departments(deptno));
           Table created.
Insert record in the tables DEPARTMENTS & EMPLOYEES.
ANS: 1) Insert the record in departments table 
               SQL> Insert into departments values(10,’Accounting’,’New York’);
               1 row created.
               SQL> Insert into departments values(20,’Research’,’Dallas’);
                1 row created.
              SQL> Insert into departments values(30,’Sales’,’Chicago’);
               1 row created.
              SQL> Insert into departments values(40,’Operations’,’Boston’);
               1 row created.
              
 SQL> select * from departments;
        DEPTNO      DNAME              LOC
        ----------       ----------             ----------
        10                Accounting       New York
        20               Research            Dallas
        30               Sales                   Chicago
        40               Operations         Boston
                   
           2.  insert  record into the employees table
SQL>   insert into employees  values(7369,'Smith','Clerk',7902,'17-dec-1980',800,0,20);
1 row created.
SQL>    insert into employees  values(7499,'Allen','Salesman',7698,'20-feb-1981',1600,300,30);
1 row created.
SQL>    insert into employees  values(7521,'Ward','Salesman',7698,'22-feb-1981',1250,500,30);
1 row created.
SQL>   insert into employees  values(7566,'Jones','Manager',7839,'2-apr-1981',2975,0,20);
1 row created.
SQL>   insert into employees  values(7698,'Blake','Manager',7839,'1-may-1981',2850,0,30);
1 row created.
SQL>   insert into employees  values(7782,'Clark','Manager',7839,'9-jun-1981',2450,0,10);
1 row created.
SQL>   insert into employees  values(7789,'Scott','Analyst',7566,'9-dec-1982',3000,0,20);
1 row created.
SQL>   insert into employees  values(7839,'King','President',0,'17-nov-1981',5000,0,10);
1 row created.
SQL>  insert into employees  values(7876,'Adams','Clerk',7788,'12-jan-1983',1100,0,20);
1 row created.
SQL>   insert into employees  values(7902,'Ford','Analyst',7566,'4-dec-1981',3000,0,20);
1 row created.
      SQL>  select * from employees;
     EMPNO  ENAME             JOB             MGR            HIREDATE        SAL   COMM     DEPTNO
     -------      ---------              ----------        -------------    -----------------   --------- -----------  ------------
      7369       Smith                Clerk            7902             17-DEC-80        800         0               20
      7499       Allen                Salesman      7698             20-FEB-81       1600      300              30
      7521      Ward                Salesman       7698             22-FEB-81       1250      500              30
      7566      Jones                Manager        7839             02-APR-81      2975        0                 20
      7698      Blake                Manager        7839             01-MAY-81     2850       0                 30
      7782      Clark                Manager        7839             09-JUN-81       2450       0                 10
      7789      Scott                Analyst           7566             09-DEC-82       3000      0                 20
      7839      King                 President           0                17-NOV-81      5000      0                 10
      7876      Adams             Clerk               7788             12-JAN-83       1100      0                 20
      7902      Ford                 Analyst           7566             04-DEC-81      3000       0                 20
10 rows selected. 
                                       
QUERY1: List the names of analysts and salesman.
SQL>  select * from employees where job='Analyst' OR  job='Salesman';
 EMPNO      ENAME             JOB             MGR     HIREDATE      SAL     COMM     DEPTNO
----------       ----------           -----------         --------    ---------------     ------     ----------   --------------
    7499       Allen                Salesman         7698      20-FEB-81       1600      300            30
    7521       Ward                 Salesman        7698      22-FEB-81       1250      500            30
    7789       Scott                Analyst             7566      09-DEC-82      3000         0             20
   7902        Ford                 Analyst             7566      04-DEC-81      3000         0             20

QUERY2: List details  of employees who have joined before 30 sep 81.
SQL> select * from employees where hiredate<'30-sep-1981';
     EMPNO ENAME                JOB            MGR    HIREDATE      SAL   COMM  DEPTNO
     ---------- -------------------- ----------       ---------- -------------    ---------- ----------   ----------
      7369      Smith                 Clerk            7902       17-DEC-80     800         0           20
      7499      Allen                  Salesman     7698       20-FEB-81     1600      300        30
      7521      Ward                  Salesman     7698       22-FEB-81     1250      500        30
      7566      Jones                  Manager      7839       02-APR-81     2975        0          20
      7698      Blake                  Manager      7839      01-MAY-81    2850       0           30
      7782      Clark                  Manager      7839       09-JUN-81      2450       0           10
   6 rows selected.

QUERY3: List name of employees who are not managers.
 SQL> Select  ename from employees where  job!=’Manager’;
ENAME
--------------------
Smith
Allen
Ward
King
Adams
Ford
Scott
7 rows selected.

QUERY4:List the name of employees whose employee numbers are 7369,7521,7839,7934,7788.
SQL> Select ename from employees where  empno=7369  OR empno=7521  OR empno=7839  OR empno=7788 OR empno=7934;
ENAME
--------------------
Smith
Ward
King

QUERY5:List employees not belonging to department 30,40,or 10.
METHOD-1:
SQL>  (Select  ename  from employees  where  deptno=20)
  2      MINUS
  3      (Select  ename  from employees  where  deptno=30 or deptno=10);
ENAME
--------------------
Adams
Ford
Jones
Scott
Smith
METHOD-2:
SQL>  Select  ename  from employees  where  deptno!=30 AND deptno!=10 AND deptno!=40;
ENAME
--------------------
Smith
Jones
Scott
Adams
Ford

QUERY6:List employees names for those who have joined between 30 june and 31 dec 81.
SQL> select ename  from  employees  where hiredate >='30-jun-1981'  AND  hiredate<='31-dec-1981';
ENAME
--------------------
King
Ford

QUERY7:List the different designations in the company.
SQL> select job from employees group by job;
 JOB
----------
Manager
Salesman
Clerk
President
Analyst

QUERY8: List name of employees who are not eligible for commission.
SQL> Select  ename   from  employees  where comm=0;
ENAME
--------------------
Smith
Jones
Blake
Clark
Scott
King
Adams
Ford
8 rows selected.

QUERY9: List the name  and  designation  of  the  employee who does not report to anybody.
SQL> Select  ename ,job  from  employees  where comm!=0;
ENAME                JOB
-------------------- ----------
Allen                Salesman
Ward               Salesman

QUERY10: List the employees not assigned to any department.
SQL> select ename from employees E,departments D where E.deptno=D.deptno AND E.deptno!=10 AND E.deptno!=20 AND E.deptno!=30;
no rows selected

QUERY11:  List the employees who are eligible for commision. 
SQL> Select  ename from  employees  where comm!=0;
ENAME
--------------------
Allen
Ward


QUERY12: List employee’s whose names either start or end with “S”.
SQL> select ename from employees where ename like'S%' OR ename like'%s';
ENAME
--------------------
Smith
Jones
Scott
Adams

QUERY13: List name of employees whose names have “i” as the second character.
SQL> select ename from employees where ename like '_i%';
ENAME
--------------------
King

QUERY14: List the number of employees working with the company.
SQL> select count(empno) from employees;
COUNT(EMPNO)
------------
          10

QUERY15: List the number of designations available in the EMP table.
SQL> select count(job),job  from employees group by job;
COUNT(JOB)   JOB
----------          ----------
         3           Manager
         2           Salesman
         2           Clerk
         1           President
         2           Analyst

QUERY16: List the total salaries paid to employees.
SQL> select sum(sal) from employees;
  SUM(SAL)
  ----------
   24025

QUERY17:List the maximum, minimum and average  salary in the company.
SQL> select  max(sal), min(sal), avg(sal) from employees;
  MAX(SAL)   MIN(SAL)   AVG(SAL)
   ----------       ----------       ----------
      5000         800            2402.5

QUERY18: List the maximum salary paid to a salesman.
METHOD-1:
SQL> select max(sal) from employees where job='Salesman';
  MAX(SAL)
    ----------
      1600
METHOD-2:
SQL> select max(sal) from employees where job='Salesman' group by deptno;
  MAX(SAL)
    ----------
      1600

QUERY19: List the number of employees and average salary for employees in department 20. 
SQL> select count(empno),avg(sal) from employees where deptno=20;
COUNT(EMPNO)               AVG(SAL)
----------------------                --------------
           5                                 2175

QUERY20: List name, salary and PF amount of all employees. (PF is calculated as 10% of basic salary)
SQL> Select ename,sal,sal*0.1 PF from employees;
ENAME                  AL         PF
-------------------    ----------  ----------
Smith                       800         80
Allen                      1600        160
Ward                      1250        125
Jones                      2975      297.5
Blake                      2850        285
Clark                      2450        245
Scott                      3000        300
King                      5000        500
Adams                   1100        110
Ford                       3000        300
10 rows selected.

QUERY21: List the employees details in the descending order of their basic salary.
SQL> Select * from employees ORDER BY sal desc;
     EMPNO  ENAME           JOB            MGR        HIREDATE       SAL   COMM     DEPTNO
     -------      ---------           ----------    -------------    -----------------   --------- -----------  ------------  
     7839       King               President         0             17-NOV-81      5000        0              10   
     7789       Scott               Analyst        7566          09-DEC-82       3000        0              20
     7902       Ford                Analyst        7566          04-DEC-81       3000        0              20
     7566       Jones               Manager      7839          02-APR-81       2975        0              20
     7698       Blake               Manager      7839          01-MAY-81     2850        0              30  
     7782       Clark               Manager       7839         09-JUN-81       2450         0             10
     7499       Allen               Salesman     7698          20-FEB-81       1600     300             30
     7521       Ward               Salesman     7698          22-FEB-81       1250     500             30
     7876       Adams             Clerk           7788          12-JAN-83       1100        0              20
     7369       Smith               Clerk           7902          17-DEC-80        800        0              20
10 rows selected. 

QUERY22: List the employee name and hire date in the descending order of the hiredate.
SQL> select ename, hiredate from employees order by hiredate desc;
ENAME                HIREDATE
--------------------    ---------
Adams                   12-JAN-83
Scott                      09-DEC-82
Ford                      04-DEC-81
King                      17-NOV-81
Clark                     09-JUN-81
Blake                    01-MAY-81
Jones                     02-APR-81
Ward                     22-FEB-81
Allen                     20-FEB-81
Smith                    17-DEC-80
10 rows selected.

QUERY23: List the employees name ,salary,PF,HRA,DA and gross;order the results in the ascending order of gross .(HRA is 50% of the salary and DA is 30% of the salary.
SQL> Select ename,sal,sal*0.1 PF,sal*0.5 HRA,sal*0.3 DA,sal+sal*0.1+sal*0.5+sal*0.3 "GROSS" from employees order by GROSS;
ENAME                SAL         PF        HRA         DA      GROSS
--------------------  ---------- ---------- ---------- ---------- ----------
Smith                      800         80        400        240         1520
Adams                  1100        110        550        330         2090
Ward                    1250        125        625        375         2375
Allen                    1600        160        800        480         3040
Clark                    2450        245       1225        735        4655
Blake                    2850        285       1425        855        5415
Jones                    2975      297.5     1487.5      892.5      5652.5
Scott                     3000        300       1500        900         5700
Ford                     3000        300       1500        900         5700
King                     5000        500       2500       1500        9500
                 10 rows selected.

QUERY24: List the department numbers  and number of employees in each department.
SQL> select count(empno),deptno from employees group by(deptno);
COUNT(EMPNO)     DEPTNO
------------                  ----------
           3                      30
           5                      20
           2                      10

QUERY25: List the department number and total salary payable in each department. 
SQL> select deptno,sum(sal) from employees group by(deptno);
    DEPTNO    SUM(SAL)
    ----------       ----------
        30            5700
        20            10875
        10            7450

QUERY26: List the jobs and number of employees in each job. The result should be in the descending order of number of employees. 
SQL> select job, empCnt from (select job,count(empno) empCnt from employees group by(job)) order by empCnt desc;
JOB                EMPCNT
----------         ----------
Manager            3
Analyst              2
Salesman           2
Clerk                  2
President            1

QUERY27: List the total salary,maximum and minimum salary and average salary of the employees job wise.
SQL> select job,sum(sal),max(sal),min(sal),avg(sal) from employees group by(job);
JOB            SUM(SAL) MAX(SAL) MIN(SAL)   AVG(SAL)
----------      ----------       ----------      ----------        ----------
Manager        8275         2975            2450            2758.33333
Salesman       2850         1600            1250            1425
Clerk             1900         1100              800            950
President       5000         5000            5000            5000
Analyst          6000        3000            3000            3000


QUERY28: List the total salary, maximum and minimum salary and average salary of the employees, for department 20.
SQL> select sum(sal),min(sal),max(sal),avg(sal) from employees where deptno=20;
  SUM(SAL)   MIN(SAL)   MAX(SAL)   AVG(SAL)
    ----------      ----------       ----------          ----------
     10875        800               3000               2175

QUERY29: List the total salary, maximum and minimum salary and average salary of the employees  job wise, for department 20 and display only those rows having an average salary>1000. 
SQL> select sum(sal),max(sal),min(sal),avg(sal) from employees where  deptno=20  group by(job) having avg(sal)>1000;
  SUM(SAL)   MAX(SAL)   MIN(SAL) AVG(SAL)
  ----------        ----------       ----------        ----------
      2975         2975            2975              2975
      6000         3000            3000              3000