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