51) Display the name of the employee who earns highest salary.
SQL>select ename from emp where sal=(select max(sal) from
emp);
52) Display the employee number and name for employee working as
clerk and earning highest salary among clerks.
SQL>select empno,ename from emp where where job='CLERK' and
sal=(select max(sal) from emp where job='CLERK');
53) Display the names of salesman who earns a salary more than
the highest salary of any clerk.
SQL>select ename,sal from emp where job='SALESMAN' and
sal>(select max(sal) from emp where job='CLERK');
54) Display the names of clerks who earn a salary more than the
lowest salary of any salesman.
SQL>select ename from emp where job='CLERK' and
sal>(select min(sal) from emp where job='SALESMAN');
Display the names of employees who earn a salary more than that
ofJones or that of salary grether than that of scott.
SQL>select ename,sal from emp where sal>(select sal from
emp where ename='JONES')and sal> (select sal from emp where ename='SCOTT');
55) Display the names of the employees who earn highest salary
in their respective departments.
SQL>select ename,sal,deptno from emp where sal in(select
max(sal) from emp group by deptno);
56) Display the names of the employees who earn highest salaries
in their respective job groups.
SQL>select ename,sal,job from emp where sal in(select
max(sal) from emp
group by job);
57) Display the employee names who are working in accounting
department.
SQL>select ename from emp where deptno=(select deptno from
dept where dname='ACCOUNTING');
58) Display the employee names who are working in Chicago.
SQL>select ename from emp where deptno=(select deptno from
dept where LOC='CHICAGO');
59) Display the Job groups having total salary greater than the
maximum salary for managers.
SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING
SUM(SAL)>(SELECT MAX(SAL) FROM EMP WHERE JOB='MANAGER');
60) Display the names of employees from department number 10
with salary greater than that of any employee working in other department.
SQL>select ename from emp where deptno=10 and
sal>any(select sal from emp where deptno not in 10);
61) Display the names of the employees from department number 10
with salary greater than that of all employee working in other
departments.
SQL>select ename from emp where deptno=10 and
sal>all(select sal from emp where deptno not in 10);
62) Display the names of the employees in Uppercase.
SQL>select upper(ename)from emp;
63) Display the names of the employees in Lower case.
SQL>select lower(ename)from emp;
64) Display the names of the employees in Proper case.
SQL>select initcap(ename)from emp;
65) Display the length of Your name using appropriate function.
SQL>select length('name') from dual;
66) Display the length of all the employee names.
SQL>select length(ename) from emp;
67) select name of the employee concatenate with employee
number.
SQL>select ename||empno from emp;
68) User appropriate function and extract 3 characters starting
from 2 characters from the following string 'Oracle'. i.e the output
should be 'ac'.
SQL>select substr('oracle',3,2) from dual;
69) Find the First occurance of character 'a' from the following
string i.e 'Computer Maintenance Corporation'.
SQL>SELECT INSTR('Computer Maintenance Corporation','a',1)
FROM DUAL;
70) Replace every occurance of alphabhet A with B in the string
Allens(use translate function)
SQL>select translate('Allens','A','B') from dual;
71) Display the informaction from emp table.Where job manager is
found it should be displayed as boos(Use replace function).
SQL>select replace(JOB,'MANAGER','BOSS') FROM EMP;
72) Display empno,ename,deptno from emp table.Instead of display
department numbers display the related department name(Use decode function).
SQL>select
empno,ename,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS')
from emp;
73) Display your age in days.
SQL>select to_date(sysdate)-to_date('10-sep-77')from dual;
74) Display your age in months.
SQL>select months_between(sysdate,'10-sep-77') from dual;
75) Display the current date as 15th August Friday Nineteen
Ninety Seven.
SQL>select to_char(sysdate,'ddth Month day year') from dual;
76) Display the following output for each row from emp
table.scott has joined the company on Wednesday 13th August nineteen ninety.
SQL>select ENAME||' HAS JOINED THE COMPANY ON
'||to_char(HIREDATE,'day ddth Month year') from EMP;
77) Find the date for nearest saturday after current date.
SQL>SELECT NEXT_DAY(SYSDATE,'SATURDAY')FROM DUAL;
78) Display current time.
SQL>select to_char(sysdate,'hh:MM:ss') from dual;
79) Display the date three months Before the current date.
SQL>select add_months(sysdate,3) from dual;
80) Display the common jobs from department number 10 and 20.
SQL>select job from emp where deptno=10 and job in(select job
from emp
where deptno=20);
81) Display the jobs found in department 10 and 20 Eliminate
duplicate jobs.
SQL>select distinct(job) from emp where deptno=10 or
deptno=20
(or)
SQL>select distinct(job) from emp where deptno in(10,20);
82) Display the jobs which are unique to department 10.
SQL>select distinct(job) from emp where deptno=10;
83) Display the details of those who do not have any person
working under them.
SQL>select e.ename from emp,emp e where emp.mgr=e.empno group
by e.ename having count(*)=1;
84) Display the details of those employees who are in sales
department and grade is 3.
SQL>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);
85) Display those who are not managers and who are managers any
one.
i)display the managers names
SQL>select distinct(m.ename) from emp e,emp m where
m.empno=e.mgr;
ii)display the who are not managers
SQL>select ename from emp where ename not in(select
distinct(m.ename) from emp e,emp m where m.empno=e.mgr);
86) Display those employee whose name contains not less than 4
characters.
SQL>select ename from emp where length(ename)>4;
87) Display those department whose name start with "S"
while the location name ends with "K".
SQL>select dname from dept where dname like 'S%' and loc like
'%K';
88) Display those employees whose manager name is JONES.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and
e.ename='JONES';
89) Display those employees whose salary is more than 3000 after
giving 20% increment.
SQL>select ename,sal from emp where (sal+sal*.2)>3000;
90) Display all employees while their dept names;
SQL>select ename,dname from emp,dept where
emp.deptno=dept.deptno;
91) Display ename who are working in sales dept.
SQL>select ename from emp where deptno=(select deptno from
dept where dname='SALES');
92) Display employee name,deptname,salary and comm for those sal
in between 2000 to 5000 while location is chicago.
SQL>select ename,dname,sal,comm from emp,dept where sal
between 2000 and 5000 and loc='CHICAGO' and emp.deptno=dept.deptno;
93)Display those employees whose salary greter than his manager
salary.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and
p.sal>e.sal;
94) Display those employees who are working in the same dept
where his manager is work.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and
p.deptno=e.deptno;
95) Display those employees who are not working under any
manager.
SQL>select ename from emp where mgr is null;
96) Display grade and employees name for the dept no 10 or 30
but grade is not 4 while joined the company before 31-dec-82.
SQL>select ename,grade from emp,salgrade where sal between
losal and hisal and deptno in(10,30) and grade<>4 and
hiredate<'31-DEC-82';
97) Update the salary of each employee by 10% increment who are
not eligible for commission.
SQL>update emp set sal=sal+sal*10/100 where comm is null;
98) SELECT those employee who joined the company before
31-dec-82 while their dept location is newyork or Chicago.
SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPTWHERE
(EMP.DEPTNO=DEPT.DEPTNO)AND HIREDATE <'31-DEC-82' AND DEPT.LOC
IN('CHICAGO','NEW YORK');
99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO
ARE WORKING AS MANAGER?
SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr
is not null;
100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? --[AND
ALSO DISPLAY THEIR MANAGER NAME]?
SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND
E.ENAME='JONES';
No comments:
Post a Comment