1 / 16

SQL - Select

SQL - Select. Union / Intersect / Difference / Division. 3. Union / Intersect / Difference / Division. Union Intersect Difference Division. 3.1 Union. List all cities where either a customer or an agent, or both is based. Customers[city] U Agents[city]

britain
Télécharger la présentation

SQL - Select

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 - Select • Union / Intersect / Difference / Division

  2. 3. Union / Intersect / Difference / Division • Union • Intersect • Difference • Division

  3. 3.1 Union List all cities where either a customer or an agent, or both is based. Customers[city] U Agents[city] Select city from customers union select city from agents Select city from customers union all select city from agents order by city

  4. 3.1 Union (cont) List all orders (ordno) that are placed by c001 or c002 Select distinct ordno from orders where cid = 'c001' or cid = 'c002‘ Select ordno from orders where cid = 'c001' union select ordno from orders where cid = 'c002'

  5. 3.2 Intersect Find customers (cid) who order both products p01 and p07. (Customers where pid = p01)[cid] n (Customers where pid = p07)[cid] Select cid from orders where pid = 'p01' and pid = 'p07‘ Select distinct o1.cid from orders o1 where o1.pid = 'p01' and cid in (select cid from orders o2 where o2.pid = 'p07')

  6. 3.3 Difference Find all customers (cid) where the customer does not place an order through agent a05 (Orders)[cid] - (Orders where aid = ‘a05’)[cid] Select distinct cid from orders where aid != 'a05' Select distinct cid from orders where cid not in (select cid from orders where aid = 'a05')

  7. 3.4 Division • List customers (cid) who place orders with all agents based in New York. • Orders[cid, aid] divideby (Agents where city = ‘New York’)[aid] • Select cid from orders where aid = all (select aid from agents where city = 'New York')

  8. 3.4 Division (cont) • R÷S = R[A1..An] – ((R[A1..An] X S) – R)[A1..An] • Condition 1: An agent based in New York that does not take an order for Customer.cid • Condition 2: For customer.Cid, there is not agents that satisfy condition 1. • Select cid from customers where condition 2.

  9. 3.4 Division (cont) • Condition 1: An agent based in New York that does not take an order for Customer.cid • a.city = ‘New York’ and not exists (select * from orders o where o.cid = c.cid and o.aid = a.aid)

  10. 3.4 Division (cont) Condition 2: For customer.Cid, there is not agents that satisfy condition 1. • Not exists (select * from agents a where condition 1) • Not exists (select * from agents a where a.city = ‘New York’ and not exists (select * from orders o where o.cid = c.cid and o.aid = a.aid))

  11. 3.4 Division (cont) • Select cid from customers where condition 2. • select c.cid, c.cname from customers c where not exists (select * from agents a where a.city = 'New York' and not exists (select * from orders o where o.cid = c.cid and o.aid = a.aid))

  12. 3.4 Division (cont) • Get the aid values of agents in Duluth who place orders for all products costing more than a dollar.

  13. 3.4 Division (cont) • Condition 1: An product (pid) costing over $1 is not order by an agent • Condition 2: For aid, there is not product that satisfy condition 1. • Select aid from agents where condition 2 + agents based in Duluth

  14. 3.4 Division (cont) • Condition 1: An product (pid) costing over $1 is not order by an agent • p.price > 1.00 and not exists (select * from orders o where o.pid = p.pid and x.aid = a.aid)

  15. 3.4 Division (cont) • Condition 2: For aid, there is not product that satisfy condition 1. • Not exists (select pid from products p where condition 1) • Not exists (select pid from products p where p.price > 1.00 and not exists (select * from orders o where o.pid = p.pid and x.aid = a.aid))

  16. 3.4 Division (cont) • Select aid from agents where condition 2 + agents based in Duluth • select a.aid from agents a where a.city = 'Duluth' and not exists (select pid from products p where p.price > 1.00 and not exists (select * from orders o where o.pid = p.pid and o.aid = a.aid))

More Related