Adv Database Management: SQL Sub-queries and views

Please note that the following blog post provides a summary view for what you need to get done (left column) and quick examples that illustrate how to do it in SQL (right column with SQL code in red). For more information please see the resources below:

Subquery
SELECT enames
  FROM emp
  WHERE sal >
    (SELECT sal
     FROM emp
     WHERE empno = 7566)
Correlated Subqueries
SELECT empno, sal, deptno
  FROM   emp outr
  WHERE  sal >
    (SELECT AVG(sal)
     FROM   emp innr
     WHERE  outr.deptno = innr.deptno)
Exists
SELECT empno, ename, job, deptno
  FROM   emp outr
  WHERE  EXISTS
    (SELECT empno
     FROM   emp innr
     WHERE  innr.mgr = outr.empno)
Not Exists
SELECT dname, deptno
  FROM   dept d
  WHERE  NOT EXISTS
    (SELECT *
     FROM   emp e
     WHERE  d.deptno = e.deptno)
In
SELECT empno, ename, job, deptno
  FROM   emp outr
  WHERE empno IN
    (SELECT mgr
     FROM   emp)

Creating a view
CREATE VIEW empvu10
  AS SELECT empno, ename, job
     FROM emp
     WHERE deptno = 10
Drop view
DROP VIEW empvu10

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s