The syntax is as DELETE FROM emp WHERE deptnoSELECT deptno FROM dept WHERE

The syntax is as delete from emp where deptnoselect

This preview shows page 14 - 16 out of 24 pages.

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.
Image of page 14
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 Education In 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
Image of page 15
Image of page 16

You've reached the end of your free preview.

Want to read all 24 pages?

  • Winter '17
  • DR Mubashir

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture