SQL / Queries

Table : WORKER

Table : PAYLEVEL

a)To display the details of all workers in descending order of dob.
    ANS: select * from worker order by dob desc;

b)To display name and designation of those workers, whose plevel is either P001 or P002.
    ANS: select name,desig from worker where plevel in ('P001','P002');

c)To display the content of all the workers, whose dob is in between 19-01-1984 and 18-01-1987.
    ANS: select * from worker where dob between '19-JAN-84' and '18-JAN-87';

d)To add a new row with the following details :
   ECODE = 19
   NAME = Daya Kishore
   DESIGNATION = OPERATOR
   PLEVEL = P003
   DATE OF JOINING = 19-06-2008
   DATE OF BIRTH = 11-06-1984

    ANS: insert into worker values (19,'Daya Kishore','Operator','P003','19-JUN-08','11-JUN-84');

e)To display name and pay of workers whose ecode is less than 13
    ANS: select name,pay from worker w,paylevel p where w.plevel=p.plevel amd w.ecode<13;

f)To display the name and total payment of all workers.

    ANS: select name,pay+allowance as payment from worker w,paylevel p where w.plevel=p.plevel;

SQL / Queries

Table : EMP

Table : DEPT

a)List all employees' details whose name ends with S.
      ANS: select * from emp where ename like '%S';

b)List all Employee details whose comm is greater than 300 and salary is greater than 1200.
      ANS: select * from emp where comm > 300 and sal >1200;

c)Select all the employee who works in Boston.    
      ANS: select * from emp e,dept d where e.deptno=d.deptno and d.dname='BOSTON';

d)Update all empolyee salary by 15% who work in dept no. 30.
    ANS: update emp set sal = sal + sal*0.15 where deptno=30;

e)Enter a new employee detail, which as follows
    EMP NO = 7940
    ENAME = RITIK
    JOB = MANAGER
    MGR - 7566
    HIRE DATE = 1998-01-28
    SAL = 1225
    COMM = 100
    DEPT NO = 10

    ANS: insert into emp values(7940,'RITIK','MANAGER',7566,'28-JAN-98',1225,100,10);