1 / 18

SQL advanced select using Oracle

SQL advanced select using Oracle. 7. Multiple Tables: Joins and Set Operations 8. Subqueries: Nested Queries. Select, 156. Simple data from a single table Advanced data from more tables join sub-queries. Join, 157. Cartesian product select * from tableA, tableB

urielle-jenkins
Download Presentation

SQL advanced select using Oracle

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. SQL advanced select using Oracle 7. Multiple Tables: Joins and Set Operations 8. Subqueries: Nested Queries SQL advanced select using Oracle

  2. Select, 156 • Simple • data from a single table • Advanced • data from more tables • join • sub-queries SQL advanced select using Oracle

  3. Join, 157 • Cartesian product • select * from tableA, tableB • all rows in tableA combined with all rows from tableB • huge result! seldom useful!! • Different types of joins • equi-join • non-equi-join • outer join • self-join SQL advanced select using Oracle

  4. Equi-join, 158 • Joining 2 tables using common attributes [usually primary and foreign keys] • Structure select … from tableA, tableB where tableA.PK = tableB.FK • Comparison using equality (=) is equi-join • Examples select * from student, city where city.postcode = student.postcode select * from student, course, studentCourse where student.ID = studentCourse.studentID and studentCourse.courseID = course.ID SQL advanced select using Oracle

  5. Non-equi-joins, 161 • Where clause using operations other than equality • Fig. 7-6, page 162 SQL advanced select using Oracle

  6. Outer joins, 163 • Ordinary join • if a value in one table has no matching value in the other table, the row is not included in the result. • Outer join • values without matching values are included in the result. • Oracle has 2 syntaxes • Special syntax • Special (old) Oracle syntax • New syntax • SQL99 standard syntax SQL advanced select using Oracle

  7. Outer join, old syntax • Old / special Oracle syntax SELECT … FROM tableA, tableB WHERE tableA.col1 (+)= tableB.col2 • rows from tableA with no matching attribute in tableB is included in the result • Fig. 7-7, page 163 SQL advanced select using Oracle

  8. Outer join, new syntax • New / SQL 99 standard syntax SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id • Result • Department with and without employees are included in the result • New in Oracle 9i (not present in Oracle 8i) • No examples in the book! • Look in the Oracle SQL documentation for more examples SQL advanced select using Oracle

  9. Self-join, 165 • Joining a table with itself. • ER recursive relationship • Rel. model foreign key to another attribute in the same relation. • same table used twice in the from clause we need to name the two instances of the table (table aliases) • select … from employee e, employee s where e.supervisorID = s.ID • Fig. 7-9 page 165 SQL advanced select using Oracle

  10. Set operators, 166 • Tables may be used in set operations • union tableA union tableB • all rows in tableA and all rows in tableB • union all tableA union all tableB • as union, but includes duplicates • intersect tableA intersect tableB • all rows that appears in both tableA and tableB • minus tableA minus tableB • all rows in tableA, but not in tableB • Tables must be "union compatible" • same number + types + order of attributes SQL advanced select using Oracle

  11. Sub-query, 174 • The where clause in a select statement may contain another select statement (sub-query)! • Fig. 8-1, page 175 • Two types of sub-queries • single row • returning a single row of data, and most often a single data item • multiple row • returning more than on row of data SQL advanced select using Oracle

  12. Single row query, 174 • Inner query is executed first, returning a value that is used in the outer query. • select … from tableA where attributA operator (sub-query) • Operator may be: =, >, <, etc. SQL advanced select using Oracle

  13. Sub-queries in create, insert, update and delete, 176 • create table tableName as select query • pure redundancy! • insert into tableName select query • pure redundancy! • update tableName set (columnNames) = (query) where condition • delete from tableName where columnName = (query) SQL advanced select using Oracle

  14. Multiple-row sub-queries, 181 • Sub-query returning more than one row of data. • select … from … where attribute operator (sub-query) • Operator may be • in • <all, >all • <any, >any, =any • Figure 8-11, 8-12, page 182 • Figure 8-13, page 183 SQL advanced select using Oracle

  15. Top-N analysis,183 • MS Access SQL and SQL Server has a special keyword top • select top 10 attribute … • Oracle has no such feature, but it can be simulated using the pseudo-column rownum. • Fig. 8-14 + 8-15, page 184 • sub-query in from clause • inline view [more on views later on] SQL advanced select using Oracle

  16. Correlated subquery,185 • Ordinary subquery • The inner query does not reference columns from the outer query • The inner query can be “calculated” once and for all. • Correlated subquery • The inner query references columns from the outer query • Like programming 2 loops, one inside the other • Figure 8-17, page 187 SQL advanced select using Oracle

  17. The EXISTS operator, 187 • New operator used with correlated sub-queries • Result • False • If the current result of the sub-query is empty • True • If the current result of the sub-query is not empty • Examples • Figure 8-18, page 187 • At least one student has the faculty (teacher) • Select ‘1’ • We have to select something, but in this case we don’t use it! • Figure 8-19, page 188 • No student has the faculty (teacher) SQL advanced select using Oracle

  18. Final remarks • SQL (specially select) is like programming. • There's only one place to learn it - at the keyboard! • Make advanced select statements incrementally • Starting from the innermost sub-query. • Monitor the results • Take advantage of advanced select statements • Don't try to program the same features in your application. SQL advanced select using Oracle

More Related