1 / 8

Advanced SQL

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

alec-mccullough
Télécharger la présentation

Advanced SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related