1 / 25

SQL - Select

SQL - Select. Join / Outer Join Sub queries. 1. Join. Join Outer join Left outer join Right outer join. 1.1 Join. List customer ACME’s orders record in January Select * from customers where cname = ‘ACME’ Select * from orders where cid = ‘c001’ and month = ‘jan’

tadita
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 • Join / Outer Join • Sub queries

  2. 1. Join • Join • Outer join • Left outer join • Right outer join

  3. 1.1 Join • List customer ACME’s orders record in January • Select * from customers where cname = ‘ACME’ • Select * from orders where cid = ‘c001’ and month = ‘jan’ • select o.* from customers c, orders o where c.cid = o.cid and cname = 'ACME' and month = 'jan'

  4. 1.1 Join (cont) • List the names of all customers who have bought comb • Select pid from products where pname = ‘comb’ • Select cid from orders where pid = ‘p01’ • Select cname from customers where cid = …. • Select cname from customers c, orders o, products p where p.pid = o.pid and c.cid = o.cid and p.pname = ‘comb’

  5. 1.1 Join (cont) • List the names of all customers who have bought comb • Select distinct cname from customers c, orders o, products p where p.pid = o.pid and c.cid = o.cid and p.pname = 'comb'

  6. 1.1 Join (cont) • List all customer pairs who live in the same city • Select c1.cid, c2.cid from customers c1, customers c2 where c1.city = c2.city and c1.cid != c2.cid • Select c1.cid, c2.cid from customers c1, customers c2 where c1.city = c2.city and c1.cid < c2.cid

  7. 1.1 Join (cont) • List all the product names that have been ordered by at least two customers. • select distinct p.pname, o1.pid from orders o1, orders o2, products p where o1.pid = p.pid and o1.pid = o2.pid and o1.cid < o2.cid

  8. 1.1 Join (cont) • List the agent names and their total sale amount (orders.dollars). • select aname, sum(dollars) total_sales from agents a, orders o where a.aid = o.aid group by a.aid, a.aname

  9. R lxlo S A B1 B2 C a1 b1 b1 c1 R lxl S a1 b1 b1 c2 A B1 B2 C a2 b1 b2 c3 a1 b1 b1 c1 a1 b2 b1 null a1 b1 b1 c2 null b2 b2 c4 a2 b1 b2 c3 1.2 Outer join A B1 B2 a1 b1 b1 R a1 b2 b1 a2 b1 b2 B1 B2 C b1 b1 c1 S b1 b1 c2 b1 b2 c3 b2 b2 c4

  10. 1.2 Outer Join (cont) • Modification to CAPS database. • 1. Add two more records • insert agents values ('a07', 'Tony', 'Boston', 8); • insert orders values (1030, 'mar', 'c006', 'a08', 'p01', 800, 400.00); • 2. Create a new table “Sales” • create table Sales • (aid char(3) not null, • total money • ); • 3. Load data into table “Sales” • Insert into Sales • select aid, sum(dollars) from orders group by aid

  11. 1.2 Outer Join (cont) Join (inner join) • select aname, aid, total from sales s join agents a on s.aid = a.aid • Select aname, a.aid, total from sales s join agents a on s.aid = a.aid

  12. 1.2 Outer Join (cont) Outer Join • select aname, a.aid, total from sales s full outer join agents a on s.aid = a.aid

  13. R left outer join S A B1 B2 C a1 b1 b1 c1 a1 b1 b1 c2 a2 b1 b2 c3 a1 b2 b1 null 1.3 Left outer join A B1 B2 a1 b1 b1 R a1 b2 b1 a2 b1 b2 B1 B2 C b1 b1 c1 S b1 b1 c2 b1 b2 c3 b2 b2 c4

  14. 1.3 Left outer Join (cont) Left outer Join: List name, aid and total sale record of agents who have sale records in the sales table. • select aname, a.aid, total from sales s left outer join agents a on s.aid = a.aid

  15. R right outer join S A B1 B2 C a1 b1 b1 c1 a1 b1 b1 c2 a2 b1 b2 c3 null b2 b2 c4 1.4 Right outer join A B1 B2 a1 b1 b1 R a1 b2 b1 a2 b1 b2 B1 B2 C b1 b1 c1 S b1 b1 c2 b1 b2 c3 b2 b2 c4

  16. 1.4 Right outer Join (cont) Right outer Join: List name, aid and total sale record of agents who have records in the agents table. • select aname, a.aid, total from sales s left outer join agents a on s.aid = a.aid

  17. 2.1 Sub queries • List cid values of customers who place orders with agent in Dallas • Select aid from agents where city = 'Dallas‘ • Select distinct cid from orders where aid in (select aid from agents where city = 'Dallas')

  18. 2.1 Sub queries (cont) • List the names and discounts of all customers who place order through agents in Dallas • Select aid from agents where city = 'Dallas‘ • Select distinct cid from orders where aid in (select aid from agents where city = 'Dallas') • Select cname, discount from customers where cid in (select distinct cid from orders where aid in (select aid from agents where city = 'Dallas'))

  19. Uncorrelated subquery Correlated subquery 2.1 Sub queries (cont) • List the names of customers who order product p05. • Select distinct cname from customers where cid in (select distinct cid from orders where pid = 'p05') • Select distinct cname from customers where 'p05' in (select pid from orders where cid = customers.cid)

  20. 2.1 Sub queries (cont) • List the names of customers who do not order product p05. • Select distinct cname from customers where cid in (select distinct cid from orders where pid != 'p05') • Select distinct cname from customers where cid not in (select distinct cid from orders where pid = 'p05')

  21. 2.2 Sub queries – other key words • Some\ any \ all • Exists

  22. 2.2 Sub queries – other key words (cont) • Find agents (aid) with a minimum commission. • Select aid from agents where percentage <= all (select percentage from agents) • Select aid from agents where percentage = (select min(percentage) from agents)

  23. 2.2 Sub queries – other key words (cont) • Find agents (aid) with a minimum commission. • Select aid from agents where percentage <= any (select percentage from agents) • Select aid from agents where percentage <= some (select percentage from agents)

  24. 2.2 Sub queries – other key words (cont) • Find all customers (cid and cname) who have the same discount as that of any of the customers in Dallas • Select cid, cname from customers where discnt = some (select discnt from customers where city = 'Dallas') • Select cid, cname from customers where discnt = any (select discnt from customers where city = 'Dallas')

  25. 2.2 Sub queries – other key words (cont) • Find all customer names where the customers places an order through agent a05 • Select distinct cname from customers where cid in (select cid from orders where aid = 'a05') • Select distinct c.cname from customers c, orders o where c.cid = o.cid and o.aid = 'a05' • Select distinct c.cname from customers c where exists (select * from orders o where c.cid = o.cid and o.aid = 'a05') • Select distinct c.cname from customers c join orders o on c.cid = o.cid and o.aid = 'a05'

More Related