are using the sub query, we can delete all those records in a single step. The syntax is as DELETE FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname=’Physics’);Firstly, it will execute the SELECT statement and then the output of this Statement will used as an input for the DELETE operation, thus doing both the things in one syntax.Example2: Update the salary of ‘Axay’ to the highest salary the company.UPDATE emp SET sal=(SELECT MAX(sal) FROM emp) WHERE ename=’Axay’;Example 3: Retrieve the second highest salary.SELECT MAX (sal) FROM emp WHERE sal <> (SELECT MAX(sal) FROM emp);Example 4: Get the name of the employees who are getting the second highest salary.SELECT ename FROM emp WHERE sal=(SELECT MAX (sal) FROM emp WHERE sal <> (SELECT MAX (sal) FROM emp));Example 5:List all the employees from Physics department.
Data Base Management systems87NotesSELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname=’Physics’);Example 6:Retrieve the name of all the employees who work in Mr. Axay’s department and getting the same salary as Mr. Ashish is getting in Chemistry department.Amity Directorate of Distance & Online EducationIn normal sub-query, SELECT statements can be nested in the WHERE clause of the outer query. Correlated sub query is a nested sub query, The above query gives the details of employees who earn a salary greater than the average salary for their department. But this query has an unusual thing and that is E.deptno column of outer query used in the inner query. So why it is executing? The reason is that