Mastering SQL: Handling Nulls, Dynamic SQL, and Correlated Subqueries

Advanced SQL

SQL - Nulls • Nulls are not equal to anything - Null is not even equal to Null where columna != ‘ABC’ --this will not return records where columna is null change to one of these: where nvl(columna, ‘XXX’) != ‘ABC’ where columna != ‘ABC’ or columna is null • Same principle on update update tablea set columnb = columnb + 10 --note: if columnb is null, result will be null change to: update tablea set columnb = nvl(columnb, 0)+10

Dynamic SQL • Can be used to script many operations • using SQL to write SQL set head off set feedback off spool drop_spacer_tables; SELECT ‘drop table ‘ || table_name || ‘ cascade constraints;’ FROM user_tables WHERE table_name like ‘SPACER%’; spool off;

rownum • Uses ORDER BY clause • Retrieves resulting row numbers SELECT * FROM (SELECT empno, ename, hiredate FROM emp ORDER BY hiredate DESC) WHERE rownum < 16;

Correlated subqueries • Want the subquery to match an item in the outer query • must reference the outer query from inside the subquery (use aliases)

correlation SQL question • Find employees whose salary is higher than the average salary for their department SELECT empno, ename, sal, deptno FROM emp e WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno=e.deptno);

SQL question • Show all employees that have been hired on the same day of the week on which the maximum number of employees has been hired

SQL question • Show the department number, name, the number of employees and the average salary for each department together with the names, salaries, and jobs of the employees working in that department

Mastering SQL: Handling Nulls, Dynamic SQL, and Correlated Subqueries