DATABASEchw32

2020-03-01 153浏览

  • 1.DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
  • 2.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.1 Select distince cid from orders where ai d in (select aid from agents where cit y =“Duluth” or city = “ Dallas”);  Zhejiang Sci-Tech University Lican Huang 2
  • 3.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.2 select * from agents where city in(“Dul uth”,”Dallas”);  Zhejiang Sci-Tech University Lican Huang 3
  • 4.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.3 Select cname, discnt from customers w here cid in(select cid from orders whe re aid in (select aid from agents wher e city in (“Duluth”,”Dallas”)));  Zhejiang Sci-Tech University Lican Huang 4
  • 5.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.4 Select distinct cname from customers where ‘p05’ in (select pid from orders where cid = customers.cid);  Zhejiang Sci-Tech University Lican Huang 5
  • 6.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.6 Select ordno from orders where(cid,ai d) in (select cid, aid from customers c, agents a where c.city = “Duluth” and a.city =“New York”);  Zhejiang Sci-Tech University Lican Huang 6
  • 7.3 Basic SQL Query Language  3.4 Subqueries The IN Predicate expr NOT IN(Subquery) Expr[NOT] IN (Subquery) expr[NOT] IN (val {,val….}) Zhejiang Sci-Tech University Lican Huang 7
  • 8.3 Basic SQL Query Language 3.4 Subqueries The Quantified Comparison Predicate Expr theta {SOME ANY ALL}(Subquery)  Theta <,<=, =, <>,>,>= SOME ANY are same. Zhejiang Sci-Tech University Lican Huang 8
  • 9.3 Basic SQL Query Language 3.4 Subqueries Example 3.4.7 Select aid from agents where percent < =all (select percent from agents);  Zhejiang Sci-Tech University Lican Huang 9
  • 10.3 Basic SQL Query Language 3.4 Subqueries Example 3.4.8 select cid, cname from customers wher e discnt = some(select discnt from cu stomers where city = “Dallas” or city = “Boston”);  Zhejiang Sci-Tech University Lican Huang 10
  • 11.3 Basic SQL Query Language 3.4 Subqueries Example 3.4.9 select cid from customers where disc nt < all (select discnt from customers where city = “Duluth”);  Zhejiang Sci-Tech University Lican Huang 11
  • 12.3 Basic SQL Query Language  3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.10 select distinct c.aname from custom ers c where exists (select * from order s x where c.cid = x.cid and x.aid = “a 05”); Zhejiang Sci-Tech University Lican Huang 12
  • 13.3 Basic SQL Query Language  3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.11 select distinct cid from orders x whe re pid =“p01” and exists (select * fro m orders where cid = x.cid and pid = ‘p07’); Zhejiang Sci-Tech University Lican Huang 13
  • 14.3 Basic SQL Query Language  3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.12 select distinct c.cname from custom ers c where not exists(select * from or ders x where c.cid = x.cid and x.aid = ‘a05’); Zhejiang Sci-Tech University Lican Huang 14
  • 15.3 Basic SQL Query Language 3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.13 page 105  Zhejiang Sci-Tech University Lican Huang 15
  • 16.3 Basic SQL Query Language 3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.14 select distinct cid from orders x whe re no exists (select * from orders wher e cid = x.cid and aid = ‘a03’);  Zhejiang Sci-Tech University Lican Huang 16
  • 17.3 Basic SQL Query Language  3.5 UNION Operator Subquery UNION [ALL] Subquery Select city from customers union select city from agents Select city from customers union all sel ect city from agents  Zhejiang Sci-Tech University Lican Huang 17
  • 18.3 Basic SQL Query Language  3.6 Advanced SQL Syntax  Subquery {UNION [ALL] INTERSECT[ALL] EXCEPT [ALL]Subquery} Page 116 Q1 INTERSECT ALL Q2 Q1 INTERSECT Q2 Q1 EXCEPT ALL Q2 Q1 EXCEPT Q2    Zhejiang Sci-Tech University Lican Huang 18
  • 19.3 Basic SQL Query Language  3.6 Advanced SQL Syntax example 3.6.1 four solutions Example 3.6.2 Page 116, 117  Zhejiang Sci-Tech University Lican Huang 19
  • 20.3 Basic SQL Query Language  3.6 Advanced SQL Syntax Join  Example 3.6.4 select distinct cname from(orders join produ cts using(pid)) join customers using(cid) wh ere price < 0.50;  Zhejiang Sci-Tech University Lican Huang 20
  • 21.3 Basic SQL Query Language 3.7 set functions in SQL Example 3.7.1 select sum(dollars) as totaldollars from orders;  Zhejiang Sci-Tech University Lican Huang 21
  • 22.3 Basic SQL Query Language 3.7 set functions in SQL COUNT SUM AVG MAX MIN  page 124 Zhejiang Sci-Tech University Lican Huang 22
  • 23.3 Basic SQL Query Language 3.7 set functions in SQL Example 3.7.2 select sum(dollars) as total from order s where pid = “p03”;  Zhejiang Sci-Tech University Lican Huang 23
  • 24.3 Basic SQL Query Language 3.7 set functions in SQL Example 3.7.3 select count(cid) from customers; Example 3.7.4 select count(distinct city) from customers;  Zhejiang Sci-Tech University Lican Huang 24
  • 25.3 Basic SQL Query Language 3.7 set functions in SQL Example 3.7.5 select cid from customers where discn t <(select max(discnt) from customer s);  Zhejiang Sci-Tech University Lican Huang 25
  • 26.3 Basic SQL Query Language 3.7 set functions in SQL Example 3.7.6 select cid from customers where discn t <(select max(discnt) from customer s);  Zhejiang Sci-Tech University Lican Huang 26
  • 27.3 Basic SQL Query Language  3.7 set functions in SQL Example 3.7.7 Example 3.7.8 select * from customers where discnt is null; Zhejiang Sci-Tech University Lican Huang 27
  • 28.3 Basic SQL Query Language 3.7 set functions in SQL Example 3.7.9 select avg(discnt) from customers;  Zhejiang Sci-Tech University Lican Huang 28