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 lt 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
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 gt (SELECT avg(sal) FROM emp WHERE
deptnoe.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