Sql Queries1

1.Display the dept information from department table
select * from dept;

2.Display the details of all employees
select * from emp;

3.Display the name and job for all employees
select ename,job from emp;

4.Display name and salary for all employees
select ename,sal from emp;

5.Display employee number and total salary for each employee
select empno,sal+comm from emp;

6.Display employee name and annual salary for all employees
select empno,ename,12*sal+nvl(comm,0) annualsal from emp;

7.Display the names of all employees who are working in department number 10
select ename from emp where deptno = 10;

8.Display the names of all employees working as clerks and drawing a salary more than 3000
select ename from emp wher job = 'CLERK' and sal > 3000;

9.Display employee number and names for employees who earn commission
select empno,ename from emp where comm is not null and comm > 0;

10.Display names of employees who do not earn any commission
select empno,ename from emp where comm is null and comm = 0;

11.Display the names of employees who are working as clerk , salesman or analyst and drawing a salary more than 3000
select ename from emp where (job='CLERK' or job='SALESMAN' or job='ANALYST') and sal>3000;

12.Display the names of employees who are working in the company for the past 5 years
select ename from emp where sysdate - hiredate > 5*365;

13.Display the list of employees who have joined the company before 30 th june 90 or after 31 st dec 90
select * from emp where hiredate between '30-jun-1990' and '31-dec-1990';

14.Display current date
select sysdate from dual;

15.Display the list of users in your database (using log table)
select * from dba_users;

16.Display the names of all tables from the current user
select * from tab;

17.Display the name of the current user
show user;

18.Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks , salesman or analyst
select ename from emp where deptno in (10,20,40) or job in ('CLERK','SALESMAN','ANALYST');

19.Display the names of employees whose name starts with alphabet S
select ename from emp where ename like 'S%';

20.Display employee name from employees whose name ends with alphabet S
select ename from emp where ename like '%S';

21.Display the names of employees whose names have sencond alphabet A in their names
select ename from emp where ename like '_S%';

22.Display the names of employees whose name is exactly five characters in length
select ename from emp where length(ename)=5;
or
select ename from emp where ename like '_____';

23.Display the names of employees who are not working as managers
select * from emp minus (select * from emp where empno in (select mgr from emp));
or
select * from emp where empno not in (select mgr from emp where mgr is not null);
or
select * from emp e where empno not in (select mgr from emp where e.empno=mgr);

24.Display the names of employees who are not working as SALESMAN or CLERK or ANALYST
select job from emp where job not in ('CLERK','ANALYST','SALESMAN');

25.Display all rows from emp table. The system should wait after every screen full of information
set pause on;

26.Display the total number of employees working in the company
select count(*) from emp;

27.Display the total salary and total commission to all employees
select sum(sal), sum(nvl(comm,0)) from emp;

28.Display the maximum salary from emp table
select max(sal) from emp;

29.Display the minimum salary from emp table
select min(sal) from emp;

30.Display the average salary from emp table
select avg(sal) from emp;

31.Display the maximum salary being paid to CLERK
select max(sal) from emp where job='CLERK';

32.Display the maximum salary being paid in dept no 20
select max(sal) from emp where deptno=20;

33.Display the minimum salary being paid to any SALESMAN
select min(sal) from emp where job='SALESMAN';

34.Display the average salary drawn by managers
select avg(sal) from emp where job='MANAGER';

35.Display the total salary drawn by analyst working in dept no 40
select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;

36.Display the names of employees in order of salary i.e. the name of the employee earning lowest salary shoud appear first
select ename from emp order by sal;

37.Display the names of employees in descending order of salary
select ename from emp order by sal desc;

38.Display the details from emp table in order of emp name
select ename from emp order by ename;

39.Display empnno,ename,deptno and sal. Sort the output first based on name and within name by deptno and witdhin deptno by sal;
select * from emp order by ename,deptno,sal;

40) Display the name of employees along with their annual salary(sal*12).
the name of the employee earning highest annual salary should appear first?
Ans:select ename,sal,sal*12 "Annual Salary" from emp order by "Annual Salary" desc;

41) Display name,salary,Hra,pf,da,TotalSalary for each employee.
The out put should be in the order of total salary ,hra 15% of salary ,DA 10% of salary .pf 5% salary Total Salary
will be (salary+hra+da)-pf?
Ans: select ename,sal SA,sal*0.15 HRA,sal*0.10 DA,sal*5/100 PF, sal+(sal*0.15)+(sal*0.10)-(sal*.05) TOTALSALARY
from emp ORDER BY TOTALSALARY DESC;

42) Display Department numbers and total number of employees working in each Department?
Ans: select deptno,count(*) from tvsemp group by deptno;

43) Display the various jobs and total number of employees working in each job group?
Ans: select job,count(*) from tvsemp group by job;

44)Display department numbers and Total Salary for each Department?
Ans: select deptno,sum(sal) from tvsemp group by deptno;

45)Display department numbers and Maximum Salary from each Department?
Ans: select deptno,max(Sal) from tvsemp group by deptno;

46)Display various jobs and Total Salary for each job?
Ans: select job,sum(sal) from tvsemp group by job;

47)Display each job along with min of salary being paid in each job group?
Ans: select job ,min(sal) from tvsemp group by job;

48) Display the department Number with more than three employees in each department?
Ans: select deptno ,count(*) from tvsemp group by deptno having count(*)>3;

49) Display various jobs along with total salary for each of the job where total salary is greater than 40000?
Ans: select job,sum(sal) from tvsemp group by job having sum(SAl)>40000;

50) Display the various jobs along with total number of employees in each job.The
output should contain only those jobs with more than three employees?
Ans: select job,count(*) from tvsemp group by job having count(*)>3;

51) Display the name of employees who earn Highest Salary?
Ans: select ename, sal from tvsemp where sal>=(select max(sal) from tvsemp );

52) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?
Ans: select ename,empno from tvsemp where sal=(select max(sal) from tvsemp where job='CLERK') and job='CLERK' ;

53) Display the names of salesman who earns a salary more than the Highest Salary of the clerk?
Ans: select ename,sal from tvsemp where sal>(select max(sal) from tvsemp where job='CLERK') AND job='SALESMAN';

54) Display the names of clerks who earn a salary more than the lowest Salary of any salesman?
Ans: select ename,sal from tvsemp where sal>(select min(sal) from tvsemp where job='SALESMAN') and job='CLERK';

55) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?
Ans: select ename,sal from tvsemp where sal>all(select sal from tvsemp where ename='JONES' OR ename='SCOTT');

56) Display the names of employees who earn Highest salary in their respective departments?
Ans: select ename,sal,deptno from tvsemp where sal in (select max(sal) from tvsemp group by deptno);

57) Display the names of employees who earn Highest salaries in their respective job Groups?
Ans: select ename,job from tvsemp where sal in (select max(sal) from tvsemp group by job);

58) Display employee names who are working in Accounting department?
Ans: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';

59) Display the employee names who are Working in Chicago?
Ans: select e.ename,d.loc from emp e,tvsdept d where e.deptno=d.deptno and d.loc='CHICAGO';

60) Display the job groups having Total Salary greater than the maximum salary for Managers?
Ans: select job ,sum(sal) from tvsemp group by job having sum(sal) >(select max(sal) from tvsemp where job='MANAGER');

61) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?
Ans: select ename,deptno from tvsemp where sal>any(select min(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;

62) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?
Ans: select ename,deptno from tvsemp where sal>all(select max(sal) from tvsemp where deptno!=10 group by deptno) and deptno=10 ;

63) Display the names of mployees in Upper Case?
Ans: select upper(ename) from tvsemp;

64) Display the names of employees in Lower Case?
Ans: select Lower(ename) from tvsemp;

65) Display the names of employees in Proper case?
Ans: select InitCap(ename)from tvsemp;

Q:66) Find the length of your name using Appropriate Function?
Ans: select lentgh('RAMA') from dual;

67) Display the length of all the employee names?
Ans: select length(ename) from tvsemp;

68) Display the name of employee Concatinate with Employee Number?
Ans: select ename||' '||empno from tvsemp;

69) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
Ans: select substr('Oracle',3,2) from dual;

70) Find the first occurance of character a from the following string Computer Maintenance Corporation?
Ans: select lstr('Computer Maintenance Corporation','a' ) from dual;

71) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)?
Ans: select translate('Alliens','A','B') from Dual;

72) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
Ans: select ename ,replace(job,'MANAGER','BOSS') from tvsemp;

73) Display empno,ename,deptno from tvsemp table. Instead of display department numbers
display the related department name(Use decode function)?
Ans: select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING'
,20,'RESEARCH',30,'SALES','OPERATIONS')DName from tvsemp;

74) Display your Age in Days?
Ans: select sysdate-to_date('30-jul-1977') from dual;

75) Display your Age in Months?
Ans: select months_between(sysdate,to_date('30-jul-1977')) from dual;

76) Display current date as 15th August Friday Nineteen Nienty Seven?
Ans: select To_char(sysdate,'ddth Month Day year') from dual;

77) Display the following output for each row from tvsemp table?
Ans: Q:78

78) Scott has joined the company on 13th August ninteen ninety?
Ans: select empno,ename,to_char(Hiredate,'Day ddth Month year') from tvsemp;

79) Find the nearest Saturday after Current date?
Ans: select next_day(sysdate,'Saturday') from dual;

80) Display the current time?
Ans: select To_Char(sysdate,'HH:MI:SS') from dual;

81) Display the date three months before the Current date?
Ans: select Add_months(sysdate,-3) from dual

82) Display the common jobs from department number 10 and 20?
Ans: select job from tvsemp where job in (select job from tvsemp where deptno=20) and deptno=10;

83) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?
Ans: select Distinct job from tvsemp where deptno in(10,20);

84) Display the jobs which are unique to department 10?
Ans: select job from tvsemp where deptno=10;

85) Display the details of those employees who do not have any person working under him?
Ans: select empno,ename,job from tvsemp where empno not in (select mgr from tvsemp where mgr is not null );

86) Display the details of those employees who are in sales department and grade is 3?
Ans: select e.ename,d.dname,grade from emp e,dept d ,salgrade where e.deptno=d.deptno and dname='SALES' and grade=3;

87) Display thoes who are not managers?
Ans: select ename from tvsemp where job!='MANAGER';

88) Display those employees whose name contains not less than 4 characters?
Ans: select ename from tvsemp where length(ename)>=4

89) Display those department whose name start with"S" while location name ends with "K"?
Ans: select e.ename,d.loc from tvsemp e ,tvsdept d where d.loc like('%K') and ename like('S%')

90) Display those employees whose manager name is Jones?
Ans: select e.ename Superior,e1.ename Subordinate from tvsemp e,e1 where e.empno=e1.mgr and e.ename='JONES';

91) Display those employees whose salary is more than 3000 after giving 20% increment?
Ans: select ename,sal,(sal+(sal*0.20)) from tvsemp where (sal+(sal*0.20))>3000;

92) Display all employees with their department names?
Ans: select e.ename,d.dname from tvsemp e, tvsdept d where e.deptno=d.deptno

93) Display ename who are working in sales department?
Ans: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';

94) Display employee name,dept name,salary,and commission for those sal in between 2000
to 5000 while location is Chicago?
Ans: Select e.ename,d.dname,e.sal,e.comm from tvsemp e,dept d where e.deptno=d.deptno and sal between 2000 and 5000;

95) Display those employees whose salary is greater than his managers salary?
Ans: Select e.ename,e.sal,e1.ename,e1.sal from tvsemp e,e1 where e.mgr=e1.empno and e.sal>e1.sal;

96) Display those employees who are working in the same dept where his manager is work?
Ans: select e.ename,e.deptno,e1.ename,e1.deptno from tvsemp e,e1 where e.mgr=e1.empno and e.deptno=e1.deptno;

97) Display those employees who are not working under any Manager?
Ans: select ename from tvsemp where mgr is null;

98) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while
joined the company before 31-DEC-82?
Ans: select ename,grade,deptno,sal from tvsemp ,salgrade where ( grade,sal) in
( select grade,sal from salgrade,tvsemp where sal between losal and hisal)
and grade!=4 and deptno in (10,30) and hiredate<'31-Dec-82

99) Update the salary of each employee by 10% increment who are not eligible for commission?
Ans: update tvsemp set sal= (sal+(sal*0.10)) where comm is null;

100) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
Ans: select e.ename,e.hiredate,d.loc from tvsemp e,tvsdept d where
e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in('NEW YORK','CHICAGO');