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');
101)
Display employee name ,job,deptname,loc for all who are working as manager?
Ans:
select e.ename,e.job,d.dname,d.loc from tvsemp e,tvsdept d where
e.deptno=d.deptno
and
e.empno in (select mgr from tvsemp where mgr is not null);
102)
Display those employees whose manager name is jones and also display their
manager
name?
Ans:
select e.ename sub,e1.ename from tvsemp e,e1 where e.mgr=e1.empno and
e1.ename='JONES';
103)
Display name and salary of ford if his salary is equal to hisal of his grade?
Ans:
select ename,grade,hisal,sal from emp,salgrade where ename='FORD' and
sal=hisal;
OR
select
grade,sal,hisal from tvsemp,salgrade where ename='FORD' and sal between losal
and hisal;
OR
select
ename,sal,hisal,grade from tvsemp,salgrade where ename='FORD'
and
(grade,sal) in (select grade,hisal from salgrade,tvsemp where
sal
between losal and hisal);
104)
Display employee name ,job,deptname,his manager name ,his grade and make an
under
department wise?
Ans:
select e.ename sub,e1.ename sup,e.job,d.dname ,grade from tvsemp
e,e1,salgrade,tvsdept d where e.mgr=e1.empno and e.sal between losal and hisal
and e.deptno=d.deptno group by d.deptno,e.ename,e1.ename,e.job,d.dname,grade;
OR
select
e.ename sub,e1.ename sup,e.job,d.dname ,grade from tvsemp e,e1,salgrade,tvsdept
d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno
105)
List out all the employee names ,job,salary,grade and deptname for every one in
a company except 'CLERK' . Sort on salary display the highest salary?
Ans:
select e.ename ,e.job,e.sal,d.dname ,grade from tvsemp e,salgrade,tvsdept d
where (e.deptno=d.deptno and e.sal between losal and hisal ) order by e.sal
desc
106) Display employee name,job abd his manager .Display also
employees who are with out
managers?
Ans:
select e.ename ,e1.ename,e.job,e.sal,d.dname from tvsemp e,e1,tvsdept d where
e.mgr=e1.empno(+) and e.deptno=d.deptno
107)
Display Top 5 employee of a Company?
Ans:
108)
Display the names of those employees who are getting the highest salary?
Ans:
select ename,sal from tvsemp where sal in (select max(sal) from tvsemp)
109)
Display those employees whose salary is equal to average of maximum and
minimum?
Ans:
select * from tvsemp
where
sal=(select (max(sal)+min(sal))/2 from tvsemp)
110)
Select count of employees in each department where count >3?
Ans:
select count(*) from tvsemp group by deptno having count(*)>3
111)
Display dname where atleast three are working and display only deptname?
Ans:
select d.dname from tvsdept d, tvsemp e where e.deptno=d.deptno group by
d.dname having count(*)>3;
112)
Display name of those managers name whose salary is more than average salary of
Company?
Ans:
select distinct e1.ename,e1.sal from tvsemp e,e1,dept d where e.deptno=d.deptno
and e.mgr=e1.empno and e1.sal> (select avg(sal) from tvsemp);
113)
Display those managers name whose salary is more than average salary salary of
his
employees?
Ans:
select distinct e1.ename,e1.sal from tvsemp e,e1,dept d where e.deptno=d.deptno
and e.mgr=e1.empno and e1.sal>any (select avg(sal) from tvsemp group by
deptno);
114)
Display employee name,sal,comm and netpay for those employees whose netpay is
greater
than or equal to any other employee salary of the company?
Ans:
select ename,sal,NVL(comm,0),sal+NVL(comm,0) from tvsemp where
sal+NVL(comm,0)
>any (select e.sal from tvsemp e );
115)
Display those employees whose salary is less than his manager but more than
salary of
other
managers?
Ans:
select e.ename sub,e.sal from tvsemp e,e1,tvsdept d where
e.deptno=d.deptno and e.mgr=e1.empno
and
e.sal<e1.sal
and
e.sal >any (select e2.sal from tvsemp e2, e,tvsdept d1 where
e.mgr=e2.empno
and d1.deptno=e.deptno);
116)
Display all employees names with total sal of company with each employee name?
Ans:
117)
Find the last 5(least) employees of company?
Ans:
118)
Find out the number of employees whose salary is greater than their managers
salary?
Ans:
select e.ename,e.sal,e1.ename,e1.sal from tvsemp e,e1,tvsdept d where
e.deptno=d.deptno and e.mgr=e1.empno and e.sal>e1.sal
119)
Display the manager who are not working under president but they are working
under
any
other manager?
Ans:
select e2.ename from emp e1,emp e2,emp e3 where e1.mgr=e2.empno and
e2.mgr=e3.empno and e3.job!='PRESIDENT';
120)
Delete those department where no employee working?
Ans:
delete from tvsemp where empno is null;
121)
Delete those records from emp table whose deptno not available in dept table?
Ans:
delete from tvsemp e where e.deptno not in (select deptno from tvsdept)
122)
Display those enames whose salary is out of grade available in salgrade table?
Ans:
select empno,sal from tvsemp where sal<(select min(LOSAL) from salgrade )
OR
sal>(select max(hisal) from salgrade)
123)
Display employee name,sal,comm and whose netpay is greater than any othere in
the
company?
Ans:
select ename,sal,comm,sal+comm from tvsemp where sal+comm>any
(select
sal+comm from tvsemp )
124)
Display name of those employees who are going to retire 31-Dec-99 if maximum
job period
is
30 years?
Ans:
select empno, hiredate,sysdate, to_char(sysdate,'yyyy') -
to_char(hiredate,'yyyy')
from
tvsemp where to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')=30
125)
Display those employees whose salary is odd value?
Ans:
select ename ,sal from tvsemp where mod(sal,2)!=0
126) Display those employees whose salary contains atleast 3
digits?
Ans:
select ename,sal from tvsemp where length(sal)=3
127)
Display those employees who joined in the company in the month of Dec?
Ans:
Select empno,ename from tvsemp where trim(to_char(hiredate,'Mon'))=trim('DEC')
128)
Display those employees whose name contains A?
Ans:
select ename from tvsemp where ename like('%A%')
129)
Display those employees whose deptno is available in salary?
Ans:
select ename,sal from tvsemp where deptno in (select distinct sal from tvsemp);
130)
Display those employees whose first 2 characters from hiredate - last 2
characters sal?
Ans:
select empno,hiredate,sal from tvsemp where
trim(substr(hiredate,1,2))=trim(substr(sal,-2,2));
or
select
hiredate,sal from tvsemp where to_Char(hiredate,'dd')=trim(substr(sal,-2,2))
131)
Display those employeess whose 10% of salary is equal to the year joining?
Ans:
select ename ,sal,0.10*sal from tvsemp where
0.10*sal=trim(to_char(hiredate,'yy'))
132)
Display those employees who are working in sales or research?
Ans:
select e.ename from tvsemp e ,tvsdept d where e.deptno=d.deptno and d.dname in('SALES','RESEARCH');
133)
Display the grade of jones?
Ans:
select ename,grade from tvsemp,salgrade where ( grade,sal) =
(select
grade,sal from salgrade,tvsemp where sal between losal and hisal and
ename='JONES')
134)
Display those employees who joined the company before 15th of the month?
Ans:
select ename ,hiredate from tvsemp where hiredate<'15-Jul-02' and hiredate
>='01-jul-02';
135)
Display those employees who has joined before 15th of the month?
Ans:
select ename ,hiredate from tvsemp where hiredate<'15-Jul-02'
136)
Delete those records where no of employees in particular department is less
than 3?
Ans:
delete from tvsemp where deptno in (select deptno from tvsemp group by deptno
having count(*) <3
137A) Delete those employeewho joined the company 10 years back
from today?
Ans:
delete from tvsemp where empno in (select empno from tvsemp
where
to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10)
137B)
Display the deptname the number of characters of which is equal to no of
employee
in
any other department?
Ans:
138)
Display the deptname where no employee is working?
Ans:
select deptno from tvsemp where empno is null;
139)
Display those employees who are working as manager?
Ans:
select e2.ename from tvsemp e1,e2 where e1.mgr=e2.empno and e2.empno is not
null
140)
Count th number of employees who are working as managers (Using set opetrator)?
Ans:
select d.dname from tvsdept d where length(d.dname) in (select count(*) from
tvsemp e where e.deptno!=d.deptno group by e.deptno)
141)
Display the name of the dept those employees who joined the company on the same
date?
Ans:
select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and
a.empno!=b.empno
142)
Display those employees whose grade is equal to any number of sal but not equal
to first number of sal?
Ans:
select ename,sal,grade ,substr(sal,grade,1) from tvsemp,salgrade where
grade!=substr(sal,1,1)
and grade = substr(sal,grade,1)
and
sal between losal and hisal
143)
Count the no of employees working as manager using set operation?
Ans:
Select count(empno) from tvsemp where
empno
in (select a.empno from tvsemp a
intersect
select
b.mgr from tvsemp b)
144)
Display the name of employees who joined the company on the same date?
Ans:
select a.ename,b.ename from tvsemp a,tvsemp b where a.hiredate=b.hiredate and
a.empno!=b.empno;
145)
Display the manager who is having maximum number of employees working under
him?
Ans: select e2.ename,count(*) from tvsemp e1,e2 where
e1.mgr=e2.empno group by e2.ename Having count(*)=(select max(count(*)) from
tvsemp e1,e2 where e1.mgr=e2.empno group by e2.ename)
146)
List out the employee name and salary increased by 15% and express as whole
number of Dollars?
Ans:
select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from
tvsemp
147)
Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and
job ?
Ans:
select ename"EMPLOYEE_AND",job"JOB" FROM TVSEMP;
148)
Lust of employees with hiredate in the format of 'June 4 1988'?
Ans:
select ename,to_char(hiredate,'Month dd yyyy') from tvsemp;
149)
print list of employees displaying 'Just salary' if more than 1500 if exactly
1500
display
'on taget' if less than 1500 display below 1500?
Ans:
select ename,sal,
(
case
when sal < 1500 then
'Below_Target'
when
sal=1500 then
'On_Target'
when
sal > 1500 then
'Above_Target'
else
'kkkkk'
end
)
from
tvsemp
150)
Which query to calculate the length of time any employee has been with the
company
Ans:
select hiredate,to_char(hiredate,' HH:MI:SS') FROM tvsemp
151)
Given a string of the format 'nn/nn' . Verify that the first and last 2
characters are numbers .And that the middle character is '/' Print the expressions
'Yes' IF valid
'NO'
of not valid . Use the following values to test your
solution'12/54',01/1a,'99/98'?
Ans:
152)
Employes hire on OR Before 15th of any month are paid on the last friday of
that month
those
hired after 15th are paid the last friday of th following month .print a list
of employees .their hiredate and first pay date sort those who se salary
contains first
digit
of their deptno?
Ans: select ename,hiredate, LAST_DAY (
next_day(hiredate,'Friday')),
(
case
when to_char(hiredate,'dd') <=('15') then
LAST_DAY
( next_day(hiredate,'Friday'))
when
to_char(hiredate,'dd')>('15') then
LAST_DAY(
next_day(add_months(hiredate,1),'Friday'))
end
)
from
tvsemp
153)
Display those managers who are getting less than his employees salary?
Ans:
select a.empno,a.ename ,a.sal,b.sal,b.empno,b.ename from tvsemp a, tvsemp b
where a.mgr=b.empno and a.sal>b.sal
154)
Print the details of employees who are subordinates to BLAKE?
Ans:
select a.empno,a.ename ,b.ename from tvsemp a, tvsemp b where a.mgr=b.empno
and
b.ename='BLAKE'
**********************
151.Display
those who working as manager using co related sub query
select
* from emp where empno in (select mgr from emp);
152.Display
those employees whose manager name is JONES and also with his manager name
select
* from emp where mgr=(select empno from emp where ename='JONES') union select *
from emp where empno =
(select
mgr from emp where ename='JONES');
153.Define
variable representing the expressions used to calculate on employees total
annual renumaration
define
emp_ann_sal=(sal+nvl(comm,0))*.12;
154.Use
the variable in a statement which finds all employees who can earn 30000 a year
or more
select
* from emp where &emp_ann_sal>30000;
155.Find
out how many managers are there with out listing them
select
count(*) from emp where empno in (select mgr from emp);
156.Find
out the avg sal and avg total remuneration for each job type remember salesman
earn commission
select
job,avg(sal+nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by job;
157.Check whether all employees number are indeed unique
select
count(empno) ,count(distinct(empno)) from emp having
count(empno)=(count(distinct(empno));
158.List
out the lowest paid employees working for each manager, exclude any groups
where minsal is less than
1000
sort the output by sal
select
e.ename,e.mgr,e.sal from emp e where sal in (select min(sal) from emp where
mgr=e.mgr) and
e.sal>1000
order by sal;
159.List
ename,job,annual sal,depno,dname and grade who earn 30000 per year and who are
not clerks
select
e.ename,e.job,(e.sal+nvl(e.comm,0))*12,e.deptno,d.dname,s.grade from emp
e,salgrade s,dept d
where
e.sal between s.losal and s.hisal and e.deptno=d.deptno and (e.sal+nvl(comm,0))*12
> 30000
and
e.job<>'CLERK';
160.Find
out th job that was falled in the first half of 1983 and the same job that was
falled during the
same
period on 1984
161.Find
out the all employees who joined the company before their manager
select
* from emp e where hiredate <(select hiredate from emp where empno=e.mgr);
162.List
out the all employees by name and number along with their manager's name and
number also display
'NO
MANAGER' who has no manager
select
e.empno,e.ename,m.empno Manager,m.ename ManagerName from emp e,emp m where
e.mgr=m.empno
union
select
empno,ename,mgr,'NO Manager' from emp where mgr is null;
163.Find
out the employees who earned the highest sal in each job typed sort in
descending sal order
select
* from emp e where sal=(select max(sal) from emp where job=e.job);
164.Find
out the employees who earned the min sal for their job in ascending order
select
* from emp e where sal=(select min(sal) from emp where job=e.job) order by sal;
165.Find
out the most recently hired employees in each dept order by hire date
select
* from emp order by deptno,hiredate desc;
166.Display
ename,sal and deptno for each employee who earn a sal greater than the avg of
their department
order by deptno
select
ename,sal,deptno from emp e where sal>(select avg(sal) from emp where
deptno=e.deptno) order by deptno;
167.Display
the department where there are no employees
select
deptno,dname from dept where deptno not in (select distinct(deptno) from emp);
168.Display
the dept no with highest annual remuneration bill as compensation
select
deptno,sum(sal) from emp group by deptno having sum(sal)=(select max(sum(sal))
from emp group by deptno);
169.In
which year did most people join the company. Display the year and number of
employees
select
count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');
170.Display
avg sal figure for the dept
select
deptno,avg(sal) from emp group by deptno;
171.Write
a query of display against the row of the most recently hierd employee.display
ename hire date
and
column max date showing
select
empno,hiredate from emp wher hiredate=(select max(hiredate) from emp);
172.Display
employees who can earn more than lowest sal in dept no 30
select
* from emp where sal > (select min(sal) from emp where deptno=30);
173.Find
employees who can earn more than every employees in dept no 30
select
* from emp where sal>(select max(sal) from emp where deptno=30);
select
* from emp where sal>all(select sal from emp where deptno=30);
174.select
dept name and deptno and sum of sal
break
on deptno on dname;
select
e.deptno,d.dname,sal from emp e,dept d where e.deptno=d.deptno order by
e.deptno;
175.Find
out avg sal and avg total remainders for each job type
176.Find
all dept's which have more than 3 employees
select
deptno from emp group by deptno having count(*)>3;
177.If
the pay day is next Friday after 15th and 30th of every month. What is the next
pay day from
their
hire date for employee in emp table
178.If
an employee is taken by you today in your organization and is a policy in your
company to have a
review after 9 months the joined date (and of 1st of next month
after 9 months) how many days from today
your
employee has to wait for a review
179.Display
employee name and his sal whose sal is greater than highest avg of deptno
180.Display
the 10 th record of emp table (without using rowid)
181.Display
the half of the enames in upper case and remaining lower case
select
concat(upper(substr(ename,0,length(ename)/2),lower(substr(ename,length(ename)/2+1,length(ename))))
from
emp;
182.Display
the 10th record of emp table without using group by and rowid
183.Delete
the 10th record of emp table
184.Create
a copy of emp table
create
table emp1 as select * from emp;
185.select
ename if ename exists more than once
select
distinct(ename) from emp e where ename in (select ename from emp where
e.empno<>empno);
186.Display
all enames in reverse order
select
ename from emp order by ename desc;
187.Display
those employee whose joining of month and grade is equal
select
empno,ename from emp e,salgrade s where e.sal between s.losal and s.hisal and
to_char(hiredate,
'mm')=grade;
188.Display
those employee whose joining date is available in deptno
select
* from emp where to_char(hiredate,'dd') =deptno;
189.Display
those employee name as follows A ALLEN, B BLAKE
select
substr(ename,1,1)||''||ename from emp;
190.List
out the employees ename,sal,pf from emp
select
ename,sal,sal*15/100 pf from emp;
191.Display
RSPS from emp without using updating,inserting
192.Create
table emp with only one column empno
create
table emp (empno number(5));
193.Add this column to emp table ename varchar2(20)
alter
table emp add ename varchar2(20) not null;
194.OOPSI
i forget to give the primary key constraint. Add it now
alter
table emp add constraint emp_empno primary key (empno);
195.Now
increase the length of ename column to 30 characters
alter
table emp modify ename varchar2(30);
196.Add
salary column to emp table
alter
table emp add sal number(7,2);
197.I
want to give a validation saying that sal can not be greater 10000(note give a
name to this column)
alter
table emp add constraint emp_sal_check check(sal<10000);
198.For
the time being i have decided that i will not impose this validation. My boss
has agreed to pay
more
than 10000
alter
table emp disable constraint emp_sal_check;
199.My
boss has changed his mind. Now he doesn't want to pay more than 10000 So revoke
that salary constraint
alter
table emp enable constraint emp_sal_check;
200.Add
column called as mgr to your emp table
alter
table emp add mgr number(5);
201.Oh!
This column should be related to empno, Give a command tdo add this constraint
Alter
table emp add constraint emp_mgr foreign key (empno);
202.Add
dept no column to your emp table
alter
table emp add deptno number(3);
203.This
deptno column should be related to deptno column of dept table
alter
table emp1 add constraint emp1_deptno foreign key (deptno) references
dept(deptno);
204.Create
table called as new emp. Using single command create this table as well as to
get data into
this
table (use create table as)
create
table newemp as select * from emp;
205.Create
table called as newemp. This table should contain only empno,ename,dname
create table newemp as select empno,ename,dname from emp e,dept d
where e.deptno=d.deptno;
206.Delete
the rows of employees who are working in the company for more than 2 years
delete
from emp where floor(sysdate-hiredate)>2*365;
207.Provides
a commission to employees who are not earning any commission
select
emp set comm=300 where comm is null;
208.If
any employee has commission his commission should be incremented by 100% of his
salary
update
emp set comm=comm*10/100 where comm is not null;
209.Display
employee name and department name for each employee
select
ename,dname from emp e,dept d where e.deptno=d.deptno;
210.Display
employee number,name and location of the department in which he is working
select
empno,ename,loc from emp e,dept d where e.detpno=d.deptno;
211.Display
ename,dname even if there no employees working in a particular department(use
outer join)
select
ename,dname from emp e,dept d where e.deptno(+)=d.deptno;
212.Display
employee name and his manager name.
select
e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
213.Display
the department name along with total salary in each department
select
deptno,sum(sal) from emp group by deptno;
214.Display
the department name and total number of employees in each department
select deptno,count(*)
from emp group by deptno;
for 23rd question we can also write as
ReplyDeleteselect * from emp where job!='manager';