90 likes | 219 Views
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
E N D
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