Advanced SQL

1. Advanced SQL

2. 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

3. 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;

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

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

6. 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);

7. 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

8. 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