1 / 15

Geo-Databases: lecture 4 Complex Queries in SQL

Learn how to efficiently manage interrelated information in multiple tables using advanced SQL queries in this comprehensive lecture led by Prof. Dr. Thomas H. Kolbe from Technische Universität Berlin.

lshaddix
Download Presentation

Geo-Databases: lecture 4 Complex Queries in 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. Geo-Databases: lecture 4Complex Queries in SQL Prof. Dr. Thomas H. Kolbe Institute for Geodesy and Geoinformation Science Technische Universität Berlin Credits: This material is mostly an english translation of the course module no. 8 (‘Geo-Datenbanksysteme‘) of the open e-content platform www.geoinformation.net.

  2. Complex queries in SQL

  3. Motivation • In many cases interrelated information is spread across multiple tables, e.g. in order to avoid redundancy. • Problem: How can we associate the professors with their lectures?

  4. Combination of tables The assignment of professors with their lectures follows a general principle: 1. Create all pairs of tuples of Professoren and Vorlesungen! 2. Filter the valid combinations from this tuple set.

  5. Cross Join All pairs of tuples are generated using the following query. The result is the so-called cross join (also called cartesian product)of the two tables. Schema of the resulting table: Concatenation of attributes SELECT * FROM Professoren CROSS JOIN Vorlesungen

  6. General Join Operator The general join allows to constrain the considered tuples using an arbitrary selection condition. The selection condition specifies which tuple pairs are valid. Wanted: Which professors teach which subjects? SELECT * FROM Professoren JOIN Vorlesungen ON Professoren.PersNr = Vorlesungen.PersNr Result:

  7. Natural Join • Tests all columns having the same name from both tables on identity • Schema of the resulting table: Like cross join, but attributes with the same name are only given once • Example: Show the lecturer for each course! • SELECT * FROM Professoren NATURAL JOIN Vorlesungen

  8. Problem of previous Join Operators • In some situations we may be interested in joining two tables, where tuples, that have no corresponding partner should also be included in the result set. • Example: Create a table with all professors and their lectures! Problem: The last tuple has no corresponding partner. Therefore it would not appear in the result set using the previously introduced join operators. Previously presented join operators do not fit the problem!

  9. Outer Join Left outer join: tuples of the left table, that have no corresponding partner are retained. For these tuples the attributes of the right table are set to NULL. Similarly the right outer join retaines all the tuples from the right table. The full outer join combines the functionality of both left and right outer join. Example: SELECT * FROM Professoren LEFT OUTER JOIN Vorlesungen ON Professoren.PersNr=Vorlesungen.PersNr

  10. Overview of Join Operators attributelist: all attributes from Professoren, non-redundant attributes from Vorlesung

  11. set operation subquery (=nested SQL-query) Nested Queries • A nested query (or subquery)“SELECT…FROM…WHERE…“is allowed: • in the from clause (as a substitute for table names) • as operand of the operators UNION, INTERSECT, EXCEPT, IN, EXISTS • In case the result of the subquery is a scalar (that means a table comprising one row and usually one column) also: • in a comparison operation • as an attribute within the select clause • Example: Formulation of a professors-lectures join with subquery:

  12. Existential Quantifier • Existential quantifiers offer another way to perform complex queries • operates on a table T • returns the logical value TRUE if T is not empty • Example: • All professors that do not have lectures. negation existential quantifiers Remark: A FOR ALL -operator is not provided in SQL!

  13. Set Operations (1) • Alternatively, the previous query can be formulated using set • operations. The operators INand NOT IN are used to test • for membership within a set. • Wanted: All professors that don‘t have lectures. all professors professors with lectures

  14. Set Operations (2) • SQL provides the classical set operations: • RUNIONS for set union • RINTERSECTS for the intersection and • REXCEPTS for the set difference of two tables R and S • Remarks: • Schema compatibility of R and S is required • The set operations eliminate duplicates! • If duplicates should be retained: UNION ALL,… union operator all staff members all employees all professors

  15. References • Jim Melton, Alan R. Simon, SQL 1999: Understanding Relational Language Components, Morgan Kaufmann Publishers, 2001

More Related