Some Important Query For Beginners PART (1)

1.Details of all Employee ?          

SELECT  * FROM Emp;

2.Department Information from Departmet Table ?

 SELECT * FROM Dept;

3.Name and Job of all Employee ?

SELECT  EName,Job FROM Emp;

4.Name and Salary of all Employee ?

SELECT EName,Sal FROM Emp;

5.Employee number and Total Salary of all Employee ?

SELECT Empno,Sal+NVL(Comm,0) TotalSalary FROM Emp;

6.Employee Name and Annual salary of all Employee ?

SELECT EName,12*Sal  AnnualSalary FROM Emp;

7.Employee name who are working in department 10 ?

SELECT EName FROM Emp

WHERE Deptno=10;

8.Empoyee name who are working ac clerk and earning  more than 3000 ?

SELECT EName

FROM Emp

WHERE UPPER(Job)=UPPER(‘CLERK’) AND  Sal >3000;

9.Employee number who are earning commission ?

SELECT Empno

FROM Emp

WHERE Comm IS NOT NULL;

 

10.Employee number who are not earning commission ?

SELECT Empno

FROM Emp

WHERE Comm NOT NULL;

 

11.Employee names who are working as clerk,salesman or analyst and earning more than 3000?

SELECT EName

FROM Emp

WHERE UPPER(Job) IN   (‘CLERK’,’SALESMAN’,’ANALYST’)

                AND  Sal >3000;

12.Employee name who are working for last 5 year ?

SELECT EName

FROM emp

WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HireDate)/12) > 5

13.Employee name who are joined before 30-jun-90 or after 31-dec-90 ?

SELECT EName ,HireDate

FROM Emp

WHERE HireDate NOT BETWEEN '30-jun-90' AND  '31-dec-90';

14.Current Date ?

SELECT SYSDATE FROM DUAL;

15.List of all user in your database ?

SELECT DISTINCT Creator FROM Catalog;

16.Name of all table of current user ?

SELECT TName

FROM Catalog

WHERE Creator= (SELECT USER  FROM DUAL ) ;

17.Current User Name ?

SELECT USER FORM DUAL;

18.Employee name who are working for Department 10,20 or 40 or working as clerk,salesman,analyse ?

SELECT EName

FROM Emp

WHERE Deptno IN (10 ,20 ,40 )

   OR

            Job IN  (  'CLERK' ,'SALESMAN' ,'ANALYST')

19.Employee name whose name starts with s ?

SELECT EName

FROM Emp

 WHERE Ename LIKE('S%');

20. Employee name whose name ends with s ?

SELECT EName

FROM Emp

 WHERE Ename LIKE('%S');

21.  Employee name whose name whose second  alphabet is a  in their name ?

SELECT EName

FROM Emp

 WHERE Ename LIKE('_A%');

22.Employee Name exactly five character in length ?

SELECT EName

FROM Emp

WHERE LENGTH(Ename)=5

23.Employees who are not workin as manager ?

SELECT Ename,Job

FROM Emp

 WHERE Job <> 'MANAGER';

24.Employees who are not workin as Clerk,analyst,salesman ?

SELECT Ename,Job

FROM Emp

WHERE Job NOT IN ( 'CLERK', 'SALESMAN' ,'ANALYST')

25.Pause the screen after page full of information ?

26.Total no. of Employee ?

SELECT COUNT(*) TotalEmployee FROM Emp

27.Total salary paid to all employees ?

SELECT SUM(sal ) TotalSalary FROM Emp;

28.Maximum  salary  from employee table ?

SELECT MAX(Sal) MaxSalary FROM Emp;

29.Minimum  salary  from employee table ?

SELECT MIN(Sal) MinSalary  FROM Emp;

30.Average salary  from employee table ?

SELECT ROUND(AVG(Sal),2) AvgSalary FROM Emp;

31.Maximum salary paid to clerk ?

SELECT MAX(Sal) MaxSalary

 FROM Emp

WHERE Job=’CLERK’;

32. Maximum salary paid to Department 20?

SELECT MAX(Sal)  MaxSalary 

FROM Emp

WHERE Deptno=20;

33. Minimum salary paid to Salesman?

SELECT MAX(Sal)  MaxSalary 

FROM Emp

WHERE Job=’SALESMAN’;

34. Avrage salary drawn by manager ?

SELECT ROUND(AVG(Sal),2)  AvgSalary 

FROM Emp

WHERE Job=’MANAGER’;

35.Total Salary drawn by analyst working under department 40 ?

SELECT SUM(Sal) TotalSalary 

FROM Emp

WHERE Job=’ANALYST’ AND Dptno=40;

36.Employee names inorder of salary

SELECT EName

FROM Emp

ORDER BY Sal;

37.Lowest earning emploue name first ?

SELECT EName

FROM Emp

ORDER BY Sal;

38.Employee name in decending order of salary

SELECT EName

FROM Emp

ORDER BY Sal  DESC;

39.Employee name in order of employee name ?

SELECT EName

FROM Emp

ORDER BY EName;

40.Empno ,Ename,Deptno,salary in order of name ?

SELECT Empno,EName,Deptno,Sal

FROM Emp

ORDER BY EName ;

41.Empno ,Ename,Deptno,salary in order of name ,then Deptno then Salary ?

SELECT Empno,EName,Deptno,Sal

FROM Emp

ORDER BY EName,Deptno,Sal ;

42.Employee neme with annual salary based on highest annual salary ?

SELECT EName,Sal*12 AnnualSalary 

FROM Emp

ORDER BY AnnualSalary  DESC

43.Employee name,salary,hra,pf,da,total where hra=15% ,pf=10% and da=5% of salary ?

SELECT EName,TO_CHAR(Sal,'9G999D99') Basic

   ,TO_CHAR(Sal*.15,'9G999D99') Hra

   ,TO_CHAR(Sal*.1,'9G999D99') PF

   ,TO_CHAR(Sal*.05,'9G999D99') DA

   ,TO_CHAR(((Sal +Sal*.15 +Sal*.05 ) -Sal*.1),'9G999D99') TotalSalary

FROM Emp

44.Department no along with no of employe in each department ?

SELECT Deptno,COUNT(*) NO_OF_Employees

FROM Emp

GROUP BY Deptno

45.No of employee in job wise ?

SELECT Job,COUNT(*) NO_OF_Employees

FROM Emp

GROUP BY Job

46.Department no and total salary of each department ?

SELECT Deptno,SUM(Sal)  DeptTotalSal

FROM Emp

GROUP BY Deptno

47.Department no with maximum salary in each department ?

SELECT Deptno,MAX(Sal)  DeptMaxSal

FROM Emp

GROUP BY Deptno

48.Job wise Total Salary ?

SELECT Job,SUM(Sal)  JobTotalSal

FROM Emp

GROUP BY Job

49.Department no with more than 3 employee ?

SELECT Deptno,COUNT(*) NO_OF_Employees

FROM Emp

GROUP BY Deptno

HAVING COUNT(*) > 3

50.Jobs alongwith total salary  where total salary greater than 4000?

SELECT Job,SUM(Sal)  JobTotalSal

FROM Emp

GROUP BY Job

HAVING SUM(Sal) > 4000

51.Jobs more than 3 employee job in that job ?

SELECT Job,COUNT(*) NO_OF_Employees

FROM Emp

GROUP BY Job

HAVING COUNT(*) > 3;

52.Employees earning highest salary?

SELECT EName

FROM Emp

WHERE Sal = ( SELECT MAX(sal)

                                    FROM Emp)

53.Employee no. name who works as clerk and earning highest salary among them ?

SELECT EName ,Sal

FROM Emp

WHERE Sal IN ( SELECT MAX(sal)

                                    FROM Emp

                                    WHERE Job='CLERK')

    AND  Job=' CLERK '

54.Salesman who earns more than any of the highest earning clerk ?

SELECT EName

FROM Emp

WHERE Sal >  ANY ( SELECT MAX(sal)

                                                FROM Emp

                                                WHERE Job='CLERK' )

               AND  Job='SALESMAN'

55.clerks who earns higher than any low paid salesman ?

SELECT EName

FROM Emp

WHERE Sal >  ANY ( SELECT MIN(sal)

                                    FROM Emp

                                    WHERE Job='SALESMAN' )

                 AND  Job='CLERK'

56.Employees earns more than Jones or Scott ?

SELECT EName

FROM Emp

WHER E Sal >  ANY ( SELECT sal

                                         FROM Emp

                                         WHERE Ename IN ( 'JONES' , 'SCOTT' )

                                       )

              AND Ename NOT IN ( 'JONES' , 'SCOTT' )

57.Employees who earns highest salary in their respective  department ?

SELECT EName

FROM Emp

WHERE Sal IN ( SELECT MAX(sal)

                                         FROM Emp

                                         GROUP BY Deptno)

58. Employees who earns highest salary in their respective job?

SELECT EName FROM Emp

 WHERE Sal IN ( SELECT MAX(sal)

                             FROM Emp

                        GROUP BY Job )

59.Employees working for Accounting department ?

SELECT EName,Deptno

FROM Emp

WHERE Deptno = ( SELECT Deptno

                                    FROM Dept

                                    WHERE DName='ACCOUNTING')

60.Employees working in chicago ?

SELECT EName,Deptno

FROM Emp

WHERE Deptno = ( SELECT Deptno

                                     FROM Dept

                                    WHERE LOC='CHICAGO')

61.Job Groups having salary more than managers ?

SELECT Job,SUM(Sal)

FROM Emp

GROUP BY Job

HAVING  SUM(Sal) > ( SELECT SUM(Sal)

                                                   FROM Emp

                                                   WHERE Job='MANAGER' )

62.Employee earning more than any other department and he is working in department 10 ?

SELECT EName,Deptno,Sal

FROM Emp

WHERE Sal > ANY ( SELECT MAX(Sal)

                               FROM Emp

                              GROUP BY Deptno )

                 AND Deptno=10

63. Employee earning more than all other department and he is working in department 10 ?

SELECT EName,Deptno,Sal

FROM Emp

WHERE Sal > ALL (             SELECT MAX(Sal)

                                    FROM Emp

                                    GROUP BY Deptno

                                    HAVING Deptno <> 10)

            AND Deptno=10

64.Employee name in uppercase ?

SELECT UPPER(EName)  Name

FROM Emp;

65.Employee name in Lowercase ?

SELECT LOWER(EName) Name

 FROM Emp;

66.Employee name in proper case ?

SELECT INITCAP(EName) Name

 FROM Emp;

67.Length of employee name ?

SELECT LENGTH(EName) No_Of_Char

FROM Emp;

68.Lentht of your name ?

SELECT LENGTH(‘&Name’) Name_Length

FROM DUAL;

69.Employee name concat with name ?

SELECT CONCAT(Empno,Ename) Empno_Name

 FROM Emp;

SELECT Empno||Ename Empno_Name

FROM Emp;

70.From “oracle” get output as “ac” ?

SELECT 'oracle' String ,SUBSTR('ORACLE',3,2)  SubString

 FROM DUAL

71.First occurance of ‘a’ in ‘computer maintenance corporation’ ?

SELECT  'computer maintenance corporation'  String

   ,INSTR('computer maintenance corporation','a',1,1)  FoundAt

FROM DUAL

72.Repalce ‘b’ for ‘a’ in ‘allen’ ?

SELECT  'allen'  OldString,REPLACE('allen','a','b')  NewString

FROM DUAL

73.Replace manager by boos in employee ?

SELECT  EName,REPLACE(Job,'MANAGER','Boos')  Designation

FROM Emp

74.Emloyee num,name,Deptno but in case of deptno show its department name ?

SELECT Empno,EName,DECODE(Deptno,10,'ACCOUNTING'

                                                                        ,20,'RESEARCH'

                                                                         ,30,'SALES'

                                                                         ,40,'OPERATIONS'

                                                                                    ,' Invalid  ')

FROM  Emp

75.Dispaly your Age ?

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,'&DOB')/12) Age

FROM DUAL

76.Display your age in year,month,day?

SELECT   TRUNC(E.Months/12) Year

 ,TRUNC(MOD(E.Months,12)) Month

 ,TRUNC((MOD(E.Months,12)-TRUNC(MOD(E.Months,12))) * 30) Days

 , TO_CHAR(SYSDATE,'       HH24      MM       SS')  "     Hours  Minute  Second"

FROM    (SELECT MONTHS_BETWEEN(SYSDATE,'&DOB') Months FROM DUAL) E

77. Display current date as 15th aug Friday nineteen ninety seven ?

SELECT   TO_CHAR(SYSDATE, 'DDTH FMMonth Day YyyySP')      ToDay

 FROM   DUAL;

78.Employee date in full year spell with day name and month name ?

SELECT   EName ,TO_CHAR(HireDate,'DDTH FMMonth Day YyyySP') HiredOn

FROM   Emp;

79. scott’s join date in Q.78 format ?

SELECT   'Mr. ' ||  EName || '  was hiresd on ' || TO_CHAR(HireDate,'DDTH FMMonth Day YyyySP')

FROM   Emp

WHERE Ename='SCOTT'

80. Nearest Saturday from current date ?

SELECT   SYSDATE Today ,NEXT_DAY(SYSDATE,'&DayName')  NextDay

 FROM  DUAL

Here DayName=’sat’

81. Display current time ?

SELECT 'Now Time is ' || TO_CHAR(SYSDATE,'HH:MM:SS PM') || ' in 12 hour format' Time

FROM  DUAL

SELECT 'Now Time is ' || TO_CHAR(SYSDATE,'HH24:MM:SS PM') || ' in 24 hour format' Time

FROM  DUAL

82. Date 3 month before current date ?

SELECT SYSDATE Today ,ADD_MONTHS(SYSDATE,&Month)  NewDate

FROM  DUAL;  

Ans:Enter Month=-3

83.Common jobs from deptno 10 and 20 ?

SELECT DISTINCT Deptno,Job

FROM Emp

WHERE Job  IN (      SELECT  Job

                                     FROM Emp               

                                    WHERE Deptno=10)

             AND Deptno=20

//low  porformance

SELECT Job FROM Emp

WHERE Deptno=10

INTERSECT

SELECT Job FROM Emp

WHERE Deptno=20

84. Eliminate common job in dept 10 and 20 ?

SELECT DISTINCT Job

 FROM Emp

 WHERE Job NOT IN (SELECT DISTINCT Job

                                      FROM Emp

                                     WHERE Job  IN (      SELECT  Job

                                                                                    FROM Emp

                                                                                    WHERE Deptno=10)

                                                AND Deptno=20    )

            AND  Deptno<>30

//low  porformance

(SELECT Job   FROM Emp

  WHERE Deptno=10

 UNION

  SELECT Job   FROM Emp

  WHERE Deptno=20

)

MINUS

(SELECT Job   FROM Emp

  WHERE Deptno=10

INTERSECT

  SELECT Job   FROM Emp

  WHERE Deptno=20)

85. Jobs unique to department ?

//More CPU Cost, Memory Consume, Less IO Cycle

SELECT DISTINCT Job

FROM Emp

WHERE Job IN      (   SELECT DISTINCT Job

                                    FROM Emp

                                    WHERE Job  IN (      SELECT  Job

                                                                                    FROM Emp

                                                                                    WHERE Deptno=10)

                                                AND Deptno=20)

//Less CPU Cost, Memory Consume, More  IO Cycle

SELECT Job   FROM Emp

WHERE Deptno=10

INTERSECT

SELECT Job FROM Emp

WHERE Deptno=20

INTERSECT

SELECT Job FROM Emp

WHERE Deptno=30

86. who  do not have any person working under them ?

SELECT EName

FROM Emp

WHERE   Empno NOT IN ( SELECT DISTINCT Mgr

                                                FROM Emp

                                                WHERE Mgr IS NOT NULL )

87. Employee in sales department and having grade 3 ?

SELECT * FROM Emp

WHERE Deptno= (SELECT Deptno FROM Dept

                                                WHERE DName='SALES')

            AND   Sal BETWEEN (SELECT LoSal FROM SalGrade

                                WHERE Grade=3 )

                                AND  (SELECT HiSal FROM SalGrade

                                                    WHERE Grade=3);

88. Those who are not managers and who are manager to any one ?

SELECT E.EName EmplName ,M.EName MgrName

FROM Emp E,Emp M

WHERE  E.mgr=M.Empno;

89. Manager names?

SELECT EName

FROM Emp

WHERE   Empno IN (          SELECT DISTINCT Mgr

                                                FROM Emp

                                                WHERE Mgr IS NOT NULL );

//More Memory

SELECT DISTINCT M.EName MgrName

FROM Emp E,Emp M

WHERE  E.mgr=M.Empno;

90. who are not managers ?

SELECT EName

FROM Emp

WHERE   Empno NOT IN ( SELECT DISTINCT Mgr

                                                FROM Emp

                                                WHERE Mgr IS NOT NULL );

//More Memory ,IO cycle ,cpu cost

SELECT  E.EName EmpName

FROM Emp E

WHERE  NOT EXISTS ( SELECT  *

                                          FROM Emp M

                                          WHERE   E. Empno=M.Mgr);

91.Employee name contain less than 4 character ?

SELECT EName

FROM Emp

WHERE LENGTH(EName) < &Lngth;

Ans: Length = 4

92.Employees department starts with ‘s’ ?

SELECT EName,Deptno

FROM Emp

WHERE Deptno IN (SELECT Deptno

 FROM Dept

                                    WHERE LOC LIKE(UPPER('&StartChar%')) );

Ans: StartChar = s

93.Employee name ends with ‘k’ ?

SELECT EName,Deptno

FROM Emp

WHERE Ename LIKE(UPPER('%&EndChar'));

Ans: EndChar=k

94.Employee under jones ?

SELECT EName EmpName

FROM Emp

WHERE EName<>'JONES'

START WITH EName='JONES'

CONNECT BY PRIOR Empno=Mgr;

95.Employees salary more than 3000 after gating 20% Increment ?

SELECT EName EmpName,Sal+(Sal*.2)  IncreSal

FROM Emp

WHERE  Sal+(Sal*.2) > 3000

96.Employee with department name ?

SELECT EName EmpName,DName Department

FROM Emp,Dept

WHERE Emp.Deptno=Dept.Deptno;

97.Employees working in sales department?

SELECT   EName EmpName   ,Deptno Department

FROM Emp

WHERE Deptno=(   SELECT Deptno

FROM Dept

                                                WHERE  DName='SALES');

SELECT   EName EmpName   ,DName Department

FROM Emp,Dept

WHERE Emp.Deptno=Dept.Deptno

               AND DName='SALES';

98.Employee name,Department name ,sal and commission for salary between 2000 to 5000 and location Chicago ?

SELECT   EName EmpName   ,DName Department

               ,Sal Salary  ,Comm Commission

FROM Emp,Despt

WHERE Emp.Deptno=Dept.Deptno

               AND LOC='CHICAGO'

               AND Sal BETWEEN 2000 AND 5000;

99.Employees salary greater than their Manager’s salary ?

SELECT   E.EName Employee,E.Sal EmpSalary

               ,M.EName manager,M.Sal MgrSalary

FROM Emp E,Emp M

WHERE E.Mgr=M.Empno

               AND

               E.Sal > M.Sal;

100.Employees working in same department of his  manager ?

SELECT   E.EName Employee,E.Deptno EmpDepartment

              ,M.EName manager,M.Deptno MgrDepartment

FROM Emp E,Emp M

WHERE E.Mgr=M.Empno

              AND

              E.Deptno = M.Deptno;