SQL - Select

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'