More Queries

From

Database Principals Programming Performance by O’Neil & O’Neil

A weakness of SQL: Too many equivalent forms

• The SQL language is controversial in part because there are often a large number of different way to pose the same query

• How would you write this query: Retrieve the city names for customers who order product p01

Possible ways to write query

• select distinct city from customers
where cid in ( select cid from orders
where pid = ‘p01’);

• select distinct city from customers
where cid =any ( select cid from orders
where pid =‘p01’);

• select distinct city from customers c
where exists ( select * from orders
where cid = c.cid and pid = ‘p01’);

• select distinct city
from customers c, orders x
where x.cid = c.cid and x.pid = ‘p01’);

Why are there so many ways to do the same thing?

• Good question.

• In the early 1970s, an IBM group advocated the multiplicity of predicates as being more user friendly.

• It is perfectly feasible to do without the IN predicate and all the quantified (ANY-or-ALL) predicates, as long as we have the predicate [NOT]EXISTS

1.Get the customer IDs of customers who order a product for which an order is also placed by agent a06.

• Select distinct y.cid
from orders x, orders y
where y.pid = x.pid and x.aid = ‘a06;

2.Retrieve all information concerning agents based in Duluth or Dallas

• Select * from agents
where city in (‘Dulth’, ‘Dallas’);

3.Find the agent ID of agents with a minimum percent commission

• select aid from agents
where percent <= ( select percent
from agents);

4.Find all customers who have the same discount as that of any of the customers in Dallas or Boston

• Select cid, cname from cusomers
where discnt =any ( select discnt
from customers
where city = ‘Dallas’
OR city=‘Boston’);

5. Provide product ID values of all products purchased by at least two customers (133)

• select pid from orders
group by pid
having count(distinct cid) >=2;

6. Find out all product and agent IDs and the total quantity ordered of the product by the agent, when this quantity exceeds 1000;

• select pid, aid, sum(qty) as TOTAL
from orders
group by pid, aid
having sum(qty) > 1000;

7. Print out the agent name and agent ID, and the product name and product ID, together with the total quantity each agent supplies of that product to customers c002 and c003. (130)

• select aname, a.aid, pname, p.pid, sum(qty)
from orders x, products p agents a
where x.pid=p.pid
and x.aid = a.aid
and x.cid in (‘c002), ‘c003’)
group by, aname, a.aid, p.name, pid;