SQL QUERIES -Part 7
121) Display those employee whose deptno is available in salary?
SQL>select
emp.ename from emp, emp e where emp.sal=e.deptno;
122)
Display those employee whose first 2 characters from hiredate -last 2
characters of salary?
SQL>select
ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp
123)
Display those employee whose 10% of salary is equal to the year of
joining?
SQL>select
ename from emp where to_char(hiredate,'YY')=sal*0.1;
124)
Display those employee who are working in sales or research?
SQL>SELECT
ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE
DNAME IN('SALES','RESEARCH'));
125)
Display the grade of jones?
SQL>SELECT
ENAME,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
AND Ename='JONES';
126)
Display those employees who joined the company before 15 of the month?
a)select
ename from emp where to_char(hiredate,'DD')<15;
127)
Display those employee who has joined before 15th of the month.
a)select
ename from emp where to_char(hiredate,'DD')<15;
128)
Delete those records where no of employees in a particular department
is less than 3.
SQL>delete
from emp where deptno=(select deptno from emp
group by deptno having
count(deptno)<3);
129)
Display the name of the department where no employee working.
SQL>
SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO
130)
Display those employees who are working as manager.
SQL>SELECT
M.ENAME MANAGER FROM EMP M ,EMP E
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME
131)
Display those employees whose grade is equal to any number of sal but
not equal to first number of sal?
SQL>
SELECT ENAME,GRADE FROM EMP,SALGRADE
WHERE GRADE NOT IN(SELECT
SUBSTR(SAL,0,1)FROM EMP)
132)
Print the details of all the employees who are Sub-ordinate to BLAKE?
SQL>select
emp.ename from emp, emp e where emp.mgr=e.empno and
e.ename='BLAKE';
133)
Display employee name and his salary whose salary is greater than
highest average of department number?
SQL>SELECT
SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP
GROUP BY DEPTNO);
134)
Display the 10th record of emp table(without using rowid)
SQL>SELECT
* FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE ROWNUM<10
135)
Display the half of the ename's in upper case and remaining lowercase?
SQL>
SELECT
SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME))
FROM EMP;
136)
Display the 10th record of emp table without using group by and rowid?
SQL>SELECT
* FROM EMP WHERE ROWNUM<11
MINUS
SELECT * FROM EMP WHERE
ROWNUM<10
Delete the 10th record of emp
table.
SQL>DELETE
FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11
MINUS
SELECT EMPNO FROM EMP WHERE
ROWNUM<10)
137)
Create a copy of emp table;
SQL>create
table new_table as select * from emp where 1=2;
138)
Select ename if ename exists more than once.
SQL>select
ename from emp e group by ename having
count(*)>1;
139)
Display all enames in reverse order?(SMITH:HTIMS).
SQL>SELECT
REVERSE(ENAME)FROM EMP;
140)
Display those employee whose joining of month and grade is equal.
SQL>SELECT
ENAME FROM EMP WHERE SAL BETWEEN
(SELECT LOSAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM')) AND
(SELECT HISAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM'));
Very informative post ! There is a lot of information here that can help any business get started with a successful social networking campaign !data science course in malaysia
ReplyDeletebest tableau training
data analytics course malaysia
360DigiTMG
Great Article android based projects
ReplyDeleteJava Training in Chennai Project Center in Chennai Java Training in Chennai projects for cse The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training Project Centers in Chennai