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:

• SQL Course 2. (2018). SQL Course 2: Interactive Online SQL Training. Retrieved from http://www.sqlcourse2.com/
• Mode Community. (2018). The SQL Tutorial for Data Analysis. Retrieved from https://community.modeanalytics.com/sql/tutorial/introduction-to-sql/
• Programmer Interview. (2018). Practice SQL Interview Questions and Answers. Retrieved from http://www.programmerinterview.com/index.php/database-sql/practice-interview-question-1/

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