









Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
This document, authored by david toman from the university of waterloo, provides an introduction to sql syntax sugar, focusing on the use of where subqueries. Various types of esoteric predicates, such as range searches and string matches, and explains how to use subqueries to define more complex search conditions. The document also discusses the advantages and disadvantages of using where subqueries and provides examples of their usage.
Typology: Slides
1 / 17
This page cannot be seen from the preview
Don't miss anything!
David Toman
School of Computer Science University of Waterloo
Introduction to Databases CS
1 Esoteric predicates (atomic)
In the first two cases Q has to be unary.
Nesting in the WHERE clause is mere syntactic sugar:
SELECT r.b SELECT r.b FROM r FROM r, ( WHERE r.a IN ( SELECT DISTINCT b SELECT b FROM s FROM s ) s ) WHERE r.a=s.b
All of the remaining constructs can be rewritten in similar fashion...
All author-publication ids for all publications except books and journals: SQL> select * 2 from wrote 3 where publication not in ( 4 ( select pubid from book ) 5 union 6 ( select pubid from journal ) )
AUTHOR PUBLICAT
1 ChTo 1 ChTo98a 1 Tom 2 ChTo 2 ChTo98a
... search conditions may contain complex queries.
Find article with most pages: SQL> select pubid 2 from article 3 where endpage-startpage>=all ( 4 select endpage-startpage 5 from article 6 )
ChTo
... another way of saying max attr = SOME (Q) is the same as attr IN (Q) attr <> ALL (Q) is the same as attr NOT IN (Q)
... and now it is easy to complement conditions:
SQL> select * 2 from wrote r 3 where not exists ( 4 select * 5 from wrote s 6 where r.publication=s.publication 7 and r.author<>s.author 8 )
AUTHOR PUBLICAT
1 Tom
SQL> select * 2 from wrote r 3 where publication in ( 4 select publication 5 from wrote s 6 where r.author<>s.author 7 )
AUTHOR PUBLICAT
1 ChTo 1 ChTo98a 2 ChTo 2 ChTo98a 2 ChSa 3 ChSa
List all authors who always publish with someone else: SQL> select a1.name, a2.name 2 from author a1, author a 3 where not exists ( 4 select * 5 from publication p, wrote w 6 where p.pubid=w1.publication 7 and a1.aid=w1.author 8 and a2.aid not in ( 9 select author 10 from wrote 11 where publication=p.pubid 12 and author<>a1.aid 13 ) 14 )